Data processing method and device

Abstract

The invention discloses a data processing method and device, relates to the field of databases, and can reduce the overhead of processing query statements including connection operation and window functions by a server and improve the data processing efficiency. The method comprises the following steps: obtaining a query statement, wherein the query statement comprises a connection operation for connecting a first list and a second list based on a first connection condition and a window function; an ordered memory index is constructed based on records in the first list, data records of the ordered memory index store values of connection keys corresponding to the records in the first list, matching mark values, results of the window functions and input parameters of the window functions, and search keys of the ordered memory index comprise the connection keys; according to the first connection condition and the value of the search key, the record in the second list is used for detecting the ordered memory index; and if the record in the second list and the data record of the ordered memory index meet the first connection condition, updating a matching mark value corresponding to the data record.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a data processing method and device. Background Art

[0002] When processing query statements, a database management system (DBMS) usually uses a single operator for each operation. This processing method has good versatility and a wide range of applications. However, when processing query statements for connection operations, existing operators consume more server resources, waste server performance, and reduce server operating efficiency. Summary of the invention

[0003] The present application provides a data processing method and device, which can consume less server resources when processing query statements including connection operations.

[0004] In order to achieve the above objectives, the embodiments of the present application provide the following technical solutions:

[0005] In a first aspect, a data processing method is provided, including: obtaining a query statement, the query statement including a join operation of joining a first list and a second list based on a first join condition; constructing an ordered memory index based on records in the first list, the data record of the ordered memory index storing a join key value and a match tag value of a record in the first list, the search key of the ordered memory index including the join key; the match tag value is used to mark whether the data record of the ordered memory index matches the record in the second list; according to the first join condition and the value of the search key, using the records in the second list to detect the ordered memory index; if the record in the second list and the data record of the ordered memory index meet the first join condition, then updating the match tag value stored in the data record.

[0006] For example, the initial value of the matching record value is empty or no, and when the record in the second list and the first record in the ordered memory index meet the first connection condition, the matching record value corresponding to the first record can be updated to yes. For another example, if the initial value of the matching record value is yes, the initial value of the matching record value can also be updated to yes. In the case where the matching record values ​​before and after the update shown above do not change, it can also be referred to as not updating the matching record value. For another example, when the record in the second list and the first record in the ordered memory index meet the first connection condition, if the matching record value already stored in the first record is 0, the matching record value corresponding to the first record can be increased by 1, that is, updated to 1, and if the matching record value already stored in the first record is 1, the matching record value corresponding to the first record can be increased by 1, that is, updated to 2, and so on.

[0007] Through this solution, for the above query statement, the server builds an ordered memory index based on the records in the first list, and detects the ordered memory index through the records in the second list. When the record in the second list and the first record in the ordered memory index meet the first connection condition, the matching record value corresponding to the first record can be updated. By updating the matching number value, the connection result of the records in the first list and the records in the second list is marked, so that in the subsequent process, the server can read the result of the connection operation between the first list and the second list according to the matching tag value. This solution detects the ordered memory index through the search key, so that in the process of connecting the first list and the second list, with the help of the ordered characteristics of the ordered memory index, the record matching a certain record in the second list in the ordered memory index built based on the first list is obtained without traversing each record in the first list, that is, there is no need to compare a certain record in the second list with each record in the first list, thereby improving the efficiency of the server in executing query statements.

[0008] In combination with the first aspect, in a possible design, the data records of the ordered memory index store the connection key value and the matching tag value corresponding to a record in the first list, including: reserving memory for the connection key value and / or the matching tag value.

[0009] In this scheme, in the process of building an ordered memory index based on the records in the first list, the server can reserve memory for the connection key value and / or matching tag value, store placeholders in the reserved memory, or do not store content in the reserved memory, and store the actual connection key value and / or matching tag value in the reserved memory in a subsequent process.

[0010] In combination with the first aspect, in one possible design, the data record of the ordered memory index stores the value of the connection key and the matching tag value corresponding to a record in the first list, including: storing the initial value of the connection key value and / or the initial value of the matching tag value.

[0011] In this solution, the initial value of the connection key value and / or the initial value of the matching tag value may be a preset value, for example, the initial value of the matching tag value is set to no, or the initial value of the matching tag value is set to 0. For another example, the initial value of the connection key value is "00". The initial value of the connection key value and / or the initial value of the matching tag value may be selected based on the value in the first list, for example, the initial value of the connection key value may be the value of the connection key of the record in the first list of records for constructing the ordered memory index.

[0012] In combination with the first aspect, in a possible design, the query statement includes an output value, and the data record further stores: the output value in the query statement.

[0013] In this solution, by storing the output value in the query statement in the data record, when the query result of the query statement needs to be output later, the data record can be directly read and part of the content in the data record can be output. Exemplarily, the query result of the query statement may include the result of the connection operation of the first list and the second list. If the connection is successful, the value of the connection key stored in the data record is output, and if the connection is unsuccessful, it is not output. For example, the columns of the first list include name and serial number, the columns of the second list include name and age, the connection key is name, and the connection condition is that the names in the first list and the second list are the same. Then the data record can store the value of the connection key (that is, the name corresponding to the record in the first list), and if a record in the data record is successfully connected to the record in the second list, the name corresponding to the connection key of the record is output. In another exemplary embodiment, the query statement includes a window function, and the output value of the query statement includes the result of the window function and the input parameter of the window function. Then the result of the window function and the input parameter of the window function are stored in the data record.

[0014] In combination with the first aspect, in a possible design, the query statement includes a window function, and the data record of the ordered memory index also stores the result of the window function and / or the input parameter of the window function corresponding to a record in the first list.

[0015] In this solution, by storing the results of the window function and / or the input parameters of the window function in the data record, when the results of the window function and / or the input parameters of the window function need to be output later, it can be achieved directly by reading the data record.

[0016] In addition, when operating on a query statement including a join operation and a window function, the processor can load the records of the second list from the memory into the cache, perform a join operation with the records in the first list, and perform the window function operation, and save the calculation results of the window function in the records of the ordered memory index. Thus, the join operation and the window function can be implemented by loading the records in the second list once, which has good spatiotemporal locality.

[0017] In combination with the first aspect, in a possible design, it also includes: if the records in the second list and the data records of the ordered memory index meet the first connection condition, the input parameters of the window function are obtained; the input parameters of the window function are the parameters corresponding to the data records and / or the parameters corresponding to the records in the second list; according to the input parameters of the window function and the window function, the results of the window function are updated.

[0018] In this solution, the result of the window function can be calculated based on the input parameters of the window function and the window function. By updating the result of the window function stored in the data record with the calculated window function result, the latest window function result corresponding to the record can be directly obtained in the field of the window function result of the subsequent data record.

[0019] In combination with the first aspect, in a possible design, when the input parameters of the window function include parameters corresponding to the data record, when constructing the ordered memory index, the server may store the input parameters corresponding to the records in the first list into the input parameter field of the window function corresponding to the data record. Thus, when the server calculates the window function, the input parameters of the window function may be read from the input parameter field of the window function corresponding to the record, and the result of the window function may be obtained based on the input parameters of the window function and the window function calculation, and the result of the window function corresponding to the data record may be updated.

[0020] In combination with the first aspect, in one possible design, when the input parameters of the window function include parameters corresponding to the records in the second list, when the server detects the ordered memory index through the records in the second list, the server can obtain the input parameters of the window function of the records in the second list, and read the data records in the ordered memory index that match the records in the second list, calculate the result of the window function according to the window function and the input parameters of the window function of the records in the second list, and update the result of the window function corresponding to the data record.

[0021] In combination with the first aspect, in a possible design, when the input parameters of the window function include parameters corresponding to the data record and parameters corresponding to the record in the second list, when constructing the ordered memory index, the server may store the input parameters corresponding to the record in the first list into the input parameter field of the window function corresponding to the data record. When the server detects the ordered memory index through the records in the second list, the server may obtain the input parameters of the window function of the record in the second list, and read the data record in the ordered memory index that matches the record in the second list, calculate the result of the window function according to the window function, the input parameters of the window function of the data record, and the input parameters of the window function of the record in the second list, and update the result of the window function corresponding to the data record.

[0022] In combination with the first aspect, in a possible design, before building an ordered memory index based on the records in the first list, the following steps are included:

[0023] Determine whether the query statement satisfies the first condition. If the query statement satisfies the first condition, build an ordered memory index based on the records in the first list; the first condition includes at least one of the following conditions: when the window function includes a sort clause and a partition clause, the sort key specified by the sort clause and the partition key specified by the partition clause are both from the first list; when the window function includes a sort clause, the join key of the first list is a prefix of the sort key specified by the sort clause; when the window function includes a partition clause, the join key of the first list is a prefix of the partition key specified by the partition clause; when the window function includes a sort clause and a partition clause, the join key of the first list is composed of one of the following: the partition key specified by the partition clause and the prefix of the sort key specified by the sort clause, the prefix of the partition key specified by the partition clause, or the prefix of the sort key specified by the sort clause.

[0024] In combination with the first aspect, in one possible design, when the window function includes a sort clause, the search key of the ordered memory index is the sort key specified by the sort clause; or, when the window function includes a partition clause, the search key of the ordered memory index is the partition key specified by the partition clause; or, when the window function includes a sort clause and a partition clause, the search key of the ordered memory index is composed of one of the following: the partition key specified by the partition clause and the sort key specified by the sort clause, the partition key specified by the partition clause, or the sort key specified by the sort clause.

[0025] In combination with the first aspect, in a possible design, when the search key of the ordered memory index is composed of a partition key specified by a partition clause and a sort key specified by a sort clause, the partition key is set in front and the sort key is set in the back.

[0026] In combination with the first aspect, in a possible design, it also includes: outputting the result of the connection operation corresponding to the data record according to the connection type and matching tag value of the connection operation.

[0027] In combination with the first aspect, in a possible design, the above-mentioned matching tag value is the matching tag value of each data record in one or more data records, and according to the connection type and the matching tag value of the connection operation, outputting the result of the connection operation corresponding to the data record includes: according to the matching tag value of each data record and the connection type of the connection operation, outputting the result of the connection operation corresponding to the data record.

[0028] In combination with the first aspect, in one possible design, the matching tag value is used to mark a successful match or an unsuccessful match; if the connection type of the join operation is an inner join, the result of the join operation is the output value corresponding to the successfully matched record; if the connection type of the join operation is a left outer join, the result of the join operation is the output value corresponding to all data records of the ordered memory index; if the connection type of the join operation is a right outer join, the result of the join operation is the output value corresponding to the successfully matched record with the matching tag value and all records in the second list; if the connection type of the join operation is a full join, the result of the join operation is the output value corresponding to all data records of the ordered memory index and all records in the second list.

[0029] In combination with the first aspect, in a possible design, the match tag value is the number of matches between the data record and the records in the second list; the number of results of the connection operation stored in each data record output is equal to the number of matches of each data record. Thus, the server can output the result of the connection operation corresponding to the data record according to the connection type and the number of matches of the connection operation.

[0030] In combination with the first aspect, in a possible design, if the connection type is an inner join, the result of the join operation is the output value corresponding to the record with a matching count value greater than 0; if the connection type is a left outer join, the result of the join operation is the output value corresponding to all data records of the ordered memory index; if the connection type is a right outer join, the result of the join operation is the output value corresponding to the record with a matching count value greater than 0 and all records in the second list; if the connection type is a full join, the result of the join operation is the output value corresponding to all data records of the ordered memory index and all records in the second list.

[0031] In combination with the first aspect, in one possible design, detecting the ordered memory index includes: detecting the ordered memory index when the value of the connection key corresponding to the record in the second list satisfies a range condition, and the range condition is used to indicate the range of the connection key in the search key of the ordered memory index.

[0032] In this solution, the server can first determine whether the connection key of the record meets the range condition before detecting the ordered memory index of the record in the second list. If the connection key of the record meets the range condition, the ordered memory index will be detected, thereby reducing the detection of a part of the records in the second list that do not meet the connection conditions, thereby reducing energy waste.

[0033] In combination with the first aspect, in one possible design, the records in the second list satisfy the range condition including: if the connection key value of the record in the second list is between the maximum value and the minimum value of the connection key value in the search key of the ordered memory index, then the connection key value of the record in the second list satisfies the range condition.

[0034] In conjunction with the first aspect, in a possible design, the query statement includes a sorting clause. In this solution, for the query statement including the sorting clause, when the server outputs the query results, the ordered query results can be output according to the order of the data records of the ordered memory index, and there is no need to sort the results after the first list and the second list are connected again, thereby saving server resources.

[0035] In combination with the first aspect, in a possible design, the sorting clause in the query statement includes the number of output records; the result of the connection operation corresponding to the output data record includes: outputting the result of the connection operation corresponding to M data records, where M is the number of output records and M is a positive integer.

[0036] In a second aspect, a device is provided, comprising: a processor, the processor being coupled to a memory, the processor being configured to execute a computer program stored in the memory, so that the device executes the method described in the first aspect and any one of the implementations thereof.

[0037] In a third aspect, an electronic device is provided, comprising a memory and a processor; the memory and the processing unit are coupled; the memory is used to store computer program code, the computer program code comprises computer instructions, and when the processing unit executes the computer instructions, the electronic device executes the method as described in the first aspect and any one of its implementations.

[0038] In a fourth aspect, a chip system is provided, comprising: a processor and an interface circuit; the processor and the interface circuit are interconnected via a line; the interface circuit is used to read instructions stored in a memory, and when the instructions are executed by the processor, the chip system executes the method described in the first aspect and any one of its implementations.

[0039] In a fifth aspect, a chip system is provided, the chip system including a processor for supporting an electronic device to implement the functions involved in the first aspect. In a possible design, the chip system also includes a memory for storing program instructions and data necessary for the electronic device. The chip system may be composed of a chip, or may include a chip and other discrete devices.

[0040] In a sixth aspect, an electronic device is provided, which has the function of implementing any one of the methods in the first aspect. The function can be implemented by hardware, or by hardware executing corresponding software. The hardware or software includes one or more modules corresponding to the above functions.

[0041] In a seventh aspect, a computer-readable storage medium is provided, comprising computer instructions. When the computer instructions are executed on an electronic device, the electronic device executes the method as described in the first aspect and any one of its implementations.

[0042] In an eighth aspect, a computer program product is provided. When the computer program product is run on a computer, the computer is enabled to execute the method as described in the above aspects and any possible implementation thereof.

[0043] According to a ninth aspect, a database system is provided, wherein the database system comprises the apparatus according to the second aspect.

[0044] It can be understood that the beneficial effects that can be achieved by the methods, devices, chip systems, electronic devices, computer-readable storage media, and computer program products provided in the second to ninth aspects above can refer to the beneficial effects of the first aspect provided above and any possible implementation method, and will not be repeated here. BRIEF DESCRIPTION OF THE DRAWINGS

[0045] Figure 1 A schematic diagram of an execution plan of a query statement 1 in the related art;

[0046] Figure 2 A flowchart of an execution plan of query statement 1 in the related art;

[0047] Figure 3 A schematic diagram of an execution plan of query statement 2 in the related art;

[0048] Figure 4 It is a flowchart of the execution plan of query statement 2 in the related technology;

[0049] Figure 5 A schematic diagram of a database system provided in an embodiment of the present application;

[0050] Figure 6 A schematic diagram of the hardware structure of an electronic device provided in an embodiment of the present application;

[0051] Figure 7 A schematic diagram of the architecture of a DBMS provided in an embodiment of the present application;

[0052] Figure 8 A schematic diagram of a process for parsing SQL statements provided in an embodiment of the present application;

[0053] Fig. 9 Another schematic diagram of the process of parsing SQL statements provided in an embodiment of the present application;

[0054] Fig.10 A flowchart of a data processing method provided in an embodiment of the present application;

[0055] Fig.11 A flowchart of processing query statements provided for related technologies;

[0056] Fig.12 Another flow chart of the data processing method provided in the embodiment of the present application;

[0057] Fig.13 A schematic diagram of a process for constructing an ordered memory index provided in an embodiment of the present application;

[0058] Fig.14 A schematic diagram of a process for detecting ordered memory indexes and calculating window functions provided in an embodiment of the present application;

[0059] Fig.15 A schematic diagram of a process for scanning an ordered memory index and outputting a calculation result of a fusion operator provided in an embodiment of the present application;

[0060] Fig.16 A flowchart of an execution plan for a query statement provided in an embodiment of the present application;

[0061] Fig.17 Another flow chart of the data processing method provided in the embodiment of the present application;

[0062] Fig.18 A schematic diagram of a B+ tree provided in an embodiment of the present application;

[0063] Fig.19 Another flow chart of the data processing method provided in the embodiment of the present application;

[0064] Fig. 20 Another flow chart of the data processing method provided in the embodiment of the present application;

[0065] Fig.21 Another flow chart of the data processing method provided in the embodiment of the present application;

[0066] Fig. 22 A schematic diagram of a device provided in an embodiment of the present application;

[0067] Fig.23 A schematic diagram of the structure of a chip system provided in an embodiment of the present application. DETAILED DESCRIPTION

[0068] First, some of the terms and related technologies involved in this application are explained to facilitate understanding by those skilled in the art.

[0069] Database: used to organize and store data. A database consists of one or more lists (referred to as tables), each of which represents a type of entity, such as a product price list or a product inventory list. Each table consists of rows and columns, each row represents a record in the table, also called a record, data row, or data item, and each column represents a field in the table.

[0070] DBMS: Used to manage and organize the creation, access, maintenance and control of databases, and can implement data addition, deletion, modification and query operations.

[0071] Query operation (referred to as query): refers to the operation of retrieving data from the database. It can query data in a single table or in multiple tables. Through query operation, users can easily obtain the required data to perform data analysis, report generation, decision support, etc. Query operation can include join operation and window function operation.

[0072] Join operation (referred to as join): Join operation refers to matching data items in two or more tables according to join conditions in order to connect related data rows in two tables together. Join conditions are used to specify the rules for the association between multiple tables. For example, based on the comparison of certain columns in multiple tables, if the comparison results meet the join conditions, the rows containing the data in the compared columns in multiple tables can be connected. Join conditions are usually divided into equijoin and non-equijoin. Equijoin only uses the equal sign, and non-equijoin usually uses comparison operators, such as greater than, greater than or equal to, less than, less than or equal to, and not equal to. The above-mentioned equal sign and comparison operators can be collectively referred to as operators.

[0073] For example, taking the connection of Table 1 (t1) and Table 2 (t2) as an example, the connection condition is that the x column of Table 1 is equal to the a column of Table 2, that is, t1.x = t2.a. Then when the value of the x column of a record in Table 1 is equal to the value of the a column of a record in Table 2, the record in Table 1 is connected with the record in Table 2 to obtain the result after the connection. Referring to Table 3 (t3), taking the left outer connection of Table 1 and Table 2 as an example, Table 3 is an example of the connection result obtained by connecting Table 1 and Table 2. Through the connection operation, a table after multiple tables are connected can be obtained, and the query operation can query the information of multiple tables by querying the connected table.

[0074] Table 1

[0075] x y z 1 2 3 3 8 9 2 6 7 1 4 5

[0076] Table 2

[0077] a b c 1 3 2 1 5 4 3 7 6 4 9 8

[0078] Table 3

[0079] x(a) y z b c 1 2 3 3 2 1 2 3 5 4 3 8 9 7 6 2 6 7 null null 1 4 5 3 2 1 4 5 5 4

[0080] Join key: When joining two tables, the column used for comparison in the join condition is the join key. The join key can be a single column or a combination of multiple columns. For example, in the above example, the join condition t1.x=t2.a, the join key of the t1 table is t1.x, and the join key of the t2 table is t2.a. For another example, in the join condition t1.x>t2.a AND t1.y>t2.b, the join key of the t1 table is (t1.x, t1.y), and the join key of the t2 table is (t2.a, t2.b).

[0081] It is understandable that the DBMS can perform the above-mentioned operations (such as querying data) according to the read statements (or commands). For example, the above-mentioned statements can be SQL statements. The JOIN keyword in the SQL statement can implement the connection operation, such as using keywords such as INNER JOIN, LEFT JOIN, and RIGHT JOIN. In the query statement implemented by the SQL statement: SELECT*FROM t1 LEFT JOIN t2 ON t1.x=t2.a, the connection condition is that the column x in Table 1 is equal to the column a in Table 2. Through this query statement, the above-mentioned connected Table 3 can be obtained.

[0082] Window function: A window function (also called an analytical function) is a function used to analyze data in a relational database. It can sort, aggregate, and calculate (such as sum) a group of data (one or more rows) and set a result for each row in the group. Window functions are usually used in SELECT statements. Window functions can include clauses such as OVER, PARTITIONBY, and ORDER BY.

[0083] The partition key specified by the PARTITION BY clause can group the table, and rows with the same partition key value are in the same group. For example, referring to Table 1, assume that the partition key is the x field (t1.x) of Table 1, expressed as PARTITION BY t1.x. Then, according to the value of the x field of each row of data, the data in Table 1 can be divided into 3 groups. For example, each row of data is represented by (x, y, z), and the x values ​​in the first row of data (1,2,3) and the fourth row of data (1,4,5) are the same. The two rows of data constitute a group. For the convenience of description, this group is called Group A. Similarly, the third row of data (2,6,7) constitutes a group, which is called Group B. The second row of data (3,8,9) constitutes a group, which is called Group C.

[0084] Take the sum operation of the y column of a set of data as an example. Each row of data is represented by (x, y, z, sum), where the sum column represents the sum result. Group A data can be represented as (1,2,3,6) and (1,4,5,6). Group B and Group C each have only one row of data, and the sum value is the y value of the row, that is, Group B data can be represented as (2,6,7,6) and Group C data can be represented as (3,8,9,8).

[0085] The sort key specified by the ORDER BY clause in the window function can be used as the sort index. For example, referring to Table 1, assuming that the sort key is the x field (t1.x) of Table 1, it is expressed as ORDER BY t1.x. Then, according to the value of the x field of each row of data, the 4 rows of data in Table 1 can be sorted. The t1.x of the 4th row of data in Table 1 is 1, 3, 2, 1, respectively. After sorting from small to large according to the sort key, the data shown in Table 4 is obtained.

[0086] Table 4

[0087] x y z 1 2 3 1 4 5 2 6 7 3 8 9

[0088] Ordered memory index: Memory index means that all index data is stored in memory, and ordered index means that the index is sorted according to the key value of the search key. Ordered index can handle point query and range query. The query condition of point query is equality condition, and the query condition of range query is inequality condition. The implementation of ordered memory index can include data structures such as B-tree (such as B+ tree), balanced binary tree (such as red-black tree, AVL tree) and skip list. The non-leaf nodes of the above tree structure usually store key information for navigation and search, and do not contain actual data. Leaf nodes are used to store actual data.

[0089] Similar to the connection key, the search key can be a single column or a combination of multiple columns. For example, the search key is the x column (t1.x) in Table 1. After sorting from small to large according to the search key, the obtained data is shown in Table 4.

[0090] Execution plan: It is the specific steps and execution order of DBMS execution statement. The basic operation unit in the execution plan is called physical operator, which represents a specific operation, such as table scan, hash join, etc. The operators in the execution plan form a tree structure according to the execution order. The root node of the tree is the outermost operator, and the leaf node is the innermost operator.

[0091] Operator: an operator used to process data. Operators are divided into logical operators and physical operators. Logical operators describe the semantics of operations but do not involve specific implementations. Physical operators describe the specific execution methods. For example, the join operation is a logical operator, and the corresponding hash join, nested loop join, and sort-merge join are physical operators. DBMS converts logical operators into physical operators and generates an efficient execution plan. The operators in the execution plan are physical operators.

[0092] When a query statement involves a join operation and a window function, the industry generally uses a single physical operator to implement these two operations, that is, each physical operator implements one of the operations. In addition, the join operation is processed first, and then the window function is processed. For example, a hash join is used to process the join operation first, and then a window sort is used to process the window function. The following is an example of query statement processing the above Table 1 and Table 2.

[0093] For example, refer to Figure 1 For a query statement containing a join and a window function (such as query statement 1), if the join condition is an equality condition (such as t1.x=t2.a), the relevant technology usually uses a single operator hash join and a single operator window sort to perform the join operation and process the window function to obtain the query result.

[0094]

[0095] The execution plan of query statement 1 is as follows Figure 2 As shown, the specific steps include S201 to S204.

[0096] S201: The server uses a table scan operator to scan a table on one side (such as the left side of the equal sign) and builds a hash table based on the connection key.

[0097] S202: The server uses a table scan operator to scan the table on the other side (eg, the right side of the equal sign), matches the hash table according to the connection key, and outputs the matching result.

[0098] S203: The server sorts the results of the hash connection.

[0099] S204: The server calculates the window function in groups according to the sorting results and outputs the final query result.

[0100] For example, refer to Figure 3For a query statement containing a join and a window function (such as query statement 2), if the join relationship is a non-equivalent condition (for example, t1.x>t2.a), the relevant technology usually uses single operator sorting merge join and single operator window sorting to process the join operation and window function respectively to obtain the query result.

[0101]

[0102] The execution plan of query statement 2 is as follows Figure 4 As shown, the specific steps include S401 to S405.

[0103] S401: The server uses a table scan operator to scan the table on one side (eg, the left side of the greater than sign) and sorts the table according to the connection key.

[0104] S402: The server uses a table scan operator to scan the table on the other side (eg, the right side of the greater than sign) and sorts the table according to the connection key.

[0105] S403: The server matches the sorted tables according to the connection conditions and outputs the matching results.

[0106] S404: The server sorts the connection results again.

[0107] S405: The server calculates the window function in groups according to the sorting results and outputs the query results.

