An optimization method, device, electronic device and medium

Abstract

Embodiments of the present invention disclose an optimization method, apparatus, electronic device, and medium. The method includes: when generating an intermediate plan tree based on a target structured query statement, for a query statement that meets a preset condition, generating a corresponding target hierarchical query node and setting a preset mark for the target hierarchical query node; when generating an optimal execution plan based on the intermediate plan tree, for the target hierarchical query node, determining a left child node and a right child node corresponding to the target hierarchical query node; respectively optimizing the corresponding child nodes according to the cost comparison results corresponding to the left child node and the right child node. By respectively comparing the cost of the left child node and the right child node of the target hierarchical query node, the corresponding child nodes can be optimized, thereby avoiding resource waste caused by cleaning up duplicate data generated by underlying hierarchical queries and improving the performance and efficiency of hierarchical queries.

Description

Technical Field

[0001] Embodiments of the present invention relate to the technical field of data processing, and in particular, to an optimization method, apparatus, electronic device, and medium. Background Art

[0002] Hierarchical query is a database query function that uses a tree traversal method to obtain a tree-structured data set to retrieve a hierarchical relationship report of the tree.

[0003] Currently, when performing a hierarchical query operation in a database, when querying data with a hierarchical relationship for a selected row of data, for all data, calculations need to be performed according to the conditions on the right side of the query condition. When saving the calculated right-side data in memory according to the hierarchical query conditions, since it is necessary to search later, the right-side data is usually saved in a hash (i.e., HASH) table. If there are many duplicate values in the columns specified by the hierarchical query conditions, the conflict linked list in the slots of the HASH table will be very long. During this process, a large number of duplicate data will be cached in the HASH table, resulting in many duplicate data in the corresponding hierarchical query output results. The extra duplicate data will then be removed by the upper-level deduplication node (i.e., DISTINCT), and finally only one unique value will be retained.

[0004] During the entire process where a large number of duplicate data are generated in the lower-level hierarchical query first and then the upper-level DISTINCT clears the extra duplicate data, unnecessary resource waste is caused for clearing the duplicate data generated by the lower-level hierarchical query. This leads to low performance of the hierarchical query. Summary of the Invention

[0005] Embodiments of the present invention provide an optimization method, apparatus, electronic device, and medium to improve the performance of hierarchical queries.

[0006] According to one aspect of the embodiments of the present invention, an optimization method is provided, including:

[0007] When generating an intermediate plan tree based on a target structured query statement, for a query statement that meets a preset condition, generate a corresponding target hierarchical query node and set a preset flag for the target hierarchical query node; wherein, the target structured query statement includes a query statement, and the preset flag is used to indicate that the target hierarchical query node supports deduplication optimization;

[0008] When generating an optimal execution plan based on the intermediate plan tree, for the target hierarchical query node, determine the left child node and the right child node corresponding to the target hierarchical query node;

[0009] Optimize the corresponding child nodes respectively according to the cost comparison results corresponding to the left child node and the right child node; wherein, the cost comparison result is the comparison result of a first cost and a second cost, the first cost is the cost calculated without adding a duplicate removal node; and the second cost is the cost calculated with adding a duplicate removal node.

[0010] According to another aspect of the embodiments of the present invention, there is provided an optimization device, including:

[0011] A generation module, configured to generate a corresponding target hierarchical query node for a query statement that meets a preset condition and set a preset mark for the target hierarchical query node when generating an intermediate plan tree based on a target structured query statement; wherein, the target structured query statement includes a query statement, and the preset mark is used to indicate that the target hierarchical query node supports duplicate removal optimization;

[0012] A determination module, configured to determine a left child node and a right child node corresponding to the target hierarchical query node for the target hierarchical query node when generating an optimal execution plan based on the intermediate plan tree;

[0013] An optimization module, configured to optimize the corresponding child nodes respectively according to the cost comparison results corresponding to the left child node and the right child node; wherein, the cost comparison result is the comparison result of a first cost and a second cost, the first cost is the cost calculated without adding a duplicate removal node; and the second cost is the cost calculated with adding a duplicate removal node.

[0014] According to another aspect of the embodiments of the present invention, there is provided an electronic device, the electronic device includes:

[0015] At least one processor; and

[0016] A memory communicatively connected to the at least one processor; wherein,

[0017] The memory stores a computer program executable by the at least one processor, and when the computer program is executed by the at least one processor, the at least one processor can execute the optimization method according to any embodiment of the present invention.

[0018] According to another aspect of the embodiments of the present invention, there is provided a computer-readable storage medium, the computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the optimization method according to any embodiment of the present invention when executed.

[0019] In the technical solution of the embodiment of the present invention, first, when generating an intermediate plan tree based on a target structured query statement, for a query statement that meets a preset condition, a corresponding target hierarchical query node is generated, and a preset mark is set for the target hierarchical query node; then, when generating an optimal execution plan based on the intermediate plan tree, for the target hierarchical query node, the left child node and the right child node corresponding to the target hierarchical query node are determined; finally, according to the cost comparison results corresponding to the left child node and the right child node respectively, the corresponding child nodes are optimized; wherein, the cost comparison result is the comparison result of a first cost and a second cost, the first cost is the cost calculated without adding a duplicate removal node; the second cost is the cost calculated when adding a duplicate removal node. By setting a preset mark for the determined target hierarchical query node, the method can perform further optimization on the target hierarchical query node containing the preset mark when generating an optimal execution plan; in this process, by respectively comparing the cost results of the left child node and the right child node of the target hierarchical query node, the corresponding child nodes can be optimized, thereby avoiding resource waste caused by cleaning duplicate data generated by underlying hierarchical queries and improving the performance and efficiency of hierarchical queries.

[0020] It should be understood that the content described in this part is not intended to identify the key or important features of the embodiments of the present invention, nor is it used to limit the scope of the present invention. Other features of the present invention will become easily understood through the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0021] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following will briefly introduce the accompanying drawings required for the description of the embodiments. Obviously, the accompanying drawings in the following description are only some embodiments of the present invention. For those of ordinary skill in the art, other drawings can be obtained based on these drawings without creative efforts.

[0022] Figure 1 is a flowchart of an optimization method provided in Embodiment 1 of the present invention;

[0023] Figure 2 is a flowchart of an optimization method provided in Embodiment 2 of the present invention;

[0024] Figure 3 is a schematic structural diagram of an optimization device provided in Embodiment 3 of the present invention;

[0025] Figure 4 is a schematic structural diagram of an electronic device for implementing the optimization method of the embodiment of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0026] To enable those skilled in the art to better understand the solution of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are only a part of the embodiments of the present invention, rather than all of the embodiments. All other embodiments obtained by those of ordinary skill in the art based on the embodiments of the present invention without creative efforts shall fall within the scope of protection of the present invention.

[0027] It should be noted that the terms "first", "second", "target", etc. in the description and claims of the present invention and the above-mentioned drawings are used to distinguish similar objects, and do not necessarily need to be used to describe a specific order or sequence. It should be understood that such data can be interchanged under appropriate circumstances so that the embodiments of the present invention described herein can be implemented in an order other than those illustrated or described herein. In addition, the terms "comprising" and "having" and any variations thereof are intended to cover non-exclusive inclusion. For example, a process, method, system, product or device that includes a series of steps or units does not necessarily have to be limited to those steps or units clearly listed, but may include other steps or units not clearly listed or inherent to these processes, methods, products or devices.

