Partition Table In Oracle

What is Partition Table?

Decompose a table or index into smaller, more manageable pieces, called partitions. Each partition of a table or index must have the same logical attributes, such as column names, datatypes, and constraints, but each partition can have separate physical attributes such as pctfree, pctused, and tablespaces.

Partition Key

Each row in a partitioned table is unambiguously assigned to a single partition. The partition key is a set of from 1 to 16 columns that determines the partition for each row.

Sub Partition

Partitions created within partitions. They are just partitions themselves and there is nothing special about them.

Composite Partitioning

Composite partitioning is a combination of other partitioning methods. Oracle currently supports range-hash and range-list composite partitioning.

Interval Partitioning

Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions are defined by an interval. Interval partitions are automatically created by the database when data is inserted into the partition.

Partitioning Methods

There are several partitioning methods offered by Oracle Database:

1.    Range partitioning
2.    Hash partitioning
3.    List partitioning
4.    Composite range-hash partitioning
5.    Composite range-list partitioning

Range partitioning

Must specify the following three things when range partitions in created:

· Partitioning method: range
· Partitioning column(s)
· Partition descriptions identifying partition bounds

SQL Code:
CREATE TABLE sales
    (
        invoice_no NUMBER,
        sale_year  INT NOT NULL,
        sale_month INT NOT NULL,
        sale_day   INT NOT NULL
    )
 PARTITION BY RANGE (sale_year, sale_month, sale_day)
    (
        PARTITION sales_pA VALUES LESS THAN (1999, 04, 01) TABLESPACE tsa,
        PARTITION sales_pB VALUES LESS THAN (1999, 07, 01) TABLESPACE tsb,
        PARTITION sales_pC VALUES LESS THAN (1999, 10, 01) TABLESPACE tsc,
        PARTITION sales_pD VALUES LESS THAN (2000, 01, 01) TABLESPACE tsd
    );

In this example there is a table creates four partitions, one for each quarter of sales.

Partitioning column(s): sale_year, sale_month, and sale_day

Partition descriptions identifying partition bounds:

The VALUES LESS THAN clause determines the partition bound: rows with partitioning key values that compare less than the ordered list of values specified by the clause are stored in the partition.

Portion Name	Tablespace
sales_pA	tsa
sales_pB	tsb
sales_pC	tsc
sales_pD	tsd

Use range partitioning to map rows to partitions based on ranges of column values. This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed; for example, months of the year. Performance is best when the data evenly distributes across the range. If partitioning by range causes partitions to vary dramatically in size because of unequal distribution, you may want to consider one of the other methods of partitioning.

To get Information about the portion table run the following query

select partition_position, partition_name, high_value
from dba_tab_partitions
where table_name = 'SALES' order by 1;
 

Adding a Partition to a Range-Partitioned Table

ALTER TABLE sales
  ADD PARTITION jan2010 VALUES LESS THAN ( '01-FEB-2010' )
  TABLESPACE tsx;

Hash partitioning

Must specify the following three things when range partitions in created:

· Partitioning method: hash
· Partitioning column(s)
· Number of partitions or individual partition descriptions

SQL Code:
create table customer (
        customer_id            number,
        customer_name          varchar2(20)
)
partition by hash (customer_id)
partitions 4
store in (gear1, gear2, gear3, gear4);
 

The above example creates a hash-partitioned table. The partitioning column is customer_id, four partitions are created and assigned system generated names, and they are placed in four named tablespaces (gear1, gear2, gear3, gear4)

Adding a Partition to a Hash-Partitioned table

ALTER TABLE customer
      ADD PARTITION customer_id TABLESPACE gear5;

List partitioning

Must specify the following three things when range partitions in created:

· Partitioning method: List
· Partitioning column(s)
· Partition descriptions, each specifying a list of literal values (a value list),
   Which are the discrete values of the partitioning column
   that qualify a row to be included in the partition

SQL Code:
create table sales (
        product_id     number,
        trans_amt      number,
        sales_dt       date,
        state_code     varchar2(2)
)
partition by list (state_code)
(
partition ct             values ('CT'),
partition ca             values ('CA'),
partition ny_vm_nj       values ('NY', 'VM', 'NJ'),
partition def            values (default)
);

Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete values for the partitioning column in the description for each partition. This is different from range partitioning, where a range of values is associated with a partition, and from hash partitioning, where the user has no control of the row to partition mapping.

