CREATE JOIN INDEX (deprecated)
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.
Following release of DB version 3.19, you no longer need to manually create join indexes. This command is deprecated as of version 3.23.
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.|
Example–create join index with specific columns
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 INTEGER, 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 );