Table Partitioning

Now a days enterprises run databases of hundred of Gigabytes in size. These databases are known as Very Large Databases (VLDB). From Oracle Ver. 8.0  Oracle has provided the feature of table partitioning i.e. you can partition a table according to some criteria . For example you have a SALES table with the following structure

Suppose this table contains millions of records, but all the records belong to four years only i.e. 1991, 1992, 1993
and 1994.  And most of the time you are concerned about only one year i.e. you give queries like the following

select sum(amt) from sales where year=1991;

select product,sum(amt) from sales where year=1992
          Group by product;

Now whenever you give queries like this Oracle will search the whole table. If you partition this table according to
year, then the performance is improve since oracle will scan only a single partition instead of whole table.

CREATING PARTITION TABLES

To create a partition table give the following statement

create table sales (year number(4),
                    product varchar2(10),
                   amt number(10,2))
     partition by range (year)
     partition p1 values less than (1992) tablespace u1,
     partition p2 values less than (1993) tablespace u2,
     partition p3 values less than (1994) tablespace u3,
     partition p4 values less than (1995) tablespace u4,
   partition p5 values less than (MAXVALUE) tablespace u5;

In the above example sales table is created with 5 partitions. Partition p1 will contain rows of year 1991 and
it will be stored in tablespace u1. Partition p2 will contain rows of year 1992 and it will be stored in tablespace u2.
Similarly p3 and p4.

In the above example if you don’t specify the partition p4 with values less than MAVALUE, then you will not be
able to insert any row with year above 1994.

Although not required, you can place partitions in different tablespaces. If you place partitions in different tablespaces
then you can isolate problems due to failures as only a particular partition will not be available and rest of the
partitions will still be available.

The above example the table is partition by range.

In Oracle you can partition a table by

• Range Partitioning
• Hash Partitioning
• List Partitioning
• Composite Partitioning

Range Partitioning

This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed;
for example, value of year. Performance is best when the data evenly distributes across the range

Hash partitioning

Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for
performance and manageability reasons. Hash partitioning provides a method of evenly distributing data across a
specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key

The following example shows how to  create a hash partition table.

The following example creates a hash-partitioned table. The partitioning column is partno, four partitions are created
and assigned system generated names, and they are placed in four named tablespaces (tab1,tab2, ...).

CREATE TABLE products
     (partno NUMBER,
      description VARCHAR2 (60))
   PARTITION BY HASH (partno)
   PARTITIONS 4
   STORE IN (tab1, tab2, tab3, tab4);

List Partitioning

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.

List partitioning allows unordered and unrelated sets of data to be grouped and organized together very naturally

The following example creates a table with list partitioning

create table customers (custcode number(5),
                  Name varchar2(20),
                  Addr varchar2(10,2),
                  City varchar2(20),
                  Bal number(10,2))
     Partition by list (city),
     Partition north_India values (‘DELHI’,’CHANDIGARH’),
Partition east_India values (‘KOLKOTA’,’PATNA’),
Partition south_India values (‘HYDERABAD’,’BANGALORE’,’CHENNAI’),
Partition west India values (‘BOMBAY’,’GOA’);

If a row is inserted in the above table then oracle maps the value of city column and whichever partition list matches the
city column the row is stored in that partition.

COMPOSITE PARTITONING

Composite partitioning partitions data using the range method, and within each partition, subpartitions it using
the hash method. 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.

When creating composite partitions, you specify the following:

• 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

The following statement creates a composite-partitioned table. 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 (tab1, ...,tab4).

CREATE TABLE PRODUCTS (partno NUMBER,
      description VARCHAR(32),
       costprice NUMBER)
  PARTITION BY RANGE (partno)
     SUBPARTITION BY HASH(description)
    SUBPARTITIONS 8 STORE IN (tab1, tab2, tab3, tab4)
      (PARTITION p1 VALUES LESS THAN (100),
       PARTITION p2 VALUES LESS THAN (200),
       PARTITION p3 VALUES LESS THAN (MAXVALUE));

LISTING INFORMATION ABOUT PARTITION TABLES

To see how many partitioned tables are there in your schema give the following statement

select * from user_part_tables;

To see on partition level partitioning information

select * from user_tab_partitions;

NEXT ( Altering and Managing Partition Tables)