A hash connection processing method, storage medium and device

Abstract

The present invention relates to database technology, and in particular to a hash join processing method, storage medium, and device. The method comprises: calculating the cost of constructing a hash inner table using a first table, wherein the first table is any one of two tables to be joined in a pre-hash join; determining whether rehashing is required when constructing the hash inner table using the first table; if so, calculating an impact factor of rehashing; and correcting the cost of constructing the hash inner table using the first table based on the impact factor. The method of the present invention takes the impact of rehashing into account when constructing the cost of the hash inner table, thereby optimizing the inner and outer table selection algorithm for the hash join, avoiding the significant time consumption caused by rehashing, effectively shortening the overall operation time of the hash join, and thereby effectively improving the overall performance of the database.

Description

Technical Field

[0001] The present invention relates to database technology, and in particular to a hash connection processing method, storage medium and device. Background Art

[0002] When multiple tables are inner joined, a join method called a Hash Join is used. Hash Join selects a hash inner table to construct a hash table, then searches this hash table for matching data for each row of data in the outer table. Its characteristic is that after hashing a smaller table, it is read into memory as much as possible at once, and then loops through each record in the outer table to perform a hash calculation with the hash inner table to see if there is a match. Compared to other join methods (such as NestLoop), the advantage of Hash Join is that if the inner table is hashed and read into memory, a full table scan of the inner table can be avoided each time, thereby reducing the time spent on the join operation.

[0003] Currently, the optimizer's inner and outer table selection algorithm for Inner Hash Joins is as follows: by swapping the join order of the inner and outer tables, two paths are constructed, the costs of each path are calculated, and the path with the lower cost is selected as the planned path. For example, if two tables t1 and t2 are inner hash joined, two paths, t1HashJoin t2 and t2HashJoin t1, are constructed. Based on this, the cost c1 of t1HashJoin t2 is calculated first, followed by the cost c2 of t2HashJoin t1. The path with the lower cost is selected as the hash join path. If c1 < c2, t1HashJoin t2 is selected as the planned path; otherwise, t2HashJoin t1 is selected.

[0004] In most cases, after the inner table is hashed and read into memory, it does not need to be rehashed and read back in. However, in some scenarios, such as the recursive side of a CTE or a hierarchical query, a rehash may be necessary. This is because the join principle in both scenarios is to join the base table with the intermediate result table of each recursive operation. In this case, if the optimizer chooses a hash join, the intermediate result table is usually much smaller than the base table, so the intermediate result table is selected as the inner table for the join. This intermediate result table is essentially different for each recursive operation. Therefore, each recursive hash join causes the inner table to be rehashed because the intermediate result table selected as the hash inner table is different from the hash inner table of the previous recursive hash join. This, in turn, requires a full table scan of the base table, which serves as the outer table, with each recursive join. Since full table scans are very time-consuming, this makes the entire join operation very time-consuming.

[0005] Given the above situation, existing inner and outer table selection algorithms need to be optimized. Therefore, providing an optimization method for Hash Join cost calculation algorithms that can take into account the additional performance impact of rehashing and incorporate the impact of rehashing into the estimated cost of Hash Joins has become an urgent problem to be solved. Summary of the Invention

[0006] An object of the present invention is to provide a hash join processing method that optimizes the inner and outer table selection algorithm of Hash Join to shorten the Hash Join operation time and improve the overall performance of the database.

[0007] A further object of the present invention is to improve the accuracy of calculating the Hash Join path cost, so as to further optimize the inner and outer table selection algorithm of the Hash Join.

[0008] In particular, the present invention provides a method for processing hash joins, comprising:

[0009] Calculate the cost of constructing a hash inner table using the first table, where the first table is any one of the two tables to be joined in the pre-hash join;

[0010] Determine whether the hash inner table constructed using the first table needs to be rehashed;

[0011] If so, calculate the impact factor of the rehashing;

[0012] The cost of constructing the hash inner table using the first table is modified according to the impact factor.

[0013] Optionally, the step of determining whether the hash inner table constructed using the first table needs to be rehashed includes:

[0014] Determine whether the first table is an intermediate result table in a preset scenario;

[0015] If the first table is an intermediate result table in a preset scenario, it is determined that rehashing is required to construct the hash inner table based on the first table.

[0016] Optionally, the preset scenario includes at least: a recursive side of a CTE and a recursive side of a hierarchical query.

[0017] Optionally, the two tables to be connected further include a second table in addition to the first table; and

