I have a Serious Trouble of Utilization the indexes..
Table is Media... Have records about 300,000 one Column is Media_Type that have Four Values Like
I have Index on this Column which is Normal Index ... I have tried to have the Bitmap Index, But both Times this Index is Not used... All tables and Indexs are Analyzed and this Column is Used in Where Clause...
Can any one please tell me why is not used... And Which is better Index for that... Either Normal or Bitmapped
Any help greatly appreciated.. Thanks
Even The Hint is Taking so long... With out any Hint Cost is 948... and here Cost is 3296
1 select /*+ Index (Media, Idx_MediaType_Media) */ media_id, barcode, media_type
2 from media
3 where media_type = 'VID'
4* and rownum < 5
MEDIA_ID BARCODE MEDIA_TY
======== ============================== ========
29 1959307 VID
31 1959304 VID
32 1959303 VID
33 1959302 VID
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3296 Card=62148 Byte
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MEDIA' (Cost=3296 Card
3 2 INDEX (RANGE SCAN) OF 'IDX_MEDIATYPE_MEDIA' (NON-UNIQU
E) (Cost=133 Card=62148)
Do a partition on the table.see to that you have all the audio/video etc or in the separate partions ...
You can see lots of improvement..
could you show me the ddl for the table?probably then we could work out a solution for you
As i can see is the second index use will reduce the cost drastically. As far as rules concerned, for a low cardinal field and if mostly u r using or predicate frequently then u should go for the Bitmap index. As u said these four values r mostly used then what i will suggest is to use a bitmap index.
More over , if see the execution plan the range scan value that is using the second index is more prefered. What u can do is , drop the b-tree(normal) index then run the query to find out the resource cost.
All the best.
Pramathesh Kumar Mohapatra
Sql Star International Ltd.
Stp Hyderabad. Andhra Pradesh
Click Here to Expand Forum to Full Width