SQL statement execution method and device, electronic equipment and storage medium

Abstract

The invention provides an SQL (Structured Query Language) statement execution method and device, electronic equipment and a storage medium, and the method comprises the following steps: if an execution plan of an SQL statement has a nested loop connection operator and does not have an operator for limiting the number of data rows in an execution result, taking every n data rows of an outer layer table as a batch, executing a nested loop connection operator for each obtained batch in sequence, wherein n is an integer greater than 1; if the execution plan of the SQL statement has a nested loop connection operator and an operator used for limiting the number of data rows in the execution result, sequentially determining at least one batch in the outer layer table, and executing the nested loop connection operator for the determined batch after one batch is determined every time, the number of the data rows contained in each batch in the previous m determined batches is smaller than n until the number of the data rows in the execution result of the nested loop connection operator is equal to k, m is an integer larger than 0, and k is the number indicated by the operator used for limiting the number of the data rows in the execution result.

Description

Technical Field

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

[0002] In database systems, 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 the inner table to find row pairs that meet the join conditions. In order to speed up nested loop joins, a common method in the industry is to batch the outer table, thereby optimizing the IO of the inner table scan to achieve the purpose of improving performance; especially in distributed scenarios, this batching method can also reduce the cost of RPC, thereby improving query performance.

[0003] However, in the related art, nested loop joins adopt a consistent execution method in various scenarios, resulting in the performance of some scenarios to be improved. Summary of the invention

[0004] In view of this, one or more embodiments of the present specification provide a SQL statement execution method and device, an electronic device, and a storage medium.

[0005] To achieve the above objectives, one or more embodiments of this specification provide the following technical solutions: According to a first aspect of one or more embodiments of this specification, a method for executing a SQL statement is proposed, 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 limiting the number of data rows in the execution result, 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 contains a nested loop join operator and an operator for limiting the number of data rows in the execution result, at least one batch is determined in sequence in the outer table, and the nested loop join operator is executed on the determined batch and the inner table each time a batch is determined, until the number of data rows in the execution result of the nested loop join operator is equal to k, wherein the number of data rows contained in each determined batch is the same or different, the number of data rows contained in each batch determined for 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 limiting the number of data rows in the execution result.

[0006] In a possible embodiment of the present specification, determining at least one batch in sequence in the outer table includes: At least one batch is sequentially determined in the outer table in a manner of increasing the number of data rows batch by batch.

[0007] In a possible embodiment of the present specification, determining at least one batch in the outer table in sequence by increasing the number of data rows batch by batch includes: At least one batch is determined in sequence in the outer table in such a manner that the first batch contains k data rows and p data rows are added batch by batch, where p is an integer greater than 0.

[0008] In a possible embodiment of the present specification, determining at least one batch in the outer table in sequence by increasing the number of data rows batch by batch includes: At least one batch is determined in sequence in the outer table in such a manner that the first batch contains k data rows and each batch is expanded by q times the data rows, where q is an integer greater than 1.

[0009] In a possible embodiment of the present specification, determining at least one batch in the outer table in sequence by increasing the number of data rows batch by batch includes: At least one batch is determined 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 at least one batch is determined in the outer table in a manner of taking every n data rows as a batch.

[0010] In a possible embodiment of the present specification, determining 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 determining at least one batch in the outer table in a manner of taking every n data rows as a batch, includes: At least one batch is sequentially determined in the outer table in a manner of increasing the number of data rows in batches until the number of data rows in the determined batches is equal to n, and then at least one batch is sequentially determined in the outer table in a manner of taking every n data rows as a batch; At least one batch is determined in the outer table in sequence by increasing the number of data rows batch by 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 thereafter at least one batch is determined in the outer table in sequence by taking every n data rows as a batch.

[0011] 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 limiting the number of data rows in the execution result, 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, including: If the execution plan of the SQL statement to be processed contains a nested loop join operator and an operator for limiting the number of data rows in the execution result, and does not contain a non-streaming operator, then at least one batch is determined in the outer table in sequence, and after each batch is determined, the nested loop join operator is executed on 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.

