Re: [GENERAL] CLUSTER command

On Thu, Dec 12, 2002 at 05:39:44PM -0500, Jean-Luc Lachance wrote:
> Let's create a new command:
> 
> PARTITION <table> ON <attribute>
<snip>
> Because the table file is already extended (2G limit) using
> different files extension (.N)
> how complicated (modifying the code) would it be to have the table
> files split according to the cluster key?

I think the code changes would be complicated. Just at a 30-second
consideration, this would need to touch:
- all sql (selects, inserts, updates, deletes)
- vacuuming
- indexing
- statistics gathering
- existing clustering

That's not to say it's not worthwhile to look into, but it's big.

All of that aside, a view over unions is possible now:

create table u1 (...);
create table u2 (...);
create table u3 (...);

create view uv as (select "A" as partition_key, ... from u1
                   union all
                   select "B" as partition_key, ... from u2
                   union all
                   select "C" as partition_key, ... from u3);

That keeps the tables in different files on-disk while still allowing
you to query against all of them. You need to index them separately
and logic is necessary when changing data.

Hope that helps.

-johnnnnnnnnnn