Selection method, storage media and computer equipment for multi-table join execution plans

Abstract

The application provides a multi-table connection execution plan selection method, a storage medium and a computer device. The method comprises the following steps: obtaining an execution plan of a connection table; determining whether the connection table is configured with a materialized connection identifier; in the case that the connection table is configured with the materialized connection identifier, judging whether any child node participating in the connection meets a set alternative condition; and if yes, adding the execution plan to an alternative queue. According to the scheme, the execution plans in the alternative queue can all meet the specified condition, and the connection plans that do not meet the condition are excluded, so that the finally selected execution plan has higher execution efficiency.

Description

Technical Field

[0001] The present invention relates to database technology, in particular to a method for selecting a multi-table connection execution plan, a storage medium and a computer device. Background Art

[0002] Join queries are the most important queries in relational databases. Joins are a key feature of the relational database model and a hallmark that distinguishes it from other types of database systems. Existing databases generally use a cost-based approach to select the join order and method for joining multiple tables. The accuracy of this cost-based join selection method depends on data sampling and the cost calculation algorithm.

[0003] Due to the complexity of SQL (Structured Query Language) usage scenarios, the database optimizer may select an inefficient execution plan if the join selection method miscalculates the cost. In such cases, SQL users, such as DBAs (Database Administrators) or SQL developers, need to manually adjust the execution plan. However, for complex SQL statements, it's impossible for SQL users to create an optimal execution plan for the entire statement.

[0004] Therefore, the existing connection selection method may select an execution plan with very low execution efficiency and abandon other obviously better execution plans, which affects the query efficiency of the database. Summary of the Invention

[0005] An object of the present invention is to improve the execution efficiency of multi-table join execution plans.

[0006] A further object of the present invention is to improve the efficiency of the selection algorithm for multi-table join execution plans.

[0007] In particular, the present invention provides a method for selecting a multi-table join execution plan, which includes:

[0008] Get the execution plan of the connection table;

[0009] Determine whether the connection table is configured with a materialized connection identifier;

[0010] If the connection table is configured with a materialized connection identifier, determine whether any child node participating in the connection meets the set candidate condition;

[0011] If so, the execution plan is added to the alternative queue.

[0012] Optionally, before obtaining the execution plan of the join table, the following steps are further included:

[0013] Get the specified operation set by the operator;

[0014] Sets the materialized connection identifier for the tables participating in the multi-table join operation according to the specified operation.

[0015] Optionally, the step of setting a materialized connection identifier for tables participating in the multi-table connection operation according to the specified operation includes:

[0016] Set the materialized connection identifier for the table where the specified operation is located;

[0017] A recursive algorithm is used to set a materialized connection identifier for the connection table that uses the table with the materialized connection identifier as the left and right nodes.

[0018] Optionally, the step of setting a materialized connection identifier for a connection table using a recursive algorithm and using tables with materialized connection identifiers as left and right nodes includes:

[0019] Get the pending table that needs to be set up;

[0020] Determine whether the table to be set is a connection table;

[0021] If so, determine whether any of the tables where the left and right nodes of the table to be set participate in the connection have a materialized connection identifier;

[0022] When any of the tables where the left and right nodes of the table to be set participate in the connection has a materialized connection identifier, the materialized connection identifier is set for the table to be set.

[0023] Optionally, setting the candidate condition includes: the table where the child node is located has a materialized connection identifier, and when the table where any child node participating in the connection is located has the materialized connection identifier, determining that the setting candidate condition is satisfied.

[0024] Optionally, after the step of determining whether any of the tables where the left and right nodes of the table to be set are located have a materialized connection identifier, if neither of the tables where the left and right nodes of the table to be set are located have a materialized connection identifier, the method further includes:

[0025] Determine whether any of the left and right nodes involved in the connection of the table to be set is a controlled materialized node;

[0026] If so, a materialized connection identifier is set for the table to be set, and a materialized channel identifier is set for the table where the controlled materialized node is located.

[0027] Optionally, setting the candidate condition includes: the table where the child node is located has a materialized channel identifier, and when the table where any child node participating in the connection is located has a materialized channel identifier, determining that the setting candidate condition is satisfied.

