Data table connection method, device, equipment, medium and program product

Abstract

The embodiment of the present application provides a data table connection method, device, equipment, medium and program product. The method is applied to an electronic device, and the electronic device includes an optimizer and an executor. The method includes: determining by the optimizer whether the SQL query instruction to be executed includes two sub-conditions connected by or, and determining whether the two sub-conditions are both equivalent conditions of the first data table and the second data table, and the first data table and the second data table are the two data tables to be connected; when it is determined that the SQL query instruction meets the above conditions, indicating to the executor by the optimizer that one of the first data table and the second data table is an inner table and the other is an outer table; creating a corresponding hash table for the inner table by the executor, the hash table includes the hash values ​​of the data in the query column of the inner table within the two sub-conditions; according to the hash table, performing a hash connection on the query column of the outer table by the executor. This method can reduce the number of scans of the data table when performing a connection operation.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a data table connection method, device, equipment, medium and program product. Background Art

[0002] Structured query language (SQL) is a widely used programming language for managing and operating databases in database management systems. SQL can also represent an SQL query instruction, which can be used to perform various tasks, such as querying data, inserting records, updating records, deleting records, and creating and modifying database structures.

[0003] The connection methods available in the current database include nested loop connection (nestloop), hash connection (hashjoin) and merge connection (mergejoin). Among them, hashjoin and mergejoin both require an equal condition to be used. Therefore, when the user enters SQL involving connection operations, if the equality in the SQL is wrapped by the or condition, hashjoin or mergejoin cannot be directly executed, where or means either.

[0004] In some existing methods, when planning an SQL execution plan, if an equation in the SQL is wrapped by an or condition, the original SQL query instruction is rewritten to obtain a new SQL query instruction, so that a join operation can be performed in a hashjoin manner based on the new SQL query instruction. However, this method will increase the number of scans of the data table when performing the join operation. Summary of the invention

[0005] The embodiments of the present application provide a data table connection method, apparatus, device, medium and program product for achieving the purpose of reducing the number of scans of a data table when performing a connection operation when an equation in SQL is wrapped by an or condition and a hashjoin connection operation is performed.

[0006] In a first aspect, an embodiment of the present application provides a data table connection method, which is applied to an electronic device, wherein the electronic device includes an optimizer and an executor, and the method includes:

[0007] Determining, by the optimizer, whether the SQL query instruction to be executed includes two sub-conditions connected by or, and determining whether the two sub-conditions are both equivalent conditions of a first data table and a second data table, where the first data table and the second data table are two data tables to be connected;

[0008] In a case where it is determined that the SQL query instruction includes two sub-conditions connected by or, and it is determined that the two sub-conditions are both equivalent conditions of the first data table and the second data table, indicating to the executor through the optimizer that one of the first data table and the second data table is an inner table and the other is an outer table;

[0009] Creating a corresponding hash table for the inner table by the executor, the hash table including hash values ​​of data in the query column of the inner table within the two sub-conditions;

[0010] The executor performs a hash join on the query columns of the foreign table according to the hash table.

[0011] In a possible implementation, the creating a corresponding hash table for the internal table by the executor includes:

[0012] Traversing the data of each row corresponding to the query column of the inner table through the executor;

[0013] For any of the rows of data, a hash value of the row of data is calculated, and according to the hash value of the row of data, the row of data is mapped and stored in a hash bucket corresponding to the hash value of the row of data;

[0014] After traversing the data of each row corresponding to the query column of the inner table, a hash table corresponding to the inner table is obtained.

[0015] In a possible implementation, the query columns of the inner table in the two sub-conditions are at least two query columns, and according to the hash value of the data of the row, mapping the data of the row and storing it in a hash bucket corresponding to the hash value of the data of the row includes:

[0016] Comparing the hash values ​​of the data in the row for similarity or difference to obtain a first comparison result;

[0017] When the first comparison result indicates that the hash values ​​of the data in the row are all the same, mapping and storing a copy of the data in the row in the hash bucket corresponding to the hash value;

[0018] or,

[0019] When the first comparison result indicates that the hash values ​​of the data in the row are not all the same, each data in the row is mapped and stored in a hash bucket corresponding to the hash value of each data in the row.

[0020] In a possible implementation, performing a hash join on the query column of the foreign table by the executor according to the hash table includes:

[0021] Traversing the data of each row corresponding to the query column of the foreign table by the executor;

[0022] For any of the rows of data, a hash value of the row of data is calculated, and based on the hash value of the row of data, a hash value identical to the hash value of the row of data is searched in the hash table;

[0023] When a hash value identical to the hash value of the data of the row is found, condition comparison is performed according to the two sub-conditions, and when any one of the two sub-conditions is met, a connection is established for the row;

[0024] After traversing the data of each row corresponding to the query column of the external table, the hash connection of the first data table and the second data table is completed.

[0025] In a possible implementation, the query columns of the foreign table in the two sub-conditions are at least two query columns, and searching the hash table for a hash value that is the same as the hash value of the data in the row according to the hash value of the data in the row includes:

[0026] Comparing the hash values ​​of the data in the row for similarity or difference to obtain a second comparison result;

[0027] When the second comparison result indicates that the hash values ​​of the data in the row are all the same, searching the hash table for a hash value that is the same as the hash value;

[0028] or,

[0029] When the second comparison result indicates that the hash values ​​of the data in the row are not all the same, the hash values ​​that are the same as the hash values ​​of the data in the row are searched in the hash table respectively.

[0030] In a possible implementation manner, establishing a connection for the row includes:

[0031] Determine whether a row corresponding to the internal table connected to the row has been connected to the row;

[0032] If it is determined that the row corresponding to the inner table connected to the row has been connected to the row, the connection is not established repeatedly.

[0033] In a second aspect, an embodiment of the present application provides a data table connection device, which is applied to an electronic device, wherein the electronic device includes an optimizer and an executor, and the device includes:

[0034] an optimization module, configured to determine, by the optimizer, whether the SQL query instruction to be executed includes two sub-conditions connected by or, and determine whether the two sub-conditions are both equivalent conditions of a first data table and a second data table, wherein the first data table and the second data table are two data tables to be connected;

[0035] The optimization module is further configured to, when it is determined that the SQL query instruction includes two sub-conditions connected by or, and it is determined that the two sub-conditions are both equivalent conditions of the first data table and the second data table, indicate to the executor through the optimizer that one of the first data table and the second data table is an inner table and the other is an outer table;

[0036] An execution module, configured to create a corresponding hash table for the inner table through the executor, wherein the hash table includes hash values ​​of data in a query column of the inner table within the two sub-conditions;

[0037] The execution module is further configured to execute a hash connection on the query column of the foreign table through the executor according to the hash table.

[0038] In a possible implementation manner, the execution module is specifically configured to:

[0039] Traversing the data of each row corresponding to the query column of the inner table through the executor;

[0040] For any of the rows of data, a hash value of the row of data is calculated, and according to the hash value of the row of data, the row of data is mapped and stored in a hash bucket corresponding to the hash value of the row of data;

[0041] After traversing the data of each row corresponding to the query column of the inner table, a hash table corresponding to the inner table is obtained.

[0042] In a possible implementation manner, the query columns of the inner table in the two sub-conditions are at least two query columns, and the execution module is specifically configured to:

[0043] Comparing the hash values ​​of the data in the row for similarity or difference to obtain a first comparison result;

[0044] When the first comparison result indicates that the hash values ​​of the data in the row are all the same, mapping and storing a copy of the data in the row in the hash bucket corresponding to the hash value;

[0045] or,

[0046] When the first comparison result indicates that the hash values ​​of the data in the row are not all the same, each data in the row is mapped and stored in a hash bucket corresponding to the hash value of each data in the row.

[0047] In a possible implementation manner, the execution module is specifically configured to:

[0048] Traversing the data of each row corresponding to the query column of the foreign table by the executor;

[0049] For any of the rows of data, a hash value of the row of data is calculated, and based on the hash value of the row of data, a hash value identical to the hash value of the row of data is searched in the hash table;

[0050] When a hash value identical to the hash value of the data of the row is found, condition comparison is performed according to the two sub-conditions, and when any one of the two sub-conditions is met, a connection is established for the row;

[0051] After traversing the data of each row corresponding to the query column of the external table, the hash connection of the first data table and the second data table is completed.

[0052] In a possible implementation manner, the query columns of the foreign table in the two sub-conditions are at least two query columns, and the execution module is specifically configured to:

