SQL Statement Execution Method, Device, Electronic Device, and Storage Medium

Abstract

This specification provides an SQL statement execution method and apparatus, an electronic device, and a storage medium. The method includes: If there is a nested loop join operator in the execution plan of the SQL statement and there is no operator for restricting the number of data rows in the execution result, then every n data rows of the outer table are taken as a batch, and the nested loop join operator is executed sequentially for each obtained batch, where n is an integer greater than 1; If there is a nested loop join operator and an operator for restricting the number of data rows in the execution result in the execution plan of the SQL statement, then at least one batch is sequentially determined in the outer table, and after each batch is determined, the nested loop join operator is executed for the determined batch until the number of data rows in the execution result of the nested loop join operator is equal to k, the number of data rows included in each of the batches determined in the first m times is less than n, m is an integer greater than 0, and k is the number indicated by the operator for restricting the number of data rows in the execution result.

Description

Technical Field

[0001] One or more embodiments of this specification relate to the field of database technology, and in particular, to a method and apparatus for executing SQL statements, an electronic device, and a storage medium. Background Art

[0002] In a database system, Nested Loop Join (NLJ) is a commonly used algorithm for performing table joins. This algorithm compares each row in an outer table with all rows in an inner table to find row pairs that satisfy the join condition. To accelerate nested loop joins, a commonly used method in the industry is to batch the outer table, thereby optimizing the I / O for scanning the inner table and achieving the purpose of performance improvement; especially in a distributed scenario, this batching method can also reduce the cost of RPC, thereby improving query performance.

[0003] However, in related technologies, nested loop joins adopt a consistent execution method in various scenarios, resulting in room for improvement in performance in some scenarios. Summary of the Invention

[0004] In view of this, one or more embodiments of this specification provide a method and apparatus for executing SQL statements, an electronic device, and a storage medium.

[0005] To achieve the above object, one or more embodiments of this specification provide the following technical solutions:

[0006] According to a first aspect of one or more embodiments of this specification, a method for executing an SQL statement is proposed. The method includes:

[0007] If the execution plan of the SQL statement to be processed has a nested loop join operator and does not have an operator for restricting the number of data rows in the execution result, then every n data rows of the outer table are used as a batch, and the nested loop join operator is executed for each obtained batch and the inner table in turn, where n is an integer greater than 1;

[0008] If the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for restricting the number of data rows in the execution result, then at least one batch is determined in the outer table in turn, and after each batch is determined, the nested loop join operator is executed for the determined batch and the inner table until the number of data rows in the execution result of the nested loop join operator is equal to k, where the number of data rows included in each determined batch is the same or different, the number of data rows included in each of the first m batches determined is less than n, m is an integer greater than 0, and k is the number indicated by the operator for restricting the number of data rows in the execution result.

[0009] In a possible embodiment of this specification, sequentially determining at least one batch in the outer layer table includes:

[0010] Sequentially determining at least one batch in the outer layer table in a manner of increasing the number of data rows batch by batch.

[0011] In a possible embodiment of this specification, the manner of sequentially determining at least one batch in the outer layer table in a manner of increasing the number of data rows batch by batch includes:

[0012] Sequentially determining at least one batch in the outer layer table in a manner that the first batch contains k data rows and the number of data rows increases by p for each subsequent batch, where p is an integer greater than 0.

[0013] In a possible embodiment of this specification, the manner of sequentially determining at least one batch in the outer layer table in a manner of increasing the number of data rows batch by batch includes:

[0014] Sequentially determining at least one batch in the outer layer table in a manner that the first batch contains k data rows and the number of data rows expands by q times for each subsequent batch, where q is an integer greater than 1.

[0015] In a possible embodiment of this specification, the manner of sequentially determining at least one batch in the outer layer table in a manner of increasing the number of data rows batch by batch includes:

[0016] Sequentially determining at least one batch in the outer layer table in a manner of increasing the number of data rows batch by batch until the number of data rows in the determined batch reaches n, and then sequentially determining at least one batch in the outer layer table with every n data rows as a batch.

[0017] In a possible embodiment of this specification, the manner of sequentially determining at least one batch in the outer layer table in a manner of increasing the number of data rows batch by batch until the number of data rows in the determined batch reaches n, and then sequentially determining at least one batch in the outer layer table with every n data rows as a batch includes:

[0018] Sequentially determining at least one batch in the outer layer table in a manner of increasing the number of data rows batch by batch until the number of data rows in the determined batch is equal to n, and then sequentially determining at least one batch in the outer layer table with every n data rows as a batch;

[0019] Determine at least one batch in the outer table in turn in the manner of increasing the number of data rows batch by batch until the number of data rows in the determined batch is greater than n, then update the number of data rows in the determined batch to n, and then determine at least one batch in the outer table in the manner of taking every n data rows as a batch.

[0020] In a possible embodiment of the present specification, if the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for restricting the number of data rows in the execution result, then determine at least one batch in the outer table in turn, and execute the nested loop join operator for the determined batch and the inner table after each batch is determined until the number of data rows in the execution result of the nested loop join operator is equal to k, including:

[0021] If the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for restricting the number of data rows in the execution result, and does not have a non-streaming type operator, then determine at least one batch in the outer table in turn, and execute the nested loop join operator for the determined batch and the inner table after each batch is determined until the number of data rows in the execution result of the nested loop join operator is equal to k.

[0022] According to the second aspect of one or more embodiments of the present specification, a SQL statement execution device is provided, and the device includes:

[0023] A first execution module, configured to, if the execution plan of the SQL statement to be processed has a nested loop join operator and does not have an operator for restricting the number of data rows in the execution result, take every n data rows of the outer table as a batch, and execute the nested loop join operator for each obtained batch and the inner table in turn, where n is an integer greater than 1;

[0024] A second execution module, configured to, if the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for restricting the number of data rows in the execution result, determine at least one batch in the outer table in turn, and execute the nested loop join operator for the determined batch and the inner table after each batch is determined until the number of data rows in the execution result of the nested loop join operator is equal to k, where the number of data rows included in each determined batch is the same or different, and the number of data rows included in each of the first m batches determined is less than n, m is an integer greater than 0, and k is the number indicated by the operator for restricting the number of data rows in the execution result.

[0025] According to a third aspect of one or more embodiments of the present specification, a computer program product is provided, including a computer program / instructions, which, when executed by a processor, implement the steps of the method described in the first aspect.

[0026] According to a fourth aspect of one or more embodiments of the present specification, an electronic device is provided, including:

[0027] A processor;

[0028] A memory for storing instructions executable by the processor;

[0029] wherein, the processor implements the method described in the first aspect by running the executable instructions.

[0030] According to a fifth aspect of one or more embodiments of the present specification, a computer-readable storage medium is provided, on which computer instructions are stored, and when the instructions are executed by a processor, the steps of the method described in the first aspect are implemented.

[0031] The technical solutions provided by the embodiments of the present specification may include the following beneficial effects:

[0032] The SQL statement execution method provided in the embodiments of this specification, if the execution plan of the SQL statement to be processed has a nested loop join operator and does not have an operator for restricting the number of data rows in the execution result, then every n data rows of the outer table are taken as a batch, and the nested loop join operator is executed for each obtained batch and the inner table in turn, where n is an integer greater than 1; if the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for restricting the number of data rows in the execution result, then at least one batch is determined in the outer table in turn, and after each batch is determined, the nested loop join operator is executed for the determined batch and the inner table until the number of data rows in the execution result of the nested loop join operator is equal to k, where the number of data rows included in each batch determined each time may be the same or different, and the number of data rows included in each of the first m batches determined is less than n, m is an integer greater than 0, and k is the number indicated by the operator for restricting the number of data rows in the execution result. In other words, if the SQL statement to be processed does not have an operator for restricting the number of data rows in the execution result, the nested loop join operator is executed in the conventional batch accumulation manner. If the execution plan of the SQL statement to be processed has an operator for restricting the number of data rows in the execution result, the nested loop join operator can be executed until the number of data rows in the execution result reaches the number indicated by the operator for restricting the number of data rows in the execution result, thereby avoiding invalid redundant calculations caused by executing the nested loop join operator for all data rows of the outer table, improving the execution efficiency of the SQL statement, and in this case, the number of data rows in the first m batches is also controlled to be less than the number of data rows in the batches in the conventional batch accumulation manner, so that the nested loop join operator can be executed for as few data rows in the outer table as possible to satisfy that the number of data rows in the execution result reaches the number indicated by the operator for restricting the number of data rows in the execution result, so as to further improve the execution efficiency of the SQL statement. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] Figure 1 is a schematic diagram of a distributed database provided by an exemplary embodiment.

[0034] Figure 2 is a flowchart of an SQL statement execution method provided by an exemplary embodiment.

[0035] Figure 3 is a schematic diagram of batching the outer table provided by an exemplary embodiment.

[0036] Figure 4 is a schematic diagram of the structure of a device provided by an exemplary embodiment.

