Table scan predicate with integrated semi-join filter

Abstract

A system and method for implementing a table scan predicate with an integrated semi-join filter is provided. The method includes receiving a query including a request to connect first data from a first dimension table and second data from a second dimension table with fact data from a fact table. The method includes applying a first dynamic predicate to the first data by collecting the first data and screening the first column based on a first expression of the query. The method further includes applying a second dynamic predicate to the second data by collecting the second data and screening the second column based on a second expression of the query. The method further includes executing the query by scanning the fact table based at least on the query, the first screened columns, and the second screened columns.

Description

Technical Field

[0001] The subject matter described herein relates generally to database management, and more particularly to table scan predicates with integrated semi-join filters for semi-join reduction in scenarios involving multiple input sources. Background Art

[0002] The database may be configured to store a plurality of electronic data records. These data records may be organized into various database objects according to a database schema, including, for example, one or more database tables. The database is coupled to a database management system (DBMS), which may be configured to support various database operations for accessing the data records stored in the database. These database operations may include, for example, structured query language (SQL) queries, etc. Summary of the invention

[0003] Systems, methods, and articles of manufacture (including computer program products) for table scan predicates with integrated semi-join filters (filters) for use in semi-join reduction are provided. In one aspect, a system is provided. The system may include at least one data processor and at least one memory. The at least one memory may store instructions that, when executed by the at least one data processor, result in operations. The operations may include receiving a query that includes a request to join first data from a first dimension table and second data from a second dimension table with fact data from a fact table. The query also includes a first predicate that includes a first expression associated with the first data and a first reference to a first column associated with the fact data from the fact table, a second predicate that includes a second expression associated with the second data and a second reference to a second column associated with the fact data from the fact table, and a third predicate for a table scan of the fact table. The operations also include applying a first dynamic predicate associated with the first dimension table to at least the first data by collecting the first data based on at least a first expression and filtering the first column based on a first value identifier, the first value identifier being mapped to at least one data vector representing the collected first data. The first dynamic predicate includes a transformation of the first predicate for executing the query. The operations also include applying a second dynamic predicate associated with the second dimension table to at least the second data by collecting the second data based on at least the second expression and filtering the second column based on a second value identifier mapped to at least one data vector representing the collected second data. The second dynamic predicate includes a transformation of the second predicate for performing the query. The operations also include performing the query by scanning the fact table based on at least the first predicate, the second predicate, the first filtered column, and the second filtered column.

[0004] In another aspect, a computer-implemented method may also be provided. The method includes receiving a query including a request to join first data from a first dimension table and second data from a second dimension table with fact data from a fact table. The query also includes a first predicate including a first expression associated with the first data and a first reference to a first column associated with the fact data from the fact table, a second predicate including a second expression associated with the second data and a second reference to a second column associated with the fact data from the fact table, and a third predicate for a table scan of the fact table. The method also includes applying a first dynamic predicate associated with the first dimension table to at least the first data by at least collecting the first data based on the first expression and filtering the first column based on a first value identifier mapped to at least one data vector representing the collected first data. The first dynamic predicate includes a transformation of the first predicate for executing the query. The method also includes applying a second dynamic predicate associated with the second dimension table to at least the second data by at least collecting the second data based on the second expression and filtering the second column based on a second value identifier mapped to at least one data vector representing the collected second data. The second dynamic predicate includes a transformation of the second predicate for executing the query. The method also includes executing the query by scanning the fact table based on at least the first predicate, the second predicate, the first filtered column, and the second filtered column.

[0005] In another aspect, a computer program product is provided, comprising a non-transitory computer-readable medium storing instructions. The instructions may cause operations that may be performed by at least one data processor. The operations may include receiving a query including a request to join first data from a first dimension table and second data from a second dimension table with fact data from a fact table. The query also includes a first predicate including a first expression associated with the first data and a first reference to a first column associated with the fact data from the fact table, a second predicate including a second expression associated with the second data and a second reference to a second column associated with the fact data from the fact table, and a third predicate for a table scan of the fact table. The operations also include applying a first dynamic predicate associated with the first dimension table to at least the first data by at least collecting the first data based on the first expression and filtering the first column based on a first value identifier, the first value identifier being mapped to at least one data vector representing the collected first data. The first dynamic predicate includes a transformation of the first predicate for executing the query. The operations also include applying a second dynamic predicate associated with the second dimension table to at least the second data by at least collecting the second data based on the second expression and filtering the second column based on a second value identifier mapped to at least one data vector representing the collected second data. The second dynamic predicate includes a transformation of the second predicate for executing the query. The operations also include executing the query by at least scanning the fact table based on the first predicate, the second predicate, the first filtered column, and the second filtered column.

[0006] In some variations of the methods, systems, and non-transitory computer-readable media, one or more of the following features may optionally be included in any feasible combination. The operations and / or methods include transforming a first predicate into a first dynamic predicate by at least transforming the first predicate into a first child node of a table scan, and transforming a second predicate into a second dynamic predicate by at least transforming the second predicate into a second child node of the table scan. The table scan is configured to perform a scan of a fact table. The transforming also includes including the first predicate as a child node of the table scan that is in conjunction with the first dynamic predicate, and including the second predicate as another child node of the table scan that is in conjunction with the second dynamic predicate.

[0007] In some variations, collecting the first data includes parallelizing the first data. Applying the first dynamic predicate also includes merging the first data after collecting the first data. Collecting the second data includes parallelizing the second data. Applying the second dynamic predicate also includes merging the second data after collecting the second data.

[0008] In some variations, merging the first data includes uniqueizing the first data, and merging the second data includes uniqueizing the second data. Merging the first data includes uniqueizing the first data. Merging the second data includes uniqueizing the second data.

[0009] In some variations, uniqueening the first data includes removing duplicate values ​​from at least one data vector representing the collected first data. Uniqueening the second data includes removing duplicate values ​​from at least one data vector representing the collected second data.

[0010] In some variations, filtering the first column includes removing at least one row from the fact table that does not correspond to the first value identifier, and filtering the second column includes removing at least one row from the fact table that does not correspond to the second value identifier.

[0011] In some variations, the first dynamic predicate and the second dynamic predicate are implemented as a semi-join reduction to reduce at least the number of rows in the fact table for scanning the fact table.

[0012] Implementations of the current subject matter may include methods consistent with the descriptions provided herein and articles including tangibly embodied machine-readable media, which are operable to cause one or more machines (e.g., computers, etc.) to cause operations that implement one or more of the described features. Similarly, a computer system is also described, which 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 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 a direct connection between one or more computing systems in the multiple computing systems, etc.

[0013] 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 clear from the specification and the accompanying drawings and the claims. Although certain features of the subject matter currently disclosed are described for illustrative purposes, it should be readily understood that such features are not intended to be restrictive. The claims attached to this disclosure are intended to limit the scope of the protected subject matter. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0015] Figure 1 depicts an example logical representation of a query according to some example embodiments;

[0016] Figure 2 depicts example tables for performing queries according to some example embodiments;

[0017] Figure 3 depicts example transformations of logical representations of queries according to some example embodiments;

[0018] Figure 4 depicts example transformations of logical representations of queries according to some example embodiments;

[0019] Figure 5 depicts an algebraic representation of an example transformation of a query including a dynamic predicate according to some example embodiments;

[0020] Figure 6 depicts an algebraic representation of an example transformation of a query including a dynamic predicate according to some example embodiments;

[0021] Figure 7 depicts a flowchart illustrating a process for executing a query according to some example embodiments; and

[0022] Figure 8 Another block diagram of a system is depicted, according to some example embodiments.

[0023] When applicable, similar reference numerals indicate similar structures, features, or elements. DETAILED DESCRIPTION

[0024] Database management systems and operations performed on data managed by database management systems have become increasingly complex. For example, a database management system (or simply a database) can support relatively complex online analytical processing (OLAP) that can perform multidimensional analysis to more direct transaction-based online transaction processing (OLTP). In addition, a database can be configured as a row storage database or a column storage database, each of which can have certain aspects of queries and other operations at the database. For example, a database can encode data using a dictionary, while some databases may not encode data using a dictionary. In addition to these various database layer differences, queries performed at the database may include complex sequences of operations to generate corresponding responses. In order to implement complex sequences, a query execution plan (or simply a query plan) can be implemented. A query plan represents a series of operations, such as instructions, commands, etc., for accessing data in a database. The database may also include a query plan optimizer to determine an effective way to execute a query plan.

