Cost-based optimizations for join queries with union all views and derived tables

A method, comprising:

• identifying tables being joined in a query with a Union All (UA) operation;
• obtaining database statistics from a database having the tables, wherein the obtaining further includes identifying each of the branches of the UA operation as independent join processing steps, each of the independent join processing steps comprising a unique table view being joined with one or more of the tables;
• generating one or more query execution plans for each of the join processing steps based on the database statistics, each of the query execution plans including a unique permutation of join operations on the tables that satisfy each of the join processing steps;
• selecting an optimal one of the query execution plans for each of the join processing steps based on costs provided in the database statistics for the tables;
• and
• outputting results from the selected optimal one of the query execution plans for each of the join processing steps to a common spool.

A system, comprising:

• at least one hardware processor of a network computing device;
• and
• a data warehouse, executed on the at least one hardware processor of a network computing device, and including:
• a database optimizer;
• wherein the database optimizer is configured to:
• i) develop multiple query execution plans for a query having a Union All (UA) operation, wherein:
• each of the branches of the UA operation are identified as independent join processing steps,
• each of the join processing steps comprising a unique table view being joined with tables in the query, based on logically reorganizing join operations for the tables identified in each of the join processing steps, and
• each of the multiple query execution plans having a different ordering of the join operations in each of the join processing steps from remaining ones of the multiple query execution plans,
• ii) provide an optimal query execution plan for each of the join processing steps selected from the multiple query execution plans based on costs associated with the tables to a database engine for execution against the data warehouse, and
• iii) output results from the optimal query execution plan for each of the join processing steps to a common spool.