[0037] Figure 5 is a block diagram of an SQL statement execution device provided by an exemplary embodiment. Detailed Implementation Modes

[0038] Here, exemplary embodiments will be described in detail, and examples are shown in the accompanying drawings. When the following description refers to the accompanying drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The implementation modes described in the following exemplary embodiments do not represent all implementation modes consistent with one or more embodiments of this specification. On the contrary, they are merely examples of devices and methods consistent with some aspects of one or more embodiments of this specification as detailed in the appended claims.

[0039] It should be noted that: In other embodiments, the steps of the corresponding method are not necessarily executed in the order shown and described in this specification. In some other embodiments, the steps included in the method may be more or fewer than those described in this specification. In addition, a single step described in this specification may be decomposed into multiple steps for description in other embodiments; and multiple steps described in this specification may also be combined into a single step for description in other embodiments.

[0040] Based on the above technical problems mentioned in the background art, at least one embodiment of this specification provides a method for executing SQL statements. This method can improve the execution efficiency of the nested loop join operator in the SQL statement when the database executes it, avoid invalid and redundant calculations, so that each SQL statement is executed in an appropriate and efficient manner.

[0041] Exemplarily, this method can be applied to the Figure 1 distributed database shown exemplarily in the accompanying drawings.

[0042] Please refer to the Figure 2 , which exemplarily shows the flow of this method, including step S201 and step S202.

[0043] In step S201, if the execution plan of the SQL statement to be processed has a nested loop join operator and does not have an operator for restricting the number of data rows in the execution result, then every n data rows of the outer table are taken as a batch, and the nested loop join operator is executed for each obtained batch and the inner table in turn, where n is an integer greater than 1.

[0044] Among them, the execution plan of the SQL statement refers to the execution plan selected by the optimizer during the plan generation process.

[0045] Among them, the nested loop join operator is used to perform a nested loop join on two data tables. If it indicates the outer table and the inner table in the two data tables, it shall prevail. If it does not indicate the outer table and the inner table in the two data tables, the outer table and the inner table in the two can be randomly determined, or the data table with a smaller number of data rows can be used as the outer table, and the other data table as the inner table.

[0046] Among them, n is the number of data rows in a batch of the regular batch execution method preset in the database. For example, it can be 10, 100, etc.

[0047] Among them, the operator used to limit the number of data rows in the execution result can be the LIMIT operator, etc.

[0048] For example, in this step, the first n rows of the outer table can be taken as the first batch and perform a nested loop join with the inner table, and then the (n + 1)th row to the 2nth row can be taken as the second batch and perform a nested loop join with the inner table, and so on, until all the data rows in the outer table have completed the nested loop join. It should be understood that the number of data rows in the last batch of the outer table may be equal to n or less than n.

[0049] Preferably, in order to accelerate the nested loop join, an appropriate index can also be created for the inner table according to the join condition of the nested loop join operator. In this case, for each batch in the outer loop, it is not necessary to scan all the records of the inner table, but the index is used to quickly locate the potential matching rows. Thus, the amount of data of the inner table that needs to be accessed is greatly reduced, and the performance of the nested loop join can be significantly improved.

[0050] In step S202, if there is a nested loop join operator and an operator used to limit the number of data rows in the execution result in the execution plan of the SQL statement to be processed, at least one batch is sequentially determined in the outer table, and after each batch is determined, the nested loop join operator is executed for the determined batch and the inner table until the number of data rows in the execution result of the nested loop join operator is equal to k, where the number of data rows included in each batch determined each time may be the same or different, and the number of data rows included in each of the first m batches determined is less than n, m is an integer greater than 0, and k is the number indicated by the operator used to limit the number of data rows in the execution result.

[0051] Among them, the execution plan of the SQL statement refers to the execution plan selected by the optimizer during the plan generation process.

[0052] Among them, the operator used to limit the number of data rows in the execution result can be the LIMIT operator, etc.

[0053] This step can be executed only when there are no non-streaming type operators in the SQL statement to be processed, that is, when each operator in the SQL statement to be processed is a streaming type operator. Specifically: If the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for restricting the number of data rows in the execution result, and there are no non-streaming type operators, then at least one batch is sequentially determined in the outer table, and after each batch is determined, the nested loop join operator is executed for the determined batch and the inner table until the number of data rows in the execution result of the nested loop join operator is equal to k.