[0012] According to a second aspect of one or more embodiments of this specification, a SQL statement execution device is provided, the device comprising: A first execution module is 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 limiting 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, wherein n is an integer greater than 1; The second execution module is used to determine at least one batch in the outer table in sequence if the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for limiting the number of data rows in the execution result, 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, wherein the number of data rows contained in each determined batch is the same or different, the number of data rows contained in each batch determined for 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 limiting the number of data rows in the execution result.

[0013] According to a third aspect of one or more embodiments of this specification, a computer program product is provided, comprising a computer program / instruction, which implements the steps of the method described in the first aspect when executed by a processor.

[0014] According to a fourth aspect of one or more embodiments of this specification, an electronic device is provided, including: processor; a memory for storing processor-executable instructions; The processor implements the method as described in the first aspect by running the executable instructions.

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

[0016] The technical solutions provided by the embodiments of this specification may have the following beneficial effects: The SQL statement execution method provided in the embodiment of the present 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 limiting 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 limiting the number of data rows in the execution result, then at least one batch is determined in turn 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, wherein the number of data rows contained in each determined batch is the same or different, and the number of data rows contained in each batch in the batches determined for the previous m times is less than n, m is an integer greater than 0, and k is the number indicated by the operator for limiting the number of data rows in the execution result. In other words, if the SQL statement to be processed does not contain an operator for limiting the number of data rows in the execution result, the nested loop join operator is executed in a conventional batching manner. If the execution plan of the SQL statement to be processed contains an operator for limiting 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 limiting the number of data rows in the execution result and then the execution is stopped. This can avoid invalid redundant calculations caused by executing the nested loop join operator on all data rows of the outer table, thereby improving the execution efficiency of the SQL statement. In this case, the number of data rows in the first m batches is also controlled to be smaller than the number of data rows in the batch of the conventional batching manner, so that the nested loop join operator can be executed on fewer data rows in the outer table as much as possible, that is, the number of data rows in the execution result reaches the number indicated by the operator for limiting the number of data rows in the execution result, thereby further improving the execution efficiency of the SQL statement. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0018] Figure 2 The figure is a flowchart of a method for executing a SQL statement provided by an exemplary embodiment.

[0019] Figure 3It is a schematic diagram of outer surface batching provided by an exemplary embodiment.

[0020] Figure 4 It is a structural schematic diagram of a device provided by an exemplary embodiment.

[0021] Figure 5 It is a block diagram of a SQL statement execution device provided by an exemplary embodiment. DETAILED DESCRIPTION

[0022] 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 one or more embodiments of this specification. Instead, 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.

[0023] It should be noted that: in other embodiments, the steps of the corresponding method are not necessarily performed in the order shown and described in this specification. In some other embodiments, the steps included in the method may be more or less 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 be combined into a single step for description in other embodiments.

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

[0025] For example, the method can be applied to Figure 1 In the distributed database shown as an example.

[0026] Please follow the attached Figure 2 , which exemplarily shows the process of the method, including step S201 and step S202.

[0027] 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 limiting the number of data rows in the execution result, 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 inner table in turn, where n is an integer greater than 1.

[0028] The execution plan of a SQL statement refers to the execution plan selected by the optimizer during the plan generation process.

[0029] Among them, the nested loop link operator is used to perform a nested loop connection on two data tables. If it indicates the outer table and the inner table in the two data tables, its indication 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 of the two can be randomly confirmed, or the data table with the smaller number of data rows can be the outer table and the other data table can be the inner table.

[0030] Wherein, n is the number of data rows in a batch of a conventional batch execution mode preset by the database, for example, it can be 10, 100, etc.

[0031] The operator used to limit the number of data rows in the execution result may be a LIMIT operator or the like.

[0032] For example, in this step, the first batch of rows from the outer table to the nth row may be taken as the first batch and the rows are joined with the inner table in a nested loop, and then the rows from the n+1th row to the 2nth row may be taken as the second batch and the rows are joined with the inner table in a nested loop, and so on, until all the rows in the outer table are joined in a nested loop. It should be understood that the number of rows in the last batch of the outer table may be equal to n or less than n.

