Databases Reference
In-Depth Information
The next statement creates a hash partitioned table:
CREATE TABLE SALES_HP (
PROD_ID NUMBER NOT NULL,
CUST_ID NUMBER NOT NULL,
TIME_ID DATE NOT NULL,
CHANNEL_ID NUMBER NOT NULL,
PROMO_ID NUMBER NOT NULL,
QUANTITY_SOLD NUMBER(10,2) NOT NULL,
AMOUNT_SOLD NUMBER(10,2) NOT NULL)
PARTITION BY HASH (CHANNEL_ID)
(
PARTITION P1 TABLESPACE EXAMPLE,
PARTITION P2 TABLESPACE EXAMPLE,
PARTITION P3 TABLESPACE EXAMPLE,
PARTITION P4 TABLESPACE EXAMPLE
);
Please note that in creating a hash partitioned table, we don't specify the values for
each partition.
Composite partitioning
We can adopt even a mixed partitioning schema, resulting in a composite partitioned table.
For example, we can partition a table by a range of dates and, within each partition, by a list
of values, as follows:
CREATE TABLE SALES_CP (
PROD_ID NUMBER NOT NULL,
CUST_ID NUMBER NOT NULL,
TIME_ID DATE NOT NULL,
CHANNEL_ID NUMBER NOT NULL,
PROMO_ID NUMBER NOT NULL,
QUANTITY_SOLD NUMBER(10,2) NOT NULL,
AMOUNT_SOLD NUMBER(10,2) NOT NULL)
PARTITION BY RANGE (TIME_ID)
SUBPARTITION BY LIST (CHANNEL_ID)
(
PARTITION SALES_BEFORE_2000
VALUES LESS THAN (TO_DATE('20000101','YYYYMMDD'))
(SUBPARTITION DIRECT_SALES_2000 VALUES (3,9)
TABLESPACE EXAMPLE,