[0053] Comparing the data in the row for similarity or difference based on their respective hash values ​​to obtain a second comparison result;

[0054] When the second comparison result indicates that the hash values ​​of the data in the row are all the same, searching the hash table for a hash value that is the same as the hash value;

[0055] or,

[0056] When the second comparison result indicates that the hash values ​​of the data in the row are not all the same, the hash values ​​that are the same as the hash values ​​of the data in the row are searched in the hash table respectively.

[0057] In a possible implementation manner, the execution module is specifically configured to:

[0058] Determine whether a row corresponding to the internal table connected to the row has been connected to the row;

[0059] If it is determined that the row corresponding to the inner table connected to the row has already been connected to the row, the connection is not established repeatedly.

[0060] In a third aspect, an embodiment of the present application provides an electronic device, comprising: a memory and a processor; the memory stores computer-executable instructions; the processor executes the computer-executable instructions stored in the memory, so that the processor executes the first aspect above and / or various possible implementations of the first aspect.

[0061] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, they are used to implement the first aspect above and / or various possible implementations of the first aspect.

[0062] In a fifth aspect, an embodiment of the present application provides a computer program product, including a computer program, which, when executed by a processor, implements the above first aspect and / or various possible implementation methods of the first aspect.

[0063] The data table connection method, device, equipment, medium and program product provided by the embodiment of the present application detect the SQL query instruction to be executed through the optimizer, determine whether the SQL query instruction includes two sub-conditions connected by or, and determine whether the two sub-conditions are both equivalent conditions of the first data table and the second data table. When it is determined that the SQL query instruction to be executed meets the above judgment conditions, the optimizer indicates to the executor that one of the first data table and the second data table is an inner table and the other is an outer table. In this way, when it is determined that the SQL query instruction is two equivalent conditions wrapped by the or condition, the optimizer instructs the executor to perform a connection operation on the first data table and the second data table in a hash connection manner. Furthermore, the executor creates a hash table based on the data in the query column of the inner table within the two sub-conditions, and the process of creating the hash table only needs to scan the inner table once to determine the hash value of all the data in the query column to be queried in the two sub-conditions of the inner table, based on this, the number of times the inner table is scanned is reduced. According to the hash table, the hash value of the data of all query columns can be queried in the hash table. Then, when the executor performs a join operation on the query column of the outer table, the outer table can be scanned only once to complete the join operation on the query column of the outer table. Therefore, while reducing the number of scans of the inner table and the outer table, the purpose of reducing the number of scans of the data table when performing the join operation is achieved. BRIEF DESCRIPTION OF THE DRAWINGS

[0064] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0065] Figure 1 A schematic diagram of a connection operation provided in an embodiment of the present application;

[0066] Figure 2 Schematic diagram of the data table connection method provided in the embodiment of the present application Figure 1 ;

[0067] Figure 3 Schematic diagram of creating a hash table by deduplicating key values ​​provided in an embodiment of the present application Figure 1 ;

[0068] Figure 4 Schematic diagram of creating a hash table by deduplicating key values ​​provided in an embodiment of the present application Figure 2 ;

[0069] Figure 5 Schematic diagram of the data table connection method provided in the embodiment of the present application Figure 2 ;

[0070] Figure 6 A schematic diagram of a hash table scan based on a flag bit provided in an embodiment of the present application;

[0071] Figure 7 A schematic diagram of the structure of a data table connection device provided in an embodiment of the present application;

[0072] Figure 8 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application.

[0073] The above drawings have shown clear embodiments of the present application, which will be described in more detail later. These drawings and text descriptions are not intended to limit the scope of the present application in any way, but to illustrate the concept of the present application to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION

[0074] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.

[0075] In the embodiments of the present application, words such as "first" and "second" are used to distinguish the same or similar items with substantially the same functions and effects. For example, the first electronic device and the second electronic device are only used to distinguish different electronic devices, and their order is not limited. Those skilled in the art can understand that words such as "first" and "second" do not limit the quantity and execution order, and words such as "first" and "second" do not necessarily limit them to be different.

[0076] In the embodiments of the present application, the words "exemplary" or "for example" are used to indicate examples, illustrations or descriptions. Any embodiment or design described as "exemplary" or "for example" in the present application should not be interpreted as being more preferred or more advantageous than other embodiments or designs. Specifically, the use of words such as "exemplary" or "for example" is intended to present related concepts in a specific way.

[0077] In the embodiments of the present application, "at least one" means one or more, and "more" means two or more. "And / or" describes the association relationship of the associated objects, indicating that there can be three relationships. For example, A and / or B can mean: A exists alone, A and B exist at the same time, and B exists alone, where A and B can be singular or plural. The character " / " generally indicates that the associated objects before and after are in an "or" relationship.

[0078] In the technical solutions of the embodiments of the present application, the collection, storage, use, processing, transmission, provision and disclosure of user personal information involved are in compliance with the provisions of relevant laws and regulations and do not violate public order and good morals.

[0079] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with the relevant laws, regulations and standards of the relevant countries and regions, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0080] Figure 1 A schematic diagram of a connection operation provided in an embodiment of the present application, such as Figure 1 As shown, there are two data tables in the database, namely t1 and t2. You can enter SQL query instructions in the data management system of the database to perform conditional queries on the data in t1 and t2 to obtain query results that meet the conditions. When implementing data queries, the executor will connect the rows in t1 and t2 that meet the query conditions.

[0081] like Figure 1As shown, if the query is performed on column a of t1 and column a of t2, and the query condition is an equal condition: t1.a=t2.a, the equal condition means filtering out the data in column a of table t1 and the data in column a of table t2 that are equal and establishing a corresponding connection. It can be expressed as an SQL query instruction "Select*from t1,t2 where t1.a=t2.a;" where Select means selection, from means from, "Select*from t1,t2" means selecting from table t1 and table t2, where is a symbol indicating a condition, t1.a=t2.a means a query condition, which can also be understood as a filter condition.

[0082] When the executor executes the SQL query instruction, it will traverse the data in column a of t1 and the data in column a of t2, and connect the two rows with the same data value. For example, if it is determined that the data value of row 2 in column a of t1 is the same as the data value of row 3 in column a of t2, then row 2 in t1 and row 3 in t2 will be connected.

[0083] It should be noted that if the executor uses hash join to execute the SQL query instruction, the judgment is based on the hash value corresponding to each data in the process of traversing and judging the query conditions. When the SQL query instruction "Select*from t1,t2 where t1.a=t2.a;" is executed in the hash join mode, if t2 is used as the inner table, the hash value of each data in column a of t2 is calculated respectively, and the hash table corresponding to t2.a is established. Then, with t1 as the outer table, the hash value of each data in column a of t1 is calculated respectively. The hash value of the first row of data in column a of t1 is traversed in the hash table of t2.a to query whether there is an identical hash value. If there is an identical hash value, the row corresponding to the identical hash value in t2.a is connected to the first row of column a of t1. The connection can be established for multiple rows. For example, if there are multiple rows of data in column a of t2 that are the same as the first row of data in column a of t1, then their hash values ​​are also the same. In this way, the first row of column a in t1 can be connected to these rows of identical data respectively; if there is no identical hash value, the hash value of the second row of data in column a of t1 is traversed in the hash table of t2.a in the same way to query whether there is an identical hash value, until all rows in column a of t1 are traversed.

[0084] Since the hash value of the data can be understood as an index of the data, the time consumed when comparing data and judging query conditions based on the index is shorter. Therefore, hash join has certain query efficiency advantages. In many scenarios, hash join is given priority to connecting data tables.

[0085] When the database management system executes an SQL query instruction, if the conditions in the SQL query instruction are two sub-conditions connected by or, the SQL query instruction can be understood as being wrapped by the or condition. Due to the execution rule restrictions of the hash connection itself, the executor cannot directly perform a hash connection on the SQL query instruction wrapped by the or condition. Therefore, the SQL query instruction wrapped by the or condition is usually executed in the form of a nested loop connection, or the SQL query instruction is equivalently rewritten by the optimizer before the connection operation can be executed in the form of a hash connection.

[0086] The nested loop join method usually takes a long time to complete the join operation. Therefore, in the existing method, the optimizer usually rewrites the SQL query instruction wrapped by the or condition to obtain a new SQL query instruction, and then performs the join operation of the two data tables using the hash join method based on the new SQL query instruction. However, this method will increase the number of scans of the data table when performing the join operation.