[0028] Optionally, after adding the execution plan to the candidate queue, the following steps are also performed:

[0029] After the execution plans for multi-table joins are enumerated, the final execution plan is selected based on the execution costs of the execution plans in the candidate queue.

[0030] According to another aspect of the present invention, a machine-readable storage medium is provided, on which a machine executable program is stored. When the machine executable program is executed by a processor, it implements any of the above-mentioned methods for selecting a multi-table join execution plan.

[0031] According to another aspect of the present invention, a computer device is provided, comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, wherein the processor implements any of the above-mentioned methods for selecting a multi-table join execution plan when executing the machine executable program.

[0032] The method for selecting a multi-table join execution plan of the present invention adds a join table execution plan to a candidate queue if the join table is configured with a materialized join identifier and any child node participating in the join satisfies a set candidate condition. All join tables in the execution plans in the candidate queue satisfy the specified conditions, eliminating join plans that do not. This ensures that the ultimately selected execution plan has higher execution efficiency. Furthermore, because this method of forming the candidate queue avoids enumerating each join plan and performing node traversal for each join plan, algorithm efficiency is significantly improved.

[0033] Furthermore, the method for selecting a multi-table join execution plan of the present invention recursively sets a materialized join identifier for the table of the specified operation and the join table that uses the table as a child table. Specifically, after setting the materialized join identifier for the table of the specified operation, the materialized join identifier is subsequently set one by one for each join table that uses the materialized join identifier as a left or right node. This recursive method allows for efficient designation of join methods.

[0034] Furthermore, the method for selecting a multi-table join execution plan of the present invention can also set a materialized channel identifier for the table containing the controlled materialized node, and set a materialized connection identifier for the upper-level connection table. The designated sub-table of the join operation can be defined as a materialized node as the controlled materialized node through a hint mechanism, configuration parameters, or other means.

[0035] Furthermore, the multi-table join execution plan selection method of the present invention can improve SQL execution efficiency by enabling the database optimizer to specify a local execution plan through a hint mechanism, configuration parameters, or other means when the database optimizer is unable to select the optimal execution plan due to cost estimation errors. Practical verification of the solution embodiment has shown that it can significantly improve execution efficiency and shorten execution time.

[0036] Based on the following detailed description of specific embodiments of the present invention in conjunction with the accompanying drawings, those skilled in the art will become more aware of the above and other objects, advantages and features of the present invention. BRIEF DESCRIPTION OF THE DRAWINGS

[0037] Hereinafter, some specific embodiments of the present invention will be described in detail in an exemplary and non-limiting manner with reference to the accompanying drawings. The same reference numerals in the accompanying drawings indicate the same or similar components or parts. It should be understood by those skilled in the art that these drawings are not necessarily drawn to scale. In the accompanying drawings:

[0038] Figure 1 It is an enumeration diagram connecting optional execution plans;

[0039] Figure 2 is a schematic diagram of a method for selecting a multi-table join execution plan according to an embodiment of the present invention;

[0040] Figure 3 This is a flowchart of setting identifiers for connection tables in a method for selecting a multi-table connection execution plan according to an embodiment of the present invention;

[0041] Figure 4 A method for screening execution plans according to a method for selecting a multi-table join execution plan according to an embodiment of the present invention;

[0042] Figure 5 is a schematic diagram of a machine-readable storage medium according to one embodiment of the present invention; and

[0043] Figure 6 is a schematic diagram of a computer device according to one embodiment of the present invention. DETAILED DESCRIPTION

[0044] Figure 1 This is an enumeration diagram of possible join execution plans. For example, the SQL statement for joining three tables, t1, t2, and t3, is "select * from t1, t2, t3 where t1.id = t2.id and t1.id = t3.id." Possible join methods include:

[0045] a. t1 joins t2 to form the connection table t1, t2, and then joins t3 to form the connection table t1, t2, t3; that is, t1 joint2 join t3.

[0046] b. t3 joins t2 to form the connection table t3, t2, which is then joined with t1 to form the connection table t1, t2, t3; that is, t3 joint2 join t1.