[0018] The steps to calculate the rehash impact factor are:

[0019] Get the statistics of the second table;

[0020] The rehashing impact factor is calculated based on the statistical information of the second table and a preset function.

[0021] Optionally, the statistical information of the second table is statistical data generated and stored in a statistical information system table after the second table is created and data is inserted, and the statistical information of the second table includes the number of tuples in the second table and the condition selection rate of the second table generating the intermediate result table; and

[0022] The step of calculating the rehashing impact factor based on the statistical information of the second table and a preset function includes:

[0023] Obtaining a linear factor corresponding to the statistical information of the second table, wherein the correspondence between the linear factor and the statistical information of the second table is pre-configured and stored in a database;

[0024] The product of the number of tuples in the second table and the conditional selectivity of the intermediate result table generated by the second table is multiplied by the corresponding linear factor and rounded up, and the rounded result is used as the result of calculating the rehashing impact factor.

[0025] Optionally, the step of correcting the cost of constructing the hash inner table using the first table according to the impact factor includes:

[0026] The product of the rehashing impact factor and the calculated cost of constructing the hash inner table using the first table is used as the revised cost of constructing the hash inner table using the first table.

[0027] Optionally, before the step of calculating the cost of constructing the hash inner table using the first table, the processing method further includes:

[0028] A first path is pre-built, which uses the first table to build a hash inner table and uses the second table as a connection to the outer table, and a second path is pre-built, which uses the second table to build a hash inner table and uses the first table as a connection to the outer table.

[0029] Optionally, after the step of correcting the cost of constructing the hash inner table using the first table according to the impact factor, the processing method further includes:

[0030] Calculate the first cost of selecting the first path for hash join based on the corrected cost of constructing the hash inner table using the first table;

[0031] Obtain a second cost of selecting a second path for hash connection;

[0032] comparing the first price with the second price;

[0033] According to the comparison results, the path with the smaller cost is selected as the planned path for hash connection.

[0034] 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, any of the above-mentioned processing methods is implemented.

[0035] 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 processing methods when executing the machine executable program.

[0036] The processing method of the present invention first calculates the cost of constructing a hash inner table using a first table, where the first table is either of two tables to be joined in a pre-hash join. It then determines whether rehashing is required for constructing the hash inner table using the first table. If rehashing is confirmed to be required under this path for constructing the hash inner table using the first table, the impact factor of rehashing is calculated. Finally, the cost of constructing the hash inner table using the first table is corrected based on the impact factor. By considering the impact of rehashing when constructing the cost of the hash inner table, the processing method of the present invention optimizes the inner and outer table selection algorithm for Hash Join, thereby shortening the overall operation time of Hash Join and thereby improving the overall performance of the database.

[0037] Furthermore, the processing method of the present invention determines whether the first table is an intermediate result table under a preset scenario, and when confirming that the first table is an intermediate result table under the preset scenario, determines that the hash inner table constructed with the first table needs to be rehashed, thereby ensuring the accuracy of the judgment result of whether hashing is required, thereby improving the accuracy of calculating the Hash Join path cost, and further optimizing the inner and outer table selection algorithm of Hash Join.

[0038] 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

[0039] 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:

[0040] Figure 1 is a flowchart of a processing method according to an embodiment of the present invention;

[0041] Figure 2 is a flowchart of a processing method according to another embodiment of the present invention;

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

[0043] Figure 4 FIG. 1 is a schematic structural diagram of a computer device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0044] Exemplary embodiments of the present invention will be described in more detail below with reference to the accompanying drawings. Although exemplary embodiments of the present invention are shown in the accompanying drawings, it should be understood that the present invention can be implemented in various forms and should not be limited by the embodiments set forth herein. Rather, these embodiments are provided to enable a more thorough understanding of the present disclosure and to fully convey the scope of the present invention to those skilled in the art.

[0045] To solve the above technical problems, an embodiment of the present invention provides a method for processing a write-ahead log. Figure 1 FIG is a flow chart of a processing method according to an embodiment of the present invention. Figure 1 As shown, the processing method may generally include:

[0046] Step S102, calculating the cost of constructing a hash inner table using a first table, wherein the first table is any one of the two tables to be connected in the pre-hash connection;

[0047] Step S104, determining whether the hash inner table constructed using the first table needs to be rehashed, if so, executing step S106;

[0048] Step S106, calculating the impact factor of rehashing;

[0049] Step S108: Modify the cost of constructing the hash inner table using the first table according to the impact factor.

