Using partitioning

Tables (and indexes) may become very large in a database, driving performance and maintenance problems.

Partitioning is the way to improve performance in large tables (Oracle suggests to partition tables with more than 2 GB of data), to ease the configuration and care of these objects and to reduce downtime in case of failures or scheduled maintenance such as move the tables or take some data offline.

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...