[0054] Among them, streaming type operators refer to those operators that can generate output results while reading input data. Such operators do not need to wait for all input data to be ready before starting to output results; on the contrary, it can start processing and generating results when receiving partial data. Typical streaming type operators include NLJ operators, Filter operators, LIMIT operators, etc. Typical non-streaming type operators include Sort operators, Hash Join operators, Hash GroupBy operators, etc.

[0055] This step at least controls the number of data rows in the first m batches to be less than n, so that it is possible to attempt to execute the nested loop join for fewer data rows in the outer table at the initial stage, and obtain k data rows in the execution result as efficiently as possible.

[0056] Preferably, when sequentially determining at least one batch in the outer table, at least one batch can be sequentially determined in the outer table in a way that the number of data rows increases batch by batch.

[0057] For example, at least one batch is sequentially determined in the outer table in a way that the first batch contains k data rows and the number of data rows increases by p for each subsequent batch, where p is an integer greater than 0. That is, the first batch contains k data rows, and the i-th batch contains k + ip data rows, where i is an integer greater than 1.

[0058] If each data row in the outer table has a data row that satisfies the join condition in the inner table, then the first batch is set to k data rows, and k data rows can be directly obtained in the execution result after executing the nested loop join for the first batch, thus completing the execution of the nested loop join operator. If not every data row in the outer table has a data row that satisfies the join condition in the inner table, then each subsequent batch is determined in a way that the number of data rows increases by p, and the nested loop join can be gradually executed with more data rows to obtain k data rows in the execution result as soon as possible.

[0059] For another example, at least one batch is sequentially determined in the outer table in such a way that the first batch contains k data rows and the number of data rows in each subsequent batch is q times that of the previous batch, where q is an integer greater than 1. That is, the first batch contains k data rows, and the i-th batch contains k * q i data rows, where i is an integer greater than 1. If q = 2, the batching situation of the outer table is as shown in the appendix Figure 3 .

[0060] If each data row in the outer table has a data row in the inner table that satisfies the join condition, setting the first batch to k data rows allows k data rows to be directly obtained in the execution result after performing a nested loop join on the first batch, thus completing the execution of the nested loop join operator. If not every data row in the outer table has a data row in the inner table that satisfies the join condition, subsequent batches are determined in such a way that the number of data rows in each batch is q times that of the previous batch, enabling the nested loop join to be gradually executed with a larger number of data rows to quickly obtain k data rows in the execution result.

[0061] For another example, at least one batch is sequentially determined in the outer table by increasing the number of data rows in each batch until the number of data rows in the determined batch reaches n, and then at least one batch is sequentially determined in the outer table with every n data rows as a batch.

[0062] That is: at least one batch is sequentially determined in the outer table by increasing the number of data rows in each batch until the number of data rows in the determined batch is equal to n, and then at least one batch is sequentially determined in the outer table with every n data rows as a batch;

[0063] at least one batch is sequentially determined in the outer table by increasing the number of data rows in each batch until the number of data rows in the determined batch is greater than n, then the number of data rows in the determined batch is updated to n, and then at least one batch is sequentially determined in the outer table with every n data rows as a batch.

[0064] In other words, the method of increasing the number of data rows in each batch is limited by the number of data rows in a batch in the conventional batch accumulation method and cannot exceed this limit; batches that have not reached this limit attempt to obtain k data rows in the execution result with batches having a smaller number of data rows. If unable to obtain them after the attempt, the execution continues according to the conventional batch accumulation method.

[0065] For another example, at least one batch is sequentially determined in the outer table in the manner of increasing the number of data rows batch by batch, and after each execution of the nested loop join for the confirmed batch and the inner table, the number of data rows in the next batch is determined according to the number of missing data rows in the execution result. For example, if the number of missing data rows in the execution result after a certain execution of the nested loop join for the confirmed batch and the inner table is greater than a preset threshold, then continue to determine the number of data rows in the next batch in the manner of increasing the number of data rows batch by batch; if the number of missing data rows in the execution result after a certain execution of the nested loop join for the confirmed batch and the inner table is not greater than the preset threshold, then determine the number of data rows in the next batch in the manner of decreasing the number of data rows batch by batch thereafter.

[0066] The above examples can be combined with each other to obtain more cases of the way of increasing the number of data rows batch by batch. For example, combining the first example and the third example above, the following way can be obtained: in the outer table, at least one batch is sequentially determined in the way that the first batch contains k data rows and p data rows are increased batch by batch until the number of data rows in the determined batch reaches n, and then at least one batch is sequentially determined in the outer table in the way that every n data rows form a batch, where p is an integer greater than 0. For example, combining the second example and the third example above, the following way can be obtained: in the outer table, at least one batch is sequentially determined in the way that the first batch contains k data rows and the number of data rows is expanded q times batch by batch until the number of data rows in the determined batch reaches n, and then at least one batch is sequentially determined in the outer table in the way that every n data rows form a batch, where q is an integer greater than 1. For example, combining the first example and the fourth example above, the following way can be obtained: in the outer table, at least one batch is sequentially determined in the way that the first batch contains k data rows and p data rows are increased batch by batch until the number of missing data rows in the execution result after performing the nested loop join on the confirmed batch and the inner table is not greater than the preset threshold, and then the number of data rows in the next batch is determined in the way that p data rows are reduced batch by batch. It should be noted that the way of reducing the number of data rows batch by batch still takes k as the lower limit, that is, it will not continue to decrease when it drops to k data rows, where p is an integer greater than 0. For example, combining the second example and the fourth example above, the following way can be obtained: in the outer table, at least one batch is sequentially determined in the way that the first batch contains k data rows and the number of data rows is expanded q times batch by batch until the number of missing data rows in the execution result after performing the nested loop join on the confirmed batch and the inner table is not greater than the preset threshold, and then the number of data rows in the next batch is determined in the way that the number of data rows is reduced by 1 / q batch by batch. It should be noted that the way of reducing the number of data rows batch by batch still takes k as the lower limit, that is, it will not continue to decrease when it drops to k data rows, where q is an integer greater than 1.

[0067] This method is a nested loop join algorithm for dynamically adjusting the batch size. By dynamically adjusting the batch size, for SQL statements that do not need to return all results, a large amount of invalid calculations can be reduced, thereby fundamentally improving the performance of this type of query.

[0068] For example, in the following SQL statement, there is a LIMIT operator in this SQL statement, and the execution result only needs to return one row of data. For each row of data in the outer table t1, there is a row of data in the inner table t2 that meets the condition of nested loop join: t1.b = t2.a. If the batch size of the conventional batch execution in the related art contains 1000 rows of data, then it is necessary to perform a nested loop join for all rows of data in the outer table t1 with 1000 rows of data as a batch, and finally return only one row of data after obtaining a large number of rows of data. By using this method to execute this SQL statement, the first row of the outer table t1 can be used as a batch to perform a nested loop join, and directly return after obtaining one row of data in the execution result, saving a large amount of invalid and redundant nested loop join calculations and improving the execution efficiency of this SQL statement.

[0069] SQL statement:

[0070] create table t1(a int primary key, b int, c int);

[0071] create table t2(a int primary key, b int, c int);

[0072] explain select * from t1, t2 where t1.b = t2.a LIMIT 1;

[0073] In the SQL statement execution method provided by the embodiments of this specification, if the execution plan of the SQL statement to be processed has a nested loop join operator and does not have an operator for restricting the number of data rows in the execution result, then every n data rows of the outer table are taken as a batch, and the nested loop join operator is executed for each obtained batch and the inner table in sequence, where n is an integer greater than 1; if the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for restricting the number of data rows in the execution result, then at least one batch is determined in sequence within the outer table, and after each batch is determined, the nested loop join operator is executed for the determined batch and the inner table until the number of data rows in the execution result of the nested loop join operator is equal to k, where the number of data rows included in each batch determined each time may be the same or different, the number of data rows included in each of the first m batches determined is less than n, m is an integer greater than 0, and k is the number indicated by the operator for restricting the number of data rows in the execution result. In other words, if the SQL statement to be processed does not have an operator for restricting the number of data rows in the execution result, the nested loop join operator is executed in the conventional batch accumulation manner. If the execution plan of the SQL statement to be processed has an operator for restricting the number of data rows in the execution result, the nested loop join operator can be executed until the number of data rows in the execution result reaches the number indicated by the operator for restricting the number of data rows in the execution result, thereby avoiding invalid redundant calculations caused by executing the nested loop join operator for all the data rows of the outer table, improving the SQL statement execution efficiency. In this case, the number of data rows in the first m batches is also controlled to be less than the number of data rows in a batch in the conventional batch accumulation manner, so that the nested loop join operator can be executed for as few data rows in the outer table as possible to satisfy that the number of data rows in the execution result reaches the number indicated by the operator for restricting the number of data rows in the execution result, further improving the SQL statement execution efficiency.