[0108] The above example contains query statements for join operations and window functions. The join operations (such as building a hash table and joining hash tables) and window functions (such as sorting) are implemented by a single operator. Building a hash table and sorting are both computationally intensive operations, which are redundant and consume a large amount of central processing unit (CPU) and memory resources, wasting server performance. When the query statement meets specific characteristics, the above single operator method cannot use the specific characteristics to further improve the execution efficiency of the execution plan. For example, in the memory tiering scenario, the CPU cache is relatively small. When the CPU processes data, it needs to load the data from the memory into the cache and then calculate the data in the cache. To use a single operator to join Table 1 and Table 2 and calculate the window function, the following steps need to be performed. First, each record of Table 1 needs to be loaded into the cache for sorting during the sorting process, and then each record of Table 2 needs to be loaded into the cache for sorting. Then, in the process of calculating the window function, the records in Table 1 and the records in Table 2 are loaded into the cache for calculating the window function. That is, this method requires loading the records of Table 1 and Table 2 from the memory into the cache multiple times, which has poor spatiotemporal locality, wastes processor performance, and consumes a lot of energy. Based on this, an embodiment of the present application provides a data processing method, constructs an ordered memory index for the table on one side of the operator, detects the ordered memory index for the table on the other side of the operator, and uses the data records in the ordered memory index to store the search key value, the window function value, the input parameter of the window function, and the number of matching times. Through the method provided by the embodiment of the present application, when the server processes operations including connection operations, the overhead of data processing resources can be reduced, the data processing efficiency can be improved, and the performance of the database system can be improved. In addition, when the server processes query statements including window functions and connection operations through the method provided by the embodiment of the present application, it is no longer necessary to use two operators to process window functions and connection operations respectively. The server reads the records in the list of connection operations in the memory once, and can realize the connection operation and window function calculation for the record, reducing the number of times the list is added to the cache, thereby reducing the energy consumption of the processor, saving server resources, and having good spatiotemporal locality.

[0109] The present application embodiment provides a database system, referring to Figure 5 The system includes: an electronic device 100 and a client 110. The electronic device 100 may include different database platforms, such as Hive, MySQL, Oracle or other types of database platforms. The client 110 is connected to the electronic device 100 via a network. The client 110 sends a query statement to the electronic device 100, and the electronic device 100 parses the query statement and obtains the query result, and feeds the query result back to the client 110.

[0110] In some embodiments, the electronic device 100 is provided with client software, and the electronic device can obtain the query instruction input by the user. The data processing method provided in the embodiment of the present application can be applied in the electronic device 100. Exemplarily, the electronic device can be, for example, a database server, a mobile phone, a tablet computer, a personal computer (PC), a netbook, a wearable device, a vehicle-mounted device, etc., and the present application does not impose any special restrictions on the specific form of the electronic device.

[0111] Taking a mobile phone as an example, Figure 6 The hardware structure diagram of the electronic device 100 is shown in FIG. Figure 6 The structure of the electronic device 100 is shown.

[0112] The electronic device 100 includes at least one processor 201 , a memory 202 , and at least one transceiver 203 .

[0113] The processor 201 may be a general-purpose CPU, a microprocessor, an application-specific integrated circuit (ASIC), or one or more integrated circuits for controlling the execution of the program of the present application.

[0114] Transceiver 203, used to communicate with other devices. In the embodiment of the present application, the transceiver can be a module, circuit, bus, interface or other device capable of realizing communication function, used to communicate with other devices. Optionally, the transceiver can be an independently arranged transmitter, which can be used to send information to other devices, and the transceiver can also be an independently arranged receiver, which is used to receive information from other devices. The transceiver can also be a component that integrates the functions of sending and receiving information, and the embodiment of the present application does not limit the specific implementation of the transceiver.

[0115] In some embodiments, the transceiver may obtain SQL statements from other devices.

[0116] The memory 202 may be a read-only memory (ROM) or other types of static storage devices that can store static information and instructions, a random access memory (RAM) or other types of dynamic storage devices that can store information and instructions, or an electrically erasable programmable read-only memory (EEPROM), a compact disc read-only memory (CD-ROM) or other optical disc storage, optical disc storage (including compressed optical disc, laser disc, optical disc, digital versatile disc, Blu-ray disc, etc.), a magnetic disk storage medium or other magnetic storage device, or any other medium that can be used to carry or store the desired program code in the form of instructions or data structures and can be accessed by a computer, but is not limited thereto. The memory may exist independently and be connected to the processor via a communication line. The memory may also be integrated with the processor.

[0117] The memory 202 is used to store computer-executable instructions for implementing the solution of the present application, and the execution is controlled by the processor 201. The processor 201 is used to execute the computer-executable instructions stored in the memory 202, thereby implementing the various methods provided in the following embodiments of the present application.

[0118] Optionally, the computer-executable instructions in the embodiments of the present application may also be referred to as application code, instructions, computer program or other names, which are not specifically limited in the embodiments of the present application.

[0119] In a specific implementation, as an embodiment, the processor 201 may include one or more CPUs, such as CPU0 and CPU1.

[0120] In a specific implementation, as an embodiment, the electronic device 100 may include multiple processors. Each of these processors may be a single-core (single-CPU) processor or a multi-core (multi-CPU) processor. The processor here may refer to one or more devices, circuits, and / or processing cores for processing data (e.g., computer program instructions).

[0121] The data processing method provided in the embodiment of the present application can be applied in DBMS. Figure 7 , Figure 7 The architecture of a DBMS provided by an embodiment of the present application is shown. Taking the DBMS executing SQL statements as an example, the DBMS includes software modules such as a protocol adaptation module, an instance management module, an SQL engine, a storage engine, and a public service module. The functions of each module are as follows:

[0122] Protocol adapter module: used to communicate with the client software. The protocol adapter module and the instance management module transmit commands and result sets through application messages.

[0123] Instance management module: An instance is a mechanism for accessing a database, consisting of a memory structure and some background processes. Information is passed between the instance management module and the SQL engine through sessions.

[0124] SQL engine: responsible for parsing SQL statements and performing corresponding operations, such as query, insert, update, delete, etc. For details, refer to Figure 8 ,The SQL engine includes a lexical parsing module, a syntax parsing module, a semantic analysis module, a query ,optimization module and an execution engine module.

[0125] The lexical analysis module is used to parse SQL statements and parse SQL statements into words.

[0126] The syntax parsing module is used to form a syntax tree from the parsed words. The logical operation of the SQL statement can be obtained based on the parsed syntax tree.

[0127] The semantic analysis module is used to analyze and verify the syntax tree to obtain a verified syntax tree. The verified syntax tree shows the operations included in the SQL statement (such as connection operations and window functions).

[0128] The query optimization module is used to parse (such as lexical analysis, syntax analysis, etc.), analyze (such as semantic analysis, etc.), optimize and generate an execution plan for SQL query statements. For example, for a given SQL query statement, the query optimization module performs query rewriting (also known as query conversion) on the SQL statement according to predefined rules, and generates one or more query statements equivalent to the initial SQL statement. The query optimization module evaluates the cost of these equivalent query statements, and generates the optimal execution plan based on the equivalent query statement with the minimum cost, so as to meet the user's query needs with the minimum time and resource cost.

[0129] The query optimization module is also used to determine whether to merge the join operation and the window function into one operator, such as a window sort join fusion operator (referred to as the fusion operator). The query optimization module is also used to generate an execution plan tree, and pass the executed operator to the execution engine module through the execution plan tree. Among them, the operator in the execution plan tree can be a physical operator (such as a hash join operator, a fusion operator).

[0130] In some embodiments, reference Fig. 9 ,The query optimization module includes a fusion condition judging module and a fusion operator generating module.

[0131] The fusion condition judgment module is used to judge whether the SQL statement meets the fusion condition. If the SQL statement meets the fusion condition, the next step is executed.

[0132] The fusion operator generation module is used to generate fusion operators based on SQL statements.

[0133] The execution engine module is used to execute the operators generated by the query optimization module, for example, the window sorting join fusion operator. Based on this fusion operator, the join operation and the calculation of the window function can be completed simultaneously.

[0134] In some embodiments, reference Fig. 9 , the execution engine module is used to execute the fusion operator. Exemplarily, the execution engine module is used to build an ordered memory index, detect the ordered memory index, calculate the window function, scan the ordered memory index, and output the fusion operator result.

[0135] In an embodiment of the present application, information is transmitted between the SQL engine and the storage engine through sessions and cursors.

[0136] Storage engine: responsible for data storage management, including data access, transaction management, persistence and concurrency control management.

[0137] Public service module: provides a public interface for other modules, such as lock management, etc. The public module is connected to the above modules and serves the above modules.

[0138] The data processing method provided in the embodiment of the present application can be implemented inside the SQL engine of the DBMS.

[0139] It should be noted that the above DBMS architecture is only an example. In other examples, the DBMS architecture may include more or fewer modules. Or, the functions performed by each module may be changed. For example, the SQL engine does not include a lexical parsing module, a grammatical parsing module, and a semantic analysis module. The SQL engine directly obtains the verified syntax tree, and generates an execution plan tree based on the syntax tree to obtain the result after the execution plan tree is executed.

[0140] For another example, the architecture of the DBMS does not include the SQL engine, but directly includes various modules in the SQL engine (such as a query optimization module and an execution engine module), and each module in the SQL engine respectively performs the functions of each module in the above examples.

[0141] The above introduces the architecture of DBMS by taking the DBMS executing SQL statements as an example. It should be noted that each module may have a different name. For example, the SQL engine may be called a query execution engine, a query processor, etc.

[0142] Reference Fig.10 , Fig.10This is a flow chart of a data processing method provided in an embodiment of the present application. Taking the electronic device as a database server (hereinafter referred to as the server) and the query statement as an SQL statement as an example, the method includes:

[0143] S1001. The server receives an SQL statement.

[0144] Exemplarily, the SQL statement may include operations such as creating data, inserting data, updating data, deleting data, querying data, connecting, and window functions.

[0145] In some embodiments, the server can communicate with other devices and receive SQL statements from other devices. The server can parse (such as lexical parsing, grammatical parsing, etc.), analyze (such as semantic analysis) and optimize the SQL statements to obtain parsed, analyzed and optimized SQL statements.

[0146] In some embodiments, the query statement includes a join operation that joins the first list and the second list based on a first join condition. For example, the join condition in the above query statement 1 is t1.x=t2.a, that is, the x column of table 1 is equal to the y column of table 2. For another example, the join condition in the above query statement 2 is t1.x>t2.a, that is, the value of the x column of table 1 is greater than the value of the y column of table 2.

[0147] In some other embodiments, the query statement includes a window function and the above-mentioned join operation of joining the first list and the second list based on the first join condition. For example, the window function may be a SUM() function, a RANK() function, etc.

[0148] S1002. The server determines whether the SQL statement satisfies the fusion condition (an example of the first condition).

[0149] In the embodiment of the present application, the fusion conditions include fusion condition 1, fusion condition 2 and fusion condition 3.

[0150] Fusion condition 1 means that if the window function includes a partition clause (PARTITION BY clause) and an order clause (ORDER BY clause), the partition key specified by the PARTITION BY clause and the order key specified by the ORDER BY clause come from the list used to build the ordered memory index (an example of the first list). In other words, if a join operation is performed on Table 1 and Table 2, the partition key and the order key both come from Table 1 or from the list used to build the ordered memory index in Table 2.

[0151] For example, in the clause "PARTITION BY t1.x ORDER BY t1.y" in the SQL statement, the partition key and sort key are both from table 1. If table 1 is used to build an ordered memory index, fusion condition 1 is met. For another example, in the clause "PARTITION BYt1.x ORDER BY t2.b", the partition key is from table 1, the sort key is from table 2, and the partition key and sort key are from both sides of the operator of the join operation (hereinafter referred to as the operator), that is, the partition key and sort key cannot be from the same list, so fusion condition 1 is not met.

[0152] Fusion condition 2 means that the join key of the list used to build the ordered memory index is the prefix of the partition key and sort key combination specified in the window function. In other words, the join key of the list used to build the ordered memory index is the same as the prefix of the partition key and / or sort key. For example, taking the number before the decimal point as the prefix, if t1.x=11.2, t2.a=11.4, then the prefixes of t1.x and t2.a are the same; if t1.x=11.2, t2.a=12.4, then the prefixes of t1.x and t2.a are different. As another example, taking the first 3 bits of the data as a prefix, if t1.x=1125, t2.a=1124, then the prefixes of t1.x and t2.a are both 112, and t1.x and t2.a are the same; if t1.x=1125, t2.a=1224, then the prefix of t1.x is 112, the prefix of t2.a is 122, and the prefixes of t1.x and t2.a are different. In some embodiments, fusion condition 2 means that the connection key of the list used to construct the ordered memory index is the partition key and sort key combination specified in the window function. In other words, the connection key of the list used to construct the ordered memory index is the same as the partition key and / or sort key.

[0153] Exemplarily, when the window function includes a partition clause, the partition key specified in the window function is the join key for constructing a list of ordered memory indexes. It can also be said that the join key corresponding to the first list is the sort key specified by the partition clause.

[0154] For example, the partition key specified in the window function is t1.x. If the join condition is t1.x=t2.a, the join key t1.x is the prefix t1.x of the partition key (t1.x) specified in the window function, and meets the fusion condition 2. If the join condition is t1.y=t2.a, the join key t1.y is not the prefix t1.x of the partition key (t1.x) specified in the window function, and does not meet the fusion condition 2.

[0155] As another example, when the window function includes a sort clause, the sort key specified in the window function is the join key for building a list of ordered memory indexes. That is, the join key for building a list of ordered memory indexes is the sort key specified by the sort clause in the window function. For example, the sort key specified in the window function is t1.x. If the join condition is t1.x=t2.a, the join key t1.x is the prefix t1.x of the sort key (t1.x) specified in the window function, and the fusion condition 2 is satisfied. If the join condition is t1.y=t2.a, the join key t1.y is not the prefix t1.x of the sort key (t1.x) specified in the window function, and the fusion condition 2 is not satisfied.

[0156] As another example, when the window function includes a partition clause and a sort clause, the partition key and / or sort key specified in the window function is a join key for constructing a list of ordered memory indexes.

[0157] For example, a window function includes a partition clause and a sort clause, and the partition key specified in the window function is t1.x and the sort key is t1.y. Then the combination of the partition key and the sort key can be expressed as (t1.x, t1.y). The prefix of this combination is t1.x and t1.y. Taking the join key consisting of one field as an example, if the join condition is t1.x=t2.a, then the join key t1.x is the prefix t1.x of (t1.x, t1.y) specified in the window function, and meets the fusion condition 2. If the join condition is t1.y=t2.a, then the join key t1.y is the prefix t1.y of (t1.x, t1.y) specified in the window function, and meets the fusion condition 2. If the join condition is t1.z=t2.a, then the join key t1.z is not the prefix t1.x of (t1.x, t1.y), nor is it the prefix t1.y of (t1.x, t1.y), and does not meet the fusion condition 2. For example, if the join key consists of multiple fields, if the join condition is t1.x=t2.a and t1.y=t2.b, then the join key (t1.x, t1.y) is the prefix of the partition key and sort key combination (t1.x, t1.y) specified in the window function, and meets the fusion condition 2. If the join condition is t1.x=t2.a and t1.z=t2.b, then the join key (t1.x, t1.z) is not the prefix of (t1.x, t1.y), and does not meet the fusion condition 2.

[0158] Fusion condition 3 means that if the window function includes (also called the window function with) input parameters, the input parameters come from the same side of the operator.

[0159] For example, taking the join condition t1.x=t2.a as an example, in the window function SUM(t1.z), the input parameter t1.z comes from the t1 table, that is, the input parameter comes from the left side of the operator, which satisfies fusion condition 3. Similarly, in the window function SUM(t2.c), the input parameter t2.c comes from the t2 table, that is, the input parameter comes from the right side of the operator, which also satisfies fusion condition 3. For another example, in the window function SUM(t1.z+t2.c), the input parameters t1.z and t2.c come from the t1 table and t2 table respectively, that is, the input parameters come from both sides of the operator, which does not satisfy fusion condition 3.

[0160] In some other embodiments, fusion condition 3 may also mean: if the window function includes multiple input parameters, the multiple input parameters come from the same side of the operator.

[0161] The server can obtain the semantics of the SQL statement through the parsed SQL statement and determine whether the SQL statement is a query statement with a join operation and a window function. If the SQL statement is a query statement with a join operation and a window function, it is determined whether the query statement meets the fusion condition.

[0162] In some embodiments, the SQL statement satisfies all the fusion conditions in the above fusion conditions 1 to fusion condition 3 and is deemed to meet the fusion conditions. Exemplarily, taking query statement 3 as an example, the partition key and sort key combination specified by the window function is (t4.x, t4.y), both of which are from the same side of the operator, thus satisfying the first fusion condition; the join key t4.x is a prefix of (t4.x, t4.y), satisfying the second fusion condition; the input parameter of the window function SUM is t4.z, which is on the same side of the operator, satisfying the third fusion condition.

[0163]

[0164] In other embodiments, a SQL statement that satisfies some of the above fusion conditions from fusion condition 1 to fusion condition 3 can be considered to satisfy the fusion condition. For example, the partition key or sort key is empty, that is, the window function only specifies the PARTITION BY clause or only specifies the ORDER BY clause. In this case, the SQL statement does not need to satisfy fusion condition 1. For another example, the query statement can only satisfy fusion condition 1 and fusion condition 2, and does not need to satisfy fusion condition 3.

[0165] In some other embodiments, when fusion condition 3 refers to: if the window function includes multiple input parameters, the multiple input parameters are from the same side of the operator. When the window function does not include an input parameter or the window function includes one input parameter, the server may not need to determine fusion condition 3.

[0166] In some embodiments, the server performs the following steps according to the result of determining whether the SQL statement meets the fusion condition: If the SQL statement does not meet the fusion condition, then execute S1003; if the SQL statement meets the fusion condition, then execute S1004.

[0167] In other embodiments, after executing S1001, the server does not execute S1002 and continues to execute S1004. In the process of creating a fusion operator, if the creation of the fusion operator fails because the query statement does not meet the conditions of the fusion operator, the server can terminate the creation of the fusion operator and use the traditional operator to process the query statement in the subsequent process.

[0168] S1003. The server generates an execution plan for the traditional operator.

[0169] Traditional operators are operators in related technologies. For example, operators of join operations are hash joins, nested loop joins, and sort merge joins. For another example, operators of sort clauses in window functions are window sorts.

[0170] Exemplarily, when the SQL statement includes a window function and a join operation, the execution plan of the traditional operator is an execution plan based on a single operator. Take the SQL statement as query statement 3 as an example. Query statement 3 is an SQL query statement with a window function and INNER JOIN. Table 4 (t4) and Table 2 are sample data. The execution plan shows that a hash join is used to perform the join operation, and a window sort is used to calculate the window function.

[0171] like Fig.11 As shown, referring to (1), the hash join operator first constructs the hash table corresponding to Table 4 and the hash table corresponding to Table 2, and obtains the hash join result based on the hash table as shown in (2). The window sort operator sorts the hash join result according to the partition key and sort key of the window function, and obtains the sort result as shown in (3). The value of the window function is calculated based on the sort result, and the final query result is shown in (4).

[0172] S1004. The server creates a fusion operator and a traditional operator.

[0173] The logic of the traditional operator may refer to the execution plan of the traditional operator described in S1003 , and the server may execute the execution plan of the traditional operator according to the logic of the traditional operator.

[0174] A fusion operator is an operator that operates on SQL query statements with window functions and join operations.

[0175] Reference Fig.12 ,The logic of the fusion operator can be implemented as the following steps.

[0176] S1201. The server builds an ordered memory index.

[0177] In this embodiment, the server constructs an ordered memory index based on Table 4. Table 4 is a list used to construct an ordered memory index in S1002 above.

[0178] The implementation of ordered memory index can be a tree, linked list, array, table and other structures. Among them, the record used for indexing can be called index record or index entry, and the record used for storing data can be called data record or data entry.

[0179] In the present application, refer to Fig.12 , taking the B+ tree to implement ordered memory index as an example. The storage structure of the B+ tree includes root nodes, internal nodes, and leaf nodes. Internal nodes are nodes other than root nodes and leaf nodes. The root node and internal nodes are index records, and the leaf nodes are data records.

[0180] The search key of the B+ tree includes the connection key of the connection operation. For example, taking query statement 3 as an example, the connection key of table 4 is column x, and the search key of the B+ tree includes column x of table 4.

[0181] In some embodiments, when the window function of the query statement includes a sort clause, the search key of the B+ tree is the sort key specified by the sort clause.

[0182] In some embodiments, when the window function of the query statement includes a partition clause, the search key of the B+ tree is the partition key specified by the partition clause.

[0183] In some embodiments, when the window function of the query statement includes a sort clause and a partition clause, the search key of the B+ tree is composed of one of the following: a partition key specified by the partition clause and a sort key specified by the sort clause, a partition key specified by the partition clause, or a sort key specified by the sort clause.

[0184] For example, if the window function specifies the partition key as t4.x and the sort key as t4.z, the search key of the B+ tree may be (t4.x, t4.y), or the search key of the B+ tree may be (t4.x), or the search key of the B+ tree may be (t4.y).

[0185] In some embodiments, the partition key constituting the search key is in front and the sort key is in the back. If the partition key involves multiple columns, the order of the columns can be adjusted. If the sort key involves multiple columns, the order of the columns cannot be adjusted. For example, if the window function specifies that the partition key is t4.x and t4.y, and the sort key is t4.z, that is, the SQL statement can be expressed as PARTITION BY t4.x, t4.y ORDER BY t4.z, then the search key can be expressed as (t4.x, t4.y, t4.z) or (t4.y, t4.x, t4.z). For another example, if the window function specifies that the partition key is t4.x, and the sort key is t4.y and t4.z, that is, the SQL statement can be expressed as PARTITIONBY t4.x ORDER BY t4.y, t4.z, then the search key can be expressed as (t4.x, t4.y, t4.z).

[0186] It should be noted that the table where the search key is located (such as the above Table 4) can be a physical table stored in the database (also called a base table) or an intermediate result generated during the execution process, for example, the result of a subquery.

[0187] In some embodiments, the query statement includes an output value (also referred to as a projection column or a feedback result of the query statement), and the leaf node of the B+ tree is used to store the output value in the query statement. That is, a record in the leaf node of the B+ tree stores: the output value in the query statement. The output value is the value to be output in the query statement, for example, the output value is the feedback result specified by the select keyword. Exemplarily, the query statement is an SQL statement, and the output value can be referred to as an SQL expression.

[0188] Exemplarily, taking the above query statement 3 as an example, the output values ​​include column x (t4.x) of Table 4, the result of summing column z of Table 4 (SUM(t4.z)OVER(PARTITION BY t4.x ORDER BY t4.y)), and the ranking (RANK()OVER(PARTITION BY t4.x ORDER BY t4.y)). Then the records in the leaf node store the above three pieces of information. For example, the records in the leaf node include a field for storing the column x parameter of Table 4, a summation field, and a ranking field.

[0189] In some embodiments, the leaf nodes of the B+ tree store the search key value and the matching tag value.

[0190] Still taking the above query statement 3 as an example, the matching tag value is used to mark whether the records in the leaf nodes of the B+ tree (such as the B+ tree constructed in Table 4) and the records in Table 2 (an example of the second list) meet the connection conditions, that is, whether the records in the leaf nodes of the B+ tree match the records in Table 4. If a record in Table 2 matches a record in the leaf node of the B+ tree (also called a successful match), it can be said that a record in Table 2 matches a record in the leaf node of the B+ tree constructed in Table 4.

[0191] In the embodiment of the present application, the matching mark value is used as an example for introduction. The matching number value of a record in the leaf node of the B+ tree indicates the number of times the record matches the record in Table 2. By marking the matching number value of the record in the leaf node, when the connection result of Table 4 and Table 2 is subsequently output, the connection result can be output according to the matching number value. For example, if the matching number value of a record in the leaf node is 3, then the connection result of 3 records of the record is output.

[0192] In some other embodiments, the record of the leaf node of the B+ tree stores the window function value (an example of the result of the window function) and the auxiliary information required for calculating the window function corresponding to a record in Table 4. The auxiliary information required for calculating the window function may include the number of matches, the input parameters of the window function, etc. The server may output the query result of the SQL statement according to the number of matches.

[0193] Among them, if record 1 of the leaf node is constructed by record 1 of Table 4, then the information stored in record 1 of the leaf node (such as the window function value, auxiliary information required to calculate the window function, etc.) is said to be the information corresponding to a record in Table 4.

[0194] Reference Fig.13 , take query statement 3 as an example, to perform join operation and window function operation on table 4 and table 2, Fig.13 The schematic diagram of the process of constructing an ordered memory index by the server is shown. The server constructs an ordered memory index based on Table 4, including steps S1301 to S1304. Through this process, an ordered memory index can be constructed according to the records of Table 4 on the left side of the operator of query statement 3.

[0195] S1301. The server determines whether all records in Table 4 have been acquired.

[0196] In some embodiments, the server constructs an ordered memory index for the table where the sort key and / or partition key of the window function in the query statement is located (such as Table 4).

[0197] In query statement 3, the table on the left side of the operator is Table 4. Taking the construction of an ordered memory index through Table 4 as an example, if the server has obtained all the records in Table 4, that is, the 4 records in the above Table 4 have been obtained, then this process ends. If the server has not obtained all the records in Table 4, then S1302 is executed.

[0198] S1302. The server obtains a record from Table 4.

[0199] In some embodiments, each record in Table 4 is obtained by a table scan operator. In other embodiments, each record in Table 4 is obtained by an index scan operator.

[0200] S1303: The server constructs a new record corresponding to the record in the B+ tree according to the acquired record.

[0201] The new record includes one or more of the parameters corresponding to the following fields: search key value, window function value (such as the sum value of the sum function SUM(), the ranking of the ranking function RANK()), window function input parameter (such as the input parameter of the sum function), and matching number value. The above parameters can be the parameters corresponding to the record obtained in S1302.

[0202] The server obtains the search key value according to the partition key value and sort key value corresponding to the record, and obtains the input parameter of the window function corresponding to the above record in Table 4 according to the field where the input parameter of the window function is located.

[0203] In some embodiments, when the server is constructing a new record, the initial value of the window function corresponding to the record (such as the sum value of the sum function SUM(), the ranking of the ranking function RANK()) and the initial value of the number of matches are both 0.

