That's true, but direct joins between partitioned tables in a DW are pretty rare -- usually you see such things in OLTP systems.
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.
For the sake of flexibility I'd go for non-prefixed indexes, especially if they are bitmap-type.
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?
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.
Re: Primary key using a local partitioned index
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.
Originally posted by mike9
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).
Oracle Certified Master - September, 2003, the Second OCM in China
*** LOOKING for PART TIME JOB***
Data Warehouse & Business Intelligence Expert
MCSE, CCNA, SCJP, SCSA from 1998
Click Here to Expand Forum to Full Width