[0033] Preferably, in order to speed up the nested loop join, a suitable index can 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 records of the inner table, but the index can be used to quickly locate the potential matching rows. This greatly reduces the amount of data in the inner table that needs to be accessed, and can significantly improve the performance of the nested loop join.

[0034] In step S202, if the execution plan of the SQL statement to be processed contains a nested loop join operator and an operator for limiting the number of data rows in the execution result, at least one batch is determined in sequence in the outer table, and the nested loop join operator is executed on the determined batch and the inner table each time a batch is determined, until the number of data rows in the execution result of the nested loop join operator is equal to k, wherein the number of data rows contained in each determined batch is the same or different, the number of data rows contained in each batch determined for 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 limiting the number of data rows in the execution result.

[0035] The execution plan of a SQL statement refers to the execution plan selected by the optimizer during the plan generation process.

[0036] The operator used to limit the number of data rows in the execution result may be a LIMIT operator or the like.

[0037] This step can be performed only when there is no non-streaming operator in the SQL statement to be processed, that is, when each operator in the SQL statement to be processed is a streaming operator. Specifically: if the execution plan of the SQL statement to be processed contains a nested loop join operator and an operator for limiting the number of data rows in the execution result, and does not contain a non-streaming operator, at least one batch is determined in sequence 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.

[0038] Among them, streaming operators refer to those that can generate output results while reading input data. This type of operator does not need to wait until all input data is ready before starting to output results; instead, it can start processing and generating results when receiving partial data. Typical streaming operators include NLJ operator, Filter operator, LIMIT operator, etc. Typical non-streaming operators include Sort operator, Hash Join operator, Hash GroupBy operator, etc.

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

[0040] Preferably, when determining at least one batch in the outer table in sequence, at least one batch may be determined in sequence in the outer table in a manner of increasing the number of data rows batch by batch.

[0041] For example, at least one batch is sequentially determined in the outer table in such a manner that the first batch contains k data rows and p data rows are added batch by batch, where p is an integer greater than 0. That is, the first batch contains k data rows, the i-th batch contains k+ip data rows, and i is an integer greater than 1.

[0042] If each data row of the outer table has a data row that meets the join condition in the inner table, the first batch is set to k data rows, and k data rows can be directly obtained in the execution result after the nested loop join is performed on the first batch, thereby completing the operator execution of the nested loop join. If not every data row of the outer table has a data row that meets the join condition in the inner table, each subsequent batch is determined by increasing p data rows batch by batch, and the nested loop join can be gradually performed with more data rows to obtain k data rows in the execution result as soon as possible.

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

[0044] If each data row in the outer table has a data row in the inner table that meets the join condition, the first batch is set to k data rows, and k data rows can be directly obtained in the execution result after the nested loop join is performed on the first batch, thereby completing the nested loop join operator execution. If not every data row in the outer table has a data row in the inner table that meets the join condition, each subsequent batch is determined by expanding the number of data rows batch by batch by q times, and the nested loop join can be gradually performed with more data rows to obtain k data rows in the execution result as quickly as possible.

[0045] For another example, at least one batch is determined 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 at least one batch is determined in the outer table in a manner of taking every n data rows as a batch.

[0046] That is, at least one batch is determined in the outer table in a manner of increasing the number of data rows in batches, until the number of data rows in the determined batch is equal to n, and then at least one batch is determined in the outer table in a manner of taking every n data rows as a batch; At least one batch is determined in the outer table in sequence by increasing the number of data rows batch by 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 thereafter at least one batch is determined in the outer table in sequence by taking every n data rows as a batch.

[0047] In other words, the method of increasing the number of data rows batch by batch is based on the number of data rows in the conventional batching method as the upper limit, which cannot be exceeded; for batches that have not reached the upper limit, an attempt is made to obtain k data rows in the execution result in batches with a smaller number of data rows. If the attempt fails, the conventional batching method is continued to be executed.

[0048] For another example, at least one batch is first determined in the outer table in sequence by increasing the number of data rows batch by batch, and after executing the nested loop connection for each 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 executing the nested loop connection for the confirmed batch and the inner table is greater than a preset threshold, the number of data rows in the next batch is determined by increasing the number of data rows batch by batch; if the number of missing data rows in the execution result after executing the nested loop connection for the confirmed batch and the inner table is not greater than the preset threshold, the number of data rows in the next batch is determined by decreasing the number of data rows batch by batch.

[0049] The above examples can be combined with each other to obtain more situations of increasing the number of data rows batch by batch. For example, combining the first example and the third example above, the following method can be obtained: in a manner that the first batch contains k data rows and p data rows are increased batch by batch, at least one batch is determined in sequence in the outer table until the number of data rows in the determined batch reaches n, and at least one batch is determined in sequence in the outer table with every n data rows as a batch, where p is an integer greater than 0. For example, combining the second example and the third example above, the following method can be obtained: in a manner that the first batch contains k data rows and q times the data rows are increased batch by batch, at least one batch is determined in sequence in the outer table, until the number of data rows in the determined batch reaches n, and at least one batch is determined in sequence in the outer table with every n data rows as a batch, where q is an integer greater than 1. For example, by combining the first example and the fourth example mentioned above, the following method can be obtained: in accordance with the method that the first batch contains k data rows and p data rows are added batch by batch, at least one batch is determined in the outer table in sequence, until the number of missing data rows in the execution result after executing the nested loop connection for 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 by reducing p data rows batch by batch. It should be noted that the method of reducing the number of data rows batch by batch still uses k as the lower limit, that is, it will no longer decrease after it decreases to k data rows, where p is an integer greater than 0. For example, combining the second example with the fourth example above, the following method can be obtained: in accordance with the method that the first batch contains k data rows and the data rows are expanded by q times batch by batch, at least one batch is determined in the outer table in sequence, until the number of missing data rows in the execution result after executing the nested loop connection for 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 accordance with the method of reducing the data rows by q times batch by batch. It should be noted that the method of reducing the number of data rows batch by batch still uses k as the lower limit, that is, it will no longer decrease after it decreases to k data rows, where q is an integer greater than 1.

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

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

[0052] SQL statement: create table t1(a int primary key, b int, c int); create table t2(a int primary key, b int, c int); explain select * from t1, t2 where t1.b = t2.a LIMIT 1; The SQL statement execution method provided in the embodiment of the present 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 limiting 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 limiting the number of data rows in the execution result, then at least one batch is determined in turn 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, wherein the number of data rows contained in each determined batch is the same or different, and the number of data rows contained in each batch in the batches determined for the previous m times is less than n, m is an integer greater than 0, and k is the number indicated by the operator for limiting the number of data rows in the execution result. In other words, if the SQL statement to be processed does not contain an operator for limiting the number of data rows in the execution result, the nested loop join operator is executed in a conventional batching manner. If the execution plan of the SQL statement to be processed contains an operator for limiting 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 limiting the number of data rows in the execution result and then the execution is stopped. This can avoid invalid redundant calculations caused by executing the nested loop join operator on all data rows of the outer table, thereby improving the execution efficiency of the SQL statement. In this case, the number of data rows in the first m batches is also controlled to be smaller than the number of data rows in the batch of the conventional batching manner, so that the nested loop join operator can be executed on fewer data rows in the outer table as much as possible, that is, the number of data rows in the execution result reaches the number indicated by the operator for limiting the number of data rows in the execution result, thereby further improving the execution efficiency of the SQL statement.

[0053] Figure 4 is a schematic structural diagram of a device provided by an exemplary embodiment. 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, and may also include hardware required for other tasks. One or more embodiments of this specification may be implemented based on software, such as the processor 402 reading the corresponding computer program from the non-volatile memory 410 into the memory 408 and then running it. Of course, in addition to the software implementation, one or more embodiments of this specification do not exclude other implementations, such as logic devices or a combination of software and hardware, etc., that is, the execution subject of the following processing flow is not limited to each logic unit, but can also be hardware or logic devices.

[0054] Please refer to Figure 5 , the SQL statement execution device can be applied to Figure 4 The SQL statement execution device may include: The first execution module 501 is used for, if the execution plan of the SQL statement to be processed has a nested loop join operator and does not have an operator for limiting the number of data rows in the execution result, taking every n data rows of the outer table as a batch, and sequentially executing the nested loop join operator for each obtained batch and the inner table, wherein n is an integer greater than 1; The second execution module 502 is used to determine at least one batch in the outer table in sequence if the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for limiting the number of data rows in the execution result, 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, wherein the number of data rows contained in each determined batch is the same or different, the number of data rows contained in each batch in 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 limiting the number of data rows in the execution result.

