Star Transformation

Microsoft Office document icon Star Transformation White Paper.doc987.5 KB


Hi - your paper is an interesting read, but I think there are two mistakes.


In the paper you say "An extra index is created on the column which is the logical primary key of the fact table" and then give this code:
create bitmap index pk_f_idx on w_syndd_ids_f_bkp(row_wid);
You are creating a bitmap index on the primary key of your fact table. This is 100% wrong. It is impossible to enforce a primary key constraint with a bitmap index, and you can't index the column again with a B*Tree index. So your fact table cannot have a primary key constraint. Moreover, using a bitmap index on a unique column is exactly the opposite of when bitmap indexes will be efficient. Under what circumstances do you think this bitmap index would ever be superior to a B*Tree index? You need to create a B*Tree index on your primary key column, and define a primary key constraint on it.


You say that star transformation can work without defining foreign key constraints. This is not always true. Foreign key constraints will improve the chance of getting better execution plans. Without this constraint information, there will be circumstances where the optimizer cannot do a star transformation, because it cannot be certain that all rows will be included if it does. Think of anti-joins, or any logic involving NOT EQUAL TO. I can't test this on your schema because you didn't give the full DDL, but I think you need to check this out and prove to yourself that foreign constraints will give better results, typically by reducing the necessity for table or partition scans.

To conclude, it is an interesting paper, but these points need to be corrected to increase its value.

Hi John

Thanks for taking the time to read through my posts and thanks for your kinds words. The entire point of my paper was to prove that Star Transformation can take place without integrity constraints. I understand that having a bitmap index in a unique column would traditionally not make sense , but it does have a better chance of making an index join or bitmap operations such as "bitmap or" and "bitmap and" and unless a "BITMAP CONVERSION FROM ROWIDS" takes place it is a better option for index joins or bitmap operations. Also Oracle itself states that there should be a bitmap index on each join column and too few bitmap index can hamper star transformation. I did try this approach in a few other projects with good success. Please refer to the following oracle link to read more about this

Thanks again


Another confirmation is that we did not create primary key constraints, we actually dropped the constraints and created bitmap indexes in their places, as has been rightly said by you we cannot have bitmap indexes and primary keys at the same time. We treated them only as logical primary keys (that is we knew which columns were supposed to be primary keys, but these columns did not have physical constraints on them)

Subrata :)