Skip to Main Content
  • Questions

  • Local unique indexes on partitioned tables

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Alex.

Asked: October 27, 2017 - 5:48 pm UTC

Last updated: October 30, 2017 - 2:27 am UTC

Version: 11.2.0.1

Viewed 10K+ times! This question is

You Asked

Tom,

I'm wondering why Oracle requires the partitioning key on a local unique index.
I realize that internally Oracle doesn't want to have to scan all local index segments to check the constraint.
But why should the partitioning key need to be on the index itself? Why can't Oracle get its value from the table row behind the scenes?
It seems to me that they require the key on the index for optimization purposes. That's so unlike Oracle.
And adding the key to the constraint is not a solution, as it changes the meaning of the constraint.

How do people handle this (in my view) limitation? Why don't I see more complaints about this?

thanks
Alex

and Connor said...

It is a locking/concurrency issue. Let's consider a table with 5 partitions P1, P2, P3, P4 and P5, and we have allowed the concept of a local unique index *without* the partitioning key.

Someone does:

insert into MY_TABLE ( [values] )

where the key column would put this into (say) partition P2, and I need to make sure it will be unique.

So I will:

- check P1 for this value... not present, cool, I proceed
- check P2 for this value... not present, cool, I proceed
- check P3 for this value... not present, cool, I proceed
... so far, everything is looking good.

Now as I move to P4 ... someone else logs on, and inserts this SAME value into partition P1. I'll miss this because I have *already* checked that partition.

To overcome this, it means on *every single* insert, I need to lock the entire table (or lock the indexes in *all* partitions) so I can safely allow the insert to take place.

Not a good idea.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Good explanation but I need to clarify

Alex, October 29, 2017 - 3:33 pm UTC

I understand that checking all partitions for uniqueness creates a locking/concurrency issue.
My argument is that Oracle can still check only the affected partition without having the partitioning key on the index definition.
Oracle could simply read the key from the table row during the DML, use it to identify the single partition to check, and then discard it (not save it on the index row).
Aren’t all index values coming from the table row during DMLs? Why not read one more on the fly, the partitioning key, for unique locally partitioned indexes?
To me it sounds like Oracle chose not to go the extra mile.
Connor McDonald
October 30, 2017 - 2:27 am UTC

My argument is that Oracle can still check only the affected partition without having the partitioning key on the index definition


The "affected" partition ? And which is that ? Don't forget the base premise here - you want to have a local partition, which means the index entries only know about the *local* table data.

So the index entry you are about to insert is:

(keyval, restricted rowid) ... ie, that rowid has no knowledge about the other partitions. That is what *makes* it a local index.

So the index values give me nothing. Let's now grab the partition key from the row I'm about to insert. So now I have the partition for the *new* row....I still don't have the partition for any *existing* row that might have that key. I have to search each partition...and that means I have to lock them all.

But lets say all of these problems can somehow be overcome...and that (say) as I insert a key value into partition P2, then I magically have an algorithm that tells me I need to look in (say) partition P4 to know if there will be a clashing key. (All of this being done *without* storing the partition key in the index itself)

We still end up with a fundamental problem. What if P4 is in a tablespace that is offline. What is P4 is being recovered ? We've broken a key principle of partitioning and local indexes....insulation from each other.



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.