A connection optimization method, product, medium and device

Abstract

The present application relates to database technology, in particular to a connection optimization method, product, medium and equipment. The connection optimization method comprises: identifying multiple hash connection nodes referring to the same base table according to a query plan, and obtaining the connection condition and the filtering condition of each hash connection node; determining whether the multiple hash connection nodes meet the preset reuse condition according to the connection condition and the filtering condition; if yes, setting the target role of each hash connection node according to the filtering condition, the target role comprising a basic role, an incremental role or a reuse role; constructing or reusing a shared hash table according to the target role of the hash connection node, and performing a connection operation. The present application realizes the hash table sharing between the multiple hash connection nodes corresponding to the same base table by identifying the reusable hash connection nodes and assigning differentiated roles, avoids repeatedly constructing the hash table for the same base table in multiple hash connection nodes, significantly reduces the construction overhead, and improves the execution efficiency.

Description

Technical Field

[0001] This invention relates to database technology, and in particular to a connection optimization method, product, medium, and device. Background Technology

[0002] In existing database systems, hash joins are widely used to perform equi-joins. The classic process involves building a hash table for the inner table and then probing the hash table row by row in the outer table for join matching. When the same table is referenced multiple times in a query plan, the system builds a separate hash table for each hash join. The executor scans the same table multiple times and builds multiple hash tables with the same structure but different instances.

[0003] However, existing hash join methods suffer from the problem of repeatedly scanning the same base table, generating significant unnecessary disk I / O and memory I / O overhead. Furthermore, building a separate hash table for each hash join requires repeatedly calculating hash values, wasting CPU resources. Additionally, existing hash join methods also involve repeatedly allocating memory to manage multiple independent hash tables, leading to a rapid increase in memory usage. Therefore, existing hash join methods are inefficient in scenarios where hash tables are built multiple times on the same data source, failing to meet the performance requirements of complex query scenarios. Summary of the Invention

[0004] In view of the above problems, a connection optimization method, product, medium and device are proposed to overcome the above problems or at least partially solve the above problems.

[0005] One object of the present invention is to provide a connection optimization method to reduce construction overhead, thereby improving execution efficiency.

[0006] A further objective of this invention is to provide clear and quantifiable criteria for reusability determination, thereby improving the accuracy of the determination.

[0007] Another further objective of this invention is to improve the accuracy of role settings, thereby further enhancing execution efficiency.

[0008] Specifically, the present invention provides a connection optimization method, comprising: Identify multiple hash join nodes that reference the same base table based on the query plan, and obtain the join conditions and filter conditions for each hash join node; Determine whether multiple hash connection nodes meet the preset reuse conditions based on connection and filtering conditions; If so, then set the target role for each hash connection node according to the filtering conditions, where the target role includes a basic role, an incremental role, or a reuse role; Based on the target role of the hash connection node, a shared hash table is constructed or reused, and a connection operation is performed.

[0009] Optionally, the connection conditions include a connection key; and the step of determining whether multiple hash connection nodes meet the preset reuse conditions based on the connection conditions and filtering conditions includes: Determine in turn whether the connection keys of multiple hash connection nodes are the same and whether the data ranges limited by the filtering conditions of multiple hash connection nodes have an intersection; If all the judgment results are yes, it is determined that multiple hash connection nodes meet the reuse condition; If any judgment result is negative, it is determined that multiple hash connection nodes do not meet the reuse condition.

[0010] Optionally, the steps of setting the target role of each hash connection node according to the filtering conditions include: Set the hash join node that meets the reuse conditions and is the first to scan the base table as the basic role; Based on the filtering criteria, other hash connection nodes that meet the reuse criteria are set as incremental roles or reuse roles.

[0011] Optionally, the step of setting other hash connection nodes that meet the reuse conditions as incremental or reuse roles based on the filtering conditions includes: Hash connection nodes whose data ranges for filtering conditions overlap with but are not fully included in the data ranges for filtering conditions of the basic role are set as incremental roles; Set the hash connection node whose data range of the filtering conditions is completely contained within the data range of the filtering conditions of the base role and / or the incremental role as a reuse role.

[0012] Optionally, the shared hash table includes a base hash table and an incremental hash table; and the steps of constructing or reusing the shared hash table according to the target role of the hash connection node include: For hash connection nodes set as basic roles, a basic hash table is constructed based on the base table and the filtering conditions of the basic roles; For hash connection nodes set as incremental roles, an incremental hash table is constructed based on the base table, the filtering conditions of the basic roles, and the filtering conditions of the incremental roles. For hash connection nodes set to reuse roles, reuse the base hash table and / or the incremental hash table.

[0013] Optionally, the steps for constructing the basic hash table include: A basic hash table is constructed based on the data in the base table that meets the filtering conditions for the basic roles; and The steps to build an incremental hash table include: An incremental hash table is constructed based on data in the base table that does not meet the filtering conditions for basic roles but meets the filtering conditions for incremental roles.

[0014] Optionally, the steps for performing the connection operation include: For hash connection nodes set as basic roles, the basic hash table is used for connection matching. For hash connection nodes set as incremental or reuse roles, connection matching is performed using the basic hash table and the incremental hash table in sequence.

[0015] According to another aspect of the present invention, a computer program product is also provided, comprising a computer program that, when executed by a processor, implements the steps of any of the above-described connection optimization methods.

[0016] According to another aspect of the present invention, a computer-readable storage medium is also provided, on which a computer program is stored, wherein the computer program, when executed by a processor, implements the steps of any of the above-described connection optimization methods.

[0017] According to another aspect of the present invention, a computer device is also provided, which includes a memory, a processor, and a computer program stored in the memory, wherein the processor executes the computer program to implement the steps of any of the above-described connection optimization methods.

[0018] The connection optimization method of this invention automatically detects scenarios involving repeated hash table construction by identifying multiple hash connection nodes referencing the same base table and determining whether these nodes meet preset reuse conditions based on their connection and filtering conditions. Furthermore, this method sets target roles for each hash connection node based on filtering conditions and constructs or reuses a shared hash table based on these roles, enabling multiple hash connection nodes corresponding to the same base table to perform hash connections using the shared hash table. Therefore, the connection optimization method of this invention, by identifying reusable hash connection nodes and assigning differentiated roles, achieves hash table sharing among multiple hash connection nodes corresponding to the same base table, avoiding repeated hash table construction across multiple hash connection nodes, significantly reducing scan counts and construction overhead, improving connection performance, thereby increasing execution efficiency and ultimately improving the overall system performance in complex query scenarios.

[0019] Furthermore, the connection optimization method of the present invention, by sequentially judging whether the connection keys of multiple hash connection nodes are the same and whether the data ranges limited by the filtering conditions of multiple hash connection nodes have an intersection, and only determining that the reuse condition is met when all the judgment results are yes, provides a clear and quantifiable judgment basis for reusability judgment, thereby improving the judgment accuracy and ensuring the reliability and correctness of the sharing mechanism.

[0020] Furthermore, the connection optimization method of the present invention sets the hash connection node that meets the reuse conditions and is the first to scan the base table as the basic role, ensuring that the basic hash table can be built as early as possible for subsequent nodes to reuse. By setting other hash connection nodes that meet the reuse conditions as incremental roles or reuse roles according to filtering conditions, the method achieves clear role division in the shared hash table scenario, improves the accuracy of role setting, and thus further improves execution efficiency.

[0021] The above and other objects, advantages and features of the present invention will become more apparent to those skilled in the art from the following detailed description of specific embodiments of the invention in conjunction with the accompanying drawings. Attached Figure Description

[0022] The following sections will describe some specific embodiments of the invention in detail by way of example and not limitation, with reference to the accompanying drawings. The same reference numerals in the drawings denote the same or similar parts or portions. Those skilled in the art should understand that these drawings are not necessarily drawn to scale. In the drawings: Figure 1 This is a schematic diagram of a connection optimization method according to an embodiment of the present invention; Figure 2 This is a control flowchart of a connection optimization method according to an embodiment of the present invention; Figure 3 This is a schematic diagram of a computer program product according to an embodiment of the present invention; Figure 4 This is a schematic diagram of a computer-readable storage medium according to an embodiment of the present invention; Figure 5 This is a schematic block diagram of a computer device according to an embodiment of the present invention. Detailed Implementation

[0023] Exemplary embodiments of the invention will now be described in more detail with reference to the accompanying drawings. While exemplary embodiments of the invention are shown in the drawings, it should be understood that the invention may be implemented in various forms and should not be limited to the embodiments set forth herein. Rather, these embodiments are provided so that this disclosure will be thorough and complete, and will fully convey the scope of the invention to those skilled in the art.

[0024] To address the above problems, this invention proposes a connection optimization method. Figure 1 This is a schematic diagram of a connection optimization method according to an embodiment of the present invention. Figure 1 As shown, the connection optimization method in this embodiment generally includes: Step S102: Identify multiple hash join nodes referencing the same base table based on the query plan, and obtain the join condition and filter condition for each hash join node. It should be noted that the query plan in this embodiment is a plan tree generated based on the query statement. Specifically, the plan tree includes multiple nodes, each used to perform a specific database operation. When the query statement references the same base table multiple times, multiple hash join nodes referencing that base table will be generated in the plan tree. Furthermore, the join condition defines the matching rules for hash joins, and the filter condition limits the range of data to be processed when scanning the base table.

[0025] Step S104: Determine whether multiple hash join nodes meet the preset reuse conditions based on the join and filter conditions. If so, proceed to step S106. In this embodiment, the preset reuse conditions can be pre-set and stored in the database. If multiple hash join nodes meet the preset reuse conditions, it means that multiple hash join nodes can use a shared hash table, and the subsequent process can continue. If multiple hash join nodes do not meet the preset reuse conditions, it means that multiple hash join nodes cannot use a shared hash table, and the target role setting step can be skipped, and a separate hash table can be built for each hash join node based on the existing hash join method.

[0026] Step S106: Set the target role for each hash join node according to the filtering conditions. The target role includes a basic role, an incremental role, or a reuse role. In this embodiment, the optimizer can set the corresponding table role for each hash join node based on the filter conditions. Furthermore, the basic role can be denoted as the BASE role, the incremental role as the DELTA role, and the reuse role as the REUSE role.

[0027] Step S108: Construct or reuse a shared hash table based on the target role of the hash connection node, and perform a join operation. Specifically, the shared hash table may include a base hash table and a delta hash table. That is, during the construction phase, the executor can use different scanning strategies for different target roles based on the target roles determined by the optimizer; during the probing phase, different probing strategies are used for different target roles.

[0028] The join optimization method of this invention identifies multiple Hash Join nodes referencing the same base table and determines whether these nodes meet preset reuse conditions based on the Join and Filter conditions of each Hash Join node, thus achieving automatic detection of scenarios involving repeated Hash Table construction. Furthermore, the join optimization method of this invention also sets the target role of each Hash Join node according to the Filter conditions and constructs or reuses a shared hash table based on the target role of the Hash Join nodes, enabling multiple Hash Join nodes corresponding to the same base table to perform hash joins using the shared hash table.

[0029] Therefore, the connection optimization method of this invention, by identifying reusable Hash Join nodes and assigning differentiated roles, realizes hash table sharing among multiple Hash Join nodes corresponding to the same base table, avoids the repeated construction of Hash Table in multiple Hash Join nodes for the same base table, significantly reduces the number of scans and construction overhead, improves connection performance, and thus improves execution efficiency.

[0030] In some embodiments, step S102 may include the following steps: the optimizer traverses each node of the plan tree, identifies all Hash Join nodes that reference the same base table, and collects the Join conditions and Filter conditions for each Hash Join node that references the same base table.

[0031] In other words, the optimizer examines the internal table references of each Hash Join node to determine whether multiple nodes reference the same base table, thus identifying multiple references to the same source table. For Hash Join nodes referencing the same base table, the optimizer further records the Join and Filter conditions for each hash join. Additionally, the optimizer can record the join type for each hash join, which is used for subsequent join operations.

[0032] Therefore, the connection optimization method of this invention achieves automatic discovery of shareable scenarios by traversing the plan tree and identifying Hash Join nodes that reference the same base table through the optimizer. It also provides an accurate basis for subsequent reusability judgment by collecting the Join conditions and Filter conditions of each node, thereby improving the accuracy of subsequent role allocation and hash table sharing.

[0033] In some embodiments, the Join condition for each Hash Join node may include a join key, and different Hash Join nodes may have the same or different join keys. Based on this, step S104 above may include the following steps: sequentially determining whether the join keys of multiple Hash Join nodes are the same and whether there is an intersection between the data ranges defined by the Filter conditions of multiple Hash Join nodes; if the determination results are both yes, determining that multiple Hash Join nodes meet the reuse condition; if any determination result is no, determining that multiple Hash Join nodes do not meet the reuse condition.