[0028] Embodiment 1

[0029] Figure 1 A flowchart of an optimization method is provided for Embodiment 1 of the present invention. This embodiment is applicable to the situation of optimizing the hierarchical query of child nodes. This method can be executed by an optimization device, which can be implemented in the form of hardware and / or software, and the optimization device can be configured in an electronic device. As Figure 1 shown, the method includes:

[0030] S110. When generating an intermediate plan tree based on a target structured query statement, for a query statement that meets a preset condition, generate a corresponding target hierarchical query node and set a preset mark for the target hierarchical query node.

[0031] In this embodiment, the Structured Query Language (SQL), also known as SQL statement, is a database query and programming language, and can also refer to a relational database manipulation language; it can be used to access data and query, update, and manage relational database systems; and it is also the extension of database script files. The target structured query statement can be understood as the structured query statement containing a query statement input by the user, and the target structured query statement can include one query statement; where the query statement can be understood as the statement starting with the SELECT keyword in the target SQL statement. The target structured query statement only describes what operations to perform on the corresponding database, but does not describe how to operate specifically. Therefore, after receiving the target structured query statement, the database can generate an execution plan containing how to operate specifically based on the target structured query statement. The execution plan can be implemented as a "binary tree" composed of operators in the database, that is, it can be understood as an execution plan tree. The intermediate plan tree can refer to an execution plan tree initially generated based on the target structured query statement, so as to facilitate subsequent optimization based on the intermediate plan tree to obtain the final execution plan tree.

[0032] The preset condition can refer to the clause containing the deduplication clause and the hierarchical query clause, but does not include the clause that needs to rely on the complete table data to obtain the correct result set. Wherein, the query statement can include one or more clauses. The deduplication clause can refer to the clause with the deduplication function, that is, it can be understood as taking the data in the table as the keyword by column, deleting the redundant duplicate row data and only retaining the unique one, which can be expressed as the DISTINCT clause. The hierarchical query clause can refer to the clause for querying the hierarchical relationship between data, and specifically, the hierarchical relationship between data can be obtained through the form of the CONNECT BY keyword. The clause that needs to rely on the complete table data to obtain the correct result set can be understood as the clause that needs to rely on the complete table data (that is, the table data that has not been deduplicated or deleted) to correctly execute the corresponding operation instruction to obtain the correct result set; for example, the clause that needs to rely on the complete table data to obtain the correct result set can be the clause containing the aggregate function, the paging function (that is, rownum), the analytic function, or the indeterminate function, etc., which is not limited here.

[0033] Optionally, the optimization method further includes: determining the query statement meeting the preset condition in the following manner: judging whether the query statement in the target structured query statement contains the deduplication clause and the hierarchical query clause; if the query statement contains the deduplication clause and the hierarchical query clause, then judging whether the query statement contains the preset clause, and the preset clause is the clause that relies on the complete table data to obtain the correct result set; if the query statement does not contain the preset clause, then determining the query statement as the query statement meeting the preset condition.

[0034] Among them, the process of determining a query statement that meets the preset conditions can be as follows: Determine whether the query statement of the target structured query statement contains a distinct clause and a hierarchical query clause; then, if the query statement contains a distinct clause and a hierarchical query clause, determine whether the query statement contains a preset clause, otherwise the query statement is not a sub-query statement that meets the preset conditions; finally, if the query statement does not contain a preset clause, where the preset clause can be understood as a clause that relies on the complete table data to obtain the correct result set, it can be determined that the query statement is a query statement that meets the preset conditions, otherwise the query statement is not a query statement that meets the preset conditions.

[0035] Exemplarily, the query statement A can be expressed as: SELECT DISTINCT C1 FROM T1 CONNECT BY PRIOR C1 = C2; where DISTINCT C1 can represent the distinct clause, and CONNECT BY can represent the hierarchical query clause. The query statement A does not contain a sub-query statement; if on the basis of the query statement A, DISTINCT C1 is transformed into DISTINCT (statement B starting with the SELECT keyword), then statement B is the sub-query statement of the query statement A.

[0036] It can be understood that the query statement may not contain a sub-query statement, or may contain one or more sub-query statements. When determining whether a query statement meets the preset conditions, the sub-query statements contained in the query statement are also judged in the same way. If a certain sub-query statement in the query statement meets the preset conditions, then the query statement also meets the preset conditions, except that when generating the target hierarchical query node, it is for the sub-query statement that meets the preset conditions.

[0037] In this embodiment, the target hierarchical query node may refer to a plan node for hierarchical query generated for a query statement that meets the preset conditions when generating an intermediate plan tree based on the target structured query statement.

[0038] However, it should be noted that if there are multiple sub-query statements that all meet the preset conditions, each sub-query statement that meets the preset conditions can correspond to generating a target hierarchical query node, which is not limited here.

[0039] The preset flag may refer to a preset flag, which can be used to characterize that the target hierarchical query node supports distinct optimization. Supporting distinct optimization can be understood as that during the execution of the target hierarchical query node, it can support the distinct of the distinct clause to achieve the corresponding optimization.

[0040] When generating an intermediate plan tree based on a target structured query statement, for a query statement that meets the preset conditions, a corresponding target hierarchical query node can be generated, and a corresponding preset tag can be set for the target hierarchical query node. Exemplarily, only the preset tag can be set for the target hierarchical query node; alternatively, on the basis of setting the preset tag for the target hierarchical query node, a tag can also be set for a non-target hierarchical query node (i.e., the hierarchical query node corresponding to a query statement or sub-query statement that does not meet the preset conditions) to indicate that the non-target hierarchical query node does not support duplicate removal optimization; and it can be understood that this tag is different from the preset tag of the target hierarchical query node.

[0041] S120. When generating an optimal execution plan based on the intermediate plan tree, for the target hierarchical query node, determine the left child node and the right child node corresponding to the target hierarchical query node.

[0042] In this embodiment, the optimal execution plan can be understood as the final execution plan obtained by optimizing based on the intermediate plan tree. In the process of generating the optimal execution plan based on the intermediate plan tree, it can be determined whether a hierarchical query node in the intermediate plan tree is a target hierarchical query node according to whether the hierarchical query node contains a preset tag. If it contains a preset tag, it can be determined that the hierarchical query node is a target hierarchical query node; otherwise, it is not a target hierarchical query node.

[0043] After determining the target hierarchical query node, the next-level plan node connected to the target hierarchical query node can be determined as the child node of the target hierarchical query node according to the binary tree structure relationship of the intermediate plan tree. In this process, since the function of the target hierarchical query node is to query the hierarchical relationship of data, the child nodes of the target hierarchical query node are two table data. Here, the two child nodes can be distinguished by the left and right sides of the intermediate plan tree, that is, they can be called the left child node and the right child node, and one child node can correspond to one table data.