[0204] For example, taking the query statement 3 above, the search key is (t4.x, t4.y), the window functions are the sum function SUM() and the ranking function RANK(), and the input parameter of the window function is t4.z. The server obtains the first record (1,2,3) from table 4, the search key value is (1,2), the window function value is 0, the input parameter of the window function is 3, the number of matches is 0, and the ranking is 0.

[0205] S1304. The server stores the new record in a leaf node in the B+ tree.

[0206] Internal nodes can include search key values.

[0207] Each leaf node may include one record or multiple records. Exemplarily, when the search key values ​​of multiple records are the same, these records may be stored in the same leaf node. Exemplarily, when the search key values ​​of multiple records are different, these records may be stored in different leaf nodes.

[0208] Take Table 4 as an example to construct a B+ tree. The search key value of each record in Table 4 is different. Each record in Table 4 is stored in a different leaf node, such as Fig.12 In the leaf nodes of the B+ tree shown. Referring to Table 5, Table 5 shows the leaf nodes corresponding to each record in Table 4. Among them, the sum value of the sum function SUM() is shown as the sum value, and the ranking of the ranking function RANK() is shown as the ranking.

[0209] Table 5

[0210]

[0211] In the process of storing the above new records in leaf nodes such as those shown in Table 5, the server can store each record in the corresponding leaf node in order according to the search key value of each record. For example, the search key values ​​of the four records in Table 5 are (1,2), (1,4), (2,6), and (3,8) respectively. When storing these four records, the server inserts the corresponding leaf nodes according to the search key values ​​of these four records, thereby realizing the orderly storage of these four records.

[0212] The above example shows a way of constructing an ordered memory index through S1301 to S1304, and for each record in Table 4, inserting it into the ordered memory index according to the search key value. In this case, each record in Table 4 can be directly inserted into the ordered memory index according to the order of each record in Table 4.

[0213] In other embodiments, the ordered memory index may be constructed by scanning Table 4, sorting the search key value for each record in Table 4, and inserting each record into the ordered memory index in sequence using the sorted result.

[0214] In the above example, Table 4 is used as an example to construct an ordered memory index. It should be noted that in other embodiments, the table on one side of the operator in the SQL statement is Table 1. After the server uses Table 1 to construct an ordered memory index, the leaf node corresponding to each record can still be entered. Figure 7 As shown, that is to say, by constructing an ordered memory index, the records in Table 1 can be sorted in sequence according to the search key value, thereby realizing the sorting of the records in Table 1.

[0215] S1202. The server probes the ordered memory index and calculates the window function.

[0216] Exemplarily, the server uses the records in Table 2 to probe the ordered memory index and calculate the window function according to the first connection condition and the value of the search key.

[0217] It should be noted that detection can also be called matching.

[0218] Reference Fig.14 , Fig.14 The flowchart of detecting the ordered memory index and calculating the window function is shown. Through this process, the records in Table 2 on the right side of the operator of the above query statement 3 can be used to Fig.13 The records in the leaf nodes of the ordered memory index constructed in the above method are updated. Taking query statement 3, which performs a join operation and a window function operation on table 4 and table 2 as an example, S1202 can be implemented as S1401 to S1405.

[0219] S1401. The server determines whether all records in Table 2 have been acquired.

[0220] In some embodiments, after the server builds an ordered memory index for the table where the sort key and / or partition key of the window function in the query statement is located (such as Table 4), it detects the ordered memory index for the table on the other side of the operator (such as Table 2).

[0221] If the server has completed acquiring all the records in Table 2, the scanning ends and S1402 is not executed. If the server has not completed acquiring all the records in the table, S1402 is executed.

[0222] S1402. The server obtains a record from Table 2.

[0223] In some embodiments, each record in Table 2 is obtained by a table scan operator. In other embodiments, each record in Table 2 is obtained by an index scan operator.

[0224] S1403. The server uses the connection key value of the record in Table 2 to detect the B+ tree.

[0225] That is, the server searches the B+ tree for a record having the same join key value as the record in table 2.

[0226] The search key of the B+ tree includes a connection key. The server can obtain the connection key value of the data record of the ordered memory index that matches the record in the second list according to the first connection condition and the connection key value of the record in the second list, and obtain the search key value according to the connection key. The server searches for the record matching the record in the second list in the ordered memory index through the search key, thereby realizing the detection of the ordered memory index using the records in the second list.

[0227] Exemplarily, taking the record (3, 7, 6) in Table 2 as an example, the connection key value of the connection key t2.a of the record is 3. When the server uses the connection key value 3 of the record to detect the B+ tree constructed according to Table 4, the connection key value of the 4th record in Table 4 is obtained as 3 according to the search key value (3, 8) of the B+ tree. Since the connection key value of the connection key t2.a of the 3rd record (3, 7, 6) in Table 2 is the same as the connection key value of the connection key of the 4th record in Table 4, it can be determined that the record (3, 7, 6) in Table 2 matches the 4th record shown in Table 5 (i.e., the 4th record in the original Table 4). There is no need to use the records in Table 2 to match each record in Table 4 one by one, thereby reducing the time for matching with Table 4 and saving processor resources.

[0228] As another example, take the record (4, 9, 8) in Table 2 as an example, the connection key value of the connection key t2.a of the record is 4. When the server uses the connection key value 4 of the record to detect the B+ tree constructed in Table 4, the connection key value with the largest value is 3 according to the search key value (3, 8) of the B+ tree, and no record with the search key value equal to 4 is detected. It can be directly obtained that there is no record matching the record in the leaf node of the B+ tree shown in Table 5. Since the records in the B+ tree are stored in the leaf nodes in order according to the search key, and the search key is determined by the connection key, when using the records in Table 2 to detect the B+ tree, the server can find the record with the same connection key value as the record in Table 2 more quickly, consuming less matching time.

[0229] S1404: The server determines whether the record in Table 2 matches the record in the B+ tree successfully.

[0230] If the record successfully matches the record in the ordered memory index constructed in S1201, execute S1405. If the record does not successfully match the record in the above ordered memory index, obtain the next record in Table 2 and repeat S1401 to S1405 until the scan is completed.

[0231] Exemplarily, the record (3, 7, 6) in Table 2 is successfully matched with the 4th record in the leaf node of the B+ tree shown in Table 5. Further exemplary, the record (4, 9, 8) in Table 2 is not successfully matched with the record in the B+ tree.

[0232] S1405. The server calculates the window function of the records in the B+ tree that match the record, and accumulates the number of matches.

[0233] The server obtains the input parameters of the window function; the input parameters of the window function are the parameters corresponding to the records in the leaf nodes of the B+ tree specified by the window function and / or the parameters corresponding to the records in Table 2 (an example of the second list) specified by the window function. Exemplarily, taking query statement 3 as an example, the input parameter of the sum function SUM() is the z column of Table 4. The parameters corresponding to the records in the leaf nodes of the B+ tree are the parameters of the z column of the records in Table 4 that construct the records. The server executes the window function according to the input parameters of the window function, obtains the result of the window function, and uses the result to update the result of the window function.

[0234] Exemplarily, when the records in Table 2 (such as (3, 7, 6)) match the records stored in the leaf nodes of the B+ tree shown in Table 5 (such as the records in the leaf nodes corresponding to the records (3, 8, 9) in the original Table 4), the server can calculate the parameter values ​​corresponding to the fields stored in the leaf nodes (such as the fields shown in Table 5) based on the records in Table 2 and the records shown in Table 5, that is, calculate the result of the query statement (such as query statement 3).

[0235] For example, if the record (3, 7, 6) in Table 2 matches the 4th record in the B+ tree (i.e., the record with the search key value (3, 8)), the number of matches of the 4th record in the B+ tree is increased by 1, and the ranking corresponding to the RANK() function is increased by 1. The sum value is 9 according to SUM(t4.z) and the input parameter t4.z(9) of the sum function.

[0236] In some embodiments, S1405 further includes: saving the window function calculation result and the matching number value of the record in a record of an ordered memory index matching the record.

[0237] S1202 is introduced below by taking the use of Table 2 to detect the above-mentioned ordered memory index and calculate the window function as an example. First, the first record (1, 3, 2) of Table 2 is used to detect the ordered memory index, and the two records (1, 2, 3) and (1, 4, 5) in the ordered memory index are matched according to the search key values ​​(1, 2) and (1, 4). The sum value of SUM() in the window function calculated for the record (1, 2, 3) is 3, the ranking of RANK() is 1, and the number of matches is 1. The sum value of SUM() in the window function calculated for the record (1, 4, 5) is the original 3 plus the key value 5 of t4.z of the record (1, 4, 5), and the sum value of SUM() is 8. The ranking of RANK() is calculated to be 1, and the number of matches is 1. In the ordered memory index, the server updates the number of matches, the output parameters of the sum function, and the sum value of the above two records, and the records of the updated ordered memory index are shown in Table 6.

[0238] Table 6

[0239]

[0240]

[0241] After detecting the first record of Table 2, S1401 is executed again, and the ordered memory index is detected using the second record (1, 5, 4) of Table 2. The two records (1, 2, 3) and (1, 4, 5) in the ordered memory index are matched according to the search key values ​​(1, 2) and (1, 4). The sum of the SUM() in the window function for the record (1, 2, 3) is the original 3 plus the key value 3 of t4.z in this match (1, 2, 3), and the sum of the SUM() is 6. The ranking of RANK() is still 1, and the number of matches is 2. The sum value of SUM() in the window function for record (1, 4, 5) is the sum value 8 obtained after matching the record (1, 3, 2) in the previous table 2, plus the key value 3 of t4.z that matches the search key value (1, 2) in record (1, 4, 5), plus the key value 5 of t4.z in record (1, 4, 5), and the sum value of SUM() is 16. The rank of RANK() is calculated to be 3, and the number of matches is 2. The server updates the number of matches, the output parameters of the sum function, and the sum value of the above two records in the ordered memory index. The records of the updated ordered memory index are shown in Table 7.

[0242] Table 7

[0243]

[0244] After detecting the second record of Table 2, S1401 is executed again, and the third record (3, 7, 6) of Table 2 is used to detect the ordered memory index, and a record (3, 8, 9) in the ordered memory index is matched according to the search key value (3, 8). The sum value of SUM() in the window function is calculated for the record (3, 8, 9) is 9. The ranking calculated by RANK() is still 1, and the number of matches is 1. The server updates the number of matches, the output parameter of the sum function, and the sum value of the record in the ordered memory index, and the updated record of the ordered memory index is shown in Table 8.

[0245] Table 8

[0246]

[0247] After detecting the third record in Table 2, S1401 is executed again, and the ordered memory index is detected using the fourth record (4, 9, 8) in Table 2. If no record in the ordered memory index is matched, the record in the ordered memory index is not updated. The fourth record is the last record in Table 2. After detecting the fourth record, the process of S1202 ends, and the records of the ordered memory index are obtained as shown in Table 8.

[0248] S1203. The server scans the ordered memory index and outputs the calculation result of the fusion operator.

[0249] The result of the fusion operator is stored in the leaf nodes of the ordered memory index. All leaf nodes are traversed, and the corresponding records are output according to the connection type (such as INNER JOIN or LEFT OUTER JOIN) and the number of matches. For example, the connection type can be obtained from the flag set when constructing the fusion operator.

[0250] Reference Fig.15 , Fig.15 A schematic diagram of the process of scanning the ordered memory index and outputting the calculation results of the fusion operator is shown.

[0251] S1501. The server determines whether all records in the B+ tree have been scanned.

[0252] If the server has completed scanning all the records in the B+ tree, the process ends. If the server has not completed scanning all the records in the B+ tree, S1502 is executed.

[0253] S1502. The server obtains a record from a leaf node of the B+ tree.

[0254] S1503. The server outputs the result according to the connection type and the number of matches.

[0255] Exemplarily, the server outputs the result of the connection operation corresponding to the record of the leaf node (an example of a data record) according to the connection type and the number of matches.

[0256] In some embodiments, the result of the connection operation corresponding to the record of the leaf node may be the result of the connection operation stored in the record of the leaf node. The result of the connection operation may indicate the number of each record, where the record is the record in the leaf node. For example, the leaf node includes record 1, record 2, record 3, and record 4, and the result of the connection operation may be 2 records 1, 2 records 2, 0 records 3, and 1 record 4.

[0257] In some embodiments, the number of results of the connection operation corresponding to each leaf node record output by the server is equal to the matching times value of the record. For example, the leaf node includes record 1, record 2, record 3 and record 4, and the matching times values ​​corresponding to record 1, record 2, record 3 and record 4 are 2 times, 2 times, 0 times and 1 times respectively, then the server outputs 2 output values ​​of record 1, 2 output values ​​of record 2, 0 output values ​​of record 3 and 1 output value of record 4.

[0258] Taking query statement 3 as an example, the output value corresponding to the record of each leaf node is the value of column x of Table 4 stored in the record, the sum value (abbreviated as sum) and the ranking.

[0259] In some embodiments, if the join type is an inner join (INNER JOIN), the result of the join operation is the output value corresponding to the record with a matching count value greater than 0. The query result for the records stored in the leaf nodes shown in Table 5 in the B+ tree is shown in Table 9.

[0260] Table 9

