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
