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.
Claims17
A method processed by one or more processors, the method comprising the steps of:
- (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 processed by a processor, the method comprising the steps of:
- 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, wherein decomposing includes creating a spooled table to house just columns accessed via the join as a first step of the 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;
- and
- executing, via the processor, the rewritten query.
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;
- wherein the join manager provides the two-step process to an optimizer to provide back the costs and the selected query execution plan;
- and
- wherein the join manager is configured to provide a first step that performs a join on the CP table and the non-CP table retaining just columns accessed by the join, and the join manager is configured to provide a second step that rowid joins the intermediate joined rows from the first step to remaining columns in the CP table.
Dependencies are inferred from the claim text.