[0025] Join methods can be used for query processing and optimization of distributed databases. When processing and / or optimizing queries in distributed databases, data needs to be transferred between databases located at different sites. Depending on the amount of data that needs to be transferred, this join operation can be computationally expensive. Therefore, query optimization can be used to minimize the amount of data that is transferred between sites and processed during query execution.

[0026] In order to optimize the execution of queries, and to reduce the amount of data scanned and / or transferred between sites during a join operation, semi-join reduction techniques can be used. For example, semi-joins can be used to process joins between tables more quickly. In particular, a left semi-join can be used to return a row from the left table of the join (e.g., the left side of a predicate) at most once. In other words, even if the right table of the join (e.g., the right side of a predicate) contains two matches for a row in the left table, only one copy of the row from the left table will be returned. Thus, processing a semi-join can include searching the right table for a match in the left table, and for each match that is not a duplicate, returning only a single row from the left table.

[0027] For semi-joins involving tables stored at different sites (e.g., hosts), data transfer costs can be reduced by first sending only the projected join columns to the other site, where they are joined with the second relation table. All matching tuples from the second relation are then sent back to the first site to compute the final join result.

[0028] As an example, a query may include a request to join relationship tables S and T (e.g., ). Semijoin reduction techniques can eliminate empty hanging tuples from table S to process requests more efficiently and / or quickly with fewer computing resources. Empty hanging tuples can include tuples (e.g., rows in a table) that do not match any tuple in table T, such as tuples in table S. In relational algebra, this is captured by the following law: Operations in this context (e.g., table T left semi join table S) is an operation to eliminate each tuple in table S that does not have a matching (e.g., corresponding) row in table T. Depending on the query predicates, query criteria, and / or the storage format of the data in the tables (e.g., dictionary compressed column format or other), this operation can be computationally cheap and can significantly reduce the number of tuples from table T that are considered during the execution of a request to join tables S and T (e.g., a join operation).

[0029] In addition, some systems for semi-join reduction are limited to a single input source, which limits the efficiency of the system when processing queries. In contrast, the database management system described herein can adapt to any number (e.g., an arbitrary number) of input source relations. Consistent with the embodiments of the current subject matter, the database management system described herein can maximize the efficiency of semi-join reduction to help process queries, such as queries that include join operations with multiple input source relations. To this end, the database management system can employ multiple filters corresponding to each input source. In other words, the database management system can generate and use dynamic predicates (e.g., table scan predicates with integrated semi-join filters) for each input source. This corresponds to a left semi-join, which allows tuples of arbitrary relations on the right-hand side (e.g., relative to the input source tables S1...Sn) to follow the following relationship: t[J 1 ]=s 1 [J 1 ]∧…∧t[J n ]=s n [J n ]}, where t[J i ]=s i [J i ] represents a tuple-by-tuple comparison of rows at their corresponding join attributes Ji. This means that S1, ..., Sn act as filters to reduce the number of rows in the join operation. By applying the multi-source semi-join described herein, this relation is transformed into

[0030] Dynamic predicates for each input source can be injected into existing predicates for table scans, such as by using conjunctions to eliminate or reduce empty hanging tuples, such as in table T. The described configuration can direct analytical query operations to relationships organized in a star schema—a large fact table (e.g., table T) and smaller dimension tables (e.g., tables S1, S2, etc.). Therefore, the database system described herein can significantly reduce the number of rows of the fact table (e.g., table T) considered during the join. This takes advantage of the probability and selectivity of using multi-dimensional tables to improve query execution performance.

[0031] The configurations described herein may additionally and / or alternatively allow column storage specific optimizations, such as for value identifier (e.g., valueid) processing. In some examples, for dictionary compressed columns, the value identifier is an index in the dictionary, thereby allowing efficient integer representation and processing of table data and predicate values. In addition, the dynamic predicates described herein may include at least one value list (e.g., ValueList) operator that takes into account values ​​that are unknown at compile time but collected at query execution time. According to some embodiments, a separate dynamic predicate may be created and associated for each input source, which may include a dimension table. For example, a dynamic predicate may be applied to data stored in a corresponding dimension table by collecting values ​​from at least a corresponding expression included in the query and by filtering columns in a fact table based on value identifiers corresponding to the collected values. Therefore, the database management system can pre-screen the data scanned during query execution. Therefore, the database management system described herein can improve query processing speed and efficiency.

[0032] Figure 1 An example logical representation 200 of a query according to some example embodiments is depicted. In some embodiments, the database management system 100 (see Figure 8 ) may include an execution engine 150 that may be configured to receive a query, generate a query plan (including, for example, a query algebra) that may be depicted by the logical representation 200, optimize the query plan, and / or generate executable code, as discussed herein and with respect to Figure 8 The database management system 100 may also include a query optimizer 110 and / or a query execution engine 112 that performs one or more operations of a query, such as before or during execution of a query execution plan. The query optimizer 110 and / or the query execution engine 112 (which may include precompiled code and / or generated executable code) may define an application that is part of or separate from the execution engine 150 that processes queries.

[0033] refer to Figure 8, the database management system 100 may include at least one table stored on a database (e.g., databases 190A-N). The database management system 100 may receive a query from a client device 102A, 102B for execution. The query may include a request to join data from at least one table (such as a dimension table or an input source) with data from a fact table. The at least one table (such as a dimension table or an input source) may include one, two, three, four, five, or more tables. The at least one table (such as a dimension table or an input source) may include a first dimension table, a second dimension table, a third dimension table, and the like. The data from the at least one table (such as a dimension table or an input source) may be stored on the same or different databases (such as databases 190A-N). The data from the fact table may be stored on a database (which may be the same or different from the database storing the data from the at least one table (such as a dimension table or an input source)), such as databases 190A-190N.

[0034] As described above, a query may include a request to connect first data from a first dimension table and second data from a second dimension table with fact data from a fact table. The data contained in the dimension tables (e.g., the first dimension table and the second dimension table) may include attributes or other information about the fact data stored in the fact table. The query may also include at least one predicate for processing the request, such as one, two, three, four or more predicates. As used herein, the predicates of a query define the logical conditions or expressions included in the query. A predicate may be an expression (e.g., a Boolean expression) applied to a row in a table, a condition, a keyword specifying the relationship between two expressions, etc. In other words, a predicate may determine which rows of a table are relevant to a particular query, such as being relevant to a request to connect the first data and the second data with the fact data. In addition, as used herein, a dynamic predicate is a transformation of a predicate that includes a filter.

[0035] The query may include at least a first predicate, a second predicate, and a third predicate, among other predicates. The first predicate may include any arbitrary predicate, such as a predicate for a table scan of at least one of the first dimension table, the second dimension table, and / or the fact table. The second predicate may also include any arbitrary predicate, such as a predicate for a table scan of at least one of the first dimension table, the second dimension table, and / or the fact table. The third predicate may include an expression, such as a query expression, associated with first data from the first dimension table, second data from the second dimension table, and / or fact data from the fact table. Additionally and / or alternatively, the third predicate may include a reference to a column associated with first data from the first dimension table, second data from the second dimension table, and / or fact data from the fact table.

[0036] Example queries are included below:

[0037] SELECT *

[0038] FROM

[0039] ((T JOIN S1 ON T.col1=S1.exp)

[0040] JOIN S2 ON T.col2=S2.exp))

[0041] WHERE pred(T);

[0042] The example query includes a join operator. Here, the join operator includes combining the fact data from the fact table (e.g., table T) with the fact data from the first dimension table (e.g., table S). 1 ) and the first data from the second dimension table (e.g., table S 2 ) of the second data connection. Although the example query describes Figure 1-6 , but database management system 100 can use the same and / or similar described methods and / or techniques to process other queries, such as queries that include a join operator. For example, other queries with a join operator that include joining a fact table (e.g., table T) with a table from one (e.g., table S) can be processed by database management system 100 using the same and / or similar described methods and / or techniques. 1 ), two (for example, Table S 2 ), three (for example, Table S 3 ) and other dimension tables.

