Data processing method and apparatus

Abstract

A data processing method and apparatus, relating to the field of databases. The present solution can reduce the overhead of a server processing a query statement comprising a connection operation and a window function, and improve data processing efficiency. The method comprises: acquiring a query statement, wherein the query statement comprises a connection operation for connecting a first list and a second list on the basis of a first connection condition, and a window function; building an ordered memory index on the basis of records in the first list, wherein data records of the ordered memory index store values of connection keys corresponding to the records in the first list, values of matching tags, a result of the window function, and input parameters of the window function, and search keys in the ordered memory index comprise the connection keys; on the basis of the first connection condition and values of the search keys, using records in the second list to detect the ordered memory index; and if a record in the second list and a data record of the ordered memory index satisfy the first connection condition, updating the value of the matching tag corresponding to the data record.

Description

Data processing method and device

[0001] This application claims priority from the Chinese patent application filed with the State Intellectual Property Office on September 22, 2023, with application number 202311242954.9 and application name “Data Processing Method and Device”, the entire contents of which are incorporated by reference into this application. Technical Field

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

[0003] When processing query statements, database management systems (DBMSs) typically use a single operator for each operation. This approach is versatile and widely applicable. However, existing operators consume significant server resources when processing join operations, wasting server performance and reducing server efficiency.

[0004] Summary of the Invention

[0005] The present application provides a data processing method and apparatus that can consume fewer server resources when processing query statements including join operations.

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

[0007] 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 the join key value and matching tag value of a record in the first list, the search key of the ordered memory index including the join 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 record 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 matching tag value stored in the data record.

[0008] For example, if the initial value of the matching record value is empty or no, and 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 said that the matching record value is not updated. For another example, if 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; 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.

[0009] 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 count 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 mark 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 in the ordered memory index built based on the first list that matches a record in the second list is obtained, without traversing every record in the first list, that is, there is no need to compare a record in the second list with every record in the first list, thereby improving the efficiency of the server in executing query statements.

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

[0011] 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 a placeholder in the reserved memory, or not store content in the reserved memory, and store the actual connection key value and / or matching tag value in the reserved memory in subsequent processes.

[0012] In combination with the first aspect, in one possible design, the data records of the ordered memory index store 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.

[0013] In this solution, the initial value of the join key value and / or the initial value of the match tag value can be a preset value. For example, the initial value of the match tag value is set to no, or the initial value of the match tag value is set to 0. For another example, the initial value of the join key value is "00". The initial value of the join key value and / or the initial value of the match tag value can be selected based on the values ​​in the first list. For example, the initial value of the join key value can be the value of the join key of a record in the first list of records used to construct the ordered memory index.

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

[0015] In this solution, by storing the output value of 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. 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. The data record can store the value of the connection key (that is, the name corresponding to the record in the first list). 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. As another example, 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 parameters of the window function. The result of the window function and the input parameters of the window function are stored in the data record.

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

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

[0018] In addition, when operating on a query statement that includes 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.

[0019] 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, then obtain the input parameters of the window function; 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, update the result of the window function.

[0020] In this solution, the window function result can be calculated based on the window function input parameters and the window function. By updating the window function result 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 window function result field when the data record is subsequently read.

[0021] In conjunction with the first aspect, in one possible design, when the window function input parameters include parameters corresponding to the data record, the server may store the input parameters corresponding to the record in the first list in the window function input parameter field corresponding to the data record when constructing the ordered in-memory index. Thus, when the server calculates the window function, it can read the window function input parameters from the window function input parameter field corresponding to the record, calculate the window function result based on the window function input parameters and the window function, and update the window function result corresponding to the data record.

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

[0023] In conjunction with the first aspect, in one 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 in the input parameter field of the window function corresponding to the data record. When the server detects the ordered memory index through the record in the second list, the server may obtain the input parameters of the window function for the record in the second list, read the data record in the ordered memory index that matches the record in the second list, calculate the result of the window function based on the window function, the input parameters of the window function for the data record, and the input parameters of the window function for the record in the second list, and update the result of the window function corresponding to the data record.

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

[0025] Determine whether the query statement satisfies the first condition. 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: 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.

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

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

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

[0029] 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 outputting 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 includes: outputting the result of the connection operation corresponding to the data record according to the matching tag value of each data record and the connection type of the connection operation.

[0030] 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 of 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 of the second list.

[0031] In conjunction with the first aspect, in one possible design, the match tag value is the number of matches between the data record and records in the second list; the number of join operation results stored in each output data record is equal to the number of matches for each data record. Thus, the server can output the join operation result corresponding to the data record based on the join type and number of matches of the join operation.

[0032] In combination with the first aspect, in one possible design, 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 count 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 count value greater than 0 and all records of 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 of the second list.

[0033] 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 join key corresponding to the record in the second list meets the range condition, and the range condition is used to indicate the range of the join key in the search key of the ordered memory index.

[0034] 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 number of records in the second list that do not meet the connection condition to be detected, thereby reducing energy waste.

[0035] 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 and minimum connection key values ​​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.

[0036] In conjunction with the first aspect, in one possible design, the query statement includes a sort clause. In this solution, when the server outputs query results for a query statement including a sort clause, it can output the query results in an ordered order based on the order of the data records in the ordered memory index, eliminating the need to re-sort the result of concatenating the first and second lists, thereby conserving server resources.

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

[0038] In a second aspect, a device is provided, comprising: a processor coupled to a memory, the processor configured to execute a computer program stored in the memory, so that the device performs the method described in the first aspect and any implementation thereof.

[0039] 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, and 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.

[0040] In a fourth aspect, a chip system is provided, comprising: a processor and an interface circuit; the processor and the interface circuit are interconnected through 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.

[0041] In a fifth aspect, a chip system is provided, comprising a processor configured to support an electronic device in implementing the functions described in the first aspect. In one possible design, the chip system further comprises a memory configured to store program instructions and data necessary for the electronic device. The chip system may consist solely of a chip or may include a chip and other discrete components.

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

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

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

[0045] In a ninth aspect, a database system is provided, wherein the database system includes the apparatus according to the second aspect.

[0046] 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 be referred to the beneficial effects in the first aspect and any possible implementation method provided above, and will not be repeated here. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] FIG1 is a schematic diagram of an execution plan of a query statement 1 in the related art;

[0048] FIG2 is a flow chart of an execution plan of a query statement 1 in the related art;

[0049] FIG3 is a schematic diagram of an execution plan of query statement 2 in the related art;

[0050] FIG4 is a flow chart of an execution plan of query statement 2 in the related art;

[0051] FIG5 is a schematic diagram of a database system provided in an embodiment of the present application;

[0052] FIG6 is a schematic diagram of the hardware structure of an electronic device provided in an embodiment of the present application;

