-- table EMP has 458752 records.
-- Just a regular primary key on EMPNO.
-- As expected, we get a unique scan, with just 4 logical i/o
-- 
scott@9i > select * from emp where empno=10000;

Elapsed: 00:00:00.37

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=79)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=3 Card=1 Byte
          s=79)

   2    1     INDEX (UNIQUE SCAN) OF 'PK_EMPNO' (UNIQUE) (Cost=2 Card=
          458752)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        948  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
--
-- Now let us ask CBO not to use the index
-- Elapsed time increases
-- INdex is not used. 2000 more LIO
--
scott@9i >  select /*+ NO_INDEX(emp pk_empno) */ * from emp where empno=10000;

Elapsed: 00:00:00.96

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=261 Card=1 Bytes=79)
   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=261 Card=1 Bytes=79)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2707  consistent gets
          0  physical reads
          0  redo size
        948  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


--
-- Drop this unique index and create a non-unique
--
scott@9i > alter table emp drop constraint pk_empno;

Table altered.

Elapsed: 00:00:00.76
scott@9i > create index EMP_EMPNO on emp(empno);

Index created.

Elapsed: 00:00:06.14
scott@9i > @gather_table_stats;
Enter value for table_name: EMP

PL/SQL procedure successfully completed.

Elapsed: 00:00:30.04


--
-- Very minimal increase in LIO.
-- But may produce a different plan using a range scan.
--
scott@9i >  select * from emp where empno=10000;

Elapsed: 00:00:00.38

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=79)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=4 Card=1 Byte
          s=79)

   2    1     INDEX (RANGE SCAN) OF 'EMP_EMPNO' (NON-UNIQUE) (Cost=3 C
          ard=1)
Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        948  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

--
-- Let us trick the CBO now.
-- Now CBO thinks there is only 10 distinct rows in table
-- and decides not to use the index.
--

scott@9i > exec dbms_stats.set_column_stats('SCOTT','EMP','EMPNO',distcnt=>10);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.28
scott@9i >  select * from emp where empno=10000;

Elapsed: 00:00:00.93

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=261 Card=45875 Bytes
          =3624125)

   1    0   TABLE ACCESS (FULL) OF 'EMP' (Cost=261 Card=45875 Bytes=36
          24125)

Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
       2709  consistent gets
          0  physical reads
          0  redo size
        948  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed