STAR AND SNOWFLAKE JOIN QUERY PERFORMANCE

摘要

A system and method for processing a database query is described. The method can, in response to detection that a database query involves a star or snowflake join operation, determine a selectivity ratio for each of a plurality of dimension tables. The selectivity ratio having a lower value can correspond to a more restrictive dimension table. Thereafter, a table ordering can be created beginning with a fact table and continuing with each of the dimension tables in ascending order of their corresponding selectivity ratios. Then a query plan involving join operations between successive tables in the table ordering can be created.

主权项

1. A method for optimizing a database query, comprising:
accessing a fact table and a plurality of dimension tables referenced by the database query; detecting that the database query involves a star join or snowflake join operation using the fact table and the plurality of dimension tables; in response to detecting that the database query involves a star join or snowflake join operation:
determining a selectivity ratio for each of the plurality of dimension tables, wherein a lower value for the selectivity ratio indicates that a corresponding dimension table is more restrictive compared to other dimension tables in the plurality of dimension tables;creating a table ordering beginning with the fact table and continuing with each of the dimension tables in ascending order of the corresponding selectivity ratios of the dimension tables; andcreating a query plan involving join operations between successive tables in the table ordering.