| Bitmap Join Index for DW [message #440576] |
Mon, 25 January 2010 20:45  |
summoner
Messages: 44
Registered: March 2009
|
Member |
|
|
Dear all,
I have a fact F1 that links to multiple dimension D1,D2,D3,D4,D5,D6. F1 has foreign keys FK_D1 to D1, FK_D2 to D2, etc. D1 has attributes D1_1, D1_2, D1_3, etc. Each day user can run a report that links up with those dimensions and the users complain the performance is low (2~4 minutes for running a "simple" report  ). Therefore, I would like to apply the bitmap join index to boost up the speed
Each dimension has at most 10000 records and the fact tables have 10 million records. Do I need to add all the columns for each dimensions in the index because I am not sure which field the user are looking for
CREATE INDEX "MIX"."Fact_IDX01" ON F1 (
"D1_1",
"D1_2",
"D1_3",
"D2_1")
FROM F1, D1, D2
WHERE
F1.FK_D1 = D1.PK AND F1.FK_D2 = D2.PK
;
Thanks
|
|
|
|
| Re: Bitmap Join Index for DW [message #440579 is a reply to message #440576] |
Mon, 25 January 2010 22:19  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>WHERE F1.FK_D1 = D1.PK AND F1.FK_D2 = D2.PK
For a child row to exist (FK), a corresponding row in parent table (PK) must exist.
Correct?
So this WHERE clause matches every row in D1 & D2 tables.
Correct?
So what is the purpose of this WHERE clause?
In a composite index, the order of the columns in the index matter.
Why is your order optimal?
Why is a composite index better than individual indexes in this case?
[Updated on: Mon, 25 January 2010 22:31] Report message to a moderator |
|
|
|
|
|
| Re: Bitmap Join Index for DW [message #440732 is a reply to message #440579] |
Tue, 26 January 2010 20:30 |
summoner
Messages: 44
Registered: March 2009
|
Member |
|
|
Thanks for help.
Sorry for careless mistakes that the query should be CREATE BITMAP INDEX
BlackSwan wrote on Mon, 25 January 2010 22:19>WHERE F1.FK_D1 = D1.PK AND F1.FK_D2 = D2.PK
For a child row to exist (FK), a corresponding row in parent table (PK) must exist.
Correct?
So this WHERE clause matches every row in D1 & D2 tables.
Correct?
So what is the purpose of this WHERE clause?
Isn't that Cartesian Product will take place if WHERE clause is missing?
Quote:
In a composite index, the order of the columns in the index matter.
Why is your order optimal?
Why is a composite index better than individual indexes in this case?
We have a general query that retrieve all data users want and the query takes around 2~4 minutes to run. I check some documents saying that bitmap join index can improve the performance for DW and therefore i check whether it suits what we need.
|
|
|
|
|
|
| Re: Bitmap Join Index for DW [message #441234 is a reply to message #440576] |
Fri, 29 January 2010 10:51 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Your situation is a common DW query scenario. You have a fact table and lots of dimension tables. You should understand first how bitmap indexes work before you look at bitmap join indexes. 9 times out of 10, in a DW your best bet is to use plain jane bitmap indexes. So let me ask you this...
Quote:for each of your foreign keys, have you defined a bitmap index on the fk column?
If so then you should be getting the best performance possible for the maximum number of potential queries.
In a DW situation, there are basic steps in query execution (this is not the oracle steps).
1) figure out which rows to get
2) get the rows
For a DW, step 1 presents special problems because of the "free for all" nature of queries in a DW. This "free for all" nature you have described well in your scenario. Bitmap indexes are designed to fix the problem of step 1 above by making the finding of rows to fetch from the fact table fast given the fact that there are no traditional indexing scenarios that can be used in DW queries.
But bitmap indexes do nothing for step 2 because once you know you need 2 million rows, you need 2 million rows and it is gonna take pretty much the same amount of time to get the 2 million rows regardless of how your figured out they were the ones you needed.
Fetching 2 million rows out of 10 million rows will take some time.
Fetching 2 thousand rows out of 10 millino rows will take much less time.
One of the reasons to use a bitmap JOIN index is to prejoin fact and dimension tables so you can "reverse" the order of access in a query such that you avoid going back to a dimension table with a join once fact rows are found. In the right situation and done correctly this can often result in an order of magnitude improvement in query performance. However, the use of bitmap join indexes in practice is not as straight forward as the "simple" examples you will find online suggest. Bitmap Join indexes present special problems of their own which you must account for in order to reap their benefits. In one way at least, bitmap join indexes go backwards in the general theory that bitmap indexes promote. You will need to study up on bitmap join indexes and do the necessary testing on your own in order to know if your situation warrants their use.
For now I would suggest you make certain your system has the correct set of bitmap indexes in place on your fact table and that statistics have been correctly collected. In short, make sure your basics are correct before you move on to advanced features.
For all my talking I would point out the following:
few people atually understand the reason for bitmap indexes. They were create to query data in a situation where random queries were possible across a large number of access paths. Consider this fact table of the OP. The OP's fact table has six (6) foreign key columns. This means six dimension tables (or at least six dimensional references).
The idea of a DW (Data Warehouse) is to slice and dice the fact table by its dimensions. Thus a query using any combination of dimensions is possible and all queries are equally important. How many different combinations is this then? Lots. In order to find rows fast, you would need hundreds of indexes in the OPs scenario to cover all possible query combinations. Not doable. But with bitmap indexes you need only six indexes (one for each FK) and as long as dimensions are small < 10,000 rows lets day, you get close to maximum performance for every query you issue.
The key to using bitmap indexes right, it not in understanding the bitmap index access mechanism.
The key to using bitmap indexes right, is in understanding the special query environment a DW presents.
Once you understand the DW query environment, you can realize why bitmap indexes work in a DW. Once you understand this, you can understand why a bitmap join index is a very special animal and why it is actually a step backwards in its philosophy of the bitmap access of a table. It is a highly specialized tool good for very special situations and as such maybe 1/10th of 1% of all oracle systems will actually have cause to use it.
Good luck, Kevin
[Updated on: Wed, 03 February 2010 10:56] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
