Primary key using a local partitioned index

[slimdave]

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.

[slimdave]

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?

[Calvin_Qiu]

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.