Index not used
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Index not used

  1. #1
    Join Date
    Dec 2000
    Posts
    126

    Question Index not used

    Can any one explain why index is not used ???

    Thanks



    17:17:13 SQL> set autotrace off
    17:17:22 SQL> l
    1* select count(*) from SSRFACC_NAME
    17:17:24 SQL> /

    COUNT(*)
    ---------
    252607

    17:17:25 SQL> select count(*) from SSRFACC_NAME where accnt_type='T';

    COUNT(*)
    ---------
    19

    17:17:46 SQL> CREATE bitmap INDEX MAGTAC.SSFRACC_NAME_ACCNTTYPE_BIDX ON MAGTAC.SSRFACC_NAME (ACCNT_T
    YPE)
    17:17:59 2 tablespace indx;

    Index created.

    17:18:07 SQL> analyze table ssrfacc_name compute statistics;

    Table analyzed.

    17:20:22 SQL> analyze table ssrfacc_name compute statistics for all indexes;

    Table analyzed.

    17:21:04 SQL> SELECT /*+ INDEX(SSFRACC_NAME_ACCNTTYPE_BIDX) */ (anal_a2), a2_name
    17:21:33 2 FROM
    17:21:33 3 ssrfacc_name a
    17:21:33 4 WHERE accnt_type ='T'

    17:21:39 SQL> set autotrace trace
    17:21:45 SQL> SELECT /*+ INDEX(SSFRACC_NAME_ACCNTTYPE_BIDX) */ (anal_a2), a2_name
    17:21:46 2 FROM
    17:21:46 3 ssrfacc_name a
    17:21:46 4 WHERE accnt_type ='T';

    19 rows selected.


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1917 Card=36087 Byte
    s=1515654)

    1 0 TABLE ACCESS (FULL) OF 'SSRFACC_NAME' (Cost=1917 Card=3608
    7 Bytes=1515654)





    Statistics
    ----------------------------------------------------------
    0 recursive calls
    5 db block gets
    12630 consistent gets
    11835 physical reads
    0 redo size
    1161 bytes sent via SQL*Net to client
    660 bytes received via SQL*Net from client
    5 SQL*Net roundtrips to/from client
    2 sorts (memory)
    0 sorts (disk)
    19 rows processed

    17:21:51 SQL>

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Not really an answer to your question, but you are using a bitmap index.. How many distinct values are in your indexed column? Only 19 out of a quarter of a million are 'T' makes me think your column is of high cardinality and should probably be a B*Tree. Possibly.

  3. #3
    Join Date
    Jan 2004
    Posts
    162
    > Can any one explain why index is not used

    You mean because you hinted for it? Perhaps because your hint syntax is wrong?

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    I didn't check you syntax on the hint, but a hint is just that a hint. If the cardinality is high then you should not use a bitmapped index, as waitecj suggested. Also if the index is really valid Oracle might use it without the hint. Try dropping the bitmapped index and creating a b*tree index.
    this space intentionally left blank

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    use the INDEX_COMBINE hint
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jan 2004
    Posts
    162
    > but a hint is just that a hint.

    Care to explain what you mean by that?

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    Originally posted by padders
    > but a hint is just that a hint.

    Care to explain what you mean by that?
    You can not force Oracle to follow a hint. i.e. You can't make Oracle use an index with a hint.
    this space intentionally left blank

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by padders
    > but a hint is just that a hint.

    Care to explain what you mean by that?
    I sense a flaming!
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Dec 2000
    Posts
    126

    Angry

    Normal index created , but still NOT USED




    SQL> create index MAGTAC.SSFRACC_NAME_ACCNTTYPE_BIDX ON MAGTAC.SSRFACC_NAME (accnt_type)
    2 tablespace indx;

    Index created.

    SQL> analyze table ssrfacc_name compute statistics;

    Table analyzed.

    SQL> analyze table ssrfacc_name compute statistics for all indexes;

    Table analyzed.

    SQL> set autotrace trace
    SQL> ed
    Wrote file afiedt.buf

    1 SELECT (anal_a2), a2_name
    2 FROM
    3 ssrfacc_name a
    4* WHERE accnt_type ='T'
    SQL> /

    19 rows selected.


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1917 Card=36087 Byte
    s=1515654)

    1 0 TABLE ACCESS (FULL) OF 'SSRFACC_NAME' (Cost=1917 Card=3608
    7 Bytes=1515654)





    Statistics
    ----------------------------------------------------------
    143 recursive calls
    5 db block gets
    12703 consistent gets
    11764 physical reads
    0 redo size
    1120 bytes sent via SQL*Net to client
    579 bytes received via SQL*Net from client
    5 SQL*Net roundtrips to/from client
    2 sorts (memory)
    0 sorts (disk)
    19 rows processed

    SQL>

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by waitecj
    Only 19 out of a quarter of a million are 'T' makes me think your column is of high cardinality . . . .
    Or maybe all the others are 'F'? In which case a histogram on the column should help.

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