[0261] x and Ranking 1 6 1 1 6 1 1 16 3 1 16 3 3 9 1

[0262] In some embodiments, if the join type is a left outer join, the result of the join operation is the output value corresponding to all records of the leaf nodes of the B+ tree. The query result for the records stored in the leaf nodes shown in Table 5 in the B+ tree is shown in Table 10.

[0263] Table 10

[0264] x and Ranking 1 6 1 1 6 1 1 16 3 1 16 3 2 7 1 3 9 1

[0265] In some embodiments, if the join type is a right outer join, the result of the join operation is the output values ​​corresponding to the records with a matching count value greater than 0 and all the records in Table 2 (an example of the second list).

[0266] In some embodiments, if the connection type is a full connection, the result of the connection operation is the output values ​​corresponding to all records of the leaf nodes of the B+ tree and all records of Table 2.

[0267] It can be seen that this solution includes the number of matches in the records of the ordered memory index. As shown in Tables 5 to 8 above, when one record in Table 4 can be connected to multiple records in Table 2, it is only necessary to store one record in Table 4 and modify the number of matches, sum value, and ranking corresponding to the record to obtain the result of the window function after the record in Table 4 is connected to multiple records (such as sum value and ranking), without having to store each case where the record in Table 4 is connected to each record in Table 2 as a new record. Thus, it is possible to reduce the number of duplicate records generated by the storage connection operation, and play a role in data compression.

[0268] In addition, by storing the parameters corresponding to the fields of the matching times in the records of the B+ tree, when outputting the query results, the matching fields can be output according to the connection type. For example, when the connection type is an inner connection, referring to the result after the connection of Table 4 and Table 2 shown in Table 8, the matching times of the records corresponding to the search key (2, 6) is 0, and the matching times of the remaining search keys are all greater than 0. Therefore, according to the matching times, the records corresponding to the search key (2, 6) are not output, thereby realizing the output of the query statement results.

[0269] It should be noted that the above Fig.15 In the flowchart shown, the example in which the server first executes S1501 to determine whether all the records in the B+ tree have been scanned is used for explanation. In some other embodiments, the server may first try to obtain a record in the B+ tree. If no new record is obtained, it is considered that the server has scanned all the records in the B+ tree. In other words, step S1502 is executed first. If S1502 is not executed successfully, it is considered that the result of the step shown in S1501 is that all the records in the B+ tree have been scanned, thereby terminating the process.

[0270] Through this plan, refer to Fig.16 , two single operators in the traditional operator (such as the window sort operator and the hash join operator, or the window sort operator and the sort merge join operator) are merged into one operator (such as the window sort join fusion operator), that is, the window function and join operation can be realized through one window sort join fusion operator. Fig.17 , when the record in Table 2 matches the record in the ordered memory index, the window function is directly calculated and the corresponding value in the record of the ordered memory index is updated in (1). Fig.18 , the top root node stores the value of the search key 2, the left node in the second row of nodes stores the search key 2, the right node in the second row of nodes stores the search key 3, the left node in the third row of nodes stores the search key 1, and the right node in the third row of nodes stores the search key 2. Using the method of the embodiment of the present application, the connection key t2.a in Table 2 is used to detect Fig.18The B+ tree shown. Among them, the first record (connection key 1) corresponding to t2.a in Table 2 is compared with the node (if the value of the record is greater than the value of the connection key in the search key stored in a node, then continue to compare with the search key value stored in the right child node of the node; if it is less than or equal to, then continue to compare with the search key stored in the left child node). After comparison, the connection key value (1) of the first record is less than the connection key value (2) stored in the root node, then the left node 2 of the second row is compared. After comparison with the node, 1<2, then the information of the first record corresponding to Table 2 is connected with the record in the leftmost leaf node of the third row. By analogy, for each row of records in Table 2, the above method can be used to compare the ordered memory index. It can be seen that the number of comparisons for the first row of records in Table 2 is 3, and there is no need to traverse each record in Table 4. Implementing the connection operation by detecting the ordered memory index can consume less resources and improve the efficiency of the server.

[0271] It can be seen that when the leaf node records store two or more records, a record in Table 2 only needs to be detected once to achieve connection with two or more records. This saves server resources. Fig.17 , there is no need to first connect Table 2 and Table 4, and then calculate the window function on the connected table of Table 2 and Table 4, and directly scan the leaf nodes through (2) to query the ordered memory index to obtain the query result (3), thereby saving the server time for processing window functions and connection operations and saving server resources.

[0272] It should be noted that Table 2 can be either a physical table stored in a database or an intermediate result generated during the execution process, for example, the result of a subquery.

[0273] The above example takes Table 4 in the connection operation as an example to construct an ordered memory index and Table 2 as an example to introduce the logic of the fusion operator. It should be noted that in other embodiments, an ordered memory index can be constructed for Table 2 in the above example, and an ordered memory index can be detected for Table 4 in the above example. The embodiments of the present application are not limited to this.

[0274] In the above example, the logic of the fusion operator is introduced with the table as the processing unit. In some embodiments, the server partitions the two side tables of the operator (such as Table 4 and Table 2), that is, the server can divide each table into multiple parts and build an ordered memory index for each part of the table in turn. The partitioning method can be hash partitioning, range partitioning, etc. Among them, range partitioning means partitioning according to the range of data.

[0275] Exemplarily, the data in Table 4 and Table 2 can be divided into two parts, the first half of the data in Table 4 and Table 2 (the first two rows) belong to the first part, and the second half of the data in Table 4 and Table 2 (the last two rows) belong to the second part. The server first constructs an ordered memory index for the data in the first part of Table 4. After the ordered memory index for the data in the first part of Table 4 is constructed, the ordered memory index is detected using the data in the first part of Table 2 to obtain the first group of ordered memory indexes after detection. Then, the ordered memory index is detected using the data in the second part of Table 2 to obtain the second group of ordered memory indexes after detection. Then, the ordered memory index is constructed for the data in the second part of Table 4. After the ordered memory index for the data in the second part of Table 4 is constructed, the ordered memory index is detected using the data in the first part of Table 2 to obtain the third group of ordered memory indexes after detection. Then, the ordered memory index is detected using the data in the second part of Table 2 to obtain the fourth group of ordered memory indexes after detection. Therefore, when the amount of data in Table 4 and Table 2 is large and the index data cannot be stored in the memory, the server first partitions Table 4 and Table 2 so that the data of each partition can be stored in the memory, and then builds an ordered memory index for each partition, thereby building an ordered memory index for all the data.

[0276] When the server partitions the tables on both sides of the operator, the server obtains records in the table based on the partition as the processing granularity. In this case, the server determines whether all records in the table have been obtained. This can be achieved as follows: the server determines whether all records in all partitions in the table have been scanned.

[0277] In the above example, the logic of the fusion operator is introduced by using a B+ tree to construct an ordered memory index. It should be noted that the data records in the B+ tree can be called leaf nodes. In other ordered memory index structures, data records can also have other names, which are not limited in the embodiments of the present application.

[0278] It should be noted that the process of executing S1201 in the above example is: the server first determines whether all records in the table are obtained, and if not, executes S1302 and S1303. In other embodiments, S1201 can also be implemented as the server first attempts to obtain a record in Table 4, and if the record is obtained, executes S1303, and if the record is not obtained, the operation ends. Similarly, S1202 can be implemented as the server first attempts to obtain a record in Table 2, and if the record is obtained, executes S1403, and if the record is not obtained, the operation ends.

[0279] It should be noted that when the server builds the fusion operator, a tag can be set for the connection type of the fusion operator (such as inner join, left outer join, right outer join, full join), and the tag is used to distinguish the connection type of the fusion operator. Therefore, when outputting the result of the fusion operator, the output result can be corresponding to the connection type of the fusion operator.

[0280] S1005. The server determines whether the cost of the fusion operator is lower than the cost of the traditional operator.

[0281] If the cost of the fusion operator is lower than the cost of the traditional operator, S1006 is executed. Otherwise, if the cost of the fusion operator is higher than the cost of the traditional operator, S1003 is executed.

[0282] The cost of an operator refers to the cost required to execute the operator, such as CPU cost, input / output (I / O) cost, etc. The smaller the cost of an operator, the shorter the time required to execute the operator.

[0283] As a possible implementation, S1005 can be implemented as follows: the server determines whether the cost of the execution plan of the fusion operator is lower than the cost of the execution plan of the traditional operator. The cost of the execution plan refers to the sum of the costs of all operators in the execution plan. Exemplarily, there are one or more traditional operators, and the one traditional operator or the multiple traditional operators implement the same function as the fusion operator. The server determines whether the cost of the fusion operator is lower than the sum of the costs of each of the above traditional operators.

[0284] For example, for a query statement that includes a join operation and a sorting operation in a window function, the fusion operator can implement the functions of the join operation and the sorting operation in the window function. The hash join operator in the traditional operator can implement the join operation, and the window sorting operator can implement the sorting operation. The cost of the traditional operator is the sum of the cost of the hash join operator and the cost of the window sorting operator. Therefore, the server compares the cost of the fusion operator with the cost of the traditional operator to determine whether the cost of the fusion operator is lower than the cost of the traditional operator.

[0285] It should be noted that if the cost of the fusion operator is the same as the cost of the traditional operator, the server can select the operator to generate the execution plan based on other conditions. For example, when the unused memory capacity is greater than the capacity threshold, execute S1006. Alternatively, if the cost of the fusion operator is the same as the cost of the traditional operator, the server can directly give priority to the fusion operator to generate the execution plan or give priority to the traditional operator to generate the execution plan without referring to other conditions. For example, when the cost of the fusion operator is the same as the cost of the traditional operator, execute S1006.

[0286] S1006. The server generates an execution plan for the fusion operator.

[0287] For example, if the query optimizer evaluates and finds that the total cost of the window sort operator and the hash join operator is greater than the cost of the fusion operator, the fusion operator will be used to complete the join operation and the calculation of the window function in the execution plan finally generated by the server. Thus, when the cost of the fusion operator is low, the server will use the fusion operator to replace the traditional single operator to make the new execution plan cheaper. In addition, compared with the single operator execution plan, the fusion operator involves less resource overhead. Moreover, since the tables on both sides of the join key share the same data structure (such as the B+ tree of the ordered memory index mentioned above), the operator execution takes less time, consumes less memory resources, and has higher execution efficiency.

[0288] S1007. The server executes the execution plan of the fusion operator.

[0289] The server can execute the execution plan of the fusion operator according to the logic of the above fusion operator.

[0290] Through this solution, for the above query statements containing connection operations and window functions, the server builds an ordered memory index based on the records in Table 4, and detects the ordered memory index through the records in Table 2. When the records in Table 2 and the data records in the ordered memory index meet the first connection condition, the window function can be calculated and the matching record value corresponding to the data record, the result of the window function and the input parameter of the window function can be updated. By updating the result of the window function and the input parameter of the window function, the server can calculate the result of the new window function based on the result of the window function and the input parameter of the window function in subsequent processing, and output the result of the window function when outputting the result of the query statement later. By updating the matching number value, the connection result of the records in Table 4 and the records in Table 2 is marked, so that in the subsequent process, the server can read the result of the connection operation between Table 4 and Table 2 according to the matching tag value. This solution detects the ordered memory index through the search key, so that in the process of connecting Table 4 and Table 2, with the help of the ordered characteristics of the ordered memory index, the record matching a certain record in Table 2 in the ordered memory index built based on Table 4 is obtained without traversing each record in Table 4, that is, there is no need to compare a certain record in Table 2 with each record in Table 4, thereby improving the efficiency of the server in executing query statements.

[0291] In some embodiments, reference Fig.19 If there is an ORDER BY clause in the query statement, and the sort key specified by the ORDER BY clause in the query statement is a prefix of the partition key and the sort key in the window function, the fusion operator proposed in the present invention can also be used to improve the execution performance of the query statement.

[0292]

[0293] The records of the ordered memory index are sorted according to the partition key and sort key of the window function. Therefore, if the sort key specified by the ORDER BY clause of the query statement is a prefix of the ordered memory index search key, there is no need to sort the calculation results of the window function.

[0294] In other embodiments, the sort clause in the query statement includes the number of output records, such as outputting M records (l imi t M), where M is the number of output records and M is a positive integer. Taking the ordered memory index as a B+ tree as an example, when the server outputs the records of the ordered memory index, it outputs the results of the connection operation corresponding to the records of the M leaf nodes.

[0295] Reference Fig. 20 In another embodiment of the present application, after S1301, S2001 is also included.

[0296] That is to say, after S1201 and before S1202, S2001 is also included.

[0297] S2001. The server generates a range condition including a join key based on the minimum and maximum values ​​of the search key value.

[0298] After the server builds the ordered memory index, it can obtain the maximum and minimum values ​​of the search key value and use the maximum and minimum values ​​of the search key value as the range condition of the join key, where the search key value is the search key value of the list for building the ordered memory index. The range condition is used to indicate the range of the join key in the search key of the ordered memory index.

