Techniques for join processing on column partitioned tables
AssigneeTeradata US, Inc.
InventorsGrace Kwan-On Au, Mohammed Al-Kateb
Life of this patent
Estimating the term…
Abstract
Techniques for processing joins on column partitioned tables are provided. A join operation having a column partitioned table within a query is decomposed into a two-step process. The first process performs the join condition on the column portioned table with optional filtering conditions and a non-column partitioned table and spools the resulting columns to a spooled table. The spooled table is then rowid joined back to the column partitioned table to acquire remaining columns not present in the spooled table. Both the first and second steps can be separately resolved for costs when determining a query execution plan.
Claims21
A method implemented and programmed within memory or a non-transitory computer-readable storage medium and processed by one or more processors, the processors configured to execute the method, comprising:
- (1) receiving, via the processor, a query having a join operation on a column partitioned (CP) table and a non-CP table;
- (2) processing, via the processor, a join condition for the join operation on the non-CP table and the CP table with optional filtering to produce intermediate joined rows that satisfy the join condition;
- (3) creating, via the processor, a rowid spooled table having row identifiers for logical CP rows that are associated with the intermediate joined rows;
- and
- (4) performing, via the processor, a rowid join from the rowid spooled table to the CP table to acquire remaining columns of the CP table based on the row identifiers in the rowid spooled table.
A method implemented and programmed within memory or a non-transitory computer-readable storage medium and processed by a processor, the processor configured to execute the method, comprising:
- detecting, via the processor, a query having a join on a column partition (CP) table and a non-CP table;
- decomposing, via the processor, the join of the query into a two-step process;
- costing, via the processor, each step of the two-step process;
- and
- rewriting, via the processor, the query based on the costing.
A system, comprising:
- memory configured with a join manager that executes on a processor;
- wherein the join manager is configured to decompose a join operation on a column partition (CP) table and a non-CP table in a query into a two-step process and permit each step to have resolved costs for selecting a query execution plan for the query.
Dependencies are inferred from the claim text.