[0087] For example, a SQL query instruction wrapped in an or condition is, for example, "Select*from t1,t2 where t1.a=t2.aor t1.a=t2.b;" wherein "t1.a=t2.a or t1.a=t2.b" after where is a query condition, which indicates that a connection is established for rows that meet the conditions of t1.a=t2.a or t1.a=t2.b, and t1.a=t2.a and t1.a=t2.b are two sub-conditions connected by or. In the existing method, after obtaining the SQL query instruction, the optimizer can use a union (UNION ALL) combined with a negation function (LNNVL) to split the or condition and obtain a new SQL query instruction, so that the executor can execute the new SQL query instruction by hash connection. The new SQL query instruction can be expressed as "(Select*from t1,t2 where t1.a=t2.a)union all;

[0088] (Select*from t1,t2 where t1.a=t2.b and LNNVL(t1.a=t2.a));”.

[0089] Among them, "(Select*from t1,t2 where t1.a=t2.a)union all" is a SQL sub-query instruction, and "(Select*from t1,t2 where t1.a=t2.b and LNNVL(t1.a=t2.a))" is another SQL sub-query instruction. It can be seen that if you want to complete the connection operation of the SQL query instruction before rewriting, you need to execute two new query instructions.

[0090] For the first SQL subquery instruction, the query condition is "t1.a=t2.a", then the inner table (one of the data tables t1 and t2) needs to be scanned once to create a hash table, and then the outer table (the other of the data tables t1 and t2) needs to be scanned once to complete the hash connection operation for the SQL subquery instruction. After that, for the second SQL subquery instruction, the query condition is "t1.a=t2.b and LNNVL(t1.a=t2.a)", then the inner table (one of the data tables t1 and t2) needs to be scanned once to create a hash table, and then the outer table (the other of the data tables t1 and t2) needs to be scanned once to complete the hash connection operation for the SQL subquery instruction. It can be seen that the data table needs to be scanned at least four times to complete the hash connection operation. Each time the data table is scanned once, the data of each row in the data table needs to be traversed. Therefore, the number of scans of the data table based on this rewriting method will increase.

[0091] The inventor found through the above research that although the two SQL sub-query instructions after splitting can be connected by hash connection respectively, the restriction that the SQL query instruction wrapped by the or condition cannot use hash connection is lifted. However, the query tables of the two SQL sub-query instructions are both data tables t1 and t2, but the conditions are different, and redundant data table scans are required, resulting in an increase in the number of scans. If the amount of data in the query table is large, the efficiency of the connection operation will be greatly reduced, and then the query efficiency of the data query will be reduced.

[0092] Furthermore, the inventors have found through research that since the problem of redundant data table scanning cannot be avoided by rewriting the original SQL query instruction through the optimizer, can the problem be solved by changing the execution logic of the optimizer and the executor. Through research on SQL query instructions wrapped by or conditions, it is found that if the sub-conditions on both sides of the or condition are equivalent conditions about data table t1 and data table t2, the optimizer can instruct the executor to use the inner table and outer table, and based on the executor, determine the hash value of all query columns in the inner table in one scan of the inner table, and create a hash table, which can avoid scanning the inner table again and reduce the number of scans of the inner table. And when performing a join operation on the query column of the outer table, based on the hash table, the outer table can be scanned only once to complete the join operation on the query column of the outer table.

[0093] In view of this, an embodiment of the present application provides a data table connection method, which detects the SQL query instruction to be executed through an optimizer, determines whether the SQL query instruction includes two sub-conditions connected by or, and determines whether the two sub-conditions are both equivalent conditions of the first data table and the second data table. When it is determined that the SQL query instruction to be executed meets the above judgment conditions, the optimizer indicates to the executor that one of the first data table and the second data table is an inner table and the other is an outer table. In this way, when it is determined that the SQL query instruction is two equivalent conditions wrapped by the or condition, the optimizer instructs the executor to perform a connection operation on the first data table and the second data table in a hash connection manner. Furthermore, the executor creates a hash table based on the data in the query column of the inner table within the two sub-conditions, and the process of creating the hash table only needs to scan the inner table once to determine the hash value of all the data in the query column to be queried in the two sub-conditions of the inner table, thereby reducing the number of times the inner table is scanned. According to the hash table, the hash value of the data of all query columns can be queried in the hash table. Then, when the executor performs a join operation on the query column of the outer table, the outer table can be scanned only once to complete the join operation on the query column of the outer table. Therefore, while reducing the number of scans of the inner table and the outer table, the purpose of reducing the number of scans of the data table when performing the join operation is achieved.

[0094] Exemplarily, the sufficient condition for hash join is the equivalence condition between the two data tables to be queried. Therefore, based on the possible situations of the equivalence condition, the two sub-conditions connected by or are analyzed to find that there are the following two situations:

[0095] In the first case, if the query condition is "t1.a=t2.a or t1.a=t2.b", it can be equivalently understood as "t1.a=t2.aor t2.b". In this case, there are two situations: In the first case, a hash join is performed with t1 as the inner table and t2 as the outer table. At this time, the hash table is constructed based on the hash value of the data corresponding to each row of t1.a, and only one scan of the data table t1 is required; when performing a hash join, the executor can use the condition "t1.a=t2.a or t1.a=t2.b" as the judgment logic, and only one scan of the data table t2 is required to complete the join operation. In the second case, a hash join is performed with t2 as the inner table and t1 as the outer table. In this case, the hash table is constructed with the hash values ​​of the data of each row corresponding to t2.a and t2.b, so it is only necessary to scan the data table t2 once. When performing a hash join, the executor can use the condition "t1.a=t2.a or t1.a=t2.b" as the judgment logic, and only needs to scan the data table t1 once to complete the join operation.

[0096] In the second case, if the query condition is "t1.a=t2.a or t1.b=t2.b", it can be seen that columns a and b in data table t1 are query columns, and columns a and b in data table t2 are also query columns. At this time, ignoring the data volume of data table t1 and data table t2, it does not matter which of data table t1 and data table t2 is used as the inner table and which is used as the outer table to construct the hash table. Assuming that a hash join is performed with t2 as the inner table and t1 as the outer table, at this time, the hash table is constructed with the hash values ​​of the data corresponding to each row of t2.a and t2.b, and only one scan of data table t2 is required; when performing a hash join, the executor can use the condition "t1.a=t2.a or t1.b=t2.b" as the judgment logic, and only one scan of data table t1 is required to complete the join operation.

[0097] It can be seen that no matter which of the above two situations is used, the number of scans of the two data tables in the scanning equivalent conditions can be reduced, thereby reducing the number of scans of the data tables.

[0098] The technical solution of the present application is described in detail with specific embodiments below. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of the present application will be described below in conjunction with the accompanying drawings.

[0099] Figure 2 Schematic diagram of the data table connection method provided in the embodiment of the present application Figure 1, the execution subject of the method can be an electronic device with corresponding data storage and computing capabilities, such as a computer, server or server cluster. The electronic device includes an optimizer and an executor. The optimizer can be understood as a component or module in a database system or database management system that optimizes SQL and formulates an execution plan. The executor can be understood as a component or module in a database system or database management system that performs specific execution based on the SQL and execution plan indicated by the optimizer. Both the optimizer and the executor can be functional modules composed of computer programs. Figure 2 As shown, the method includes:

[0100] S201, determining, by an optimizer, whether the SQL query instruction to be executed includes two sub-conditions connected by or, and determining whether the two sub-conditions are both equivalent conditions of a first data table and a second data table, the first data table and the second data table being two data tables to be connected.

[0101] Exemplarily, the electronic device may receive a SQL query instruction input by a user or a SQL query instruction automatically generated by a computer program. The SQL query instruction may be input into an optimizer, and the optimizer may receive the SQL query instruction to be executed.

[0102] A detection code for detecting the SQL query instruction to be executed can be preset in the optimizer, so as to determine through the detection code whether the SQL query instruction includes two sub-conditions connected by or, and determine whether the two sub-conditions are both equivalent conditions of the first data table and the second data table.

[0103] For example, or is an instruction symbol representing either, and the two sub-conditions connected by or can be understood as a parent condition, and if any of the two sub-conditions connected by or is satisfied, the parent condition is satisfied. For example, the parent condition is "t1.a=t2.a or t1.b=t2.b", where "t1.a=t2.a" is a sub-condition, and "t1.b=t2.b" is another sub-condition.

