A secondary index helps in reducing the full table scan on the table. Full table scans read every data block, whereas a non-unique SI does not read every data block, but its usefulness depends on the percentage of rows qualifying and the number of rows in the data block:
A full table scan is faster when:
- >= 1 row per block qualifies, THEN a full table scan of the base table is faster than NUSI access, and NUSI is not used
- > If 100 rows/block and 1% of the data qualifies, then every block will be read
- A full table scan is done for values that represent a large percent of the table
NUSI is used when:
- < 1 row per block qualifies, ...
