Partitioned Tables And Indexes
Maintenance of large tables and indexes can become very time and resource consuming. At the same time, data access performance can reduce drastically for these objects. Partitioning of tables and indexes can benefit the performance and maintenance in several ways.
Partition independence means backup and recovery operations can be performed on individual partitions, whilst leaving the other partitions available. Query performance can be improved as access can be limited to relevant partitions only.
There is a greater ability for parallelism with more partitions.
Range Partitioning Tables
Range partitioning is useful when you have distinct ranges of data you want to store together. The classic example of this is the use of dates. Partitioning a table using date ranges allows all data of a similar age to be stored in same partition. Once historical data is no longer needed the whole partition can be removed. If the table is indexed correctly search criteria can limit the search to the partitions that hold data of a correct age.
Types of partitions
Range partitions
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);
Range Parttions
CREATE TABLE clients
(
client_id NUMBER,
name VARCHAR2 (50),
country VARCHAR2 (2)
)
PARTITION BY LIST (country)
(PARTITION clients_inusae
VALUES ('BE', 'NE', 'LU'),
PARTITION clients_uk
VALUES ('UK'),
PARTITION clients_other
VALUES (DEFAULT));
Composite partitioning
A partition can be subpartitioned by any of the previous methods, in any combination since 11g.
This will allow more queries to benefit from table partitioning.
Partition pruning will occur when filtering on both keys or only one of the keys.
CREATE TABLE clients_hl
(
client_id NUMBER,
name VARCHAR2 (50),
country VARCHAR2 (2)
)
PARTITION BY LIST (country)
SUBPARTITION BY HASH (name)
SUBPARTITIONS 5
(PARTITION clients_inusae
VALUES ('IN', 'US', 'AE'),
PARTITION clients_uk
VALUES ('UK'),
PARTITION clients_other
VALUES (DEFAULT));
