Create a concatenated index on table ITEM.

SQL> create index idx_item on item(create_by,item_type_code,item_class_id);

SQL> select * from item where create_by='SCM OL Maint Conv';

Execution Plan

----------------------------------------------------------                     

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=120 Card=288 Bytes=149184)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (TABLE) (Cost=120 Card=288 Bytes=149184)

   2    1     INDEX (RANGE SCAN) OF 'IDX_ITEM' (INDEX) (Cost=4 Card=288)

SQL> select * from item where item_class_id='ZX000';

Execution Plan

----------------------------------------------------------                     

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=18000 Card=1286 Bytes=666148)

   1    0   TABLE ACCESS (FULL) OF 'ITEM' (TABLE) (Cost=18000 Card=1286 Bytes=666148)                                                      

SQL> analyze table item compute statistics

  2  for table

  3  for all indexes

  4  for all indexed columns;

Table analyzed.

SQL> select * from item where create_by='SCM OL Maint Conv';

Execution Plan

----------------------------------------------------------                     

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2753 Card=7005 Bytes=2619870) 

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (TABLE) (Cost=2753 Card=7005 Bytes=2619870)

   2    1     INDEX (RANGE SCAN) OF 'IDX_ITEM' (INDEX) (Cost=38 Card=7005)

SQL> select * from item where item_class_id='ZX000';

Execution Plan

----------------------------------------------------------                     

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4237 Card=9875 Bytes=3693250)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (TABLE) (Cost=4237 Card=9875 Bytes=3693250)

   2    1     INDEX (SKIP SCAN) OF 'IDX_ITEM' (INDEX) (Cost=410 Card=9875)

Conclusion:

1. To make CBO use INDEX SKIP SCAN, you need to ANALYZE this table and indexes and columns.

2. Oracle says that skip scanning is advantageous if there are few distinct values in the leading column of the composite.