scott@9i > get emp_loopinsert
  1   begin
  2   for mag in 1..15 loop
  3       insert into emp (select * from emp);
  4   end loop;
  5   commit;
  6*  end;
scott@9i > /

PL/SQL procedure successfully completed.

scott@9i > update emp set empno=rownum;

458752 rows updated.

scott@9i > commit;

Commit complete.

scott@9i > create index emp_ename_empno on emp(ename,empno);

Index created.

scott@9i >  exec dbms_stats.gather_table_stats('SCOTT','EMP',cascade=>true);

PL/SQL procedure successfully completed.

scott@9i >  select table_name,column_name,num_distinct from user_tab_cols where table_name='EMP' and column_name in ('EMPNO','ENAME');

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT
------------------------------ ------------------------------ ------------
EMP                            EMPNO                                458752
EMP                            ENAME                                    14

-- Normal as expected. 

scott@9i > set autotrace traceonly exp
scott@9i > select ename from emp where ename='SCOTT';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=103 Card=32768 Bytes
          =196608)

   1    0   INDEX (RANGE SCAN) OF 'EMP_ENAME_EMPNO' (NON-UNIQUE) (Cost
          =103 Card=32768 Bytes=196608)


-- Index skip scan works if the leading column is not used and if the leading column has  a few distinct values.

scott@9i > select ename from emp where empno=12345;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1 Bytes=11)
   1    0   INDEX (SKIP SCAN) OF 'EMP_ENAME_EMPNO' (NON-UNIQUE) (Cost=
          16 Card=1 Bytes=11)

-- let us manipulate the dictinct values in ENAME.

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

PL/SQL procedure successfully completed.

scott@9i >  select ename from emp where empno=12345;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=137 Card=1 Bytes=11)
   1    0   INDEX (FAST FULL SCAN) OF 'EMP_ENAME_EMPNO' (NON-UNIQUE) (
          Cost=137 Card=1 Bytes=11)