Total Pageviews

May 18, 2015

5/18/2015 02:48:00 AM
Oracle table Partitions

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));