Index join query optimization

Abstract

In some implementations, there is provided a method comprising: receiving a query request comprising a join, where the join comprises a range between a first predicate of the join and a second predicate of the join; generating a query plan including an index join operator; executing a query plan comprising an index join operator, the executing comprising obtaining a first value identifier, a second value identifier, and one or more intermediate value identifiers between the first value identifier and the second value identifier from the ordered dictionary, and performing an index join operator using the first value identifier, the second value identifier, and the one or more intermediate value identifiers to obtain a set of results.

Description

Technical Field

[0001] The subject matter described herein relates generally to database processing and, more particularly, to database query optimization. Background Art

[0002] A database can be configured to store an organized collection of data. For example, the data stored in a relational database can be organized according to a pattern that defines one or more relations, each of which is a collection of tuples that share one or more common attributes. The tuples of a relation can occupy rows of a database table, and the columns of the database table can store the values ​​of the common attributes shared by the tuples. In addition, one or more attributes can be used as keywords for establishing and identifying the relationships between the related ones that occupy different database tables. A database can support various database operations for accessing data stored in a database. For example, a database can support transaction processing (e.g., online transaction processing (OLTP)) that modifies data stored in a database. Alternatively and / or additionally, a database can support analytical processing (e.g., online analytical processing (OLAP)) that evaluates data stored in a database. Summary of the invention

[0003] Systems, methods, and articles of manufacture including computer program products for join query optimization are provided. In some embodiments, a system is provided, comprising at least one data processor and at least one memory storing instructions, wherein the instructions, when executed by the at least one data processor, cause operations, wherein the operations include: receiving a query request including a join, wherein the join includes a range between a first predicate of the join and a second predicate of the join; generating a query plan including an index join operator, wherein the index join operator includes a join processor, wherein the join processor is configured to obtain a first value identifier corresponding to the first predicate, a second value identifier corresponding to the second predicate, and one or more intermediate value identifiers between the first value identifier and the second value identifier from a sorted dictionary when the query plan is executed; executing the query plan including the index join operator, wherein the execution also includes obtaining a first value identifier, a second value identifier, and one or more intermediate value identifiers between the first value identifier and the second value identifier from the sorted dictionary, and executing the index join operator using the first value identifier, the second value identifier, and the one or more intermediate value identifiers to obtain a result set; and responding to the query request by providing the result set.

[0004] In some variations, one or more features disclosed herein including one or more of the following features may also be implemented. A query request may be received at a database execution engine. The join may select one or more values ​​from a first table and a second table based on a range between a first predicate of the join and a second predicate of the join. A query plan may be generated by the database execution engine in response to the received query request. A join processor may be configured to include one or more instructions to perform acquisition from a sorting dictionary. The query plan may include a plurality of operators, the plurality of operators including an index join operator. The plurality of operators may be configured to be executed by the database execution engine using at least one pipeline. The execution of a query plan including an index join operator may be performed at least by the database execution engine. The index join operator may include a join processor, and the join processor may obtain a first value identifier, a second value identifier, and one or more intermediate value identifiers from the sorting dictionary. The index join operator may include a join processor, wherein the join processor may use the first value identifier, the second value identifier, and the one or more intermediate value identifiers to execute the index join operator.

[0005] Implementations of the current subject matter may include methods consistent with the descriptions provided herein and articles including tangibly embodied machine-readable media that are operable to cause one or more machines (e.g., computers, etc.) to cause one or more operations of implementing the described features. Similarly, a computer system is also described that may include one or more processors and one or more memories coupled to the one or more processors. A memory that may include a non-transitory computer-readable or machine-readable storage medium may include, encode, store, etc., one or more programs that cause one or more processors to perform one or more operations described herein. A computer-implemented method consistent with one or more implementations of the current subject matter may be implemented by one or more data processors residing in a single computing system or multiple computing systems. Such multiple computing systems may be connected and may exchange data and / or commands or other instructions, etc., via one or more connections, including, for example, connections through a network (e.g., the Internet, a wireless wide area network, a local area network, a wide area network, a wired network, etc.), via direct connections between one or more of the multiple computing systems, etc.

[0006] The details of one or more variations of the subject matter described herein are set forth in the accompanying drawings and the following description. Other features and advantages of the subject matter described herein will be apparent from the description and drawings, and from the claims. The claims following this disclosure are intended to define the scope of the protected subject matter. BRIEF DESCRIPTION OF THE DRAWINGS

[0007] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate certain aspects of the subject matter disclosed herein and, together with the description, help explain some principles associated with the disclosed embodiments.

[0008] Figure 1A and 1B depicts an example of dictionary encoding of a value identifier (ID) according to some embodiments;

[0009] Figure 1C depicts examples of query plans for operators configured in a pipeline according to some implementations;

[0010] Figure 2A and 2B An example of a system including a database execution engine according to some implementations is depicted;

[0011] Figure 3A depicts an example of a process for query optimization and execution using a join index according to some implementations;

[0012] Figure 3B An example of a process including generating a join index operator according to some implementations is depicted; and

[0013] Figure 4 Depicted is a block diagram illustrating an example of a computing system in accordance with some example implementations.

[0014] When practical, similar reference numerals indicate similar structures, features or elements. DETAILED DESCRIPTION

[0015] The database system may use some type of indexing technology to facilitate lookups when performing a join operation. The term "join" refers to a database operation that combines parts of a database, such as combining one part of a table (e.g., a column) with another part of another table (e.g., another column). Indexes may be used to provide auxiliary data structures that use key information during a join to support efficient data lookups, in which case the join is referred to herein as an "index join." The following is an example of an inner join SQL query:

