Selectivity refers to the percentage of table rows with the same nonunique secondary index value. An index with high selectivity retrieves a small number of rows. A unique primary index retrieval, for example, is highly selective, never returning more than one row. An index with low selectivity retrieves multiple rows.
Low Selectivity Indexes
When an index has low selectivity, a large number of rows have the same NUSI value and there are a small number of distinct values.
A column with those characteristics is typically a poor choice for a NUSI, because the cost of using the NUSI may be as high or higher than a full-table scan.
For example, assume that employee table contains 10,000 rows of about 100 bytes each, and there are only 10 different departments. If an average employee table data block is 2,560 bytes and can store about 25 rows, then the entire table requires about 400 data blocks.
If dept_no is defined as a nonunique secondary index on the employee table, and the dept_no values are evenly distributed, then the following query accesses about 1,000 row selections.
SELECT * FROM employee WHERE dept_no = 300;
Each AMP reads its own rows of the dept_no secondary index subtable. If any rows contain the index value 300, the AMP uses the associated rowIDs to select the data rows from its portion of the employee table.
Regardless of the number of AMPs involved, this retrieval requires 1,000 row selections from the employee table. To satisfy this number of select operations, all 400 employee table data blocks must typically be read.
If that were the case, then the number of I/O operations undertaken by the retrieval can easily exceed the number required for a full-table scan. In such instances, a table scan is a much more efficient solution than a NUSI-based retrieval.
High Selectivity Index
If deptno is a high-selectivity index and few employee rows share the same deptno value, using deptno for retrieval is more efficient than a full-table scan. Because of these selective conditions, the Optimizer specifies NUSI access for request processing when NUSI access is less costly than a full-table scan.