Query processing using logical query steps in canonical form

Abstract

A query processing device comprising: a communication interface for accessing a database and a database directory; the memory is used for storing instructions; and a processor coupled to the memory and the communication interface. The processor executes the instructions to: parse a query to generate first and second execution plans for the query; retrieving, from the database directory, respective previously determined radix values for previously executed logical steps of the first and second execution plans; selecting an execution plan from the first execution plan or the second execution plan, the selected execution plan having a lower cost based on the previously determined cardinal value; and executing the selected execution plan on data accessed from the database. The query processing system stores an actual radix value determined during execution of the logical step in the database directory for use by subsequent queries. Accordingly, the query processing apparatus may reuse a previously determined cardinal value.

Description

[0001] This application is a divisional application. The application number of the original application is 202080023252.X, and the original application date is March 19, 2020. The entire contents of the original application are incorporated into this application by reference. Technical Field

[0002] The present invention relates to query processing in a database management system (DBMS), and more particularly to a DBMS that parses queries into logical steps having a canonical form and stored cardinality information. Background Art

[0003] Database query optimization methods use cardinality and data size estimates to formulate better queries in cost-based query optimization systems. Cardinality is a measure of the uniqueness of data values ​​in a particular column of a database. A small cardinality value in that column may indicate a large number of repeated elements in the column. Cardinality estimates include the number of rows and the number of distinct values ​​for base tables (e.g., database columns) and intermediate results (e.g., intermediate data produced by operations on base tables). The amount of output data for each operation performed is also a cardinality value that can affect performance. The number of rows, the number of distinct values, and the size of the data play an important role in operations such as join ordering, selecting the type of join method, and selecting the type of aggregation method to use in the execution plan for a particular query.

[0004] For example, DBMS employs two types of join algorithms: nested loop join algorithm and sort-merge join algorithm. For the example join operation JOIN(A,B), the nested loop algorithm compares each record in table A with each record in table B to generate the joined table, while the sort-merge join algorithm sorts table A and table B separately and combines the sorted tables to generate the joined table. The nested loop algorithm is more efficient for relatively small tables, while the sort-merge algorithm is more efficient for relatively large tables. Therefore, the query optimizer of the DBMS benefits from knowing the cardinality of the tables to be joined. Summary of the invention

[0005] The DBMS parses the query to generate an execution plan. In the example described below, the execution plan is a combination of logical steps that are combined to implement a database query. A logical step is a sub-part of a query that acts on one or more database columns to produce an intermediate result. The results of multiple logical steps can be combined in other logical steps to execute the complete query. In the execution plan described below, each logical step has a canonical form with fully qualified column names, and the column names are arranged in a predetermined order (e.g., alphabetical order) when possible. After the execution plan, the DBMS stores the execution plan and statistics of the logical steps that make up the execution plan in the database directory. The stored statistics are indexed by corresponding hash values ​​derived from the canonical form of the steps. The query optimizer of the DBMS accesses these stored statistics when processing a query that occurs later and contains one or more of the same logical steps to select one or more queries. The statistics are accessed using hash values ​​generated from the canonical form steps of the execution plan of the query that occurs later.

[0006] These examples are contained in the features of the independent claims. Further embodiments are evident from the dependent claims, the detailed description and the drawings.

[0007] According to a first aspect, a query processing device comprises: a communication interface for accessing a database and a database directory; a memory for storing instructions; and a processor coupled to the memory and the communication interface. The processor executes the following instructions: parsing a query to generate a first and a second execution plan for the query, each of the first and the second execution plan comprising one or more logical steps; retrieving corresponding previously determined cardinality values ​​of previously executed logical steps of the first and the second execution plans from the database directory; selecting an execution plan from the first execution plan or the second execution plan, the selected execution plan having a lower cost based on the previously determined cardinality values; and executing the selected execution plan on data accessed from the database through the communication interface.

[0008] In a first implementation of the device according to the first aspect, the processor is configured to generate the logic steps in a corresponding canonical form having a defined syntax and including a corresponding source name.

[0009] In a second implementation of the device according to the first aspect, the processor is configured to retrieve previously determined cardinality values ​​of the first and second execution plans.

[0010] In a third implementation of the device according to the first aspect, the processor is configured to fully qualify each source name of each logic step in each of the first and second execution plans. The processor is further configured to determine that a first logic step of the one or more logic steps has a plurality of source names and is interchangeable and to arrange the plurality of source names in a predetermined order in the first logic step.

[0011] In a fourth implementation of the device according to the first aspect, the processor is used to determine that the first logical step is used for an operation, and the operation includes: Inner Join, Full Join, Multi-Way Join, Union or Intersect.

[0012] In a fifth implementation of the device according to the first aspect, the processor is configured to calculate a corresponding hash value for each logical step of the first and second execution plans. The processor is further configured to access the database directory based on the corresponding hash value to retrieve the corresponding previously determined cardinality value of the logical step of the first and second execution plans.

[0013] In a sixth implementation of the device according to the first aspect, the one or more logical steps include a structured query language (SQL) operation, and the operation includes at least one of a Scan operation, a Join operation, an Aggregate Scan By operation, a Union operation, or an Intersect operation.

[0014] In a seventh implementation of the device according to the first aspect, the Join operation includes at least one of a SingleJoin operation, a Multi-Way Join operation, a Left Outer Join operation, a Semi-Join operation, an Anti-Join operation, and a Full Outer Join operation.

[0015] In an eighth implementation of the device according to the first aspect, the processor is used to execute each logical step of the selected execution plan. The processor is also used to obtain a corresponding actual cardinality value for each executed logical step and to obtain the corresponding hash value for each executed logical step. The processor is used to store the corresponding actual cardinality value in the database directory indexed by the obtained corresponding hash value.