[0043] like Figure 1 As shown, Figure 1 A logical representation 200 of a query is depicted, and referring to the example query above, the query at 204A includes transferring data from the first dimension table S 1 (For example, Figure 2 The first data of the first dimension table 250 shown in FIG. 2 is compared with the first data of the first dimension table 250 from the fact table T (eg, Figure 2 252 shown in FIG. 254 ) to the fact table 252 (e.g., SELECT * FROM query AS S 1 JOIN T) (such as the first request). At 204A, the join may include a join operator, such as an inner join, an outer join, etc. At 204A, the join may additionally and / or alternatively be a hash join (see Figure 4 At 202, the first dimension table S may be used in the projection. 1 The request to join the first data of the query table T with the fact data from the fact table T. At 206A, the query plan for executing the query may be executed via a first sub-plan S 1 Provided from the first dimension table S1 The first data and / or the fact data from the fact table T.

[0044] Additionally and / or alternatively, at 204B, the query includes adding a second dimension table S2 (eg, Figure 2 251) with the fact data from the fact table T (e.g., fact table 252) (e.g., SELECT * FROM query AS S 2 JOIN T) (such as the second request). At 204B, the join may include a join operator, such as an inner join, an outer join, etc. At 204B, the join may additionally and / or alternatively include a hash join (see Figure 4 ). The projection at 202 may be used to convert the second dimension table S 2 The second dimension data from the second dimension table S may be provided via a second sub-plan S2 of the query plan for executing the query. 2 The second data of and / or fact data from fact table T. Consistent with the embodiments described herein, the first request and the second request can be part of the same request or different requests within the query. In addition, in some embodiments, the query includes an additional request to join data from additional dimension tables with fact table 252.

[0045] An example query includes a first predicate, a second predicate, and a third predicate. The first predicate may include: "ON T.col 1 =S 1 .exp" Therefore, the first predicate can include the first dimension table S 1 The associated expression (e.g., S 1 .exp). The first predicate may also include a query on a column associated with the fact data from the fact table T (e.g., T.col 1 ). In some embodiments, the column is used to connect the first dimension table S 1 and the join columns of the fact table T. Thus, such as at 204A, the first predicate (in this case, part of the ON clause) can be used to determine the first dimension table S 1 and / or which rows in the fact table T are relevant for the join operation.

[0046] The second predicate may include: "ON T.col 2 =S 2 .exp" Therefore, the second predicate can include the second dimension table S 2 The associated expression (e.g., S 2.exp). The second predicate may also include a column associated with the fact data from the fact table T (e.g., T.col 2 ). In some embodiments, the column is used to connect the second dimension table S 2 and the join columns of the fact table T. Thus, such as at 204B, the second predicate (in this case part of the ON clause) can be used to determine the second dimension table S 2 and / or which rows in the fact table T are relevant for the join operation.

[0047] Referring again to the example query, the third predicate in this example is in the WHERE clause. For example, the third predicate includes "pred(T)", which is any predicate on the fact table T (e.g., fact table 252), such as an expression, a condition, etc. The third predicate (in this case, part of the WHERE clause of the SELECT statement) can be used to determine the first dimension table S 1 , the second dimension table S 2 and / or which rows in the fact table T are relevant to the execution of the query. The third predicate can be used during a table scan of the fact table T. For example, Figure 1 As shown, the table scan at 208 may be based on at least the third predicate of the query. The table scan may be used to scan the table T for rows related to the join operation of the query based on at least the third predicate.

[0048] Figure 3 Depicted are some example embodiments Figure 2 300 of the logical representation 200 of the query shown in . Database management system 100 can employ a semi-join reduction technique using dynamic predicates (corresponding to each input source or dimension table) consistent with embodiments of the current subject matter to maximize the efficiency of processing queries (such as the example query that includes a join), such as at 204A and / or 204B. To implement a semi-join reduction technique consistent with embodiments of the current subject matter, logical representation 200 has been transformed to include a semi-join at 302, a first shared subplan at 207A, and a second shared subplan at 207B. Including a semi-join at 302 allows for semi-join reduction to be implemented by database management system 100.

[0049] The first shared sub-plan at 207A is an extension of the sub-plan at 206A and is a view in front of the first sub-plan, which includes the semantics of all data flowing from the first sub-plan at 206A into the build side 230 and / or the exploration side 232 of the query plan. The second shared sub-plan at 207B may also be an extension of the second sub-plan at 206B and is a view in front of the second sub-plan, which includes the semantics of all data flowing from the second sub-plan at 206B into the build side 230 and / or the exploration side 232 of the query plan. On the build side 230, one or more hash tables may be used as auxiliary structures to facilitate the first dimension table S on the exploration side 232. 1 and the fact table T and the second dimension table S 2 and fact table T. Because the exploration side 232 is computationally more expensive than the construction side 230, the database management system 100 applies semi-join reductions consistent with embodiments of the current subject matter to filter and / or reduce the number of rows scanned as part of query processing.

[0050] refer to Figure 3 , the semi-join at 302 may include a left semi-join, a right semi-join, etc. The semi-join may be based on the first predicate and / or the second predicate. For example, as described herein with respect to the join at 204A, the first predicate may include a first dimension table S 1 The first expression associated with it (e.g., S 1 .exp). The first predicate may also include a first column associated with the fact data from the fact table T (eg, T.col 1 ). In some embodiments, the first column is a join column used in the semi-join at 302. Thus, such as at 302, the first predicate (in this case, part of the ON clause) can be used to determine the first dimension table S 1 and / or which rows in the fact table T are relevant for the semi-join operation.

[0051] Similarly, as described herein with respect to the join at 204B, the second predicate may include a second dimension table S 2 The associated second expression (e.g., S 2 .exp). The second predicate may also include a second column associated with the fact data from the fact table T (e.g., T.col 2 ). In some embodiments, the second column is a join column used in the semi-join at 302. Thus, such as at 302, the second predicate (in this case, part of the ON clause) can be used to determine the second dimension table S 2 and / or which rows in the fact table T are relevant for the semi-join operation.

[0052] Figure 4 Depicted Figure 1 4 is a second example transformation 400 of the logical representation 200 of the query shown. According to some example embodiments, the second example transformation 400 is a further transformation of the logical representation 200 based on the first example transformation 300. Figure 4 As shown, the table scan at 208 and the semi-join at 302 have been combined into a table scan semi-join operator at 402. To generate the combined table scan semi-join operator at 402, the first predicate (e.g., S 1 .exp=T.col 1 ) and a second predicate (e.g., S 2 .exp=T.col 2 ) can each be transformed into a dynamic predicate (e.g., a table scan predicate with an integrated semi-join filter) for use in executing the query, as described in more detail below. In other words, the first predicate can be transformed into a first dynamic predicate, and the second predicate can be transformed into a second dynamic predicate, and so on.

[0053] Figure 5 An algebraic representation 500 of an example transformation of a query according to some example embodiments is depicted, further illustrating a transformation of a first predicate to a first dynamic predicate and a transformation of a second predicate to a second dynamic predicate. The algebraic representation 500 corresponds to the example transformation 400. For example, the first subplan at 506A, the first shared subplan at 507A, the first hash join at 504, the second subplan at 506B, the second shared subplan at 507B, and the second hash join at 504B correspond to the first subplan at 206A, the first shared subplan at 207A, the first hash join at 204A, the second subplan 206B, the second shared subplan 207B, and the second hash join at 204B, respectively. In other words, the first subplan at 506A, the first shared subplan at 507A, the hash connection at 504A, the second subplan 506B, the second shared subplan 507B, and the second hash connection at 504B are respectively algebraic representations of the first subplan at 206A, the first shared subplan at 207A, the first hash connection at 204A, the second subplan 206B, the second shared subplan 207B, and the second hash connection at 204B. Therefore, the first subplan at 506A, the first shared subplan at 507A, the hash connection at 504A, the second subplan 506B, the second shared subplan 507B, and the second hash connection at 504B have the same properties and / or components as the first subplan at 206A, the first shared subplan at 207A, the first hash connection at 204A, the second subplan 206B, the second shared subplan 207B, and the second hash connection at 204B, respectively.

[0054] refer to Figure 5 , 402( Figure 4 The table scan semi-join at 502 is represented algebraically by the table scan + (e.g., TableScan+) at 502. Figure 5 As further shown in , the first predicate of the query has been transformed into a first dynamic predicate at 512A, and the second predicate of the query has been transformed into a second dynamic predicate at 512B. For example, the first predicate has been converted to a first child node of a table scan at 502 (at 512A), and the second predicate has been converted to a second child node of a table scan at 502 (at 512B). At 509, the third predicate of the query (e.g., pred(T)) has also been converted to a child node that is conjuncted with the first dynamic predicate. For example, as shown, the third predicate at 509 and the first dynamic predicate at 512A can be child nodes of the table scan at 502 and can be combined using the conjunction at 504B, for example, using an AND clause. In addition, the second dynamic predicate at 512B is shown as a child node of the table scan at 502, which can be combined with the child node corresponding to the third predicate at 509 and the child node corresponding to the first dynamic predicate at 512A using the conjunction at 504A (e.g., using an AND clause). Although the first dynamic predicate is shown as being combined with the third predicate using the conjunction at 504B before being combined with the second dynamic predicate using the conjunction at 504A, the third predicate can alternatively be converted to a child node at 509 of the conjunction at 504B with the second dynamic predicate at 512B before being combined with the first dynamic predicate at 512A using the conjunction at 504A. In some embodiments, the query optimizer 110 determines the order of conjunctions and / or whether the third predicate is combined with the first and / or second dynamic predicates to improve query execution efficiency depending on the scenario.

[0055] Reference again Figure 5 , the first dynamic predicate at 512A is shown as a first value list (e.g., ValueList) operator, and the second dynamic predicate at 512B is shown as a second value list (e.g., ValueList) operator. For a given column within the query, the first value list operator and the second value list operator each return a list of all values ​​bounded by a given value.

[0056] Using the example query provided above, the first value list operator at 512A can be written as: ValueList(S 1 .exp,T.col 1 ). The database management system 100 may execute the first value list operator at 512A to convert the expression S at 508A into 1 .exp collects all values ​​and filters T.col based on the value at 510A 1In addition, the second value list operator at 512B can be written as: ValueList(S 2 .exp,T.col 2 ). The database management system 100 may execute a second value list operator at 512B to extract the expression S from the expression S at 508B. 2 .exp collects all values ​​and filters T.col based on the value at 510B 2 Although T.col 1 represents the first column from the fact table T, and T.col 2 represents the second column from the fact table T, wherein the table scan at 502 is performed, but in some embodiments, S 1 .exp and S 2 .exp can be any expression. In this way, the first value list operator at 512A and the second value list operator at 512B can be similar to an (IN-List) predicate in a list, which can include Tb IN (e1, e2, ..., en), where e1, e2, ..., en are unknown at compile time, but collected at query execution time.

[0057] In some embodiments, the first data from the first dimension table, the second data from the second dimension table, and the fact data from the fact table can be represented by at least one data vector. For example, the first data from the first dimension table, the second data from the second dimension table, and / or the fact data from the fact table can be stored as a data vector in a row of a corresponding one of the first dimension table, the second dimension table, or the fact table. The data vector may include multiple values, such as numerical values ​​and / or integer values. For example, each column of the first dimension table, the second dimension table, and / or the fact table may be represented as a data vector with a length of "table size". Each row of the first dimension table, the second dimension table, and / or the fact table is a tuple including an entry of a data vector from each column. The tuple may include and / or represent a vector of data. As an example, in order to find the value of a certain column of row #100, the data vector of the certain column is searched at position 100 within the column. Therefore, each row of the first dimension table, the second dimension table and / or the fact table may include a tuple (or data vector) representing at least some of the first data, the second data and / or the fact data, respectively, which is an entry of a data vector from at least one column in a corresponding one of the first dimension table, the second dimension table and / or the fact table.

[0058] At least one data vector may correspond to a value identifier (also referred to herein as valueId or VID). For example, in a database (e.g., databases 190A-N) (such as a column storage database or a column-oriented database) and / or one or more tables stored on the database (such as the first dimension table S 1(e.g., first dimension table 250), second dimension table S 2 In the first dimension table S (e.g., the second dimension table 251) and / or the fact table T (e.g., the fact table 252), a dictionary or dictionary encoding may be used to compress the data values ​​represented by the vectors of data in the columns of the table. Typically, the dictionary includes columns that store the actual data. 1 , the second dimension table S 2 The columns of the and / or fact table T can be represented as data vectors, which can store index values ​​that reference the dictionary. These index values ​​can be value identifiers, which can include numerical values ​​mapped to the data vectors and / or identifiers including numerical values, so there is no need to search for the first data, the second data, and / or the fact data itself, which will be cumbersome and computationally expensive. On the contrary, the columns of the dictionary can be lists of index values ​​(e.g., value identifiers). Each value identifier in the column represents a position in the dictionary. For example, a value identifier with a value of "5" points to the sixth value in the corresponding dictionary because the value identifier starts with a value "0" that points to the first value in the corresponding dictionary. The database management system 100 can maintain a mapping between a data vector representing the first data, the second data, and / or the fact data and a value identifier corresponding to each data vector, such as via a dictionary.

[0059] Figure 6 An example transformed algebraic representation 600 of a query that has been further expanded is depicted according to some example embodiments. For example, Figure 6 An example pipeline architecture of the database management system 100 , such as within the execution engine 150 , is shown.

[0060] refer to Figure 6 , representation 600 includes a first subplan S1 at 606A and a first repeater operator (e.g., repeater Op) at 607A, which correspond to the first subplan at 506A and the first shared subplan at 507A, respectively. The repeater operator at 607A receives data from the first subplan at 606A. The data (e.g., first data) flows from the first repeater operator at 607A to the build side 230 and to the probe side 232. On the build side 230, the data flows from the first repeater operator at 607A to the hash join build pipeline at 604A, where the data is used to build a hash table for hash join.

[0061] Similarly, representation 600 includes a second sub-plan S2 at 606B and a second repeater operator (e.g., repeater Op) at 607B corresponding to the second sub-plan at 506B and the second shared sub-plan at 507B, respectively. The second repeater operator at 607B receives data from the second sub-plan at 606B. The data (e.g., the second data) flows from the second repeater operator at 607B to the build side 230 and / or to the probe side 232. On the build side 230, the second data flows from the second repeater operator at 607B to the hash join build pipeline at 604B, where the second data is used to build a hash table for the hash join.

[0062] Figure 6 An expansion of a first dynamic predicate (eg, a value list operator at 512A) and a second dynamic predicate (eg, a value list operator at 512B) is shown. Each dynamic predicate may include a collection operator, a merge operator, and / or a filter operator.

[0063] The collection operator collects data flowing from the repeater operator to the probe side 232. The collection operator parallelizes the collected data for further processing. For example, the collection operator can parallelize the data to increase processing speed and efficiency. Parallelizing the data can allow parallel processing of the data, which increases processing speed.

[0064] The merge operator may receive parallelized data from the collection operator. The merge operator may merge the parallelized data. Merging the parallelized data allows the parallelized data to be further processed before it is filtered. The merged data may be stored in a temporary repository. The merge operator may additionally and / or alternatively unique the merged data, which allows faster filtering of the data. The merged data may be uniqued by removing duplicate values ​​from each data vector representing the data (e.g., first data and / or second data from a first dimension table and / or a second dimension table, respectively). For example, the data vector may include: <1 1 2 2 4>. After the data vector is uniqued, the data vector may include: <1 2 4>. Therefore, duplicate values ​​from each data vector may be removed. Although the term "uniqued" is used herein, uniqued data may also be referred to as deduplication or elimination of duplication in a data vector herein.

[0065] In some embodiments, vectors of data may be mapped to value identifiers, as described herein. For example, as described herein, database management system 100 may maintain a mapping between vectors of data representing first data, second data, and / or fact data and corresponding value identifiers stored in data vectors (e.g., a list of value identifiers). Each value identifier may indicate a location (e.g., a row) in a dictionary where a corresponding data vector is stored. In some embodiments, a merge operator maps a value identifier back to a corresponding data vector.

[0066] The filter operator can perform a data vector scan and / or an index scan. The filter operator of the dynamic predicate filters at least the columns associated with the fact data from the fact table T (e.g., the columns specified by the query) based on the value identifier mapped to at least one data vector representing the merged data. The column can be filtered by removing at least one row from the fact table T that does not correspond to the value identifier based on which the filter is performed. Therefore, at least one row can be removed from the fact table T without reading all the data represented by each data vector stored in the corresponding dimension table. Instead, the filter operator can only search for the value identifier associated with each data vector. In other words, the filter operator can request all rows containing data corresponding to at least one value identifier from a data store containing at least the fact table T. This allows empty hanging tuples to be quickly and efficiently removed from the fact table T, which in turn reduces the number of rows in the fact table T considered by the database management system 100 during query execution. The configuration of the database management system 100 described herein reduces the processing speed and improves the processing efficiency of queries (such as queries including a join operator).

[0067] In some embodiments, a query may include multiple predicates that may be transformed into dynamic predicates. In such embodiments, the dynamic predicate may include multiple value list operators corresponding to each value identified in the query. In this example, the expression S.exp may refer to multiple columns from a single source table. This may allow efficient operation in data warehouse scenarios where data is organized in star schemas, snowflake schemas, and the like. As described herein, dynamic predicates (e.g., based on predicates included in the query) may be created for each input source table (e.g., dimension table) referenced by the query. This allows multiple input source relationships to be utilized, further improving the efficiency of query processing.

[0068] refer to Figure 6 , the first dynamic predicate may include a first collection operator (e.g., SJRCollectOp) at 620A, a first merge operator (e.g., SJRMergeOp) at 622A, and / or a first filter operator (e.g., SJRFilter) at 624A. For example, the first collection operator at 620A is based on at least a first expression (e.g., SJRCollectOp) included in the query (e.g., in the first predicate of the query).1 .exp) from the first dimension table S 1 At least first data is collected. The first collection operator at 620A may parallelize the first data for further processing. The first merge operator at 622A may receive the parallelized first data from the first collection operator at 620 and merge the parallelized first data. The first merge operator at 622A may additionally and / or alternatively unique the merged first data, which allows for faster filtering of the data. The first filter operator at 624A filters at least a first column associated with the fact data from the fact table T (e.g., T.col from the first predicate of the query) based on a value identifier mapped to at least one data vector representing the merged first data. 1 ).

[0069] Reference again Figure 6 , the second dynamic predicate may include a second collection operator (e.g., SJRCollectOp) at 620B, a second merge operator (e.g., SJRMergeOp) at 622B, and / or a second filter operator (e.g., SJRFilter) at 624B. For example, the second collection operator at 620B is based on at least a second expression (e.g., SJRCollectOp) included in the query (e.g., in the second predicate of the query). 2 .exp) from the second dimension table S 2 At least the second data is collected. The second collection operator at 620B can parallelize the second data for further processing. The second merge operator at 622B can receive the parallelized second data from the second collection operator at 620 and merge the parallelized second data. The second merge operator at 622B can additionally and / or alternatively unique the merged second data, which allows for faster filtering of the data. The second filter operator at 624B further filters at least a second column associated with the fact data from the fact table T (e.g., T.col from the second predicate of the query) based on a value identifier mapped to at least one data vector representing the merged second data. 2 ).