[0034] For example, in the query plan, all Hash Join nodes referencing the same base table include nodes l1, l2, and l3. If the filter conditions for nodes l1 and l3 are both l_receiptdate > l_commitdate, and node l2 has no filter condition, it means that the filter conditions for nodes l1 and l3 are the same, i.e., the data ranges defined by their filter conditions overlap. The data range defined by the filter condition of node l2 completely includes the data ranges defined by the filter conditions of nodes l1 and l3, so there is also an overlap.

[0035] For example, if the filter condition of node l1 is "age>18 and age<25" and the filter condition of node l2 is "age>20 and age<30", it means that the data ranges defined by the filter conditions of node l1 and node l2 have an intersection (20,25).

[0036] In the example above, if the connection key of node l1 is l_suppkey, and the connection keys of nodes l2 and l3 are both l_orderkey, then the connection keys of nodes l1, l2, and l3 are different, and node l1 does not meet the reuse condition. However, the connection keys of nodes l2 and l3 are the same, and the reuse condition is met.

[0037] Additionally, if the connection keys of nodes l1, l2, and l3 are all l_orderkey, then the connection keys of nodes l1, l2, and l3 are the same, satisfying the reuse condition, and they can be used together in a shared hash table.

[0038] Therefore, the connection optimization method of this invention, by sequentially judging whether the connection keys of multiple Hash Join nodes are the same and whether the data ranges limited by the Filter conditions of multiple Hash Join nodes have an intersection, and only determining that the reuse condition is met when all the judgment results are yes, provides a clear and quantifiable judgment basis for reusability judgment, thereby improving the judgment accuracy and ensuring the reliability and correctness of the sharing mechanism.

[0039] In some embodiments, after step S104 above, the join optimization method of the present invention may further include the following steps: each Hash Join node constructs a Hash Table and performs a join operation.

[0040] In other words, the optimizer only determines that multiple Hash Join nodes can use a shared hash table if the join key and filter conditions of multiple Hash Join nodes all meet the reuse conditions. Otherwise, each Hash Join node still needs to build its own hash table, thus ensuring the stability of the overall system performance.

[0041] In some embodiments, step S106 may include the following steps: setting the Hash Join node that meets the reuse conditions and is the first to scan the base table as the BASE role; setting other Hash Join nodes that meet the reuse conditions as the DELTA role or REUSE role according to the Filter conditions. It should be noted that among multiple Hash Join nodes that meet the reuse conditions, there is one and only one BASE role, while there can be zero or one or more DELTA and REUSE roles.

[0042] Specifically, for a set of Hash Join nodes that meet the reuse criteria, the optimizer, according to the scan order in the execution plan, designates the first Hash Join node that scans the same source base table as the BASE role. For other Hash Join nodes used for the same source base table, the optimizer further sets them to the DELTA role or the REUSE role based on the data range relationship between their filter conditions and the filter conditions of the BASE role.

[0043] For example, if a set of Hash Join nodes that meet the reuse conditions includes nodes l1, l2, and l3 in the scan order in the execution plan, then the first node l1 that scans the same source base table can be directly identified as the BASE role. Then, based on the data range relationship between the filter conditions of nodes l2 and l3 and the filter conditions of the BASE role, nodes l2 and l3 can be set as the DELTA role or the REUSE role, respectively.

[0044] Therefore, the connection optimization method of this invention, by setting the Hash Join node that meets the reuse conditions and is the first to scan the base table to the BASE role, ensures that the Base Hash Table can be built as early as possible for subsequent nodes to reuse. By setting other Hash Join nodes that meet the reuse conditions to the DELTA role or REUSE role according to the Filter conditions, it achieves clear role division in the shared hash table scenario, improves the accuracy of role setting, thereby further improving execution efficiency and thus improving the overall system performance in complex query scenarios.

[0045] Furthermore, the step of setting other Hash Join nodes that meet the reuse conditions as DELTA or REUSE roles based on the Filter conditions may include the following steps: setting Hash Join nodes whose data range of the Filter conditions intersects with the data range of the Filter conditions of the BASE role but is not fully included as DELTA roles; setting Hash Join nodes whose data range of the Filter conditions is fully included by the data range of the Filter conditions of the BASE role and / or the DELTA role as REUSE roles.

[0046] Specifically, the statement that the data range of the Filter condition in the above steps is completely included by the data range of the Filter condition of the BASE role and / or the DELTA role means that: the data range of the Filter condition of the REUSE role is completely included by the data range of the Filter condition of the BASE role and any one of the DELTA roles; or the data range of the Filter condition of the REUSE role is completely included by the union of the data ranges of the Filter conditions of any number of the BASE role and any number of the DELTA roles.

[0047] For example, node l1's filter condition is "age>18 and age<25", node l2's filter condition is "age>20 and age<30", and node l3's filter condition is "age>19 and age<28". After determining node l1 as the BASE role, since the data range of node l2's filter condition intersects with the data range of the BASE role's filter condition (20,25) but is not completely contained, node l2 is set to the DELTA role. The data range [25,30) that is not completely contained can be used to construct the Delta Hash Table. Since the data range of node l3's filter condition (19,28) is completely contained by the union of the data ranges of node l1 and node l2's filter conditions (18,30), node l3 is set to the REUSE role.

[0048] Therefore, the connection optimization method of this invention further divides the DELTA role and REUSE role by using the data range based on the Filter condition, thereby achieving a clear division of roles in the shared hash table scenario and further improving the accuracy of role settings.

[0049] In some embodiments, the step S108 above, which involves constructing or reusing a shared hash table based on the target role of the Hash Join node, may include the following steps: for a Hash Join node set to the BASE role, constructing a Base Hash Table based on the base table and the filter conditions of the BASE role; for a Hash Join node set to the DELTA role, constructing a Delta Hash Table based on the base table, the filter conditions of the BASE role, and the filter conditions of the DELTA role; for a Hash Join node set to the REUSE role, reusing the Base Hash Table and / or the Delta Hash Table. In other words, the executor can execute different construction strategies based on the target role assigned by the optimizer.

[0050] Specifically, the steps for constructing the Base Hash Table described above may include the following: The BASE role constructs the Base Hash Table based on the data in the base table that meets the BASE role's filter conditions. In other words, the BASE role only scans the data in the base table that meets the BASE role's filter conditions and constructs the Base Hash Table accordingly.

[0051] Furthermore, the steps for constructing the Delta Hash Table described above may include the following: constructing the Delta Hash Table based on data in the base table that does not meet the BASE role's filter conditions but meets the DELTA role's filter conditions. Thus, the Delta Hash Table and the Base Hash Table have distinct and independent data, minimizing the Delta Hash Table's memory footprint. In other words, the DELTA role only scans data in the base table that does not meet the BASE role's filter conditions but meets the DELTA role's filter conditions, and constructs the Delta Hash Table accordingly.

[0052] In addition, REUSE role nodes do not perform any scanning or building operations and can be directly looked up in the Base HashTable and / or Delta Hash Table.

[0053] For example, the filter condition for the BASE role is "age>18 and age<25", the filter condition for the DELTA role is "age>20 and age<30", and the filter condition for the REUSE role is "age>19 and age<28". The BASE role builds a Base Hash Table based on data falling within the range (18,25) in the base table, the DELTA role builds a Delta Hash Table based on data falling within the range [25,30) in the base table, and the REUSE role can directly reuse the existing Base Hash Table and Delta Hash Table.

[0054] Therefore, the connection optimization method of this invention achieves differentiated execution for different roles by constructing a Base Hash Table for the BASE role, a Delta Hash Table for the DELTA role, and directly reusing the shared hash table for the REUSE role. This significantly reduces the number of scans and construction overhead, improves connection performance, and thus improves execution efficiency.

[0055] In some embodiments, the step of performing the join operation in step S108 above may include the following steps: for Hash Join nodes set to the BASE role, use the Base Hash Table for join matching; for Hash Join nodes set to the DELTA role or REUSE role, use the Base Hash Table and the Delta Hash Table for join matching in sequence.

[0056] Specifically, during the probing phase, the BASE role directly probes its constructed Base Hash Table for join matching. If a match is found, the join result is output. The DELTA and REUSE roles, on the other hand, need to perform two probes: first, they probe the Base Hash Table for join matching. If a match is found, the join result is output directly; if no match is found, they continue to probe the Delta Hash Table for join matching. If a match is found, the join result is output.

[0057] In addition, the above-mentioned steps for performing connection matching can be specifically executed as performing connection matching based on the Join type of the Hash Join node.

[0058] Therefore, the join optimization method of this invention, by using a Base HashTable separately for the BASE role and using the Base Hash Table and Delta Hash Table sequentially for the DELTA role and REUSE role for join matching, achieves efficient reuse of the same set of shared hash tables by multiple Hash Join nodes while ensuring the correctness of the join results, thereby significantly improving the execution performance of complex queries.

[0059] In one specific embodiment, the join optimization method of the present invention introduces a shared hash table planning module at the optimizer layer. This module analyzes the plan tree at the query optimization node and determines whether it is suitable to construct a Base Hash Table or a Delta Hash Table based on the subquery structure, the same-origin table reference relationship, and the join type. The specific join optimization strategy is as follows: 1. Optimizer decision-making process: (1) Identify multiple references to the same source table: The optimizer traverses the plan tree, collects all nodes that scan the same base table, and records the filter conditions, join conditions, and join types; (2) Determine if there are reusable conditions: For two or more Hash Join nodes, the optimizer sequentially determines whether the same table is scanned, whether the same join key is used, and whether there is an intersection of Filter conditions; (3) Set table roles: Set the Hash Join node of the first scan of the same base table to BASE, set it to DELTA for tables with larger ranges, and set it to REUSE for other tables; 2. During the construction phase, starting with the first base table scan, the executor determines the role of the Hash Join based on the optimizer and uses different scanning strategies: BASE role: Scans the base table and constructs the Base Hash Table normally; DELTA role: For data in the Base Hash Table, skip it; for data not in the Base Hash Table, insert it into the Delta Hash Table. REUSE role: Skip build operations; 3. During the probing phase, the optimizer determines the role of Hash Join and uses different probing strategies: BASE role: Probe the Base Hash Table; DELTA role: Search in the Base Hash Table and Delta Hash Table respectively; REUSE role: If a Delta Hash Table exists, search in both the Base Hash Table and the Delta Hash Table; or, if a Delta Hash Table does not exist, search in the Base Hash Table.

[0060] For example, in TPC-H's Q21, the Lineitem table requires the construction of three HashTables, and its partial execution plan is as follows: Among them, all Hash Join nodes referencing the same Lineitem table include nodes l1, l2, and l3, which are executed sequentially. The join key for node l1 is l_suppkey, while the join keys for nodes l2 and l3 are both l_orderkey. The filter condition for nodes l1 and l3 is l_receiptdate > l_commitdate, while node l2 has no filter condition.

[0061] In this example, the connection keys of nodes l1, l2, and l3 are different; therefore, node l1 does not meet the reuse condition. Nodes l2 and l3 have the same connection key, and the data range defined by the filter condition of node l2 completely includes the data range defined by the filter condition of node l3, thus there is an intersection; therefore, nodes l2 and l3 meet the reuse condition.

[0062] Based on this, among multiple Hash Join nodes that meet the reuse conditions, node l2 acts as BASE and builds the Base Hash Table; node l3 acts as REUSE and reuses the Base Hash Table.

[0063] For example, if the connection key of node l1 in the above example is also l_orderkey, then node l1 can act as BASE to build the Base Hash Table; node l2 can act as DELTA to build the Delta Hash Table; and node l3 can act as REUSE to reuse the Bash Hash Table.

[0064] Therefore, the connection optimization method of this invention realizes the construction of a shared hash table by analyzing the characteristics of the same base table, the filter conditions of multiple Hash Join nodes and the join key, etc., avoiding the repeated construction of the Hash Table in multiple Hash Join nodes for the same base table, and significantly reducing the number of scans and construction overhead.

[0065] Furthermore, the connection optimization method in this embodiment of the invention also improves the scanning and probing efficiency by determining the target role of each Hash Join node and using different scanning and probing strategies according to the target role, thereby improving connection performance and ultimately improving the overall system performance in complex query scenarios.

[0066] Figure 2 This is a control flowchart of a connection optimization method according to an embodiment of the present invention. The following is in conjunction with… Figure 2 The process steps of this embodiment will be described in detail below.