Adding a Partition to a List-Partitioned Table

The following statement illustrates adding a new partition to a list-partitioned table. In this example physical attributes and NOLOGGING are specified for the partition being added.

ALTER TABLE sales
   ADD PARTITION hi VALUES ('HI')
      STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3
      NOLOGGING;

Composite range-hash partitioning

Must specify the following three things when range partitions in created:

· Partitioning method: range
· Partitioning column(s)
· Partition descriptions identifying partition bounds
 
· Subpartitioning method: hash
· Subpartitioning column(s)
· Number of subpartitions for each partition or descriptions of subpartitions

SQL Code:
CREATE TABLE scubagear (
        equipno NUMBER,
        equipname VARCHAR(32),
        price NUMBER
        )
 PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname)
    SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)
        (
        PARTITION p1 VALUES LESS THAN (1000),
        PARTITION p2 VALUES LESS THAN (2000),
        PARTITION p3 VALUES LESS THAN (MAXVALUE)
        );

In this example, three range partitions are created, each containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the STORE IN clause distributes them across the 4 specified tablespaces (ts1, ts2, ts3, ts4).

Range-hash partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. These composite partitions are ideal for both historical data and striping, and provide improved manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.

The partitions of a range-hash partitioned table are logical structures only, as their data is stored in the segments of their subpartitions. As with partitions, these subpartitions share the same logical attributes. Unlike range partitions in a range-partitioned table, the subpartitions cannot have different physical attributes from the owning partition, although they are not required to reside in the same tablespace

Adding a Partition to a range-hash Table

ALTER TABLE sales
      ADD PARTITION q1_2000 VALUES LESS THAN (2000, 04, 01)
      SUBPARTITIONS 8 STORE IN tbs5;

Composite range-list partitioning

Must specify the following three things when range partitions in created:

· Partitioning method: range
· Partitioning column(s)
· Partition descriptions identifying partition bounds
 
· Subpartitioning method: list
· Subpartitioning column
· Subpartition descriptions, each specifying a list of literal values (a value list),
  which are the discrete values of the subpartitioning
  column that qualify a row to be included in the subpartition

SQL Code:
CREATE TABLE quarterly_regional_sales
      (deptno number, item_no varchar2(20),
       txn_date date, txn_amount number, state varchar2(2))
  TABLESPACE ts4
  PARTITION BY RANGE (txn_date)
    SUBPARTITION BY LIST (state)
      (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
         (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
         ),
       PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
         (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
         ),
       PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
         (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')
         ),
       PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
         (SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')
         )
      );

Like the composite range-hash partitioning method, the composite range-list partitioning method provides for partitioning based on a two level hierarchy. The first level of partitioning is based on a range of values, as for range partitioning; the second level is based on discrete values, as for list partitioning. This form of composite partitioning is well suited for historical data, but lets you further group the rows of data based on unordered or unrelated column values.

Dropping a Table Partition

Use one of the following statements to drop a table partition or subpartition:

• ALTER TABLE ... DROP PARTITION to drop a table partition


• ALTER TABLE ... DROP SUBPARTITION to drop a subpartition of a range-list partitioned table

Partition Indexes

There is two types of indexes on partitioned tables:

• Local Index


• Global Index

Local Index: The index is partitioned in exactly the same way as the base table. For instance, you can create a local index on the TRANS_AMOUNT column of the TRANS table as follows:

create index in_trans_01
on trans (trans_amount)
local;

This creates a range-partitioned index, on the TRANS_DT column, the same way the TRANS table is partitioned. All the index entries for a specific partition, such as Y05Q1, will exist only inside the corresponding partition of the index.

Global Index: The index can span all partitions in the base table. For example, suppose there is a primary key on TRANS, on the TRANS_ID column. The primary key can be anywhere inside the table, across all the partitions. In this case, the index entries of a partition of the table may exist outside the corresponding partition of the index. For such a situation, create a global index, as follows:

alter table trans
add constraint pk_trans
primary key (trans_id)
using index global;

Reference:
http://www.oracle.com/technology/oramag/oracle/06-sep/o56partition.html
http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10739/partiti.htm