[0070] Reference again Figure 6 , the database management system 100 (eg, via the execution engine 150) may scan at least the fact table T (and / or the first dimension table S) based on the third predicate (eg, pred(T)) and the filtered columns. 1 and / or the second dimension table S 2) to execute the query. For example, the execution engine 150 may scan the fact table T at 602 using a table scan based at least on a first conjunction (e.g., AND) of the filtered first column from the first filter operator at 624A and the third predicate at 609 at 627 and a second conjunction (e.g., AND) of the filtered second column from the second filter operator at 624B at 629. Thus, the first dynamic predicate and the second dynamic predicate are implemented as part of an efficient semi-join reduction that reduces at least the number of rows in the fact table T based on a plurality of input source tables for scanning the fact table T. The scan may be pushed to a table scan pipeline (e.g., TableScan pipeline) at 603 and pushed to a hash join probe side pipeline (e.g., HashJoin probe pipeline) at 604B for hash join.

[0071] Figure 7 A flow chart illustrating a process 700 for processing a query using a table scan predicate with an integrated semi-join filter according to some example embodiments is depicted. In addition, process 700 may be used to process a query using a table scan predicate with an integrated semi-join filter in a scenario where multiple input sources (e.g., relational input sources such as dimension tables) are joined with a fact table. Figure 1-6 , one or more aspects of process 700 may be performed by execution engine 150 and / or another component of database management system 100. As described herein, database management system 100 facilitates efficient execution of queries, such as when the query includes a request to join data from multiple input sources (e.g., relational input sources, such as dimension tables) with another table (e.g., a fact table), and / or it is desired to utilize a semi-join reduction of the multiple input sources.

