-
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>
-
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.
-
> Can any one explain why index is not used
You mean because you hinted for it? Perhaps because your hint syntax is wrong?
-
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.
-
use the INDEX_COMBINE hint
-
> but a hint is just that a hint.
Care to explain what you mean by that?
-
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.
-
Originally posted by padders
> but a hint is just that a hint.
Care to explain what you mean by that?
I sense a flaming!
-
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>
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|