[0016] In a ninth implementation of the device according to the first aspect, the processor is further used to estimate the cardinality value of each logical step in each execution plan that does not have a previously determined cardinality value in the database directory, and select the one with a lower cost among the first execution plan or the second execution plan based on the retrieved previously determined cardinality value and the estimated cardinality value.

[0017] According to a second aspect, a method for processing a query is provided, for parsing a query to generate first and second execution plans for the query, each of the first and second execution plans comprising one or more logic steps. The method retrieves corresponding previously determined cardinality values ​​of previously executed logic steps of the first and second execution plans, the method selects one of the first execution plan or the second execution plan having a lower cost, and executes the selected execution plan on data in a database.

[0018] In a first implementation of the method according to the second aspect, parsing the query comprises generating the logical steps in a corresponding canonical form having a defined syntax and including a corresponding source name.

[0019] In a second implementation of the method according to the second aspect, retrieving the previously determined cardinality values ​​of the previously executed logical steps of the first and second execution plans also includes retrieving the previously determined cardinality values ​​of the first and second execution plans.

[0020] In a third implementation of the method according to the second aspect, parsing the query includes fully qualifying each source name of each logic step in each of the first and second execution plans. The method also includes determining that a first logic step of the one or more logic steps has multiple source names and is interchangeable and arranging the multiple source names in a predetermined order in the first logic step.

[0021] In a fourth implementation of the method according to the second aspect, determining that the first logical step is interchangeable includes determining that the first logical step is used for an operation, and the operation includes: Inner Join, FullJoin, Multi-Way Join, Union or Intersect.

[0022] In a fifth implementation of the method according to the second aspect, the method also includes calculating a corresponding hash value for each logical step of the first and second execution plans, and accessing a database directory based on the corresponding hash value to retrieve the corresponding previously determined cardinality values ​​of the logical steps of the first and second execution plans.

[0023] In a sixth implementation of the method according to the second aspect, parsing the query includes parsing a structured query language (SQL) query into operations, the operations including at least one of a Scan operation, a Join operation, an Aggregate Scan By operation, a Union operation, or an Intersect operation.

[0024] In a seventh implementation of the method according to the second aspect, executing the selected execution plan on the data in the database includes executing each logical step of the selected execution plan. The method further includes obtaining a corresponding actual cardinality value for each executed logical step and obtaining the corresponding hash value for each executed logical step. The method further includes storing the corresponding actual cardinality value in a database directory indexed by the obtained corresponding hash value.

[0025] In an eighth implementation of the method according to the second aspect, the selecting one of the first execution plan or the second execution plan based on the retrieved previously determined cardinality value further comprises estimating a cardinality value of each logical step in each execution plan that does not have a previously determined cardinality value in a database catalog. The method further comprises selecting the one of the first execution plan or the second execution plan having a lower cost based on the retrieved previously determined cardinality value and the estimated cardinality value.

[0026] According to a third aspect, a non-transitory computer-readable medium stores instructions, which, when executed by one or more processors, cause the one or more processors to perform the following steps: parsing a query to generate a first and a second execution plan for the query, each of the first and second execution plans including one or more logical steps; retrieving corresponding previously determined cardinality values ​​of previously executed logical steps of the first and second execution plans; selecting an execution plan from the first execution plan or the second execution plan, the selected execution plan having a lower cost based on the previously determined cardinality value; and executing the selected execution plan on data in a database.

[0027] According to a fourth aspect, a query processing device comprises: a communication interface for accessing a database and a database directory; an execution plan method for parsing a query and generating a first and a second execution plan for the query, each of the first and the second execution plan comprising one or more logical steps; a cardinality method for retrieving corresponding previously determined cardinality values ​​of previously executed logical steps of the first and the second execution plans from the database directory; a selection method for selecting an execution plan from the first execution plan or the second execution plan, the selected execution plan having a lower cost based on the previously determined cardinality value; and an execution method for executing the selected execution plan on data accessed from the database through the database interface. BRIEF DESCRIPTION OF THE DRAWINGS

[0028] Figure 1 is a block diagram of a system for processing database queries provided by an example embodiment.

[0029] Figure 2 The following is a flowchart of a query processing method provided by an exemplary embodiment.

[0030] Figure 3 is a flowchart of a query processing method provided by another exemplary embodiment.

[0031] Figure 4 is a block diagram of a computing device for performing query processing provided by an example embodiment. DETAILED DESCRIPTION

[0032] The following is a detailed description in conjunction with the accompanying drawings, which are part of the description and show by way of illustration specific embodiments in which the invention may be implemented. These embodiments will be described in sufficient detail to enable those skilled in the art to practice the disclosed embodiments, and it should be understood that other embodiments may be used and structural, logical, and electrical changes may be made without departing from the scope of the appended claims. Therefore, the following description of the exemplary embodiments should not be construed as limiting the appended claims.

[0033] Execution plans generated by a cost-based query optimizer may be sensitive to the accuracy of cardinality and data size estimates. A cost-based query optimizer may select an execution plan for a query from multiple execution plans as the execution plan with the lowest cost (e.g., lowest response time, lowest CPU and / or I / O processing cost, and / or lowest network processing cost). These costs are significantly affected by the amount of data being processed (data size) and the number of distinct data values ​​being processed (cardinality).

[0034] Cardinality estimates can exhibit considerable variation and may overestimate or underestimate the true cardinality and data size values. Many relational DBMSs use the ANALYZE command to collect cardinality and data size values. The ANALYZE command generates statistics for a table or a single column of a table. In addition to the total number of values, the ANALYZE command can return other statistics, such as a breakdown of the number of distinct entries in a table or column. Running the ANALYZE command can be expensive, especially when run on large data sets. Therefore, statistics generated by a single call to the ANALYZE command can be stored and used for subsequent operations on the table or column. However, after a table or column has undergone many inserts, deletes, and updates, these statistics can become out-of-date, requiring the database administrator to rerun the ANALYZE command to refresh the statistics. As an alternative to using the ANALYZE command, the DBMS can obtain cardinality data from the table's histogram.

