Local or Global Index on Primary Key

Hi All,

I bumped into below question and answer, while I am googling about local indexes.

I wonder if Oracle really locks all the partitions while doing insert/delete/update operations on primary key.

And why row level locking is not used?

Thanks.

Local or Global Index on Primary Key

I have a table of accounts that has 80 million records. I would like to partition the table by the ACCT_BY_DATE column. I will be going with a range partition and global indexes. My concern is regarding the primary key ACCT_ID. Should the index that will be created for the primary key be a local or a global index?

The index used to enforce the primary key can be local if and only if the partition key is, in fact, contained in the primary key. Otherwise, you will receive an ORA-14039 error (partitioning columns must form a subset of key columns of a unique index).

In this case, you are partitioning by ACCT_BY_DATE, but the primary key is ACCT_ID. The index used for the enforcement of that primary key must be a global index; there is no chance of using a local index.

If you think about the structure of a local index, the value ACCT_ID = 55could be in any partition. Hence, any insert would have to check all local
partitions for 55 and lock each index partition to ensure that no one even tries to insert ACCT_ID = 55 right now. It would be a huge scalability inhibitor, because only one person at a time could insert, update, or delete the primary key.

In a globally partitioned index, on the other hand, the value 55 can be in exactly one partition. Treat that partition the way you would any normal index, and you can achieve multiple insert, update, and delete operations at the same time.
-- Excerpt from:

http://www.oracle.com/technology/oramag/oracle/04-sep/o54asktom.html

--

http://www.freelists.org/webpage/oracle-l Received on Mon Mar 05 2007 - 03:19:02 CST