[0044] When performing a hierarchical query, a row of data is taken from the table data of the left child node to the table data of the right child node for hierarchical query detection. Among them, the table data of the left child node and the table data of the right child node are determined according to the query statement. For example, the table data of the left child node and the table data of the right child node can be the same or different. Here, how to determine the table data of the left child node and the right child node according to the query statement is not specifically limited.

[0045] Optionally, the intermediate plan tree includes one or more hierarchical query nodes, and the hierarchical query nodes are plan nodes generated based on a query statement including a hierarchical query clause; for a target hierarchical query node, determining the left child node and the right child node corresponding to the target hierarchical query node includes: for each hierarchical query node, determining whether the hierarchical query node includes a preset flag; if so, determining the hierarchical query node as the target hierarchical query node, and determining the left child node and the right child node corresponding to the target hierarchical query node.

[0046] Among them, the intermediate plan tree may include one or more hierarchical query nodes, and the hierarchical query nodes can be understood as plan nodes generated based on a query statement including a hierarchical query clause; it can be understood that if the query statement includes multiple sub-query statements, the sub-query statement including the hierarchical query clause will also generate a hierarchical query node, so there may be multiple hierarchical query nodes in the intermediate plan tree. For a target hierarchical query node, the process of determining the left child node and the right child node corresponding to the target hierarchical query node can be: for each hierarchical query node, first determine whether the hierarchical query node includes a preset flag; if it includes a preset flag, it can be determined that the hierarchical query node is the target hierarchical query node, and on this basis, determine the left child node and the right child node corresponding to the target hierarchical query node based on the connection relationship of the plan nodes in the intermediate plan tree.

[0047] S130. Optimize the corresponding child nodes respectively according to the cost comparison results corresponding to the left child node and the right child node.

[0048] In this embodiment, the cost comparison result can be understood as the comparison result between a first cost and a second cost. The first cost can be understood as the cost calculated without adding a deduplication node; the second cost can be understood as the cost calculated when adding a deduplication node. The deduplication node can be understood as a plan node generated based on a query statement including a deduplication clause. The left child node corresponds to a first cost and a second cost, and the corresponding first cost and second cost can be determined based on the output columns corresponding to the left child node. Similarly, the right child node corresponds to a first cost and a second cost, and the corresponding first cost and second cost can be determined based on the output columns corresponding to the right child node.

[0049] Optionally, before optimizing the corresponding child nodes respectively according to the cost comparison results corresponding to the left child node and the right child node, it further includes: collecting the first output column of the left child node and the second output column of the right child node; determining the first cost and the second cost corresponding to the left child node based on the first output column, and determining the first cost and the second cost corresponding to the right child node based on the second output column.

[0050] Among them, before optimizing the corresponding child nodes according to the cost comparison results corresponding to the left child node and the right child node respectively, the first cost and the second cost corresponding to the left child node and the right child node can be determined respectively. Specifically, the output columns corresponding to the left child node (i.e., the first output column) and the output columns corresponding to the right child node (i.e., the second output column) can be collected respectively.

[0051] It can be understood that the left child node and the right child node correspond to a table data, and the table data may contain multiple rows of data, and each row of data may contain multiple columns; the output columns of the child node can be determined according to the columns specified in the query condition of the hierarchical query clause and the columns specified in the deduplication condition of the deduplication clause. On this basis, the output columns of the child node can be understood as the table data composed of these determined columns. Since the query condition of the hierarchical query clause and the deduplication condition of the deduplication clause may be complex, such as may not be directed at the same overall table data, the table data corresponding to the left child node and the right child node may be different.

[0052] Based on the first output column, the first cost and the second cost corresponding to the left child node can be determined, and based on the second output column, the first cost and the second cost corresponding to the right child node can be determined.

[0053] Optionally, determining the first cost and the second cost corresponding to the left child node based on the first output column includes: determining the first deduplication rate corresponding to the first output column based on the first output column; determining the first Central Processing Unit (CPU) and memory usage rate when performing the hierarchical query operation without adding the first deduplication node, and determining the first cost corresponding to the left child node according to the first deduplication rate and the first CPU and memory usage rate; determining the second CPU and memory usage rate when performing the hierarchical query operation when adding the first deduplication node, and determining the second cost corresponding to the left child node according to the first deduplication rate and the second CPU and memory usage rate.

[0054] Among them, the first deduplication rate can refer to the ratio of the number of duplicate row data in the first output column to the total number of row data in the first output column. For example, if the first output column contains a total of 100 row data and the number of duplicate row data is 30, then the first deduplication rate can be 30% at this time. The first deduplication node can refer to a plan node for deduplication generated based on the duplicate row data in the first output column. The first CPU and memory utilization rate can be understood as the resource utilization rate of the CPU and memory occupied during the execution of the hierarchical query operation without adding the first deduplication node. Correspondingly, the second CPU and memory utilization rate can be understood as the resource utilization rate of the CPU and memory occupied during the execution of the hierarchical query operation when the first deduplication node is added. The execution of the hierarchical query operation can be understood as the overall hierarchical query operation based on the deduplication clause and the hierarchical query clause in the subquery statement that meets the preset conditions.

[0055] The process of determining the first cost and the second cost corresponding to the left child node based on the first output column can be as follows: Based on the first output column, determine the first deduplication rate corresponding to the first output column. On this basis, without adding the first deduplication node, determine the first CPU and memory utilization rate during the execution of the hierarchical query operation, and based on the first deduplication rate and the first CPU and memory utilization rate, determine the first cost corresponding to the left child node; when the first deduplication node is added, determine the second CPU and memory utilization rate during the execution of the hierarchical query operation, and based on the first deduplication rate and the second CPU and memory utilization rate, determine the second cost corresponding to the left child node. Here, there is no specific limitation on how to determine the first cost corresponding to the left child node based on the first deduplication rate and the first CPU and memory utilization rate, and how to determine the second cost corresponding to the left child node based on the first deduplication rate and the second CPU and memory utilization rate.

[0056] Optionally, determining the first cost and the second cost corresponding to the right child node based on the second output column includes: Based on the second output column, determine the second deduplication rate corresponding to the second output column; without adding the second deduplication node, determine the third CPU and memory utilization rate during the execution of the hierarchical query operation, and based on the second deduplication rate and the third CPU and memory utilization rate, determine the first cost corresponding to the right child node; when the second deduplication node is added, determine the fourth CPU and memory utilization rate during the execution of the hierarchical query operation, and based on the second deduplication rate and the fourth CPU and memory utilization rate, determine the second cost corresponding to the right child node.

[0057] Among them, the second deduplication rate can refer to the ratio of the number of duplicate row data in the second output column to the total number of row data in the second output column. The second deduplication node can refer to a plan node for deduplication generated based on the duplicate row data in the second output column. The third CPU and memory utilization rate can be understood as the resource utilization rate of the CPU and memory occupied during the execution of the hierarchical query operation without adding the second deduplication node. Correspondingly, the fourth CPU and memory utilization rate can be understood as the resource utilization rate of the CPU and memory occupied during the execution of the hierarchical query operation when adding the second deduplication node.

