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.Code:Alter Table my_fact Add Constraint xpkmy_fact Primary Key ( time, customer, project ) Disable Novalidate; Alter Table my_fact Modify Constraint xpkmy_fact Rely;
You might also place a bitmap index on the time column, which would aid some types of queries.




Reply With Quote