Re: Local or Global Index on Primary Key
- From: "Ghassan Salem" <salem.ghassan@xxxxxxxxx>
- To: deryaoktay@xxxxxxxxx
- Date: Mon, 5 Mar 2007 11:07:45 +0100
Derya,
When you have a partitioned table, the primary key must either be
partitioned or global,
in both cases, the lock is taken as in a non-partitioned table.
The excerpt that you jave from asktom, explains why there is no support for
local unique keys when the partitionning column is not in the key. So oracle
does not lock any partition when you insert a row, as it does not lock the
table (non-partitioned one) when you insert a row in it.
The issue is that in order to support what you want to do, it would have to
lock all the partitions, in order to insure uniqueness, which is not
acceptable. (the row can be in different partitions, so a lock on a given
block/row is not sufficient).
rgds
On 3/5/07, Derya Oktay <deryaoktay@xxxxxxxxx> wrote:
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
Other related posts:
