Thread: Is there anything equivalent to Oracle9i's list partitioning?
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. Thanks
On 12 Feb 2003, Jeff Bearer wrote: > 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. Not yet. There's some work going on over in the hackers list with tablespaces. For now, you can "cheat" by creating your table, shutting down postgresql, moving the file that represents the table to another drive, and then soft linking it to the original location. Pain in the butt but it works. You can use uid2name from the contrib directory if you want an easy way to find out what the numerical name of your tables and databases are to move them around. You can also build database clusters wholly on other storage than primary using environmental variables and the initlocation command. That's somewhat easier than moving individual files and indexes around, and you don't have to shut down postgresql to do it.
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
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
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@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 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)