[0035] Related columns referenced in predicates or join conditions can also cause errors in cardinality estimates. For example, consider the following query:

[0036] SELECT customer_id,purchase_price FROM car_sales WHERE Maker='Honda'AND Model='Accord'

[0037] In this query, "Maker" and "Model" are separate columns of the table. However, these columns are likely to be highly correlated because the model names used by each car manufacturer are typically unique to that car manufacturer.

[0038] Another possible source of cardinality estimation error is expressions containing "regular expressions." As used herein, a "regular expression" is a sequence of characters that defines a search pattern. Examples of regular expressions include the following:

[0039] WHERE product_name LIKE'%green%'or

[0040] WHERE o_comment NOT LIKE'%special%requests%'

[0041] The first expression searches the Product Name field of the database for names containing the characters "green". The second expression searches the Other Notes field of the database for notes that are not special requests. Because the results of these searches are unknown, it is difficult to estimate the size of the data that you will get using the expressions.

[0042] The query optimizer in the DBMS attempts to select the best execution plan from many different execution plans. As described above, the query optimizer uses cardinality estimation to select the best plan. The query optimizer provided by the example embodiment solves the above problem by breaking down each complex query into multiple steps and capturing the actual cardinality of the complex query and its constituent steps when executing the query. These cardinality values ​​are formatted and stored so that they can be used to select the best query plan from a set of later query plans that include the same query and / or steps.

[0043] The embodiment described below reuses the statistics of previous query plan executions to obtain cardinality information for future queries. The solution includes a producer side and a consumer side. The producer side captures the cardinality statistics of the actual execution, and the execution engine saves this data to the catalog table of the DBMS. The consumer side is the cardinality estimation component of the optimizer, which obtains the cardinality information from the catalog and uses it to select the best execution plan for the received query.

[0044] The apparatus and method may reuse previously determined cardinality values, increasing the ability to select an optimal execution plan. The apparatus and method may be used for query optimization. The apparatus and method may be used to select an execution plan. The apparatus and method may be used to select an execution plan with the lowest cost. The apparatus and method may be used to reuse previously generated cardinality values ​​rather than using cardinality estimates. The apparatus and method may be used to improve database query performance through improved statistical estimates. The apparatus and method may be used to improve database query performance by reusing previous cardinality values ​​rather than relying on estimates of cardinality values. The apparatus and method may be used to maximize the reusability of previously executed query statistics.

[0045] Figure 11 is a block diagram of a system 100 for processing database queries provided by an example embodiment. The system 100 in the illustrated embodiment includes a query source 102 and / or receives a database query. In the examples described below, the query may include information obtained from a database column, a set of input values, or include intermediate results generated by previous logical steps. The examples described below use structured query language (SQL) queries, but other query languages ​​may also be considered. The system 100 in the illustrated embodiment includes a query processor 104, a query optimizer 110 including a cost-based (physical layer) optimizer 120, a communication interface 132 for communicating with a database 130, a database directory 140, and an execution engine 150. In some embodiments, the system 100 includes a database management system (DBMS) technology. The communication interface 132 may communicate with the database 130 and the database directory 140 at the same time or with one of the two. In some embodiments, the query processor 104 parses the received query into multiple logical steps to generate a query tree 112. The query tree includes a root logic step, one or more sub-logic steps, and one or more leaf logic steps that have no sub-logic steps. Each logic step has a canonical form, follows a defined syntax, and has a fully qualified source name arranged in a predetermined order. The logic steps generated from the example queries described below are canonical because the logic steps have a syntax defined by rules so that two queries including the same logic step generate the same textual representation of the logic step. Although the embodiments described below process SQL queries, it is contemplated that other embodiments may process other types of database queries.

[0046] The query optimizer 110 processes the logical steps in the query tree to generate one or more query execution plans 126 for execution by the execution engine 150, such as a first execution plan and a second execution plan. For simplicity, the first execution plan and the second execution plan will be described below, but it should be understood that any number of query execution plans can be generated. In order to generate one or more query execution plans 126, the query optimizer 110 and the cost-based optimizer 120 can access the database catalog 140 and / or receive information therefrom. In an example embodiment, the database table searched according to the query resides in the database 130.

[0047] In some embodiments, in addition to generating the query tree 112, the query processor 104 also examines the syntactic structure of the query 102. The query processor 104 analyzes the semantics of the query tree 112 to determine if there are any problems, such as incompatible operation types or references to non-existent tables. Figure 1 Although not shown, the query processor 104 may access information in the database directory 140 to implement these functions.

[0048] The query optimizer 110 includes a logic layer optimizer 114 that applies rules and retrieves the cardinality of the logic steps in the query tree 112 to generate an execution plan for the query tree based on the retrieved cardinality and the optimization rules. The logic layer optimizer 114 can calculate the respective hash values ​​of the entire query tree 112 and the sub-branches of the query tree 112 including the respective logic steps, and access the cardinality data of the database directory 140 based on the hash values. Since the logic steps in the query are generated according to the canonical form, as described below, the same logic steps appearing in different queries have the same text and thus the same hash value. Therefore, if the cardinality of the logic steps is stored in the database directory 140 and indexed by their hash values, the logic layer optimizer 114 can quickly retrieve the cardinality of previously executed queries and / or each previously executed sub-part. The logic layer optimizer 114 can then generate multiple execution plans 122 and evaluate different plans based on the returned cardinality. The execution plan 122 may specify different execution orders for logical steps and / or different types of operations (e.g., different types of join operations, such as Hash Join, Nested LoopJoin, or Merge Join). As described above, the cardinality of the source and / or column and / or the size of the output result may affect the cost of each logical step in the execution plan, thereby affecting the cost of the entire execution plan.

