Re: Is there anything equivalent to Oracle9i's list partitioning?

Christopher Browne <cbbrowne@acm.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