[0074] Figure 4 It is a schematic structural diagram of a device provided by an exemplary embodiment. Please refer to Figure 4 , at the hardware level, the device includes a processor 402, an internal bus 404, a network interface 406, a memory 408, and a non-volatile memory 410. Of course, it may also include other hardware required for other tasks. One or more embodiments of this specification can be implemented in a software manner. For example, the processor 402 reads the corresponding computer program from the non-volatile memory 410 into the memory 408 and then runs it. Of course, in addition to the software implementation manner, one or more embodiments of this specification do not exclude other implementation manners, such as a logic device or a combination of software and hardware, etc. That is to say, the execution subject of the following processing flow is not limited to each logic unit, and can also be hardware or a logic device.

[0075] Please refer to Figure 5 , the SQL statement execution device can be applied to devices such as Figure 4 shown to implement the technical solutions of this specification. The SQL statement execution device may include:

[0076] A first execution module 501, configured to, if the execution plan of the SQL statement to be processed has a nested loop join operator and does not have an operator for restricting the number of data rows in the execution result, take every n data rows of the outer table as a batch, and sequentially execute the nested loop join operator for each obtained batch and the inner table, where n is an integer greater than 1;

[0077] A second execution module 502, configured to, if the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for restricting the number of data rows in the execution result, sequentially determine at least one batch in the outer table, and execute the nested loop join operator for the determined batch and the inner table each time after determining a batch, until the number of data rows in the execution result of the nested loop join operator is equal to k, where the number of data rows included in each determined batch is the same or different, and the number of data rows included in each of the first m determined batches is less than n, m is an integer greater than 0, and k is the number indicated by the operator for restricting the number of data rows in the execution result.

[0078] In a possible embodiment of the present disclosure, when the second execution module is configured to sequentially determine at least one batch in the outer table, it is configured to:

[0079] Sequentially determine at least one batch in the outer table in a manner of increasing the number of data rows batch by batch.

[0080] In a possible embodiment of the present disclosure, when the second execution module is configured to sequentially determine at least one batch in the outer table in a manner of increasing the number of data rows batch by batch, it is configured to:

[0081] Sequentially determine at least one batch in the outer table in a manner that the first batch includes k data rows and the number of data rows increases by p batch by batch, where p is an integer greater than 0.

[0082] In a possible embodiment of the present disclosure, when the second execution module is configured to sequentially determine at least one batch in the outer table in a manner of increasing the number of data rows batch by batch, it is configured to:

[0083] Sequentially determine at least one batch in the outer table in a manner that the first batch includes k data rows and the number of data rows expands by q times batch by batch, where q is an integer greater than 1.

[0084] In a possible embodiment of the present disclosure, when the second execution module is used to sequentially determine at least one batch in the outer table in a manner of increasing the number of data rows batch by batch, it is used for:

[0085] Sequentially determine at least one batch in the outer table in a manner of increasing the number of data rows batch by batch until the number of data rows in the determined batch reaches n, and then sequentially determine at least one batch in the outer table in a manner of taking every n data rows as a batch.

[0086] In a possible embodiment of the present disclosure, when the second execution module is used to sequentially determine at least one batch in the outer table in a manner of increasing the number of data rows batch by batch until the number of data rows in the determined batch reaches n, and then sequentially determine at least one batch in the outer table in a manner of taking every n data rows as a batch, it is used for:

[0087] Sequentially determine at least one batch in the outer table in a manner of increasing the number of data rows batch by batch until the number of data rows in the determined batch is equal to n, and then sequentially determine at least one batch in the outer table in a manner of taking every n data rows as a batch;

[0088] Sequentially determine at least one batch in the outer table in a manner of increasing the number of data rows batch by batch until the number of data rows in the determined batch is greater than n, then update the number of data rows in the determined batch to n, and then sequentially determine at least one batch in the outer table in a manner of taking every n data rows as a batch.

[0089] In a possible embodiment of the present disclosure, the second execution module is used for:

[0090] If the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for restricting the number of data rows in the execution result, and does not have a non-streaming type operator, then sequentially determine at least one batch in the outer table, and after each determination of a batch, execute the nested loop join operator for the determined batch and the inner table until the number of data rows in the execution result of the nested loop join operator is equal to k.

[0091] One or more embodiments of this specification also propose a computer program product, including a computer program / instructions, and when the computing program / instructions are executed by a processor, the steps of the method provided in the first aspect are implemented.

[0092] One or more embodiments of this specification also propose a computer-readable storage medium, on which computer instructions are stored, and when the instructions are executed by a processor, the steps of the method as described in the first aspect are implemented.

