Re: Unique constraint across all partitions?

Laurenz Albe schrieb am 26.01.2021 um 09:33:
>>> So, yes, so long as your unique constraint takes into
>>> consideration the partitioning scheme it will enforce uniqueness
>>> across all of the partitions. But the answer to the full, but
>>> possibly
>>> implied, question is no.
>>
>> This is why global indices are needed.
>
> Global indexes are likely more pain than gain.

While not extremely popular, many Oracle users do find them useful.

> They will make the best part of partitioning (attaching and detaching
> partitions) painfully slow. Hardly worth it to gain a tiny little bit
> of integrity. Within a partition, you can guarantee uniqueness easily.

I wonder why they would make the "best parts" slow?

I would expect that partition pruning or partition wise joins are unaffected
by that (which I do consider one of the "best parts")

Obviously the overhead of maintaining the index is there, especially when
dropping partitions this would mean a substantial overhead.

But it's up to the user to decide if the benefits (proper foreign keys, uniqueness)
outweigh the disadvantages.

Is there any other downside I am missing?

Would DML statements be affected (if you factor out the cost of maintaining
the global index)?

Thomas