Primary key using a local partitioned index
I'm working in a DWH project with Oracle 9.2
The question is about the importance of the order of the keys of an local partitionned unique index used by the PK.
I have a fact table which is range partitioned over the monthes.
There are 3 dimensions attached to this fact table: time (month), customer and project. The primary key uses the dimension foreign keyes and is so based on the columns time, customer and project.
For each monthly partition I have about 1'000'000 entries related to 1'000'000 different projects, 500'000 customers and 1 time value (1st day of the month).
My question is: doest it have any bad impact for the optimizer if I create an index for the primary key with the partition range key at the last position of the index.
So it would be an index like:
create unique index pk_ft on fact_table(project_id, customer_id, time_id) local...
create unique index pk_ft on fact_table(time_id, project_id, customer_id) local...
Thanks for any feedback
Click Here to Expand Forum to Full Width