[0093] The systems, devices, modules, or units described in the above embodiments can be specifically implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer, and the specific form of the computer can be a personal computer, laptop computer, cellular phone, camera phone, smart phone, personal digital assistant, media player, navigation device, email transceiver, game console, tablet computer, wearable device, or a combination of any several of these devices.

[0094] In a typical configuration, a computer includes one or more processors (CPUs), an input / output interface, a network interface, and memory.

[0095] The memory may include non-permanent memory in the computer-readable medium, in the form of random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. The memory is an example of a computer-readable medium.

[0096] Computer-readable media includes permanent and non-permanent, removable and non-removable media and can store information by any method or technology. The information can be computer-readable instructions, data structures, program modules, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassette tapes, magnetic disk storage, quantum memory, graphene-based storage media, or other magnetic storage devices, or any other non-transmission media that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transitory media, such as modulated data signals and carrier waves.

[0097] It should also be noted that the term "comprising", "including" or any other variation thereof is intended to cover non-exclusive inclusion, such that a process, method, commodity or device comprising a series of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such process, method, commodity or device. Without further limitation, an element defined by the statement "comprising an..." does not exclude the presence of additional identical elements in the process, method, commodity or device comprising the element.

[0098] The above describes specific embodiments of this specification. Other embodiments are within the scope of the appended claims. In some cases, the acts or steps recited in the claims can be performed in a different order than in the embodiments and still achieve the desired results. Additionally, the processes depicted in the figures do not necessarily require the particular order or sequential order shown to achieve the desired results. In certain embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0099] The terms used in one or more embodiments of this specification are for the purpose of describing specific embodiments only and are not intended to limit one or more embodiments of this specification. The singular forms "a", "the", and "said" used in one or more embodiments of this specification and the appended claims are also intended to include the plural forms unless the context clearly dictates otherwise. It should also be understood that the term "and / or" as used herein refers to and encompasses any and all possible combinations of one or more of the associated listed items.

[0100] The user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data for analysis, stored data, displayed data, etc.) involved in this specification are all information and data that have been authorized by the user or fully authorized by all parties, and the collection, use, and processing of the relevant data need to comply with the relevant laws, regulations, and standards of the relevant countries and regions, and corresponding operation entrances are provided for users to choose to authorize or reject.

[0101] It should be understood that although the terms first, second, third, etc. may be used in one or more embodiments of this specification to describe various information, such information should not be limited to these terms. These terms are only used to distinguish information of the same type from each other. For example, without departing from the scope of one or more embodiments of this specification, the first information may also be referred to as the second information, and similarly, the second information may also be referred to as the first information. Depending on the context, the word "if" as used herein can be interpreted as "when" or "while" or "in response to determining".

[0102] The above is only the preferred embodiment of one or more embodiments of this specification and is not intended to limit one or more embodiments of this specification. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principle of one or more embodiments of this specification shall be included within the scope protected by one or more embodiments of this specification.

Claims

1. A method for executing an SQL statement, the method comprising: If the execution plan of the SQL statement to be processed has a nested loop join operator and does not have an operator for restricting the number of data rows in the execution result, then each n data rows of the outer table are taken as a batch, and the nested loop join operator is executed for each obtained batch and the inner table in turn, where n is an integer greater than 1; If the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for restricting the number of data rows in the execution result, then at least one batch is sequentially determined within the outer table, and after each determination of a batch, the nested loop join operator is executed for the determined batch and the inner table until the number of data rows in the execution result of the nested loop join operator is equal to k, where the number of data rows included in each determined batch is the same or different, and the number of data rows included in each of the first m batches is less than n, m is an integer greater than 0, and k is the number indicated by the operator for restricting the number of data rows in the execution result.

2. The method for executing an SQL statement according to claim 1, wherein sequentially determining at least one batch within the outer table comprises: Sequentially determining at least one batch within the outer table in a manner of increasing the number of data rows batch by batch.

3. The method for executing an SQL statement according to claim 2, wherein sequentially determining at least one batch within the outer table in a manner of increasing the number of data rows batch by batch comprises: Sequentially determining at least one batch within the outer table in a manner that the first batch includes k data rows and each batch increases by p data rows, where p is an integer greater than 0.

4. The method for executing an SQL statement according to claim 2, wherein sequentially determining at least one batch within the outer table in a manner of increasing the number of data rows batch by batch comprises: Sequentially determining at least one batch within the outer table in a manner that the first batch includes k data rows and each batch expands by q times the number of data rows, where q is an integer greater than 1.