[0058] The process of determining the first cost and the second cost corresponding to the right child node based on the second output column can be as follows: Based on the second output column, the second deduplication rate corresponding to the second output column can be determined. On this basis, without adding the second deduplication node, the third CPU and memory utilization rate during the execution of the hierarchical query operation is determined, and according to the second deduplication rate and the third CPU and memory utilization rate, the first cost corresponding to the right child node is determined; when adding the second deduplication node, the fourth CPU and memory utilization rate during the execution of the hierarchical query operation is determined, and according to the second deduplication rate and the fourth CPU and memory utilization rate, the second cost corresponding to the right child node is determined. Here, there is no specific limitation on how to determine the first cost corresponding to the right child node according to the second deduplication rate and the third CPU and memory utilization rate, and how to determine the second cost corresponding to the right child node according to the second deduplication rate and the fourth CPU and memory utilization rate.

[0059] In this embodiment, after determining the first cost and the second cost corresponding to the left child node, the left child node can be optimized according to the corresponding cost comparison result. Exemplarily, if the first cost corresponding to the left child node is greater than the second cost corresponding to the left child node, it can indicate that adding the first deduplication node will optimize the execution of the corresponding execution plan. At this time, the first deduplication node can be added between the target hierarchical query node and the left child node; otherwise, the optimization is exited.

[0060] Correspondingly, after determining the first cost and the second cost corresponding to the right child node, the right child node can be optimized according to the corresponding cost comparison result. Exemplarily, if the first cost corresponding to the right child node is greater than the second cost corresponding to the right child node, it can indicate that adding the second deduplication node will optimize the execution of the corresponding execution plan. At this time, the second deduplication node can be added between the target hierarchical query node and the right child node; otherwise, the optimization is exited.

[0061] Embodiment 1 provides an optimization method. First, when generating an intermediate plan tree based on a target structured query statement, for a query statement that meets a preset condition, a corresponding target hierarchical query node is generated, and a preset mark is set for the target hierarchical query node. Then, when generating an optimal execution plan based on the intermediate plan tree, for the target hierarchical query node, the left child node and the right child node corresponding to the target hierarchical query node are determined. Finally, the corresponding child nodes are optimized according to the cost comparison results of the left child node and the right child node respectively. The cost comparison result is the comparison result of a first cost and a second cost. The first cost is the cost calculated without adding a deduplication node, and the second cost is the cost calculated with adding a deduplication node. By setting a preset mark for the determined target hierarchical query node, this method can further optimize the target hierarchical query node with the preset mark when generating the optimal execution plan. In this process, by comparing the cost results of the left child node and the right child node of the target hierarchical query node respectively, the corresponding child nodes can be optimized, thus avoiding resource waste caused by cleaning duplicate data generated by the underlying hierarchical query and improving the performance and efficiency of the hierarchical query.

[0062] Embodiment 2

[0063] Figure 2 The flowchart of an optimization method provided by Embodiment 2 of the present invention. This embodiment is a further refinement based on the above embodiment. In this embodiment, the processes of respectively determining the first cost and the second cost corresponding to the left child node and the right child node, and respectively optimizing the corresponding child nodes according to the cost comparison results of the left child node and the right child node are specifically described. As Figure 2 shown, the method includes:

[0064] S210. When generating an intermediate plan tree based on a target structured query statement, for a query statement that meets a preset condition, a corresponding target hierarchical query node is generated, and a preset mark is set for the target hierarchical query node.

[0065] In this embodiment, the target structured query statement may include a query statement, and the preset mark can be used to indicate that the target hierarchical query node supports deduplication optimization.

[0066] S220. When generating an optimal execution plan based on the intermediate plan tree, for the target hierarchical query node, the left child node and the right child node corresponding to the target hierarchical query node are determined.

[0067] In this embodiment, the intermediate plan tree may include one or more hierarchical query nodes. A hierarchical query node can be understood as a plan node generated based on a query statement containing a hierarchical query clause. When generating an optimal execution plan based on the intermediate plan tree, for each hierarchical query node, the hierarchical query node containing a preset tag is determined as the target hierarchical query node. On this basis, the left child node and the right child node corresponding to the target hierarchical query node are determined.

[0068] S230. Collect the first output column of the left child node and the second output column of the right child node.

[0069] S240. Determine the first cost and the second cost corresponding to the left child node based on the first output column, and determine the first cost and the second cost corresponding to the right child node based on the second output column.

[0070] S250. Determine whether the first cost corresponding to the left child node is greater than the second cost corresponding to the left child node. If so, execute S260; if not, execute S290.

[0071] In this embodiment, based on the first cost and the second cost corresponding to the left child node, determine whether the first cost corresponding to the left child node is greater than the second cost corresponding to the left child node. If so, S260 can be executed, and a first duplicate removal node is added between the target hierarchical query node and the left child node. If the first cost corresponding to the left child node is less than or equal to the second cost corresponding to the left child node, it can be indicated that adding the first duplicate removal node does not optimize the execution plan but instead makes the execution cost of the execution plan greater. At this time, the optimization can be exited, that is, the original execution plan of the left child node is maintained.

[0072] S260. Add a first duplicate removal node between the target hierarchical query node and the left child node for optimizing the left child node, and the optimization ends.

[0073] In this embodiment, by adding a first duplicate removal node between the target hierarchical query node and the left child node to optimize the left child node, after the addition is completed, the optimization of the left child node ends.

[0074] Optionally, adding a first duplicate removal node between the target hierarchical query node and the left child node includes: creating a first duplicate removal node, and using the first output column as the duplicate removal column corresponding to the first duplicate removal node; adding the first duplicate removal node between the target hierarchical query node and the left child node, setting the first duplicate removal node as the child node of the target hierarchical query node, and setting the left child node as the child node of the first duplicate removal node.

[0075] Specifically, create a first duplicate removal node, where the first output column can be used as the duplicate removal column corresponding to the first duplicate removal node. The duplicate removal column can be understood as the column targeted when performing the duplicate removal operation. For example, if the columns included in the first output column are c1 and c2, then the duplicate removal columns are c1 and c2, and in all row data, the duplicate removal process is performed only on the data in the c1 and c2 columns for each row data. On this basis, add the first duplicate removal node between the target hierarchical query node and the left child node, which can be understood as inserting the first duplicate removal node as a layer between the target hierarchical query node and the left child node in the intermediate plan tree; set the first duplicate removal node as the child node of the target hierarchical query node (i.e., the first duplicate removal node is in the next layer of the target hierarchical query node), and set the left child node as the child node of the first duplicate removal node (i.e., the left child node is in the next layer of the first duplicate removal node).

[0076] S270. Determine whether the first cost corresponding to the right child node is greater than the second cost corresponding to the right child node. If so, execute S280; if not, execute S290.

[0077] In this embodiment, based on the first cost and the second cost corresponding to the right child node, determine whether the first cost corresponding to the right child node is greater than the second cost corresponding to the right child node. If so, S280 can be executed to add a second duplicate removal node between the target hierarchical query node and the right child node; if the first cost corresponding to the right child node is less than or equal to the second cost corresponding to the right child node, it can indicate that adding the second duplicate removal node does not optimize the execution plan but instead makes the execution cost of the execution plan greater. At this time, the optimization can be exited, that is, the original execution plan of the right child node is maintained.

[0078] S280. Add a second duplicate removal node between the target hierarchical query node and the right child node to optimize the right child node, and the optimization ends.

[0079] In this embodiment, by adding a second duplicate removal node between the target hierarchical query node and the right child node to optimize the right child node, after the addition is completed, the optimization of the right child node ends.

[0080] Optionally, adding a second duplicate removal node between the target hierarchical query node and the right child node includes: creating a second duplicate removal node and using the second output column as the duplicate removal column corresponding to the second duplicate removal node; adding the second duplicate removal node between the target hierarchical query node and the right child node, setting the second duplicate removal node as the child node of the target hierarchical query node, and setting the right child node as the child node of the second duplicate removal node.

[0081] Correspondingly, adding a second duplicate removal node between the target hierarchical query node and the right child node can be as follows: create a second duplicate removal node, and use the second output column as the duplicate removal column corresponding to the second duplicate removal node. On this basis, add the second duplicate removal node between the target hierarchical query node and the right child node. It can be understood that in the intermediate plan tree, the second duplicate removal node is inserted as a layer between the target hierarchical query node and the two layers of the right child node. And set the second duplicate removal node as the child node of the target hierarchical query node (that is, the second duplicate removal node is at the next layer of the target hierarchical query node), and set the right child node as the child node of the second duplicate removal node (that is, the right child node is at the next layer of the second duplicate removal node).

[0082] S290. Exit optimization.

[0083] In this embodiment, exiting optimization can be understood as not making any changes to the execution plans corresponding to the original child nodes, and continuing to maintain the original execution plans for corresponding plan execution.

[0084] The second embodiment provides an optimization method, which specifies the process of separately determining the first cost and the second cost corresponding to the left child node and the right child node, and respectively optimizing the corresponding child nodes according to the cost comparison results corresponding to the left child node and the right child node. This method can determine whether to add the corresponding duplicate removal nodes to optimize the execution plans of the left child node and the right child node by comparing the cost before and after adding the duplicate removal nodes for the left child node and the right child node respectively, thereby improving the execution performance and efficiency of hierarchical queries.

[0085] The following is an exemplary description of the present invention:

[0086] A hierarchical query clause is a way to obtain the hierarchical relationship between data through the CONNECT BY keyword. The keyword can be understood as a query condition.

[0087] The DISTINCT clause is a duplicate removal function clause, which performs duplicate removal processing on the columns included in the query items using the columns as keywords (i.e., duplicate removal conditions) and outputs the results.

[0088] For statements that contain both DISTINCT and CONNECT BY at the same time, as follows:

[0089] CREATE TABLE T1(C1 INT, C2 INT); (That is, create table T1, and table T1 contains columns C1 and C2)

[0090] INSERT INTO T1 VALUES(3, 4);

[0091] INSERT INTO T1 VALUES(2, 3);

[0092] INSERT INTO T1 VALUES(2, 3);

[0093] INSERT INTO T1 VALUES(1, 2); (That is, insert 4 rows of data into table T1)

[0094] COMMIT;

[0095] SELECT DISTINCT C1 FROM T1 CONNECT BY PRIOR C1 = C2;

[0096] Among them, PRIOR is used to specify the parent node, and C1 is the parent node. For example, if C1 = PRIOR C2, then C2 is the parent node.

[0097] Get an execution plan similar to the following:

[0098] Node 1#NSET2: [1, 1, 8]; (That is, the output result set)

[0099] Node 2#PRJT2: [1, 1, 8]; exp_num(1), is_atom(FALSE);

[0100] Node 3#DISTINCT: [1, 1, 8] KEY(C1); (KEY(C1) indicates de-duplication with column C1 as the de-duplication column)

[0101] Node 4#HIERARCHICAL QUERY(UNIQUE): [1, 1, 8]; KEY_NUM(1); (That is, the hierarchical query plan node)

[0102] Node 5#CSCN2: [1, 1, 8]; INDEX33559824(T1); (That is, the table data of the left child node is table T1)

[0103] Node 6#CSCN2: [1, 1, 8]; INDEX33559824(T1); (That is, the table data of the right child node is table T1)

[0104] The hierarchical query probes the data of the left child node (the 5th node) to the right child node (the 6th node), and outputs the records that meet the PRIOR condition. The execution process is as follows:

[0105] 1. Take a row of data from the left child node as the driving row data to probe the right child node, and obtain the records that meet the PRIOR condition. When processing the first row of data (3, 4), the second row (2, 3) and the third row (2, 3) are detected; specifically, based on the hierarchical query condition of PRIOR C1 = C2, probe the row data in the right child node where the C2 column is the same as the C1 column (i.e., the value 3) of the first row of data (3, 4), that is, the second row (2, 3) and the third row (2, 3), and their C2 columns are both the value 3. Jump to step 2 for caching.

[0106] 2. Cache the records that meet the conditions (i.e., (2, 3), (2, 3)), and at the same time output the results to the parent layer (the output results are the detected row data and the driving row data itself, that is, (3, 4), (2, 3), (2, 3)). The parent layer can refer to node 1.

[0107] 3. For the two cached rows of data, that is, (2, 3), (2, 3), continue to probe the right child node row by row; specifically, continue to probe the right child node for the row data (2, 3), obtain the records that meet the PRIOR condition, that is, the row data (1, 2), and continue to repeat steps 2 and 3; continue to probe the right child node for the next row data (2, 3), obtain the records that meet the PRIOR condition, that is, the row data (1, 2), and continue to repeat steps 2 and 3; until the cached data is processed. Since there is no matching data when probing the right child node for (1, 2), the probing with the first row of data (3, 4) as the driving row data is aborted this time.

[0108] 4. Take the next row of data (i.e., the second row of data (2, 3)) of the first row of data (3, 4) from the left child node to probe the right child node, obtain the records that meet the PRIOR condition, and repeat steps 2, 3, and 4 until the row data of the left child node is processed. When taking the second row of data (2, 3) from the left child node, the processing flow actually repeats that of the first row of data (3, 4) before.

[0109] However, there is a problem with the above planned process. When saving the data of the right child node according to the hierarchical query condition in memory, since it needs to be searched later, generally the records that meet the PRIOR condition are put into a hash (i.e., HASH) table. PRIOR C1 = C2, that is, the hierarchical query key is the C2 column. If there are many duplicate values in C2, then the conflict linked list in the slot of the HASH table will be very long. When executing according to the above process, a large amount of duplicate data will be cached, and there are also many duplicate data in the output results. The redundant duplicate data will be removed by the upper-level DISTINCT node later, and finally only one value is retained. In the whole process, a large number of duplicate data are generated by the underlying hierarchical query first, and then the upper layer cleans up the duplicate data, resulting in low performance.