[0047] c. t1 joins the join table t3, t2 to obtain the join table t1, t2, t3, where the join table t3, t2 is obtained by joining t3 with t2; that is, t1 join (t3 join t2).

[0048] d. t1 joins join tables t3, t2 to obtain join tables t1, t2, t3, where join tables t3, t2 are obtained by joining t2 with t3; that is, t1 join (t2 join t3).

[0049] e. t2 joins the join tables t1 and t3 to obtain the join table t1, t2, t3, where the join table t1 and t3 is obtained by joining t1 and t3; that is, t2 join (t1 join t3).

[0050] In addition to the connection methods a to e described above, other connection methods can be further deduced. A simple three-table connection can generate so many connection execution plans that the optimizer is likely to make errors in estimating the execution cost, abandoning the preferred solution and selecting an execution plan with very low execution efficiency. For example, if the DBA already knows in advance that connecting t2 to t3 using a nested loop method and materializing the results is a relatively optimal method, that is, connecting tables t3 and t2 satisfies materialization, then this preferred method is only included in connection method c and connection method d. The purpose of the method in this embodiment is to ensure that connection methods c and d, which have been clearly identified as having higher execution efficiency, can be retained in the alternative queue of the execution plan.

[0051] In the description of this embodiment, a simple table is a table, subquery or view in a database; a joined table is the result of joining multiple simple tables, such as t1 join t2. The optimizer is a component in the database that generates the final execution plan for an SQL. The execution plan is the execution method of the SQL. There are many ways to execute an SQL. For example, scanning a table includes full table scan, index scan, bitmap scan and other methods. The unit in which the database optimizer participates in the operation is the table. For example, a basic table t1 can be regarded as a table, the connection of multiple tables such as t1 join t2 is also regarded as a table, and a subquery is also regarded as a table. The process of selecting the optimal execution plan is to enumerate all execution plans for each table, and then use the plan with the lowest execution cost as the final execution plan for the table.

[0052] Based on the above considerations, the main principles of the method of this embodiment are: if an execution plan is a specified operation (OPER1), then the table where the operation is located (for example, A) only retains the execution plan; when the table where the specified operation is located (A) is a subtable of a connection (tables A & B), the connection table must meet the specified conditions; when the connection table (A & B) is a subtable of a higher-level connection (A & B & C), the higher-level connection must meet the specified conditions.

[0053] For example, when (A&B&C) is joined by two tables (A&C) and B, the condition is definitely not met, and all execution plans generated for this case are discarded. However, when (A&B&C) is joined by two tables (A&B) and C, the condition is definitely met because all execution plans for tables (A&B) meet the condition.

[0054] Figure 2 FIG. 1 is a schematic diagram of a method for selecting a multi-table join execution plan according to an embodiment of the present invention. The method for selecting a multi-table join execution plan includes:

[0055] Step S202, obtaining the execution plan of the connection table;

[0056] Step S204, determining whether the connection table is configured with a materialized connection identifier;

[0057] Step S206: If the connection table is configured with a materialized connection identifier, determine whether any child node participating in the connection meets the set candidate condition;

[0058] Step S208: Add the execution plan to the candidate queue.

[0059] The materialized join identifier "materialjoin" indicates that the join table with this identifier has a subtable that satisfies the materialized node requirements of the hint. If the join table has the "materialjoin" identifier, the join algorithm is further evaluated for the two subtables involved in the join. If the subtables meet the candidate criteria, the join method is added to the candidate queue.

[0060] After the candidate queue is enumerated, the plan with the lowest execution cost can be selected from the candidate queue as the final execution plan. That is, after adding the execution plan to the candidate queue, the process can also include: after the multi-table join execution plan is enumerated, selecting the final execution plan based on the execution costs of the execution plans in the candidate queue.

[0061] Before obtaining the execution plan for the join table in step S202, the following steps may be performed: obtaining a specified operation set by an operator; and setting a materialized join identifier for the tables participating in the multi-table join operation according to the specified operation. The process of setting the materialized join identifier may employ a recursive method. Specifically, the step of setting the materialized join identifier for the tables participating in the multi-table join operation according to the specified operation may include: setting the materialized join identifier for the table where the specified operation is performed; and setting the materialized join identifier for the join table using a recursive algorithm, with the tables with the materialized join identifier as left and right nodes.

