Re: Partitioning of a table

From: Eric Parker <eric.parker_at_virgin.net>
Date: 2000/07/24
Message-ID: <397c0929.235331@news.virgin.net>#1/1

You could add a column to your table to identify the partition and then using a trigger detect the partition that the row belonged in. Something like :

DROP TABLE part_test;
CREATE TABLE part_test
(part_no NUMBER,
part_ch VARCHAR2(20))
STORAGE (INITIAL 16k NEXT 16k)
PARTITION BY RANGE (part_no)
(PARTITION part0 VALUES LESS THAN (1) TABLESPACE users, PARTITION part1 VALUES LESS THAN (2) TABLESPACE users);

CREATE OR REPLACE TRIGGER part_test_trg
BEFORE INSERT OR UPDATE
ON part_test
FOR EACH ROW
DECLARE
BEGIN
  IF (:new.part_ch IN ('A', 'F', 'H', 'K', 'P'))     THEN :new.part_no := 0;
    ELSE :new.part_no := 1;
  END IF;
END;
/

INSERT INTO part_test VALUES (-1, 'A');
INSERT INTO part_test VALUES (-1, 'C');
INSERT INTO part_test VALUES (-1, 'F');
INSERT INTO part_test VALUES (-1, '');

COMMIT;
SELECT part_no, part_ch, SUBSTR(rowid,1,6) PART_OBJ FROM part_test;

This produced the following output :

SQL> @part_table

Table dropped.

Table created.

Trigger created.

1 row created.

Commit complete.

PART_NO PART_CH PART_O

-------------------- ------

         0 A                    AAAFV+

         0 F                    AAAFV+

         1 C                    AAAFV/

         1                      AAAFV/

eric
On Fri, 21 Jul 2000 17:11:36 GMT, Madhavan Amruthur <mad5698_at_my-deja.com> wrote:

>Hi,
>I have a requirement to partition a table based on a column as below
>
>table_name - products
>column name - product_type
>
>The values for the column
>0
>A
>C
>F
>H
>K
>P
><null>
>
>The requirement is A,F,H,K and P go into their own partitions and C,0
>and <null> should go into one partition.
>
>Is this possible?
>
>Thanks for your help in advance.
>Regards,
>
>--
>Madhavan
>IBM Corporation WebServer Division
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Mon Jul 24 2000 - 00:00:00 CDT