CREATE JOIN INDEX

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;

See Also

DROP JOIN INDEX