Index not used /Hints get ignored
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Index not used /Hints get ignored

  1. #1
    Join Date
    Sep 2000
    Posts
    384
    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
    Radhakrishnan.M

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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:
    Code:
    select /*+ index(CUSTOMER_DEATILS IDX_DELIVERY_ID) */
    count(*) from CUSTOMER_DEATILS 
    WHERE STATUS_ID= 'A' 
    and DELIVERY_ID='F' ;
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Feb 2000
    Posts
    175
    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.

  4. #4
    Join Date
    Sep 2000
    Posts
    384
    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 ..
    Radhakrishnan.M

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    have not you read that your syntax is wrong???

  6. #6
    Join Date
    Feb 2000
    Posts
    175
    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.

  7. #7
    Join Date
    Sep 2000
    Posts
    384
    Thanks Moff ...
    Radhakrishnan.M

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width