A NUSI should maintain row ID pointers for each logical row of a join index, not just the compressed physical rows.
ALL enables a NUSI to cover a join index, enhancing performance by eliminating the need to access the join index itself when all columns included in a query are contained in the NUSI. Use ALL when a NUSI is being defined for a join index and you want to make it eligible for the Optimizer to select when covering reduces access plan cost.
The ALL option ignores the case specification for a column so that a NUSI defined with the ALL option can:
- Include case-specific values.
- Cover a table or join index on a NOT CASESPECIFIC column set.
You cannot specify the ALL option for primary or unique secondary indexes.
Specifying ALL may require additional index storage space.
You cannot specify multiple NUSIs that differ only by the presence or absence of the ALL option.
ALL is not applicable to geospatial indexes.