Summary
When an indexed column has a low Number of Distinct Values (NDV), the selectivity (1/NDV) for that column becomes poor.
If the indexed column with poor selectivity is the leading column of an index, the whole selectivity of the index becomes poor.
When the CBO has the option of selecting an index range scan on an index with poor selectivity, it may opt to perform a full table scan (FTS) instead, as it can result on a lower computed cost.
When the index with poor selectivity has a non-uniform data distribution, the cost of the FTS may be lower than the cost for the index range scan, still the performance using the index for values
with small number of rows may be better.

In situations like this, improving the selectivity of the index/column enables the CBO to choose
an index range scan over a FTS. Over time, when the data increases the NDV for the column, improving automatically the selectivity for the
indexed column, there is no further need to modify the statistics for index/column, as the standard process to gather stats will update the
statistics for index/column with more selective values. In the meantime, this script uses DBMS_STATS to change the following columns on the data dictionary.

Execute from SQL*Plus connected as system or apps:

# sqlplus apps/apps@vis11i
SQL> START bde_chg_stats.sql;

Parameters

-------------------------
1. p_owner Database User that owns the Table and the Index for which the statistics will be changed.
2. p_table_name Table (name).
3. p_index_name Index (name) that will have its statistics changed.
4. p_column_name Column (name) that will have its statistics changed.