[0104] The first data table and the second data table can be two arbitrary data tables to be connected in the database, and can also be understood as two arbitrary data tables to be queried. For example, the first data table is data table t1, and the second data table is data table t2. The equivalence condition can be understood as the condition that the elements on both sides of the equal sign of the equation are equal, for example, t1.a=t2.a, where t1.a is the element on one side of the equal sign, and t2.a is the element on the other side of the equal sign.

[0105] S202, when it is determined that the SQL query instruction includes two sub-conditions connected by or, and it is determined that the two sub-conditions are equivalent conditions of the first data table and the second data table, the optimizer indicates to the executor that one of the first data table and the second data table is an inner table and the other is an outer table.

[0106] Exemplarily, a detection code may be set to first detect whether or is included after where in the SQL query instruction. When or is included after where in the SQL query instruction, the two sub-conditions on the left and right sides of or are then detected. It may be first determined whether both sub-conditions are equivalent conditions including an equal sign. When it is determined that both are equivalent conditions including an equal sign, it may be determined whether the sub-conditions of the two equivalent conditions are equivalent conditions of the first data table and the second data table. When it is determined that both are equivalent conditions of the first data table and the second data table, it may be determined that the SQL query instruction includes two sub-conditions connected by or, and it may be determined that both sub-conditions are equivalent conditions of the first data table and the second data table.

[0107] The inner table may refer to a data table that is scanned first in a hash join, and the outer table may refer to a data table that is scanned later in a hash join. A hash table for hash join may be created based on the inner table.

[0108] Exemplarily, when the optimizer indicates to the executor that one of the first and second data tables is an inner table and the other is an outer table, it can determine which is the inner table and which is the outer table according to the data volume of the first and second data tables. For example, a data table with a smaller data volume can be indicated as an inner table, and a data table with a larger data volume can be indicated as an outer table. In this way, the size of the storage space occupied by the hash table can be controlled, making the hash table as small as possible to improve the efficiency of the connection operation. When determining the data volume, it can be determined based on the data volume of the entire data table, or it can be determined based on the data volume of the query column to be queried in the data table.

[0109] S203, creating a corresponding hash table for the inner table through the executor, where the hash table includes hash values ​​of data in the query columns of the inner table within the two sub-conditions.

[0110] Exemplarily, since the SQL query instruction includes two sub-conditions connected by or, and the two sub-conditions are equal conditions of the first data table and the second data table, when creating the hash table corresponding to the inner table, the hash values ​​of the data of all query columns in the two sub-conditions of the inner table are added to the hash table. The query column can be understood as the column in the data table that is queried during the hash connection.

[0111] For example, the two sub-conditions connected by or are "t1.a=t2.a" and "t1.a=t2.b". If t1 is the inner table, since both sub-conditions use column a of t1 as the query column, a hash table is created with column a of t1 as the query column. If t2 is the inner table, since one of the two sub-conditions uses column a of t2 as the query column and the other uses column b of t2 as the query column, a hash table is created with columns a and b of t2 as the query columns. The hash table includes the hash values ​​of all the data in the query columns of the inner table. The data of each query column in any row of the inner table can be combined into a tuple.

[0112] In one possible implementation, when the executor creates a corresponding hash table for the inner table, it can be implemented as follows: the executor traverses the data of each row corresponding to the query column of the inner table; for the data of any row, the hash value of the row data is calculated, and according to the hash value of the row data, the row data is mapped and stored in the hash bucket corresponding to the hash value of the row data; after traversing the data of each row corresponding to the query column of the inner table, the hash table corresponding to the inner table is obtained.

[0113] Exemplarily, the executor may traverse the data of each row corresponding to the query column of the internal table, and may calculate the corresponding hash value for each data during the traversal. The hash value may be understood as the value obtained after hash mapping the data, and the data may be calculated through a hash function to obtain the hash value corresponding to the data. The hash function may be any hash function, for example, SHA-1, SHA-2, or SHA-256.

[0114] A hash bucket can be understood as a storage area corresponding to a hash value. One hash bucket corresponds to one hash value. If two or more data are calculated to obtain the same hash value, then these data can be stored in the hash bucket corresponding to the hash value. For example, if the hash value of a data is 1, then the data can be mapped and stored in hash bucket 1; if the hash value of a data is 101, then the data can be mapped and stored in hash bucket 101. For another example, if the hash values ​​of two or more data are both 2002, then the two or more data can be mapped and stored in hash bucket 2002.

[0115] Among them, mapping storage can refer to storing data by mapping. For example, the data in the data table are all stored in storage spaces such as memory or hard disk, and the address of the storage space can be mapped by a pointer pointing to the storage space. Therefore, when mapping storage, the address or pointer of the data can be stored in the hash bucket of the hash table, and the data itself can be mapped and queried through the address or pointer.

[0116] Generally, the amount of data in an address or pointer is smaller than the amount of data itself. Therefore, by mapping the storage, the computational overhead of data storage when creating a hash table can be reduced, the speed of creating a hash table can be increased, and the storage space occupied by the hash table can be reduced, thereby increasing the efficiency of creating a hash table. It is understandable that if the amount of data itself is small or the size or efficiency of the hash table to be created is not considered, the data can be directly stored in the hash table or the data and the address or pointer corresponding to the data can be stored.

[0117] S204: According to the hash table, a hash join is performed on the query column of the foreign table through the executor.

[0118] Exemplarily, the query column of the external table can be understood as the queried column in the external table included in the query condition. The executor performs a hash join on the query column of the external table, or traverses the data of each row of the queried query column in a traversal manner.

[0119] In one possible implementation, when a hash join is performed on a query column of an external table through an executor according to a hash table, it can be specifically implemented in the following manner: the executor traverses the data of each row corresponding to the query column of the external table; for the data of any row, a hash value of the row data is calculated, and based on the hash value of the row data, a hash value identical to the hash value of the row data is searched in the hash table; when a hash value identical to the hash value of the row data is found, a condition comparison is performed according to two sub-conditions, and a connection is established for the row when any of the two sub-conditions are met; after traversing the data of each row corresponding to the query column of the external table, a hash join of the first data table and the second data table is completed.

[0120] For example, according to the query condition, if the query column of the outer table is a column, you can start traversing the hash table from the data in the first row of the column, and establish connections with the first row of the outer table for each row of the inner table that corresponds to the hash value of the data that meets the equal value condition. This process is repeated until all rows of the query column of the outer table are traversed.

[0121] For another example, according to the query conditions, the query column of the outer table is two columns, and the hash table can be traversed starting from the data in the first row of the two columns. At this time, the first row corresponds to two data, and the two data come from the two query columns. When traversing the hash table, the rows of the inner table corresponding to the hash values ​​of the two data that meet the equal value condition are connected to the first row of the outer table. And so on, until all rows of the query column of the outer table are traversed.

[0122] The executor traverses the outer table, compares the hash value of the data of the corresponding row of the query column of the outer table with the hash table, and establishes a connection between the rows of the inner table and the rows of the outer table that meet the conditions when any of the two sub-conditions of the or connection are met. Based on this, no matter there are one or more query columns of the outer table in the two sub-conditions of the or connection, the hash connection of the first data table and the second data table can be completed by scanning the outer table once, thereby reducing the number of scans.

[0123] The data table connection method provided in the embodiment of the present application detects the SQL query instruction to be executed through the optimizer, determines whether the SQL query instruction includes two sub-conditions connected by or, and determines whether the two sub-conditions are both equivalent conditions of the first data table and the second data table. When it is determined that the SQL query instruction to be executed meets the above judgment conditions, the optimizer indicates to the executor that one of the first data table and the second data table is an inner table and the other is an outer table. In this way, when it is determined that the SQL query instruction is two equivalent conditions wrapped by the or condition, the optimizer instructs the executor to perform a connection operation on the first data table and the second data table in a hash connection manner. Furthermore, the executor creates a hash table based on the data in the query column of the inner table within the two sub-conditions, and the process of creating the hash table only needs to scan the inner table once to determine the hash value of all the data in the query column to be queried in the two sub-conditions of the inner table, thereby reducing the number of times the inner table is scanned. According to the hash table, the hash value of the data of all query columns can be queried in the hash table. Then, when the executor performs a join operation on the query column of the outer table, the outer table can be scanned only once to complete the join operation on the query column of the outer table. Therefore, while reducing the number of scans of the inner table and the outer table, the purpose of reducing the number of scans of the data table when performing the join operation is achieved.

