Did it require dropping the index before dropping a partition? Absolutely!! But of course every RDBMS has limitations. You accept and work around them, or migrate to a different RDBMS.
On 8/26/22 03:50, James Vanns wrote:
Thanks for that, David. It makes sense and no, it certainly wouldn't do to have a global index across all the partitions! It sounds like the key thing that needs highlighting is if the result of an _expression_ (function call in this case) cannot guarantee the uniqueness of the value across all partitions, then that is why it's forbidden. Cheers Jim On Thu, 25 Aug 2022 at 16:32, David Rowley <dgrowleyml@xxxxxxxxx> wrote:On Fri, 26 Aug 2022 at 03:08, James Vanns <jvanns@xxxxxxx> wrote:Also, is there a chance that this limitation will be relaxed in the future?(forgot to answer this part) Certainly not in the near future, I'm afraid. It would require allowing a single index to exist over multiple tables. There has been discussions about this in the past and the general thoughts are that if you have a single index over all partitions, then it massively detracts from the advantages of partitioning. With partitioning, you can DETACH or DROP a partition and get rid of all the data quickly in a single metadata operation. If you have an index over all partitions then that operation is no longer a metadata-only operation. It suddenly needs to go and remove or invalidate all records pointing to the partition you want to detach/drop. David
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