[0110] In response to the above problems, the embodiment of the present invention proposes a solution that not only uses DISTINCT in the upper layer, but also delegates DISTINCT to both sides of the underlying hierarchical query (i.e., the left child node and the right child node), so that the row data on both sides of the hierarchical query are deduplicated first. Then, when saving data and detecting matching data, the scale of intermediate data and the number of driving rows and detection times can be reduced, thereby avoiding unnecessary waste of memory and CPU and improving performance.

[0111] After the above statement is optimized using the method of the embodiment of the present invention, the query plan may become similar to:

[0112] Node 1#NSET2:[1,1,8];

[0113] Node 2#PRJT2:[1,1,8]; exp_num(1), is_atom(FALSE);

[0114] Node 3#DISTINCT:[1,1,8]KEY(C1);

[0115] Node 4#HIERARCHICAL QUERY(UNIQUE):[1,1,8];KEY_NUM(1);

[0116] Node 5#DISTINCT:[1,1,8]KEY(C1,C2);

[0117] Node 6#CSCN2:[1,1,8];INDEX33559824(T1);

[0118] Node 7#DISTINCT:[1,1,8]KEY(C1,C2);

[0119] Node 8#CSCN2:[1,1,8];INDEX33559824(T1);

[0120] The KEY of the newly added DISTINCT operator (node ​​5, node 7) should be set to all the output columns of the original left (node ​​6) and right (node ​​8) nodes of the hierarchical query. In the given example, it is the union of the hierarchical query condition PRIOR C1=C2 and the query item C1, that is, C1, C2. In addition, before and after adding a new DISTINCT, the corresponding execution cost can be determined by estimating the deduplication rate of the DISTINCT and the CPU and memory usage. Based on the performance savings and overhead brought by this cost, consider whether to add new DISTINCT nodes to the left and right sides (i.e., left and right child nodes) of the underlying hierarchical query, which can adapt to more data scenarios and achieve better performance optimization effects. According to the actual situation, the final result of adding a DISTINCT node may be two sides, one side, or 0 sides.

[0121] In addition, the above examples are just simple statements, and the above optimizations can be applied to clauses where both sides of the hierarchical query are multi-table joins or other complex forms.

[0122] The embodiments of the present invention are further optimized after the lexical, syntactic, and semantic analyses of the SQL statements input by the user are correct based on the existing relational database and the analysis results (i.e., semantic analysis results) can be successfully obtained. The method may include the following steps:

[0123] Intermediate plan tree generation stage:

[0124] 1) Based on the semantic analysis results, the statement must contain a DISTINCT clause (i.e., a deduplication clause) and a hierarchical query clause. Otherwise, the optimization ends and the optimization exits. Proceed to step 2;

[0125] 2) The statement cannot contain set functions, rownum, analytic functions, indeterminate functions, and other clauses and keywords that may rely on complete data to obtain the correct result set. Otherwise, the optimization ends.

[0126] Proceed to step 3;

[0127] 3) Generate a hierarchical query plan node (i.e., the target hierarchical query node), and set a flag F1 = TRUE (i.e., a preset flag) for the target hierarchical query node, indicating that the current hierarchical query supports DISTINCT optimization. Generate the intermediate plan tree according to the original logic. Proceed to step 4;

[0128] Execution plan generation stage:

[0129] 4) When trying various tree forms to obtain the optimal plan based on the intermediate plan tree generated under the above conditions, when processing the hierarchical query node, if the flag F1 is TRUE, then process the left and right child nodes of the hierarchical query in sequence. Collect the output columns of the child nodes, and based on the comparison result of the cost calculated without DISTINCT (i.e., the first cost) and the cost calculated with DISTINCT (i.e., the second cost), determine whether a new DISTINCT process needs to be added to this side of the child node. If adding a new DISTINCT can reduce the cost, it is considered that DISTINCT needs to be added; otherwise, it does not need to be added. If a new DISTINCT does not need to be added, the optimization exits; if a new DISTINCT needs to be added, proceed to step 5;

[0130] 5) Create a new DISTINCT node, use the output columns of the left and right child nodes of the hierarchical query as the deduplication columns, and execute step 6;

[0131] 6) Add a DISTINCT node between the target hierarchical query node and the corresponding child nodes, and set the parent-child relationship between the nodes: the child node of the target hierarchical query is the DISTINCT node, and the child nodes of the DISTINCT node are the original child nodes of the target hierarchical query (i.e., the original left and right child nodes). The optimization is completed.

[0132] Embodiment III

[0133] Figure 3 It is a schematic structural diagram of an optimization device provided by Embodiment III of the present invention. As Figure 3 shown, the device includes: a generation module 310, a determination module 320, and an optimization module 330;

[0134] The generation module 310 is configured to generate a corresponding target hierarchical query node for a query statement that meets a preset condition and set a preset mark for the target hierarchical query node when generating an intermediate plan tree based on the target structured query statement; wherein, the target structured query statement includes a query statement, and the preset mark is used to indicate that the target hierarchical query node supports duplicate removal optimization;

[0135] The determination module 320 is configured to determine the corresponding left child node and right child node of the target hierarchical query node for the target hierarchical query node when generating an optimal execution plan based on the intermediate plan tree;

[0136] The optimization module 330 is configured to optimize the corresponding child nodes respectively according to the cost comparison result corresponding to the left child node and the right child node; wherein, the cost comparison result is the comparison result of a first cost and a second cost, the first cost is the cost calculated without adding a duplicate removal node; the second cost is the cost calculated when adding a duplicate removal node.

[0137] First, through the generation module 310, when generating an intermediate plan tree based on a target structured query statement, for a query statement that meets the preset conditions, a corresponding target hierarchical query node is generated, and a preset mark is set for the target hierarchical query node. Then, through the determination module 320, when generating an optimal execution plan based on the intermediate plan tree, for the target hierarchical query node, the left child node and the right child node corresponding to the target hierarchical query node are determined. Finally, through the optimization module 330, according to the cost comparison results corresponding to the left child node and the right child node respectively, the corresponding child nodes are optimized; wherein, the cost comparison result is the comparison result of the first cost and the second cost, the first cost is the cost calculated without adding a duplicate removal node; the second cost is the cost calculated when adding a duplicate removal node. By setting a preset mark for the determined target hierarchical query node, the device can perform further optimization on the target hierarchical query node containing the preset mark when generating the optimal execution plan; during this process, by respectively comparing the cost results of the left child node and the right child node of the target hierarchical query node, the corresponding child nodes can be optimized, thereby avoiding resource waste caused by cleaning duplicate data generated by the underlying hierarchical query and improving the performance and efficiency of the hierarchical query.

[0138] Optionally, the device further includes:

[0139] Determine the query statement that meets the preset conditions in the following manner:

[0140] The first judgment module is used to judge whether the query statement of the target structured query statement contains a duplicate removal clause and a hierarchical query clause;

[0141] The second judgment module is used to, if the query statement contains a duplicate removal clause and a hierarchical query clause, judge whether the query statement contains a preset clause, and the preset clause is a clause that relies on the complete table data to obtain the correct result set;

[0142] The statement determination module is used to, if the query statement does not contain the preset clause, determine that the query statement is a query statement that meets the preset conditions.

[0143] Optionally, the intermediate plan tree includes one or more hierarchical query nodes, and the hierarchical query node is a plan node generated based on a query statement containing a hierarchical query clause;