[0053] FIG7 is a schematic diagram of the architecture of a DBMS provided in an embodiment of the present application;

[0054] FIG8 is a schematic diagram of a process for parsing SQL statements according to an embodiment of the present application;

[0055] FIG9 is another schematic diagram of a process for parsing SQL statements according to an embodiment of the present application;

[0056] FIG10 is a flow chart of a data processing method provided in an embodiment of the present application;

[0057] FIG11 is a flow chart of processing a query statement provided by the related art;

[0058] FIG12 is another flow chart of the data processing method provided in an embodiment of the present application;

[0059] FIG13 is a schematic diagram of a process for constructing an ordered memory index according to an embodiment of the present application;

[0060] FIG14 is a schematic diagram of a process for detecting ordered memory indexes and calculating window functions according to an embodiment of the present application;

[0061] FIG15 is a schematic diagram of a process for scanning ordered memory indexes and outputting calculation results of fusion operators according to an embodiment of the present application;

[0062] FIG16 is a flow chart of an execution plan of a query statement provided in an embodiment of the present application;

[0063] FIG17 is another flow chart of the data processing method provided in an embodiment of the present application;

[0064] FIG18 is a schematic diagram of a B+ tree provided in an embodiment of the present application;

[0065] FIG19 is another flow chart of the data processing method provided in an embodiment of the present application;

[0066] FIG20 is another flow chart of the data processing method provided in an embodiment of the present application;

[0067] FIG21 is another flow chart of the data processing method provided in an embodiment of the present application;

[0068] FIG22 is a schematic diagram of a device provided in an embodiment of the present application;

[0069] FIG23 is a schematic structural diagram of a chip system provided in an embodiment of the present application. DETAILED DESCRIPTION

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

[0071] Database: A database is used to organize and store data. A database consists of one or more tables, each representing a type of entity, such as a table of product prices or a table of product inventory. 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.

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

[0073] A query operation (or simply query) refers to the process of retrieving data from a database. This can be done for a single table or across multiple tables. Through query operations, users can easily obtain the required data for data analysis, report generation, and decision support. Query operations can include joins and window functions.

[0074] 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 the two tables. 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 generally 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.

[0075] For example, taking the connection between Table 1 (t1) and Table 2 (t2), the connection condition is that column x of Table 1 is equal to column a of Table 2, that is, t1.x = t2.a. Then, when the value of column x of a record in Table 1 is equal to the value of column a of a record in Table 2, the record in Table 1 is connected with the record in Table 2 to obtain the connection result. Referring to Table 3 (t3), taking the left outer join 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. The connection operation can obtain a table after multiple tables are connected, and the query operation can query the information of multiple tables by querying the connected table.

[0076] Table 1

[0077] Table 2

[0078] Table 3

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

[0080] It is understandable that the DBMS can perform the aforementioned operations (e.g., querying data) based on the statements (or commands) it reads. For example, the aforementioned statements may be SQL statements. The JOIN keyword in the SQL statement can implement join operations, such as using keywords such as INNER JOIN, LEFT JOIN, and RIGHT JOIN. In the SQL query statement SELECT * FROM t1 LEFT JOIN t2 ON t1.x = t2.a, the join condition is that column x in Table 1 is equal to column a in Table 2. This query statement can yield the joined Table 3.

[0081] Window functions: Window functions (also known as analytic functions) are used to analyze data in relational databases. They can sort, perform aggregate calculations (such as sums), and generate a result for each row in a group (one or more rows). Window functions are typically used in SELECT statements and can include clauses such as OVER, PARTITION BY, and ORDER BY.

[0082] The partition key specified by the PARTITION BY clause can group a table, with rows with the same partition key value placed in the same group. For example, referring to Table 1, assume the partition key is the x field (t1.x) in Table 1, expressed as PARTITION BY t1.x. Based on the value of the x field in each row of data, the data in Table 1 can be divided into three groups. For example, if 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, these two rows of data form a group. For ease of description, this group is called Group A. Similarly, the third row of data (2,6,7) forms a group, which is called Group B. The second row of data (3,8,9) forms a group, which is called Group C.

[0083] For example, let's sum the y column of a set of data. Each row of data is represented by (x, y, z, sum), where the sum column represents the summed result. Group A can be represented as (1, 2, 3, 6) or (1, 4, 5, 6). Groups B and C each have only one row of data, and the sum value is the y value of that row. That is, Group B can be represented as (2, 6, 7, 6), and Group C can be represented as (3, 8, 9, 8).

[0084] The sort key specified in the ORDER BY clause of a window function can be used as the sort index. For example, referring to Table 1, assume the sort key is the x field (t1.x) in Table 1, expressed as ORDER BY t1.x. The four rows of data in Table 1 can be sorted based on the value of the x field in each row. For the fourth row of data in Table 1, t1.x is 1, 3, 2, and 1, respectively. After sorting in ascending order by the sort key, the data shown in Table 4 is obtained.

[0085] Table 4

[0086] 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 queries and range queries. The query conditions of point queries are equality conditions, and the query conditions of range queries are inequality conditions. 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.

[0087] Similar to the join key, the search key can be a single column or a combination of multiple columns. For example, the search key is column x (t1.x) in Table 1. After sorting the columns from smallest to largest according to the search key, the resulting data is shown in Table 4.

[0088] An execution plan is the specific steps and sequence for executing a statement in a DBMS. The basic unit of operation in an execution plan is called a physical operator, representing a specific operation, such as a table scan or hash join. Operators in an execution plan form a tree structure in the order in which they are executed. The root node of the tree is the outermost operator, and the leaf nodes are the innermost operators.

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

[0090] When a query involves a join operation and a window function, the industry generally uses a single physical operator to implement both operations. Each physical operator performs one of the operations. Furthermore, the join operation is processed first, followed by the window function. For example, a hash join is used first, followed by a window sort. The following example illustrates the query processing of Tables 1 and 2.

[0091] For example, referring 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.

[0092] SELECT t1.x,

[0093] SUM(t1.z)OVER(PARTITION BY t1.x)

[0094] FROM t1,t2

[0095] WHERE t1.x=t2.a; Query statement 1 The execution plan of query statement 1 is shown in FIG2 , and the specific steps include S201 to S204 .

[0096] S201: The server uses a table scan operator to scan the 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 join 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, referring to Figure 3, for a query statement containing a join and a window function (such as query statement 2), if the join relationship is a non-equivalence 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] SELECT t1.x,

[0102] SUM(t1.z)OVER(PARTITION BY t1.x ORDER BY t1.y)

[0103] FROM t1,t2

[0104] WHERE t1.x>t2.a; Query statement 2 The execution plan of query statement 2 is shown in FIG4 , and the specific steps include S401 to S405 .

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