[0016] SELECT *

[0017] FROM T INNER JOIN S

[0018] ON Ta=Sb;Query 1;

[0019] Among other things, the query performs an inner join of table T and table S by combining records from both tables; wherever the value of "a" at table T matches "b" at table S, the matching value is placed in the result set. In other words, the join is performed where the predicate values ​​"a" and "b" are equivalent (such as identical, equal, etc.).

[0020] Because the index implementation can efficiently perform inner joins using indexes, query optimization of Query 1 can cause the query optimizer to use the index join implementation for inner joins. For example, the index join implementation can only be used if an index on either (or both) column Ta or column Sb has been previously created (or is easily available). In the case of an "index join" with Ta, (because the following BETWEEN query assumes that the index is on column Ta) the predicate match is used to find the result set. However, when there is a range in the predicate, such as "BETWEEN (Sb, Sc)" in the following query, the index join optimization performed using Query 1 cannot be used:

[0021] SELECT *

[0022] FROM T INNER JOIN S

[0023] ON Ta BETWEEN(Sb,Sc); Query 2;

[0024] Among them, in query 2, the index join uses the index of table T and uses the range of predicate values.

[0025] Figure 1A An example of a columnar value ID-based storage layout employed in a column store database is depicted. A data vector 110 represents an attribute value 105 that is encoded using a dictionary 115 and then stored in a column store database. For example, the attribute value "Rome" 102A is encoded using the dictionary value ID "4" 102B. Other attribute values ​​such as Berlin, NULL, Istanbul, Hamburg, etc. are also encoded using the dictionary 115. Thus, the column 105 can be stored in an encoded form (e.g., encoded based on the dictionary 115) as a data vector 110 in a column store database (which can also be an in-memory column store database). In Figure 1A In the example of , a dictionary 115 is used to encode null values, but null values ​​may not be stored in a column store database, although they may be stored. A reverse index 148 may be used to map each value ID of a data vector 110 to a list of row IDs (which represent row positions) in the data vector 110 that contain the value ID. Figure 1AThe example shows that in the indexed case, predicates such as "b" and "c" correspond to value IDs of the data vector 110 (which are encoded based on the dictionary 115), rather than actual range values ​​such as Berlin, Hamburg, etc., and therefore using value IDs from the index in the range of "b" and "c" may lead to incorrect results for index joins.

[0026] In some implementations, processing of different types of predicates in index joins is provided, such as the predicate range indicated in "BETWEEN" at Query 2. Although some examples involve the use of pipelines, columns of a column store database, and database execution engines, these are examples of how the disclosed systems and methods for processing index joins and ranges can also be implemented in other database system environments.

[0027] In some embodiments, a dictionary is used to evaluate the index join predicate to obtain a value identifier (ID). Referring to the example of BETWEEN(Sb,Sc), the first predicate "Sb" is used to determine (e.g., using a dictionary of column Ta, such as the sorting dictionary 115) the first value ID of the first predicate of the index join. Similarly, the second predicate "Sc" is used to determine (e.g., using a dictionary of column Ta) the second value ID of the second predicate of the index join. The range defined by Sb and Sc is in a sense converted to a range of value IDs defined by the dictionary of Ta, so that this range of value IDs of Ta drives the index lookup on column Ta. In addition, a dictionary (such as the sorting dictionary 115 for column Ta) is also used to determine the intermediate value ID of the range. Assume, for example, that the range between Sb and Sc corresponds to the total sales of some citations, and Sb corresponds to a value ID of 1 (hamburger) at the sorting dictionary 115, and Sc corresponds to a value ID of 4 (Rome) at the sorting dictionary 115. In this example, the database execution engine (and / or the join processor therein) will return the following value IDs for BETWEEN (Sb, Sc): 1, 2, 3, and 4 (where Sb corresponds to 1, Sc corresponds to 4, and the middle value IDs of the range are 2 and 3). Next, the database execution engine (and / or the join processor therein) will perform a join (e.g., an inner join) on Ta for each of the value IDs 1, 2, 3, and 4. For example, the database execution engine (and / or the join processor therein) executes: FROM T INNER JOIN S ON "Ta=Sb", where Sb corresponds to the value ID of 1 in this example. For example, this process is repeated for each value ID in the range 2, 3, and 4, so the database execution engine (and / or the join processor therein) also executes: FROM T INNER JOIN S ON "Ta=Sb", where Sb corresponds to the value ID of 2, and repeats again with Sb, the value ID of 3, and then finally with Sb, the value ID of 4. In this way, the range of BETWEEN is performed using a value ID lookup for each value ID of the range obtained from the sorted dictionary.

