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