Oracle Partitioning

Note:  Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage.

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 . 

Partitioning allows a table, index, or index-organized table to be subdivided into smaller, manageable pieces, where each piece of such a database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics.

Logically the partitioned object remains as a single entity for the consumers, where physically the object is split into a number of relatively smaller partitions. Partitioning feature provides the flexibility to tune the structures for better performance and availability. All the partitions of a partitioned object must have the same logical attributes like colunm names, data types, constraints etc, but each partition can have separate physical attributes like tablespace etc. Partition tables are created using the PARTITION BY clause in the CREATE TABLE / CREATE INDEX commands.

A table can be partitioned upto 1024K – 1 partitions
A table containing LONG or LONG RAW data types cannot be partitioned

Each row in a partitioned table is unambiguously assigned to a single partition. The Partition Key is comprised of one or more columns that determine the partition where each row will be stored. Oracle automatically directs insert, update, and delete operations to the appropriate partition through the use of the partitioning key.
A partition key -

• Determines a unique partition of the object for each row
• Consists of a list of 1 to 16 columns
• Cannot contain a column of type ROWID
• Can contain columns that are NULLable

Overview of Partitioned Tables

Any table can be partitioned into a million separate partitions except those tables containing columns with LONG or LONG RAW datatypes. You can, however, use tables containing columns with CLOB or BLOB datatypes.

When to Partition a Table

Here are some suggestions for when to partition a table:

• Tables greater than 2 GB should always be considered as candidates for partitioning.
• Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.
• When the contents of a table need to be distributed across different types of storage devices.

Overview of Partitioned Indexes

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes). In general, you should use global indexes for OLTP applications and local indexes for data warehousing or DSS applications. Also, whenever possible, you should try to use local indexes because they are easier to manage. When deciding what kind of partitioned index to use, you should consider the following guidelines in order:

1. If the table partitioning column is a subset of the index keys, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 2.
2. If the index is unique and does not include the partitioning key columns, then use a global index. If this is the case, then you are finished. Otherwise, continue to guideline 3.
3. If your priority is manageability, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 4.
4. If the application is an OLTP one and users need quick response times, use a global index. If the application is a DSS one and users are more interested in throughput, use a local index.

When to Partition an Index

Here are some suggestions for when to consider partitioning an index:

• Avoid rebuilding the entire index when data is removed.
• Perform maintenance on parts of the data without invalidating the entire index.
• Reduce the impact of index skew caused by an index on a column with a monotonically increasing value.

Overview of Partitioned Index-Organized Tables

Partitioned index-organized tables are very useful for providing improved performance, manageability, and availability for index-organized tables.

For partitioning an index-organized table:

• Partition columns must be a subset of the primary key columns
• Secondary indexes can be partitioned (both locally and globally)
• OVERFLOW data segments are always equi-partitioned with the table partitions

Benefits of partitioning

There are many compelling reasons to implement Oracle partitioning for larger databases, and Oracle partitioning has become the de-facto standard for systems over 500 gigabytes.  Oracle partitioning has many benefits to improve performance and manageability:

• Stable - Oracle partitioning is a very stable technology and has been used in Oracle since Oracle 8, back in 1997.  Each new release of Oracle improves Oracle partitioning features.
• Robust - Oracle partitioning allows for multi-level keys, a combination of the Range and List partitioning technique. The table is first range-partitioned with Oracle partitioning, and then each individual range-partition is further sub-partitioned using a list partitioning technique with Oracle partitioning. Unlike composite Range-Hash Oracle partitioning, the content of each sub-partition represents a logical subset of the data, described by its appropriate Range and List Oracle partition setup. 
• Faster backups - A DBA can back-up a single Oracle partition of a table, rather than backing up the entire table, thereby reducing backup time.
• Less overhead - Because older Oracle partitioned tablespaces can be marked as read-only, Oracle has less stress on the redo logs, locks and latches, thereby improving overall performance.
• Easier management - Maintenance of Oracle partitioned tables is improved because maintenance can be focused on particular portions of tables. For maintenance operations across an entire database object, it is possible to perform these operations on a per-partition basis, thus dividing the maintenance process into more manageable chunks.
• Faster SQL - Oracle is partition-aware, and some SQL may improve is speed by several orders of magnitude (over 100x faster).

- Index range scans : Oracle partitioning physically sequences rows in index-order causing a dramatic improvement (over 10x faster) in the speed of partition-key scans.

-  Full-table scans : Oracle partition pruning only accesses those data blocks required by the query.

-  Table joins : Oracle partition-wise joins take the specific sub-set of the query partitions, causing huge speed improvements on nested loop and hash joins.

-  Updates : Oracle parallel query for partitions improves batch load speed.

In sum, Oracle partitioning has a very-fast payback time and the immediate improvements to performance and stress reduction on the Oracle server makes it a slam-dunk decision.

Suppose if a table contains millions of records, but all the records belong to four years only i.e. 2011, 2012, 2013 and 2014.  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=2013;

select product,sum(amt) from sales where year=2014 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 improved since oracle will scan only a single partition instead of whole table.

 In Oracle you can partition a table by

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

List, Range, and Hash Partitioning

Composite Partitioning

As of 11g version, we saw these new partitioning features. 

• Interval partitioning for tables - This is a new 11g partitioning scheme that automatically creates time-based partitions as new data is added. You can now partition by date, one partition per month for example, with automatic partition creation. Interval partitions build upon the foundation introduced with range partitioning for Oracle 11g. Interval   partitioning resolves the limitations built into range partitioning when a specific range is unknown by the  developer or DBA creating the partitions for the table. It tells Oracle to automatically setup new  partitions for a particular interval when data inserted to tables are greater than the range partitions. As  such the requirement of interval partitioning dictates that at least one range partition is specified. Once  the range partitioning key is given for the high value of the range partitions, this transition point is used  as the baseline to create interval partitions beyond  this point. When using interval partitioning, consider the following restrictions:
  • You can only specify one partitioning key column, and it must be of NUMBER or DATE type.
  • Interval partitioning is not supported for index-organized tables.
  • You cannot create a domain index on an interval-partitioned table.
  You can create single-level interval partitioned tables as well as the following composite partitioned tables:
  • Interval-range
  • Interval-hash
  • Interval-list
• System Partitioning - The new system partitioning feature in Oracle 11g provides you with the ability to implement and manage new partitions without a specific partition key. Instead, each partition is mapped to a tablespace using the extended partitioning syntax for system partitions. Due to lack of partition keys with system partitioning, the usual performance benefits available for partitioned tables do not exist with system partitions. Another drawback to system partitions is that they cannot be used for partition-wise joins or traditional partition pruning operations. As such, the main benefit of using the new system partitioning is for manageability purposes.

CREATING PARTITION TABLES

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.

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

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 (2011) tablespace u1,

     partition p2 values less than (2012) tablespace u2,

     partition p3 values less than (2013) tablespace u3,

     partition p4 values less than (2014) 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 2011 and
it will be stored in tablespace u1. Partition p2 will contain rows of year 2012 and it will be stored in tablespace u2. Similarly p3 and p4.

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

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 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 ('KOLKATA','PATNA'),

Partition south_India values ('HYDERABAD','BANGALORE','CHENNAI'),