[0062] The step of using a recursive algorithm to set a materialized connection identifier for a connection table that uses tables with materialized connection identifiers as left and right nodes may include: obtaining a table to be set that requires an identifier setting; determining whether the table to be set is a connection table; if so, determining whether any of the tables containing the left and right nodes that the table to be set participates in the connection contains a materialized connection identifier; and if any of the tables containing the left and right nodes that the table to be set participates in the connection contains a materialized connection identifier, setting the materialized connection identifier for the table to be set.

[0063] After the step of determining whether any of the tables containing the left and right nodes of the table to be set participating in the connection has a materialized connection identifier, if both tables containing the left and right nodes of the table to be set participating in the connection do not have a materialized connection identifier, it is further possible to determine whether any of the left and right nodes of the table to be set participating in the connection is a controlled materialized node; if so, a materialized connection identifier is set for the table to be set, and a materialized channel identifier is set for the table containing the controlled materialized node.

[0064] When a connection table is configured with a materialized connection identifier, the candidate conditions for any child node participating in the connection may include: the table containing the child node has the materialized connection identifier, or the table containing the child node has the materialized channel identifier. In other words, if the table containing any child node participating in the connection has the materialized connection identifier, the candidate condition can be determined to be satisfied; if the table containing any child node participating in the connection has the materialized channel identifier, the candidate condition can also be determined to be satisfied.

[0065] In the multi-table join execution plan selection method described in the above embodiment, if a connection table is configured with a materialized join identifier and any child node participating in the connection satisfies a set candidate condition, the execution plan is added to a candidate queue. All connection tables in the execution plans in the candidate queue meet the specified condition, eliminating connection plans that do not meet the condition. This ensures that the ultimately selected execution plan has higher execution efficiency. Furthermore, because this method of forming the candidate queue avoids enumerating and traversing each connection plan, it significantly improves algorithm efficiency.

[0066] Because the above method recursively sets the materialized connection flag for the table of the specified operation and the connection table that uses the table as a child table, that is, after setting the materialized connection flag for the table of the specified operation, the materialized connection flag is subsequently set one by one for the tables with the materialized connection flag as the connection tables of the left and right nodes. This recursive method can efficiently implement the specification of the connection method.

[0067] Figure 3This is a flow chart of setting identifiers for connection tables in a method for selecting a multi-table connection execution plan according to an embodiment of the present invention. In order to simplify the description, in the figure, the materialjoin identifier is simplified to identifier X, and the materialpath identifier is simplified to identifier Y. Figure 3 The process of setting a flag for a table calculation before executing the plan may include:

[0068] Step S302, determining whether the table to be set is a table for a join operation;

[0069] Step S304: determine whether the tables where the left and right nodes of the table to be set participate in the connection have an identifier X;

[0070] Step S306, setting an identifier X for the table to be set;

[0071] Step S308: Determine whether the table where the left and right nodes of the table to be set are connected is the specified execution plan;

[0072] Step S310 : Set an identifier X for the table to be set, and set an identifier Y for the table where the materialized node to be controlled is located.

[0073] A specific application example of the above process may be: when a database user or DBA specifies to materialize a connected table, a new materialize hint materialize Hint may be defined. The implementation process of the materialize hint may be: if the table to be set (i.e., the table to be set) is a connected table, such as t1 join t2; identify whether there is a materialjoin identifier on any subtable of the table. If all subtables do not have a materialjoin identifier, determine whether there is a subtable that satisfies the materialize hint, that is, determine whether any of the left and right nodes involved in the connection is a controlled materialized node. If so, the satisfied subtable is set with the materialpath identifier, and the connected table is set with the materialjoin identifier.

[0074] For example, for t1 join t2, if the two subtables t1 and t2 of the t1 join t2 table do not have materialjoin, but t2 meets the requirements of the materialize(t2) hint, then t2 is set with the materialpath flag, and t1 join t2 is set with the materialjoin flag.

