Iterative query construction processing in a relational database

Abstract

A method for functionally rewriting an iterative query of a relational database management system (RDBMS) is provided. The method includes receiving a first iterative query having a first non-iterative portion defining a first main table and a first iterative portion generating values in rows of a first work table based on values in rows of the first main table, determining that the first iterative portion modifies all rows of the first work table, and rewriting the first iterative portion including: adding a rename operation to rename the first work table to a new first main table and rename the first main table to a new first work table; adding a first delete operation to delete each row of the new first main table; and adding a first loop operation to repeat the first iterative portion until a first termination condition is satisfied.

Description

Technical Field

[0001] This disclosure relates to techniques for constructing iterative queries for relational databases and methods for processing iterative query constructs within a relational database system. Background Technology

[0002] Relational database management systems (RDBMS) store data as tuples (rows), which are grouped into relations (tables) with different attributes (columns). The primary goal of the relational model is to hide the complexity of how data should be processed, focusing instead on what data should be returned to the user. To achieve this, most RDBMSs use Structured Query Language (SQL) to access the data within the RDBMS. The RDBMS parses, analyzes, and optimizes the SQL statements to generate an execution plan. Finally, the RDBMS executes this plan and returns the resulting tuples to the user to answer the query. Summary of the Invention

[0003] Various examples are now described to introduce, in a simplified form, some selected concepts that will be further described in the detailed embodiments below. This invention is not intended to identify key or essential features of the claimed subject matter, nor is it intended to limit the scope of the claimed subject matter.

[0004] Relational Database Management Systems (RDBMS) handle iterative queries, such as iterative Common Table Expressions (CTEs) or iterative views, by modifying the RDBMS's function rewrite process. Function rewrite adds a rename or update operation at the end of the first iteration of the query. The added rename operation swaps the memory used by the main table with the memory used by the working table and is used when all rows of the working table are modified in the iteration portion. The update operation is used when only some rows of the working table are modified and those rows are transferred to the main table. After the rename or update operation, function rewrite also adds an operation at the end of the iteration portion to conditionally branch back to the first operation in the iteration portion if the termination condition is not met, and to terminate the iteration portion when the termination condition is met.

[0005] According to a first aspect, a method for functionally rewriting an iterative query of a relational database management system (RDBMS) functionally rewrites a first query that operates on a first master table defined by a first non-iterative portion of the first query and a first work table defined by a first iterative portion of the first query. The query rewriting determines that the iterative portion modifies all rows of the first work table and adds a rename operation to rename the first work table to a new first master table, and renames the first master table to a new first work table. The functional rewriting also adds a first delete operation to delete each row of the new first work table and adds a loop operation to repeat the iterative portion until a first termination condition is met.

[0006] According to a first embodiment of the first aspect, the loop operation adds a first comparison operation and a first branch operation as the last operation in the first iteration portion. The first comparison operation tests a first termination condition; if the first termination condition is not met, the first branch operation branches to the first operation in the first iteration portion. If the first termination condition is met, the first comparison operation terminates the first iteration portion.

[0007] According to the second embodiment of the first aspect, rewriting the first iteration portion includes adding a rename operation and a first deletion operation immediately before the first comparison operation.

[0008] According to a third embodiment of the first aspect, the method functionally rewrites the second iterative query, which includes a second non-iterative portion defining a second master table and a second iterative portion generating values ​​for rows in a second worksheet based on values ​​in rows of the second master table. The functional rewrite determines that the second iterative portion modifies fewer rows than all rows in the second worksheet and adds an update operation to replace the corresponding row in the second master table with the modified row from the second worksheet. The rewrite also adds a second delete operation to delete each modified row in the second worksheet.

[0009] According to the fourth embodiment of the first aspect, the function rewrite adds a second comparison operation and a second branch operation as the last operations in the second iteration section. The second comparison operation tests a second termination condition. When the second comparison operation determines that the termination condition is met, the second branch operation conditionally branches to the first operation in the second iteration section, and terminates the second iteration section when the termination condition is not met. The function rewrite adds an update operation and a second deletion operation immediately before the second comparison operation.

[0010] According to a fifth embodiment of the first aspect, the first termination condition includes the number of iterations performed by the first iteration portion. The function rewrites the initialization counter, and a first comparison operation compares the value of the counter with the number of iterations.

[0011] According to a sixth embodiment of the first aspect, the first termination condition includes an expression evaluated by a first iteration portion. A first comparison operation evaluates the expression to determine whether the first termination condition is satisfied.

[0012] According to a seventh embodiment of the first aspect, the first termination condition includes a difference measurement between a first value of a target entry from the first master table in the previous iteration and a second value of a target entry from the first master table in the current iteration. A function rewrites the add operator to store the first value of the target entry from the first master table as a first operation of the first iteration portion. A first comparison operation determines whether the difference between the second value of the target entry from the first master table and the stored first value of the target entry is less than the difference measurement, wherein the first comparison operation determines whether the termination condition is met.

[0013] According to a second aspect, an apparatus for functionally rewriting an iterative query of a relational database management system (RDBMS) includes: a memory including program instructions; and one or more processors in communication with the memory, wherein the program instructions cause the one or more processors to functionally rewrite a first query, the first query operating on a first master table defined by a first non-iterative portion of the first query and a first worksheet defined by a first iterative portion of the first query. The program instructions further cause the one or more processors to determine that the first iterative portion modifies all rows of the first worksheet and add a rename operation to rename the first worksheet to a new first master table, and rename the first master table to a new first worksheet. The program instructions also cause the one or more processors to add a first delete operation to delete each row of the new first worksheet and add a loop operation to repeat the iterative portion until a first termination condition is met.

[0014] According to the first embodiment of the second aspect, the program instructions for adding loop operations add a first comparison operation and a first branch operation as the last operations in the first iteration portion. The first comparison operation and the first branch operation are configured to cause one or more processors to test a first termination condition, branch to the first operation in the first iteration portion when the first termination condition is not met, and terminate the first iteration portion when the first termination condition is met.

[0015] According to the second embodiment of the second aspect, the program instructions add a rename operation and a first delete operation immediately before the first comparison operation.

[0016] According to a third embodiment of the second aspect, the operation further includes rewriting a second iterative query having a second non-iterative portion defining a second master table and a second iterative portion generating values ​​in rows of a second work table based on values ​​in rows of the second master table. Program instructions further cause one or more processors to determine that the second iterative portion modifies fewer rows than all rows of the second work table. The operation further includes adding an update operation to the second iterative portion to replace the corresponding row in the second master table with the modified row from the second work table, and adding a second delete operation to delete each modified row in the second work table.

[0017] According to a fourth embodiment of the second aspect, program instructions cause one or more processors to add a second comparison operation and a second branch operation as the final operation in a second iteration portion. The second comparison operation tests a second termination condition; if the termination condition is not met, the second branch operation branches to the first operation in the second iteration portion; and if the second comparison operation determines that the termination condition is met, the second iteration portion terminates. The program instructions also cause one or more processors to add an update operation and a second deletion operation immediately preceding the second comparison operation, and to add the deletion operation as the first operation in the second iteration portion.

