Re: PK on partitioned table

>From what I've read so far, LOCAL index should provide
> better performance (as opposed to a GLOBAL index).
> Is this correct?

No
Get the docs read and really test with partitioning. Lots of things to
consider.
 As with everything oracle, it depends.  Unique prefixed indexes can have
performance advantages over nonprefixed indexes by allowing the database to
access only one index partition to find the data it requires. As with any
index, the contents of the WHERE clause in a select statement can control
the efficiency of the index lookup.
I have lots of local indexes that aren't prefixed. So every query using
those indexes scans every partition and on a multi-cpu box it will be done
in parallel. There all bitmaps so it's still lightning fast. On one server
accessed ~30 000 hits/day on the partition table (range partition), users
access using their userid. A global index was necessary for performance
reasons. However, you then get the downtime associated with partition
maintenance.
I  think you can have a global pk or a local prefixed pk.

Mike

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: