Index not used

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

Thanks

:confused:

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?

Quote:

---

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.

Quote:

---

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
:rolleyes:



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>

Quote:

---

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.

What data type is 'ACCNT_TYPE'?

Quote:

---

Originally posted by DaPi
Or maybe all the others are 'F'? In which case a histogram on the column should help.

---

The column is almost certainly massively skewed.
The table/column names remind me of an accounting package.

The optimizer thinks it will get 36087 rows back based on 'X' distinct values and 'Y' rows (where 36087 is 'Y'/'X').
It reads about 12,000 blocks (consistent gets), so is guessing it will pick up an average of three records per block. Since it thinks it will be picking up records from every block anyway, there's no point in using an index.

Suggest you get histograms on the column ...
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('MAGTAC','SSRFACC_NAME ',NULL,
DBMS_STATS.AUTO_SAMPLE_SIZE,FALSE,
'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',DBMS_STATS.DEFAULT_DEGREE,
'DEFAULT',TRUE);
END;