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
--
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London