Jim C. Nasby On Mon, Sep 13, 2004 at 11:07:35PM +0100, Simon Riggs wrote:
PostgreSQL's functionality is in many ways similar to OraclePartitioning.
from the queryLoading up your data in many similar tables, then creating a view like:
CREATE VIEW BIGTABLE (idate, col1, col2, col3...) AS SELECT 200409130800, col1, col2, col3... FROM table200409130800 UNION ALL SELECT 200409131000, col1, col2, col3... FROM table200409131000 UNION ALL SELECT 200409131200, col1, col2, col3... FROM table200409131200 ...etc...
will allow the PostgreSQL optimizer to eliminate partitionswhen you run queries which include a predicate on thepartitioning_col, e.g.
select count(*) from bigtable where idate >= 200409131000
The "partitions" are just tables, so no need for other management tools. Oracle treats the partitions as sub-tables, so you need a range of commands to add, swap etc the partitions of the main table.
A few years ago I wrote a federated query engine (wrapped as an ODBC driver) that had to handle thousands of contributors (partitions) to a pseudotable / VIEWofUNIONs. Joins did require some special handling in the optimizer, because of the huge number of crossproducts between different tables. It was definitely worth the effort at the time, because you need different strategies for: joining a partition to another partition on the same subserver; joining two large partitions on different servers; and joining a large partition on one server to a small one on another.
The differences may not be so great for a solitary server;
but they're still there, because of disparity in subtable sizes. The simplistic query plans tend to let you down, when you're dealing with honking warehouses.
I'm guessing that Oracle keeps per-subtable AND cross-all-subtables statistics, rather than building the latter from scratch in the course of evaluating the query plan. That's the one limitation I see in emulating their partitioned tables with Views.
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster