Table scan predicate with integrated semi-join filter
A table scan predicate with integrated semi-join filter is provided. A method includes receiving a query including: a request to join first data from a first table and second data from a second table, a first predicate for use in a table scan of the second table, and a second predicate including an expression associated with the first data from the first table and a reference to a column associated with the second data from the second table. The method may include transforming the second predicate into a dynamic predicate for execution of the query. the method may include applying the dynamic predicate to at least the first data. The method may include executing the query by at least scanning the second table based on the first predicate and filtered first data from the application of the dynamic predicate. Related systems and articles of manufacture are provided.
1 . A system, comprising:
at least one data processor; and
at least one memory including instructions which when executed by the at least one processor cause operations comprising:
receiving, via a query optimizer of a database execution engine, a structured query language (SQL) query from a user equipment, the SQL query including at least a join operator, a first predicate, and a second predicate;
generating, using the query optimizer, a query plan for execution of the SQL query, wherein generating the query plan comprises:
transforming the second predicate into a dynamic predicate, wherein the dynamic predicate includes at least one value list operator that references one or more values that are not known at compile time but are gathered at query execution time, and
applying the dynamic predicate to at least first data, wherein the applying comprises parallelizing the first data; and
executing, using a query execution engine of the database execution engine and based on executable code received from a query plan compiler, the query plan to execute the SQL query by at least the applying of the dynamic predicate to at least the first data,
wherein each of the join operator and the at least one value list operator is a SOL operator executed at runtime of the SQL query, and wherein each of the first predicate, the second predicate, and the dynamic predicate is a logical expression evaluated at runtime of the SQL query to return a Boolean value based on data stored in one or more tables.
2 . The system of claim 1 , wherein the second predicate includes an expression associated with a first table.
3 . The system of claim 2 , wherein the second predicate includes a reference to a column associated with second data from a second table, and wherein the join operator references the first data from the first table and the second data from the second table.
4 . The system of claim 1 , wherein the operations further comprise implementing a semi-join reduction technique using the dynamic predicate.
5 . The system of claim 4 , wherein the semi-join reduction technique comprises a transformation to include a semi-join and a shared sub plan.
6 . The system of claim 5 , wherein the shared sub plan includes a build side and a probe side of the query plan.
7 . The system of claim 6 , wherein the build side includes one or more auxiliary structures to facilitate matching of rows between a first table and a second table on the probe side.
8 . The system of claim 1 , wherein the operations further comprise injecting the dynamic predicate into a table scan using a conjunction.
9 . The system of claim 1 , wherein the join operator references the first data from a first table and second data from a second table, and wherein the first predicate is used during a table scan of the second table.
10 . The system of claim 1 , wherein the first predicate is used to determine which rows in a first table and a second table are relevant to execution of the query.
11 . A method comprising:
receiving, via a query optimizer of a database execution engine, a structured query language (SOL) query from a user equipment, the SQL query including at least a join operator, a first predicate, and a second predicate;
generating, using the query optimizer, a query plan for execution of the SOL query, wherein generating the query plan comprises:
transforming the second predicate into a dynamic predicate, wherein the dynamic predicate includes at least one value list operator that references one or more values that are not known at compile time but are gathered at query execution time, and
applying the dynamic predicate to at least first data, wherein the applying comprises parallelizing the first data; and
executing, using a query execution engine of the database execution engine and based on executable code received from a query plan compiler, the query plan to execute the SQL query by at least the applying of the dynamic predicate to at least the first data,
wherein each of the join operator and the at least one value list operator is a SQL operator executed at runtime of the SQL query, and wherein each of the first predicate, the second predicate, and the dynamic predicate is a logical expression evaluated at runtime of the SQL query to return a Boolean value based on data stored in one or more tables.
12 . The method of claim 11 , wherein the second predicate includes an expression associated with a first table.
13 . The method of claim 12 , wherein the second predicate includes a reference to a column associated with second data from a second table, and wherein the join operator references the first data from the first table and the second data from the second table.
14 . The method of claim 11 , further comprising implementing a semi-join reduction technique using the dynamic predicate.
15 . The method of claim 14 , wherein the semi-join reduction technique comprises a transformation to include a semi-join and a shared sub plan.
16 . The method of claim 15 , wherein the shared sub plan includes a build side and a probe side of the query plan.
17 . The method of claim 16 , wherein the build side includes one or more auxiliary structures to facilitate matching of rows between a first table and a second table on the probe side.
18 . The method of claim 11 , further comprising injecting the dynamic predicate into a table scan using a conjunction.
19 . The method of claim 11 , wherein the join operator references the first data from a first table and second data from a second table, wherein the first predicate is used during a table scan of the second table.
20 . A non-transitory computer-readable medium storing instructions, which when executed by at least one data processor, result in operations comprising:
receiving, via a query optimizer of a database execution engine, a structured query language (SQL) query from a user equipment, the SQL query including at least a join operator, a first predicate, and a second predicate;
generating, using the query optimizer, a query plan for execution of the SQL query, wherein generating the query plan comprises:
transforming the second predicate into a dynamic predicate, wherein the dynamic predicate includes at least one value list operator that references one or more values that are not known at compile time but are gathered at query execution time, and
applying the dynamic predicate to at least first data, wherein the applying comprises parallelizing the first data; and
executing, using a query execution engine of the database execution engine and based on executable code received from a query plan compiler, the query plan to execute the SOL query by at least the applying of the dynamic predicate to at least the first data,
wherein each of the join operator and the at least one value list operator is a SQL operator executed at runtime of the SQL query, and wherein each of the first predicate, the second predicate, and the dynamic predicate is a logical expression evaluated at runtime of the SQL query to return a Boolean value based on data stored in one or more tables.