[0144] The determination module 320 specifically includes:

[0145] The mark judgment unit is used to, for each hierarchical query node, judge whether the hierarchical query node contains a preset mark;

[0146] A node determination unit, which, if so, determines the hierarchical query node as the target hierarchical query node, and determines the left child node and the right child node corresponding to the target hierarchical query node.

[0147] Optionally, the apparatus further includes:

[0148] A collection module, configured to collect the first output column of the left child node and the second output column of the right child node before optimizing the corresponding child nodes respectively according to the cost comparison results corresponding to the left child node and the right child node;

[0149] A cost determination module, configured to determine the first cost and the second cost corresponding to the left child node based on the first output column, and determine the first cost and the second cost corresponding to the right child node based on the second output column.

[0150] Optionally, the optimization module 330 is specifically configured to:

[0151] If the first cost corresponding to the left child node is less than or equal to the second cost corresponding to the left child node, then exit the optimization; otherwise, add a first duplicate removal node between the target hierarchical query node and the left child node for optimizing the left child node;

[0152] If the first cost corresponding to the right child node is less than or equal to the second cost corresponding to the right child node, then exit the optimization; otherwise, add a second duplicate removal node between the target hierarchical query node and the right child node for optimizing the right child node.

[0153] Optionally, adding a first duplicate removal node between the target hierarchical query node and the left child node is specifically configured to:

[0154] Create a first duplicate removal node, and use the first output column as the duplicate removal column corresponding to the first duplicate removal node;

[0155] Add the first duplicate removal node between the target hierarchical query node and the left child node, set the first duplicate removal node as the child node of the target hierarchical query node, and set the left child node as the child node of the first duplicate removal node;

[0156] And adding a second duplicate removal node between the target hierarchical query node and the right child node includes:

[0157] Create a second duplicate removal node, and use the second output column as the duplicate removal column corresponding to the second duplicate removal node;

[0158] Add the second duplicate removal node between the target hierarchical query node and the right child node, set the second duplicate removal node as the child node of the target hierarchical query node, and set the right child node as the child node of the second duplicate removal node.

[0159] Optionally, the cost determination module is specifically configured to:

[0160] Determine the first duplicate removal rate corresponding to the first output column based on the first output column;

[0161] Without adding a first duplicate removal node, determine the first CPU and memory usage rates when performing a hierarchical query operation, and determine the first cost corresponding to the left child node according to the first duplicate removal rate and the first CPU and memory usage rates;

[0162] When adding a first duplicate removal node, determine the second CPU and memory usage rates when performing a hierarchical query operation, and determine the second cost corresponding to the left child node according to the first duplicate removal rate and the second CPU and memory usage rates.

[0163] Optionally, the cost determination module is further specifically configured to:

[0164] Determine the second duplicate removal rate corresponding to the second output column based on the second output column;

[0165] Without adding a second duplicate removal node, determine the third CPU and memory usage rates when performing a hierarchical query operation, and determine the first cost corresponding to the right child node according to the second duplicate removal rate and the third CPU and memory usage rates;

[0166] When adding a second duplicate removal node, determine the fourth CPU and memory usage rates when performing a hierarchical query operation, and determine the second cost corresponding to the right child node according to the second duplicate removal rate and the fourth CPU and memory usage rates.

[0167] The optimization device provided by the embodiments of the present invention can execute the optimization method provided by any embodiment of the present invention, and has corresponding functional modules and beneficial effects for executing the method.

[0168] Embodiment 4

[0169] Figure 4FIG. 0 shows a schematic structural diagram of an electronic device 10 that can be used to implement an embodiment of the present invention. The electronic device 10 is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device 10 can also represent various forms of mobile devices, such as personal digital processors, cellular phones, smart phones, wearable devices (such as helmets, glasses, watches, etc.) and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely examples and are not intended to limit the implementation of the present invention described and / or claimed herein.

[0170] As Figure 4 shown, the electronic device 10 includes at least one processor 11, and a memory communicatively connected to the at least one processor 11, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc. The memory stores a computer program executable by the at least one processor. The processor 11 can perform various appropriate actions and processes according to the computer program stored in the read-only memory (ROM) 12 or the computer program loaded from the storage unit 18 into the random access memory (RAM) 13. In the RAM 13, various programs and data required for the operation of the electronic device 10 can also be stored. The processor 11, the ROM 12, and the RAM 13 are connected to each other through a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0171] A plurality of components in the electronic device 10 are connected to the I / O interface 15, including: an input unit 16, such as a keyboard, a mouse, etc.; an output unit 17, such as various types of displays, speakers, etc.; a storage unit 18, such as a magnetic disk, an optical disc, etc.; and a communication unit 19, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 19 allows the electronic device 10 to exchange information / data with other devices through a computer network such as the Internet and / or various telecommunication networks.

[0172] The processor 11 can be various general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of the processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various dedicated artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. The processor 11 executes the various methods and processes described above, such as the optimization method.

[0173] In some embodiments, the optimization method may be implemented as a computer program tangibly embodied in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program may be loaded and / or installed onto the electronic device 10 via the ROM 12 and / or the communication unit 19. When the computer program is loaded into the RAM 13 and executed by the processor 11, one or more steps of the optimization method described above may be performed. Alternatively, in other embodiments, the processor 11 may be configured to execute the optimization method by any other suitable means (e.g., by means of firmware).

[0174] The various embodiments of the systems and techniques described above in this document may be implemented in digital electronic circuitry, integrated circuit systems, field programmable gate arrays (FPGA), application specific integrated circuits (ASIC), application specific standard products (ASSP), systems on a chip (SOC), complex programmable logic devices (CPLD), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include: being implemented in one or more computer programs that may be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a special-purpose or general-purpose programmable processor that may receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit the data and instructions to the storage system, the at least one input device, and the at least one output device.

[0175] The computer programs for implementing the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing apparatus, such that the computer programs, when executed by the processor, cause the functions / operations specified in the flowchart and / or block diagram to be implemented. The computer programs may be executed entirely on the machine, partly on the machine, as a stand-alone software package partly on the machine and partly on a remote machine, or entirely on the remote machine or server.

[0176] In the context of the present invention, a computer-readable storage medium can be a tangible medium that can contain or store a computer program for use by or in connection with an instruction execution system, apparatus, or device. The computer-readable storage medium can include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination of the foregoing. Alternatively, the computer-readable storage medium can be a machine-readable signal medium. More specific examples of the machine-readable storage medium would include an electrical connection based on one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0177] To provide for interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and a pointing device (e.g., a mouse or a trackball) by which the user can provide input to the electronic device. Other kinds of devices can also be used to provide for interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including acoustic input, speech input, or tactile input).

[0178] The systems and techniques described herein can be implemented in a computing system that includes backend components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes frontend components (e.g., a user computer having a graphical user interface or a web browser through which the user can interact with an implementation of the systems and techniques described herein), or a computing system that includes any combination of such backend components, middleware components, or frontend components. The components of the system can be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), a blockchain network, and the Internet.

