Oracle index skip scan

Before we had 9i a composite index practically create left most implicit indeces for the columns in the indexes. We know that we should sort the columns in a composite index with the column with the lowest cardinality to the left. Starting in 9i Oracle introduced a new index scan method called "index skip scan". Even though it's been available for a just over 4 years I haven't seen much news or design articles about this method. With a skip scan you can use any part of a composite index in (almost) any type of query.
It comes with a cost of course since Oracle will have to probe the index the same number of times as the leading column(s). I.e. if you leading column have 10 distinct values, Oracle will have to do 10 probes in the binary tree. Skip scans should usually be avoided in extremly heavy use queries but may be a nice feature to keep in mind for reporting queries and other lower use queries.
The benefit is that you can avoid having several indexes on the same columns, saving you update time and a bit of disk space.

Let us do a little test

OPS$HLINDEN@orcl> create table ct as select * from all_objects;

Table created.

OPS$HLINDEN@orcl> insert into ct select * from ct;

...

OPS$HLINDEN@orcl> commit;

Commit complete.

OPS$HLINDEN@orcl> create index ct_test_idx 
  2  on ct(owner,object_type,object_name);

Index created.

OPS$HLINDEN@orcl> EXEC DBMS_STATS.gather_table_stats('OPS$HLINDEN', 'CT');

PL/SQL procedure successfully completed.

OPS$HLINDEN@orcl> set autotrace traceonly exp
OPS$HLINDEN@orcl> select status from ct where owner='OPS$HLINDEN'
  2  and object_type='TABLE' and object_name='T9';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=49)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CT' (TABLE) (Cost=4 Card
          =1 Bytes=49)

   2    1     INDEX (RANGE SCAN) OF 'CT_TEST_IDX' (INDEX) (Cost=3 Card
          =1)

OPS$HLINDEN@orcl> select status from ct where object_name='T9';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=44 Card=29 Bytes=9
          57)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'CT' (TABLE) (Cost=44 Car
          d=29 Bytes=957)

   2    1     INDEX (SKIP SCAN) OF 'CT_TEST_IDX' (INDEX) (Cost=15 Card
          =29)

Notice the extra cost for scanning the index, eventhough we have fewer conditions we have a five fold cost. Now if the index would have been created backwards ,like ct(object_name,object_type,owner), Oracle would hesitate to even use it unless all three columns had conditions since the number of left most distinct values would have been to high (about 14k in this example, with only between 20 and 25 for the other two columns).

Skip scans can be good, but for intense queries do create indexes on the required columns.

Read more here.