[0028] Figure 1B Another example of attribute values ​​117A of table S is depicted, which are stored in the database as data vectors 117B of value IDs. The value IDs are encoded using a sorted dictionary 117C. Figure 1BIn an example, a database execution engine may receive a query, such as Query 1 described above. In this case, the database execution engine may generate an executable query plan for execution. During query plan generation, the database execution engine may decide to optimize the query algebra of Query 1 by using an index join. In this case, an index join operator is used, and the index join operator with a range (e.g., "BETWEEN(Sb,Sc)") is detected. For example, a SELECT*FROM TINNER JOIN S ON Ta BETWEEN(Sb,Sc) using an index join is detected. When this inner index join using a range is detected (or used), the index join operator is configured to include additional logic (e.g., instructions, code, etc.) for a join processor. The join processor is configured to at least determine a value ID of a predicate value of the range. For example, the join processor may use Figure 1B , determines (at query plan execution time) the value IDs in the range. For example, Sb may have a value of "1" which maps to "$100.00" in the sorting dictionary, while Sc may have a predicate value of "4" which maps to "$200.00". In this example, the join processor obtains the following value IDs for the range from Sb to Sc from the sorting dictionary 117C: 1, 2, 3, and 4. Next, the join processor includes logic to perform an index join multiple times during query plan execution using the range of value IDs obtained from the sorting dictionary. Therefore, after a query plan is generated so as to include an index join (which includes a labeled "join processor"), the query plan is executed, and when an index join operator (which includes a labeled "join processor") is executed, the index join operator (which includes a labeled "join processor") obtains the range of value IDs as described above, and then performs the index join multiple times for each value ID obtained from the sorting dictionary. By performing the index join multiple times using the value IDs obtained from the sorting dictionary, the query plan can correctly perform the index join within the range.

[0029] Before providing additional description regarding the processing of index joins and ranges as disclosed herein, a description is provided below regarding an example database management system that may practice index join query optimization.

[0030] In some embodiments, the database execution engine is included or coupled to an in-memory relational database system. In the case of an in-memory relational database system, the in-memory relational database can utilize the main memory ("memory") of the main storage device for the database table. For example, the in-memory relational database can be implemented as a column-oriented database (or column-oriented database) that stores data from the database table by column rather than by row. For example, in the case of an in-memory column-oriented relational database, each tuple of the relation can correspond to a record occupying a row of the database table, and the columns of the database table can store the values ​​of the common attributes shared by multiple tuples, so that the values ​​of each column occupying the database table (which can span multiple rows (or records) of the database table) can be stored sequentially in one or more data pages, each data page storing at least a portion of the column. The in-memory column-oriented relational database can support efficient data compression and partitioning for large-scale parallel processing. Because the in-memory database can be directly accessed by the central processing unit (CPU) of the computing engine, transactions accessing the in-memory database can be executed to provide almost instantaneous results. Although some examples relate to in-memory databases, the subject matter disclosed herein about custom table scans for top query operators can also be used with other types of databases.

[0031] The throughput and storage capacity of a database (such as a column-oriented relational database and other types of databases) can be maximized by distributing the processing and storage of data across multiple computing nodes. Therefore, a single database table can be divided into multiple fragments (also referred to as partitions). For example, a fragment can be a part of a database, such as a column (or part thereof), a row (or part thereof), and / or other parts of a database table. In the case of multiple fragments, each fragment can be stored in a computing node (among multiple computing nodes) to provide distributed query processing. Doing so can maximize the opportunity for parallelization, where a single query is divided into multiple tasks and assigned to each worker thread for parallel execution. As used herein, the term "worker thread" refers to a thread or execution thread, which is a sequence of instructions independently managed by a scheduler. Each worker thread (e.g., a corresponding sequence of instructions) can be scheduled for sequential execution and / or parallel execution by one or more central processing units (CPUs).

[0032] In a parallelization framework, a database execution engine (which may also be referred to simply as a query execution engine or execution engine) may divide a query that accesses a data set (e.g., one or more database tables) into an amount of tasks determined based on the amount of time required to execute the query on a portion of the data set. Thus, if a large amount of time is required to execute the query on that portion of the data set, the database execution engine may determine that the query is computationally intensive and therefore divide the query into a larger number of smaller tasks. At the same time, the number of worker threads assigned to execute the query may be determined based on the progress of the query observed at various time intervals. For example, when allocating an initial number of worker threads to execute tasks associated with a query, the database execution engine may monitor the progress of the query (e.g., the number of tasks that have not yet been executed by any worker thread, the number of tasks that have been executed relative to the total amount of unfinished tasks, etc.) to determine whether to increase the initial number of worker threads.

[0033] In addition, the database execution engine can also use pipelines. In the case of pipelines, the database execution engine breaks data into smaller data blocks so that each smaller data block can be processed by a series of one or more operators. Operators refer to low-level operators, such as code that performs specific operations during operator execution, rather than high-level relational algebra operators. For example, low-level operators can implement locks on database tables, perform index scans, perform table scans at a given database, etc. During query plan generation and optimization, these low-level operators can be connected to each other to form a graph, such as a directed acyclic graph (DAG). Operators without outgoing links can divide the DAG into multiple pipelines. For example, a pipeline ends at an operator without an outgoing link and starts with an operator after the previous pipeline ends. Pipeline execution can be performed in a cyclic manner, so that the operator "n" of the pipeline takes a data block as input, performs its operation ("n") on the input data, and then the operator "n" pushes its corresponding output to the next operator "n+1". After the last operator in the pipeline completes executing its operation, control is returned to the previous operator. For example, when operator n+1 completes, execution control may return to the previous operator n. Operator n may (or may not) continue processing (e.g., produce another piece of data and push it down the pipeline of operators, perform cleanup tasks, release resources, etc.). In other words, a pipeline represents a set of data processing elements (e.g., operators) connected in series, such that the output of one element of the pipeline is the input of the next element of the pipeline, and buffer storage may be used between elements. In some cases, the operators of a pipeline may be executed in parallel, sequentially, or in a time-sliced ​​manner.