[0049] The cost-based optimizer 120 receives the execution plans 122 and applies the plans to a plan selection module 124. The plan selection module 124 accesses the database catalog 140 and the cardinality estimation module 128 to select one or more of the execution plans 122. When cardinality data for the logic steps of a particular execution plan 122 is stored in the database catalog 140, the plan selection module 124 uses the stored data. In an example system, cardinality data retrieved by the logical layer optimizer 114 may be transmitted to the plan selection module 124 along with the execution plan 122. When the database catalog 140 does not include cardinality data for a logic step or table, the cardinality estimation module 128 generates an estimate of the cardinality of a table, for example, by using statistics previously generated by an ANALYZE command, by sampling the data in the table, and / or by generating a histogram for the table.

[0050] The plan selection module 124 also accesses the cost function of the database catalog 140 to estimate the cost of the execution plan 122. The cost function may estimate the cost of executing each plan using the cardinality estimation and / or other statistical data of the database catalog 140 and / or the cardinality estimation module 128. The plan selection module 124 selects one or more execution plans 122 with lower costs as the query execution plan 126.

[0051] The execution engine 150 executes the query execution plan 126 using data in the database 130 to generate intermediate results 154, which are further processed to generate output results 156. As part of executing the query execution plan 126, the execution engine 150 determines the actual cardinality of the component tables of the query and the intermediate results 154. This cardinality data is fed back to the database directory 140 along with the corresponding hash values ​​for use in optimizing subsequent queries 102 in a closed-loop configuration.

[0052] As described below, each SQL query may include one or more predicates. Each predicate may be defined as a condition that a given portion of a data table satisfies a portion of the execution plan 122. Such predicates may be determined and / or evaluated based on one or more perspectives.

[0053] The plan selection module 124 selects the execution plan 122 with the lowest cost. Since these costs are based on cardinality estimates, more accurate cardinality estimates can improve the performance of the plan selection module 124.

[0054] For example, the cost of executing a query may depend on the order of operations in the execution plan 122. A plan that evaluates the logical steps of a query tree in one order may produce larger intermediate results than a plan that evaluates the logical steps in a different order. Therefore, it is necessary to know not only the cardinality of the source table, but also the cardinality of any intermediate tables that the query plan may generate. As part of the execution plan generation, the logic layer optimizer 114 can specify the order of query operations (e.g., predicate evaluation or predicate combination) so that operations in the execution plan that are expected to produce smaller intermediate results 154 occur earlier than query operations that are expected to produce larger intermediate results 154. This sorting can be performed based on cardinality estimates, which can be considered as the expected size of the intermediate results. The execution engine 150 can determine the cardinality of these intermediate results 154 during the execution of the first query plan and make these cardinality values ​​available to the logic layer optimizer 114 and / or the plan selection module 124 to generate / select the best query execution plan for queries that occur later.

[0055] The following example describes a general logical canonical form that allows the execution engine 150 to capture information in each logical step of the query. The logical steps in the canonical form are generated on the producer side. The logical steps in the canonical form (and their corresponding cardinality statistics) are saved by the execution engine 150 to the database catalog 140. On the consumer side, the query optimizer 110 generates a query tree including the logical steps in the logical canonical form and accesses the database catalog 140 to quickly find the matching canonical form and its associated cardinality and data size information.

[0056] The example described below collects statistics about previously executed queries and makes these statistics available for queries that occur later. The logical steps in the canonical form of the execution plan allow the query optimizer 110 to determine the best execution plan based on a set of execution steps because the query optimizer 110 can quickly determine the cardinality of the execution steps, database columns, and intermediate results based on the actual cardinality of the same steps previously executed using the same database columns and intermediate results. The statistical granularity maintained by the system is at the execution step level, not at the query level. In addition, the cost-based optimizer 120 uses cardinality data to perform physical query optimization. The general canonical form described below is at the logical level. It does not include information such as join order, join algorithm, grouping order, or predicate order. However, the collected cardinality data allows the query optimizer 110 to select one or more alternative execution plans 122 based on the actual cardinality data for each logical step contained in the database.

[0057] The cost-based query optimizer 110 performs physical optimization. The following examples use the word "physical" to distinguish query optimization performed by the logical layer optimizer 114 and the cost-based query optimizer 110. Logical query optimization selects a plan based on cardinality data retrieved from previously executed steps. However, this cardinality data may not be complete because all steps in the query plan may not have been executed before, and all database columns and intermediate results may not have been processed before. The physical optimization also considers estimated cardinality values ​​for previously unexecuted steps. The cost-based query optimizer 110 also considers the impact of join order, join algorithm, grouping order, and predicate order on the cost of the execution plan.

[0058] As described below, each logic step is represented in a canonical form and has a corresponding hash value. The hash value can be generated by applying a hash function (e.g., an MD5 hash function) to the textual representation of the logic step. The hash value allows the logic layer optimizer 114 and / or the plan selection module 124 to quickly find statistics for a specific logic step in the database catalog 140.

[0059] The hash value and its associated statistical information are stored in the database directory 140. Alternatively, the logic steps in the canonical form may also be stored in the database directory 140. Similar to a key-value hash map, the query optimizer 110 uses the matching hash value of the canonical form logic step (e.g., the key generated from the logic step) to find the actual cardinality statistics of the corresponding logic step in the database directory 140. As described above, in the canonical form, the table name and / or column name are fully qualified. That is, the table name and / or column name include all names in the hierarchical order on a given element and the name of the table and / or column itself. Each fully qualified table name or column name is unique in the schema of its database. In addition, the canonical form contains all dependent logic steps of the execution step. The canonical form is recursively generated by including all logic steps that the current logic step depends on. The canonical form defines keywords and grammatical structures for each type of operation. The words (e.g., table names) in the canonical form logic steps are sorted alphabetically to improve their reusability. This is particularly useful because many SQL operations have a commutative property, that is, the steps can be executed in any order. Thus, the system can match the logical steps to the canonical form even if the order of some terms is different from the order of corresponding terms used in previous queries. Although the fully qualified table names are described as being arranged in alphabetical order, the table names may be arranged in other predetermined orders, for example, first by name length and then alphabetically. Any predetermined order may be used as long as the order is applicable to each query processed by the system 100.