[0106] 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 join key.

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

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

[0109] S405: The server calculates the window function in groups based on the sorting results and outputs the query results.

[0110] The above example contains a query statement that includes a join operation and a window function. A single operator approach is used to implement the join operation (such as building a hash table and joining hash tables) and the window function (such as sorting). Both hash table building and sorting are computationally intensive operations, making them redundant and consuming significant amounts of central processing unit (CPU) and memory resources, wasting server performance. When the query statement meets certain characteristics, the above single operator approach cannot leverage these characteristics to further improve the execution plan's efficiency. For example, in a memory-tiered scenario, the CPU cache is relatively small. When the CPU processes data, it needs to load the data from memory into the cache and then perform operations on the cached data. Using a single operator approach to join Tables 1 and 2 and perform window function operations requires the following steps: First, during the sorting process, each record in Table 1 needs to be loaded into the cache for sorting, and then each record in Table 2 needs to be loaded into the cache for sorting. Then, during the window function calculation process, the records in Tables 1 and 2 need to be loaded into the cache for window function calculation. 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, which 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 search key values, window function values, input parameters of the window function, and matching times. Through the method provided by the embodiment of the present application, when the server processes operations including join 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 join operations through the method provided by the embodiment of the present application, it is no longer necessary to use two operators to process the window function and the join operation respectively. The server reads the record in the list of the join operation once in the memory, and can perform the join operation and the calculation of the window function on the record, reducing the number of times the list is added to the cache, thereby reducing the energy consumption of the processor, saving the resources of the server, and having good spatiotemporal locality.

[0111] An embodiment of the present application provides a database system. Referring to FIG5 , the system includes an electronic device 100 and a client 110. The electronic device 100 may include various 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, which parses the query statement, obtains a query result, and feeds the query result back to the client 110.

[0112] In some embodiments, the electronic device 100 is provided with client software, and the electronic device can obtain the query command input by the user. The data processing method provided in the embodiment of the present application can be applied to the electronic device 100. For example, the electronic device can be a database server, a mobile phone, a tablet computer, a personal computer (PC), a netbook, a wearable device, an in-vehicle device, etc. The specific form of the electronic device is not particularly limited in this application.

[0113] Taking a mobile phone as an example, FIG6 shows a schematic diagram of the hardware structure of the electronic device 100. The structures of other electronic devices can refer to the structure of the electronic device 100 shown in FIG6.

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

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

[0116] Transceiver 203 is used to communicate with other devices. In the embodiments of the present application, the transceiver can be a module, circuit, bus, interface, or other device capable of implementing communication functions, used to communicate with other devices. Optionally, the transceiver can be a standalone transmitter that can be used to send information to other devices, or a standalone receiver that can be used to receive information from other devices. The transceiver can also be a component that integrates the functions of sending and receiving information. The embodiments of the present application do not limit the specific implementation of the transceiver.

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

[0118] The memory 202 may be a read-only memory (ROM) or other type of static storage device that can store static information and instructions, a random access memory (RAM) or other type of dynamic storage device 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, an optical disc storage (including a compact 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 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.

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

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

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

[0122] 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 herein may refer to one or more devices, circuits, and / or processing cores for processing data (e.g., computer program instructions).

[0123] The data processing method provided in the embodiments of the present application can be applied in a DBMS. Referring to FIG7 , FIG7 illustrates the architecture of a DBMS provided in the embodiments of the present application. Taking the DBMS execution of SQL statements as an example, the DBMS includes software modules such as a protocol adapter module, an instance management module, an SQL engine, a storage engine, and a public service module. The functions of each module are as follows:

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

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

[0126] SQL engine: responsible for parsing SQL statements and performing corresponding operations, such as query, insert, update, delete, etc. Specifically, referring 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.

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

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

[0129] 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).

[0130] The query optimization module is used to parse SQL query statements (e.g., lexical and syntactic parsing), analyze (e.g., semantic analysis), optimize, and generate execution plans. For example, for a given SQL query statement, the query optimization module rewrites the SQL statement (also known as query transformation) based on predefined rules, generating one or more query statements equivalent to the original SQL statement. The query optimization module then evaluates the cost of these equivalent query statements and generates an optimal execution plan based on the equivalent query statement with the lowest cost, thereby meeting the user's query requirements with the least time and resource costs.

[0131] The query optimization module is also responsible for determining whether to fuse join operations and window functions into a single operator, such as a window sort join fusion operator (referred to as the fusion operator). The query optimization module is also responsible for generating an execution plan tree, which is used to pass the executed operators to the execution engine module. The operators in the execution plan tree can be physical operators (e.g., hash join operators, fusion operators).

[0132] In some embodiments, referring to FIG. 9 , the query optimization module includes a fusion condition determination module and a fusion operator generation module.

[0133] The fusion condition determination module is used to determine whether the SQL statement meets the fusion condition. If the SQL statement meets the fusion condition, the next step is executed.

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

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

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

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

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

[0139] 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 provides services for them.

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

[0141] It should be noted that the above-described DBMS architecture is merely an example. In other examples, the DBMS architecture may include more or fewer modules. Alternatively, the functions performed by each module may vary. For example, the SQL engine does not include a lexical parsing module, a syntax parsing module, or a semantic analysis module. The SQL engine directly obtains the validated syntax tree, generates an execution plan tree based on the syntax tree, and obtains the results after executing the execution plan tree.

[0142] For another example, the architecture of the DBMS does not include an 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.

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

[0144] Referring to Figure 10, Figure 10 is a flow chart of a data processing method provided by 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:

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

[0146] For example, an SQL statement may include operations such as creating data, inserting data, updating data, deleting data, querying data, connecting, and window functions.

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

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

[0149] In 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.

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

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

[0152] Fusion condition 1 means that if the window function includes a partitioning clause (PARTITION BY clause) and an ordering clause (ORDER BY clause), the partition key specified in the PARTITION BY clause and the sort key specified in the ORDER BY clause come from the list used to build the ordered in-memory index (an example of the first list). In other words, if Table 1 and Table 2 are joined, the partition key and sort key both come from Table 1 or from the list used to build the ordered in-memory index in Table 2.

[0153] For example, in the SQL statement "PARTITION BY t1.x ORDER BY t1.y", both the partition key and sort key come from Table 1. If Table 1 is used to construct an ordered in-memory index, fusion condition 1 is satisfied. For another example, in the clause "PARTITION BY t1.x ORDER BY t2.b", the partition key comes from Table 1, and the sort key comes from Table 2. Since the partition key and sort key come from both sides of the join operator (hereinafter referred to as the operator), that is, the partition key and sort key cannot come from the same list, fusion condition 1 is not satisfied.

[0154] Fusion condition 2 means that the join key for the list used to build the ordered memory index is a prefix of the partition key and sort key combination specified in the window function. In other words, the join key for the list used to build the ordered memory index must be the same as the prefix of the partition key and / or sort key. For example, using the number before the decimal point as the prefix, if t1.x = 11.2 and t2.a = 11.4, then the prefixes of t1.x and t2.a are the same. If t1.x = 11.2 and t2.a = 12.4, then the prefixes of t1.x and t2.a are different. For example, taking the first three digits of the data as a prefix, if t1.x = 1125 and 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 and t2.a = 1224, then the prefix of t1.x is 112 and 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 join 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 join key of the list used to construct the ordered memory index is the same as the partition key and / or sort key.

[0155] Exemplarily, when a window function includes a partition clause, the partition key specified in the window function is the join key for constructing the list of ordered in-memory indexes. Alternatively, the join key corresponding to the first list is the sort key specified by the partition clause.

[0156] For example, if the partition key specified in the window function is t1.x, and the join condition is t1.x = t2.a, then the join key t1.x is a prefix of the partition key (t1.x) specified in the window function, satisfying merge condition 2. If the join condition is t1.y = t2.a, then the join key t1.y is not a prefix of the partition key (t1.x) specified in the window function, and thus does not satisfy merge condition 2.

[0157] 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, then the join key t1.x is a prefix t1.x of the sort key (t1.x) specified in the window function, and fusion condition 2 is satisfied. If the join condition is t1.y=t2.a, then the join key t1.y is not a prefix t1.x of the sort key (t1.x) specified in the window function, and fusion condition 2 is not satisfied.

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

[0159] For example, a window function includes a partition clause and an order clause. The partition key specified in the window function is t1.x, and the order key is t1.y. The combination of the partition key and order key can be expressed as (t1.x, t1.y). The prefix of this combination is t1.x and t1.y. For example, if the join key consists of a single field, if the join condition is t1.x = t2.a, then the join key t1.x is a prefix of (t1.x, t1.y) specified in the window function, satisfying fusion condition 2. If the join condition is t1.y = t2.a, then the join key t1.y is a prefix of (t1.x, t1.y) specified in the window function, satisfying fusion condition 2. If the join condition is t1.z = t2.a, then the join key t1.z is neither a prefix of (t1.x, t1.y) nor a prefix of (t1.x, t1.y), thus failing to satisfy fusion condition 2. For example, if the join key consists of multiple fields, and the join conditions are t1.x = t2.a and t1.y = t2.b, then the join key (t1.x, t1.y) is a prefix of the partition key and sort key combination (t1.x, t1.y) specified in the window function, satisfying merge condition 2. If the join conditions are t1.x = t2.a and t1.z = t2.b, then the join key (t1.x, t1.z) is not a prefix of (t1.x, t1.y), and thus does not satisfy merge condition 2.

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

[0161] For example, taking the join condition t1.x = t2.a, 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.

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

[0163] The server can obtain the semantics of the parsed SQL statement from the 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 will determine whether the query statement meets the fusion conditions.

[0164] In some embodiments, a SQL statement that satisfies all of the above fusion conditions from fusion condition 1 to fusion condition 3 is considered to satisfy the fusion condition. For example, 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 located on the same side of the operator, satisfying the third fusion condition.

[0165] SELECT t4.x,

[0166] SUM(t4.z)OVER(PARTITION BY t4.x ORDER BY t4.y)

[0167] RANK()OVER(PARTITION BY t4.x ORDER BY t4.y)

[0168] FROM t4 INNER JOIN t2 ON t4.x=t2.a; Query statement 3

[0169] In other embodiments, a SQL statement that satisfies some of the aforementioned merging conditions 1 through 3 may be considered to satisfy the merging conditions. For example, if the partition key or sort key is empty, that is, the window function specifies only a PARTITION BY clause or only an ORDER BY clause, then the SQL statement need not satisfy merging condition 1. Furthermore, for example, a query statement may satisfy only merging conditions 1 and 2, but not merging condition 3.

[0170] In 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 any input parameters or includes one input parameter, the server may not need to determine fusion condition 3.

[0171] In some embodiments, the server performs the following steps based on 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.

[0172] In other embodiments, after executing S1001, the server does not execute S1002 and continues to execute S1004. During 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 traditional operators to process the query statement in subsequent processes.

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

[0174] Traditional operators are those used in related technologies. For example, operators for join operations include hash join, nested loop join, and sort merge join. Another example is the operator for the sort clause in a window function, such as window sort.

[0175] For example, when an SQL statement includes a window function and a join operation, the execution plan for a traditional operator is based on a single operator. For example, consider query statement 3, which includes a window function and an INNER JOIN. Table 4 (t4) and Table 2 provide sample data. The execution plan shows that a hash join is used for the join operation, and a window sort is used to calculate the window function.

[0176] As shown in Figure 11, 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).

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

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

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

[0180] 12 , the logic of the fusion operator can be implemented as the following steps.

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

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

[0183] Ordered memory indexes can be implemented as structures such as trees, linked lists, arrays, and tables. The records used for indexing are called index records or index entries, and the records used to store data are called data records or data entries.

[0184] In the embodiment of the present application, referring to FIG12 , a B+ tree is used as an example to implement an ordered memory index. The storage structure of the B+ tree includes a root node, internal nodes, and leaf nodes. Internal nodes are nodes other than the root node and leaf nodes. The root node and internal nodes are index records, and the leaf nodes are data records.

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

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

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

[0188] 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: 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.

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

[0190] 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 the partition keys as t4.x and t4.y, and the sort key as 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 the partition key as t4.x, and the sort key as t4.y and t4.z, that is, the SQL statement can be expressed as PARTITION BY t4.x ORDER BY t4.y, t4.z, then the search key can be expressed as (t4.x, t4.y, t4.z).

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

[0192] In some embodiments, a query statement includes an output value (also referred to as a projection column or a query statement's feedback result). Leaf nodes of a B+ tree are used to store the output value in the query statement. Specifically, a record in a 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 feedback result specified by the select keyword. For example, the query statement is an SQL statement, and the output value can be referred to as an SQL expression.

[0193] For example, using query statement 3 above, the output values ​​include column x (t4.x) of Table 4, the sum of column z (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)) of Table 4. The records in the leaf nodes store the above three pieces of information. For example, the records in the leaf nodes include a field for storing the parameter for column x of Table 4, a summation field, and a ranking field.

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

[0195] Still taking the above query statement 3 as an example, the match 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 (which can also be called a successful match), it can be said that the record in Table 2 matches the record in the leaf node of the B+ tree constructed in Table 4.