[0034] Figure 1CAn example of a portion of a directed acyclic graph for a pipeline is shown, where circles correspond to operators 150, 152A-C, and 156, which are connected by links 154A, 154B, 154C, 154D, and 154E. As noted, the operators of the pipeline can form or represent a directed acyclic graph, and each operator can have one or more outgoing links (which are used to push data) to one or more other operators. Similarly, each operator can have one or more incoming links (which provide data to the operator) from one or more other operators. As noted, Figure 1C An example of a pipeline including operators 150, 152A-C, and 156 connected by links 154A-E is depicted.

[0035] Figure 2A A system diagram illustrating an example of a system 200 according to some example implementations is depicted. Figure 2A , system 100 may include one or more client devices (such as client device 102), a database execution engine 1500, and one or more databases (such as database 190). Figure 2A As shown, client device 102, database execution engine 1500, and database 190 (which may include one or more database tables 195A-B) may be communicatively coupled via network 160. One or more databases, such as database 90, may include various relational database technologies, including, for example, in-memory databases, column-based databases, row-based databases, hybrid databases (e.g., a combination of column-based and row-based), etc. One or more client devices, such as client device 102, may include processor-based devices, including, for example, mobile devices, wearable devices, personal computers, workstations, Internet of Things (IoT) devices, etc. Network 160 may be a wired network and / or a wireless network, including, for example, a public land mobile network (PLMN), a local area network (LAN), a virtual local area network (VLAN), a wide area network (WAN), the Internet, etc. Although some examples describe database execution engine 1500 as being separate from database 190, database 190 may also include a database execution engine.

[0036] Figure 2B A block diagram illustrating an example implementation of a database execution engine 1500 according to some example implementations is depicted. Figure 2BAs shown, the one or more databases may include a first database 190A, a second database 190B, and a third database 190C, and the database may represent a database layer of a database management system (DBMS), where data may be persisted and / or stored in a structured manner, and where operations such as SQL commands or other types of commands / instructions may be used to query or manipulate the data to provide read, write, and / or perform other operations. To illustrate by way of example, one or more client devices 102A-102N may send queries to a database layer including one or more databases 190 via a database execution engine 1500, which may represent a persistence and / or storage layer that may store and / or query database tables. The query may be sent via, for example, a connection provided by a network 160, such as a wired connection and / or a wireless connection (e.g., the Internet, a cellular link, a Wi-Fi link, etc.).

[0037] The database execution engine 1500 may include a query optimizer 110, such as a SQL optimizer and / or another type of optimizer, to receive at least one query from one or more client devices 102 and generate a corresponding query plan (which may be optimized) for execution by the query execution engine 120. The query optimizer 110 may receive a request such as a query and then form or propose an optimized query plan. The query plan (which may be optimized) may be represented as a so-called "query algebra" or "relational algebra" and may be in the form of a directed acyclic graph. The query plan may propose an optimal query plan with respect to, for example, the execution time of the overall query. In order to optimize the query, the query plan optimizer 110 may obtain one or more costs for different ways in which the query plan may be executed, and the cost may be a cost in terms of execution time at, for example, one or more databases 190. For example, the database execution engine (and / or in particular the query plan optimizer) may choose to optimize the inner join query of query 1 using an index join as shown in query 2.

[0038] The query plan compiler 112 may enable compilation of at least a portion of a query plan. The query plan compiler 112 may compile the optimized query algebra into operations, such as program code and / or any other type of commands, operations, objects, or instructions. The code may include precompiled code 114 (which may be precompiled and stored and then selected for certain operations in the query plan) and / or generated code 116 generated specifically for the execution of the query plan. For example, the query plan compiler 112 may select precompiled code 114 for a given operation as part of the optimization of the query plan, while for another operation in the query plan, the query plan compiler 112 may allow the compiler to generate code (i.e., generated code 116). The precompiled code 114 and generated code 116 represent code for executing the query plan, and the code may be provided to a query plan generator 118, which interfaces with a query execution engine 120.

[0039] The query optimizer 110 may optimize the query plan (which may include a pipeline of the indicated operators) by compiling and generating code. In addition, the query optimizer may generate a DAG that includes operators that may be configured into one or more pipelines. In addition, the query optimizer 110 may optimize the query plan to achieve pipelining during execution. The query execution engine 120 may receive compiled code from the query plan generator 118 to enable execution of the optimized query plan, although the query execution engine 120 may also receive code or other commands directly from a higher-level application or another source (such as one or more client devices 102). The precompiled code 114 and / or the generated code 116 may be provided to the plan execution engine 122 of the query execution engine 120. The plan execution engine 122 may then prepare the plan for execution, and the query plan may include the precompiled code 114 and / or the generated code 116. When the code of the query plan is ready to be executed during runtime, the query execution engine 120 may step through the code, perform some operations within the database execution engine 1500 and send some operations (or commands supporting operations, such as reads, writes, etc.) to be executed at one or more of the databases 190A-N.

[0040] As noted, the query execution engine 120 can be configured to handle different types of databases and corresponding persistence layers and / or tables therein. For example, one or more databases 190A-N may include at least one row-oriented database, in which case the insertion is performed by adding a row with a corresponding row identifier. Alternatively and / or additionally, one or more databases 190A-N may include one or more column storage databases that may use dictionary and compression techniques when inserting data into a table. In the case where the database layer includes multiple different types of databases, the query execution engine 120 may perform execution related to handling the differences between different types of databases (such as row-oriented databases and column storage databases). This may enable a reduction in processing at the database layer, for example, at each of the one or more databases 190A-N. In addition, the query execution engine 120 may perform other operations, including rule-based operations such as joins and projections, as well as filtering, grouping, multidimensional analysis, etc., to reduce the processing burden on the database layer. In this way, the query execution engine 120 may perform these and other complex operations, while one or more databases 190A-N may perform simpler operations to reduce the processing burden at one or more databases 190A-N.

