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...
Your right my point was the order of the columns realted to their cardinality. I'm also convinced that if that unique index is used the response time should be better. What I'm worried about is that the index becomes an non-prefixed index because the range key is not the left most key of the index (select index_name, alignment from user_part_indexes).
So what I'm realy wondering is the impact of having a non-prefixed index. Until now we only used prefixed PK indexes where the partition range key was the first key of the index.
Any predicate placed on the range partitioning key is automaticaly going to allow partition elimination in the query, and this applies to the indexes as well as the table. So don't get hung up on the time column having to be the left-most column of a composite index.
What you might like to do is adopt a common practice in DW fact tables of not having a real PK at all. You can declare a primary key (which aids the optimizer in making good decisions), but not enforce it, like ...
Then, create local btree indexes on the customer and the project columns respectively.
You might also place a bitmap index on the time column, which would aid some types of queries.
Originally posted by mike9 Pando, What do you exactly mean by "probe".
I can read pando's mind right now... "I'll show you probing..."
The index would be local so range partitionned the same way as the table and it would be unique. It would aslo containt the partition key but not in the first but last position of the index.
Also would you rather recommand to create 2 indexes:
a local prefixed unique (PK) index for ( time, project, customer)
and a local non-prefixed index for ( customer)
than only one index:
a local non-prefixed unique (PK) Index on ( customer, project, time )
This is also a trade-off between performance and administrative effort. The non-prefixed index would need to be rebuilt (in one form or another) almost every time you needed to do partition maintenance on the table.