[0060] The following material describes the canonical form of the various operations used in the execution steps. Each logic step has a canonical form that follows the definition syntax described below.

[0061] The SCAN operation has the following canonical form:

[0062] SCAN(source[,PREDICATE(filter-expression)])

[0063] The words SCAN and PREDICATE are keywords, and the words in [] are optional. "source" can be a base table (e.g., a column of a database) or an intermediate table (e.g., a table resulting from executing a previous operation in the query). The table name is fully qualified.

[0064] As an example of the application of the canonical form of the SCAN operation, the SQL query "SELECT * FROM t1 WHERE c1>10" generates the canonical form "SCAN(public.t1,PREDICATE(public.t1.c1>10))", where "public" is the database name, "t1" is a specific column of the database, and "c1" is a variable representing the value in the public.t1 column.

[0065] A single JOIN operation has the following canonical form:

[0066] JOIN(source1,source2[,PREDICATE(join-condition)])

[0067] The words SCAN and PREDICATE are keywords, and the words in [] are optional. The JOIN operation can be an inner join (with a join condition) or a Cartesian product join (without a join condition). The items "source1" and "souce2" can be base tables or intermediate tables. source1 and souce2 are sorted in a predetermined order, and in an example embodiment, the predetermined order is alphabetical order.

[0068] As an application example of the canonical form of a JOIN operation, the SQL query "SELECT * FROM t1, t2 WHERE t1.c1 = t2.c1 and t1.c1>10" generates the canonical form "JOIN(SCAN(public.t1, PREDICATE(public.t1.c1>10)), SCAN(public.t2, PREDICATE(public.t2.c1>10)), PREDICATE(public.t1.c1 = public.t2.c1))". Note that this canonical form includes the SCAN operation in canonical form with the predicates from the SQL query.

[0069] The Multi-Way JOIN operation (also called the Consecutive JOIN operation) has the following canonical form:

[0070] JOIN(source1,source2,source3,…[,PREDICATE(join-condition)])

[0071] Multi-Way JOIN operations can be flattened (e.g., sources can be specified individually, regardless of the database structure containing the sources) to promote reusability of the Multi-Way JOIN canonical form. The flattened join canonical form with sorted source names allows the cardinality data to be reused in future queries, even if the future queries contain a different join order. Multi-Way JOIN operations can be flattened (e.g., (A join B) produces the same results as (B join A)) due to the commutative property of JOIN operations. Thus, (A join B join C) has the same canonical form as (B join C join A).

[0072] As an application example of the canonical form of the Multi-Way JOIN operation, the SQL query "SELECT * FROM t1 INNER JOIN t2 ON t1.c1=t2.c1 INNER JOIN t3 ON t1.c1=t3.c1 WHERE t1.c1>10" generates the canonical form "JOIN(SCAN(public.t1,PREDICATE(public.t1.c1>10)),SCAN(public.t2,PREDICATE(public.t2.c1>10)),SCAN(public.t3,PREDICATE(public.t3.c1>10)),PREDICATE(public.t1.c1=public.t2.c1 AND public.t1.c1=public.t3.c1))".

[0073] The Left Outer JOIN operation has the following canonical form:

[0074] LEFTJOIN(source1,source2[,PREDICATE(join-condition)])

[0075] LEFTJOIN and PREDICATE are keywords. In the Left Outer JOIN operation, the order of source1 and source2 cannot be changed because the order of the two sources is important in the semantics of the Left Outer JOIN operation. Therefore, the Left Outer JOIN operation cannot achieve flattening. The canonical form of the Right Outer Join operation (RIGHTJOIN) is similar to the canonical form of the Left Outer JOIN operation. Many query optimizers convert the Right Outer JOIN operation to the Left Outer JOIN operation. As an example of the LEFTJOIN operation, the SQL query "SELECT * FROM t2 LEFT JOINt1 ON t1.c1=t2.c1" generates the canonical form "LEFTJOIN(SCAN(public.t2),SCAN(public.t1),PREDICATE(public.t1.c1=public.t2.c1))". Other Join operations have a canonical form similar to the Left Outer JOIN operation. These include Semi-join operations that have a canonical form:

[0076] SEMIJOIN(source1,source2[,PREDICATE(join-condition)])

[0077] And the anti-join operation has the canonical form:

[0078] ANTIJOIN(source1,source2[,PREDICATE(join-condition)])

[0079] As with the Left Outer Join operation, the order of source1 and source2 in the SEMIJOIN and ANTIJOIN operations cannot be changed because the order is important in the semantics of the operation.

[0080] The Full Outer JOIN operation has the following canonical form:

[0081] FULLJOIN(source1,source2[,PREDICATE(join-condition)])

[0082] Since the Full Outer JOIN operation has a commutative property, the order of source1 and source2 in the Full Outer JOIN operation can be changed to a predetermined order.

[0083] The Aggregate Group By operation has the following canonical form:

[0084] AGGREGATE(source,GROUPBY(columns)[,

[0085] PREDICATE(having-condition)])

[0086] In this normal form, the words AGGREGATE, GROUPBY, and PREDICATE are keywords, "columns" is the list of columns specified in the GROUP BY clause, and PREDICATE contains the conditions specified in the HAVING clause. As an example of an Aggregate Group By operation, the query "SELECT customer_id, COUNT(order_id) FROM orders GROUP BY customer_id HAVING COUNT(order_id)>100" generates the normal form of the operation "AGGREGATE(SCAN(public.orders), GROUPBY(public.orders.customer_id), PREDICATE(count(order_id)>100))".

[0087] The Union operation has the following canonical form:

[0088] UNION(source1,source2,source3,…)

