Join indexes have several uses, including the following.
You can also use a column‑partitioned join index to create a full or partial replication of a base table to facilitate various manipulations of the data.
You can define a join index on one or several tables. Single-table join index functionality is an extension of the original intent of join indexes, hence the confusing adjective join used to describe single-table join indexes.
Join indexes, along with secondary indexes, are the only way a single row or a row subset can be retrieved from either an NoPI table or a column‑partitioned table without a full‑table scan, scan of subset of row partitions, or full‑column scan for a table (see “NoPI Tables, Column‑Partitioned Tables, and Column-Partitioned Join Indexes” on page 280).
Join indexes, like hash indexes, are not indexes in the usual sense of the word. They are system‑maintained tables that cannot be accessed directly using DML requests.
The Optimizer includes a multitable join index in a plan in the following situations.
The Optimizer includes a join index in a plan in the following situations.
You can only specify ROWID in the outermost SELECT of the CREATE JOIN INDEX statement. See “CREATE JOIN INDEX” in SQL Data Definition Language Detailed Topics.
It is preferable to specify NUPI and ROWID over NUPI and USI.
In this case, the Optimizer uses the ROWID, base table UPI or NUPI, or base table USI to join the join index rows with their underlying base table rows to pick up the base table columns necessary to complete the cover.