[0299] Exemplarily, taking the fusion operator executing query statement 5, query statement 5 performs a join operation and a window function operation on table 11 and table 12 as an example, the server builds an ordered memory index for table 11 (t11). As shown in Table 13, the search key of the ordered memory index is (t11.x, t11.y). After the ordered memory index is built, according to the first parameter (t11.x) in the search key of the leaf node, it can be known that the minimum value of the join key t11.x is 0 and the maximum value is 5, that is, t1.x>=0AND t1.x<=5. According to the join condition, t1.x>t2.a, the range condition t2.a<=5 can be dynamically generated.

[0300] SELECT t11.x,

[0301] SUM(t11.z)OVER(PARTITION BY t11.x ORDER BY t11.y)

[0302] FROM t11 INNER JOIN t12 ON t11.x>t12.a; Query statement 5

[0303] Table 11

[0304] x y z 0 1 2 5 3 4

[0305] Table 12

[0306] a b c 10 20 20 20 30 30

[0307] Table 13

[0308]

[0309] Reference Fig.21 In another embodiment of the present application, during the implementation of S1202, S2102 is also included between S1402 and S1403.

[0310] S2102. The server determines whether the record meets the range condition. If the connection key value of a certain record is within the range condition, S1403 is executed for the record. If the connection key value of a certain record is not within the range condition, S1403 is not executed for the record, and the next record is read. Exemplarily, t12.a of the two records in Table 12 is greater than 5, that is, the connection key values ​​of the records in Table 12 are not within the above range condition, so there is no need to execute step S1403, and there is no need to execute the step of detecting the ordered memory index.

[0311] The embodiment of the present application introduces dynamic condition generation technology (i.e., range condition), that is, after the server creates the ordered memory index, it can dynamically generate a range condition for the connection key. Through this condition, before matching the ordered memory index, it is determined in advance whether the records in the table used to detect the ordered memory index (such as Table 2) meet the conditions. If the record in Table 2 does not meet the conditions, the ordered memory index will not be detected for the record, thereby reducing unnecessary calculations.

[0312] The above-mentioned electronic device is used as a server to introduce the data processing method of the embodiment of the present application. It should be noted that the above-mentioned introduction to the method for receiving SQL statements in the embodiment of the present application is only an example. In other embodiments, if the electronic device is a mobile phone, the mobile phone can communicate with other devices in the above manner to receive SQL statements, or it can receive SQL statements through the keyboard or microphone of the mobile phone. Exemplarily, if the client requesting the service (such as a client sending query statement 3 to DBMS) is set in the mobile phone, the mobile phone can receive SQL statements through its own keyboard or microphone, and send the SQL statement to DBMS, so that DBMS processes the query statement.

[0313] The above-mentioned data processing method of the embodiment of the present application is introduced by taking the match mark value as the number of matches as an example. The following is an introduction to the embodiment of the present application by taking the match mark value as the match flag bit used to indicate whether the records in the list on both sides of the operator of the connection operation in the query statement match as an example. Among them, the match flag bit can indicate a successful match or an unsuccessful match.

[0314] Still taking query statement 3 as an example, the connection condition is t4.x=t2.a, the connection type is inner connection, and the construction of B+ tree is introduced with Table 4. In the process of constructing the B+ tree, the matching flag bits corresponding to the records in the leaf nodes constructed by the records of Table 4 can all be set to no. That is, the initial values ​​of the matching flag bits of the records of the leaf nodes of the B+ tree are all set to no. Among them, after the detection of the B+ tree by Table 2 is completed, the matching flag bit of the record of the leaf node is "yes", which can indicate that the value of the a column of the record in Table 2 is the same as the value of the x column of Table 4 stored in the record of the leaf node. That is, it indicates that there is a record in Table 2 that matches the record of the leaf node. The server can set the matching flag bit to "1" to set the bit to yes. The matching flag bit of the record of the leaf node is "no", which can indicate that the value of the a column of the record in Table 2 is the same as the value of the x column of Table 4 stored in the record of the leaf node. That is, it indicates that there is no record in Table 2 that matches the record of the leaf node. The server can set the matching flag bit to "0" to set the bit to no.

[0315] In the process of the server using the first record in Table 2 to detect the B+ tree constructed by Table 4, it is detected that the record matches the records in the leaf node constructed by the first record in Table 4 and the records in the leaf node constructed by the second record in Table 4. The server can set the matching flags of the records in the leaf node constructed by the first record in Table 4 and the records in the leaf node constructed by the second record in Table 4 to "yes". In the process of the server using the second record in Table 2 to detect the B+ tree constructed by Table 4, it is detected that the record matches the records in the leaf node constructed by the first record in Table 4 and the records in the leaf node constructed by the second record in Table 4. At this time, the matching flags of the records in the leaf node constructed by the first record in Table 4 and the records in the leaf node constructed by the second record in Table 4 have been set to "yes", and the server may not change the matching flags of the above records. When the server detects the B+ tree constructed by Table 4 using the third record in Table 2, it detects that the record matches the record in the leaf node constructed by the fourth record in Table 4. The server can set the matching flags of the records in the leaf node constructed by the fourth record in Table 4 to "yes". After all the records in Table 2 are detected, the matching flags of the records in the leaf nodes constructed by the first, second and fourth records in Table 4 are all "yes", that is, the match is successful, and the matching flag of the record in the leaf node constructed by the third record in Table 4 is "no", that is, the match is unsuccessful.

[0316] Thus, the server can output the result of the record whose matching flag is a successful match according to the connection type and matching flag of the connection operation. The result of the record whose matching flag is a successful match is shown in Table 14.

[0317] Table 14

[0318] x and Ranking 1 6 1 1 16 3 3 9 1

[0319] In other embodiments, if the connection type of the connection operation of the query statement 3 is a left outer connection, the result of the connection operation is the output value corresponding to all records of the leaf nodes of the B+ tree.

[0320] In other embodiments, if the connection type of the connection operation of the above query statement 3 is a right outer join, the result of the connection operation is the output value corresponding to the record whose matching mark value is a successful match and all the records in Table 2 (an example of the second list).

[0321] In other embodiments, if the connection type of the connection operation of the query statement 3 is a full connection, the result of the connection operation is the output value corresponding to all records of the leaf nodes of the B+ tree and all records of Table 2. Through the above embodiment, the server can output the connection result of the records in Table 2 and the records in Table 4 according to the connection type and matching flag of the connection operation, thereby realizing the connection of the data in Table 2 and the data in Table 4.

[0322] In the above embodiment, the method of the embodiment of the present application is introduced by taking the query statement including the window function and the join operation as an example. In other embodiments, the query statement includes the join operation but does not include the window function. The data processing method of the embodiment of the present application is described below by taking the query statement including the join operation as an example.

[0323] Exemplarily, the server obtains query statement 6, which includes a join operation connecting Table 4 and Table 2 based on the first join condition (column x of Table 4 is equal to column a of Table 2, t4.x=t2.a). The output value in the query statement is t4.x after the SELECT statement, that is, the output column x of Table 4. The connection type of the join operation is an inner join.

[0324] SELECT t4.x,

[0325] FROM t4 INNER JOIN t2 ON t4.x=t2.a; Query statement 6

[0326] The server constructs a B+ tree (an example of an ordered memory index) based on the records in Table 4 (an example of the first list), and a record in a leaf node (an example of a data record) of the B+ tree stores the value of the connection key and the matching tag value of a record in Table 4. Exemplarily, taking the matching tag value as the matching number value as an example, constructing a B+ tree with the records in Table 4 can obtain the records of the leaf nodes shown in Table 15.

[0327] Table 15

[0328]

[0329] The server can detect the B+ tree using the records in the second list according to the first connection condition and the value of the search key. The search key of the B+ tree includes the connection key. For example, the server can detect the B+ tree by the connection key in the search key.

[0330] If the record in Table 2 and the record shown in Table 15 meet the first connection condition, the matching number value stored in the record in Table 15 is updated. For example, if the first record in Table 2 matches the first record in Table 15, the matching number value of the first record in Table 15 is updated to 1. By updating the matching number value, the connection result of the record in Table 4 and the record in Table 2 is marked, so that in the subsequent process, the server can read the result of the connection operation between Table 4 and Table 2 according to the matching number value.

[0331] It should be noted that the above-mentioned multiple embodiments can be combined and the combined scheme can be implemented. Optionally, some operations in the process of each method embodiment are optionally combined, and / or the order of some operations is optionally changed. In addition, the execution order between the steps of each process is only exemplary and does not constitute a limitation on the execution order between the steps. There can also be other execution orders between the steps. It is not intended to indicate that the execution order is the only order in which these operations can be performed. Ordinary technicians in this field will think of many ways to reorder the operations of this article. In addition, it should be pointed out that the process details involved in a certain embodiment of this article are also applicable to other embodiments in a similar manner, or different embodiments can be used in combination.

[0332] In addition, some steps in the method embodiment may be equivalently replaced by other possible steps. Alternatively, some steps in the method embodiment may be optional and may be deleted in certain usage scenarios. Alternatively, other possible steps may be added in the method embodiment. Alternatively, the execution subject (such as a functional module) of some steps in the method embodiment may be replaced by other execution subjects.

[0333] Furthermore, the above method embodiments may be implemented separately or in combination.

[0334] Some other embodiments of the present application provide a device, which may be the above-mentioned electronic device, etc. The device may include: a memory and one or more processors. Optionally, the device may also include a display screen. The display screen, the memory and the processor are coupled. The memory is used to store computer program code, and the computer program code includes computer instructions. When the processor executes the computer instructions, the device may execute the various functions or steps executed by the corresponding device in the above-mentioned method embodiment. The structure of the device can be seen in Fig. 22 The electronic device (device) shown.

[0335] The core structure of the device can be expressed as Fig. 22 As shown in the structure, the device includes: a processing module 1301, an input module 1302 and a storage module 1303.

[0336] The processing module 1301 may include at least one of a central processing unit, an application processor (AP) or a communication processor (CP). The processing module 1301 may perform operations or data processing related to the control and / or communication of at least one of other elements of the user electronic device. Specifically, the processing module 1301 may be used to control the content displayed on the main screen according to certain trigger conditions. The processing module 1301 is also used to process input instructions or data and determine the display style according to the processed data.

[0337] The input module 1302 is used to obtain instructions or data input by the user and / or obtain instructions or data from other devices, and transmit the obtained instructions or data to other modules of the electronic device. Specifically, the input mode of the input module 1302 may include touch, gesture, approaching the screen, etc., or voice input. For example, the input module can be the screen of the electronic device, obtain the user's input operation and generate an input signal according to the obtained input operation, and transmit the input signal to the processing module 1301. Exemplarily, the input module can obtain instructions or data from other devices through the communication module.

[0338] The storage module 1303 may include a volatile memory and / or a non-volatile memory. The storage module is used to store at least one instruction or data related to other modules of the user device. Specifically, the storage module may record information related to processes, services, businesses, and submodules.

[0339] In some other embodiments, the device further includes a display module 1304. The display module 1304 may include, for example, a liquid crystal display (LCD), a light emitting diode (LED) display, an organic light emitting diode (OLED) display, a micro-electromechanical system (MEMS) display, or an electronic paper display, and is used to display content (e.g., text, images, videos, icons, symbols, etc.) that can be viewed by a user.

[0340] In other embodiments, the device also includes a communication module 1305 for supporting the device to communicate with other devices (through a communication network). For example, the communication module can be connected to a network via wireless communication or wired communication to communicate with other devices or a network server. Wireless communication can use at least one of cellular communication protocols, such as long-term evolution (LTE), advanced long-term evolution (LTE-A), code division multiple access (CDMA), wideband code division multiple access (WCDMA), universal mobile telecommunications system (UMTS), wireless broadband (WiBro) or global mobile communication system (GSM). Wireless communication may include, for example, short-range communication. Short-range communication may include at least one of wireless fidelity (Wi-Fi), Bluetooth, near field communication (NFC), magnetic stripe transmission (MST) or GNSS. The device can obtain a query statement from a client device through a communication module, and send the result of the processed query statement to the client device through the communication module.

[0341] In some embodiments, the input module is used to obtain a query statement, and the query statement includes a connection operation that connects the first list and the second list based on a first connection condition. The processing module is used to build an ordered memory index based on the records in the first list, and the data record of the ordered memory index stores the value of the connection key and the matching tag value of a record in the first list. The search key of the ordered memory index includes the connection key; the matching tag value is used to mark whether the data record of the ordered memory index matches the record in the second list. According to the first connection condition and the value of the search key, the ordered memory index is detected using the records in the second list. If the record in the second list and the data record of the ordered memory index meet the first connection condition, the matching tag value stored in the data record is updated. The storage module is used to store the data record of the ordered memory index, which stores the value of the connection key and the matching tag value of a record in the first list.

[0342] In some other embodiments, the query statement includes an output value. The storage module is further used to store the output value in the query statement stored in the data record.

[0343] In some other embodiments, the query statement further includes a window function. The storage module is further configured to store a result of the window function and / or an input parameter of the window function corresponding to a record in the first list of the data record storage of the ordered memory index.

[0344] In some other embodiments, if the records in the second list and the data records of the ordered memory index meet the first connection condition, the input module is further used to obtain input parameters of the window function. The input parameters of the window function are parameters corresponding to the data records specified by the window function and / or parameters corresponding to the records in the second list specified by the window function. The processing module is also used to execute the window function according to the input parameters of the window function to update the result of the window function.