[0072] At 702, database management system 100 (e.g., execution engine 150) may receive a query. The query may include a request to join first data from a first dimension table and second data from a second dimension table with fact data from a fact table. For example, the query may include a join operator.

[0073] The query may additionally and / or alternatively include a first predicate, a second predicate, and a third predicate. The first predicate may include a first expression (e.g., a query expression) associated with first data from a first dimension table and / or a first reference to a first column associated with fact data from a fact table. The second predicate may include a second expression (e.g., a query expression) associated with second data from a second dimension table and / or a second reference to a second column associated with fact data from a fact table. The third predicate may be used for a table scan of a fact table. A table scan may be used to scan a fact table.

[0074] At 704, the database management system 100 (e.g., the execution engine 150) may apply a first dynamic predicate to at least the first data. The first dynamic predicate may be based on a value that is not available when the query is received but may be available when the query is executed. The first dynamic predicate may be associated with a first dimension table. The first dynamic predicate may include a transformation of the first predicate for use in executing the query. For example, the database management system 100 (e.g., the execution engine 150) may transform the first predicate into a first dynamic predicate to execute the query. The first predicate may be transformed into the first dynamic predicate by converting the first predicate into a first child node of a table scan that scans the fact table as described herein. In some embodiments, a third predicate may also be included as another child node of the table scan. The first predicate and the third predicate may be combined using a conjunction (e.g., an AND conjunction) for use during a table scan.

[0075] In some embodiments, the first dynamic predicate can be applied to at least first data referenced by a first expression included in the query (such as in a first predicate of the query). The first dynamic predicate can be applied to at least the first data by collecting the first data based on at least the first expression. For example, the first data can be collected, such as a portion of the first data related to the first expression. The collected first data can be parallelized to increase data processing speed.

[0076] Additionally and / or alternatively, the execution engine 150 may apply the first dynamic predicate by at least merging the first data. The first data may be merged after the first data is collected. The first data may be merged by at least uniquely making the first data. For example, the first data may be uniquely made by removing duplicate values ​​from at least one data vector representing the first data. This allows at least one data vector to include unique values ​​that are easier to process.

[0077] Additionally and / or alternatively, the execution engine 150 may apply the first dynamic predicate by filtering the first column associated with the first data from the fact table. The execution engine 150 may filter the first column based on a first value identifier mapped to at least one data vector representing the merged first data. For example, the execution engine 150 may remove at least one row from the fact table that does not correspond to the expected first value identifier. This allows for fast and efficient removal of empty hanging tuples.

[0078] At 706, the database management system 100 (e.g., the execution engine 150) may apply the second dynamic predicate to at least the second data. The second dynamic predicate may be based on a value that is not available when the query is received but may be available when the query is executed. The second dynamic predicate may be associated with a second dimension table. The second dynamic predicate may include a transformation of the second predicate for use in executing the query. For example, the database management system 100 (e.g., the execution engine 150) may transform the second predicate into a second dynamic predicate for use in executing the query. The second predicate may be transformed into a second dynamic predicate by converting the second predicate into a second child node of a table scan that scans the fact table as described herein. In some embodiments, a third predicate may also be included as another child node of the table scan. The second predicate, the first predicate, and the third predicate may be combined using a conjunction (e.g., an AND conjunction) for use during a table scan.

