Multi-table scan semi-join database optimization

A system comprising:

• at least one processor;
• and
• at least one memory including program code which when executed by the at least one processor causes operations comprising:
• generating, using a query optimizer of a database management system, a logical query plan for a structured query language (SQL) query that includes a join of a single fact table with each of multiple reducer tables, wherein generating the logical query plan comprises generating a first syntax tree including the single fact table and the multiple reducer tables;
• optimizing, using the query optimizer, the logical query plan for query execution by at least:
• applying to the logical query plan a first semi-join reduction to a first join operator that joins the single fact table with at least a first reducer table of the multiple reducer tables,
• creating a first table scan semi-join operator as a physical alternative to a first semi-join operator formed by the first semi-join reduction,
• applying a join thru join to a second join operator to change an order of the first join operator and the second join operator in the logical query plan,
• applying a second semi-join reduction to the second join operator above the first semi-join operator, and
• creating a second table scan semi-join operator as a physical alternative to a second semi-join operator to absorb the first table scan semi-join operator,
• wherein the optimizing of the logical query plan produces an optimized second syntax tree different from the first syntax tree;
• and
• executing, using a query execution engine of the database management system and based on executable code received from a query compiler, the optimized logical query plan in accordance with the optimized second syntax tree to execute the SQL query,
• wherein each of the first join operator, the second join operator, the first semi-join operator, the second semi-join operator, the first table scan semi-join operator, and the second table scan semi-join operator is a SQL operator executed at runtime of the SQL query.

A non-transitory computer readable storage medium storing program code which when executed by at least one processor causes operations comprising:

• generating, using a query optimizer of a database management system, a logical query plan for a structured query language (SQL) query that includes a join of a single fact table with each of multiple reducer tables, wherein generating the logical query plan comprises generating a first syntax tree including the single fact table and the multiple reducer tables;
• optimizing, using the query optimizer, the logical query plan for query execution by at least:
• applying to the logical query plan a first semi-join reduction to a first join operator that joins the single fact table with at least a first reducer table of the multiple reducer tables,
• creating a first table scan semi-join operator as a physical alternative to a first semi-join operator formed by the first semi-join reduction,
• applying a join thru join to a second join operator to change an order of the first join operator and the second join operator in the logical query plan,
• applying a second semi-join reduction to the second join operator above the first semi-join operator, and
• creating a second table scan semi-join operator as a physical alternative to a second semi-join operator to absorb the first table scan semi-join operator,
• wherein the optimizing of the logical query plan produces an optimized second syntax tree different from the first syntax tree;
• and
• executing, using a query execution engine of the database management system and based on executable code received from a query compiler, the optimized logical query plan in accordance with the optimized second syntax free to execute the SQL query,
• wherein each of the first join operator, the second join operator, the first semi-join operator, the second semi-join operator, the first table scan semi-join operator, and the second table scan semi-join operator is a SQL operator executed at runtime of the SQL query, and
• wherein the non-transitory computer readable storage medium excludes storing the program code in a signal form.