[0055] 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, it is used to: At least one batch is sequentially determined in the outer table in a manner of increasing the number of data rows batch by batch.

[0056] In a possible embodiment of the present disclosure, the second execution module is used to, when determining at least one batch in the outer table in sequence in a manner of increasing the number of data rows batch by batch, to: At least one batch is determined in sequence in the outer table in such a manner that the first batch contains k data rows and p data rows are added batch by batch, where p is an integer greater than 0.

[0057] In a possible embodiment of the present disclosure, the second execution module is used to, when determining at least one batch in the outer table in sequence in a manner of increasing the number of data rows batch by batch, to: At least one batch is determined in sequence in the outer table in such a manner that the first batch contains k data rows and each batch is expanded by q times the data rows, where q is an integer greater than 1.

[0058] In a possible embodiment of the present disclosure, the second execution module is used to, when determining at least one batch in the outer table in sequence in a manner of increasing the number of data rows batch by batch, to: At least one batch is determined 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 at least one batch is determined in the outer table in a manner of taking every n data rows as a batch.

[0059] In a possible embodiment of the present disclosure, 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 in batches, 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, and is used to: At least one batch is sequentially determined in the outer table in a manner of increasing the number of data rows in batches until the number of data rows in the determined batches is equal to n, and then at least one batch is sequentially determined in the outer table in a manner of taking every n data rows as a batch; At least one batch is determined in the outer table in sequence by increasing the number of data rows batch by 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 thereafter at least one batch is determined in the outer table in sequence by taking every n data rows as a batch.

[0060] In a possible embodiment of the present disclosure, the second execution module is used to: If the execution plan of the SQL statement to be processed contains a nested loop join operator and an operator for limiting the number of data rows in the execution result, and does not contain a non-streaming operator, then at least one batch is determined in the outer table in sequence, and after each batch is determined, the nested loop join operator is executed on 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.

[0061] One or more embodiments of the present specification also propose a computer program product, including a computer program / instruction, which implements the steps of the method provided in the first aspect when the computer program / instruction is executed by a processor.

[0062] One or more embodiments of the present specification also propose a computer-readable storage medium having computer instructions stored thereon, which, when executed by a processor, implement the steps of the method described in the first aspect.

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

[0064] In a typical configuration, a computer includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0065] Memory may include non-permanent storage in a 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. Memory is an example of a computer-readable medium.

[0066] Computer-readable media include permanent and non-permanent, removable and non-removable media that can be used to store information by any method or technology. 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 technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, 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 that can be accessed by a computing device. As defined herein, computer-readable media does not include temporary computer-readable media (transitory media), such as modulated data signals and carrier waves.

[0067] It should also be noted that the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, commodity 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, commodity or device. In the absence of more restrictions, the elements defined by the sentence "comprises a ..." do not exclude the existence of other identical elements in the process, method, commodity or device including the elements.

[0068] The above is a description of a specific embodiment of the specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recorded in the claims can be performed in an order different from that in the embodiments and still achieve the desired results. In addition, the processes depicted in the drawings do not necessarily require the specific order or continuous order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

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

[0070] 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 manual 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 relevant countries and regions, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0071] It should be understood that although the terms first, second, third, etc. may be used to describe various information in one or more embodiments of this specification, these information should not be limited to these terms. These terms are only used to distinguish the same type of information 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 may be interpreted as "at the time of" or "when" or "in response to determining".

[0072] The above description is merely a preferred embodiment of one or more embodiments of the present specification and is not intended to limit one or more embodiments of the present specification. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of one or more embodiments of the present specification shall be included in the scope of protection of one or more embodiments of the present specification.

Claims