[0196] In the embodiments of the present application, a match mark value is used as an example. The match mark value of a record in a leaf node of a B+ tree indicates the number of times that record matches the records in Table 2. By marking the match mark value of the records in the leaf node, when the join results of Table 4 and Table 2 are subsequently output, the join results can be output based on the match mark value. For example, if the match mark value of a record in the leaf node is 3, then three join results for that record will be output.

[0197] In some other embodiments, a leaf node of the B+ tree stores a window function value (an example of a window function result) corresponding to a record in Table 4 and auxiliary information required for calculating the window function. The auxiliary information required for calculating the window function may include a match count value, input parameters of the window function, and the like. The server may output the query results of the SQL statement based on the match count.

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

[0199] Referring to Figure 13 , using query statement 3 as an example, which performs a join and window function operation on Table 4 and Table 2, Figure 13 illustrates a schematic diagram of the process by which the server constructs an ordered in-memory index. The server constructs an ordered in-memory index based on Table 4, including steps S1301 to S1304. This process constructs an ordered in-memory index based on the records in Table 4 to the left of the operator in query statement 3.

[0200] S1301. The server determines whether all records in Table 4 have been obtained.

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

[0202] In query statement 3, the table on the left side of the operator is Table 4. Taking Table 4 as an example, if the server has retrieved all records in Table 4, that is, all four records in Table 4, then this process ends. If the server has not retrieved all records in Table 4, then S1302 is executed.

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

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

[0205] S1303. The server constructs a new record corresponding to the record in the B+ tree based on the acquired record.

[0206] The new record includes one or more parameters corresponding to the following fields: a search key value, a window function value (such as the sum of a SUM() function or the rank of a RANK() function), window function input parameters (such as the input parameters of a SUM() function), and a match count value. These parameters may be the parameters corresponding to the record obtained in S1302.

[0207] The server obtains the search key value based on the partition key value and sort key value corresponding to the record, and obtains the input parameters of the window function corresponding to the above record in Table 4 based on the field where the input parameters of the window function are located.

[0208] In some embodiments, when the server constructs 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.

[0209] For example, using query statement 3 above, the search key is (t4.x, t4.y), the window functions are SUM() and RANK(), and the window function input parameter is t4.z. The server retrieves the first record (1,2,3) from table 4, with a search key value of (1,2), a window function value of 0, an input parameter of 3, a match count value of 0, and a rank of 0.

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

[0211] Internal nodes can include search key values.

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

[0213] Taking Table 4 as an example, the search key value for each record in Table 4 is different, and each record in Table 4 is stored in a different leaf node, such as the leaf nodes in the B+ tree shown in Figure 12. Referring to Table 5, Table 5 shows the leaf nodes corresponding to each record in Table 4. 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.

[0214] Table 5

[0215] When storing the new records in leaf nodes such as those shown in Table 5, the server can sequentially store each record in the corresponding leaf node based on 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 them into the corresponding leaf nodes according to their search key values, thereby achieving orderly storage of these four records.

[0216] The above example shows a method 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.

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

[0218] 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 the ordered memory index, the leaf node corresponding to each record can still be as shown in Figure 7. In other words, constructing the ordered memory index can sort the records in Table 1 in sequence according to the search key value, thereby realizing the sorting of the records in Table 1.

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

[0220] Exemplarily, the server uses the records in Table 2 to probe the ordered memory index and calculate the window function based on the first join condition and the value of the search key.

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

[0222] Referring to Figure 14 , a schematic diagram illustrates the process of detecting an ordered memory index and calculating a window function. This process allows the records in Table 2 to be updated in the leaf nodes of the ordered memory index constructed in Figure 13 using the records in Table 2 to the right of the operator in Query Statement 3. Taking Query Statement 3 as an example, where a join operation and window function operation are performed on Table 4 and Table 2, S1202 can be implemented as S1401 through S1405.

[0223] S1401. The server determines whether all records in Table 2 have been obtained.

[0224] 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).

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

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

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

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

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

[0230] The B+ tree's search key includes a join key. Based on the first join condition and the join key values ​​of the records in the second list, the server can obtain the join key values ​​of the data records in the ordered memory index that match the records in the second list, and then obtain the search key value based on the join key. The server uses the search key to search the ordered memory index for records that match the records in the second list, thereby probing the ordered memory index using the records in the second list.

[0231] For example, taking the record (3, 7, 6) in Table 2 as an example, the join key value of the join key t2.a of this record is 3. When the server uses the join key value 3 of this record to probe the B+ tree constructed based on Table 4, it obtains the join key value of the fourth record in Table 4 as 3 based on the search key value (3, 8) of the B+ tree. Because the join key value of the join key t2.a of the third record (3, 7, 6) in Table 2 is the same as the join key value of the join key of the fourth record in Table 4, it can be determined that the record (3, 7, 6) in Table 2 matches the fourth record shown in Table 5 (i.e., the fourth record in the original Table 4). This eliminates the need to match each record in Table 2 with each record in Table 4 one by one, thereby reducing the time required to match Table 4 and conserving processor resources.

[0232] As another example, take the record (4, 9, 8) in Table 2. The connection key value of the connection key t2.a of this record is 4. When the server uses the connection key value 4 of this record to probe the B+ tree constructed in Table 4, it obtains the maximum connection key value of 3 based on the search key value (3, 8) of the B+ tree. No record with a search key value equal to 4 is detected. Therefore, it can be directly obtained that there is no record matching this record in the leaf node of the B+ tree shown in Table 5. Because 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 probe 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.

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

[0234] 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-mentioned ordered memory index, obtain the next record in Table 2 and repeat S1401 to S1405 until the scan is completed.

[0235] For example, 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. For another example, the record (4, 9, 8) in Table 2 is not successfully matched with the record in the B+ tree.

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

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

[0238] For example, 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).

[0239] For example, if the record (3, 7, 6) in Table 2 matches the fourth record in the B+ tree (i.e., the record with the search key value (3, 8)), the number of matches of the fourth 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.

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

[0241] The following uses Table 2 to detect the above-mentioned ordered memory index and calculate the window function as an example to introduce S1202. 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 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 record (1, 4, 5) is the original 3 plus the key value 5 of t4.z of 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. 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 6.

[0242] Table 6

[0243] After probing the first record in Table 2, S1401 is executed again, using the second record in Table 2 (1, 5, 4) to probe the ordered memory index. The search key values ​​(1, 2) and (1, 4) match the two records in the ordered memory index: (1, 2, 3) and (1, 4, 5). The SUM() value in the window function for record (1, 2, 3) is calculated by adding the original value of 3 for t4.z in this match (1, 2, 3), resulting in a SUM() value of 6. The RANK() ranking is still 1, and the number of matches is 2. The sum of the SUM() window function for record (1, 4, 5) is calculated as 8, the sum of the matching values ​​for record (1, 3, 2) in Table 2, plus the key value 3 of t4.z for record (1, 4, 5) that matches the search key (1, 2), plus the key value 5 of t4.z for record (1, 4, 5). The resulting SUM() sum is 16. The RANK() ranking is 3, and the match count is 2. The server updates the match count values, the output parameters of the SUM function, and the sum value for the two records in the ordered memory index. The updated records in the ordered memory index are shown in Table 7.

