Primary key using a local partitioned index

Printable View

08-15-2003, 04:56 AM
mike9

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
08-15-2003, 05:08 AM
pando

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
08-15-2003, 05:11 AM
abhaysk

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.
08-15-2003, 05:35 AM
mike9

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.
08-15-2003, 05:44 AM
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...?
08-15-2003, 05:49 AM
guru_heaven

Quote:

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
08-15-2003, 06:39 AM
mike9

Quote:

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 )
08-15-2003, 09:35 AM
slimdave

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.
08-19-2003, 11:54 AM
tamilselvan

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
08-19-2003, 02:22 PM
marist89

Quote:

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..."

Quote:

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.
08-19-2003, 03:31 PM
slimdave

Quote:

Originally posted by tamilselvan
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

That's true, but direct joins between partitioned tables in a DW are pretty rare -- usually you see such things in OLTP systems.

For the sake of flexibility I'd go for non-prefixed indexes, especially if they are bitmap-type.
08-19-2003, 03:32 PM
slimdave

Quote:

Originally posted by marist89
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.

I don't think that there is any particular difference between prefixed and non-prefixed local indexes, with regard to what kind of partition operations require a partition rebuild, is there?
09-02-2003, 05:38 AM
Calvin_Qiu

Re: Primary key using a local partitioned index

Quote:

Originally posted by mike9
Hi,

I have a fact table which is range partitioned over the monthes.

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).

You may create an index with compress 1 in order of (time, project, customer) since you have a single time value in each partition. That provide good performance for project-based search and also enable index probing.