[0124] In a possible implementation, the query columns of the inner table in the two sub-conditions are at least two query columns. When the data of the row is mapped and stored in a hash bucket corresponding to the hash value of the data of the row according to the hash value of the data of the row, it can be specifically implemented in the following manner: a first comparison result is obtained by performing a same or different comparison based on the hash values ​​of each data of the row; when the first comparison result indicates that the hash values ​​of each data of the row are all the same, a copy of each data of the row is mapped and stored in the hash bucket corresponding to the hash value; or, when the first comparison result indicates that the hash values ​​of each data of the row are not all the same, a copy of each data of the row is mapped and stored in the hash bucket corresponding to the hash value of each data of the row.

[0125] Exemplarily, after calculating the hash values ​​of each data in the same row, the executor can compare the hash values ​​to obtain a first comparison result, which is the result of comparing the hash values ​​of each data in a row of the internal table, and is used to determine how to map and store the data in the row.

[0126] Exemplarily, when creating a hash table, if the hash values ​​corresponding to two or more data in the same row of the inner table are all the same, then the data in the row can be mapped and stored in the hash bucket corresponding to the hash value, that is, each data in the row is mapped and stored in the hash bucket corresponding to the hash value. This method is equivalent to mapping and storing each data in the row once in one hash bucket, which can reduce the storage space occupied.

[0127] If the hash values ​​corresponding to two or more data in the same row of the internal table are not all the same, it is necessary to map and store each data of the row in the hash bucket corresponding to each hash value, that is, treat each data of the row as a group, and map and store a copy in the hash bucket corresponding to each hash value of each data of the row. This method maps and stores each data of the row multiple times in multiple hash buckets, which can avoid missing the rows that should be connected during the connection operation and improve the accuracy of the hash connection. The method of creating a hash table for the above two strategies can also be understood as creating a hash table by deduplicating key values.

[0128] The main function of the hash table is to map and store the data of the inner table, so that the outer table can be queried according to the hash table to implement the connection operation. As for the nature of or itself, if any of the two sub-conditions of the or connection is true, the parent condition is true, that is, the parent condition can return true.

[0129] For example, if the query condition is "t1.a=t2.a or t1.a=t2.b", and the data table t1 is used as the outer table and the data table t2 is used as the inner table, it can be understood that it is essentially a join operation of one query column (t1.a) in the outer table and two query columns (t2.a and t2.b) in the inner table. If the hash values ​​corresponding to the data in any row of the query columns t2.a and t2.b of the inner table are the same, there is no need to repeatedly map and store the data. If the hash values ​​corresponding to the data in any row of the query columns t2.a and t2.b of the inner table are different, it is necessary to map and store the data in the hash bucket corresponding to each hash value of the row to avoid omissions in the join operation.

[0130] Figure 3 Schematic diagram of creating a hash table by deduplicating key values ​​provided in an embodiment of the present application Figure 1 ,like Figure 3As shown, the SQL query instruction is "Select*from t1,t2 where t1.a=t2.a or t1.a=t2.b". Assuming that the optimizer instructs t2 to be the inner table, the query columns of the inner table are t2.a and t2.b. When the hash table is established, the data of any row is two data, namely the data of column a of the row and the data of column b of the row.

[0131] Take the first row in data table t2 as an example, calculate the hash value of the data in the first row a column as KEY1, and calculate the hash value of the data in the first row b column as KEY2, then compare whether KEY1 and KEY2 are the same. If they are the same (YES), that is, KEY1=KEY2, then the data in the a column and the data in the b column of the row (expressed as t2(a, b)) are mapped and stored in the hash bucket of KEY1. Because KEY1 and KEY2 are the same value, they correspond to the same hash bucket, that is, t2(a, b) is mapped and stored in the hash bucket of KEY2, which means that one copy is stored in one hash bucket; if they are not the same (NO), that is, KEY1≠KEY2, then the data in the a column and the data in the b column of the row (expressed as t2(a, b)) are stored in the hash bucket of KEY1 and the hash bucket of KEY2 respectively, which is equivalent to storing one copy of t2(a, b) in two different hash buckets.

[0132] For example, if KEY1=KEY2=1, then t2(a, b) is stored in hash bucket 1. Thus, when scanning the external table, assuming that the hash value corresponding to the data of a row of t1.a in the external table is also 1, the executor will query whether the condition "t1.a=t2.a or t1.a=t2.b" is satisfied in hash bucket 1 of the hash table according to the hash value 1 of the data of the row of t1.a in the external table query column. Since the hash value of the data of t1.a is 1 and the hash value of the data of t2.a is also 1, t1.a=t2.a is satisfied, and the condition is satisfied as a whole. Then the executor can establish a connection between the row of the external table and the row corresponding to t2(a, b) in the internal table. It should be understood that in the above case, the condition "t1.a=t2.b" is also satisfied.

[0133] In actual applications, when the executor searches the hash bucket corresponding to the hash table according to the hash value of the external data, if it can find the hash bucket with the same hash value as the external data, it can compare the data of t1.a and t2.a based on the conditions of the SQL query instruction, rather than just comparing the hash values. Similarly, it can compare the data of t1.a and t2.b based on the data of t1.a and t2.b.

[0134] Taking the first row in data table t2 as an example, and for another example, KEY1=2, KEY2=3, that is, KEY1≠KEY2, if there is no mapping and storage of two copies, that is, t2(a, b) is only stored in hash bucket 2, and t2(a, b) is not stored in hash bucket 3, then when scanning the external table, assuming that the hash value corresponding to the data of a row of query column t1.a in the external table is 3, the executor will query in hash bucket 3 but not in other hash buckets. Since t2(a, b) is not stored in hash bucket 3 when the hash table is created, the executor cannot query t2(a, b) at this time, and the executor will not establish a connection between the row of the external table and the row of the internal table. However, according to the actual situation, the hash value of the data in the row of the outer table query column t1.a is 3, which is the same hash value as the hash value 3 of the row of the inner table query column t2.b, indicating that the data of the two are the same, that is, they satisfy the "t1.a=t2.b" condition "t1.a=t2.a or t1.a=t2.b", and the condition "t1.a=t2.a or t1.a=t2.b" is established as a whole, so the row of the outer table should be connected to the row of the inner table, but because t2(a, b) was not stored in hash bucket 3 when the hash table was created, the rows that should have been connected were not connected, resulting in the omission of the connection operation, making the overall connection result obtained after traversing the outer table incomplete, reducing the accuracy of the hash connection.

[0135] According to the method of this embodiment, KEY1=2, KEY2=3, that is, KEY1≠KEY2, then t2(a, b) will be stored in hash bucket 2, and t2(a, b) will also be stored in hash bucket 3. In this way, when scanning the external table, if the hash value corresponding to the data of a row of the query column t1.a in the external table is 3, t2(a, b) can be queried in hash bucket 3. At this time, the condition of "t1.a=t2.a or t1.a=t2.b" is met, and the executor will establish a connection between the row of the external table and the row of the internal table. Similarly, since a copy of t2(a, b) is stored in hash bucket 2 and hash bucket 3 respectively, assuming that the hash value corresponding to the data of another row of the query column t1.a in the external table is 2, t2(a, b) can be queried in hash bucket 2. At this time, the condition of "t1.a=t2.a or t1.a=t2.b" is met, and the executor will establish a connection between the row of the external table and the row of the internal table.

[0136] The following example illustrates the creation of a hash table for deduplication of key values ​​by taking the query columns of the outer table as two query columns.

[0137] Figure 4 Schematic diagram of creating a hash table by deduplicating key values ​​provided in an embodiment of the present application Figure 2 ,like Figure 4As shown, the SQL query instruction is "Select*from t1,t2 where t1.a=t2.a or t1.b=t2.b". Assuming that the optimizer instructs t2 to be the inner table, the query columns of the inner table are t2.a and t2.b. When the hash table is established, the data of any row is two data, namely the data of column a of the row and the data of column b of the row.