[0089] In this canonical form, the word "UNION" is the keyword and the source can be a base table or an intermediate table. All source names are sorted in a predetermined alphabetical order because the UNION operation has a commutative property.

[0090] The Intersect operation has the following canonical form:

[0091] INTERSECT(source1,source2,source3,…)

[0092] In this canonical form, the word "INTERSECT" is the keyword and the source can be a base table or an intermediate table. All source names are sorted in a predetermined alphabetical order because the Intersect operation has a commutative property. In addition, consecutive INTERSECT operations can be combined, sorted, and flattened to promote reusability.

[0093] The above operations are not all operations used in the DBMS embodiment. The canonical forms of other operations can be generated in a similar manner.

[0094] For example, the query optimizer 110 may Figure 4 The query optimizer 110 is implemented by the computing device 400 shown in FIG. 1 . In some embodiments, the query optimizer 110 includes an independent unit. Alternatively, in other embodiments, the query optimizer 110 includes one or more of the query processor 104, the database interface 132, the database catalog 140, or the execution engine 150.

[0095] Figure 2 2 is a flowchart of a query processing method provided by an example embodiment. In operation 202, method 200 receives an SQL query. In operation 204, method 200 parses the query into one or more execution plans with normalized form logical steps, as described above. In operation 206, method 200 fully qualifies the table name and reorders the table name in a predetermined order for operations with commutative properties. In operation 208, method 200 calculates corresponding hash values ​​for each execution plan and a subset of each execution plan, including corresponding hash values ​​for each logical step. Then operation 210 searches for a cardinality value in a database catalog based on the calculated hash value. In operation 212, method 200 determines whether a cardinality has been found for the parsed query or any sub-portion thereof, and whether the cardinality value has a current timestamp. If operation 212 determines that the current cardinality has been found, operation 214 transmits the parsed query with the found cardinality value, and (optionally) transmits the hash value of the execution plan for the query and the sub-portion of the query for cost optimization. If operation 212 determines that no cardinalities were found, or that the cardinalities found have older timestamps, this indicates that the cardinalities may not be reliable. Operation 216 sends only the parsed query for cost optimization.

[0096] Figure 3300 is a flowchart of a query processing method 300 provided by another example embodiment. In some embodiments, the method 300 includes a recursive query processing method. The method 300 executes the steps of an execution plan. As described above, the execution plan is in the form of a tree. The root of the tree is the entire query, and the branches are sub-parts of the tree. The leaves of the tree are basic operations, such as the SCAN operation described above. The method 300 starts from operation 302 (processing the root step in the execution plan). In operation 304, the method 300 determines whether the current step is an empty step, which occurs when the previous step is a leaf step without substeps. If the current step is a leaf step, the method 300 ends at operation 306. If operation 304 determines that the current step is not an empty step, the method 300 performs operation 308 to determine whether the current step is a straight-through step. A straight-through step is a step in the execution plan that is not affected by cardinality. Examples of non-straight-through steps include, but are not limited to, SCAN, JOIN, AGGREGATE, UNION, INTERSECT, MINUS, and LIMIT. These steps are sensitive to the cardinality and / or size of the data being processed. The example of straight through step includes but is not limited to SORT, window function (such as SUM, COUNT, AVG) and REDISTRIBUTE. These steps are insensitive to data cardinality and / or size. If operation 308 determines that the current step is a straight through step, operation 310 sets the current step to the substep of the straight through step and branches back to operation 302 to process the new current step.

[0097] If operation 308 determines that the current step is not a pass-through step, the method 300 processes the cardinality data. In operation 312, the method 300 determines whether the actual cardinality data determined when executing the step in operation 302 is different from the estimated cardinality. For example, the estimated cardinality can be included in the execution plan or obtained from the database catalog. When there is no difference between the actual cardinality value and the estimated cardinality value, the method 300 passes control to operation 318, which is described below, which recursively calls the method 300 for the two subtrees of the current step.

[0098] When operation 312 determines that the actual cardinality determined in operation 302 is different from the cardinality estimate stored in the database catalog or received through the query execution plan, operation 314 generates a canonical form and a hash value for the current step. Operation 314 can generate these values ​​from the execution plan or regenerate the canonical form of the step using the above rules. Similarly, the hash value can be received through the execution plan or calculated from the canonical form of the step. In operation 316, the actual cardinality and the estimated cardinality of the step are stored in the database catalog, indexed by the hash value.

[0099] After operation 316, or after operation 312, if the cardinality of the step is the same as the estimated cardinality, operation 318 calls the method 300 for the left and right substeps of the current step. This is indicated by the branch from operation 318 to operation 302.

[0100] As an alternative to the above method, operation 318 may occur immediately before operation 302, so that the method may be recursively called until a leaf of the query tree is encountered. The method processes the leaf to generate intermediate results, which are transmitted back to the higher-level call of the method to be processed according to the branches of the query tree. This process continues until the logic step at the root node is processed using the intermediate results generated by the child logic steps of the logic step.

[0101] Figure 4 is a block diagram of a computing device 400 for performing query processing provided by an example embodiment. In some embodiments, the computing device 400 implements Figure 1 The query processor 110 shown. All components need not be used in each embodiment. For example, the client, server and network resources can use different component sets, or if it is a server, a larger storage device can be used.

[0102] The computing device 400 may include a processor 402, a memory 403, a removable memory 410, and a non-removable memory 412. In different embodiments, the computing device 400 may take different forms. For example, the computing device 400 may be any computing device for maintaining a database. In addition, although various data storage elements are shown as part of the computing device 400, the memory 410 may also or alternatively include a cloud-based storage accessible via a network (not shown, such as the Internet), or a server-based storage.

[0103] The memory 403 may include a volatile memory 414 and a non-volatile memory 408. The computing device 400 may include (or have access to a computing environment. The computing environment includes) various computer-readable media, such as the volatile memory 414 and the non-volatile memory 408, a removable memory 410, and a non-removable memory 412. Computer memory 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 memory technology, compact disc read-only memory (CD ROM), digital versatile disk (DVD) or other optical disk storage, cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other medium capable of storing computer-readable instructions.

