Creates a new join index for a given dimension column and a fact table.
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.