Bitmap Indexes
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Bitmap Indexes

  1. #1
    Join Date
    Sep 2005
    Posts
    278

    Bitmap Indexes

    Hi everyone,

    A table structure of which two columns never gets modified, and the data rarely get deleted.

    One columns has very low cardinality with 5 distinct values and the other column has more than 10000 distinct values. A query involves predicates which involves both the columns, I want to create Bitmap Index on those two columns to utilize Bitmap Merge feature of Oracle. The data for the second Index is scattered globally, with around 1 row in 30-50 blocks. The data is already partitioned on other column. And the query will always use one partition.

    But the only concern is locking every day around 5-6 Millions of records are inserted, apporximately around 0.4 Millions in a single partition. Is it advisable to have local bitmap indexes on those two columns?

    Any input will really be very helpful.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What's the partitioning scheme?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Sep 2005
    Posts
    278
    Hi dave,

    its Hash partition.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    so it's partitioned on the column having 10,000 distinct values?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Sep 2005
    Posts
    278
    No, its not partitioned on that column.

    Sorry for not being more clear. The partition is on some other column and the column with 10000 distinct values is different.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    hmmm. it seems to me that you'd do well to list partition that table on the five distinct values and index it only on the 10,000 values column. Is that an option?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Sep 2005
    Posts
    278
    Most of the queries and also the query for which I'm considering using Bitmap indexes, utilizes the column on which the partition is done on the table. I dont feel any other column can replace the present partitioned column.

    The query is like:

    Code:
    SELECT col1....
    FROM tab
    WHERE partcol = 'XYZABC'
    AND dist5_col IN (20, 35, 75)
    AND dist10000_col = '20060504'
    Last edited by tabreaz; 09-12-2006 at 03:24 PM.

  8. #8
    Join Date
    Feb 2005
    Posts
    158
    "But the only concern is locking every day around 5-6 Millions of records are inserted, apporximately around 0.4 Millions in a single partition. Is it advisable to have local bitmap indexes on those two columns?"
    If the inserts are OLTP, then steer clear of bitmaps.
    Are subpartitions a possibility ?

  9. #9
    Join Date
    Sep 2005
    Posts
    278
    No subpartitions can't be applied, as the Table is presently partition using hash method.

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Quote Originally Posted by tabreaz
    Code:
    SELECT col1....
    FROM tab
    WHERE partcol = 'XYZABC'
    AND dist5_col IN (20, 35, 75)
    AND dist10000_col = '20060504'
    How many rows does the above SQL return?

    Tamil

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