[0041] As described above, the query execution engine 120 can run the generated code 116 generated for some query operations, while the precompiled code 114 can be run for other operations. In addition, the query execution engine 120 can combine the generated code 116 with the precompiled code 114 to optimize the execution of query-related operations. In addition, the query execution engine 120 can provide a plan execution framework that can handle (one or more) data blocks, pipelining, and state management during query execution. In addition, the query execution engine 120 can provide the ability to access table storage via an abstract interface to a table adapter, which can reduce the dependency on a specific type of storage / persistence layer (which can be implemented for use with different types of storage / persistence layers).

[0042] The database layer can provide distributed data storage, in which the processing and storage of data are distributed on multiple nodes, including, for example, the first database 190A, the second database 190B, the third database 190C, etc. Therefore, in order to store the database table 195 at one or more databases 190, the database execution engine 1500 can divide the database table 195 into multiple fragments by applying one or more of hash partitioning, round-robin partitioning, and range partitioning.

[0043] Figure 3A Depicted is a flow chart illustrating an example of a process 300 for query optimization including range index joins, according to some example implementations.

[0044] At 352, according to some embodiments, a query request including a join may be received for query plan generation, optimization, and execution. Further, the join may include a range between a first predicate of the join and a second predicate of the join. Further, the join may be selected from first and second table values ​​that match the predicates of the range. For example, the database execution engine 1500 may receive a request for a join, such as Query 2 described above, from a client device, such as the client device 102A.

[0045] At 354, the database execution engine may generate a query plan including an index join operator, wherein the index join operator includes a join processor configured to obtain a first value identifier corresponding to the first predicate, a second value identifier corresponding to the second predicate, and one or more intermediate value identifiers between the first value identifier and the second value identifier from the sorted dictionary when the query plan is executed. For example, in response to receiving the query at 352, the database execution engine (e.g., a query optimizer therein) may generate a query plan. In addition, the database execution engine 1500 may choose to optimize the query received at 352 using an index join. In this case, the index join operator may be configured with a "join processor" to handle a range of value IDs. As indicated in the example above, the join processor is capable of (at the time of query plan execution) obtaining a value ID for a predicate from the sorted dictionary 115 of the table S of query 2, such as a value ID for a range between Sb and Sc (e.g., a first value identifier corresponding to the first predicate Sb, a second value identifier corresponding to the second predicate Sb, and one or more intermediate value identifiers between the first value identifier and the second value identifier, such as value IDs 2 and 3 indicated in the example above). In addition, the index join operator is also configured to obtain the value ID (e.g., "Ta") of table T. Alternatively or additionally, the query plan may include other operators. Alternatively or additionally, the query plan may include other operators configured to use the pipeline as described above. After generating the query plan of the operator, the query plan of the operator can be executed by the database execution engine.

[0046] At 356, the database execution engine may execute the query plan including the index join operator, wherein the execution further includes obtaining the first value identifier, the second value identifier, and one or more intermediate value identifiers between the first value identifier and the second value identifier from the sorted dictionary, and executing the index join operator using the first value identifier, the second value identifier, and the one or more intermediate value identifiers to obtain a result set. For example, the query plan may include multiple operators such as Figure 1CWhen the index join operator 150 (which includes join processor logic) is executed as part of the query plan execution, the index join operator 150 obtains the first value identifier, the second value identifier, and one or more intermediate value identifiers (which are between the first value identifier and the second value identifier) ​​from the sorted dictionary, and the index join is performed using the first value identifier, the second value identifier, and the one or more intermediate value identifiers of table S (e.g., BETWEEN Sa, SB) to match with the value ID (e.g., Ta) of table t to obtain a result set.

[0047] At 358, a result set is provided as a response to the query request. For example, the database execution engine 1500 may provide a result set of an index join operator as a response to the query request received at 352 (eg, to a client device).

[0048] The database execution engine disclosed in some examples herein may be configured to perform one or more of: receiving a query, optimizing a query, generating a query plan, executing at least a portion of a query plan, and / or performing other database-related operations. Figure 2B A single database execution engine 1500 is depicted as being separate from the database, but a given database may also include its own database execution engine.

[0049] Figure 3B An example of a signaling graph of an index join operator configured to include a join processor is depicted. During query plan generation 310, the join operator is transformed 312A to include predicates 312B, index join relational algebra logic 312C, and a join processor 312D, the join processor 312D including logic configured to (at query plan execution) (1) obtain value IDs for predicate values ​​for a range of index joins, and (2) perform the index join multiple times using the obtained value IDs. Next, a query plan is constructed 314 such that it includes multiple executable operators, the multiple executable operators including an index join operator 316 (which includes the join processor logic). At query plan execution 380, the query plan is executed at 382, ​​and the index join operator is executed at 384. When executed, the index join operator obtains, for example, a first value identifier, a second value identifier, and one or more intermediate value identifiers (which are between the first value identifier and the second value identifier) ​​from the sorted dictionary when the query plan is executed, and the index join operator uses each obtained value ID (e.g., for table S) to perform an index join to obtain a match (e.g., table T with a match for index value "a") and form a result set, which is provided as a result set at 386.

