Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

On Mon, Sep 13, 2004 at 11:07:35PM +0100, Simon Riggs wrote:
> PostgreSQL's functionality is in many ways similar to Oracle Partitioning.
>
> Loading 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 partitions from the query
> when you run queries which include a predicate on the partitioning_col, e.g.
>
> select count(*) from bigtable where idate >= 200409131000
>
> will scan the last two partitions only...
>
> There are a few other ways of creating the view that return the same answer,
> but only using constants in that way will allow the partitions to be
> eliminated from the query, and so run for much longer.

Is there by any chance a set of functions to manage adding and removing
partitions? Certainly this can be done by hand, but having a set of
tools would make life much easier. I just looked but didn't see anything
on GBorg.
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"