[0050] It should be noted that when performing an Inner Hash Join on two tables, the optimizer must first execute the Inner Hash Join's inner and outer table selection algorithm. Specifically, by swapping the join order between the inner and outer tables, two paths are constructed, the costs of each path are calculated, and the path with the lower cost is selected as the planned path. In other words, when performing an Inner Hash Join on two tables, the cost of constructing a hash inner table using each of the two tables to be joined must be calculated separately. Furthermore, when performing an Inner Hash Join on multiple tables, it is also necessary to first perform a hash join on two of the tables to be joined in a predetermined order to generate a result table, which is then hash joined with the next table to be joined. In other words, the Inner Hash Join's inner and outer table selection algorithm executed by the optimizer of the present invention always performs a hash join between the two tables to be joined. Therefore, for ease of explanation, the present invention denotes the hash inner table in the path whose cost is currently being calculated as the first table, and denotes the other table to be joined, other than the first table, included in the two tables to be joined as the second table. That is, the two to-be-connected tables include a first table and a second table, wherein the first table refers to any one of the two to-be-connected tables, and the second table refers to another to-be-connected table except the first table.

[0051] The processing method of this embodiment first calculates the cost of constructing a hash inner table with the first table, wherein the first table is any one of the two tables to be connected in the pre-hash connection, then determines whether rehash is required to construct the hash inner table with the first table, and when it is confirmed that rehash is required under this path of constructing the hash inner table with the first table, calculates the impact factor of rehash, and finally corrects the cost of constructing the hash inner table with the first table according to the impact factor. Using the processing method of this embodiment, the impact of rehash is taken into account when calculating the cost of constructing the hash inner table. If rehash is not required subsequently when constructing the hash inner table with the first table, the cost is not increased. If rehash is required subsequently when constructing the hash inner table with the first table, the cost is increased by a multiple of the rehash factor based on the calculated result of the cost of constructing the hash inner table with the first table, thereby realizing the consideration of the impact of rehash when constructing the cost of the hash inner table, optimizing the inner and outer table selection algorithm of Hash Join, thereby shortening the overall operation time of HashJoin, and further improving the overall performance of the database.

[0052] Before step S102 , the processing method of this embodiment may further include: pre-building a first path that uses the first table to construct a hash inner table and uses the second table as a connection to the outer table, and pre-building a second path that uses the second table to construct a hash inner table and uses the first table as a connection to the outer table.

[0053] After step S108, the processing method of this embodiment may further include: calculating a first cost for selecting the first path for Hash Join based on the corrected cost of constructing the hash inner table using the first table; obtaining a second cost for selecting the second path for Hash Join; comparing the first cost with the second cost; and selecting the path with the smaller cost as the planned path for Hash Join based on the comparison result.

[0054] Using the processing method of this embodiment, when performing inner and outer table selection, if a certain path requires rehashing, the calculation result of the cost of constructing the hash inner table in this path is corrected based on the impact factor of rehashing, and the cost of this path is obtained based on the corrected cost of constructing the hash inner table, thereby increasing the cost of selecting this path for Hash Join. In other words, the processing method of this embodiment increases the cost of a certain path when a certain path requires rehashing, thereby changing the comparison result between this path and other paths based on the size of the cost, thereby avoiding the selection of this path that subsequently requires rehashing for Hash Join, avoiding the need for the base table as the outer table to be re-scanned in full with each recursive Join, thereby shortening the overall operation time of Hash Join, and achieving optimization of the inner and outer table selection algorithm of Hash Join.

[0055] The following describes in detail a method for determining whether a path requires rehashing and a method for correcting the calculation result of the cost of constructing the hash internal table in the path based on the impact factor of rehashing, with reference to some specific embodiments.

[0056] In some embodiments, the process of the above step S104 may include: determining whether the first table is an intermediate result table in a preset scenario; if the first table is an intermediate result table in the preset scenario, determining that the hash inner table constructed with the first table needs to be rehashed. It should be noted that the preset scenario includes at least the recursive side of CTE (Common Table Expression) and the recursive side of hierarchical query. In either of these two preset scenarios, the intermediate result tables generated by each recursion are basically different, so each recursive HashJoin will cause the inner table to be rehashed. Therefore, when it is determined that the first table is an intermediate result table in the preset scenario, it can be determined that the hash inner table constructed with the first table needs to be rehashed. When it is determined that the first table is not an intermediate result table in the preset scenario, it can be determined that the hash inner table constructed with the first table does not need to be rehashed.

[0057] Specifically, the type of the table to be connected can be determined based on the enumeration value of the table. The intermediate result table is a table identified by a special enumeration value in the database kernel. And the special enumeration value is different from the enumeration value of other types of tables. In the database kernel, each type of table has its own unique enumeration value to represent the type of the table. For example, the enumeration values ​​of different types of tables such as intermediate result tables, basic tables, and index tables are different. Therefore, in a specific embodiment, the step of determining whether the first table is an intermediate result table in the above step S104 may include: obtaining the enumeration value of the first table; determining whether the enumeration value of the first table is a special enumeration value indicating that the target type is an intermediate result table; if so, determining that the first table is an intermediate result table; if not, determining that the first table is not an intermediate result table.

[0058] Using the processing method of this embodiment, it is determined whether the first table is an intermediate result table. If it is confirmed that the first table is an intermediate result table, it is determined that the hash inner table constructed based on the first table needs to be rehashed, thereby ensuring the accuracy of the result of determining whether hashing is required, thereby improving the accuracy of calculating the Hash Join path cost, and further optimizing the inner and outer table selection algorithm of Hash Join.

[0059] In the Hash Join of the present invention, the two tables to be joined cannot both be intermediate result tables. Therefore, if the first table is an intermediate result table in a preset scenario, the second table is determined to be a base table. A base table is a table that is permanently stored and actually exists in a database and is a logical representation of the actual stored data. Based on this, the process of step S106 may include: obtaining statistical information of the second table; and calculating a rehash impact factor (rehash_frac) based on the statistical information of the second table and a preset function. Specifically, the calculated number of rehashes n for the intermediate result table is used as rehash_frac in this embodiment. In addition, the statistical information of the base table is statistical data generated and stored in the statistical information system table after the table is created and data is inserted. The statistical information of the table may include the number of tuples N in the base table and the conditional selectivity s of the base table generating the intermediate result table. This information is used to depict the data distribution of each attribute column of the base table from multiple perspectives. The statistical information of the base table can be used to complete the cost estimation and conditional selectivity of the table. In one specific embodiment, the number of tuples N in the base table is the number of rows in the base table excluding the row containing the attribute name. In addition, the applicant has learned from preliminary experiments that the statistical information of the base table is linearly related to the intermediate result table, and its linear factor is denoted as a. Moreover, the correspondence between the linear factor a and the statistical information of the base table can be pre-configured and stored in the database based on the results obtained from the preliminary experiments. On this basis, the step of calculating rehash_frac based on the statistical information of the second table and the preset function in the above step S106 may include: obtaining the linear factor a corresponding to the statistical information of the second table; multiplying the product of the number of tuples N in the second table and the conditional selectivity s of the intermediate result table generated by the second table by the corresponding linear factor a and rounding the result, and using the number of times n the rounded result is rehashed as the result of calculating rehash_frac.

[0060] In a specific embodiment, the above-mentioned step S106 can also be specifically executed as follows: obtaining statistical information of the second table, the statistical information of the second table including the number of tuples N of the second table and the conditional selection rate s of the second table generating the intermediate result table; obtaining the linear factor a corresponding to the statistical information of the second table; calculating the number of rehashes n according to a preset rehashing number n calculation formula, wherein the preset rehashing number n calculation formula is n=[a×N×s]; and using the calculated n as rehash_frac.

[0061] In some embodiments, the process of step S108 may include: multiplying rehash_frac by the calculated cost of constructing the hash inner table using the first table as the revised cost of constructing the hash inner table using the first table. In other words, based on the calculated cost of constructing the hash inner table using the first table, this cost is multiplied by the rehash factor, thereby taking the impact of rehash into account when constructing the hash inner table, thereby optimizing the inner and outer table selection algorithm of the Hash Join.

[0062] Figure 2 This is a flow chart of a processing method according to another embodiment of the present invention. Figure 2 The process steps of this embodiment are described in detail. In this embodiment, the processing method of hash join can be the optimizer's inner and outer table selection algorithm in InnerHash Join. Figure 2 The process steps of this embodiment are described in detail.

[0063] Step S202: Two paths are constructed respectively by exchanging the connection order of the inner and outer tables. It should be noted that each path constructs a hash inner table with any table to be connected and uses the other table to be connected as the outer table.

[0064] Step S204: Calculate the inner and outer table startup costs of the Inner Hash Join.

[0065] Step S206: Calculate the cost of constructing the internal table hash through the current path.

[0066] Step S208: Determine whether repeated rehashing is required. If so, execute step S210; if not, execute step S216. Specifically, situations where repeated rehashing is required include: the current inner table is an intermediate result table on the recursive side of a hierarchical query or an intermediate result table on the recursive side of a CTE.

[0067] In step S210, the number of rehashes n for the intermediate result table is calculated using the statistical information of the base table and the corresponding linear factor a, where n = [a × N × s], where N represents the number of tuples in the base table and s represents the selectivity of the conditions for generating the intermediate result table from the base table. At this point, the current inner table is the intermediate result table, and therefore, the current outer table is the base table.

[0068] Step S212: Use the calculated n as the rehash impact factor rehash_frac.

[0069] In step S214 , rehash_frac is multiplied by the cost of constructing the internal table hash through the current path calculated in step S206 , and the product is used as the revised cost of constructing the internal table hash through the current path.

[0070] Step S216: Complete the calculation of the cost of the current path of the Inner Hash Join.

[0071] Step S218: Obtain the cost calculation result of the other path of the Inner Hash Join, and compare the cost of the current path with the cost of the other path.

[0072] Step S220 : Based on the comparison result, a path with a smaller cost is selected as a planned path for Inner Hash Join.

[0073] In a specific embodiment, two tables T1 and T2 are subjected to an Inner Hash Join. Two paths, a first path and a second path, can be pre-constructed. The first path constructs a hash inner table using T1 and uses T2 as the outer table for the join. The second path constructs a hash inner table using T2 and uses T1 as the outer table for the join. The cost C1 of the first path is calculated, and the cost C2 of the second path is calculated. During the calculation of the cost C1 of the first path, when calculating the cost of constructing the hash inner table using T1, it is necessary to determine whether T1 is an intermediate structure table. If T1 is an intermediate structure table, rehash_frac is calculated, and the calculated cost of constructing the hash inner table using T1 is multiplied by rehash_frac. The resulting product is used as the corrected cost of constructing the hash inner table using T1. The corrected cost of constructing the hash inner table using T1 is then combined with the calculated inner and outer table startup costs of the Inner Hash Join according to a preset calculation formula to obtain the cost C1 of the first path. It should be noted that the preset calculation formula is not further limited in this invention; it is sufficient to ensure that the residual cost calculation of the Inner Hash Join can be completed according to the preset calculation formula. At the same time, in the process of calculating the cost C2 of the first path, when calculating the cost of constructing the hash inner table with T2, it is also necessary to determine whether T2 is an intermediate structure table. In the case that T1 is an intermediate structure table, T2 should be the base table, that is, T2 is not an intermediate structure table. If T2 is not an intermediate structure table, the calculation result of the cost of constructing the hash inner table with T2 will not be increased. On this basis, in the case that T1 is an intermediate structure table, the optimized inner and outer table selection algorithm will increase the calculation result of C1, so that C1>C2, and thus select the second path, that is, choose to construct the hash inner table with T2 and use table T1 as the connected outer table. Similarly, in the case that T2 is an intermediate structure table, the optimized inner and outer table selection algorithm will increase the calculation result of C2, so that C1<C2, and thus select the first path, that is, choose to construct the hash inner table with T1 and use table T2 as the connected outer table. Therefore, in the processing method of this embodiment, if the table to be connected includes an intermediate result table, the optimized inner and outer table selection algorithm will always select the intermediate result table as the outer table to be connected, and perform hash operations on the base table component hash inner table.

[0074] The processing method of this embodiment, through the optimized Hash Join cost calculation algorithm, can identify situations where rehashing is required. Therefore, in the selection of inner and outer tables, the intermediate result table is selected as the outer table for the join, and the base table is selected as the inner table for the join to perform the hash operation. Therefore, for the base table whose content remains unchanged in each recursive operation, only one full table scan hash operation is required. In the subsequent recursive Hash Join, multiple repeated full table scans of the base table are avoided, thereby shortening the overall Hash Join operation time and improving the overall performance of the database.

[0075] This embodiment also provides a machine-readable storage medium and a computer device. Figure 3 is a schematic structural diagram of a machine-readable storage medium 10 according to an embodiment of the present invention, Figure 4 FIG. 2 is a schematic structural diagram of a computer device 20 according to an embodiment of the present invention.

[0076] The machine-readable storage medium 10 stores a machine-executable program 11 , which implements the processing method of any of the above embodiments when executed by a processor.

