jump to navigation

Local Index Issue With Partitioned PK and Unique Key Constraints December 20, 2007

Posted by Richard Foote in Constraints, Index Access Path, Local Indexes, Oracle Indexes, Partitioning, Performance Tuning, Unique Indexes.

Nuno Souto (Noons) also asked a really interesting question on my Differences between Unique and Non-Unique Indexes blog entry (comment 4) that I thought it worthy of a separate blog entry to do the answer justice. The question was:

“Isn’t it still the case that unique indexes cannot be locally partitioned unless the partition key is part of the index key? Not sure if 11g removes this. If still so, that would weigh heavily in favour of non-unique indexing for PK on a table potentially requiring local index partitions.”

Simplistically, the answer to the first part is Yes it is still the case, even in 11g and the answer to the second part is No, it wouldn’t weigh heavily in favour of non-unique indexing for PK on a table requiring local index partitions. It wouldn’t actually be a consideration at all.

Let me explain why.

Firstly, there is a really really good reason why Oracle doesn’t allow us to create a Unique Index in which the Partition key is not part of a Local Index. It’s called protecting us from ourselves !!

Let’s start by mentioning constraints again.

Remember, the main reason we have indexes policing PK and Unique constraints is so that Oracle can very quickly and efficiently determine whether or not a new value already exists. Do a quick index look-up, is the value there, yes or no, allow the insert (or update), yes or no.

Just imagine for one moment what would happen if Oracle actually allowed us to create a Unique Local index in which the index didn’t include the partitioned column(s).

Lets say a table is Range Partitioned on column ‘A’ and we try and create a Unique Local index on just column ‘B’. Let’s assume we have (say) 500 table partitions meaning we must therefore have 500 local index partitions as well. When we insert a new value for our unique index for value B, it will attempt to do so in the corresponding local index partition as governed by the value A for the new row. However Oracle can’t just check this one index partition for uniqueness to ensure value of column B doesn’t already exist, Oracle would need to check all 500 index partitions because it would be possible for our new value of column B to potentially have previously been inserted into any of the other 499 partitions !!

Each and every insert into our partitioned table (partitioned by column A) therefore would require Oracle to check all (say)500 index partitions each and every time to check for duplicates of column B. Again, it’s important to understand that any given value of column B could potentially be in any of the 500 partitions, IF Oracle allowed us to create a Local Partitioned Index just on column B.

Checking all 500 index partitions looking for a specific value of column B would obviously be impractical, inefficient and totally un-scalable. Therefore Oracle doesn’t allow us to do this. It doesn’t allow us to create a Local index in which the indexed columns does’t include the partitioning columns as well.

This is actually a good thing.

If you want to create a Unique index in a partitioned table, you MUST either add all the partitioned columns and make it part of the LOCAL unique index (so that way each and every insert would only have to check the one local partition as this value is known now it’s part of the index) or you must create it as a GLOBAL index (in which again, Oracle only has to check the one index structure).

It actually makes a lot of sense to do this.

Moving onto the second part of the question. Let’s just use a Local Non-Unique index to police our PK constraints then.

Fortunately this isn’t allowed either for exactly the same reasons. You can’t create a Local Non-unique index to police a PK (or Unique) constraint if the Constraint does not also include the partitioned columns. Otherwise again, Oracle would need to check each and every index partition to determine whether the constraint has been violated or not.

If you attempt to use an existing Local Non-Unique index to police a PK or Unique constraint that does not contain the partitioned columns, you will get an error saying it can’t create the (by default Global index) because the useless Local Non-Unique index (from a policing the constraint point of view) already exists.

Again if you want to create a Non-Unique index to police a PK or Unique constraint you must either ensure the constraint includes all the partitioned columns in which case it can be Local or you must use a Global Non-Unique index.

In other words, the rules apply equally to both Unique and Non-Unique indexes.

