Similarities of Join Indexes to User Data Tables
In many respects, hash and join indexes are identical to base user data tables.
For example, you can do the following things with a join index.
UPIs are supported only for uncompressed single‑table join indexes without an ORDER BY clause. See SQL Data Definition Language for details.
You can do this only if both of the following criteria are true:
Note: You cannot specify row‑level security constraint columns in a partitioning expression for a PPI or column‑partitioned join index.
The following expression types are not valid in a join index definition:
Note that 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” on page 575).
Unlike base tables, you cannot do the following things with join indexes:
For example, if ordCustIdx is a join index, then the following query is not legal:
SELECT o_status, o_date, o_comment
If multi‑value 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 (see “Default Column Multivalue Compression for Join Index Columns When the Referenced Base Table Column Is Compressed” on page 501).