| Bitmap Indexes [message #129044] |
Thu, 21 July 2005 12:57  |
oratech
Messages: 3
Registered: July 2005
|
Junior Member |
|
|
Folks..I have some confusion in the working of the Bitmap Indexes.
A table has 10 Million rows. There is 1 low cardinalty column - specifying the gender (m/f). And there's a bitmap index built on the gender column of the table.
My question is what would happen to the bitmap index in the following scenarios-
1. If the table has more than 90% of the rows with 'm' value in the gender -
a. A query is issued to retrive the columns with 'm' value.
b. A query is issued to retrive the columns with 'f' value.
2. If the table has 70% of the rows with 'm' value in the gender-
a. A query is issued to retrive the columns with 'm' value.
b. A query is issued to retrive the columns with 'f' value.
3. If the table has 50% of the rows with 'm' value in the gender-
a. A query is issued to retrive the columns with 'm' value.
b. A query is issued to retrive the columns with 'f' value.
I was thinking that in case (1) for the query with 'f' the index w'd be used but I was surprised to see FTS for both options of case-1.I checked for this working on Oracle9.2.0.4 on linux 9.
My table has only two columns -age and sex.
(and the query is - select * from t where sex ='m')
Thanks,
|
|
|
|
| Re: Bitmap Indexes [message #129047 is a reply to message #129044] |
Thu, 21 July 2005 13:30  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
This is similiar to the discussion in the link Girish provided in another thread just a short time ago.
Don't think of it in terms of percentages of rows and rules of thumb. It depends more on other factors, such as where those rows are physically located. It is more about blocks.
Also, you have to make sure you gather full statistics, including histograms, so that oracle knows your data distribution. You may know there is 90% of one and 10% of the other, but how can oracle unless you gather statistics.
|
|
|
|
| Re: Bitmap Indexes [message #129063 is a reply to message #129047] |
Thu, 21 July 2005 15:37 |
oratech
Messages: 3
Registered: July 2005
|
Junior Member |
|
|
Hi smartin,
I appreciate your response.
My understanding is -
When there's low cardinality use Bitmap Indexes.
When the data is heavily skewed use histograms.
In my eg. I've created Bitmap Index as suggested. Now do I need to have the histogram too done? Does it imply that everytime (or almost all times) a bitmap index is used histogram has to be created?
Yes, I agree that there are plenty of factors that influence the CBO to use or to not use the index as rightly pointed by the article shown in the link given by Girish. But that article has not taken me anywhere with regard to when one must create an index.
Thanks,
Srinivas
[Updated on: Thu, 21 July 2005 16:03] Report message to a moderator |
|
|
|
| Re: Bitmap Indexes [message #129066 is a reply to message #129044] |
Thu, 21 July 2005 16:09 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
This thread also talks about issues relating to this:
http://www.orafaq.com/forum/t/49161/67982/
Basically it is not about cardinality of rows, but cardinality of blocks. How many different blocks does it take to store all of the different rows with a given index value.
Also, realize that this is a topic of considerable discussion every since bitmaps came out. Jonathan Lewis, Tom Kyte, and many countless others have written about it at length. Expect to see test results and proof though whenever you read something from someone, as many folks have written incorrectly about this topic (same goes for lots of things actually).
Actually I would think in many situations sex would be fairly 50-50. Not to mention fairly spread out across the table, unless your data was sorted by sex. But the real power comes in when using multiple bitmap indexed columns in the same query.
Say you have sex. But also hair color. And eye color. And race. And facial hair. So in a 100,000 row table, 50k (corresponding to lots of blocks) might be of one sex. But how many rows (blocks) are the combo of a given sex and hair and eye and race and facial hair. That is the real power.
Suggest you read more of the many in-depth discussions on this topic. Start with the concepts guide or data warehouse guide chapters that discuss bitmap indexes. Then follow up with the above authors. Can do further searching if you want.
With regards to histograms. I have come to the belief that, assuming you are not under any time crunch to actually gather your statistics, that you should then gather as much as you can and provide the CBO with as many details as possible. That means my standard way of gathering statistics is to use:
dbms_stats.gather_table_stats(owner,table,cascade=>true,method_opt=>'for all columns size 250');
[Updated on: Fri, 22 July 2005 08:55] Report message to a moderator |
|
|
|
| Re: Bitmap Indexes [message #129067 is a reply to message #129066] |
Thu, 21 July 2005 16:45 |
oratech
Messages: 3
Registered: July 2005
|
Junior Member |
|
|
Thanks much for your quick and good response smartin ..I really appreciate it!
It was good to have pointed out the true power of bitmap with an example.
Thanks,
Srinivas
|
|
|
|
| Re: Bitmap Indexes [message #129547 is a reply to message #129044] |
Mon, 25 July 2005 13:57 |
rkl1
Messages: 97
Registered: June 2005
|
Member |
|
|
As little I know about anything including oracle, here are my suggestions:
1. It is indeed important how many rows we will be getting out of this query. If it is more than 5% rows (oracle gurus differ) then CBO will be more inclined to use the FTS rather than index whether bitmap or btree.
2.True power of bitmap comes from anding the columns in the query Like: where gender='M' and age='100' etc. Anding, oring or noting also works.According to great Jonathan Lewis (may he live as long as Oracle in market place so we all have a job), a single bitmap index is useless unless it adds with other predicates to lower the number of rows in the result set.
3. If you change your table structre, means build a table with either range partitioning on age and list subpartitioning on gender. Or else you may only build the table with range partitioning on age. And then build local composite btree or single local bitmap index. since your query involves with age predicate, it would go for partition prunning and even if index not used, FTS would be limited to far less number of rows.
what else: enabling parallel query option and etc, etc. forgive my I am slipping away to other teretories.
thanks.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