[0075] For example, if the two subtables of t1 join t2 join t3 have the materialjoin flag, then t1 join t2 join t3 also has the materialjoin flag.

[0076] When enumerating execution plans for each joined table, if the joined table has the materialjoin flag, it indicates that one of the child tables in the join must be a materialized node as required by the hint. If both child tables in the join have either the materialjoin flag or the materialpath flag, the execution plan for that join is added to the candidate queue. The final execution plan is then selected from the candidate queue with the lowest execution cost.

[0077] Figure 4 This is a method for screening execution plans in a method for selecting a multi-table join execution plan according to an embodiment of the present invention. Figure 4 As shown, the steps for determining whether an execution plan meets the specified conditions include:

[0078] Step S402, determining whether the connection table has an identifier X;

[0079] Step S404, determining whether the tables of the left and right nodes involved in the connection have an identifier X;

[0080] Step S406, determining whether the tables of the left and right nodes involved in the connection have an identifier Y;

[0081] Step S408: Add the execution plan of the connection table to the candidate queue.

[0082] For example, a join of t1 join t2 join t3 can be divided according to the join order, join algorithm, and subtables involved in the join. The method of this embodiment divides the join based on the subtables involved in the join. When the two subtables involved in the join are (t1 join t2) and t3, if the subtable (t1 join t2) meets the conditions, then regardless of whether the join algorithm is nested loop join, merge join, or hash join, and regardless of whether t3 is the left table or the right table, the conditions are met.

[0083] When the two sub-tables involved in the join are (t1 join t3) and t2, if sub-table t2 meets the conditions, then the join method meets the conditions.

[0084] If the hint is not "materialize(t1)" but "materialize(t1 t2)", the result of t1 join t2 is required to be materialized. In this case, for t1 join t2 join t3, if the two sub-tables are (t1 join t2), t3 meets the condition. If the sub-tables are (t1 join t3), t2 does not meet the condition.

[0085] Database users and database application developers (DABs) can specify execution plans in a variety of ways, including hints and configuration parameters. To specify a join table as a materialized node, you can define a hint using the following method. A hint can consist of the materialization hint name followed by the name of the table to be controlled. For example, in the statement materialize(t1 t2), Materialize is the hint name, and t1 and t2 are table names or subquery names.

[0086] There are multiple execution nodes in the SQL execution plan, which eventually form a tree structure. The Hint of this embodiment can control any node in the execution node (such as base table, subquery, connection, etc.) and materialize the node, that is, add a materialized node on top of the node.

[0087] The main technical ideas of the method of this embodiment can be summarized as follows:

[0088] When generating an execution plan for a joined table, a recursive method is used to determine whether a specified execution plan exists for that table. First, the execution plan for the obtained join operation is checked to determine whether the left and right nodes involved in the plan meet the following conditions: the node's operation is the specified operation; or the table where the node resides is the specified table. If these conditions are met, the join table is assigned the flag X (materialjoin flag) and the table where the child node that meets the conditions resides is assigned the flag Y (materialpath flag).

[0089] Through recursion, the connection table generated earlier may also be a table for child nodes of other connection tables. In this case, if the table containing a child node of a connection table has an X flag, then the table itself is also set to an X flag. This avoids traversing all nodes and making judgments for each connection plan, thereby improving algorithm efficiency.

[0090] For a connection table, if there is an X mark, it means that the table must meet the specified conditions. For each execution plan generated for the connection table, it is judged whether the execution plan meets the conditions. If the conditions are met, the execution plan is added to the alternative queue. The basis for judgment is: for the execution plan of a connection operation, the connection sub-nodes involved in the connection are judged separately. If the sub-node meets one of the following two conditions, it meets the conditions: (1) The table where the sub-node is located has a Y mark, and the sub-node is the operation to be executed (2) The table where the sub-node is located has an X mark. According to this rule, it can be determined that all execution plans in the sub-node table are execution plans that meet the conditions.

