Re: PK on partitioned table

From: Michael McMullen <ganstadba_at_hotmail.com>
Date: Wed, 14 Apr 2004 15:26:06 -0400
Message-ID: <BAY9-DAV54UQ9cAgjHX000228f6@hotmail.com>

>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_at_freelists.org 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
-----------------------------------------------------------------

Received on Wed Apr 14 2004 - 14:25:39 CDT