Re: Is there anything equivalent to Oracle9i's list partitioning? - Mailing list pgsql-general
From | Christopher Browne |
---|---|
Subject | Re: Is there anything equivalent to Oracle9i's list partitioning? |
Date | |
Msg-id | m3k7g5jgso.fsf@chvatal.cbbrowne.com Whole thread Raw |
In response to | Is there anything equivalent to Oracle9i's list partitioning? (Jeff Bearer <jbearer@tribweb.com>) |
Responses |
Re: Is there anything equivalent to Oracle9i's list partitioning?
|
List | pgsql-general |
In an attempt to throw the authorities off his trail, jbearer@tribweb.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
pgsql-general by date: