SQL & PL/SQL

Hi ,

I am trying to understand index by running some examples and created 2 composite indexs; one with 2 columns(test_idx) and other on 3 columns(test_idx2).

create table test as select * from user_objects;

SQL> create index test_idx on test(temporary,object_id);

Index created.

SQL> create index test_idx2 on test(DATA_OBJECT_ID,OBJECT_TYPE,CREATED);

Index created.

SQL> analyze table test compute statistics;

Table analyzed.

Using only 1 column of test_idx in where clause, it used the index to access the table

SQL> select * from test where OBJECT_ID='9999';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 559079815

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    83 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    83 |     3   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | TEST_IDX |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=9999)
       filter("OBJECT_ID"=9999)


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

Here I can see from the predicate information(access("OBJECT_ID"=9999)) above that index has been used to locate the rows.

But when I run the sql below I get a different plan. If I do not use the column DATA_OBJECT_ID in the where clause, it seems to ignore the index.
Why is there a difference in execution plan for composite indexes created on 2 cloumns and that on 3 columns?

SQL>  select * from test where OBJECT_TYPE='YYYY' and CREATED='2011-07-12';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 217508114

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    83 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    83 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CREATED"=TO_DATE(' 2011-07-12 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "OBJECT_TYPE"='YYYY')


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

The Predicate Information does not talk about using the index.

ta