[0050] Figure 4A block diagram illustrating an example of a computing system 500 consistent with implementations of the current subject matter is depicted. Referring to Figures 1-4, computing system 500 can implement database execution engine 1500 and / or any components therein.

[0051] like Figure 4 As shown, computing system 500 may include processor 510, memory 520, storage device 530, and input / output device 540. Processor 510, memory 520, storage device 530, and input / output device 540 may be interconnected via system bus 550. Processor 510 is capable of processing instructions for execution within computing system 500. Such executed instructions may implement, for example, one or more components of database execution engine 1500. In some embodiments of the current subject matter, processor 510 may be a single-threaded processor. Alternatively, processor 510 may be a multi-threaded processor. Processor 510 is capable of processing instructions stored in memory 520 and / or on storage device 530 to display graphical information for a user interface provided via input / output device 540.

[0052] Memory 520 is a computer-readable medium, such as volatile or non-volatile, that stores information within computing system 500. For example, memory 520 may store a data structure representing a configuration object database. Storage device 530 can provide persistent storage for computing system 500. Storage device 530 may be a floppy disk device, a hard disk device, an optical disk device, or a tape device or other suitable persistent storage device. Input / output device 540 provides input / output operations for computing system 500. In some embodiments of the current subject matter, input / output device 540 includes a keyboard and / or a pointing device. In various embodiments, input / output device 540 includes a display unit for displaying a graphical user interface.

[0053] According to some implementations of the current subject matter, the input / output device 540 can provide input / output operations for the network device. For example, the input / output device 540 can include an Ethernet port or other networking port to communicate with one or more wired and / or wireless networks (e.g., a local area network (LAN), a wide area network (WAN), the Internet).

[0054] In some embodiments of the current subject matter, the computing system 500 can be used to execute various interactive computer software applications that can be used to organize, analyze and / or store data in various formats (e.g., tables) (e.g., Microsoft and / or any other type of software). Alternatively, the computing system 500 can be used to execute any type of software application. These applications can be used to perform various functions, such as planning functions (e.g., generating, managing, editing electronic spreadsheet documents, word processing documents and / or any other objects, etc.), computing functions, communication functions, etc. The application can include various add-in functions, or can be an independent computing product and / or function. When activated within the application, the function can be used to generate a user interface provided via the input / output device 540. The user interface can be generated by the computing system 500 and presented to the user (e.g., on a computer screen monitor, etc.).

[0055] One or more aspects or features of the subject matter described herein can be implemented in digital electronic circuits, integrated circuits, specially designed ASICs, field programmable gate arrays (FPGAs) computer hardware, firmware, software, and / or combinations thereof. These various aspects or features can include implementations in one or more computer programs that are executable and / or interpretable on a programmable system, the programmable system including at least one programmable processor, the at least one programmable processor can be dedicated or general, coupled to receive data and instructions from a storage system, at least one input device, and at least one output device, and send data and instructions to the storage system, at least one input device, and at least one output device. A programmable system or computing system can include a client and a server. The client and the server are usually far away from each other, and usually interact through a communication network. The relationship between the client and the server is generated by means of a computer program that runs on a corresponding computer and has a client-server relationship with each other.

[0056] These computer programs (which may also be referred to as programs, software, software applications, applications, components or codes) include machine instructions for programmable processors and may be implemented in high-level procedural and / or object-oriented programming languages ​​and / or in assembly / machine languages. As used herein, the term "machine-readable medium" refers to any computer program product, apparatus and / or device for providing machine instructions and / or data to a programmable processor, such as, for example, a disk, an optical disk, a memory and a programmable logic device (PLD), including a machine-readable medium that receives machine instructions as a machine-readable signal. The term "machine-readable signal" refers to any signal for providing machine instructions and / or data to a programmable processor. A machine-readable medium may store such machine instructions non-temporarily, such as, for example, a non-temporary solid-state memory or a magnetic hard drive or any equivalent storage medium. A machine-readable medium may alternatively or additionally store such machine instructions in a transient manner, for example, a processor cache or other random access memory associated with one or more physical processor cores.

[0057] To provide interaction with a user, one or more aspects or features of the subject matter described herein may be implemented on a computer having a display device for displaying information to the user, such as, for example, a cathode ray tube (CRT) or a liquid crystal display (LCD) or a light emitting diode (LED) monitor, and a keyboard and pointing device (such as, for example, a mouse or trackball) through which the user can provide input to the computer. Other kinds of devices may also be used to provide interaction with the user. For example, the feedback provided to the user may be any form of sensory feedback, such as visual feedback, auditory feedback, or tactile feedback; and input from the user may be received in any form, including sound, voice, or tactile input. Other possible input devices include touch screens or other touch-sensitive devices, such as single-point or multi-point resistive or capacitive tracking pads, voice recognition hardware and software, optical scanners, optical pointers, digital image capture devices and associated interpretation software, and the like.

[0058] In the above description and claims, phrases such as "at least one of..." or "one or more of..." may appear, followed by a linked list of elements or features. The term "and / or" may also appear in a list of two or more elements or features. Unless otherwise implicitly or explicitly contradicted by the context in which it is used, such phrases are intended to mean any one of the listed elements or features alone, or to mean any one of the listed elements or features in combination with any one of the other listed elements or features. For example, the phrases "at least one of A and B; "one or more of A and B; " and "A and / or B" are each intended to mean "A alone, B alone, or A and B together". A similar interpretation is also intended for a list including three or more items. For example, the phrases "at least one of A, B, and C; "one or more of A, B, and C; " and "A, B, and / or C" are each intended to mean "A alone, B alone, C alone, A and B together, A and C together, B and C together, or A and B and C together". Use of the term "based on" above and in the claims is intended to mean "based, at least in part, on" such that non-recited features or elements are also allowable.

