Re: Table Partitioning and Rules

On Thursday 17 Jul 2003 6:20 pm, Girish Bajaj wrote:
> Hello All,
>
> I have a set of tables partitioned horizontally. DML below.
>
> Essentially Im trying to store a persons information in a table in the
> database. Since we could have millions of people, with duplicates! Ive
> decided we need to partition the table into segments where all people with
> the LastName starting from A to G will be in one table. H-N will be in
> another table and O-Z in the third. Ive created a VIEW that does a UNION on
> all the tables.

I'd not bother - databases are good at managing large amounts of information.
If you really need to you can set up multiple partial indexes:

CREATE INDEX my_index_a ON contact (LastName) WHERE LastName>='A' AND
LastName<'B'
CREATE INDEX my_index_b ON contact (LastName) WHERE LastName>='B' AND
LastName<'C'
etc.

> Now, Im trying to create an INSERT and UPDATE RULE on the VIEW that would
> direct an insert or update into the appropriate partition table depending
> on the person LastName.
>
> I cant seem to figure this out. Does anyone know how to do this?

Something like:
CREATE RULE ... ON INSERT TO Contact WHERE NEW.LastName>='A' AND
NEW.LastName<'H'
DO INSTEAD ...insert into correct table here...

You'll want to read the chapter on the rule system in the "Server Programming"
section of the manual.

--  Richard Huxton