Join indexes can accelerate queries that use
JOIN operations on dimension tables. Under certain circumstances, a join index can significantly reduce the compute requirements to perform a join at query runtime. For more information, see Using join indexes.
CREATE JOIN INDEX [IF NOT EXISTS] <unique_join_index_name> ON <dim_table_name> ( <dim_join_key_col>, <dim_col1>[,...<dim_colN>] );
| ||A unique name for the join index.|
| ||The name of the dimension table on which the index is configured.|
| ||The dimension table join key column. This is the column name used in the join’s |
| ||The column name which is being loaded into memory from the dimension table. More than one column can be specified.|
The example below creates a join index on the dimension table
my_dim, created using the following DDL. Note that the column
my_dim_id is defined with the
UNIQUE attribute and contains no duplicate values.
CREATE DIMENSION TABLE my_cstmr_dim ( cstmr_id BIGINT UNIQUE, name TEXT, email TEXT, hs_nm INT, street TEXT, city TEXT, st_pvnc TEXT, country TEXT, phone1 TEXT, phone2 TEXT, status TEXT) PRIMARY INDEX my_cstmr_id;
Queries often run that join different fact tables with this dimension table. Those queries
status in returned results with a join. The following join index helps to accelerate these queries.
CREATE JOIN INDEX cstmr_email_name_jidx ON my_cstmr_dim ( cstmr_id, name, email, city, status );