[0018] According to a fifth embodiment of the second aspect, the first termination condition includes the number of iterations performed by the iteration portion, and the operation includes rewriting the first iteration portion to add an operation to initialize a counter. The operation to add a first comparison operation includes adding an operation to compare the value of the counter with the number of iterations.

[0019] According to a sixth embodiment of the second aspect, the first termination condition includes an expression evaluated by the iteration portion, and the operation of adding the first comparison operation includes adding an evaluation expression to determine whether the first termination condition is satisfied.

[0020] According to a seventh embodiment of the second aspect, the first termination condition includes a difference measurement between a first value of a target entry from the first master table in the previous iteration and a second value of a target entry from the first master table in the current iteration, and the operation further includes rewriting the first iteration portion to add an operator to store the first value of the target entry from the first master table as a first operation of the first iteration portion. The operation of adding a comparison operation includes adding an operator to determine whether the difference between the second value of the target entry from the first master table and the stored first value of the target entry is less than the difference measurement, wherein the comparison operation determines whether the termination condition is met.

[0021] According to a third aspect, a computer-readable storage medium stores computer instructions for functionally rewriting an iterative query of a relational database management system (RDBMS). When executed by one or more processors, the computer instructions cause the one or more processors to functionally rewrite a first query that operates on a first master table defined by a first non-iterative portion of the first query and a first worksheet defined by a first iterative portion of the first query. The program instructions further cause the one or more processors to determine that the first iterative portion modifies all rows of the first worksheet and add a rename operation to rename the first worksheet to a new first master table, and rename the first master table to a new first worksheet. The program instructions also cause the one or more processors to add a first delete operation to delete each row of the new first worksheet and add a loop operation to repeat the iterative portion until a first termination condition is met.

[0022] According to a first embodiment of the third aspect, the loop operation adds a first comparison operation and a first branch operation as the last operations in the first iteration portion. The first comparison operation and the first branch operation are configured to cause one or more processors to test a first termination condition, branch to the first operation in the first iteration portion when the first termination condition is not met, and terminate the first iteration portion when the first termination condition is met.

[0023] According to a second embodiment of the third aspect, the operation further includes rewriting a second iterative query having a second non-iterative portion defining a second master table and a second iterative portion generating values ​​in rows of a second work table based on values ​​in rows of the second master table. Program instructions further cause one or more processors to determine that the second iterative portion modifies fewer rows than all rows of the second work table. The operation further includes adding an update operation to the second iterative portion to replace the corresponding row in the second master table with the modified row in the second work table, and adding a second delete operation to delete each modified row in the second work table.

[0024] According to a third embodiment of the third aspect, the operation further includes adding a second comparison operation and a second branch operation as the final operation in the second iteration portion. The second comparison operation is configured to cause one or more processors to test a second termination condition, and the second branch operation is configured to conditionally branch one or more processors to a first operation in the second iteration portion when the second comparison operation determines that the termination condition is not met, and to terminate the second iteration portion when the second comparison operation determines that the termination condition is met. The rewriting operation of the second iteration portion includes adding an update operation and a second deletion operation immediately preceding the second comparison operation.

[0025] According to a fourth embodiment of the third aspect, the first termination condition includes the number of iterations performed by the first iteration portion, and the operation further includes adding an operation to the first non-iteration portion to initialize a counter. The operation of adding the first comparison operation includes adding an operation to compare the value of the counter with the number of iterations.

[0026] According to a fifth embodiment of the third aspect, the first termination condition includes an expression evaluated by the first iteration portion, and the operation of adding the first comparison operation includes adding an evaluation expression to determine whether the first termination condition is satisfied.

[0027] According to a sixth embodiment of the third aspect, the first termination condition includes a difference measurement between a first value of a target entry from the first master table in the previous iteration and a second value of a target entry from the first master table in the current iteration. The operation of rewriting the first iteration portion further includes adding an operator to store the first value of the target entry from the first master table as a first operation of the first iteration portion. The operation of adding a first comparison operation includes adding an operator to determine whether the difference between the second value of the target entry from the first master table and the stored first value of the target entry is less than the difference measurement, wherein the first comparison operation determines whether the termination condition is met.

[0028] Any of the above examples can be combined with any one or more of the other examples above to create new embodiments within the scope of this disclosure. Attached Figure Description

[0029] In accompanying drawings that are not necessarily drawn to scale, similar figures may depict similar components in different views. The accompanying drawings are intended to illustrate, rather than limit, the various embodiments discussed in this document.

[0030] Figure 1 This is a diagram illustrating the data flow constructed according to an iterative database based on an example embodiment.

[0031] Figure 2 This is a block diagram of a massively parallel processing database (MPPDB) according to an example embodiment.

[0032] Figure 3 This is a block diagram illustrating details of the coordinator module according to an example embodiment.

[0033] Figure 4 This is a tree diagram illustrating the query tree for page sorting queries according to an example embodiment.

[0034] Figure 5 This is a tree diagram illustrating the query tree of a page sorting query that has been functionally rewritten according to an example embodiment.

[0035] Figure 6This is a flowchart illustrating the operation of a functionally rewritten iterative database construction according to an example embodiment.

[0036] Figure 7 This is a block diagram of a processing system according to an example embodiment. Detailed Implementation

[0037] First, it should be understood that although illustrative implementations of one or more embodiments are provided below, regarding... Figure 1-7 The systems, methods, and / or apparatuses described herein can be implemented using any number of techniques, whether currently known or not. This disclosure should not be limited in any way to the illustrative embodiments, drawings, and techniques shown below (including exemplary designs and embodiments shown and described herein), but modifications can be made within the scope of the appended claims and their full equivalents.

[0038] In the following description, reference is made to the accompanying drawings, which form a part of this document, in which specific embodiments that can be practiced are illustrated by way of illustration. These embodiments have been described in sufficient detail to enable those skilled in the art to practice the subject matter of the invention, and it should be understood that other embodiments can be used and structural, logical, and electrical modifications can be made without departing from the scope of the invention. Therefore, the following description of exemplary embodiments should not be construed as limiting, and the scope of this disclosure is defined by the appended claims.

[0039] Although SQL has been used for decades and is an industry standard, it still has some limitations. For example, SQL queries cannot efficiently express iterative computations. These computations are typically used in algorithms where each iteration receives the output data from the previous iteration as input data. Queries using iterative processing often use different data in each iteration or attempt to estimate a specific value by executing the same query multiple times. Two representative examples are graph-related problems: Single Source Shortest Path queries, which compute the shortest path between two points, and PageRank queries, which find the most important node in a graph. The current SQL standard supports recursive processing via views or CTEs, but not purely iterative processing. The main limitation is the assumption that recursive queries need to reach a fixed point: a specific set of rows that satisfy the recursive relation. Therefore, aggregate functions that perform computations on a set of values ​​and return a single-value result are not allowed in the recursive part of the query. The termination condition for a recursive query is implicit; tuples can only be appended to the result, not updated. Currently, users who wish to perform iterative queries are switching from RDBMS to dedicated graphics processing engines that support custom vertex-based APIs (Application Program Interfaces) or Datalog systems that can more effectively optimize recursive queries.

