Home » RDBMS Server » Performance Tuning » bitmap index on unstable column
| bitmap index on unstable column [message #163456] |
Thu, 16 March 2006 15:30  |
a_developer
Messages: 194
Registered: January 2006
|
Senior Member |
|
|
I have a field which is best candidate for bitmap index since it has only 5 distinct values in 100M rows. Also, I would like to use this as a subpartition key (the table is already partitioned by range using trxn_date). However, this id sometimes changes. For instance, its value is now 545 then after a massive load/update, its value would change to 234 - meaning all rows with 545 value will all change to 234.
Is it still a good candidate for bitmap index and subpartition key? Also, if it is a good candidate for a partition key, would partition pruning be better in hash or list???
[Updated on: Thu, 16 March 2006 17:07] Report message to a moderator |
|
|
|
| Re: bitmap index on unstable column [message #163521 is a reply to message #163456] |
Fri, 17 March 2006 01:12  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
It is not a good candidate for (sub)partition key because when it changes en-masse, every row needs to be migrated from one partition to another. This requires twice the I/O of an update.
It's not an especially good column for any type of index. A bitmap index will require a massive re-write of two of the bit-strings, and a b-tree index would be useless in the first place with only 5 distinct values. If you have to bitmap index it though, then go ahead.
Remember that bitmap indexes are essentially USELESS on their own. You need multiple columns to be bitmap indexed and then combine predicates on those columns in your queries. eg.would not be effective using a bitmap index. ButWHERE bitmapcol1 = :val1
AND bitmapcol2 = :val2
_____________
Ross Leishman
|
|
|
|
| Re: bitmap index on unstable column [message #163556 is a reply to message #163521] |
Fri, 17 March 2006 04:11 |
a_developer
Messages: 194
Registered: January 2006
|
Senior Member |
|
|
I understood about the consequence of having it as a subpartition key. But can you please explain:
| rleishman wrote on Fri, 17 March 2006 01:12 |
Remember that bitmap indexes are essentially USELESS on their own.
|
I do have other bitmap indexes that I use with that column as predicates in my where clause. In this case, do you say that it is better than NOT making it as either bitmap or b-tree?
|
|
|
|
| Re: bitmap index on unstable column [message #163676 is a reply to message #163556] |
Fri, 17 March 2006 19:00 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Consider the situation where you want to read every row in a table. Clearly a FTS will be faster than reading every row via the index because the FTS does less I/O (the full-index-scan reads the entire index AND the entire table).
Now consider the case where you just want to read ONE row from the table that can be uniquely identified in the index. Assuming the table occupies more than a few database blocks, the Index Scan will be faster, right?
Somewhere in-between is the break-even point; the number of rows read that takes the same amount of time whether you read from the index or do a FTS. Quantifying this break-even-point is the source of the third great religious war in IT (beaten only by indentation methods and bracing techniques). Same say it is 10% of the table, some more, some less. Most experts now agree that it cannot be generalized; it is dependent on the size and width of the table and the index, the hardware, the database config, and the number of times your mother dropped you when you were a baby. My rule-of-thumb is index for <1%, FTS for >10%, benchmark in-between.
What's more, the break-even point does not significantly differ between b-tree indexes and bitmap indexes. If you read 30% of the table using a bitmap-index scan, it will almost certainly be slower than a FTS.
So, if you create a Bitmap Index with <=10 keys, such that each key represents >=10% of the table, then you will NEVER benefit from an index scan, FTS will ALWAYS be faster.
So, when should you use bitmap indexes?
Case 1
If you have >10 (pref > 100) keys, and each key represents <10% (pref <1%) of the table, then you will benefit from WHERE col = 'val'. In this case, you would also benefit from a b-tree index. In fact, the b-tree index would probably be more efficient.
Case 2
Same situation as above, but you want to use WHERE col IN ('val1', 'val2'). Bitmap indexes do well with this sort of thing, because they can BITAND() the val1 vector with the val2 vector to find the matching rows. A b-tree index would perform two index scans. Hard to say which would be faster, but both would be OK provided the total rows returned <1% of the table.
Case 3
If you have keys that (combined) represent >90% of the table and you want to run queries that exclude those keys: WHERE col NOT IN ('val1', 'val2', 'val3', 'val4'). b-tree indexes cannot do this, but bitmap indexes can. If the total remaining rows returned is less than 10% (pref <1%) of the table then the bitmap index usage will be efficient.
Case 4
You have multiple bitmap indexed columns and you want to combine them with ANDs, ORs, and NOTs such that fewer than 10% (pref <1%) of rows are returned. This is why bitmap indexes were invented. b-tree indexes cannot do it efficiently if they can do it at all.
_____________
Ross Leishman
|
|
|
|
 Re: bitmap index on unstable column [message #163724 is a reply to message #163676] |
Sat, 18 March 2006 21:51 |
a_developer
Messages: 194
Registered: January 2006
|
Senior Member |
|
|
thanks, Ross, for patiently coming up with a detailed explanation 
I've learned a lot from it.. mine falls under Case 4, and I agree with you:
| rleishman wrote on Fri, 17 March 2006 19:00 |
... it cannot be generalized; it is dependent on the size and width of the table and the index, the hardware, the database config, and the number of times your mother dropped you when you were a baby
|
[Updated on: Sat, 18 March 2006 21:53] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Tue Apr 16 02:14:46 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