[0079] In some embodiments, the second dynamic predicate can be applied to at least second data referenced by a second expression included in the query (such as in a second predicate of the query). The second dynamic predicate can be applied to at least the second data by collecting the second data based on at least the second expression. For example, the second data can be collected, such as a portion of the second data related to the second expression. The collected second data can be parallelized to increase data processing speed.

[0080] Additionally and / or alternatively, the execution engine 150 may apply the second dynamic predicate by at least merging the second data. The second data may be merged after the second data is collected. The second data may be merged by at least uniquely making the second data. For example, the second data may be uniquely made by removing duplicate values ​​from at least one data vector representing the second data. This allows at least one data vector to include unique values ​​that are easier to process.

[0081] Additionally and / or alternatively, the execution engine 150 may apply the second dynamic predicate by screening the second column associated with the second data from the fact table. The execution engine 150 may screen the second column based on a second value identifier mapped to at least one data vector representing the merged second data. For example, the execution engine 150 may remove at least one row from the fact table that does not correspond to the expected second value identifier. This allows for fast and efficient removal of empty hanging tuples.

[0082] At 708, the database management system 100 (e.g., the execution engine 150) can execute the query by scanning the fact table based on at least the first predicate, the second predicate, the first filtered column, and the second filtered column. The first dynamic predicate and the second dynamic predicate can be utilized together and implemented as a semi-join reduction to reduce at least the number of rows in the fact table for scanning the fact table.

[0083] 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 more than one feature combination of the example and optionally combined with one or more features of one or more additional examples are additional examples that also fall within the disclosure of the present application:

[0084] Example 1: A system, comprising: at least one data processor; and at least one memory causing operations, the operations comprising: receiving a query, the query comprising a request to join first data from a first dimension table and second data from a second dimension table with fact data from a fact table, the query further comprising: a first predicate comprising a first expression associated with the first data and a first reference to a first column associated with the fact data from the fact table, a second predicate comprising a second expression associated with the second data and a second reference to a second column associated with the fact data from the fact table, and a third predicate for table scanning of the fact table; and performing a table scan of the fact table by at least collecting the first data based on the first expression and labeling the first data based on a first value. The query is performed by filtering a first column based on a value identifier to apply a first dynamic predicate associated with a first dimension table to at least the first data, the first value identifier is mapped to at least one data vector representing the collected first data, and the first dynamic predicate includes a transformation of the first predicate for executing the query; applying a second dynamic predicate associated with a second dimension table to at least the second data by at least collecting the second data based on a second expression and filtering a second column based on a second value identifier, the second value identifier is mapped to at least one data vector representing the collected second data, and the second dynamic predicate includes a transformation of the second predicate for executing the query; and executing the query by scanning the fact table at least based on the first predicate, the second predicate, the first filtered column, and the second filtered column.

[0085] Example 2: A system according to Example 1, wherein the operation further includes: transforming the first predicate into a first dynamic predicate by at least converting the first predicate into a first child node of a table scan; transforming the second predicate into a second dynamic predicate by at least converting the second predicate into a second child node of the table scan, and wherein the table scan is configured to perform a scan of a fact table.

[0086] Example 3: A system according to any one of Examples 1 to 2, wherein the transformation further includes: including the first predicate as a child node of the table scan that is conjunct with the first dynamic predicate; and including the second predicate as another child node of the table scan that is conjunct with the second dynamic predicate.

[0087] Example 4: A system according to any one of Examples 1 to 3, wherein collecting the first data includes parallelizing the first data; wherein applying the first dynamic predicate also includes merging the first data after collecting the first data, wherein collecting the second data includes parallelizing the second data; wherein applying the second dynamic predicate also includes merging the second data after collecting the second data.

[0088] Example 5: The system of any one of Examples 1 to 4, wherein merging the first data comprises uniqueizing the first data, and wherein merging the second data comprises uniqueizing the second data.

[0089] Example 6: A system according to any one of Examples 1 to 5, wherein uniqueizing the first data includes removing duplicate values ​​from at least one data vector representing the collected first data, and wherein uniqueizing the second data includes removing duplicate values ​​from at least one data vector representing the collected second data.

[0090] Example 7: A system according to any one of Examples 1 to 6, wherein filtering the first column includes removing at least one row from the fact table that does not correspond to the first value identifier, and wherein filtering the second column includes removing at least one row from the fact table that does not correspond to the second value identifier.

[0091] Example 8: The system of any one of Examples 1 to 7, wherein the first dynamic predicate and the second dynamic predicate are implemented as a semi-join reduction to reduce at least the number of rows in the fact table for scanning the fact table.

[0092] Example 9: A computer-implemented method, comprising: receiving a query, the query comprising a request to join first data from a first dimension table and second data from a second dimension table with fact data from a fact table, the query further comprising: a first predicate comprising a first expression associated with the first data and a first reference to a first column associated with the fact data from the fact table, a second predicate comprising a second expression associated with the second data and a second reference to a second column associated with the fact data from the fact table, and a third predicate for table scanning of the fact table; joining the first data to the first column by at least collecting the first data based on the first expression and filtering the first column based on a first value identifier; A first dynamic predicate associated with the dimension table is applied to at least the first data, the first value identifier is mapped to at least one data vector representing the collected first data, and the first dynamic predicate includes a transformation of the first predicate for executing the query; a second dynamic predicate associated with the second dimension table is applied to at least the second data by at least collecting the second data based on the second expression and filtering the second column based on the second value identifier, the second value identifier is mapped to at least one data vector representing the collected second data, and the second dynamic predicate includes a transformation of the second predicate for executing the query; and the query is executed by at least scanning the fact table based on the first predicate, the second predicate, the first filtered column, and the second filtered column.

[0093] Example 10: The method of Example 9, wherein the method further comprises: transforming the first predicate into a first dynamic predicate at least by converting the first predicate into a first child node of a table scan; transforming the second predicate into a second dynamic predicate at least by converting the second predicate into a second child node of the table scan, and wherein the table scan is configured to perform a scan of a fact table.

[0094] Example 11: A method according to any one of Examples 9 to 10, wherein the transformation further includes: including the first predicate as a child node of the table scan that is conjunct with the first dynamic predicate; and including the second predicate as another child node of the table scan that is conjunct with the second dynamic predicate.

[0095] Example 12: A method according to any one of Examples 9 to 11, wherein collecting the first data includes parallelizing the first data; wherein applying the first dynamic predicate also includes merging the first data after collecting the first data, wherein collecting the second data includes parallelizing the second data; wherein applying the second dynamic predicate also includes merging the second data after collecting the second data.

[0096] Example 13: A method according to any one of Examples 9 to 12, wherein merging the first data includes uniqueizing the first data, and wherein merging the second data includes uniqueizing the second data.

[0097] Example 14: A method according to any one of Examples 9 to 13, wherein uniqueizing the first data includes removing duplicate values ​​from at least one data vector representing the collected first data, and wherein uniqueizing the second data includes removing duplicate values ​​from at least one data vector representing the collected second data.

[0098] Example 15: A method according to any one of Examples 9 to 14, wherein filtering the first column includes removing at least one row from the fact table that does not correspond to the first value identifier, and wherein filtering the second column includes removing at least one row from the fact table that does not correspond to the second value identifier.

[0099] Example 16: The method of any one of Examples 9 to 15, wherein the first dynamic predicate and the second dynamic predicate are implemented as a semi-join reduction to reduce at least the number of rows in the fact table for scanning the fact table.

[0100] Example 17: A non-transitory computer-readable medium storing instructions that, when executed by at least one data processor, result in operations comprising: receiving a query comprising a request to join first data from a first dimension table and second data from a second dimension table with fact data from a fact table, the query further comprising: a first predicate comprising a first expression associated with the first data and a first reference to a first column associated with the fact data from the fact table, a second predicate comprising a second expression associated with the second data and a second reference to a second column associated with the fact data from the fact table, and a third predicate for table scanning of the fact table; collecting the first data based on at least the first expression and Applying a first dynamic predicate associated with a first dimension table to at least the first data by filtering a first column based on a first value identifier, the first value identifier being mapped to at least one data vector representing the collected first data, the first dynamic predicate comprising a transformation of the first predicate for executing the query; applying a second dynamic predicate associated with a second dimension table to at least the second data by at least collecting the second data based on a second expression and filtering a second column based on a second value identifier, the second value identifier being mapped to at least one data vector representing the collected second data, the second dynamic predicate comprising a transformation of the second predicate for executing the query; and executing the query by at least scanning the fact table based on the first predicate, the second predicate, the first filtered column, and the second filtered column.

