Best Index type to use when creating index
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Best Index type to use when creating index

  1. #1
    Join Date
    May 2001
    Posts
    28

    Question

    We're trying to determine which type of index (B-tree, Bitmap, etc..) would be better for an attribute that will only to values of either "Y" or "N" on a table that has 3.5 million records and growing. We are estimating an average of 500K with value of "N", the rest "Y".

    Thanks in advance for your help on this?

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Well, the B*tree index would be totaly useless with this kind of data distribution. Tho only usable option would be bitmap.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jun 2001
    Posts
    132
    BITMAP index definitely.
    You think I'm going to have an affair with you? --Stanley Kowalski

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    But if your database is OLTP you can forget about BITMAP indexes, would be too slow in DML operations

  5. #5
    Join Date
    May 2001
    Posts
    28

    Thumbs up

    Thank you ALL for your response. BITMAT is it. I will look into the syntax now. Any examples out there?

  6. #6
    Join Date
    Aug 2000
    Posts
    462
    What do you hope to gain by indexing? Neither option is likely to produce much benefit if any. BITMAP is a disaster in OLTP.
    Oracle DBA and Developer

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Unfortunately, it sounds as if you are taking an overly-simplistic view here.

    A bitmap index in isolation is a waste.

    First, you need to know if you have an OLTP or a DSS database. If OLTP, as others have emphasized - NO BITMAPS!!!!! Period.

    Conversely, if you are DSS, then you should probably have LOTS of bitmaps. Bitmaps not only work better on low selectivity columns, they work better in groups.

    Here is a thread where jurij and I discuss bitmap indexes in start schemas - this is applicable for most DSS situations in general, however.

    http://www.dbasupport.com/forums/sho...threadid=11745

    - Chris

  8. #8
    Join Date
    May 2001
    Posts
    28
    We have a Data Warehouse that updates once a day with batch processing. There is a cursor that processes the table with 3.5 million records and it is using sequential scan and not the associated binary index because of NULLS in one of the concatenated indexed columns.

    Given this, is BITMAP still the best and most efficient type of index we can use?

    Thanks!!

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Please take the time to read the thread.

    - Chris

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    if the distinctveness is low you can use bitmap otherwise b*tree

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