partitioning in oracle-base 1

partitioning in oracle-base

CREATE TABLE invoices
(invoice_no    NUMBER NOT NULL,
 invoice_date  DATE   NOT NULL,
 comments      VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users,
 PARTITION invoices_q4 VALUES LESS THAN (TO_DATE('01/01/2002', 'DD/MM/YYYY')) TABLESPACE users);

Here is what the above code is Doing:
1. Create a table called invoices
2. The table has 3 columns: invoice_no, invoice_date and comments
3. The table is partitioned by RANGE based on the invoice_date column
4. The RANGE partitions are:
a. invoices_q1: invoice_date < '01-APR-2001' b. invoices_q2: invoice_date >= ’01-APR-2001′ AND invoice_date < '01-JUL-2001' c. invoices_q3: invoice_date >= ’01-JUL-2001′ AND invoice_date < '01-OCT-2001' d. invoices_q4: invoice_date >= ’01-OCT-2001′
5. All partitions are stored in the users tablespace

Similar Posts