[0040] The following example describes a native implementation of iterative CTEs in a massively parallel processing database (MPPDB) system. The native implementation offers advantages over external MPPDB implementations. Specifically, the native implementation ensures that the system handles the atomicity, consistency, isolation, and durability (ACID) properties without creating long transactions. Furthermore, the native implementation allows the workload manager to schedule iterative CTEs in the same way as other native database operations and queries, as it generates a single execution plan that can be examined as a single unit of processing. Additionally, the native implementation avoids the unnecessary overhead of external implementations' Data Definition Language (DDL) and Data Manipulation Language (DML) operations. Finally, the planner can apply existing query optimizations and cost estimates to the entire iterative CTE, not just individual SQL statements derived from the CTE by the middleware.

[0041] Since 1999, CTEs have been part of the SQL standard and are used in SQL programming to simplify complex queries. Typically, a CTE is a temporary, named result set that a user can reference within an SQL statement. CTEs also support recursive evaluation, enabling users to express hierarchical queries or path traversal algorithms.

[0042] Recent research has explored the possibility of extending recursive CTEs to allow aggregate functions and proposed new SQL structures and operators that can adapt to iterative queries on relational data. One modification proposes an implementation that generates stored procedures and executes them in an RDBMS, while another implements middleware between the user and the target database engine. However, both proposed frameworks involve external solutions implemented outside the SQL system.

[0043] While external methods offer flexibility, allowing users to choose their preferred database engine and avoid data transformation and loading steps, they also have limitations. First, external solutions struggle to maintain ACID properties for long-running query executions. Second, because these constructs are external to SQL, they are transformed into a series of basic SQL operations before being fed into the RDBMS's workload manager. The workload manager then treats each of these basic operations as its own query. Therefore, if the workload manager processes the query as a whole, the RDBMS cannot achieve the efficiency it would otherwise be capable of. Furthermore, the basic operations sent to the RDBMS incur additional overhead. For example, intermediate results are often explicitly defined as temporary tables that impose metadata overhead. The Data Manipulation Language (DML) used in external methods to initialize and update these intermediate results can also add processing overhead, as it may use locking and other transactions associated with these operations that are not used in the internal implementation. Finally, these external-based extensions cannot achieve many system-specific optimizations.

[0044] The following examples illustrate solutions to these problems and limitations of external implementations. Example embodiments extend the RDBMS to support iterative CTEs by extending all components of the database kernel, including the parser, rewrite subsystem, planner, and execution engine. Although the embodiments described below are iterative CTEs implemented in SQL, it is contemplated that they can be implemented in other iterative database constructs (e.g., iterative views), and / or with query languages ​​other than SQL. One embodiment uses MPPDB as the RDBMS. These examples use MPPDB (which is an Online Analytical Processing (OLAP) solution) instead of a traditional RDBMS because iterative queries are typically used in analytical environments particularly well-suited to implementation in MPPDB. However, it is contemplated that the embodiments described below can be implemented in a traditional RDBMS.

[0045] The example MPPDB parser extension implements the syntax for iterative CTEs and generates parse trees similar to those for regular and recursive CTEs. The example implementation converts the iterative CTE specification into a logical query tree, which is still a parse tree. This conversion is accomplished through new rewrite rules that transform the iterative CTE into existing RDBMS operators such as scans, joins, and aggregations. The example update logic implementation may include scanning from one temporary table to another. To implement the iterative logic, the described example adds new simple operators that allow conditional redirection to the previous step in the execution plan. The query planner optimizes the rewritten query in the same way as other queries.

[0046] Sofoklis Floratos et al., in their paper titled "SQLoop: High-Performance Iterative Processing in Data Management" (38th International Conference on Distributed Computing Systems (ICDCS), IEEE, 1039-1051, 2018), describe a SQL extension similar to the example implementation. While the syntax of this extension is the same, the system described in that paper is significantly different because it is implemented outside of SQL and suffers from the drawbacks of the middleware approach described above.

[0047] The following material describes an implementation of a Page Rank (PR) query as an example of an iterative CTE. For this computation, all edges of the graph are stored in a relation called “Edges”, which has three attributes: source (SRC), destination (DST), and weight. Each tuple in this relation can be mapped to the graph as an edge from node SRC to node DST, and has a specified number of weights. Because recursive queries do not allow aggregation operations in the recursive part, recursive views and recursive CTEs cannot be used to represent general iterative computations, such as PR queries. The example implementations of the iterative CTEs described avoid this problem because they implement termination conditions that are explicitly defined by the user. Therefore, iterative CTEs can support more general iterative computations and allow aggregation operations to be used in the iterative part of the query.

[0048] Before describing the iterative CTE, it is useful to describe the implementation of the PR query using a custom SQL-based application, as shown in Table 1.

[0049] Table 1

[0050]

[0051]

[0052] This PR query consists of multiple SQL statements. Lines 1-5 create the main table and work tables. Lines 7-11 execute the non-iterative part, and lines 14-34 execute the iterative part of the query once. Then, the iterative part needs to be executed N times by copying lines 14-34 N times. Part of the iterative calculation is the updated page sort result in lines 29-34. This solution works if N is known, but this is not always the case.

[0053] The example implementation solves the above problem through an iterative CTE that allows SQL programmers to describe iterative computations and explicitly define their termination conditions. The example CTE, R, includes a non-iterative part R0 and an iterative part R... I Similar to recursive CTEs, R0 is executed only once, while R... I Execute multiple times. The main difference between these two SQL structures is that iterates through the CTE to update the worksheet, rather than adding a new table. Furthermore, when the termination condition T is met... C When the query terminates, the query is complete. This provides users with the flexibility to define explicit termination conditions and eliminates the assumptions about fixed-point semantics. Therefore, aggregate functions can be used in R. I The general form of the iterative CTE is:

[0054] WITH ITERATIVE R AS(R0 ITERATE RI UNTIL T C )Q F

[0055] Implementing iterative queries in a Relational Database Management System (RDBMS) involves modifying the RDBMS's functional rewrite process by adding rename or update operations at the end of the iterative portion of the query. The rename operation swaps the memory used by the master table with that used by the working table and is used when all rows in the working table have been modified in the iterative portion. The update operation is used when the iterative portion modifies fewer rows than all rows in the working table and these rows are transferred to the master table. The functional rewrite also adds operations to re-execute the iterative portion if the termination condition is not met and to terminate the iterative portion when the termination condition is met.

[0056] Figure 1 An example execution flow 100 of a general form of iterative CTE is shown. At the beginning, the iterative CTE executes R0 and stores the result in the main CTE table 102. Then, for each iteration of the CTE, flow 100: a) executes R... I And store the results in worksheet 104, b) update the main CTE table 102 with the rows that exist in the worksheet, and c) determine whether T is satisfied. C To determine whether to perform another iteration, process 100 uses a unique row key / identifier to ensure correct updates to the main CTE table 102. If the user specifies a primary key, process 100 uses it to perform the update; otherwise, process 100 creates a unique row ID. Furthermore, if the user defines a CTE where the iteration portion results in a single row of duplicates in worksheet 104, process 100 generates a runtime error (not shown). This error message is generated because process 100 may not be able to handle two (or more) updates to the same row in the main CTE table 102. In such instances, the user can rewrite the query to redefine the iteration portion and explicitly specify how to use aggregation operations, grouping operations, or other SQL operations to resolve duplicates. In the last iteration of the iteration portion... I After completion (e.g., when termination condition T is met) C (At that time), the system executes query Q. F The system then returns the final result to the client computer.

