It certainly does make sense to have global indices on partitioned tables. Rdd/VMS had them 20+ years ago, and they are (I still have two production systems using Rdb on OpenVMS) darned useful.
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@gmail.com> wrote:
On Fri, 26 Aug 2022 at 03:08, James Vanns <jvanns@ilm.com> 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.