[0179] A computing system may include a client and a server. The client and the server are generally far from each other and usually interact via a communication network. The client-server relationship is created by computer programs running on respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or a cloud host, which is a host product in the cloud computing service system, and solves the defects of difficult management and weak business scalability existing in traditional physical hosts and VPS services.

[0180] It should be understood that various forms of the processes shown above can be used, steps can be reordered, added or deleted. For example, the steps recited in the present invention can be executed in parallel, sequentially or in a different order, as long as the desired results of the technical solution of the present invention can be achieved, and no limitation is made herein.

[0181] The above specific embodiments do not constitute a limitation on the protection scope of the present invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions and improvements made within the spirit and principle of the present invention shall be included within the protection scope of the present invention.

Claims

1. An optimization method, characterized in that, the method includes: When generating an intermediate plan tree based on a target structured query statement, for a query statement that meets preset conditions, generate a corresponding target hierarchical query node and set a preset mark for the target hierarchical query node; wherein, the target structured query statement includes a query statement, and the preset mark is used to indicate that the target hierarchical query node supports duplicate elimination optimization; When generating an optimal execution plan based on the intermediate plan tree, for the target hierarchical query node, determine the left child node and the right child node corresponding to the target hierarchical query node; Optimize the corresponding child nodes respectively according to the cost comparison results corresponding to the left child node and the right child node; wherein, the cost comparison result is the comparison result of a first cost and a second cost, the first cost is the cost calculated without adding a duplicate elimination node; the second cost is the cost calculated when adding a duplicate elimination node; The step of optimizing the corresponding child nodes respectively according to the cost comparison results corresponding to the left child node and the right child node includes: If the first cost corresponding to the left child node is less than or equal to the second cost corresponding to the left child node, then exit the optimization; otherwise, add a first duplicate elimination node between the target hierarchical query node and the left child node for optimizing the left child node; If the first cost corresponding to the right child node is less than or equal to the second cost corresponding to the right child node, then exit the optimization; otherwise, add a second duplicate elimination node between the target hierarchical query node and the right child node for optimizing the right child node.

2. The method according to claim 1, characterized in that, it further includes: Determine the query statement that meets the preset conditions in the following manner: Judge whether the query statement of the target structured query statement contains a duplicate elimination clause and a hierarchical query clause; If the query statement contains a duplicate elimination clause and a hierarchical query clause, then judge whether the query statement contains a preset clause, and the preset clause is a clause that relies on the complete table data to obtain the correct result set; If the query statement does not contain the preset clause, then determine that the query statement is a query statement that meets the preset conditions.

3. The method according to claim 1, characterized in that, the intermediate plan tree includes one or more hierarchical query nodes, and the hierarchical query node is a plan node generated based on a query statement containing a hierarchical query clause; For the target hierarchical query node, the step of determining the left child node and the right child node corresponding to the target hierarchical query node includes: For each hierarchical query node, judge whether the hierarchical query node contains a preset mark; If so, determine that the hierarchical query node is the target hierarchical query node, and determine the left child node and the right child node corresponding to the target hierarchical query node.

4. The method according to claim 1, characterized in that, Before optimizing the corresponding child nodes according to the cost comparison results corresponding to the left child node and the right child node respectively, the following steps are further included: Collect the first output column of the left child node and the second output column of the right child node; Determine the first cost and the second cost corresponding to the left child node based on the first output column, and determine the first cost and the second cost corresponding to the right child node based on the second output column.

5. The method according to claim 1, wherein, Adding a first duplicate removal node between the target hierarchical query node and the left child node includes: Create a first duplicate removal node and use the first output column as the duplicate removal column corresponding to the first duplicate removal node; Add the first duplicate removal node between the target hierarchical query node and the left child node, set the first duplicate removal node as the child node of the target hierarchical query node, and set the left child node as the child node of the first duplicate removal node; And adding a second duplicate removal node between the target hierarchical query node and the right child node includes: Create a second duplicate removal node and use the second output column as the duplicate removal column corresponding to the second duplicate removal node; Add the second duplicate removal node between the target hierarchical query node and the right child node, set the second duplicate removal node as the child node of the target hierarchical query node, and set the right child node as the child node of the second duplicate removal node.

6. The method according to claim 4, wherein, The determining the first cost and the second cost corresponding to the left child node based on the first output column includes: Determine the first duplicate removal rate corresponding to the first output column based on the first output column; Without adding a first duplicate removal node, determine the first CPU (Central Processing Unit) and memory usage rate when performing the hierarchical query operation, and determine the first cost corresponding to the left child node according to the first duplicate removal rate and the first CPU and memory usage rate; When adding a first duplicate removal node, determine the second CPU and memory usage rate when performing the hierarchical query operation, and determine the second cost corresponding to the left child node according to the first duplicate removal rate and the second CPU and memory usage rate.

7. The method according to claim 4, wherein, Determining the first cost and the second cost corresponding to the right child node based on the second output column includes: Determine the second duplicate removal rate corresponding to the second output column based on the second output column; Without adding a second duplicate removal node, determine the third CPU and memory usage rate when performing the hierarchical query operation, and determine the first cost corresponding to the right child node according to the second duplicate removal rate and the third CPU and memory usage rate; When adding a second duplicate removal node, determine the fourth CPU and memory usage rate when performing the hierarchical query operation, and determine the second cost corresponding to the right child node according to the second duplicate removal rate and the fourth CPU and memory usage rate.

8. An optimization device, wherein, Comprising: A generation module, configured to generate a corresponding target hierarchical query node for a query statement that meets preset conditions and set a preset flag for the target hierarchical query node when generating an intermediate plan tree based on a target structured query statement; wherein, the target structured query statement includes a single query statement, and the preset flag is used to indicate that the target hierarchical query node supports duplicate elimination optimization. A determination module, configured to determine a left child node and a right child node corresponding to the target hierarchical query node for the target hierarchical query node when generating an optimal execution plan based on the intermediate plan tree. An optimization module, configured to optimize corresponding child nodes respectively according to a cost comparison result corresponding to the left child node and the right child node; wherein, the cost comparison result is a comparison result between a first cost and a second cost, the first cost is the cost calculated without adding a duplicate elimination node; the second cost is the cost calculated when adding a duplicate elimination node. The optimization module is specifically configured to: if the first cost corresponding to the left child node is less than or equal to the second cost corresponding to the left child node, exit the optimization; otherwise, add a first duplicate elimination node between the target hierarchical query node and the left child node for optimizing the left child node; if the first cost corresponding to the right child node is less than or equal to the second cost corresponding to the right child node, exit the optimization; otherwise, add a second duplicate elimination node between the target hierarchical query node and the right child node for optimizing the right child node.

9. An electronic device characterized in that the electronic device includes: at least one processor; and a memory communicatively connected to the at least one processor; wherein, the memory stores a computer program executable by the at least one processor, and when the computer program is executed by the at least one processor, the at least one processor is enabled to execute the optimization method according to any one of claims 1-7.

10. A computer-readable storage medium characterized in that the computer-readable storage medium stores computer instructions for causing a processor to execute the optimization method according to any one of claims 1-7 when executed.

Images