[0057] Table 2 shows an example iteration of CTE (PageRank) for implementing PR across all pages.

[0058] Table 2

[0059]

[0060] In the example shown in Table 2, rows 2-4 correspond to the non-iterative part R0, and rows 5-17 correspond to the iterative part R0. I Line 17 corresponds to the termination condition T. C It terminates the iteration after 10 iterations, part R. I PageRank is the main CTE table 102, and IncomingEdges is the worksheet 104. After the last iteration, the results are returned as tuples (node, sort), as shown in line 18.

[0061] Table 3 and Figure 3-6 This describes a native implementation of iterating over CTEs within an MPPDB context. The example implementation within MPPDB applies an execution flow similar to that used by external solutions, but without adding DDL and DML operations for Create, Insert, and Update. Insert logic is implemented through the materialization of intermediate results. Materialization of intermediate results is a common execution operator in many RDBMSs, used to store the results of intermediate joins and aggregations. Updating the main table from an intermediate table can also be achieved through another materialization, appropriately selecting the old and new values.

[0062] Table 3 shows an abstract description of the logical plan for the PageRank CTE shown in Table 2. The planner uses this logical plan to generate the actual physical plan for the execution engine, similar to other more traditional SQL structures.

[0063] Table 3

[0064]

[0065]

[0066] In this logical plan, step 1 inserts the non-iterative portion into a temporary result called PageRank. This can be done using a regular WITH SQL object. Step 2 initializes the counter to zero. Step 3 executes the iterative portion, which calculates the page ranking using the sorting from neighboring pages and the join from the edges table. Note that the main table PageRank is both an input and output table. Therefore, the implementation shown in Table 3 uses an intermediate table, Intermediate_Results, to store the values. If the query updates the entire dataset, then step 4 of the example implementation renames the Intermediate_Results table to PageRank, the name of the main table, to avoid unnecessary data movement. The rename operation is a new operation added to the MPPDB to implement the iterative CTE. Step 5 increments the counter by 1. Steps 2 and 5 are new statements added to the execution engine to implement loop functionality. These statements are supported by other parts of the system. Supporting general iterative processing in the MPPDB involves extensions to the parser, rewrite subsystem, planner, and execution engine.

[0067] Figure 2 An example MPPDB 200 is shown that processes SQL queries from client 202. As shown, MPPDB 200 includes a coordinator 204 that is communicatively coupled to client 202 and multiple data nodes 206A-206N. Although Figure 2 Three data nodes are shown, but MPPDBs typically include more. Coordinator 204 (see below) Figure 3 (As described in the example) can be any device configured to process and execute queries on behalf of client 202. Executing such queries may involve developing an execution plan that includes one or more local plan segments that outline the processing flow of data nodes 206A-206N. Data nodes 206A-206N can be any component that has access rights (e.g., exclusive access rights) to partitions of MPPDB 200. Data nodes 206A-206N can be configured to optimize the execution plan and / or the corresponding segments of the execution plan, and then execute the corresponding plan segments using data in their data stores 212A-212N to produce query results.

[0068] Data nodes 206A, 206B, and 206N each include a central processing unit (CPU) 210A, 210B, and 210N. Each CPU is coupled to its respective memory 208A, 208B, and 208N. Data nodes 206A-206N also include data stores 212A, 212B, and 212N, which hold data accessed by the corresponding portion of a query processed by each data node 206A, 206B, and 206N. CPUs 210A, 210B, and 210N may store instructions in their respective memories 208A, 208B, and 208N for processing local plan segments to perform queries using data in segments of the data stores 212A, 212B, and 212N allocated to each CPU 210A, 210B, and 210N. These instructions can further rewrite and / or optimize local plan segments, for example, in response to the cardinality of tables in each data store 212A, 212B, and 212N.

[0069] Figure 3 Example coordinator 204 is shown, illustrating the overall processing flow of SQL queries in the system. For example... Figure 2 As shown, coordinator 204 receives queries from client 202 and provides query execution results to client 202. The example coordinator 204 includes a parser 302, a planner module 304, and an execution engine 316. Planner module 304 includes a rewrite module 306 and a join and aggregation planner 314. Rewrite module 306 implements three types of rewrite operations, each implemented by a different part of rewrite module 306. These parts include a function rewrite part 308, a rule-based optimization part 310, and a cost-based optimization part 312. Execution engine 316 executes the physical plan provided by planner module 304 using data in data stores 212A, 212B, and 212N.

[0070] Parser 302 processes the syntax, semantics, and access permissions of SQL queries. Parser 302 outputs a logical query tree in parse tree format and passes the logical query tree to rewrite module 306 of planner module 304. Rewrite module 306 performs function rewriting and optimization rewriting on the query tree. Function rewriting section 308 converts some operators not supported by the execution engine into other low-level operators. Common examples are view reference expansion (inserting view definitions into the query tree) and converting complex OLAP functions (such as Cube or Rollup) into unions of simple aggregation queries. In the example described below, function rewriting section 308 adds operators to implement termination and loop operations for iterative CTEs. Rule-based optimization section 310 and cost-based optimization section 312 of rewrite module 306 operate on the modified logical plan generated by function rewriting section 308 to generate an optimized query plan tree. Examples of optimization rewriting include predicate pushdown, union simplification, and join elimination.

[0071] The join and aggregation planner 314 transforms the logical tree provided by the rewrite module 306 into a physical tree that includes join sorting and implementation, aggregation methods, and data shuffling decisions. The join and aggregation planner 314 uses LLVM optimization to generate an execution plan and passes the generated execution plan to the execution engine 316. LLVM optimization transforms the query plan provided by the join and aggregation planner 314 into architecture-independent intermediate code, optimizes the intermediate code of the execution engine 316, and compiles the optimized code to generate the execution plan for the execution engine 316. The abbreviation "LLVM" originally stood for Low-Level Virtual Machine. However, LLVM has evolved to such an extent that this meaning is no longer appropriate.

[0072] In the final step of query processing, execution engine 316 performs the actual query execution and returns the results to client 202. This example embodiment extends MPPDB components to handle iterative CTEs. However, these modifications do not introduce significant changes to the codebase, thus ensuring backward compatibility of the modified MPPDB with existing SQL programs.

[0073] Parser 302 is modified to accommodate the new syntax introduced by the iterative CTE, generating new parse tree nodes (which include information about the iterative and non-iterative parts of the query) and extracting information about the termination condition (e.g., loop type and any expressions to be evaluated against the termination condition). Parser 302 performs operations similar to those of the parser described by S. Lloratos et al. in the aforementioned referenced article.

