Databases Reference
In-Depth Information
In this recipe, we discuss partitioning a table using range partitioning. We will present hash,
list, and composite partitioning as options.
How to do it...
We can create a table range partitioned adding the
PARTITION BY RANGE
clause to the
CREATE TABLE
statement:
CREATE TABLE SALES_RP (
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)
(
PARTITION SALES_BEFORE_2000 VALUES LESS THAN
(TO_DATE('20000101','YYYYMMDD')) TABLESPACE EXAMPLE,
PARTITION SALES_2000_2001_2002 VALUES LESS THAN
(TO_DATE('20030101','YYYYMMDD')) TABLESPACE EXAMPLE,
PARTITION SALES_2003 VALUES LESS THAN
(TO_DATE('20040101','YYYYMMDD')) TABLESPACE EXAMPLE
);
Please note that we are connected to the
SH
schema, and the
SALES_RP
table logical
structure is identical to the
SALES
table of this schema.
How it works...
We have created a table, indicating that we want to partition it
BY RANGE
.
The field
TIME_ID
, which is used to partition the table, is called the
PARTITION KEY
. We are
partitioning the table in three parts, depending on the value of the partition key. We will store
sales data before the year 2000 in the
SALES_BEFORE_2000
partition, sales data between
years 2000 and 2002 inclusive in the
SALES_2000_2001_2002
partition, and sales data for
the year 2003 in the
SALES_2003
partition.
For simplicity, we have stored all the partitions in the same
EXAMPLE
tablespace. In real-life,
you will probably spread the partitions in different tablespaces.
