NoPI and NoPI column-partitioned tables require a full-table scan to retrieve rows. Column partition elimination or row partition elimination may occur that make the scans less than full-table scans.
Because NoPI and NoPI column-partitioned tables support USIs and NUSIs, consider specifying USIs for conditions designed to retrieve single rows and NUSIs for conditions designed to retrieve row sets. For details, see NoPI Tables and SELECT Statements.
Although you cannot specify join indexes in WHERE clause conditions, the Optimizer uses them if they exist and can be used to avoid a full-table scan. For information about how the Optimizer uses join indexes, see Optimizer Join Plans.
Defining join indexes on a NoPI or NoPI column-partitioned table can slow down the loading of rows into the table.