[0059] In view of the embodiments of the above-mentioned subject matter, the present application discloses the following list of examples, wherein one feature of a separate example or a combination of more than one feature of the example and optionally in combination with one or more features of one or more additional examples are additional examples that also fall within the disclosure of the present application:

[0060] Example 1: A system comprising:

[0061] at least one data processor; and

[0062] at least one memory storing instructions that, when executed by the at least one data processor, cause operations comprising:

[0063] receiving a query request including a join, wherein the join includes a range between a first predicate of the join and a second predicate of the join;

[0064] generating a query plan including an index join operator, wherein the index join operator includes a join processor configured to obtain, when the query plan is executed, from the sorted dictionary a first value identifier corresponding to the first predicate, a second value identifier corresponding to the second predicate, and one or more intermediate value identifiers between the first value identifier and the second value identifier;

[0065] executing a query plan including an index join operator, wherein the executing further comprises obtaining a first value identifier, a second value identifier, and one or more intermediate value identifiers between the first value identifier and the second value identifier from a sorted dictionary, and executing the index join operator using the first value identifier, the second value identifier, and the one or more intermediate value identifiers to obtain a result set; and

[0066] The query request is responded to by providing the result set.

[0067] Example 2: The system of Example 1, wherein the query request is received at a database execution engine.

[0068] Example 3: The system of any of Examples 1-2, wherein the join can select one or more values ​​from the first table and the second table based on a range between a first predicate of the join and a second predicate of the join.

[0069] Example 4: The system of any one of Examples 1-3, wherein the query plan is generated by a database execution engine in response to a received query request.

[0070] Example 5: The system of any one of Examples 1-4, wherein the join processor is configured to include one or more instructions to perform retrieval from the sorted dictionary.

[0071] Example 6: The system of any one of Examples 1-5, wherein the query plan includes a plurality of operators including the index join operator.

[0072] Example 7: The system of any of Examples 1-6, wherein the plurality of operators are configured to be executed by a database execution engine using at least one pipeline.

[0073] Example 8: The system of any of Examples 1-7, wherein the execution of the query plan including the index join operator is performed at least by a database execution engine.

[0074] Example 9: The system of any one of Examples 1-8, wherein the index join operator comprises the join processor, wherein the join processor obtains the first value identifier, the second value identifier, and the one or more intermediate value identifiers from the sorted dictionary.

[0075] Example 10: The system of any one of Examples 1-9, wherein the index join operator comprises the join processor, wherein the join processor performs the index join operator using a first value identifier, a second value identifier, and one or more intermediate value identifiers.

[0076] Example 11: A method comprising:

[0077] receiving a query request including a join, wherein the join includes a range between a first predicate of the join and a second predicate of the join;

[0078] generating a query plan including an index join operator, wherein the index join operator includes a join processor configured to obtain, when the query plan is executed, from the sorted dictionary a first value identifier corresponding to the first predicate, a second value identifier corresponding to the second predicate, and one or more intermediate value identifiers between the first value identifier and the second value identifier;

[0079] executing the query plan including the index join operator, wherein the executing further comprises obtaining the first value identifier, the second value identifier, and one or more intermediate value identifiers between the first value identifier and the second value identifier from the sorted dictionary, and executing the index join operator using the first value identifier, the second value identifier, and the one or more intermediate value identifiers to obtain a result set; and

[0080] The query request is responded to by providing the result set.

[0081] Example 12: The method of Example 11, wherein the query request is received at a database execution engine.

[0082] Example 13: The method of any of Examples 11-12, wherein the join can select one or more values ​​from the first table and the second table based on a range between a first predicate of the join and a second predicate of the join.

[0083] Example 14: The method according to any one of Examples 11-13, wherein the query plan is generated by a database execution engine in response to a received query request.

[0084] Example 15: The method of any one of Examples 11-14, wherein the join processor is configured to include one or more instructions to perform retrieval from the sorted dictionary.

[0085] Example 16: The method of any one of Examples 11-15, wherein the query plan includes a plurality of operators including an index join operator.

[0086] Example 17: The method of any one of Examples 11-16, wherein the plurality of operators are configured to be executed by a database execution engine using at least one pipeline.

[0087] Example 18: The method of any one of Examples 11-17, wherein the execution of the query plan including the index join operator is performed at least by a database execution engine.

[0088] Example 19: The method of any one of Examples 11-18, wherein the index join operator comprises the join processor, wherein the join processor obtains the first value identifier, the second value identifier, and one or more intermediate value identifiers from the sorted dictionary.

[0089] Example 20: A non-transitory computer readable medium comprising instructions that, when executed by at least one data processor, cause operations comprising:

[0090] receiving a query request including a join, wherein the join includes a range between a first predicate of the join and a second predicate of the join;

[0091] generating a query plan including an index join operator, wherein the index join operator includes a join processor configured to obtain, when the query plan is executed, from the sorted dictionary a first value identifier corresponding to the first predicate, a second value identifier corresponding to the second predicate, and one or more intermediate value identifiers between the first value identifier and the second value identifier;

