Index not used

[culonbu]

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>

[waitecj]

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.

[padders]

> Can any one explain why index is not used

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

[gandolf989]

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.

[slimdave]

use the INDEX_COMBINE hint

[padders]

> but a hint is just that a hint.

Care to explain what you mean by that?

[gandolf989]

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.

[slimdave]

Originally posted by padders
> but a hint is just that a hint.

Care to explain what you mean by that?

I sense a flaming!

[culonbu]

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>

[DaPi]

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.