Oracle Class – Wissem's Oracle and NOSQL Tips – Tunisia's First Oracle, NOSQL Website

The following question appeared today on the Oracle Forum,

Oracle version is: 10.2.0.4
The hint /*+ index (t (STATUS) ) */ doesn’t work.
Table t has index with column status.

If I explicit set index name /*+ index (t IDX2) */ – works fine.
Why setting of column name does not work? Is there an additional condition for using it?

I have added this comment to highlight the post for interested readers.
According to the Oracle documentation, the syntax is correct:

References:

http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/hintsref.htm#CHDCCFCC

So I have asked the Original Poster – OP to provide me the table definition and the explain plan of both queries. Below you can find his / her answer:

create table BIG_TBL
(
owner VARCHAR2(30) not null,
object_name VARCHAR2(30) not null,
subobject_name VARCHAR2(30),
object_id NUMBER not null,
data_object_id NUMBER,
object_type VARCHAR2(19),
created DATE not null,
last_ddl_time DATE not null,
timestamp VARCHAR2(19),
status VARCHAR2(7) not null,
temporary VARCHAR2(1),
generated VARCHAR2(1),
secondary VARCHAR2(1)
)

create index IDX_BIG_TBL_CREATED on BIG_TBL (CREATED);
create index IDX_BIG_TBL_CREATED2 on BIG_TBL (CREATED, OBJECT_ID, STATUS, TIMESTAMP, DATA_OBJECT_ID);

set column name

SQL> explain plan for
2 select /*+ index(t (status)) */
3 status
4 from big_tbl t
5 where status is not null
6 ;

Explained

SQL> @C:\oracle\product\10.2.0\plan.sql
Plan hash value: 859430865
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 3383K| 22M| 15765 (1)| 00:02:54 |
| 1 | TABLE ACCESS FULL| BIG_TBL | 3383K| 22M| 15765 (1)| 00:02:54 |

set index name

SQL> explain plan for
2 select /*+ index(t IDX_BIG_TBL_CREATED2) */
3 status
4 from big_tbl t
5 where status is not null
6 ;

Explained

SQL> @C:\oracle\product\10.2.0\plan.sql
Plan hash value: 2228401533
—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | SELECT STATEMENT | | 3383K| 22M| 190K (1)| 00:34:56 |
| 1 | INDEX FULL SCAN | IDX_BIG_TBL_CREATED2 | 3383K| 22M| 190K (1)| 00:34:56 |
—————————————————————————————–

8 rows selected

I have simulated this on my test database and here are the results:

My conclusion: When you want to include the column name in the index hint, be sure that this column has a full index dedicated only and just for it. Otherwise, specify all the column names that have been part of the index itself. I am still learning from the optimizer

Thanks,
Wissem