[0138] Take the first row in data table t2 as an example, calculate the hash value of the data in the first row a column as KEY1, and calculate the hash value of the data in the first row b column as KEY2, then compare whether KEY1 and KEY2 are the same. If they are the same (YES), that is, KEY1=KEY2, then the data in the a column and the data in the b column of the row (expressed as t2(a, b)) are mapped and stored in the hash bucket of KEY1. Because KEY1 and KEY2 are the same value, they correspond to the same hash bucket, that is, t2(a, b) is mapped and stored in the hash bucket of KEY2, which means that one copy is stored in one hash bucket; if they are not the same (NO), that is, KEY1≠KEY2, then the data in the a column and the data in the b column of the row (expressed as t2(a, b)) are stored in the hash bucket of KEY1 and the hash bucket of KEY2 respectively, which is equivalent to storing one copy of t2(a, b) in two different hash buckets. It can be seen that the process of constructing the hash table is the same as the process of constructing the hash table with t2 as the inner table in the above-mentioned SQL query instruction "Select*from t1,t2 where t1.a=t2.a or t1.a=t2.b", and the example hash table creation process is not repeated here.

[0139] After the hash table is created, when the join operation starts for the SQL query instruction "Select * from t1, t2 where t1.a = t2.a or t1.b = t2.b", the executor can traverse and scan each row of the outer table to determine whether to establish a join.

[0140] For example, when creating a hash table, the hash value of the data in column a of row 1 of internal table t2 is calculated to be 2, and the hash value of the data in column b of row 1 of internal table t2 is calculated to be 3. Then the data in row 1 of internal table t2 will be mapped and stored in hash bucket 2, and the data in row 1 of internal table t2 will also be mapped and stored in hash bucket 3.

[0141] When scanning the external table and traversing the data of each row of the external table to perform the join operation, the executor can traverse in order of rows. For example, when traversing the first row of the external table t1 first, assuming that the calculated hash value of the data in the a column of the first row is 1, and the hash value of the data in the b column of the first row is 3, the executor will analyze the hash values ​​1 and 3 of the external table in hash buckets 1 and 3 respectively according to the condition "t1.a=t2.a or t1.b=t2.b" to determine whether to establish a join between the first row of the external table and the first row of the internal table.

[0142] The executor determines whether to establish a connection in hash bucket 3. Hash bucket 3 stores the data of the inner table row 1, column a, and the inner table row 1, column b. Since the hash value of the outer table row 1, column a is 1, which is not equal to the hash value 2 of the inner table row 1, column a, the sub-condition "t1.a = t2.a" does not hold; however, since the hash value of the outer table row 1, column b is 3, which is equal to the hash value 3 of the inner table row 1, column b, the sub-condition "t1.b = t2.b" holds, and the executor establishes a connection between the outer table row 1 and the inner table row 1.

[0143] The executor will also determine whether to establish a connection in hash bucket 1. If hash bucket 1 stores data in columns a and b of a row in the inner table, it means that the row in the inner table has a corresponding hash value of 1. Furthermore, if the hash value of the data in column a of the row in the inner table is 1, the row in the inner table can establish a connection with the first row in the outer table, which will not be repeated here.

[0144] Based on this, the method provided by the embodiment of the present application can realize the creation of a hash table in a key value deduplication manner. When creating a hash table for an inner table, for any row of the inner table, the data in the inner table query column are compared for the same or different hash values. If they are all the same hash values, only one copy of the data needs to be mapped and stored in the hash bucket of the hash value, which can reduce the storage space occupied by the hash table; or, for any row of the inner table, the data in the inner table query column are compared for the same or different hash values. If they are not all the same hash values, one copy of the data needs to be mapped and stored in the hash buckets corresponding to each hash value, which can avoid missing the rows that should be connected during hash connection and improve the accuracy of hash connection.

[0145] In one possible implementation, the query columns of the outer table in the two sub-conditions are at least two query columns, and according to the hash value of the data of the row, the hash value identical to the hash value of the data of the row is searched in the hash table, including: performing the same or different comparison according to the hash values ​​of each data of the row to obtain a second comparison result; when the second comparison result indicates that the hash values ​​of each data of the row are all the same, searching the hash table for the hash value identical to the hash value; or, when the second comparison result indicates that the hash values ​​of each data of the row are not all the same, searching the hash table for the hash values ​​identical to the hash values ​​of each data of the row.

[0146] Exemplarily, when scanning the external table, for any row of the external table, the executor can compare the hash values ​​after calculating the hash values ​​of each data in the same row to obtain a second comparison result. The second comparison result is the result of comparing the hash values ​​of each data in a row of the external table, and is used to determine whether to use a merged query or a separate query when querying the rows of the inner table in the hash table for the data in that row.

[0147] When the second comparison result indicates that the hash values ​​of the data in the row are all the same, the hash value that is the same as the hash value is queried in the hash table. This method can be understood as a merge query method. When querying the rows of the inner table in the hash table using the merge query method, only the hash bucket corresponding to the same hash value needs to be queried, and no other hash buckets need to be queried. Therefore, unnecessary queries can be reduced, the hash table can be queried quickly, and the efficiency of establishing connections can be improved.

[0148] When the second comparison result indicates that the hash values ​​of the data in the row are not all the same, the hash values ​​that are the same as the hash values ​​of the data in the row are queried in the hash table respectively. This method can be understood as a separate query method. When querying the rows of the inner table in the hash table by separate query, it is necessary to query the hash bucket corresponding to each hash value of the data in the outer row, and it is not necessary to query the hash bucket corresponding to the hash value of the data other than the row. Since the hash bucket corresponding to each hash value of the data in the row is queried, it is possible to avoid missing the hash value in the hash table, and then a more comprehensive connection can be established, thereby improving the accuracy of the connection result.

[0149] For example, the SQL query instruction "Select * from t1, t2 where t1.a = t2.a or t1.b = t2.b" can be understood as a join operation of comparing two query columns (t1.a and t1.b) of the outer table with two query columns (t2.a and t2.b) of the inner table. Therefore, if the hash values ​​of the two data in columns a and b of a row in the inner table are different, and one copy is stored in each of the two hash buckets, when performing an outer scan, redundant duplicate joins may occur.

[0150] For example, if the hash value of the data in column a of the first row in the inner table is 2, and the hash value of the data in column b is 3, then when creating the hash table, a copy of the data in the row will be stored in hash bucket 2 and hash bucket 3 respectively. Assuming that when scanning the first row of the outer table, if the hash value of the data in column a is also 2, and the hash value of the data in column b is also 3, then the executor will query the data in the first row in hash bucket 2, and the data in columns a and b of the outer table are respectively matched with the data in columns a and b of the inner table, and the condition "t1.a=t2.a ort1.b=t2.b" is met, then the first row of the outer table will be joined with the first row of the inner table; the data in the first row will also be queried in hash bucket 3, and the data in columns a and b of the outer table are respectively matched with the data in columns a and b of the inner table, and the condition "t1.a=t2.a ort1.b=t2.b" is met, then the first row of the outer table will be joined with the first row of the inner table again, thus causing the problem of redundant repeated joins.

[0151] In a possible implementation, when establishing a connection for a row, it can be specifically implemented in the following manner: determining whether a row corresponding to an inner table connected to the row has been connected to the row; if it is determined that a row corresponding to an inner table connected to the row has been connected to the row, no connection is established repeatedly.

[0152] In this embodiment, for any row of the outer table, when a hash value identical to the hash value of the data of the row is queried, a condition comparison is performed based on two sub-conditions. When any of the two sub-conditions is met, when a connection is established for the row, the row of the inner table to be connected to the row can be first detected to see whether a connection has been established with the row of the outer table. If a connection has been established, a connection does not need to be established between the row of the inner table and the row of the outer table, thereby avoiding unnecessary repeated connection operations.

[0153] There are many ways to detect whether a row of the inner table that is about to be connected to the row has already been connected to the row of the outer table.

[0154] In one implementation, before a connection is established for a row of the outer table, the connection results of the established connections are first scanned, and the rows of the inner table that are already connected to the row of the outer table are determined in each connection result. Then, it is determined whether the row of the inner table to be connected already exists in the rows of the inner table that are already connected. If so, the connection between the row of the inner table and the row of the outer table is not repeatedly established.

[0155] For example, when scanning the 5th row of the outer table, a connection is established between the 5th row of the outer table and the 7th row of the inner table. Before the connection is established, the connection results of the established connections are scanned first. If it is determined that a connection between the 5th row of the outer table and the 7th row of the inner table already exists in each connection result, a connection is no longer established between the 5th row of the outer table and the 7th row of the inner table.

[0156] In another implementation, a flag bit may be added to the query column of the inner table to mark whether a row of data in the inner table has been connected to a row of the outer table currently being traversed when the data is stored in different hash buckets. For example, the flag bit may be a find_flag flag bit set on the tuple queried in the inner table, and the value of the find_flag flag bit may be true or false. For example, when the find_flag flag bit is true, it indicates that the row currently being queried in the outer table has been connected to the row in the inner table, and when the find_flag flag bit is false, it indicates that the row currently being queried in the outer table has not been connected to the row in the inner table. Through the find_flag flag, two tuples of the same address stored in different hash buckets of the hash table can be connected normally when they are queried for the first time. After the first connection is established, the find_flag flag of the tuple is changed to true. When the tuple is queried for the second time, since the find_flag flag is already true, although the conditions for establishing a connection are met when it is queried for the second time, no repeated redundant connections will be made. That is, the tuple will not be returned as a connection result tuple when it is found for the second time. In this way, the flag can be used to achieve the deduplication effect of preventing redundant repeated connections.

[0157] In an embodiment of the present application, when the outer table has at least two query columns, there may be a situation where duplicate connections are made when two sub-conditions of an or connection are compared to establish a connection. By determining whether the row corresponding to the inner table connected to the row has already been connected to the row, if it is determined that the row corresponding to the inner table connected to the row has already been connected to the row, the connection is not established repeatedly. This can avoid duplicate connections, reduce redundant connections, and improve connection efficiency.

[0158] Figure 5 Schematic diagram of the data table connection method provided in the embodiment of the present application Figure 2 When performing a hash join, the method provided in the embodiment of the present application relies on the condition that both sub-conditions are equal to the separable inner and outer elements. The separable inner and outer elements can be understood as the query columns about the first data table and the second data table on the left and right sides of the equal sign in the equation in the sub-conditions, respectively.

[0159] like Figure 5As shown, after the execution starts, you can first check whether the sub-conditions on both sides of or in the SQL query instruction are equivalent conditions that can separate the inner and outer elements. If not, you can execute the SQL query instruction according to other query instruction execution methods; if so, you can determine the inner table and the outer table, and create a hash table based on the query column data of the inner table.

[0160] When creating a hash table, you can use the key value deduplication method to calculate the hash value of each data in the inner table query column and store the corresponding mapping to create a hash table corresponding to the inner table. After the hash table is created, scan the outer table to perform a join operation, and you can traverse the data under each row of the outer table query column for the outer table query column.

[0161] When scanning a foreign table, for any row of data in the foreign table, if the hash values ​​of all the data in the row are the same, the hash table is queried using a combined query. For any row of data in the foreign table, if the hash values ​​of all the data in the row are not the same, the hash table is queried using a separate query.

[0162] It should be noted that before starting to scan the data of a row in the foreign table, you can reset the find_flag flag of each tuple to false so that the connection can be established normally when starting to connect to the row.

[0163] Figure 6 A schematic diagram of a hash table scan based on a flag bit provided in an embodiment of the present application, such as Figure 6 As shown, taking the SQL query instruction "Select*from t1,t2 where t1.a=t2.a or t1.b=t2.b" as an example, before scanning the nth row of the foreign table t1, the find_flag flag bit of each tuple in the hash table is reset to false, such as Figure 6 In the example, the find_flag flag bits of tuple t2(a, b) in hash bucket KEY1 and hash bucket KEY2 are both reset to false.

[0164] After starting to scan the nth row of the external table t1, calculate the hash value of column a in the nth row, for example, KEYA, and calculate the hash value of column b in the nth row, for example, KEYB. The hash value of the query column t2.a of the internal table t2 is represented by KEY1, and the hash value of the query column t2.b of the internal table t2 is represented by KEY2. Assume that the hash value KEY1 of the data in column a of a row of the internal table is equal to KEYA, that is, assume KEYA=KEY1, and the hash value KEY2 of the data in column b of the row of the internal table is equal to KEYB, that is, assume KEYB=KEY2. When scanning the corresponding hash bucket in the hash table, since the find_flag flag bits of the t2(a, b) tuples in hash bucket KEY1 and hash bucket KEY2 are both false, a normal join operation can be performed on the nth row. After the nth row of the external table is connected to the tuple in a hash bucket for the first time, the find_flag flag of the tuple in each hash bucket can be modified to true, so that when the tuple in each hash bucket is queried again, the find_flag flag is used to prohibit the tuple from being connected to the nth row of the external table again, thereby avoiding multiple repeated connections between the two.

[0165] like Figure 5 As shown, after traversing the data of all rows corresponding to the foreign table query column, that is, completing the scan of the foreign table, all connection results of the established connection can be returned.

[0166] Based on the method provided in the embodiment of the present application, in application scenarios including join operations such as executing data query on a database, when the equation in the SQL query instruction is wrapped by the or condition, the optimizer and the executor can use the hash join method to perform the hash join operation on the data table indicated in the SQL query instruction, thereby expanding the application scenarios of the hash join, indirectly realizing the or expansion function, and increasing the feasible execution methods of the SQL query instruction. In some scenarios, due to the use of the method provided in the embodiment of the present application, the number of scans of the data table is reduced, thereby reducing the amount of calculation and operation time during the scan, thereby improving the scanning efficiency of the data table, and thereby improving the efficiency of data query.

[0167] Figure 7 A schematic diagram of the structure of the data table connection device provided in the embodiment of the present application, such as Figure 7 As shown, an embodiment of the present application provides a data table connection device, which is applied to an electronic device, and the electronic device includes an optimizer and an executor, and the device includes:

[0168] The optimization module 701 is used to determine, through an optimizer, whether the SQL query instruction to be executed includes two sub-conditions connected by or, and whether the two sub-conditions are both equivalent conditions of the first data table and the second data table, where the first data table and the second data table are two data tables to be connected;

[0169] The optimization module 701 is further configured to indicate to the executor through the optimizer that one of the first data table and the second data table is an inner table and the other is an outer table when it is determined that the SQL query instruction includes two sub-conditions connected by or, and it is determined that the two sub-conditions are both equivalent conditions of the first data table and the second data table;

[0170] An execution module 702 is used to create a corresponding hash table for the inner table through an executor, where the hash table includes hash values ​​of data in the query column of the inner table within two sub-conditions;

[0171] The execution module 702 is further configured to execute a hash join on the query column of the foreign table through the executor according to the hash table.

[0172] In one possible implementation, the execution module 702 is specifically used to: traverse the data of each row corresponding to the query column of the inner table through the executor; for the data of any row, calculate the hash value of the row data, and according to the hash value of the row data, map the row data and store it in a hash bucket corresponding to the hash value of the row data; after traversing the data of each row corresponding to the query column of the inner table, obtain the hash table corresponding to the inner table.

[0173] In a possible implementation, the query columns of the inner table in the two sub-conditions are at least two query columns, and the execution module 702 is specifically used to: compare the hash values ​​of each data of the row for sameness or difference to obtain a first comparison result; when the first comparison result indicates that the hash values ​​of each data of the row are all the same, map and store a copy of each data of the row in the hash bucket corresponding to the hash value; or, when the first comparison result indicates that the hash values ​​of each data of the row are not all the same, map and store a copy of each data of the row in the hash bucket corresponding to the hash value of each data of the row.

[0174] In one possible implementation, the execution module 702 is specifically used to: traverse the data of each row corresponding to the query column of the external table through the executor; for the data of any row, calculate the hash value of the row data, and search the hash table for the hash value that is the same as the hash value of the row data based on the hash value of the row data; when a hash value that is the same as the hash value of the row data is found, perform condition comparison based on two sub-conditions, and establish a connection for the row when any of the two sub-conditions is met; after traversing the data of each row corresponding to the query column of the external table, complete the hash connection of the first data table and the second data table.

[0175] In a possible implementation, the query columns of the outer table in the two sub-conditions are at least two query columns, and the execution module 702 is specifically used to: compare the hash values ​​of each data of the row for sameness or difference to obtain a second comparison result; when the second comparison result indicates that the hash values ​​of each data of the row are all the same, query the hash value identical to the hash value in the hash table; or, when the second comparison result indicates that the hash values ​​of each data of the row are not all the same, query the hash value identical to the hash value of each data of the row in the hash table.

[0176] In a possible implementation, the execution module 702 is specifically used to: determine whether the row corresponding to the inner table connected to the row has been connected to the row; if it is determined that the row corresponding to the inner table connected to the row has been connected to the row, no connection is established repeatedly.

