CREATE JOIN INDEX
- Boaz Raufman
Creates a new join index for a given dimension column and a fact table.
Syntax
CREATE JOIN INDEX [schema_name.]join_index_name ON [schema_name.]fact_table_name([schema_name.]dim_table_name.dim_column_name) FROM [schema_name.]fact_table_name JOIN [schema_name.]dim_table_name ON [schema_name.][fact_table_name.]fact_join_key_column = [schema_name.][dim_table_name.]dim_join_key_column
Description
Join index is an index on one table, based on the values of a column in another table (dimension), and on a specific join criteria. Typically, it is an index on a large fact table based on the values of a dimension attribute. Join index accelerates queries by eliminating both the fetch of the join key from the fact table, and the join implementation (hash join or IN - merging indexes).
Join indexes are relevant when you have a relatively large dimension (few K values or more), and the attribute (the column in the dimension) has low cardinality, so that each value in the attribute represents many join key values.
Typically, a join index is defined if the average ratio between a unique dimension attribute, and the related join keys value is 1000 or more, but if the fact table is large (more than few billion) it is recommended to define join index for attribute with a smaller number of related join keys per value.
Example
CREATE JOIN INDEX store_sales_by_item_color_idx ON store_sales(item.item_color) FROM store_sales JOIN item ON ss_item_sk = i_item_sk;