[0104] The computing device 400 may include or may access a computing environment including an input interface 406, an output interface 404, and a communication interface 416. The output interface 404 may provide an interface to a display device (e.g., a touch screen), which may also be used as an input device. The input interface 406 may provide an interface in the form of a touch screen, a touchpad, a mouse, a keyboard, a camera, one or more device-specific buttons, one or more sensors integrated into the computing device 400 and / or other input devices or coupled to the computing device 400 and / or other input devices via a wired or wireless data connection. The computing device 400 may operate in a networked environment connected to one or more network nodes or remote computers (e.g., a database server) using a communication interface 416. The remote computer may include a personal computer (PC), a server, a router, a network PC, a peer device, or other public network node, etc. For example, the communication interface 416 may include a local area network (LAN), a wide area network (WAN), a cellular network, a Wi-Fi network, and / or The interface of the network.

[0105] Computer readable instructions stored on a computer readable medium (e.g., one or more applications 418) may be executed by the processor 402 of the computing device 400. Hard drives, CD-ROMs, RAM, and flash memory are some examples of components that include non-transitory computer readable media such as storage devices. The terms "computer readable medium" and "storage devices" do not include carrier waves, which are considered to be too ephemeral to be useful.

[0106] In one embodiment, software can be used to implement the functions or algorithms described herein. The software may include computer executable instructions stored on a computer-readable medium or a computer-readable storage device (e.g., one or more non-transient memories or other types of local or networked storage devices based on hardware, such as in application 418). A device provided by the embodiments described herein can implement software or computer instructions to perform query processing, including DBMS query processing. In addition, these functions correspond to modules, and the modules can be software, hardware, firmware, or any combination thereof. Multiple functions can be performed in one or more modules as needed, and the embodiments are merely examples. The software can be executed on a digital signal processor, an ASIC, a microprocessor, or other types of processors running on a computer system (e.g., a personal computer, a server, or other computer system), thereby turning such a computer system into a programming special machine.

[0107] In some examples, the query processing device 110 or 400 includes: a communication interface 132 or 416 for accessing a database 130 and a database directory 140; a memory 403 for storing instructions 418; and a processor 402 coupled to the memory 403 and the communication interface 132 or 416. The processor 402 executes the following instructions 418: parse a query to generate a first and a second execution plan for the query, each of the first and the second execution plans including one or more logical steps; retrieve corresponding previously determined cardinality values ​​of previously executed logical steps of the first and the second execution plans from the database directory 140; select an execution plan from the first execution plan or the second execution plan, the selected execution plan having a lower cost based on the previously determined cardinality value; execute the selected execution plan on data accessed from the database through the communication interface 132 or 416.

[0108] In some examples, the query processing device 110 or 400 includes: a communication interface 132 or 416 for accessing a database 130 and a database catalog 140; an execution plan method for parsing a query and generating a first and a second execution plan for the query, each of the first and the second execution plans including one or more logical steps; a cardinality method for retrieving corresponding previously determined cardinality values ​​of previously executed logical steps of the first and the second execution plans from the database catalog; a selection method for selecting an execution plan from the first execution plan or the second execution plan, the selected execution plan having a lower cost based on the previously determined cardinality value; and an execution method for executing the selected execution plan on data accessed from the database through the database interface.

[0109] In some embodiments, the query processing device 110 or 400 is implemented as the computing device 400. In some embodiments, the query processing device 110 or 400 is implemented as a database management system (DBMS) query processing device.

[0110] In an example embodiment, the computing device 400 includes: a query parser module for parsing a query to generate a first and a second execution plan for the query, each of the first and the second execution plans including one or more logical steps; a cardinality retrieval module for retrieving corresponding previously determined cardinality values ​​of previously executed logical steps of the first and the second execution plans; an execution plan selection module for selecting an execution plan from the first execution plan or the second execution plan, the selected execution plan having a lower cost based on the previously determined cardinality value; and a plan execution module for executing the selected execution plan on data in a database. In some embodiments, the computing device 400 may include other or additional modules for performing any one or combination of the steps described in the embodiments. In addition, any additional or alternative embodiments or aspects of the method as shown in any of the figures or described in any of the claims are also expected to include similar modules.

[0111] Although several embodiments are described in detail above, other modifications may be made. For example, in order to obtain the desired results, the logic flow described in the accompanying drawings need not follow the specific order or sequential order shown. Other steps may be provided or steps may be removed from the described flow, and other components may be added or removed from the described system. Other embodiments may be within the scope of the appended claims.

Claims

1. A query processing device, characterized in that: include: A communication interface for accessing a database and a database directory; A memory for storing instructions; as well as a processor, coupled to the memory and the communication interface, the processor executing the instructions to: parsing the query to generate first and second execution plans for the query, each of the first and second execution plans comprising one or more logical steps; retrieving from the database catalog corresponding previously determined cardinality values ​​for previously executed logic steps of the first and second execution plans; selecting an execution plan from the first execution plan or the second execution plan, wherein the selected execution plan has a lower cost based on the previously determined cardinality value; as well as The selected execution plan is executed on data accessed from the database through the communication interface.

2. The query processing device according to claim 1, characterized in that: The instructions that configure the processor to parse the query configure the processor to generate the logic steps in a corresponding canonical form having a defined syntax and including corresponding source names.

3. The query processing device according to claim 1 or 2, characterized in that: The instructions that configure the processor to retrieve the previously determined cardinality values ​​for the previously executed logic steps of the first and second execution plans further configure the processor to retrieve the previously determined cardinality values ​​for the first and second execution plans.