[0177] The data table connection device provided in the embodiment of the present application can be used to execute the technical solution of the data table connection method in any of the above embodiments of the present application. Its implementation principle and technical effect are similar, and this embodiment will not be repeated here.

[0178] Figure 8 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application is shown in FIG. Figure 8 As shown, the electronic device of this embodiment may include: at least one processor 801; and a memory 802 communicatively connected to the at least one processor; wherein the memory 802 stores instructions executable by the at least one processor 801, and the instructions are executed by the at least one processor 801 so that the electronic device executes a method as in any of the above embodiments.

[0179] Optionally, the memory 802 may be independent or integrated with the processor 801 .

[0180] The implementation principle and technical effects of the electronic device provided in this embodiment can be found in the aforementioned embodiments and will not be described in detail here.

[0181] An embodiment of the present application further provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions. When a processor executes the computer-executable instructions, the method described in any of the above embodiments is implemented.

[0182] An embodiment of the present application further provides a computer program product, including a computer program, which implements the method described in any of the above embodiments when executed by a processor.

[0183] In the several embodiments provided in this application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic, for example, the division of the modules is only a logical function division, and there may be other division methods in actual implementation, such as multiple modules can be combined or integrated into another system, or some features can be ignored or not executed.

[0184] The above-mentioned integrated module implemented in the form of a software function module can be stored in a computer-readable storage medium. The above-mentioned software function module is stored in a storage medium, including a number of instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) or a processor to perform some steps of the method described in each embodiment of the present application.

[0185] It should be understood that the above-mentioned processor can be a processing unit (Central Processing Unit, CPU), or other general-purpose processors, digital signal processors (Digital Signal Processor, DSP), application-specific integrated circuits (Application Specific Integrated Circuit, ASIC), etc. The general-purpose processor can be a microprocessor or the processor can also be any conventional processor, etc. The steps of the method disclosed in the application can be directly embodied as being executed by a hardware processor, or can be executed by a combination of hardware and software modules in the processor. The memory may include random access memory (Random Access Memory, RAM), and may also include non-volatile memory (Non-Volatile Memory, NVM), such as at least one disk storage, and can also be a USB flash drive, a mobile hard disk, a read-only memory, a disk or an optical disk, etc.

[0186] The above storage medium can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read only memory (EEPROM), erasable programmable read only memory (EPROM), programmable read only memory (PROM), read only memory (ROM), magnetic memory, flash memory, magnetic disk or optical disk. The storage medium can be any available medium that can be accessed by a general or special-purpose computer.

[0187] An exemplary storage medium is coupled to a processor so that the processor can read information from the storage medium and write information to the storage medium. Of course, the storage medium can also be a component of the processor. The processor and the storage medium can be located in a dedicated integrated circuit. Of course, the processor and the storage medium can also exist as discrete components in an electronic device or a main control device.

[0188] It should be noted that, in this article, the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, an element defined by the sentence "comprises a ..." does not exclude the existence of other identical elements in the process, method, article or device including the element.

[0189] The serial numbers of the above-mentioned embodiments of the present application are for description only and do not represent the advantages or disadvantages of the embodiments.

[0190] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present application, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes a number of instructions for a terminal device (which can be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in each embodiment of the present application.

[0191] The above are only preferred embodiments of the present application, and are not intended to limit the patent scope of the present application. Any equivalent structure or equivalent process transformation made using the contents of the present application specification and drawings, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present application.

[0192] Those skilled in the art will readily come up with other implementations of the embodiments of the present application after considering the specification and practicing the application disclosed herein. The present application is intended to cover any modifications, uses or adaptations of the embodiments of the present application, which follow the general principles of the embodiments of the present application and include common knowledge or customary technical means in the art that are not disclosed in the embodiments of the present application.

[0193] It should be understood that the embodiments of the present application are not limited to the precise structures described above and shown in the drawings, and various modifications and changes can be made without departing from the scope thereof. The scope of the embodiments of the present application is limited only by the appended claims.

Claims

1. A data table connection method, characterized in that: Applied to an electronic device, the electronic device includes an optimizer and an executor, and the method includes: Determining, by the optimizer, whether the SQL query instruction to be executed includes two sub-conditions connected by or, and determining whether the two sub-conditions are both equivalent conditions of a first data table and a second data table, where the first data table and the second data table are two data tables to be connected; In a case where it is determined that the SQL query instruction includes two sub-conditions connected by or, and it is determined that the two sub-conditions are both equivalent conditions of the first data table and the second data table, indicating to the executor through the optimizer that one of the first data table and the second data table is an inner table and the other is an outer table; Creating a corresponding hash table for the inner table by the executor, the hash table including hash values ​​of data in the query column of the inner table within the two sub-conditions; The executor performs a hash join on the query columns of the foreign table according to the hash table.

2. The method according to claim 1, characterized in that The step of creating a corresponding hash table for the internal table by the executor includes: Traversing the data of each row corresponding to the query column of the inner table through the executor; For any of the rows of data, a hash value of the row of data is calculated, and according to the hash value of the row of data, the row of data is mapped and stored in a hash bucket corresponding to the hash value of the row of data; After traversing the data of each row corresponding to the query column of the inner table, a hash table corresponding to the inner table is obtained.

3. The method according to claim 2, characterized in that The query columns of the inner table in the two sub-conditions are at least two query columns, and according to the hash value of the data of the row, the data of the row is mapped and stored in a hash bucket corresponding to the hash value of the data of the row, including: Comparing the hash values ​​of the data in the row for similarity or difference to obtain a first comparison result; When the first comparison result indicates that the hash values ​​of the data in the row are all the same, mapping and storing a copy of the data in the row in the hash bucket corresponding to the hash value; or, When the first comparison result indicates that the hash values ​​of the data in the row are not all the same, each data in the row is mapped and stored in a hash bucket corresponding to the hash value of each data in the row.

4. The method according to any one of claims 1 to 3, characterized in that: The performing hash join on the query column of the foreign table by the executor according to the hash table includes: Traversing the data of each row corresponding to the query column of the foreign table by the executor; For any of the rows of data, a hash value of the row of data is calculated, and based on the hash value of the row of data, a hash value identical to the hash value of the row of data is searched in the hash table; When a hash value identical to the hash value of the data of the row is found, condition comparison is performed according to the two sub-conditions, and when any one of the two sub-conditions is met, a connection is established for the row; After traversing the data of each row corresponding to the query column of the external table, the hash connection of the first data table and the second data table is completed.

5. The method according to claim 4, characterized in that The query columns of the foreign table in the two sub-conditions are at least two query columns, and searching the hash table for a hash value that is the same as the hash value of the data in the row according to the hash value of the data in the row includes: Comparing the data in the row for similarity or difference based on their respective hash values ​​to obtain a second comparison result; When the second comparison result indicates that the hash values ​​of the data in the row are all the same, searching the hash table for a hash value that is the same as the hash value; or, When the second comparison result indicates that the hash values ​​of the data in the row are not all the same, the hash values ​​that are the same as the hash values ​​of the data in the row are searched in the hash table respectively.

6. The method according to claim 5, characterized in that The establishing connection to the row comprises: Determine whether a row corresponding to the internal table connected to the row has been connected to the row; If it is determined that the row corresponding to the inner table connected to the row has already been connected to the row, the connection is not established repeatedly.

7. A data table connection device, characterized in that: Applied to an electronic device, the electronic device includes an optimizer and an executor, and the device includes: an optimization module, configured to determine, by the optimizer, whether the SQL query instruction to be executed includes two sub-conditions connected by or, and determine whether the two sub-conditions are both equivalent conditions of a first data table and a second data table, wherein the first data table and the second data table are two data tables to be connected; The optimization module is further configured to, when it is determined that the SQL query instruction includes two sub-conditions connected by or, and it is determined that the two sub-conditions are both equivalent conditions of the first data table and the second data table, indicate to the executor through the optimizer that one of the first data table and the second data table is an inner table and the other is an outer table; An execution module, configured to create a corresponding hash table for the inner table through the executor, wherein the hash table includes hash values ​​of data in a query column of the inner table within the two sub-conditions; The execution module is further configured to execute a hash connection on the query column of the foreign table through the executor according to the hash table.

8. An electronic device, characterized in that: include: Memory and processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory, so that the processor performs the method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 6 when executed by a processor.

10. A computer program product, characterized in that The method comprises a computer program, which implements the method according to any one of claims 1 to 6 when being executed by a processor.

Images