Re: Table Partitioning, Part 1

On T, 2005-05-10 at 23:09 +0100, Simon Riggs wrote:
> On Tue, 2005-05-10 at 16:31 +0300, Hannu Krosing wrote:

> > > If all partitions in the query had identical indexes on them, then we
> > > have another option. In that case, each index could be thought to form
> > > part of a larger index ordered initially on the Partitioning Key (PPK).
> > > If the first column was actually the PPK, then the set of indexes would
> > > be exactly equivalent to a Global Index. We can call this a Pseudo
> > > Global Index.
> > >
> > > The Pseudo Global Index could also be used to enforce uniqueness. If all
> > > of the composite indexes were defined unique and the index contained the
> > > PPK as one of its columns, this would work.
> > >
> > > The index enforces
> > > uniqueness within each partition and the PPK enforces uniqueness across
> > > partitions because the same PPK value cannot be in two partitions.
> >
> > But only uniqueness of PPK, not any other columns.
>
> No, it would work for *any* set of columns that included the PPK.

What I meant was that it would quarantee the uniqueness of the whole set
only, not any other columns except the PPK. and in case PPK was itself
uniqe the other columns don't matter at all.

> > Still there may be cases where smarter access methods make sense as an
> > additional feture, though I cant come up with an example right now.
>
> Look at PPUC 2 Join partition elimination, which is the classic Fact to
> TimeDimension join.

Maybe using a global index (+ bitmap scan if number of matching rows is
large and not clustered) here is enough for stage 1 implementation.

As PPUC2 needs PE elimination step for each and every value, using
global index for "kind of PE" could be the most efficient way to do it
for quite large class of PPUC2 queries.

--
Hannu Krosing <hannu(at)skype(dot)net>