Partition west India values ('MUMBAI','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));

ALTERING PARTITION TABLES

To add a partition 

You can add add a new partition to the "high" end (the point after the last existing partition). To add a partition at the beginning or in the middle of a table, use the SPLIT PARTITION clause.

For example to add a partition to sales table give the following command.

alter table sales add partition p6 values less than (2015);

To add a partition to a Hash Partition table give the following command.

alter table products add partition;

Then Oracle adds a new partition whose name is system generated and it is created in the default tablespace. 
To add a partition by user define name and in your specified tablespace give the following command.

alter table products add partition p5 tablespace u5;

To add a partition to a List partition table give the following command.

alter table customers add partition central_India values ('BHOPAL','NAGPUR');

Any value in the set of literal values that describe the partition(s) being added must not exist in any of the other partitions of the table.

Coalescing Partitions

Coalescing partitions is a way of reducing the number of partitions in a hash-partitioned table, or the number of subpartitions in a composite-partitioned table. When a hash partition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function. The specific partition that is coalesced is selected by Oracle, and is dropped after its contents have been redistributed.

To coalesce a hash partition give the following statement.

alter table products coalesce partition;

This reduces by one the number of partitions in the table products.

DROPPING PARTITIONS

To drop a partition from Range Partition table, List Partition or Composite Partition table give the following command.

alter table sales drop partition p1;

Once you have drop the partition and if you have created a global index on the table. Then you have to rebuild the global index after dropping the partition by giving the following statement.

alter index sales_ind rebuild;

To avoid rebuilding of indexes after dropping of the partitions you can also first delete all the records and then drop the partition like this

delete from sales where year=2011;

alter table sales drop partition p1;

This method is most appropriate for small tables, or for large tables when the partition being dropped contains a small percentage of the total data in the table.

Another method of dropping partitions is give the following statement.

ALTER TABLE sales DROP PARTITION p5 UPDATE GLOBAL INDEXES;

This causes the global index to be updated at the time the partition is dropped.

Exchanging a Range, Hash, or List Partition

To exchange a partition of a range, hash, or list-partitioned table with a nonpartitioned table, or the reverse, use the ALTER TABLE ... EXCHANGE PARTITION statement. An example of converting a partition into a nonpartitioned table follows. In this example, tablestocks can be range, hash, or list partitioned.

ALTER TABLE stocks EXCHANGE PARTITION p3 WITH stock_table_3;

Merging Partitions

Use the ALTER TABLE ... MERGE PARTITIONS statement to merge the contents of two partitions into one partition. Te two original partitions are dropped, as are any corresponding local indexes.

You cannot use this statement for a hash-partitioned table or for hash subpartitions of a composite-partitioned table.

You can only merged two adjacent partitions, you cannot merge non adjacent partitions.

For example the merge the partition p2 and p3 into one partition p23 give the following statement.

alter table sales merge partition p2 and p3 into partition p23;

Modifying Partitions: Adding Values

Use the MODIFY PARTITION ... ADD VALUES clause of the ALTER TABLE statement to extend the value list of an existing partition. Literal values being added must not have been included in any other partition's value list. The partition value list for any corresponding local index partition is correspondingly extended, and any global index, or global or local index partitions, remain usable.

The following statement adds a new set of cities ('KOCHI', 'MANGALORE') to an existing partition list.

ALTER TABLE customers

   MODIFY PARTITION south_india

      ADD VALUES ('KOCHI', 'MANGALORE');

Modifying Partitions: Dropping Values

Use the MODIFY PARTITION ... DROP VALUES clause of the ALTER TABLE statement to remove literal values from the value list of an existing partition. The statement is always executed with validation, meaning that it checks to see if any rows exist in the partition that correspond to the set of values being dropped. If any such rows are found then Oracle returns an error message and the operation fails. When necessary, use a DELETE statement to delete corresponding rows from the table before attempting to drop values.

You cannot drop all literal values from the value list describing the partition. You must use the ALTER TABLE ... DROP PARTITIONstatement instead. 

The partition value list for any corresponding local index partition reflects the new value list, and any global index, or global or local index partitions, remain usable.

The statement below drops a set of cities (‘KOCHI' and 'MANGALORE') from an existing partition value list.

ALTER TABLE customers

   MODIFY PARTITION south_india

      DROP VALUES ('KOCHI','MANGALORE');

SPLITTING PARTITIONS

You can split a single partition into two partitions. For example to split the partition p5 of sales table into two partitions give the following command

Alter table sales split partition p5 into 

  (Partition p6 values less than (2015),

   Partition p7 values less then (MAXVALUE));

TRUNCATING PARTITON

Truncating a partition will delete all rows from the partition. To truncate a partition give the following statement

alter table sales truncate partition p5;

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;