5. The method for executing an SQL statement according to claim 2, wherein sequentially determining at least one batch within the outer table in a manner of increasing the number of data rows batch by batch comprises: Sequentially determining at least one batch within the outer table in a manner of increasing the number of data rows batch by batch until the number of data rows in the determined batch reaches n, and then sequentially determining at least one batch within the outer table in a manner that each n data rows form a batch.

6. The method for executing an SQL statement according to claim 5, wherein sequentially determining at least one batch within the outer table in a manner of increasing the number of data rows batch by batch until the number of data rows in the determined batch reaches n, and then sequentially determining at least one batch within the outer table in a manner that each n data rows form a batch comprises: Determine at least one batch in the outer table in sequence in the manner of increasing the number of data rows batch by batch until the number of data rows in the determined batch is equal to n, and then determine at least one batch in the outer table in sequence in the manner of taking every n data rows as a batch; Determine at least one batch in the outer table in sequence in the manner of increasing the number of data rows batch by batch until the number of data rows in the determined batch is greater than n, then update the number of data rows in the determined batch to n, and then determine at least one batch in the outer table in sequence in the manner of taking every n data rows as a batch.

7. The method for executing an SQL statement according to claim 1, wherein if the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for restricting the number of data rows in the execution result, then determine at least one batch in the outer table in sequence, and after each determination of a batch, execute the nested loop join operator for the determined batch and the inner table until the number of data rows in the execution result of the nested loop join operator is equal to k, including: If the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for restricting the number of data rows in the execution result, and does not have a non-streaming type operator, then determine at least one batch in the outer table in sequence, and after each determination of a batch, execute the nested loop join operator for the determined batch and the inner table until the number of data rows in the execution result of the nested loop join operator is equal to k.

8. An SQL statement execution device, the device includes: A first execution module, configured to, if the execution plan of the SQL statement to be processed has a nested loop join operator and does not have an operator for restricting the number of data rows in the execution result, then take every n data rows of the outer table as a batch, and sequentially execute the nested loop join operator for each obtained batch and the inner table, where n is an integer greater than 1; A second execution module, configured to, if the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for restricting the number of data rows in the execution result, then determine at least one batch in the outer table in sequence, and after each determination of a batch, execute the nested loop join operator for the determined batch and the inner table until the number of data rows in the execution result of the nested loop join operator is equal to k, where the number of data rows included in each determined batch is the same or different, and the number of data rows included in each of the first m determined batches is less than n, m is an integer greater than 0, and k is the number indicated by the operator for restricting the number of data rows in the execution result.

9. The SQL statement execution device according to claim 8, wherein when the second execution module is used to determine at least one batch in the outer table in sequence, it is used for: Determine at least one batch in the outer table in sequence in the manner of increasing the number of data rows batch by batch.

10. The SQL statement execution device according to claim 9, wherein when the second execution module is used to sequentially determine at least one batch in the outer table in the manner of increasing the number of data rows batch by batch, it is used for: Determine at least one batch in sequence within the outer table in such a way that the first batch contains k data rows and each subsequent batch increases by p data rows, where, p is an integer greater than 0.

11. The SQL statement execution device according to claim 9, wherein when the second execution module is used to sequentially determine at least one batch in the outer table in the manner of increasing the number of data rows batch by batch, it is used for: Determine at least one batch in sequence within the outer table in such a way that the first batch contains k data rows and each subsequent batch expands by q times the number of data rows, where, q is an integer greater than 1.

12. The SQL statement execution device according to claim 9, wherein when the second execution module is used to sequentially determine at least one batch in the outer table in the manner of increasing the number of data rows batch by batch, it is used for: Sequentially determine at least one batch in the outer table in the manner of increasing the number of data rows batch by batch until the number of data rows in the determined batch reaches n, and then sequentially determine at least one batch in the outer table in the manner of taking every n data rows as a batch.

13. The SQL statement execution device according to claim 8, wherein the second execution module is used for: If the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for restricting the number of data rows in the execution result, and does not have a non-streaming type operator, then sequentially determine at least one batch in the outer table, and after each determination of a batch, execute the nested loop join operator for the determined batch and the inner table until the number of data rows in the execution result of the nested loop join operator is equal to k.

14. A computer program product, comprising computer programs / instructions, which when executed by a processor implement the steps of the method according to any one of claims 1 to 7.

15. An electronic device, comprising: A processor; A memory for storing processor-executable instructions; Wherein, the processor realizes the method according to any one of claims 1 to 7 by running the executable instructions.

16. A computer-readable storage medium, on which computer instructions are stored, and when the instructions are executed by a processor, the steps of the method according to any one of claims 1 to 7 are realized.

Images