[0092] executing the query plan including the index join operator, wherein the executing further comprises obtaining the first value identifier, the second value identifier, and one or more intermediate value identifiers between the first value identifier and the second value identifier from the sorted dictionary, and executing the index join operator using the first value identifier, the second value identifier, and the one or more intermediate value identifiers to obtain a result set; and

[0093] The query request is responded to by providing the result set.

[0094] Depending on the desired configuration, the subject matter described herein may be embodied in systems, devices, methods and / or articles. The embodiments set forth in the foregoing description do not represent all embodiments consistent with the subject matter described herein. On the contrary, they are merely some examples consistent with aspects related to the described subject matter. Although some variations have been described in detail above, other modifications or additions are possible. In particular, in addition to those set forth herein, additional features and / or variations may also be provided. For example, the above-described embodiments may relate to various combinations and sub-combinations of the disclosed features and / or combinations and sub-combinations of several additional features disclosed above. In addition, the logical flows depicted in the accompanying drawings and / or described herein do not necessarily require the specific order shown or the sequential order to achieve the desired results. Other embodiments may be within the scope of the appended claims.

Claims

1. A system comprising: at least one data processor; as well as at least one memory storing instructions that, when executed by the at least one data processor, cause operations comprising: receiving a query request including a join, wherein the join includes a range between a first predicate of the join and a second predicate of the join; generating a query plan including an index join operator, wherein the index join operator includes a join processor configured to obtain, when the query plan is executed, from the sorted dictionary a first value identifier corresponding to the first predicate, a second value identifier corresponding to the second predicate, and one or more intermediate value identifiers between the first value identifier and the second value identifier; executing a query plan including an index join operator, wherein the executing further comprises obtaining a first value identifier, a second value identifier, and one or more intermediate value identifiers between the first value identifier and the second value identifier from the sorted dictionary, and executing the index join operator using the first value identifier, the second value identifier, and the one or more intermediate value identifiers to obtain a result set; and The query request is responded to by providing the result set.

2. The system according to claim 1, wherein: The query request is received at a database execution engine. 3 . The system of claim 1 , wherein the join can select one or more values ​​from the first table and the second table based on a range between a first predicate of the join and a second predicate of the join.

4. The system according to claim 1, wherein: The query plan is generated by the database execution engine in response to the received query request.

5. The system according to claim 1, wherein: The join processor is configured to include one or more instructions to perform a retrieval from the sorted dictionary.

6. The system according to claim 1, wherein: The query plan includes a plurality of operators including the index join operator.

7. The system according to claim 6, wherein: The plurality of operators are configured to be executed by a database execution engine using at least one pipeline.

8. The system of claim 1, wherein the execution of the query plan including the index join operator is performed by at least a database execution engine.

9. The system of claim 1, wherein the index join operator comprises the join processor, wherein the join processor obtains the first value identifier, the second value identifier, and the one or more intermediate value identifiers from the sorted dictionary.

10. The system of claim 1, wherein the index join operator comprises the join processor, wherein the join processor performs the index join operator using the first value identifier, the second value identifier, and the one or more intermediate value identifiers.

11. A method comprising: receiving a query request including a join, wherein the join includes a range between a first predicate of the join and a second predicate of the join; generating a query plan including an index join operator, wherein the index join operator includes a join processor configured to obtain, when the query plan is executed, from the sorted dictionary a first value identifier corresponding to the first predicate, a second value identifier corresponding to the second predicate, and one or more intermediate value identifiers between the first value identifier and the second value identifier; executing a query plan including an index join operator, wherein the executing further comprises obtaining a first value identifier, a second value identifier, and one or more intermediate value identifiers between the first value identifier and the second value identifier from the sorted dictionary, and executing the index join operator using the first value identifier, the second value identifier, and the one or more intermediate value identifiers to obtain a result set; as well as The query request is responded to by providing the result set.

12. The method according to claim 11, wherein: The query request is received at a database execution engine.

13. The method according to claim 11, wherein: The join may select one or more values ​​from the first table and the second table based on a range between a first predicate of the join and a second predicate of the join.

14. The method according to claim 11, wherein: The query plan is generated by a database execution engine in response to the received query request.

15. The method according to claim 11, wherein: The join processor is configured to include one or more instructions to perform a retrieval from the sorted dictionary.

16. The method according to claim 11, wherein: The query plan includes a plurality of operators including the index join operator.

17. The method according to claim 16, wherein: The plurality of operators are configured to be executed by a database execution engine using at least one pipeline.

18. The method according to claim 11, wherein: The execution of the query plan including the index join operator is performed by at least a database execution engine.

19. The method according to claim 11, wherein: The index join operator includes the join processor, wherein the join processor obtains the first value identifier, the second value identifier, and the one or more intermediate value identifiers from the sorted dictionary.

20. A non-transitory computer-readable medium comprising instructions that, when executed by at least one data processor, cause operations comprising: receiving a query request including a join, wherein the join includes a range between a first predicate of the join and a second predicate of the join; generating a query plan including an index join operator, wherein the index join operator includes a join processor configured to obtain, when the query plan is executed, from the sorted dictionary a first value identifier corresponding to the first predicate, a second value identifier corresponding to the second predicate, and one or more intermediate value identifiers between the first value identifier and the second value identifier; executing a query plan including an index join operator, wherein the executing further comprises obtaining a first value identifier, a second value identifier, and one or more intermediate value identifiers between the first value identifier and the second value identifier from the sorted dictionary, and executing the index join operator using the first value identifier, the second value identifier, and the one or more intermediate value identifiers to obtain a result set; as well as The query request is responded to by providing the result set.

Images