Re: Local or Global Index on Primary Key

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_at_gmail.com> 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
>
>

--
http://www.freelists.org/webpage/oracle-l