Oracle Index with multiple Columns querying on single column

Question

In a table in our Oracle installation we have a table with an index on two of the columns (X and Y). If I do a query on the table with a where clause only touching column X, will Oracle be able to use the index?

For example:

Table Y: Col_A, Col_B, Col_C,

Index exists on (Col_A, Col_B)

SELECT * FROM Table_Y WHERE Col_A = 'STACKOVERFLOW';

Will the index be used, or will a table scan be done?

Answer 1

It depends.

You could check it by letting Oracle explain the execution plan:

EXPLAIN PLAN FOR 
   SELECT * FROM Table_Y WHERE Col_A = 'STACKOVERFLOW';

and then

select * from table(dbms_xplan.display);

So, for example with

create table table_y (
  col_a varchar2(30),
  col_b varchar2(30),
  col_c varchar2(30)
);

create unique index table_y_ix on table_y (col_a, col_b);

and then a

explain plan for
  select * from table_y
  where col_a = 'STACKOVERFLOW';

select * from table(dbms_xplan.display);

The plan (on my installation) looks like:

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    51 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLE_Y    |     1 |    51 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TABLE_Y_IX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("COL_A"='STACKOVERFLOW')

ID 2 shows you, that the index TABLE_Y_IX is indeed used for an index range scan.

If on another installation Oracle chooses to use the index is dependend on many things. It's Oracle's query optimizer that makes this decision.

Update If you feel you're be better off (performance wise, that is) if Oracle used the index, you might want to try the + index_asc(...) (see index hint)

So in your case that would be something like

SELECT /*+ index_asc(TABLE_Y TABLE_Y_IX) */ * 
  FROM Table_Y 
 WHERE Col_A = 'STACKOVERFLOW';

Additionally, I would ensure that you have gathered statistics on the table and its columns. You can check the date of the last gathering of statistics with a

select last_analyzed from dba_tables where table_name = 'TABLE_Y';

and

select column_name, last_analyzed from dba_tab_columns where table_name = 'TABLE_Y';

If there are no statistics or if they're stale, make yourself familiar with the dbms_stats package to gather such statistics.

These statistics are the data that the query optimizer relies on heavily to make its decisions.