4. The query processing device according to any one of claims 1 to 3, characterized in that: The instructions configuring the processor to parse the query configure the processor: fully qualifying each source name of each logical step in the first and second execution plans; determining that a first logic step of the one or more logic steps has a plurality of source names and is interchangeable; and In the first logical step, the plurality of source names are arranged in a predetermined order.

5. The query processing device according to claim 4, characterized in that: The instructions for configuring the processor to determine that the first logic step is interchangeable include instructions for configuring the processor to determine that the first logic step is for an operation including: Inner Join, Full Join, Multi-Way Join, Union, or Intersect.

6. The query processing device according to any one of claims 1 to 5, characterized in that: The instructions also configure the processor to: Calculating a corresponding hash value for each logical step of the first and second execution plans; and The database directory is accessed based on the respective hash values ​​to retrieve the respective previously determined cardinality values ​​for the logic steps of the first and second execution plans.

7. The query processing device according to any one of claims 1 to 6, characterized in that: The one or more logical steps include a structured query language (SQL) operation, and the SQL operation includes at least one of a Scan operation, a Join operation, an Aggregate Scan By operation, a Union operation, or an Intersect operation.

8. The query processing device according to claim 7, characterized in that: The Join operation includes at least one of a Single Join operation, a Multi-Way Join operation, a Left Outer Join operation, a Semi-Join operation, an Anti-Join operation, and a Full Outer Join operation.

9. The query processing device according to any one of claims 1 to 8, characterized in that: The instructions for configuring the processor to execute the selected execution plan on the data in the database configure the processor: executing each logical step of the selected execution plan; Get the corresponding actual cardinality value for each executed logical step; Get the corresponding hash value for each executed logical step; as well as The corresponding actual cardinality values ​​are stored in the database directory indexed by the obtained corresponding hash values.

10. The query processing device according to any one of claims 1 to 9, characterized in that: The instructions that configure the processor to select one of the first execution plan or the second execution plan based on the retrieved previously determined cardinality value also configure the processor to estimate the cardinality value of each logical step in each execution plan that does not have a previously determined cardinality value in the database catalog, and select one of the first execution plan or the second execution plan with a lower cost based on the retrieved previously determined cardinality value and the estimated cardinality value.

11. A query processing method, characterized in that: The method comprises: parsing the query to generate first and second execution plans for the query, each of the first and second execution plans comprising one or more logical steps; retrieving corresponding previously determined cardinality values ​​for previously executed logic steps of the first and second execution plans; selecting an execution plan from the first execution plan or the second execution plan, the selected execution plan having a lower cost based on the previously determined cardinality value; and The selected execution plan is executed on the data in the database.

12. The query processing method according to claim 11, characterized in that: Wherein said parsing said query comprises generating said logical steps in a corresponding canonical form having a defined syntax and including a corresponding source name.

13. The query processing method according to claim 11 or 12, characterized in that: Wherein said retrieving the previously determined cardinality values ​​of the previously executed logic steps of the first and second execution plans further comprises retrieving the previously determined cardinality values ​​of the first and second execution plans.

14. The query processing method according to any one of claims 11 to 13, characterized in that: Wherein parsing the query comprises: fully qualifying each source name of each logical step in the first and second execution plans; determining that a first logic step of the one or more logic steps has a plurality of source names and is interchangeable; and In the first logical step, the plurality of source names are arranged in a predetermined order.

15. The query processing method according to claim 14, characterized in that: Determining that the first logical step is exchangeable includes determining that the first logical step is used for an operation, wherein the operation includes: Inner Join, Full Join, Multi-Way Join, Union or Intersect.

16. The query processing method according to any one of claims 11 to 15, characterized in that: Also includes: Calculating a corresponding hash value for each logical step of the first and second execution plans; as well as A database catalog is accessed based on the respective hash values ​​to retrieve the respective previously determined cardinality values ​​for the logic steps of the first and second execution plans.

17. The query processing method according to any one of claims 11 to 16, characterized in that: The parsing of the query includes parsing a structured query language (SQL) query into operations, wherein the operations include at least one of a Scan operation, a Join operation, an Aggregate Scan By operation, a Union operation, or an Intersect operation.

18. The query processing method according to any one of claims 11 to 17, characterized in that: The executing the selected execution plan on the data in the database comprises: executing each logical step of the selected execution plan; Get the corresponding actual cardinality value for each executed logical step; Obtain the corresponding hash value for each logical step executed; and The corresponding actual cardinality value is stored in a database directory indexed by the obtained corresponding hash value.

19. The query processing method according to any one of claims 11 to 18, characterized in that: Wherein the selecting one of the first execution plan or the second execution plan based on the previously determined cardinality value also includes estimating the cardinality value of each logical step in each execution plan that does not have a previously determined cardinality value in a database catalog, and selecting the one of the first execution plan or the second execution plan having a lower cost based on the previously determined cardinality value and the estimated cardinality value.

20. A non-transitory computer readable medium storing computer instructions, characterized in that: The computer instructions, when executed by one or more processors, cause the one or more processors to perform the following steps: parsing the query to generate first and second execution plans for the query, each of the first and second execution plans comprising one or more logical steps; retrieving corresponding previously determined cardinality values ​​for previously executed logic steps of the first and second execution plans; selecting an execution plan from the first execution plan or the second execution plan, the selected execution plan having a lower cost based on the previously determined cardinality value; as well as The selected execution plan is executed on the data in the database.

21. A query processing device, characterized in that: include: A communication interface for accessing a database and a database directory; an execution plan module, configured to parse a query and generate a first and a second execution plan for the query, each of the first and the second execution plan comprising one or more logical steps; a cardinality module for retrieving from the database catalog corresponding previously determined cardinality values ​​for previously executed logic steps of the first and second execution plans; a selection module, configured to select an execution plan from the first execution plan or the second execution plan, the selected execution plan having a lower cost based on the previously determined cardinality value; as well as An execution module executes the selected execution plan on the data accessed from the database through the database interface.

Images