Table partition

Table partition

Partitioning enables tables and indexes to be split into smaller, more manageable components and is a key requirement for any large database with high performance and high availability requirements.

Types

Range
Range partitioning maps data to partitions based on ranges of partition key values that you establish for each partition.

Ex:

CREATE TABLE salestable
(
 s_productid    NUMBER,
 s_saledate     DATE,
 s_custid       NUMBER,
 s_totalprice   NUMBER,
 s_region       VARCHAR2(20)
)
PARTITION BY RANGE(s_saledate)
(
PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY'))
);

Hash
Hash partitioning maps data to partitions based on a hashing algorithm that evenly distributes rows among partitions, giving partitions approximately the same size.

Ex:

CREATE TABLE salestable
(
 s_productid    NUMBER,
 s_saledate     DATE,
 s_custid       NUMBER,
 s_totalprice   NUMBER,
 s_region       VARCHAR2(20)
)
PARTITION BY HASH(s_region)
PARTITIONS 10;

List
List partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values in the description for each partition.

Ex:

CREATE TABLE salestable
(
 s_productid    NUMBER,
 s_saledate     DATE,
 s_custid       NUMBER,
 s_totalprice   NUMBER,
 s_region       VARCHAR2(20)
)
PARTITION BY LIST(s_region)
(
PARTITION q1_northwest VALUES ('OR', 'WA'),
PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
PARTITION q1_northeast VALUES  ('NY', 'VM', 'NJ'),
PARTITION q1_southeast VALUES ('FL', 'GA'),
PARTITION q1_northcentral VALUES ('SD', 'WI'),
PARTITION q1_southcentral VALUES ('OK', 'TX')
);

Composite
Composite partitioning is a combination of two partitioning methods to further divide the data into sub-partitions. The table is initially partitioned by the first data distribution method and then each partition is sub-partitioned by the second data distribution method. The following composite partitions are available:
Range-Hash, Range-List, Range-Range, List-Range, List-List and List-Hash.

IntervalThe "INTERVAL" clause of the "CREATE TABLE" statement establishes interval partitioning for the table. You must specify at least one range partition using the "PARTITION" clause. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database automatically creates interval partitions for data beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition. This is a very useful 11g feature.

Ex:
The below query will automatically create a partition every month.

CREATE TABLE interval_sales
(
 prod_id        NUMBER(6),
 cust_id        NUMBER,
 time_id        DATE,
 channel_id     CHAR(1),
 promo_id       NUMBER(6),
 quantity_sold  NUMBER(3),
 amount_sold    NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(PARTITION p_first VALUES LESS THAN ('01-JAN-2006');

REF Partitioning or Reference Partitioning
Oracle Database 11g allows to partition a table by leveraging an existing parent-child relationship. The partitioning strategy of the parent table is inherited to its child table without the necessity to store the parent's partitioning key columns in the child table.

Ex:
Lets create a table orders which is range-partitioned on order_date. The reference-partitioned child table order_items is created with four partitions, Q1_2005, Q2_2005, Q3_2005, and Q4_2005, where each partition contains the order_items rows corresponding to orders in the respective parent partition.

CREATE TABLE orders
(
 order_id           NUMBER(12),
 order_date         TIMESTAMP WITH LOCAL TIME ZONE,
 order_mode         VARCHAR2(8),
 customer_id        NUMBER(6),
 order_status       NUMBER(2),
 order_total        NUMBER(8,2),
 sales_rep_id       NUMBER(6),
 promotion_id       NUMBER(6),
 CONSTRAINT orders_pk PRIMARY KEY(order_id)
)
PARTITION BY RANGE(order_date)
(
 PARTITION Q1_2005 VALUES LESS THAN (TO_DATE('01-APR-2005','DD-MON-YYYY')),
 PARTITION Q2_2005 VALUES LESS THAN (TO_DATE('01-JUL-2005','DD-MON-YYYY')),
 PARTITION Q3_2005 VALUES LESS THAN (TO_DATE('01-OCT-2005','DD-MON-YYYY')),
 PARTITION Q4_2005 VALUES LESS THAN (TO_DATE('01-JAN-2006','DD-MON-YYYY'))
);


CREATE TABLE order_items
(
 order_id           NUMBER(12) NOT NULL,
 line_item_id       NUMBER(3)  NOT NULL,
 product_id         NUMBER(6)  NOT NULL,
 unit_price         NUMBER(8,2),
 quantity           NUMBER(8),
 CONSTRAINT order_items_fk
 FOREIGN KEY(order_id) REFERENCES orders(order_id)
)
PARTITION BY REFERENCE(order_items_fk);

Virtual column based partitioning
Virtual columns, a new functionalityin Oracle Database 11g, allows the partitioning key to be defined by an expression, using one or more existing columns of a table, and storing the expression as metadata only.

Ex:
Lets create sales table partitioned by range-range using a virtual column for the subpartitioning key. The virtual column calculates the total value of a sale by multiplying amount_sold and quantity_sold.

CREATE TABLE sales
(
prod_id       NUMBER(6) NOT NULL,
cust_id       NUMBER NOT NULL,
time_id       DATE NOT NULL,
channel_id    CHAR(1) NOT NULL,
promo_id      NUMBER(6) NOT NULL,
quantity_sold NUMBER(3) NOT NULL,
amount_sold   NUMBER(10,2) NOT NULL,
total_amount AS (quantity_sold * amount_sold)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY RANGE(total_amount)
SUBPARTITION TEMPLATE
(
SUBPARTITION p_small VALUES LESS THAN (1000),
SUBPARTITION p_medium VALUES LESS THAN (5000),
SUBPARTITION p_large VALUES LESS THAN (10000),
SUBPARTITION p_extreme VALUES LESS THAN (MAXVALUE)
)
(PARTITION sales_before_2007 VALUES LESS THAN
   (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
)
ENABLE ROW MOVEMENT
PARALLEL NOLOGGING;