[0091] This embodiment addresses the problem of database optimizers failing to select the optimal execution plan due to cost estimation errors. The database can specify a local execution plan for the optimizer through a hint mechanism, configuration parameters, or other means. The method proposed in this invention can improve SQL execution efficiency.

[0092] An actual test result verifying the method of this embodiment is: when the database optimizer evaluates that a full table scan is performed on table a (a specific test table) by default, the execution efficiency of the entire SQL is the highest. However, when the indexscan hint is used to specify an index scan on table a, the algorithm rules of this embodiment are used to fix the index scan operation on table a. That is, among all execution plans, the plan that includes the index scan operation on a is retained to participate in the final competition (joining the alternative queue). When there is at least one valid execution plan in the alternative queue, other execution plans can be directly discarded).

[0093] By applying the method of this embodiment, the index scan operation for a is retained in the entire execution plan, and the SQL execution time is reduced from 836 milliseconds to 3 milliseconds.

[0094] This embodiment also provides a machine-readable storage medium and a computer device. Figure 5 is a schematic diagram of a machine-readable storage medium 40 according to one embodiment of the present invention, Figure 6 is a schematic diagram of a computer device 50 according to one embodiment of the present invention.

[0095] The machine-readable storage medium 40 stores a machine executable program 41 . When the machine executable program 41 is executed by a processor, the method for selecting a multi-table join execution plan according to any of the above embodiments is implemented.

[0096] The computer device 50 may include a memory 520, a processor 510, and a machine executable program 41 stored in the memory 520 and running on the processor 510. When the processor 510 executes the machine executable program 41, the method for selecting a multi-table join execution plan of any of the above embodiments is implemented.

[0097] It should be noted that the logic and / or steps represented in the flowchart or described in other ways herein, for example, can be considered as a sequenced list of executable instructions for implementing logical functions, and can be specifically implemented in any machine-readable storage medium for use by an instruction execution system, device or equipment (such as a computer-based system, a system including a processor or other system that can fetch instructions from an instruction execution system, device or equipment and execute instructions), or used in combination with these instruction execution systems, devices or equipment.

[0098] For the purposes of the description of this embodiment, the machine-readable storage medium 40 can be any device that can contain, store, communicate, propagate, or transmit a program for use with an instruction execution system, device, or apparatus, or in conjunction with such instruction execution systems, devices, or apparatuses. More specific examples (a non-exhaustive list) of computer-readable media include the following: an electrical connection having one or more wirings (electronic devices), a portable computer disk cartridge (magnetic devices), a random access memory (RAM), a read-only memory (ROM), an erasable and editable read-only memory (EPROM or flash memory), a fiber optic device, and a portable compact disc read-only memory (CDROM). In addition, the computer-readable medium 40 can even be paper or other suitable media on which the program can be printed, since the program can be obtained electronically, for example, by optically scanning the paper or other media, followed by editing, deciphering, or processing in other suitable ways as necessary, and then stored in a computer memory.

[0099] It should be understood that each part of the present invention can be implemented by hardware, software, firmware or a combination thereof. In the above embodiments, multiple steps or methods can be implemented by software or firmware stored in a memory and executed by a suitable instruction execution system.

[0100] The computer device 50 can be, for example, a server, a desktop computer, a laptop computer, a tablet computer, or a smartphone. In some examples, the computer device 50 can be a cloud computing node. The computer device 50 can be described in the general context of computer system executable instructions (such as program modules) executed by a computer system. Generally, program modules can include routines, programs, object programs, components, logic, data structures, etc. that perform specific tasks or implement specific abstract data types. The computer device 50 can be implemented in a distributed cloud computing environment where remote processing devices linked via a communication network perform tasks. In a distributed cloud computing environment, program modules can be located on local or remote computing system storage media, including storage devices.

[0101] The computer device 50 may include a processor 510 adapted to execute stored instructions, and a memory 520 that provides temporary storage for the instructions during operation. The processor 510 may be a single-core processor, a multi-core processor, a computing cluster, or any number of other configurations. The memory 520 may include random access memory (RAM), read-only memory, flash memory, or any other suitable storage system.