[0067] In step S202, the optimizer identifies multiple Hash Join nodes referencing the same base table based on the query plan, and obtains the Join condition, Filter condition, and Join type for each Hash Join node. It should be noted that the Join condition may include the join key.

[0068] Step S204: Determine whether the join keys of multiple Hash Join nodes are the same. If yes, proceed to step S206; otherwise, proceed to step S222.

[0069] Step S206: Determine whether there is an intersection between the data ranges defined by the Filter conditions of multiple Hash Join nodes. If yes, proceed to step S208; otherwise, proceed to step S222.

[0070] Step S208: Based on the Filter conditions, multiple Hash Join nodes that meet the reuse conditions are divided into BASE, DELTA, or REUSE roles. Specifically, the Hash Join node that meets the reuse conditions and is the first to scan the base table is set to the BASE role; the Hash Join node whose data range of the Filter conditions intersects with the data range of the BASE role's Filter conditions but is not completely included is set to the DELTA role; and the Hash Join node whose data range of the Filter conditions is completely included by the data range of the BASE role and / or the DELTA role's Filter conditions is set to the REUSE role.

[0071] In step S210, the executor identifies the role of the Hash Join node. If the Hash Join node is the BASE role, then step S212 is executed. If the Hash Join node is the DELTA role, then step S216 is executed. If the Hash Join node is the REUSE role, then step S220 is executed.

[0072] Step S212: Construct a Base Hash Table based on the data in the base table that meets the Filter conditions for the BASE role, and then execute step S214.

[0073] Step S214: Probe the Base Hash Table and perform join matching based on the Join type. This concludes the current process.

[0074] Step S216: Construct a Delta Hash Table based on the data in the base table that does not meet the Filter conditions for the BASE role but meets the Filter conditions for the DELTA role, and then execute step S218.

[0075] Step S218: Probe the Base Hash Table and Delta Hash Table sequentially, and perform join matching based on the Join type. This concludes the current process.

[0076] Step S220: Probe the Base Hash Table and / or Delta Hash Table, and perform join matching based on the Join type. This concludes the current process.

[0077] In step S222, each Hash Join node constructs a Hash Table and performs a join operation. This concludes the current process.

[0078] Therefore, the connection optimization method of this invention, by identifying reusable Hash Join nodes and assigning differentiated roles, realizes hash table sharing among multiple Hash Join nodes corresponding to the same base table, avoids the repeated construction of Hash Table in multiple Hash Join nodes for the same base table, significantly reduces the number of scans and construction overhead, improves connection performance, thereby improving execution efficiency and ultimately improving the overall system performance in complex query scenarios.

[0079] This embodiment also provides a computer program product 10, a computer-readable storage medium 20, and a computer device 30. Figure 3 This is a schematic diagram of a computer program product 10 according to an embodiment of the present invention. Figure 4 This is a schematic diagram of a computer-readable storage medium 20 according to an embodiment of the present invention. Figure 5 This is a schematic block diagram of a computer device 30 according to an embodiment of the present invention.

[0080] Computer program product 10 includes computer program 11, which, when executed by processor 32, implements the steps of any of the above-described connection optimization methods. Computer-readable storage medium 20 stores the computer program 11 thereon, which, when executed by processor 32, implements the steps of the connection optimization method of any of the above embodiments. Computer device 30 may include memory 31, processor 32, and computer program 11 stored in memory 31 and running on processor 32.

[0081] The computer program 11 used to perform the operations of the present invention may be assembly instructions, instruction set architecture (ISA) instructions, machine instructions, machine-related instructions, microcode, firmware instructions, status setting data, integrated circuit configuration data, or source code or object code written in any combination of one or more programming languages ​​and procedural programming languages.

[0082] Computer program 11 may execute entirely on the user's computer, partially on the user's computer, as a standalone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In the latter case, the remote computer may be connected to the user's computer via any type of network, including a Local Area Network (LAN) or a Wide Area Network (WAN), or may be connected to an external computer (e.g., via the Internet using an Internet service provider). In some embodiments, to perform aspects of the invention, electronic circuitry including, for example, programmable logic circuitry, a Field-Programmable Gate Array (FPGA), or a Programmable Logic Array (PLA) may execute computer-readable program instructions to personalize the electronic circuitry by utilizing state information of the computer-readable program instructions.

[0083] For the purposes of this embodiment, computer program product 10 is a related product that includes computer program 11.

[0084] For the purposes of this embodiment, computer-readable storage medium 20 is a tangible device capable of holding and storing a computer program 11. It can be any device capable of containing, storing, communicating, propagating, or transmitting the program 11 for use by or in conjunction with an instruction execution system, apparatus, or device. More specific examples (a non-exhaustive list) of computer-readable storage medium 20 include: portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), static random access memory (SRAM), portable optical disc read-only memory (CD-ROM), digital versatile disc (DVD), memory stick, floppy disk, mechanical encoding device, and any suitable combination thereof.

[0085] Therefore, those skilled in the art should recognize that although numerous exemplary embodiments of the present invention have been shown and described in detail herein, many other variations or modifications conforming to the principles of the present invention can be directly determined or derived from the disclosure of the present invention without departing from the spirit and scope of the invention. Thus, the scope of the present invention should be understood and construed as covering all such other variations or modifications.

Claims

1. A connection optimization method, comprising: Based on the query plan, identify multiple hash join nodes that reference the same base table, and obtain the join conditions and filtering conditions for each hash join node; Based on the connection conditions and the filtering conditions, determine whether the multiple hash connection nodes meet the preset reuse conditions; If so, then set the target role for each hash connection node according to the filtering conditions, wherein the target role includes a basic role, an incremental role, or a reused role; The shared hash table is constructed or reused according to the target role of the hash connection node, and the connection operation is performed.

2. The connection optimization method according to claim 1, wherein, The connection conditions include a connection key; and the step of determining whether multiple hash connection nodes meet preset reuse conditions based on the connection conditions and the filtering conditions includes: The system sequentially determines whether the connection keys of multiple hash connection nodes are the same and whether the data ranges defined by the filtering conditions of multiple hash connection nodes have an intersection. If all the judgment results are yes, then multiple hash connection nodes are determined to satisfy the reuse condition; If any judgment result is negative, it is determined that multiple hash connection nodes do not meet the reuse condition.

3. The connection optimization method according to claim 1, wherein, The step of setting the target role of each hash connection node according to the filtering conditions includes: Set the hash connection node that meets the reuse conditions and is the first to scan the base table as the basic role; Based on the filtering conditions, other hash connection nodes that meet the reuse conditions are set as incremental roles or reuse roles.

4. The connection optimization method according to claim 3, wherein, The step of setting other hash connection nodes that meet the reuse conditions as incremental or reuse roles according to the filtering conditions includes: The hash connection node whose data range of the filtering conditions intersects with the data range of the filtering conditions of the basic role but is not fully included is set as an incremental role; The hash connection node whose data range of the filtering conditions is completely included by the data range of the filtering conditions of the basic role and / or the incremental role is set as a reuse role.

5. The connection optimization method according to claim 1, wherein, The shared hash table includes a base hash table and an incremental hash table; and the step of constructing or reusing the shared hash table according to the target role of the hash connection node includes: For hash connection nodes set as basic roles, the basic hash table is constructed based on the base table and the filtering conditions of the basic roles; For hash connection nodes set as incremental roles, the incremental hash table is constructed based on the base table, the filtering conditions of the base role, and the filtering conditions of the incremental role. For hash connection nodes set to reuse roles, the base hash table and / or the incremental hash table are reused.

6. The connection optimization method according to claim 5, wherein, The steps for constructing the basic hash table include: The basic hash table is constructed based on the data in the base table that meets the filtering conditions of the basic roles; and The steps for constructing the incremental hash table include: The incremental hash table is constructed based on data in the base table that does not meet the filtering conditions for the basic roles but meets the filtering conditions for the incremental roles.

7. The connection optimization method according to claim 6, wherein, The steps for performing the connection operation include: For hash connection nodes set as basic roles, the basic hash table is used for connection matching. For hash connection nodes set as incremental or reuse roles, the basic hash table and the incremental hash table are used sequentially for connection matching.

8. A computer program product comprising a computer program, wherein, When the computer program is executed by a processor, it implements the steps of the connection optimization method according to any one of claims 1 to 7.

9. A computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the steps of the connection optimization method according to any one of claims 1 to 7.

10. A computer device comprising a memory, a processor, and a computer program stored in the memory, wherein, The processor executes the computer program to implement the steps of the connection optimization method according to any one of claims 1 to 7.

Images