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