[0074] Modify the functional rewrite section 308 of rewrite module 306 to add rewrite rules that transform the iterative CTE into a lower-level operation. This rule transformation is similar to the functionality described in steps 2, 4, 5, and 6 of Table 2. These modifications will be described in more detail below. Changes to the rule-based optimization section 310 and the cost-based optimization section 312 of rewrite module 306 enable rule-based and cost-based optimizations that can be applied without further modification to the logical query tree generated by the functional rewrite section 308 of rewrite module 306.

[0075] The join and aggregation planner 314 has minor modifications to identify two execution operators (described below) for adapting to loop and rename functions and pass them to the physical plan. The execution engine 316 is extended to implement these execution operators. These modifications are relatively minor and do not significantly affect the execution engine 316 because the rewritten query plan is based on existing RDBMS operators.

[0076] The rewriting module 306's rewriting section 308 expands the parse tree generated by parser 302 to encompass a series of SQL operators covering the non-iterative part, the iterative part, and the loop logic. The loop logic repeats the iterative part of the query until the termination condition T is met. C .

[0077] Figure 4 The parse tree 400 of the iterative CTE PageRank query in Table 2, generated by parser 302, is shown. Figure 4Instead of showing the complete and detailed tree, this section focuses on the main nodes that implement the operators. The root of parse tree 400 is the Select operation 402, which selects rows for the iterative CTE PageRank. Node 404 in parse tree 400 marks the CTE as "iterative" to distinguish it from regular and recursive CTEs. Parser 302 generates separate subtrees for the non-iterative, iterative, and cyclic parts, including termination conditions. The root of the non-iterative subtree is the Union operation 406, which is a union of two Select operations 408 and 412. Select operation 408 extracts SRC nodes from edge table 410, and Select operation 412 selects DST nodes from edge table 410. The root node of the iterative subtree is the Group By operation 416. Grouping operation 416 operates on the result of the first left outer join operation 418, which joins the PageRank table 420 with the result of the second left outer join operation 422, which joins the PageRank table 420 with the SRC nodes of the edge table 410. The loop subtree 428 describes the termination condition based on "UNTIL 10ITERATIONS" in row 17 of Table 2. This statement is parsed to indicate that the loop type is "metadata," the condition (CON) is "iteration," and the number of iterations is 10.

[0078] The rewrite module 306's function rewrite part 308 extends the parse tree 400 to a series of regular SQL operations that implement Table 2 and Figure 4 The iterative CTE is shown. Figure 5 The result of the rewrite is shown, namely logical plan tree 500, which represents the process... Figure 4 The logical query plan generated by the planner module 304 of the MPPDB 200 following the parse tree 400 shown. Note that... Figure 5 The logic plan shown is a simplified version for demonstration purposes. This rewrite will... Figure 4 The node 404 shown is transformed into two materialization operations 502 and 504, and a rename operation 506. The first materialization operation 502 materializes the PageRank table 420 and implements... Figure 4 The non-iterative subtree of parse tree 400 shown initializes the PageRank table to include the SRC and DST nodes of edge table 410. This subtree includes union operation 406, and selection operations 408 and 412, which select SRC and DST nodes from edge table 410, as referenced above. Figure 4 The second materialization operation 504 materializes the intermediate table and includes grouping operation 416 and left outer join operations 418 and 422, which operate on the PageRank table 420 and the edge table 410, as described above. Figure 4 The final operation of the extended node 404 is a renaming operation 506, which renames the intermediate table to the main PageRank table 420. As described below, for queries that do not update the entire dataset, this step can be replaced by an update operation that selects old values ​​from the PageRank table 420 and new values ​​from the intermediate table using columns used as unique row identifiers.

[0079] Node 508 of the logical plan tree 500 implements the iterative CTE loop operation. One element of this implementation is determining when a termination condition is met. As mentioned above, termination conditions can be based on data, metadata, or incremental values. Data-based termination conditions include expressions that can be implemented using SQL. For example, MPPDB can determine the number of tuples in the main CTE table that satisfy the expression after updating the main CTE table or renaming an intermediate table to the main CTE table, and terminate the loop based on that determination. Metadata-based termination can be based on the number of iterations or updates applied to the main CTE table, up to and including the most recent iteration. Incremental (Delta) loop types can be based on incremental values ​​(e.g., comparing the value from the current iteration with the value from the previous iteration). Incremental loops terminate the query by comparing the result of the current iteration with the result of the previous iteration using a user-specified SQL expression.

[0080] The implementation of the iteration part in feature rewriting depends on Figure 4 and Figure 5 The loop type is identified by the parser variable Type. The value of the Type variable is passed to the planner module 304 via a new iterative CTE object. There are two options for implementing the termination logic. First, the infrastructure already exists in the MPPDB to indicate the next operator for a given query plan node in the execution flow. The first option expands this infrastructure to be conditional so that the next operator can be selected from either the beginning of the loop or the end of the query. This option does not introduce new operators but may complicate the execution flow in general, as the execution logic will be modified to always check whether the next operator is conditional.

[0081] One less disruptive approach to implementing iterative CTEs is to introduce loop operators in the explicit conditional execution flow. When the function rewrite section 308 of the rewrite module 306 determines that the parse tree is an iterative CTE, it adds loop operators to the logical plan tree, such as... Figure 5As shown in node 508. Next, the function rewriting section 308 of the rewriting module 306 provides information from the parse tree used for the specified loop type. The new operator captures three pieces of information: 1) the loop type (metadata, data, or incremental), 2) the number of iterations in the iteration options or the expression update in the update options, with an indicator distinguishing between the two options, and 3) the SQL expression used for data and incremental loop types. As described by S. Floratos et al. in the aforementioned reference, the new operator also captures an additional indicator that can accommodate arbitrary keywords. Figure 5 The PageRank CTE plan tree shown explicitly uses 10 iterations for the query, therefore the termination condition is based on metadata. The loop operators in the logical query plan tree are populated with <<Type: metadata, N: 10, expression: none>>, as shown in node 508.

[0082] The final change implemented in the rewritten part 308 of the rewritten module 306 is the addition of an operation to initialize the loop operator immediately after executing the non-iterative part and update the loop operator at the end of each iteration. For the simple case of PageRank iterating CTE, MPPDB executes R... I The new counter is started (step ID 2 in Table 3), and then the counter is incremented (step ID 5 in Table 3) before checking if another iteration is needed (step ID 6 in Table 3).

[0083] Figure 6 The general algorithm 600 used by the MPPDB planner is illustrated, which describes the functional rewriting of the iterative CTE. This algorithm is not limited to PR calculation and provides a framework for rewriting the plan tree for any query implemented using the iterative CTE. Operation 602 materializes the non-iterative portion R0 of the CTE into the master table. Operation 604 initializes the loop operator. This operation has different functions depending on the type of the loop operator. When the type is "metadata", operation 604 initializes a counter to calculate the number of iterations. When the type is "data", operation 604 initializes the loop operator with the function to be evaluated to determine whether the termination condition T is met. C .