1. A method for executing a 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 limiting the number of data rows in the execution result, 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 contains a nested loop join operator and an operator for limiting the number of data rows in the execution result, at least one batch is determined in sequence in the outer table, and the nested loop join operator is executed on the determined batch and the inner table each time a batch is determined, until the number of data rows in the execution result of the nested loop join operator is equal to k, wherein the number of data rows contained in each determined batch is the same or different, the number of data rows contained in each batch determined for 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 limiting the number of data rows in the execution result.

2. The SQL statement execution method according to claim 1, wherein the step of sequentially determining at least one batch in the outer table comprises: At least one batch is sequentially determined in the outer table in a manner of increasing the number of data rows batch by batch.

3. The SQL statement execution method according to claim 2, wherein the step of determining at least one batch in the outer table in sequence by increasing the number of data rows in batches comprises: At least one batch is sequentially determined in the outer table in such a manner that the first batch contains k data rows and p data rows are added batch by batch, where p is an integer greater than 0.

4. The SQL statement execution method according to claim 2, wherein the step of determining at least one batch in the outer table in sequence by increasing the number of data rows in batches comprises: At least one batch is determined in sequence in the outer table in such a manner that the first batch contains k data rows and each batch is expanded by q times the data rows, where q is an integer greater than 1.

5. The SQL statement execution method according to claim 2, wherein the step of determining at least one batch in the outer table in sequence by increasing the number of data rows in batches comprises: At least one batch is determined 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 at least one batch is determined in the outer table in a manner of taking every n data rows as a batch.

6. The SQL statement execution method according to claim 2, wherein the step of determining at least one batch in the outer table in a manner of increasing the number of data rows in batches until the number of data rows in the determined batches reaches n and then determining at least one batch in the outer table in a manner of taking every n data rows as a batch comprises: At least one batch is sequentially determined in the outer table in a manner of increasing the number of data rows in batches until the number of data rows in the determined batches is equal to n, and then at least one batch is sequentially determined in the outer table in a manner of taking every n data rows as a batch; At least one batch is determined in the outer table in sequence by increasing the number of data rows batch by 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 thereafter at least one batch is determined in the outer table in sequence by taking every n data rows as a batch.

7. The SQL statement execution method 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 limiting the number of data rows in the execution result, 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, comprising: If the execution plan of the SQL statement to be processed contains a nested loop join operator and an operator for limiting the number of data rows in the execution result, and does not contain a non-streaming operator, then at least one batch is determined in the outer table in sequence, and after each batch is determined, the nested loop join operator is executed on 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. A SQL statement execution device, comprising: A first execution module is 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 limiting 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, wherein n is an integer greater than 1; The second execution module is used to determine at least one batch in the outer table in sequence if the execution plan of the SQL statement to be processed has a nested loop join operator and an operator for limiting the number of data rows in the execution result, 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, wherein the number of data rows contained in each determined batch is the same or different, the number of data rows contained in each batch determined for 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 limiting 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 sequentially determines at least one batch in the outer table, it is used to: At least one batch is sequentially determined in the outer table in a manner of increasing the number of data rows batch by batch.

10. The SQL statement execution device according to claim 9, wherein the second execution module is configured to, when determining at least one batch in the outer table in sequence in a manner of increasing the number of data rows batch by batch, At least one batch is determined in the outer table in sequence in such a way that the first batch contains k data rows and p data rows are added batch by batch, where: p is an integer greater than 0.

11. The SQL statement execution device according to claim 9, wherein the second execution module is configured to, when determining at least one batch in the outer table in sequence in a manner of increasing the number of data rows batch by batch, At least one batch is determined in the outer table in sequence in such a way that the first batch contains k data rows and each batch is expanded by q times the data rows, wherein: q is an integer greater than 1.

12. The SQL statement execution device according to claim 9, wherein the second execution module is configured to, when determining at least one batch in the outer table in sequence in a manner of increasing the number of data rows batch by batch, At least one batch is determined 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 at least one batch is determined in the outer table in a 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 contains a nested loop join operator and an operator for limiting the number of data rows in the execution result, and does not contain a non-streaming operator, then at least one batch is determined in the outer table in sequence, and after each batch is determined, the nested loop join operator is executed on 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 a computer program / instruction, which, when executed by a processor, implements the steps of the method according to any one of claims 1 to 7.

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

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

Images