Index not used /Hints get ignored

[omegamark]

The table customer_details and the all the indexes are analyzed ..

but still the index is ignored and hints are also ignored ...
where am i wrong ...

I do not want to create a composite index .. I already have it in the same table with delivery_id and another column


SQL> SELECT COUNT(*) FROM CUSTOMER_DETAILS ;

COUNT(*)
----------
1366399


SQL> select count(*) from CUSTOMER_DETAILS WHERE STATUS_ID= 'A' and DELIVERY_ID='F' ;

COUNT(*)
----------
838

SQL> set autotrace on
SQL> /

COUNT(*)
----------
838


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2246 Card=1 Bytes=2)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'CUSTOMER_DETAILS' (Cost=2246 Card=1
13867 Bytes=227734)


select /*+ index(IDX_STATUS_ID,IDX_DELIVERY_ID)*/ count(*) from CUSTOMER_DEATILS WHERE STATUS_ID= 'A' and DELIVERY_ID='F' ;

COUNT(*)
----------
838


select /*+ index(IDX_STATUS_ID)*/ count(*) from CUSTOMER_DETAILS WHERE STATUS_ID= 'A' and DELIVERY_ID='F' ;



Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2246 Card=1 Bytes=2)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'CUSTOMER_DETAILS' (Cost=2246 Card=1
13867 Bytes=227734)


SQL> select TABLE_NAME,INDEX_NAME ,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where table_name='CUSTOMER_DETAILS' ;

TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION
----------------
CUSTOMER_DETAILS IDX_STATUS_ID STATUS_ID 1
CUSTOMER_DETAILS IDX_DELIVERY_ID DELIVERY_ID 1
CUSTOMER_DETAILS IDX_DELIVERY_ID START_DATE 2

[marist89]

Typically, hints are not used if the syntax is wrong as is your case.

Check http://otn.oracle.com/docs/products/...elem.htm#43697 for syntax.

Your query should be:

select /*+ index(CUSTOMER_DEATILS IDX_DELIVERY_ID) */
count(*) from CUSTOMER_DEATILS 
WHERE STATUS_ID= 'A' 
and DELIVERY_ID='F' ;

[moff]

As STATUS_ID is first in the where clause should the query not read....

select /*+ index(CUSTOMER_DEATILS IDX_STATUS_ID) */
count(*) from CUSTOMER_DEATILS
WHERE STATUS_ID= 'A'
and DELIVERY_ID='F'

Moff.

[omegamark]

Thanks to Marist and Moff .

Moff the execution was correct since the status_id is first ...

but what is wrong in

select /*+ index(IDX_STATUS_ID)*/ count(*) from CUSTOMER_DETAILS WHERE STATUS_ID= 'A' and DELIVERY_ID='F' ;


and why it does not use the index ..

[pando]

have not you read that your syntax is wrong???

[moff]

Hi,

The syntax for an index hint is -

/*+ INDEX ( tablename indexname) */

Your statement is incorrect as it only states the indexname. Add the tablename to the statement and it should use the index you require.

Cheers
Moff.

[omegamark]

Thanks Moff ...