-
Primary key using a local partitioned index
Hi,
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...
instead of:
create unique index pk_ft on fact_table(time_id, project_id, customer_id) local...
Thanks for any feedback
Mike
-
this is not gonna help you but I think in DWH for easier management you should create non-unique index then add the PK constraint to enforce uniqueness
-
Pando :
Hez conserned about order of the COLS he shud index.
Well I think Mike, put the high Card Col in first place, it will suerly better response time.
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Abhay,
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.
Last edited by mike9; 08-15-2003 at 05:44 AM.
-
advantage of non-prefixed index:
· Useful when fast access needed when query column not based on table partition key
· Good for historical databases
disadvantage:
· More expensive to probe than prefixed index
· Nonunique nonprefixed must scan all partitions to get the row
Also do you know the way you create your PK, on the day you have to disable it for any reason you would lose the whole index and you have to build the index for your whole fact table...?
-
Originally posted by pando
advantage of non-prefixed index:
· Useful when fast access needed when query column not based on table partition key
· Good for historical databases
disadvantage:
· More expensive to probe than prefixed index
· Nonunique nonprefixed must scan all partitions to get the row
Also do you know the way you create your PK, on the day you have to disable it for any reason you would lose the whole index and you have to build the index for your whole fact table...?
You are good, learning new things everyday
-
Originally posted by pando
· More expensive to probe than prefixed index
Pando, What do you exactly mean by "probe".
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 )
Last edited by mike9; 08-15-2003 at 08:15 AM.
-
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 ...
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;
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.
-
A local prefixed index would give better performance than non-prefixed local index, if the 2 (or more) partitioned tables are joined on the partition key columns.
You will see different execution plans when 2 tables are joined on the partition key columns.
Tamil
-
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.
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|