[0084] Operation 606 deletes data from all rows of the worksheet, and operation 608 materializes the worksheet into an R object. IThis refers to the iterative portion of the CTE. This operation can include SQL delete operations. Operation 610 is executed at the end of each iteration and determines how updates to the main table occur. As mentioned above, when operation 608 updates the entire worksheet, the main table is updated by switching between the main table and the temporary table, which can be achieved through operation 612, which swaps storage space (e.g., memory and disk storage) between the temporary table and the main table. This operation effectively renames the temporary table to the main table and the main table to the temporary table. Operation 612 can also delete any data from the rows of the worksheet. In this instance, operation 606 can be omitted or performed only once before the data is first materialized into the worksheet. Alternatively, when operation 610 determines that operation 608 modifies only a subset of the rows of the worksheet, operation 614 performs multiple update operations to copy rows from the worksheet to the main table, where the copied rows are identified by the worksheet key. For example, when operation 610 detects R I When using the WHERE clause in the table, operation 610 determines that operation 608 only modifies a subset of the worksheet.

[0085] Following operation 612 or 614, operation 616 updates the loop operator using data from the current iteration. Similar to operation 604, operation 616 performs different functions based on the type of the loop. When the type is metadata, operation 616 increments the counter; when the type is data, operation 616 evaluates the function; when the type is increment, operation 616 updates the loop operator based on the difference between the result of the current iteration and the result of the previous iteration, according to the user-specified SQL expression.

[0086] When the loop type is incremental, the result of the previous iteration can be stored in the incremental table. The way the previous result is stored in the incremental table differs depending on whether the previous iteration performed operation 612 or operation 614. When operation 612 of the previous iteration swapped the main table and the worksheet, the current iteration stores the renamed main table in the incremental table at the start of the current iteration or the end of the previous iteration. When operation 614 of the previous iteration updated the main table from the worksheet, the contents of the main table and the worksheet were identical before operation 606 of the current iteration. In this instance, algorithm 600 renames the worksheet to the incremental table, and operation 606 generates a new empty worksheet. Alternatively, the current iteration can compare the main table and the worksheet to update the loop operator (operation 616) before swapping the main table with the worksheet in operation 612 or updating the main table from the worksheet in operation 614. This avoids storing a separate incremental table.

[0087] After operation 616, operation 618 determines whether the termination condition T is met. C When T is not satisfied C When T is satisfied, operation 618 branches to operation 606 to begin the next iteration. CAt that time, operation 620 returns the result Q of the iterated CTE. F As Figure 6 An alternative to the method shown is that loop operations 616, 618, and 620 can be moved before operation 606, so that operation 616 updates the loop operator with data from the previous iteration, and when T is not satisfied... C Then, continue with operation 606. In this alternative method, an unconditional branch operation (not shown) is inserted after operations 612 and 614 into the moved operation 616.

[0088] Figure 7 This is a block diagram of a computing device 700 according to an embodiment. Similar components may be used in the example computing device described herein. For example, clients, servers, and network resources may each be used. Figure 7 Different sets of components shown and / or Figure 7 The computing components are not shown. This can be implemented using a computing device similar to computing device 700. Figure 2 The client 202, coordinator 204, and data nodes 206A-206N are shown. Figure 3 The coordinator shown; implementation Figure 6 The method shown; and processes the queries described in Tables 2 and 3 to generate Figure 4 and Figure 5 The flow tree shown.

[0089] An example computing device 700 may include a processing unit (e.g., one or more processors and / or CPUs) 702, a memory 703, removable storage 710, and non-removable storage 712, all communicatively coupled via a bus 701. Although various data storage elements are shown as part of the computing device 700, the removable storage 710 may also, or alternatively, include memory in one of the data storage elements 212A-212N, such as… Figure 2 As shown, it is coupled to the processing unit 702 via a high-speed data interface (not shown).

[0090] Memory 703 may include volatile memory 714 and non-volatile memory 708. Memory 703 may also include application 718, as shown in the reference above. Figure 2 and 3The coordinator 204 is described above. The computing device 700 may include or access a computing environment comprising various computer-readable media, such as volatile memory 714 and non-volatile memory 708, removable storage 710, and non-removable storage 712. Computer storage includes random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other storage technologies, compact disc read-only memory (CD-ROM), digital versatile disk (DVD) or other optical disc storage devices, magnetic tape, magnetic tape, disk storage or other magnetic storage devices, or any other medium capable of storing computer-readable instructions.

[0091] Computing device 700 may include or access a computing environment including input interface 706, output interface 704, and communication interface 716. Output interface 704 may provide an interface to a display device such as a touchscreen (which may also be used as an input device). Input interface 706 may provide an interface to one or more of a touchscreen, touchpad, mouse, keyboard, camera, one or more device-specific buttons, one or more sensors integrated into or coupled to server computing device 700 via wired or wireless data connections, and / or other input devices. Computing device 700 may operate in a network environment using communication interface 716. Communication interface may include access to a local area network (LAN), wide area network (WAN), cellular network, WLAN network, and / or Bluetooth. One or more interfaces of a network.

[0092] In an example embodiment, computing device 700 includes: a query receiving module that receives a first iterative query having a first non-iterative portion defining a first master table and a first iterative portion generating values ​​in rows of a first worksheet based on values ​​in rows of the first master table; a modification determining module that determines that the first iterative portion modifies all rows of the first worksheet; a renaming module that adds a renaming operation to rename the first worksheet to the name of the first master table to produce a new first master table, and renames the first master table to the name of the first worksheet to produce a new first worksheet; a deletion module that adds a first deletion operation to delete each row of the new first worksheet; and a looping module that adds a first looping operation to repeat the first iterative portion until a first termination condition is met. In some embodiments, computing device 700 may include other or additional modules for performing any or a combination of the steps described in the embodiments. Furthermore, as shown in any of the drawings or described in any of the claims, any additional or alternative embodiments or aspects of the method are also contemplated to include similar modules.

[0093] Any one or more modules described herein can be implemented using hardware such as a machine's processor, application-specific integrated circuit (ASIC), field-programmable gate array (FPGA), or any suitable combination thereof. Furthermore, any two or more of these modules can be combined into a single module, and the functionality described herein for a single module can be subdivided across multiple modules. Additionally, according to various example embodiments, modules implemented within a single machine, database, or device as described herein can be distributed across multiple machines, databases, or devices. As described herein, modules can include one or both of hardware or software designed to perform one or more functions (e.g., one or more functions described herein related to providing secure and reliable data access).

[0094] While some embodiments have been described in detail above, other modifications are possible. For example, the logic flow depicted in the figures does not require the specific order shown or the sequence in which the desired result is achieved. Additional steps may be provided or eliminated from the described flow, and other components may be added to or removed from the described system. Other embodiments are within the scope of the following claims.

[0095] It should be further understood that software comprising one or more computer-executable instructions may be installed in and provided with one or more computing devices consistent with this disclosure, such computer-executable instructions facilitating the processing and operation of any or all of the steps referred to in this disclosure. Alternatively, the software may be obtained and loaded into one or more computing devices, including obtaining the software via physical media or a distribution system, including, for example, obtaining the software from a server owned by the software creator or from a server not owned by the software creator but used by the software creator. For example, the software may be stored on a server for distribution over the Internet.

