Re: Is there anything equivalent to Oracle9i's list

Greg,

I started a discussion on that topic (clustering/partitioning) a while
ago but it did not go anywhere.
My opinion was that such a scheme would also improve table scan when the
partitioning key was involved.

Maybe one day...

JLL

Greg Stark wrote:
>
> Christopher Browne <cbbrowne(at)acm(dot)org> writes:
>
> > The thing that would actually be truly /useful/ about this would be if
> > the partitioning scheme actually had some "physical" effects, as is
> > ...
>
> I'm not sure what you're saying, but in Oracle you could definitely have
> different storage set up for each partition. One of the common uses was to
> store each partition on a different tablespace. I think you could even have
> some partitions in read-only tablespaces and others in read-write tablespaces.
>
> > As it stands, this merely appears to be a little bit of non-standard
> > syntactic sugar layered on top of the use of a combination of VIEWs
> > with a "partition table."
>
> In fact prior to Oracle 8 the same feature was implemented precisely as you
> describe. The DBA had to manually create a view and enable an optimizer option
> that asked Oracle to check queries for accesses to a subset of the underlying
> views. The new implementation with first-class partition commands is much much
> cleaner and more flexible.
>
> And I'll say that having dealt with large rapidly growing tables the feature
> is an absolute life-saver. It means you can purge millions of old records
> almost instantaneously and with zero downtime. With transportable tablespaces
> you can then move the raw data to your DSS system and load it instantaneously
> as well.
>
> The partition key can also serve as a zero-space index that's as fast as a
> sequential scan. That can be a huge win when otherwise you would be stuck with
> the can't-win choice between doing a full table scan including extra records
> or the index scan of just the records you need.
>
> I think you were also able to play tricks with having different indexes on
> different partitions. We didn't need that though and in Postgres you have
> partial indexes which are fun to play with instead.
>
> But by far the biggest win was being able to purge old records in a single
> quick cheap operation.
>
> --
> greg
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)