[0077] The computer device 20 may include a memory 220, a processor 210, and a machine executable program 11 stored in the memory 220 and running on the processor 210, and the processor 210 implements the processing method of any of the above embodiments when executing the machine executable program 11.

[0078] 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.

[0079] For the purposes of the description of this embodiment, the machine-readable storage medium 10 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 system, device, or apparatus. More specific examples (a non-exhaustive list) of computer-readable media include the following: an electrical connection portion having one or more wirings (electronic device), a portable computer disk cartridge (magnetic device), 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 10 can even be paper or other suitable medium on which the program can be printed, because the program can be obtained electronically, for example, by optically scanning the paper or other medium, and then editing, interpreting, or processing in other suitable ways as necessary, and then stored in a computer memory.

[0080] 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.

[0081] The computer device 20 can be, for example, a server, a desktop computer, a laptop computer, a tablet computer, or a smartphone. In some examples, the computer device 20 can be a cloud computing node. The computer device 20 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 20 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.

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

[0083] The processor 210 can be connected to an I / O interface (input / output interface) suitable for connecting the computer device 20 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 20, or may be devices externally connected to the computing device.

[0084] The processor 210 may also be linked to a display interface suitable for connecting the computer device 20 to a display device via a system interconnect. The display device may include a display screen as a built-in component of the computer device 20. The display device may also include a computer monitor, a television, or a projector, etc., externally connected to the computer device 20. In addition, a network interface controller (NIC) may be suitable for connecting the computer device 20 to a network via a system interconnect. In some embodiments, the NIC may use any suitable interface or protocol (such as an Internet Small Computer System Interface, etc.) to transmit data. The network may be a cellular network, a radio network, a wide area network (WAN), a local area network (LAN), or the Internet, etc. A remote device may be connected to the computing device via a network.

[0085] 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.

[0086] 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 processing a hash connection, comprising: Calculate the cost of constructing a hash inner table using a first table, wherein the first table is any one of two tables to be joined in a pre-hash join; Determining whether the first table is an intermediate result table in a preset scenario; If so, calculate the impact factor of the rehashing; The product of the impact factor and the calculated cost of constructing the hash inner table using the first table is used as the revised cost of constructing the hash inner table using the first table.

2. The processing method according to claim 1, wherein The preset scenarios include at least: the recursive side of CTE and the recursive side of hierarchical query.

3. The processing method according to claim 1, wherein: The two tables to be connected further include a second table other than the first table; and The step of calculating the rehashing impact factor includes: Obtaining statistical information of the second table; The rehashing impact factor is calculated according to the statistical information of the second table and a preset function.

4. The processing method according to claim 3, wherein: The statistical information of the second table is statistical data generated and stored in the statistical information system table after the second table is created and data is inserted, and the statistical information of the second table includes the number of tuples in the second table and the condition selection rate of the second table generating the intermediate result table; and The step of calculating the rehashing impact factor according to the statistical information of the second table and a preset function includes: Obtaining a linear factor corresponding to the statistical information of the second table, wherein a correspondence between the linear factor and the statistical information of the second table is pre-configured and stored in a database; The product of the number of tuples in the second table and the conditional selectivity of the intermediate result table generated by the second table is multiplied by the corresponding linear factor and rounded up, and the rounded result is used as the result of calculating the rehashing impact factor.

5. The processing method according to claim 1, wherein: The two tables to be connected further include a second table other than the first table; and before the step of calculating the cost of constructing the hash inner table using the first table, the processing method further includes: A first path is pre-built, which uses the first table to build a hash inner table and uses the second table as a connection to an outer table, and a second path is pre-built, which uses the second table to build a hash inner table and uses the first table as a connection to an outer table.

6. The processing method according to claim 5, wherein: After the step of multiplying the impact factor by the calculated cost of constructing the hash inner table using the first table as the corrected cost of constructing the hash inner table using the first table, the processing method further includes: Calculating a first cost of selecting the first path for hash join based on the corrected cost of constructing the hash inner table using the first table; Obtaining a second cost of selecting the second path for hash connection; comparing the first price with the second price; According to the comparison results, the path with the smaller cost is selected as the planned path for hash connection.

7. A machine-readable storage medium having a machine-executable program stored thereon, wherein the machine-executable program implements the processing method according to any one of claims 1 to 6 when executed by a processor.

8. 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 processing method according to any one of claims 1 to 6 when executing the machine executable program.

Images