Splitting of a join operation to allow parallelization
AssigneeSybase, Inc.
InventorsXun Cheng, Peter Schneider
Life of this patent
Estimating the term…
Abstract
A system and method for processing a database query is described. In response to detection that a database query involves a star or snowflake join operation, a join operator in a preliminary query plan can be split into a build operator and a probe operator. The probe operator can be placed in a final query plan in the same place as the join operator in the preliminary query plan, while the build operator can be placed beneath the probe operator in the final query plan, between an exchange operator and the exchange operator's child from the preliminary query plan.
Claims20
A method for optimizing a database query, comprising:
- determining a corresponding selectivity ratio for each dimension table among a plurality of dimension tables, wherein a lower value for each selectivity ratio indicates that the corresponding dimension table is more restrictive compared to other dimension tables in the plurality of dimension tables;
- creating a table sequence that begins with specifying a fact table and thereafter specifies the plurality of dimension tables in ascending order of the corresponding selectivity ratios of the dimension tables;
- and
- creating a query plan that specifies a plurality of join operations between successive tables in the table sequence.
An apparatus comprising:
- one or more hardware processors;
- memory to store instructions that, when executed by the one or more hardware processors, cause the one or more hardware processors to perform operations comprising:
- determining a corresponding selectivity ratio for each dimension table among a plurality of dimension tables, wherein a lower value for each selectivity ratio indicates that the corresponding dimension table is more restrictive compared to other dimension tables in the plurality of dimension tables:
- creating a table sequence that begins with specifying a fact table and thereafter specifies the plurality of dimension tables in ascending order of the corresponding selectivity ratios of the dimension tables;
- and
- creating a query plan that specifies a plurality of join operations between successive tables in the table sequence.
A non-transitory machine-readable storage medium comprising instructions that, when executed by one or more machines, cause the one or more machines to perform operations comprising:
- determining a corresponding selectivity ratio for each dimension table among a plurality of dimension tables, wherein a lower value for each selectivity ratio indicates that the corresponding dimension table is more restrictive compared to other dimension tables in the plurality of dimension tables;
- creating a table sequence that begins with specifying a fact table and thereafter specifies the plurality of dimension tables in ascending order of the corresponding selectivity ratios of the dimension tables;
- and
- creating a query plan that specifies a plurality of join operations between successive tables in the table sequence.
Dependencies are inferred from the claim text.