So it’s not really a case of Oracle not allowing one to create a Local Unique index without including the partitioned columns (although that’s of course true) but really a case of Oracle not allowing a PK or Unique *constraint*  to be policed via *any* Local index (whether Unique or Non-Unique), unless the partitioned columns are also included.

Little demo to illustrate: Local Index Issue With Partitioned PK and Unique Key Constraints


1. Martin W - December 21, 2007

That’s a nice sum-up of the difficulties with primary keys and partitions.

I thought I’d comment that this issue with local partition indexing is also relevent for non-unique indexes, as I found to my cost.

“Checking all 500 index partitions looking for a specific value of column B would obviously be impractical, inefficient and totally un-scalable. Therefore Oracle doesn’t allow us to do this. It doesn’t allow us to create a Local index in which the indexed columns does’t include the partitioning columns as well”

This has caught me out with non-unique indexes on a partitioned table as well and highlights the fact that partitioning is often not good for general performance – unless most DML acces can use parition exclusion.
We had a table with initially 10 partitions, partitioned on a surrogate ID, 10M records in each partition. We also had a NAME column, with a non-unique, locally partitioned index. Everything was fine.
When we hit around 80 partitions I noticed the NAME lookup was slowing down. This is because Oracle was now having to check 100 index partitions, resulting in around 400 block reads with around 30% being physical if I rememeber correctly.
It was still fine for single look-ups but code that worked on thousands of records, based on NAME lookup, started to really suffer.
If I had bothered to Explain the name look up in the first place, rather than being simply happy that my reponse time was initially fine, I might have spotted the potential non-scalability before the table got so big that rebuilding the index as global (the answer) became a major undertaking.


2. Richard Foote - December 21, 2007

Martin, I think local indexes are often “overly promoted” as they potentially reduce maintenance overheads without due consideration for the performance penalties that can entail.

If partition pruning doesn’t reduce index probes, the local index overheads can be substantial, sometimes to the point of the CBO ignoring the index.

Your example is a classic example of a solution that appeared to be hunky dory initially while the number of partitions was small but quickly became problematic as the number of partitions increased.

When designing a partitioning solution one needs to be mindful of the number of partitions that might be needed going forward, be mindful of whether the partition column(s) is likely to be known (and hence whether partition pruning is likely) and design index structures accordingly.

Thanks for sharing your experiences.


3. Explanation of restriction of local indexes for unique keys on partitioned tables « Oracle Notes - November 24, 2010

[…] You can find the article here. […]


4. Microsoft – SQLServer – Partitioning – Index – Unique | Daniel Adeniji's Trail - January 20, 2011

[…] Local Index Issue With Partitioned PK and Unique Key Constraintshttps://richardfoote.wordpress.com/2007/12/20/local-index-issue-with-partitioned-pk-and-unique-key-co… […]


5. VMCD.ORG » Blog Archive » [转]Local Index Issue With Partitioned PK and Unique Key Constraints - December 27, 2011
6. Ragesh Hajela - August 15, 2013

Nice sum-up. But how is the performance of a non-unique local index on a non-unique field of a table?


7. Richard Foote - September 11, 2013

Hi Ragesh

Will it depends. With it being local, one thing that’s important is whether the partitioning key is known and hence whether one, some or all of the index partitions need to be accessed. Local indexes can be less efficient if we have to access lots of index partitions.


8. Alinda - September 13, 2013

Hi Richard,

Very good post, I like it very much.
But I can’t access the demo.
Could you give me an access?
Thanks a lot


Richard Foote - September 13, 2013

Hi Alinda

Well, as you liked the post so much, how can I say no 🙂 Should be available now.


9. Alinda - September 13, 2013

…and I can see a very good demo (same as usual), I love your demos…..
Thx for your quick response


Richard Foote - September 16, 2013

Hi Alinda

Thanks for the kind feedback 🙂


10. Johannes - August 3, 2018

Great explanation


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: