On Tue, 2005-10-04 at 18:16 +0200, Zeugswetter Andreas DAZ SD wrote:
> > Another possibility is optimizing for the special case of
> > indexing on a partitioning key. In this case, index values
> > would be very localized to one table, so just storing the
> > table info on each index page (or something similar) would work well.
>
> If you have the partitioning key in the index and the partitions don't
> overlap, it is better to create separate [unique] indexes on the
> subtables.
> Building separate indexes per partition is usually preferred because of:
> 1. performance of dropping a partition
> 2. smaller index for CE
...
> Imho the best solution would be a merge node,
> that merges results of
> several index accesses to avoid a sort and still use separate indexes.
> Such
> a merge node could probably also detect the case where accessing
> partitions in a certain
> order still produces ordered results.
Yes, that was my conclusion also.
There are a number of intermediate steps along the way, so it will take
some time to achieve it.
> Usually you will only want the "one big unique index" when the
> partitioning is not
> reflectable in the index keys, and then (also in other db's) such an
> index is usually a pain ...
Agreed^2. The idea of a global index is a non-starter for all of the
reasons that Tom gave and the main one: Its's unusably huge. There's no
point in partitioning a 1TB table if you then have to build a 500GB
index on it. The tree would be so deep that each insert would require
maybe 3 I/Os on index branch blocks before you got to the leaf. Insert
performance would suck real bad, which is a blocker since if you have a
large table you almost certainly have a lot of data to load. If you
don't have a big table you shouldn't be partitioning it anyway.
Best Regards, Simon Riggs