Re: Restriction on table partition expressions

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(at)gmail(dot)com> wrote:
>> On Fri, 26 Aug 2022 at 03:08, James Vanns<jvanns(at)ilm(dot)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.