[0101] Example 18: A non-transitory computer-readable medium according to any one of Examples 17 to 18, wherein the operation further includes: transforming the first predicate into a first dynamic predicate by at least converting the first predicate into a first child node of a table scan; transforming the second predicate into a second dynamic predicate by at least converting the second predicate into a second child node of the table scan, and wherein the table scan is configured to perform a scan of a fact table.

[0102] Example 19: A non-transitory computer-readable medium according to any one of Examples 17 to 18, wherein the transformation further includes: including the first predicate as a child node of the table scan that is conjunct with the first dynamic predicate; and including the second predicate as another child node of the table scan that is conjunct with the second dynamic predicate.

[0103] Example 20: A non-transitory computer-readable medium according to any one of Examples 17 to 19, wherein collecting the first data includes parallelizing the first data; wherein applying the first dynamic predicate also includes merging the first data after collecting the first data, wherein collecting the second data includes parallelizing the second data; wherein applying the second dynamic predicate also includes merging the second data after collecting the second data.

[0104] Figure 8 An example of a database management system 100 is depicted in accordance with some example implementations.

[0105] The database management system 100 may include one or more user devices 102A-N, such as computers, smart phones, tablets, Internet of Things (IoT) devices, and / or other computer or processor-based devices. The user device may include a user interface, such as a browser or other application, to enable access to one or more applications, database layers, and / or databases to generate queries to one or more databases 190A-N, and / or receive responses to these queries.

[0106] exist Figure 8 In the example of , database 190A-N represents a database layer of a database management system, where data can be persisted and / or stored in a structured manner, and where data can be queried or manipulated using operations including SQL commands or other types of commands / instructions to provide reading, writing, and / or performing other operations. To illustrate by way of example, user device 102A-N can send queries to database layer 190A-N via execution engine 150, and database layer 190A-N can represent a persistence layer and / or storage layer that can store and / or query database tables. The query can be sent via a connection such as a wired and / or wireless connection (e.g., the Internet, a cellular link, a WiFi link, etc.).

[0107] The database execution engine 150 may include a query optimizer 110, such as a SQL optimizer and / or another type of optimizer, to receive at least one query from a user device and generate a query plan (which may be optimized) for execution by a query execution engine 112. 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 expressed as a so-called "query algebra" or "relational algebra."

[0108] For example, SELECT columns from Table A and Table B and perform an INNER JOIN on Table A and Table B may represent a query received by a database execution engine 150 including a query optimizer 110. There may be several ways to implement the execution of the query. In this way, the query plan may provide hints about the execution time of the overall query or propose an optimal query plan. In order to optimize the query, the query optimizer 110 may obtain one or more costs for different ways in which the execution of the query plan may be executed. The cost may be obtained from a cost function 114 via an execution interface 112A, which responds to the query optimizer 110 with the cost of a given query plan (or portion thereof), and these costs may be, for example, the execution time at the database layer 190A-N.

[0109] The query optimizer 110 can form an optimal query plan, which can represent a query algebra, as described above. In order to compile the query plan, the query optimizer 110 can provide the query plan to the query plan compiler 116 to enable compilation of some (if not all) of the query plan. The query plan compiler 116 can 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 (which can be precompiled and stored and then selected for certain operations in the query plan) and / or just-in-time code generated specifically for executing the query plan. For example, the plan compiler can select the precompiled code for a given operation as part of the optimization of the query plan, while for another operation in the query plan, the plan compiler can allow the compiler to generate code. The precompiled and generated code represents the code for executing the query plan, and the code can be provided to the plan generator 118, which interfaces with the query execution engine 112.

[0110] In some implementations, the query optimizer 110 may optimize the query plan by compiling and generating code. In addition, the query optimizer 110 may optimize the query plan to achieve pipeline during execution.

[0111] In some embodiments, the query optimizer 110 can be configured to select other execution engines. For example, the query optimizer 110 can select an execution engine that is specifically configured to support a row storage database or an ABAP type database via interface 112C, or the query optimizer 110 can select an execution engine that is specifically configured to support a column storage type database via interface 112D. In this way, the query optimizer 110 can choose whether to use a general database execution engine 150 or a traditional (e.g., database-specific) execution engine (e.g., available via interface 112C / D).

[0112] The query execution engine 112 may receive compiled code from the plan generator 118 to enable execution of an optimized query plan, although the query execution engine may also receive code or other commands directly from a higher-level application or other device, such as a user device 102A-N.

[0113] The query execution engine 112 may then forward the code to the plan execution engine 120 via the execution interface 112B. The plan execution engine may then prepare the plan for execution, and the query plan may include the precompiled code 125 and / or the generated code 127. When the code for the query plan is ready to execute during runtime, the query execution engine 112 may step through the code to execute some operations within the database execution engine 150 and send some operations (or commands supporting operations such as reads, writes, etc.) to the execution engine application programming interface (API) for execution at one or more of the database tiers 190A-N.

[0114] Table 1 below depicts an example of a query execution plan that includes (1) a table scan (TableScan) (filter X=1) and (2) materialize (columns A, B). In this example, the table scan will result in one or more calls to one or more of the databases 190A-N via the execution engine API 199. Specifically, the table scan operator at Table 1 will result in a dictionary lookup for column "X" with value ID "1" and a call to an index vector scan with the valueid obtained from the dictionary lookup, which results in a list of document IDs identifying one or more rows in Table 1. Then, for each document ID, a call is made via the execution engine 199 to look up the value IDs for columns A and B. The value IDs can be used to look up the dictionary values ​​to be materialized, and columns A and B include the actual data values ​​of these columns.

[0115] Table 1

[0116]

[0117]

[0118] In some embodiments, as noted, the query execution engine 112 can be configured to handle different types of databases and the corresponding persistence layers and / or tables therein. For example, database 190N can be implemented as a row-oriented database so that insertions are performed by adding rows with corresponding row identifiers, while another database 190A can be implemented as a column storage database that can use dictionary and compression techniques when inserting data into a table. In this example, the query execution engine 112 can perform execution related to handling the differences between these two types of databases. This can enable reduced processing at the database layer 190A-N. In addition, the query execution engine 112 can 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 112 can perform these and other complex operations, while the persistence / storage layer 190A-N of the database can perform simpler operations to reduce the processing burden at the persistence / storage layer 190A-N of the database.

[0119] In some example embodiments, as described above, the query execution engine 112 may run the just-in-time code 127 generated for some query operations, while the precompiled code 125 may be run for other operations. In addition, the query execution engine 112 may combine the generated code 127 with the precompiled code 125 to further optimize the execution of query-related operations. In addition, the query execution engine 112 may provide a plan execution framework that is capable of handling data blocks, pipelining, and state management during query execution. In addition, the query execution engine 112 may provide the ability to access table storage via an abstract interface to a table adapter, which may reduce dependencies on a particular type of storage layer / persistence layer (which may enable use with different types of storage layers / persistence layers).

[0120] In some example embodiments, the database execution engine 150 may be provided with at least one table adapter. In some example embodiments, the table adapter may generate objects, such as table objects, which may be stored in a cache along with other code, objects, etc., awaiting runtime execution of a query. In some example embodiments, the table object may be opened during query execution to provide access to a table stored in a persistence layer of the database.

[0121] One or more aspects or features of the subject matter described herein can be implemented in digital electronic circuits, integrated circuits, specially designed application specific integrated circuits (ASICs) field programmable gate arrays (FPGAs) computer hardware, firmware, software and / or combinations thereof. These various aspects or features may include implementations in one or more computer programs executable and / or interpretable on a programmable system, the programmable system including at least one programmable processor, at least one input device and at least one output device, the at least one programmable processor may be dedicated or general purpose, coupled to receive data and instructions from a storage system, at least one input device and at least one output device, and to 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 may 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.

