Join indexes are identical to base user data tables in multiple ways.
For example, you can do the following things with a join index.
- Create a unique or nonunique primary index, either a PPI or an nonpartitioned primary index, on its columns.
UPIs are supported only for uncompressed single-table join indexes without an ORDER BY clause.
- Create a column-partitioned, single-table uncompressed, non-aggregate join index. Such a join index can be sparse join index.
- Create nonunique secondary indexes on its columns.
- Perform any of the following statements against it.
- COLLECT STATISTICS (Optimizer Form)
- DROP JOIN INDEX
- DROP STATISTICS (Optimizer Form)
- HELP JOIN INDEX
- SHOW JOIN INDEX
- Specify BEGIN and END bound functions on Period or derived Period columns in its definition.
- Specify row-level security constraint columns in its definition.You can do this only if both of the following criteria are true:
- The index references a maximum of one row-level security-protected base table.
- All of the row-level security constraint columns defined in the base table are included in the join index definition.You cannot specify row-level security constraint columns in a partitioning expression for a row-partitioned join index.
- Specify any valid expressions in the select list and WHERE clause when the expressions reference at least one column.The following expression types are not valid in a join index definition:
- OLAP expressions
- UDF expressions
- Built-in functions that are explicitly not valid such as DEFAULT and PARTITION.
A join index defined with an expression in its select list provides less coverage than a join index that is defined using a base column (see Restrictions on Partial Covering by Join Indexes).
Unlike base tables, you cannot do the following things with join indexes:
- Create a join index on a join index.
- Query or update join index rows.
For example, if ordCustIdx is a join index, then the following query is not legal:
SELECT o_status, o_date, o_comment FROM ordCustIdx;
- Create a USI on its columns.
- Define multivalue or algorithmic compression on its columns.
If multivalue or algorithmic compression are defined on any columns of its parent base table set, however, a join index does inherit that compression under most circumstances.