Index Utilization... Normal or Bitmapped
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Index Utilization... Normal or Bitmapped

  1. #1
    Join Date
    Feb 2001
    Posts
    184
    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

    Audio 27188
    Video 206504
    Film 8000
    Docum 7800

    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
    QL> /

    MEDIA_ID BARCODE MEDIA_TY
    ======== ============================== ========
    29 1959307 VID
    31 1959304 VID
    32 1959303 VID
    33 1959302 VID

    rows selected.

    real: 531

    xecution Plan
    =========================================================
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3296 Card=62148 Byte
    s=2610216)

    1 0 COUNT (STOPKEY)
    2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MEDIA' (Cost=3296 Card
    =62148 Bytes=2610216)

    3 2 INDEX (RANGE SCAN) OF 'IDX_MEDIATYPE_MEDIA' (NON-UNIQU
    E) (Cost=133 Card=62148)

  2. #2
    Join Date
    Sep 2000
    Posts
    384
    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..
    Radhakrishnan.M

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    could you show me the ddl for the table?probably then we could work out a solution for you

    hrishy

  4. #4
    Join Date
    Feb 2001
    Posts
    7

    Post

    hi,

    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
    India

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