Techniques for join processing on column partitioned tables

摘要

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.

主权项

1. 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.