[0102] The processor 510 can be connected to an I / O interface (input / output interface) suitable for connecting the computer device 50 to one or more I / O devices (input / output devices) via a system interconnect (e.g., PCI, PCI-Express, etc.). The I / O devices may include, for example, a keyboard and a pointing device, wherein the pointing device may include a touchpad or a touch screen, etc. The I / O devices may be built-in components of the computer device 50, or may be devices externally connected to the computing device.

[0103] Processor 510 can also be linked to the display interface that is suitable for connecting computer device 50 to display device through system interconnection.Display device can include the display screen that is built-in component of computer device 50.Display device can also include the computer monitor, television or projector etc. that are externally connected to computer device 50.In addition, network interface controller (network interface controller, NIC) can be suitable for connecting computer device 50 to network through system interconnection.In certain embodiments, NIC can use any suitable interface or protocol (such as Internet Small Computer System Interface etc.) to transmit data.Network can be cellular network, radio network, wide area network (WAN)), local area network (LAN) or Internet etc.Remote device can be connected to computing device through network.

[0104] The flowchart provided in this embodiment is not intended to indicate that the operations of the method will be performed in any particular order, or that all operations of the method are included in all every case. In addition, the method may include additional operations. Within the scope of the technical ideas provided by the method of this embodiment, additional changes can be made to the above method.

[0105] At this point, those skilled in the art will recognize that, although a number of exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications consistent with the principles of the present invention may be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the present invention. Therefore, the scope of the present invention should be understood and deemed to cover all such other variations or modifications.

Claims

1. A method for selecting a multi-table join execution plan, comprising: Get the specified operation set by the operator; Setting a materialized connection identifier for the table where the specified operation is located; Using a recursive algorithm, setting the materialized connection identifier on a connection table that uses the table with the materialized connection identifier as a left and right node; Get the execution plan of the connection table; Determining whether the connection table is configured with the materialized connection identifier; In the case where the connection table is configured with a materialized connection identifier, determining whether any child node participating in the connection meets the set candidate condition; If yes, add the execution plan to the candidate queue; After the execution plans for the multi-table connection are enumerated, a final execution plan is selected according to the execution costs of the execution plans in the candidate queue.

2. The method for selecting a multi-table join execution plan according to claim 1, wherein the step of setting the materialized join identifier for the join table having the table with the materialized join identifier as the left and right nodes using a recursive algorithm comprises: Get the pending table that needs to be set up; Determining whether the table to be set is a connection table; If so, determine whether any of the tables where the left and right nodes of the table to be set are connected have the materialized connection identifier; In the case that any one of the tables where the left and right nodes of the table to be set are connected has the materialized connection identifier, the materialized connection identifier is set for the table to be set.

3. The method for selecting a multi-table join execution plan according to claim 2, wherein: The setting of the candidate conditions includes: the table where the child node is located has the materialized connection identifier, and In the case where the table where any child node participating in the connection is located has the materialized connection identifier, it is determined that the set candidate condition is satisfied.

4. The method for selecting a multi-table join execution plan according to claim 2, wherein: After the step of determining whether any one of the tables where the left and right nodes of the table to be set are connected has the materialized connection identifier, if neither of the tables where the left and right nodes of the table to be set are connected has the materialized connection identifier, the method further includes: Determine whether any of the left and right nodes involved in the connection of the table to be set is a controlled materialized node; If so, the materialized connection identifier is set for the table to be set, and the materialized channel identifier is set for the table where the controlled materialized node is located.

5. The method for selecting a multi-table join execution plan according to claim 4, wherein: The setting alternative conditions include: the table where the child node is located has the materialized channel identifier, and In the case where the table where any child node participating in the connection is located has the materialized channel identifier, it is determined that the set candidate condition is satisfied.

6. A machine-readable storage medium having a machine-executable program stored thereon, wherein when the machine-executable program is executed by a processor, the method for selecting a multi-table join execution plan according to any one of claims 1 to 5 is implemented.

7. A computer device comprising a memory, a processor, and a machine executable program stored in the memory and running on the processor, wherein the processor implements the method for selecting a multi-table join execution plan according to any one of claims 1 to 5 when executing the machine executable program.

Images