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

In an attempt to throw the authorities off his trail, jbearer(at)tribweb(dot)com (Jeff Bearer) transmitted:
> I tried to post this a few days ago but I don't think it made it to the
> list, if It did I apologize.
>
> I've recently learned that Oracle has the ability to partition table
> data, Oracle9i's list partitioning feature.
>
> http://www.oracle.com/oramag/oracle/02-jan/index.html?o12part.html
>
> I'm trying to find out if Postgres or any open source database has
> something equivalent to this.

This more or less corresponds to table inheritance, which does
similar, though not identical, things.

Extracted from Ch 8 of the documentation...

"Let's create two tables. The capitals table contains state capitals
which are also cities. Naturally, the capitals table should inherit
from cities.

CREATE TABLE cities (
name text,
population float,
altitude int -- (in ft)
);

CREATE TABLE capitals (
state char(2)
) INHERITS (cities);

In this case, a row of capitals inherits all attributes (name,
population, and altitude) from its parent, cities. The type of the
attribute name is text, a native PostgreSQL type for variable length
ASCII strings. The type of the attribute population is float, a native
PostgreSQL type for double precision floating-point numbers. State
capitals have an extra attribute, state, that shows their state. In
PostgreSQL, a table can inherit from zero or more other tables, and a
query can reference either all rows of a table or all rows of a table
plus all of its descendants."

What Oracle's version of this does that doesn't appear to be well
documented (or which may not exist) is the notion of putting data into
CITIES and having PostgreSQL automagically recognize that it should
actually be considered to be in CAPITALS.

In a sense, this isn't /all/ that interesting; you could "partition" a
table

create table DEPTS (DEPTID NUMBER,
DEPTNAME VARCHAR2(20),
STATE VARCHAR2(2));

by creating views:

create view northern_depts as select * from depts where state in ('AK');
create view southern_depts as select * from depts where state in
('TX', 'MS', 'FL', 'GA');
create view loony_depts as select * from depts where state in ('MT', 'CA');

It also wouldn't be too difficult to build a "partition table" P_TABLE
so that the views would be like:

create view northern_depts as select * from depts where state in
(select state from P_TABLE where partition = 'NORTHERN');
create view southern_depts as select * from depts where state in
(select state from P_TABLE where partition = 'SOUTHERN');
create view eastern_depts as select * from depts where state in
(select state from P_TABLE where partition = 'EASTERN');
create view western_depts as select * from depts where state in
(select state from P_TABLE where partition = 'WESTERN');

I don't know that it's all that valuable to slavishly emulate every
feature that Oracle throws in...

The thing that would actually be truly /useful/ about this would be if
the partitioning scheme actually had some "physical" effects, as is
the case for the Informix "fragmentation" system. With Informix, you
can specify that groups of table entries will be physically stored
together based on a key. In effect, the "partitioning" amounts to
taking a diverse set of physical tables, each with different physical
structuring (some might conceivably cluster using hashing; others
b-treeing, and such...)

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."

Is it honestly REALLY useful, something that is so useful that
everyone should be pounding down the ANSI committee members' doors to
get them to add it in? Or is it just another bit of temptation to use
a nonportable construct that ties users a little more closely to
Oracle?
--
(concatenate 'string "aa454" "@freenet.carleton.ca")
http://www3.sympatico.ca/cbbrowne/linux.html
"Christianity has not been tried and found wanting; it has been found
difficult and not tried." -- G.K. Chesterton