[0244] Table 7

[0245] After probing the second record in Table 2, S1401 is executed again, using the third record in Table 2 (3, 7, 6) to probe the ordered memory index. The search key value (3, 8) matches one record (3, 8, 9) in the ordered memory index. The sum of the SUM() window function for record (3, 8, 9) is calculated as 9. The RANK() ranking is still 1, and the match count value is 1. The server updates the match count value, the output parameter of the SUM function, and the sum value for this record in the ordered memory index. The updated record in the ordered memory index is shown in Table 8.

[0246] Table 8

[0247] After detecting the third record in Table 2, S1401 is executed again, using the fourth record (4, 9, 8) in Table 2 to detect the ordered memory index. If no record in the ordered memory index is matched, the record in the ordered memory index is not updated. This 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 results of the fusion operator are stored in the leaf nodes of an ordered in-memory index. All leaf nodes are traversed, and the corresponding records are output based on the join type (such as INNER JOIN or LEFT OUTER JOIN) and the number of matches. For example, the join type can be obtained from the flag set when constructing the fusion operator.

[0250] 15 , which shows a flow chart of scanning ordered memory indexes and outputting calculation results of fusion operators.

[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 matching number value.

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

[0257] In some embodiments, the number of results of the join operation corresponding to each leaf node record output by the server is equal to the number of matches for the record. For example, if the leaf node includes records 1, 2, 3, and 4, and the number of matches corresponding to records 1, 2, 3, and 4 are 2, 2, 0, and 1, respectively, the server outputs 2 output values ​​for record 1, 2 output values ​​for record 2, 0 output values ​​for record 3, and 1 output value for 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, the result of the join operation is the output value corresponding to the record with a matching count value greater than 0. The query results for the records stored in the leaf nodes shown in Table 5 in the B+ tree are shown in Table 9.

[0260] Table 9

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

[0262] Table 10

[0263] 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 records in Table 2 (an example of the second list).

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

[0265] As can be seen, this solution includes the match count in the records of the ordered memory index. As shown in Tables 5 through 8 above, if a single record in Table 4 can be joined with multiple records in Table 2, only one record in Table 4 needs to be stored and the corresponding match count, summation, and ranking can be modified. This yields the window function result (e.g., summation and ranking) for the join of that record in Table 4 with multiple records, without having to store a new record for each join between that record in Table 4 and each record in Table 2. This reduces the storage of duplicate records generated by join operations, thus achieving data compression.

[0266] Furthermore, by storing parameters corresponding to the fields of the number of matches in the B+ tree records, when outputting the query results, the matching fields can be output according to the join type. For example, in the case of an inner join, referring to the result of joining Table 4 and Table 2 shown in Table 8, the record corresponding to the search key (2, 6) has a match count of 0, while the match counts for the remaining search keys are all greater than 0. Therefore, based on the number of matches, the record corresponding to the search key (2, 6) can be omitted from output, thereby achieving the output of the query statement results.

[0267] It should be noted that the flowchart shown in FIG15 above uses the example of the server first executing S1501 to determine whether all records in the B+ tree have been scanned. In other embodiments, the server may first attempt to retrieve a record from the B+ tree. If no new record is retrieved, the server is deemed to have scanned all records in the B+ tree. In other words, step S1502 is executed first. If S1502 is unsuccessful, the result of step S1501 is deemed to be that all records in the B+ tree have been scanned, and the process ends.

[0268] Through this solution, referring to Figure 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 fused into one operator (such as the window sort join fusion operator), that is, the two functions of the window function and the join operation can be realized through one window sort join fusion operator. Referring to Figure 17, when the record of Table 2 matches the record in the ordered memory index, the window function is calculated directly, and the corresponding value in the record of the ordered memory index is updated in (1). As shown in Figure 18, the top root node stores the value 2 of the search key, 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 the B+ tree shown in Figure 18. Among them, the first record (connection key 1) corresponding to t2.a in Table 2 is compared with the node (if the record value is greater than the connection key value in the search key stored in a node, it is continued to be compared with the search key value stored in the right child node of the node; if it is less than or equal to, it is continued to be compared 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. Similarly, 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 fewer resources and improve the efficiency of the server.

[0269] It can be seen that when two or more records are stored in the records of the leaf node, a record in Table 2 only needs to be detected once to achieve connection with two or more records. Saving server resources. Still referring to Figure 17, there is no need to first connect Table 2 and Table 4, and then calculate the window function on the table after connecting Table 2 and Table 4. Instead, the query result (3) is obtained by directly scanning the leaf node (2) and querying the ordered memory index, thereby saving the server time for processing the window function and connection operation and saving server resources.

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

[0271] 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 detect the ordered memory index, and introduces 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 do not limit this.

[0272] In the above example, the logic of the fusion operator was described using tables as processing units. In some embodiments, the server partitions the tables on both sides of the operator (e.g., Table 4 and Table 2). This means that the server can divide each table into multiple parts and sequentially construct an ordered in-memory index for each part. Partitioning can be performed using hash partitioning, range partitioning, or other methods. Range partitioning refers to partitioning based on data ranges.

[0273] 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 first part of Table 4 is constructed, the first part of Table 2 is used to detect the ordered memory index to obtain the first set of ordered memory indexes after detection. Then, the second part of Table 2 is used to detect the ordered memory index to obtain the second set of ordered memory indexes after detection. Then, an ordered memory index is constructed for the data in the second part of Table 4. After the ordered memory index for the second part of Table 4 is constructed, the first part of Table 2 is used to detect the ordered memory index to obtain the third set of ordered memory indexes after detection. Then, the second part of Table 2 is used to detect the ordered memory index to obtain the fourth set 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.

[0274] When the server partitions the tables on both sides of the operator, the server obtains records in the table at the partitioning granularity. In this case, the server determines whether all records in the table have been obtained. This can be achieved by: the server determines whether all records in all partitions of the table have been scanned.

[0275] 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 data records in a 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 this embodiment of the application.

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

[0277] It should be noted that when the server constructs a fusion operator, you can set a tag for the fusion operator's join type (such as inner join, left outer join, right outer join, and full join). The tag is used to distinguish the fusion operator's join type. Therefore, when outputting the fusion operator results, you can output the results according to the fusion operator's join type.

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

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

[0280] 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 lower the cost of an operator, the shorter the time required to execute the operator.

[0281] 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 operators. 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 or more 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 aforementioned traditional operators.

[0282] For example, for a query statement that includes a join operation and a sort operation within a window function, a fusion operator can implement both the join and the sort operation within the window function. Traditional operators, such as the hash join operator, can implement the join operation, while the window sort operator can implement the sort operation. The cost of a traditional operator is the sum of the costs of the hash join operator and the window sort operator. The server then compares the cost of the fusion operator with the costs of the traditional operators to determine whether the fusion operator's cost is lower than the traditional operator's.

[0283] It should be noted that if the cost of the fused operator is the same as the cost of the traditional operator, the server may select the operator to generate the execution plan based on other conditions. For example, if the unused memory capacity is greater than the capacity threshold, S1006 is executed. Alternatively, if the cost of the fused operator is the same as the cost of the traditional operator, the server may directly prioritize the fused operator or the traditional operator for generating the execution plan without considering other conditions. For example, if the cost of the fused operator is the same as the cost of the traditional operator, S1006 is executed.

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

[0285] 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 server will use the fusion operator to complete the join operation and the calculation of the window function in the execution plan finally generated by the server. Therefore, when the cost of the fusion operator is lower, the server will use the fusion operator instead of the traditional single operator to make the new execution plan cost lower. 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.

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

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

[0288] Through this solution, for the above-mentioned query statements containing connection operations and window functions, the server constructs 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 parameters of the window function can be updated. By updating the result of the window function and the input parameters 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 parameters of the window function in subsequent processing, and output the result of the window function when outputting the result of the query statement subsequently. By updating the number of matches, the connection results of the records in Table 4 and the records in Table 2 are marked, so that in the subsequent process, the server can read the results of the connection operation between Table 4 and Table 2 according to the matching tag value. This solution uses the search key to detect the ordered memory index. When joining Table 4 and Table 2, the ordered memory index can be used to obtain a record in the ordered memory index constructed based on Table 4 that matches a record in Table 2, without traversing every record in Table 4. In other words, there is no need to compare a record in Table 2 with every record in Table 4, thereby improving the efficiency of the server in executing query statements.

[0289] In some embodiments, referring to FIG. 19 , if there is an ORDER BY clause in the query statement, and the sorting key specified by the ORDER BY clause in the query statement is a prefix of the partition key and the sorting 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.

[0290] For example, refer to query statement 4, SELECT t1.x,

[0291] SUM(t1.z)OVER(PARTITION BY t1.x)

[0292] FROM t1,t2

[0293] WHERE t1.x=t2.a;

[0294] ORDER BY t1.x; query statement 4

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

[0296] In other embodiments, the sort clause in the query statement includes the number of output records, such as "output M records (limit M)," where M is the number of output records and is a positive integer. For example, if the ordered memory index is a B+ tree, when the server outputs records of the ordered memory index, it outputs the results of the join operation corresponding to the records of the M leaf nodes.

[0297] 20 , in another embodiment of the present application, after S1301 , S2001 is further included.

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

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

[0300] After the server builds the ordered in-memory index, it obtains the maximum and minimum search key values ​​and uses them as the range condition for the join key, where the search key is the search key value for the list used to build the ordered in-memory index. The range condition indicates the range of the join key within the search key of the ordered in-memory index.

[0301] For example, taking the fusion operator executing query statement 5, which performs a join operation and a window function operation on Table 11 and Table 12, as an example, the server constructs 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 constructed, the first parameter (t11.x) in the search key of the leaf node indicates that the minimum value of the join key t11.x is 0 and the maximum value is 5, that is, t1.x>=0 AND t1.x<=5. Based on the join condition, t1.x>t2.a, the range condition t2.a<=5 can be dynamically generated.

[0302] SELECT t11.x,

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

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

[0305] Table 11

[0306] Table 12

[0307] Table 13

[0308] 21 , in another embodiment of the present application, during the implementation of S1202 , S2102 is also included between S1402 and S1403 .

[0309] S2102. The server determines whether the record satisfies the range condition. If the join key value of a record is within the range condition, S1403 is executed for the record. If the join key value of a record is not within the range condition, S1403 is not executed for the record, and the server continues to read the next record. For example, if t12.a of both records in Table 12 is greater than 5, that is, the join key values ​​of the records in Table 12 are not within the above range condition, there is no need to execute step S1403 and the step of detecting the ordered memory index does not need to be executed.

[0310] 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 judged 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.

[0311] 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, such as when the electronic device is a mobile phone, the mobile phone can communicate with other devices in the above-mentioned manner to receive SQL statements, or it can receive SQL statements through the keyboard or microphone of the mobile phone. For example, if the client requesting the service (such as the client sending query statement 3 to the DBMS) is set in the mobile phone, the mobile phone can receive the SQL statement through its own keyboard or microphone, and send the SQL statement to the DBMS, so that the DBMS processes the query statement.

[0312] The data processing method of the embodiment of the present application is described above using the match tag value as an example of the number of matches. The embodiment of the present application is described below using the match tag value as a match flag used to indicate whether the records in the lists on both sides of the operator of a join operation in a query statement match. The match flag can indicate a successful match or an unsuccessful match.

[0313] Taking query statement 3 as an example, the join condition is t4.x = t2.a, the join type is an inner join, and the B+ tree construction using Table 4 will be described. During the B+ tree construction process, the match flags corresponding to the records in the leaf nodes constructed from the records in Table 4 can all be set to false. That is, the initial value of the match flags in the records of the leaf nodes of the B+ tree is all set to false. After Table 2 completes the B+ tree detection, if the match flag in the leaf node record is "true," it means that the value of column a in the record in Table 2 is the same as the value of column x in Table 4 stored in the record of the leaf node. In other words, it means that there is a record in Table 2 that matches the record in the leaf node. The server can set the match flag to "1" to true. If the match flag in the leaf node record is "false," it means that there is no record in Table 2 that has the same value of column a in the record in Table 4 stored in the record of the leaf node. In other words, it means that there is no record in Table 2 that matches the record in the leaf node. The server can set the match flag to false by setting the match flag to "0."

[0314] 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 already been set to "yes". The server may not change the matching flags of the above records. When the server uses the third record in Table 2 to probe the B+ tree constructed by Table 4, 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 match flags of the records in the leaf node constructed by the fourth record in Table 4 to "yes". After all records in Table 2 are probed, the match flags of the records in the leaf nodes constructed by the first, second, and fourth records in Table 4 are all "yes", indicating a successful match. The match flag of the record in the leaf node constructed by the third record in Table 4 is "no", indicating an unsuccessful match.

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

[0316] Table 14

[0317] In other embodiments, if the join type of the join operation of the query statement 3 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.

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

[0319] In other embodiments, if the join type of the join operation in query statement 3 is a full join, the result of the join operation is the output values ​​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 join results of the records in Table 2 and the records in Table 4 based on the join type and the match flag of the join operation, thereby achieving the join of the data in Table 2 and the data in Table 4.

[0320] In the above embodiments, the method of the present invention is described using a query statement including a window function and a join operation as an example. In other embodiments, the query statement includes a join operation but does not include a window function. The data processing method of the present invention is described below using a query statement including a join operation as an example.

[0321] For example, the server receives 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 join type of the join operation is an inner join.

[0322] SELECT t4.x,

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

[0324] The server constructs a B+ tree (an example of an ordered in-memory index) based on the records in Table 4 (an example of the first list). Each record in a leaf node (an example of a data record) of the B+ tree stores the join key value and match tag value of a record in Table 4. For example, assuming the match tag value is the number of matches, constructing a B+ tree using the records in Table 4 results in the leaf node records shown in Table 15.

[0325] Table 15

[0326] The server can use the records in the second list to detect the B+ tree based on the first join condition and the value of the search key. The search key of the B+ tree includes the join key. For example, the server can detect the B+ tree using the join key in the search key.

[0327] If the record in Table 2 meets the first join condition with the record shown in Table 15, the match count value stored for that record in Table 15 is updated. For example, if the first record in Table 2 matches the first record in Table 15, the match count value for the first record in Table 15 is updated to 1. By updating the match count value, the join result of the record in Table 4 and the record in Table 2 is marked. In subsequent processes, the server can read the result of the join operation between Table 4 and Table 2 based on the match count value.

[0328] 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 in 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.

[0329] Furthermore, some steps in the method embodiments may be equivalently replaced with other possible steps. Alternatively, some steps in the method embodiments may be optional and may be deleted in certain usage scenarios. Alternatively, other possible steps may be added to the method embodiments. Alternatively, the execution entities (e.g., functional modules) of some steps in the method embodiments may be replaced with other execution entities.

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

[0331] 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, memory, and processor are coupled. The memory is used to store computer program code, which includes computer instructions. When the processor executes the computer instructions, the device may perform the various functions or steps performed by the corresponding device in the above-mentioned method embodiment. The structure of the device can be referred to the electronic device (device) shown in Figure 22.

[0332] The core structure of the device can be represented as the structure shown in FIG22 , and the device includes: a processing module 1301 , an input module 1302 and a storage module 1303 .

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

[0334] 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 method of the input module 1302 may include touch, gesture, proximity to 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 based on 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.

[0335] Storage module 1303 may include volatile memory and / or non-volatile memory. The storage module is used to store instructions or data related to at least one of the other modules of the user equipment. Specifically, the storage module may record information related to processes, services, transactions, and sub-modules.

[0336] 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 microelectromechanical system (MEMS) display, or an electronic paper display, and is configured to display user-viewable content (e.g., text, images, videos, icons, symbols, etc.).

[0337] In other embodiments, the device further includes a communication module 1305 for supporting the device to communicate with other devices (via 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 adopt 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 System for Mobile Communications (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 the communication module, and send the result of the processed query statement to the client device through the communication module.

[0338] In some embodiments, the input module is used to obtain a query statement, which includes a connection operation connecting the first list and the second list based on a first connection condition. The processing module is used to construct 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.

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

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

[0341] In some other embodiments, if the records in the second list and the data records of the ordered memory index meet the first join condition, the input module is further configured to obtain input parameters for 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 further configured to execute the window function based on the input parameters of the window function to update the result of the window function.

[0342] In other embodiments, the processing module is further used to determine whether the query statement satisfies the first condition before constructing 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 constructed 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 join 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 join 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 join 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.

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

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

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

[0346] 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 of 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 of the second list.

[0347] In other embodiments, the matching 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 output data record is equal to the number of matches of the record.

[0348] 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 count 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 count value greater than 0 and all records of 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 of the second list.

[0349] 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 meets the 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.

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

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

[0352] An embodiment of the present application also provides a chip system, as shown in Figure 23, which 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 the memory of an electronic device). For another example, the interface circuit 1402 can be used to send signals to other devices (such as the 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 perform the various steps in the above embodiments. Of course, the chip system can also include other discrete devices, which is not specifically limited in the embodiment of the present application.

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

[0354] An embodiment of the present application also provides a computer program product. When the computer program product is run on a computer, it enables the computer to execute the various functions or steps executed by the mobile phone in the above method embodiment, or enables the computer to execute the various functions or steps executed by the vehicle computer in the above method embodiment.

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

[0356] In the several embodiments provided in this application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic. For example, the division of the modules or units is only a logical function division. There may be other division methods in actual implementation. For example, 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.

[0357] The units described as separate components may or may not be physically separate, 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 places. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.

[0358] In addition, the functional units in the various embodiments of the present application may be integrated into a single processing unit, or each unit may exist physically separately, or two or more units may be integrated into a single unit. The aforementioned integrated units may be implemented in the form of hardware or software functional units.

[0359] 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 and includes several instructions for enabling a device (which can be a single-chip microcomputer, chip, etc.) or a processor (processor) to execute all or part of the steps of the method described in each embodiment of the present application. The aforementioned storage medium includes: various media that can store program codes, such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk.

[0360] The above content is only a specific embodiment of this application, but the scope of protection of this application is not limited to this. Any changes or replacements within the technical scope disclosed in this application should be included in the scope of protection of this application. Therefore, the scope of protection of this application should be based on the scope of protection of the claims.

[0361] 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. Words such as "first" and "second" can distinguish identical or similar items with substantially the same functions and effects. For example, the first device and the second device are merely used to distinguish different devices and do not limit their order. Those skilled in the art will understand that words such as "first" and "second" do not limit the quantity or execution order, and words such as "first" and "second" do not necessarily mean different.

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

[0363] "And / or" describes the association relationship of associated objects, indicating that there can be three relationships. For example, A and / or B can mean: A exists alone, A and B exist at the same time, and B exists alone, where A and B can be singular or plural. The character " / " generally indicates that the 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.

[0364] Furthermore, the terms "including," "having," and any variations thereof, as used in the description of this application are intended to cover non-exclusive inclusions. For example, a process, method, system, product, or apparatus comprising a series of steps or units is not limited to the listed steps or units but may optionally include other steps or units not listed, or may optionally include other steps or units inherent to the process, method, product, or apparatus.

[0365] It should be noted that in the embodiments of this 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 this application should not be interpreted as being more preferred or more advantageous than other embodiments or designs. Rather, the use of words such as "exemplary" or "for example" is intended to present the relevant concepts in a concrete manner.

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