[0122] 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, device and / or equipment 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, such as, for example, a processor cache or other random access memory associated with one or more physical processor cores.

[0123] 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 types 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, but not limited to, acoustic, voice, or tactile input. Other possible input devices include, but are not limited to, touch screens or other touch-sensitive devices, such as single-point or multi-point resistive or capacitive touch pads, voice recognition hardware and software, optical scanners, optical pointers, digital image capture devices and associated interpretation software, and the like.

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

[0125] The methods shown are exemplary only. Although the methods are shown as having a specific operational flow, two or more operations may be combined into a single operation, a single operation may be performed in two or more separate operations, one or more of the operations shown may not be present in various implementations, and / or additional operations not shown may be part of the method.

Claims

1. A system comprising: at least one data processor; as well as at least one memory, causing operations comprising: A query is received, the query comprising a request to join first data from a first dimension table and second data from a second dimension table with fact data from a fact table, the query further comprising: a first predicate including a first expression associated with the first data and a first reference to a first column associated with the fact data from the fact table, a second predicate including a second expression associated with the second data and a second reference to a second column associated with the fact data from the fact table, and The third predicate is used for table scan of the fact table; applying a first dynamic predicate associated with a first dimension table to at least the first data by at least collecting the first data based on a first expression and filtering the first column based on a first value identifier, the first value identifier being mapped to at least one data vector representing the collected first data, the first dynamic predicate comprising a transformation of the first predicate for executing the query; applying a second dynamic predicate associated with the second dimension table to at least the second data by at least collecting the second data based on a second expression and filtering the second column based on a second value identifier, the second value identifier being mapped to at least one data vector representing the collected second data, the second dynamic predicate comprising a transformation of the second predicate for executing the query; and The query is performed by scanning the fact table based on at least the first predicate, the second predicate, the first filtered column, and the second filtered column.

2. The system according to claim 1, wherein: The operations also include: transforming the first predicate into a first dynamic predicate by at least converting the first predicate into a first child node of a table scan; transforming the second predicate into a second dynamic predicate by at least converting the second predicate into a second child node of the table scan, and wherein the table scan is configured to perform a scan of a fact table.

3. The system according to claim 2, wherein: The transformation further includes including the first predicate as a child node of the table scan in conjunction with the first dynamic predicate, and including the second predicate as another child node of the table scan in conjunction with the second dynamic predicate.

4. The system according to claim 1, wherein: Collecting the first data includes parallelizing the first data; wherein applying the first dynamic predicate further includes merging the first data after collecting the first data, wherein collecting the second data includes parallelizing the second data; wherein applying the second dynamic predicate further includes merging the second data after collecting the second data.

5. The system according to claim 4, wherein: Merging the first data includes uniqueizing the first data, and wherein merging the second data includes uniqueizing the second data.

6. The system according to claim 5, wherein: Uniqueizing the first data includes removing duplicate values ​​from at least one data vector representing the collected first data, and wherein uniqueizing the second data includes removing duplicate values ​​from at least one data vector representing the collected second data.

7. The system according to claim 1, wherein: Filtering the first column includes removing at least one row from the fact table that does not correspond to the first value identifier, and wherein filtering the second column includes removing at least one row from the fact table that does not correspond to the second value identifier.

8. The system according to claim 1, wherein: The first dynamic predicate and the second dynamic predicate are implemented as a semi-join reduction to reduce at least the number of rows in the fact table for scanning of the fact table.

9. A computer-implemented method comprising: A query is received, the query comprising a request to join first data from a first dimension table and second data from a second dimension table with fact data from a fact table, the query further comprising: a first predicate including a first expression associated with the first data and a first reference to a first column associated with the fact data from the fact table, a second predicate including a second expression associated with the second data and a second reference to a second column associated with the fact data from the fact table, and The third predicate is used for table scan of the fact table; applying a first dynamic predicate associated with a first dimension table to at least the first data by at least collecting the first data based on a first expression and filtering the first column based on a first value identifier, the first value identifier being mapped to at least one data vector representing the collected first data, the first dynamic predicate comprising a transformation of the first predicate for executing the query; applying a second dynamic predicate associated with the second dimension table to at least the second data by at least collecting the second data based on a second expression and filtering the second column based on a second value identifier, the second value identifier being mapped to at least one data vector representing the collected second data, the second dynamic predicate comprising a transformation of the second predicate for executing the query; and The query is performed by scanning the fact table based on at least the first predicate, the second predicate, the first filtered column, and the second filtered column.

10. The method according to claim 9, wherein: The method further includes: transforming a first predicate into a first dynamic predicate by at least converting the first predicate into a first child node of a table scan; transforming a second predicate into a second dynamic predicate by at least converting the second predicate into a second child node of the table scan, and wherein the table scan is configured to perform a scan of a fact table.

11. The method according to claim 10, wherein: The transformation further includes including the first predicate as a child node of the table scan in conjunction with the first dynamic predicate, and including the second predicate as another child node of the table scan in conjunction with the second dynamic predicate.

12. The method according to claim 9, wherein: Collecting the first data includes parallelizing the first data; wherein applying the first dynamic predicate further includes merging the first data after collecting the first data, wherein collecting the second data includes parallelizing the second data; wherein applying the second dynamic predicate further includes merging the second data after collecting the second data.

13. The method according to claim 12, wherein: Merging the first data includes uniqueizing the first data, and wherein merging the second data includes uniqueizing the second data.

14. The method according to claim 13, wherein: Uniqueizing the first data includes removing duplicate values ​​from at least one data vector representing the collected first data, and wherein uniqueizing the second data includes removing duplicate values ​​from at least one data vector representing the collected second data.

15. The method according to claim 9, wherein: Filtering the first column includes removing at least one row from the fact table that does not correspond to the first value identifier, and wherein filtering the second column includes removing at least one row from the fact table that does not correspond to the second value identifier.

16. The method according to claim 9, wherein: The first dynamic predicate and the second dynamic predicate are implemented as a semi-join reduction to reduce at least the number of rows in the fact table for scanning of the fact table.

17. A non-transitory computer-readable medium storing instructions that, when executed by at least one data processor, result in operations comprising: A query is received, the query comprising a request to join first data from a first dimension table and second data from a second dimension table with fact data from a fact table, the query further comprising: a first predicate including a first expression associated with the first data and a first reference to a first column associated with the fact data from the fact table, a second predicate including a second expression associated with the second data and a second reference to a second column associated with the fact data from the fact table, and The third predicate is used for table scan of the fact table; applying a first dynamic predicate associated with a first dimension table to at least the first data by at least collecting the first data based on a first expression and filtering the first column based on a first value identifier, the first value identifier being mapped to at least one data vector representing the collected first data, the first dynamic predicate comprising a transformation of the first predicate for executing the query; applying a second dynamic predicate associated with the second dimension table to at least the second data by at least collecting the second data based on a second expression and filtering the second column based on a second value identifier, the second value identifier being mapped to at least one data vector representing the collected second data, the second dynamic predicate comprising a transformation of the second predicate for executing the query; and The query is performed by scanning the fact table based on at least the first predicate, the second predicate, the first filtered column, and the second filtered column.

18. The non-transitory computer readable medium of claim 17, wherein: The operations also include: transforming the first predicate into a first dynamic predicate by at least converting the first predicate into a first child node of a table scan; transforming the second predicate into a second dynamic predicate by at least converting the second predicate into a second child node of the table scan, and wherein the table scan is configured to perform a scan of a fact table.

19. The non-transitory computer readable medium of claim 18, wherein: The transformation further includes including the first predicate as a child node of the table scan in conjunction with the first dynamic predicate, and including the second predicate as another child node of the table scan in conjunction with the second dynamic predicate.

20. The non-transitory computer readable medium of claim 17, wherein: Collecting the first data includes parallelizing the first data; wherein applying the first dynamic predicate further includes merging the first data after collecting the first data, wherein collecting the second data includes parallelizing the second data; wherein applying the second dynamic predicate further includes merging the second data after collecting the second data.

Images