[0096] Furthermore, those skilled in the art will understand that this disclosure is not limited to its application to the details of the construction and component arrangement described in the specification or illustrated in the drawings. The embodiments described herein can be adapted to other embodiments and can be practiced or performed in various ways. Furthermore, it should be understood that the wording and terminology used herein are for descriptive purposes and should not be considered limiting. The terms “comprising,” “including,” or “having,” and variations thereof, as used herein, are intended to cover the items listed thereafter and their equivalents, as well as additional items. Unless otherwise limited, the terms “connection,” “coupling,” and “mounting,” and variations thereof, are used broadly herein and include direct and indirect connections, couplings, and mountings. Moreover, the terms “connection” and “coupling,” and variations thereof, are not limited to physical or mechanical connections or couplings.

[0097] The components of the illustrative devices, systems, and methods used according to the illustrated embodiments may be implemented at least in part in digital electronic circuits or computer hardware, firmware, software, or combinations thereof. For example, these components may be implemented as computer program products, such as computer programs, program code, or computer instructions tangibly embodied in an information carrier or machine-readable storage device, for execution or control of their operation by a data processing apparatus, such as a programmable processor, computer, or multiple computers.

[0098] Computer programs can be written in any programming language, including compiled or interpreted languages, and can be deployed in any form, including as standalone programs or as modules, components, subroutines, methods, objects, or other units suitable for use in a computing environment. Computer programs can be deployed to execute on a single computer or multiple computers at a single site, or they can be distributed across multiple sites and interconnected via a communication network. The method steps associated with the illustrative embodiments can be executed by one or more programmable processors that execute computer programs, code, or instructions to perform functions (e.g., by manipulating input data and / or generating output). The method steps can also be executed by dedicated logic circuitry, and the means for performing these methods can be implemented as dedicated logic circuitry, such as FPGAs (Field-Programmable Gate Arrays) or ASICs (Application-Specific Integrated Circuits).

[0099] The various illustrative logic blocks, modules, and circuits described in conjunction with the embodiments disclosed herein can be implemented or executed using a general-purpose processor, digital signal processor (DSP), ASIC, FPGA, or other programmable logic device, discrete gate or transistor logic, discrete hardware components, or any combination thereof designed to perform the functions described herein. The general-purpose processor can be a single-core or multi-core microprocessor, but alternatively, the processor can be any conventional processor, controller, microcontroller, or state machine. The processor can also be implemented as a combination of computing devices, such as a combination of a DSP and a microprocessor, multiple microprocessors, one or more microprocessors combined with a DSP core, or any other such configuration.

[0100] For example, processors suitable for executing computer programs include general-purpose and special-purpose microprocessors, as well as any one or more processors in any type of digital computer. Typically, a processor receives instructions and data from read-only memory or random access memory, or both. The components of a computer include a processor for executing instructions and one or more storage devices for storing instructions and data. Typically, a computer will also include one or more mass storage devices for storing data, such as magnetic disks, magneto-optical disks, or optical disks, or operatively coupled to receive data from or transfer data to one or more mass storage devices, or both. Information carriers suitable for embodying computer program instructions and data include all forms of non-volatile memory, such as semiconductor memory devices, such as electrically programmable read-only memory or ROM (EPROM), electrically erasable programmable ROM (EEPROM), flash memory devices, and data storage disks (e.g., magnetic disks, internal hard disks or removable disks, magneto-optical disks, and CD-ROM and DVD-ROM disks). Processors and memory may be supplemented by or integrated into special-purpose logic circuitry.

[0101] Those skilled in the art will understand that information and signals can be represented using any of a variety of different technologies and techniques. For example, data, instructions, commands, information, signals, bits, symbols, and chips referenced throughout the foregoing description can be represented by voltage, current, electromagnetic waves, magnetic fields or particles, light fields or particles, or any combination thereof.

[0102] As used herein, a “machine-readable medium” (or “computer-readable storage medium”) means a device capable of temporarily or permanently storing instructions and data, and may include, but is not limited to, random access memory (RAM), read-only memory (ROM), buffer memory, flash memory, optical media, magnetic media, cache memory, other types of memory (e.g., erasable programmable read-only memory (EEPROM)) and / or any suitable combination thereof. The term “machine-readable medium” should be understood to include a single medium or multiple media capable of storing processor instructions (e.g., a centralized or distributed database, or associated caches and servers). A machine-readable medium or computer-readable storage medium should also be understood to include any medium (or combination of media) capable of storing instructions for execution by one or more processors, such that, when executed by one or more processors, the instructions cause one or more processors to perform any or more methods described herein. Therefore, a machine-readable medium or computer-readable storage medium refers to a single storage device or apparatus, and a “cloud-based” storage system or storage network comprising multiple storage devices or apparatuses. The terms “machine-readable medium” and “computer-readable storage medium” as used herein do not include the signal itself.

[0103] Furthermore, the technologies, systems, subsystems, and methods described and illustrated as discrete or independent in the various embodiments may be combined or integrated with other systems, modules, technologies, or methods without departing from the scope of this disclosure. Other examples shown or discussed as mutually coupled or directly coupled or communicating may be indirectly coupled or communicating through some interface, device, or intermediate component, whether electrically, mechanically, or otherwise. Those skilled in the art can identify other examples of changes, substitutions, and modifications, which may be made without departing from the scope of this disclosure.

[0104] Although this disclosure has been described with reference to its specific features and embodiments, it will be apparent that various modifications and combinations can be made therein without departing from the scope of this disclosure. For example, other components may be added to or removed from the described methods, modules, devices, and / or systems. Therefore, the specification and drawings are simply to be regarded as a description of this disclosure as defined by the appended claims, and are intended to cover any and all modifications, variations, combinations, or equivalents falling within the scope of this disclosure. Other aspects may be within the scope of the following claims.

Claims

1. A method for functionally rewriting iterative queries in a relational database management system (RDBMS), comprising: Receive a first iterative query, the first iterative query having a first non-iterative part that defines a first main table and a first iterative part that generates values ​​in rows of a first worksheet based on values ​​in rows of the first main table; It was determined that the first iteration modified all rows in the first worksheet; as well as Rewrite the first iteration, including: Add a rename operation to rename the first worksheet to the new first master table, and then rename the first master table to the new first worksheet. Add a first delete operation to delete every row in the new first worksheet; and Add a first loop operation to repeat the first iteration portion until the first termination condition is met.

2. The method according to claim 1, wherein: The addition of the first loop operation includes adding a first comparison operation and a first branch operation as the last operation in the first iteration part; The first comparison operation is configured to test the first termination condition; as well as The first branch operation is configured as follows: When the first comparison operation determines that the first termination condition is not met, it conditionally branches to the first operation in the first iteration portion; as well as When the first comparison operation determines that the first termination condition is met, the first iteration portion is terminated.

3. The method of claim 2, wherein rewriting the first iteration portion includes adding the rename operation and the first deletion operation immediately preceding the first comparison operation.