[0345] In other embodiments, the processing module is also used to determine whether the query statement satisfies the first condition before building the ordered memory index based on the records in the first list. If the query statement satisfies the first condition, an ordered memory index is built based on the records in the first list. The first condition includes at least one of the following conditions: when the window function includes a sorting clause and a partitioning clause, the sorting key specified by the sorting clause and the partitioning key specified by the partitioning clause are both from the first list. When the window function includes a sorting clause, the connection key of the first list is a prefix of the sorting key specified by the sorting clause. When the window function includes a partitioning clause, the connection key of the first list is a prefix of the partitioning key specified by the partitioning clause. When the window function includes a sorting clause and a partitioning clause, the connection key of the first list is composed of one of the following: the partitioning key specified by the partitioning clause and the prefix of the sorting key specified by the sorting clause, the prefix of the partitioning key specified by the partitioning clause, or the prefix of the sorting key specified by the sorting clause.

[0346] In other embodiments, when the window function includes a sort clause, the search key of the ordered memory index is the sort key specified by the sort clause; or, when the window function includes a partition clause, the search key of the ordered memory index is the partition key specified by the partition clause; or, when the window function includes a sort clause and a partition clause, the search key of the ordered memory index is composed of one of the following: the partition key specified by the partition clause and the sort key specified by the sort clause, the partition key specified by the partition clause, or the sort key specified by the sort clause.

[0347] In other embodiments, when the search key of the ordered memory index is composed of a partition key specified by a partition clause and a sort key specified by a sort clause, the partition key is set in front and the sort key is set in the back.

[0348] In some other embodiments, the display module is further used to output the result of the connection operation corresponding to the data record according to the connection type and the matching tag value of the connection operation.

[0349] In other embodiments, the matching tag value is used to mark a successful match or an unsuccessful match; if the connection type of the join operation is an inner join, the result of the join operation is the output value corresponding to the successfully matched record; if the connection type of the join operation is a left outer join, the result of the join operation is the output value corresponding to all data records of the ordered memory index; if the connection type of the join operation is a right outer join, the result of the join operation is the output value corresponding to the successfully matched record and all records in the second list with the matching tag value; if the connection type of the join operation is a full join, the result of the join operation is the output value corresponding to all data records of the ordered memory index and all records in the second list.

[0350] In other embodiments, the match mark value is the number of matches between the data record and the records in the second list; the number of results of the connection operation stored in each output data record is equal to the number of matches of the record.

[0351] In other embodiments, if the connection type is an inner join, the result of the connection operation is the output value corresponding to the record with a matching number value greater than 0; if the connection type is a left outer join, the result of the connection operation is the output value corresponding to all data records of the ordered memory index; if the connection type is a right outer join, the result of the connection operation is the output value corresponding to the record with a matching number value greater than 0 and all records in the second list; if the connection type is a full join, the result of the connection operation is the output value corresponding to all data records of the ordered memory index and all records in the second list.

[0352] In other embodiments, the processing module is also used to detect the ordered memory index when the value of the connection key corresponding to the record in the second list satisfies a range condition, and the range condition is used to indicate the range of the connection key in the search key of the ordered memory index.

[0353] In other embodiments, the records in the second list satisfy the range condition including: if the join key value of the record in the second list is between the maximum value and the minimum value of the join key value in the search key of the ordered memory index, then the join key value of the record in the second list satisfies the range condition.

[0354] It should be noted that each functional module of the device can execute one or more steps in the above method embodiment.

[0355] The present application also provides a chip system, such as Fig.23 As shown, the chip system includes at least one processor 1401 and at least one interface circuit 1402. The processor 1401 and the interface circuit 1402 can be interconnected via lines. For example, the interface circuit 1402 can be used to receive signals from other devices (such as a memory of an electronic device). For another example, the interface circuit 1402 can be used to send signals to other devices (such as processor 1401). Exemplarily, the interface circuit 1402 can read instructions stored in the memory and send the instructions to the processor 1401. When the instructions are executed by the processor 1401, the electronic device can execute the various steps in the above embodiments. Of course, the chip system can also include other discrete devices, which are not specifically limited in the embodiments of the present application.

[0356] An embodiment of the present application also provides a computer storage medium, which includes computer instructions. When the computer instructions are executed on the above-mentioned electronic device, the electronic device executes each function or step executed by the mobile phone in the above-mentioned method embodiment, or the electronic device executes each function or step executed by the vehicle computer in the above-mentioned method embodiment.

[0357] An embodiment of the present application also provides a computer program product. When the computer program product is run on a computer, the computer is enabled to execute each function or step executed by the mobile phone in the above method embodiment, or the computer is enabled to execute each function or step executed by the vehicle computer in the above method embodiment.

[0358] Through the description of the above implementation methods, technical personnel in the relevant field can clearly understand that for the convenience and simplicity of description, only the division of the above-mentioned functional modules is used as an example. In actual applications, the above-mentioned functions can be assigned to different functional modules as needed, that is, the internal structure of the device can be divided into different functional modules to complete all or part of the functions described above.

[0359] In the several embodiments provided in the present application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic. For example, the division of the modules or units is only a logical function division. There may be other division methods in actual implementation, such as multiple units or components can be combined or integrated into another device, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interfaces, indirect coupling or communication connection of devices or units, which can be electrical, mechanical or other forms.

[0360] The units described as separate components may or may not be physically separated, and the components shown as units may be one physical unit or multiple physical units, that is, they may be located in one place or distributed in multiple different places. Some or all of the units may be selected according to actual needs to achieve the purpose of the present embodiment.

[0361] In addition, each functional unit in each embodiment of the present application may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit. The above-mentioned integrated unit may be implemented in the form of hardware or in the form of software functional units.

[0362] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a readable storage medium. Based on this understanding, the technical solution of the embodiment of the present application is essentially or the part that contributes to the prior art or all or part of the technical solution can be embodied in the form of a software product, which is stored in a storage medium, including several instructions to enable a device (which can be a single-chip microcomputer, chip, etc.) or a processor (processor) to perform all or part of the steps of the method described in each embodiment of the present application. The aforementioned storage medium includes: U disk, mobile hard disk, read only memory (ROM), random access memory (RAM), disk or optical disk and other media that can store program code.

[0363] The above contents are only specific implementation methods of the present application, but the protection scope of the present application is not limited thereto. Any changes or substitutions within the technical scope disclosed in the present application shall be included in the protection scope of the present application. Therefore, the protection scope of the present application shall be subject to the protection scope of the claims.

[0364] The terms "first" and "second" in the specification and drawings of this application are used to distinguish objects, or to distinguish the processing of the same object. The words "first" and "second" can distinguish the same or similar items with basically the same functions and effects. For example, the first device and the second device are only used to distinguish different devices, and their order is not limited. Those skilled in the art can understand that the words "first" and "second" do not limit the quantity and execution order, and the words "first" and "second" do not necessarily limit them.

[0365] "At least one" means one or more, and "plurality" means two or more.

[0366] "And / or" describes the association relationship of associated objects, indicating that three relationships may exist. For example, A and / or B can mean: A exists alone, A and B exist at the same time, and B exists alone, where A and B can be singular or plural. The character " / " generally indicates that the previous and next associated objects are in an "or" relationship. "At least one of the following" or similar expressions refers to any combination of these items, including any combination of single or plural items. For example, at least one of a, b, or c can mean: a, b, c, ab, ac, bc, or abc, where a, b, c can be single or plural.

[0367] In addition, the terms "including" and "having" and any variations thereof mentioned in the description of the present application are intended to cover non-exclusive inclusions. For example, a process, method, system, product or device comprising a series of steps or units is not limited to the listed steps or units, but may optionally include other steps or units that are not listed, or may optionally include other steps or units that are inherent to these processes, methods, products or devices.

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

Claims

1. A data processing method, characterized in that: include: Obtain a query statement, wherein the query statement includes a join operation of joining the first list and the second list based on a first join condition; Building an ordered memory index based on the records in the first list, wherein the data record of the ordered memory index stores the value of a connection key and a matching tag value of a record in the first list, and the search key of the ordered memory index includes the connection key; the matching tag value is used to mark whether the data record of the ordered memory index matches the record in the second list; According to the first join condition and the value of the search key, using the records in the second list to detect the ordered memory index; If the records in the second list and the data records of the ordered memory index meet the first connection condition, the matching mark value stored in the data record is updated.

2. The method according to claim 1, characterized in that The query statement includes an output value, and the data record further stores: the output value in the query statement.

3. The method according to claim 1 or 2, characterized in that: The query statement also includes a window function, and the data record of the ordered memory index also stores a result of the window function and / or an input parameter of the window function corresponding to a record in the first list.

4. The method according to claim 3, characterized in that Also includes: If the records in the second list and the data records of the ordered memory index meet the first connection condition, then obtaining the input parameters of the window function; The input parameters of the window function are parameters corresponding to the data record specified by the window function and / or parameters corresponding to the record in the second list specified by the window function; The window function is executed according to the input parameters of the window function to update the result of the window function.

5. The method according to claim 3 or 4, characterized in that: Before building an ordered memory index based on the records in the first list, the method includes: Determine whether the query statement satisfies a first condition, and if the query statement satisfies the first condition, construct an ordered memory index based on the records in the first list; The first condition includes at least one of the following conditions: In the case where the window function includes a sort clause and a partition clause, the sort key specified by the sort clause and the partition key specified by the partition clause are both from the first list; In the case where the window function includes a sort clause, the join key of the first list is a prefix of the sort key specified by the sort clause; In the case where the window function includes a partition clause, the join key of the first list is a prefix of the partition key specified by the partition clause; In the case where the window function includes a sort clause and a partition clause, the join key of the first list consists of one of the following: a partition key specified by the partition clause and a prefix of the sort key specified by the sort clause, a prefix of the partition key specified by the partition clause, or a prefix of the sort key specified by the sort clause.

6. The method according to claim 3 or 4, characterized in that: In the case where the window function includes a sort clause, the search key of the ordered memory index is the sort key specified by the sort clause; or, In the case where the window function includes a partition clause, the search key of the ordered memory index is the partition key specified by the partition clause; or, In the case where the window function includes a sort clause and a partition clause, the search key of the ordered memory index is composed of one of the following: a partition key specified by the partition clause and a sort key specified by the sort clause, a partition key specified by the partition clause, or a sort key specified by the sort clause.

7. The method according to claim 6, characterized in that In the case where the search key of the ordered memory index is composed of a partition key specified by the partition clause and a sort key specified by the sort clause, the partition key is set in front and the sort key is set in the back.

8. The method according to any one of claims 1 to 7, characterized in that: Also includes: According to the connection type of the connection operation and the matching tag value, the result of the connection operation corresponding to the data record is output.

9. The method according to claim 8, characterized in that The matching tag value is used to mark a successful match or an unsuccessful match; if the connection type of the connection operation is an inner connection, the result of the connection operation is the output value corresponding to the record that successfully matched; If the connection type of the join operation is a left outer join, the result of the join operation is the output value corresponding to all data records of the ordered memory index; If the connection type of the join operation is a right outer join, the result of the join operation is an output value corresponding to the record whose matching mark value is a successful match and all the records in the second list; If the connection type of the connection operation is a full connection, the result of the connection operation is the output values ​​corresponding to all data records of the ordered memory index and all records of the second list.

10. The method according to claim 8, characterized in that The matching mark value is the number of matches between the data record and the records in the second list; the number of results of the connection operation stored in each output data record is equal to the number of matches of each data record.

11. The method according to claim 10, characterized in that If the connection type is an inner connection, the result of the connection operation is the output value corresponding to the record whose matching times value is greater than 0; If the connection type is a left outer connection, the result of the connection operation is the output value corresponding to all data records of the ordered memory index; If the join type is a right outer join, the result of the join operation is the output value corresponding to the record with a matching count value greater than 0 and all the records in the second list; If the connection type is a full connection, the result of the connection operation is the output values ​​corresponding to all data records of the ordered memory index and all records of the second list.

12. The method according to any one of claims 1 to 11, characterized in that: Detecting the ordered memory index includes: In the case where the value of the connection key corresponding to the record in the second list satisfies a range condition, the ordered memory index is detected, where the range condition is used to indicate the range of the connection key in the search key of the ordered memory index.

13. The method according to claim 12, characterized in that The records in the second list satisfying the range condition include: If the connection key value of the record in the second list is between the maximum value and the minimum value of the connection key value in the search key of the ordered memory index, the connection key value of the record in the second list satisfies the range condition.

14. A device, characterized in that: include: A processor, the processor is coupled to a memory, and the processor is used to execute a computer program stored in the memory so that the device performs the method according to any one of claims 1 to 13.

15. A database system, characterized in that: The database system comprises the apparatus according to claim 14.

16. A chip system, characterized in that: include: A processor and an interface circuit; the processor and the interface circuit are interconnected via a line; The interface circuit is used to read instructions stored in the memory, and when the instructions are executed by the processor, the chip system executes the method according to any one of claims 1-13.

17. A computer-readable storage medium, characterized in that: The method comprises computer instructions, which, when executed on an electronic device, cause the electronic device to execute the method according to any one of claims 1 to 13.

Images