4. The method according to claim 1, further comprising: Receive a second iterative query, the second iterative query including a second non-iterative part that defines a second main table and a second iterative part that generates values ​​in rows of a second work table based on values ​​in rows of the second main table; Determine that the number of rows modified in the second iteration is less than the total number of rows in the second worksheet; as well as Rewrite the second iteration, including: Add an update operation to replace the corresponding row in the second master table with the modified row from the second worksheet; as well as Add a second delete operation to delete each modified row in the second worksheet.

5. The method according to claim 4, further comprising: Add a second comparison operation and a second branch operation as the last operations in the second iteration, where: The second comparison operation is configured to test the second termination condition; The second branch operation is configured as follows: When the second comparison operation determines that the second termination condition is not met, a conditional branch is established to the first operation in the second iteration portion; and When the second comparison operation determines that the second termination condition is met, the second iteration portion is terminated; and The rewriting of the second iteration includes adding the update operation and the second deletion operation immediately before the second comparison operation.

6. The method according to claim 2 or 3, wherein: The first termination condition includes the number of iterations performed by the first iteration portion; The method also includes rewriting the first non-iterative portion to add an initialization counter; as well as The addition of the first comparison operation includes adding an operation that compares the value of the counter with the number of iterations.

7. The method according to claim 2 or 3, wherein: The first termination condition includes the expression evaluated by the first iteration portion; and The addition of the first comparison operation includes adding an operation for evaluating the expression to determine whether the first termination condition is met.

8. The method according to claim 2 or 3, wherein: The first termination condition includes a measurement of the difference between a first value of the target entry from the first main table in the previous iteration and a second value of the target entry from the first main table in the current iteration. The rewriting of the first iteration portion also includes adding an operator to store the first value of the target entry from the first main table as the first operation of the first iteration portion; as well as The addition of the first comparison operation includes an addition operator to determine whether the difference between the second value of the target entry from the first master table and the first value of the stored target entry is less than the difference measurement, wherein the first comparison operation determines whether the first termination condition is met.

9. An apparatus for functionally rewriting iterative queries in a relational database management system (RDBMS), comprising: Memory, including instructions; as well as One or more processors communicating with the memory, wherein the one or more processors execute the instructions to perform an operation, including: Receive a first iterative query, the first iterative query having a first non-iterative part that defines a first main table and a first iterative part that generates values ​​in rows of a first worksheet based on values ​​in rows of the first main table; It was determined that the first iteration modified all rows of the first worksheet; and Rewrite the first iteration, including: Add a rename operation to rename the first worksheet to create a new first master table, and rename the first master table to create a new first worksheet. Add a first delete operation to delete every row in the new first worksheet; and Add a first loop operation to repeat the first iteration portion until the first termination condition is met.

10. The apparatus of claim 9, wherein adding the first loop operation comprises adding a first comparison operation and a first branch operation as the last operation in the first iteration portion, wherein: The first comparison operation is configured to test the first termination condition; as well as The first branch operation is configured as follows: When the first comparison operation determines that the first termination condition is not met, it conditionally branches to the first operation in the first iteration portion; as well as When the first comparison operation determines that the first termination condition is met, the first iteration portion is terminated.

11. The apparatus of claim 10, wherein the operation of rewriting the first iteration portion includes adding the renaming operation and the first deletion operation immediately preceding the first comparison operation.

12. The apparatus of claim 9, wherein the operation further comprises: Receive a second iterative query, the second iterative query including a second non-iterative part that defines a second main table and a second iterative part that generates values ​​in rows of a second work table based on values ​​in rows of the second main table; Determine that the number of rows modified in the second iteration is less than the total number of rows in the second worksheet; as well as Rewrite the second iteration, including: Add an update operation to replace the corresponding row in the second master table with the modified row from the second worksheet; as well as Add a second delete operation to delete each modified row in the second worksheet.

13. The apparatus according to claim 12, wherein: The operation further includes adding a second loop operation to repeat the second iteration portion until the second termination condition is met; and The rewriting of the second iteration portion includes adding the update operation and the second deletion operation immediately before the second loop operation.

14. The apparatus according to claim 10 or 11, wherein: The first termination condition includes the number of iterations performed by the first iteration portion, and the operation further includes rewriting the first non-iteration portion to add an initialization counter; as well as The addition of the first comparison operation includes adding an operation that compares the value of the counter with the number of iterations.

15. The apparatus according to claim 10 or 11, wherein: The first termination condition includes the expression evaluated by the first iteration portion; and The addition of the first comparison operation includes adding an operation for evaluating the expression to determine whether the first termination condition is met.

16. The apparatus according to claim 10 or 11, wherein: The first termination condition includes a measurement of the difference between the first value of the target entry in the first master table in the previous iteration and the second value of the target entry from the first master table in the current iteration. The rewriting of the first iteration portion also includes adding an operator to store the first value of the target entry from the first main table as the first operation of the first iteration portion; as well as The addition of the first comparison operation includes an addition operator to determine whether the difference between the second value of the target entry from the first master table and the first value of the stored target entry is less than the difference measurement, wherein the first comparison operation determines whether the first termination condition is met.

17. A computer-readable storage medium storing computer instructions for functionally rewriting iterative queries of a relational database management system (RDBMS), the computer instructions, when executed by one or more processors, causing the one or more processors to perform operations, including: Receive a first iterative query, the first iterative query having a first non-iterative part that defines a first main table and a first iterative part that generates values ​​in rows of a first worksheet based on values ​​in rows of the first main table; It was determined that the first iteration modified all rows in the first worksheet; as well as Rewrite the first iteration, including: Add a rename operation to rename the first worksheet to create a new first master table, and rename the first master table to create a new first worksheet. Add a first delete operation to delete every row in the new first worksheet; and Add a first loop operation to repeat the first iteration portion until the first termination condition is met.

18. The computer-readable storage medium according to claim 17, wherein: The addition of the first loop operation includes adding a first comparison operation and a first branch operation as the last operation in the first iteration portion; and The first comparison operation is configured as follows: Test the first termination condition; and The first branch operation is configured as follows: When the first comparison operation determines that the first termination condition is not met, it conditionally branches to the first operation in the first iteration portion; as well as When the first comparison operation determines that the first termination condition is met, the first iteration portion is terminated.

19. The computer-readable storage medium of claim 17, wherein the operation further comprises: Receive a second iterative query, the second iterative query including a second non-iterative part that defines a second main table and a second iterative part that generates values ​​in rows of a second work table based on values ​​in rows of the second main table; Determine that the number of rows modified in the second iteration is less than the total number of rows in the second worksheet; as well as Rewrite the second iteration, including: Add an update operation to replace the corresponding row in the second master table with the modified row from the second worksheet; as well as Add a second delete operation to delete each modified row in the second worksheet.

20. The computer-readable storage medium of claim 19, wherein the operation further comprises: Add a second comparison operation and a second branch operation as the last operations in the second iteration, where: The second comparison operation is configured to test the second termination condition; The second branch operation is configured as follows: When the second comparison operation determines that the second termination condition is not met, a conditional branch is established to the first operation in the second iteration portion; and When the second comparison operation determines that the second termination condition is met, the second iteration portion is terminated; and The rewriting of the second iteration includes adding the update operation and the second deletion operation immediately before the second comparison operation.

Images