Table Partitioning an Oracle table - Tips & Tricks

We will discuss various table partition concepts with examples with some fundamental concepts. We ll cover topics:

A) Partitioning Concepts
B) When to Partition a Table
C) When to Partition an Index
D) Oracle Interval Partitioning Tips
E) About Interval Partitioning
F) Query performance with partitioning 
G) Index partitioning with Oracle
H) Basic Partitioning Strategies On Tables
J) Partition Advisor :

A) Partitioning Concepts:

Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts of data. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.

Partitioning is a divide-and-conquer approach to improving Oracle maintenance and SQL performance. Anyone with un-partitioned databases over 500 gigabytes is courting disaster.  Databases become unmanageable, and serious problems occur:

• Files recovery takes days, not minutes
• Rebuilding indexes (important to re-claim space and improve performance) can take days
• Queries with full-table scans take hours to complete
• Index range scans become inefficient

Advantages:

As per Oracle Documentation, Partitioning offers following advantages:

• Increased availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures.
• Easier administration of schema objects reducing the impact of scheduled downtime for maintenance operations.
• Reduced contention for shared resources in OLTP systems
• Enhanced query performance: Often the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique (called partition pruning) can provide order-of-magnitude gains in performance.

B) 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.

C) 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.

D) Oracle Interval Partitioning Tips:

Interval partitioning is an enhancement to range partitioning in Oracle 11g and interval partitioning automatically creates time-based partitions as new data is added.

Range partitioning allows an object to be partitioned by a specified range on the partitioning key.  For example, if a table was used to store sales data, it might be range partitioned by a DATE column, with each month in a different partition.

Therefore, every month a new partition would need to be defined in order to store rows for that month.  If a row was inserted for a new month before a partition was defined for that month, the following error would result "ORA-14400: inserted partition key does not map to any partition"

If this situation occurs, data loading will fail until the new partitions are created.  This can cause serious problems in larger data warehouses where complex reporting has many steps and dependencies in a batch process.  Mission critical reports might be delayed or incorrect due to this problem.

E) About Interval Partitioning:

There are a few restrictions on interval partitioning that must be taken into consideration before deciding if it is appropriate for the business requirement:

• Cannot be used for index organized tables
• Must use only one partitioning key column and it must be a DATE or NUMBER
• Cannot create domain indexes on interval partitioned tables
• Are not supported at the sub-partition level

This feature should be used as an enhancement to range partitioning when uniform distribution of range intervals for new partitions is acceptable.  If the requirement demands the use of uneven intervals when adding new partitions, then interval partitioning would not be the best solution.

 Interval Partitioning Commands

There are a few new commands to manage interval partitioning.  First, convert a range partitioned table to use interval partitioning by using :

SQL> alter table <table_name> set interval(expr).

Interval Partitioning: Introduced in 11g, interval partitions are extensions to range partitioning. These provide automation for equi-sized range partitions. Partitions are created as metadata and only the start partition is made persistent. The additional segments are allocated as the data arrives. The additional partitions and local indexes are automatically created.

Click Here to read more interval partitioning

F) Query performance with partitioning :

The Oracle engine can take advantage of the physical segregation of table and index partitions in several ways:

>> Disk load balancing —Table and index partitioning allows the Oracle data warehouse DBA to segregate portions of very large tables and indexes onto separate disk devices, thereby improving disk I/O throughput and ensuring maximum performance.

>> Improved query speed —The Oracle optimizer can detect the values within each partition and access only those partitions that are necessary to service the query. Since each partition can be defined with its own storage parameters, the Oracle SQL optimizer may choose a different optimization plan for each partition.

>> Faster parallel query —The partitioning of objects also greatly improves the performance of parallel query. When Oracle detects that a query is going to span several partitions, such as a full-table scan, it can fire off parallel processes. Each of processes will independently retrieve data from each partition. This feature is especially important for indexes, since parallel queries don't need to share a single index when servicing a parallel query.

>> Partitioning Pruning --  Partitioning pruning ( Partition elimination) is the simplest and also the most substantial means to improve performance using partitioning. Partition pruning can often improve query performance by several orders of magnitude.

For example,
suppose an application contains an TRANX_ORDERS table containing an historical record of orders, and that this table has been partitioned by week. A query requesting orders for a single week would only access a single partition of the TRANX_ORDERS table. If the table had 2 years of historical data, this query would access one partition instead of 104 partitions. This query could potentially execute 100x faster simply because of partition pruning. Partition pruning works with all of Oracle's other performance features. Oracle will utilize partition pruning in conjunction with any indexing technique, join technique, or parallel access method.

>> Partition-wise Joins --  Partitioning can also improve the performance of multi-table joins, by using a technique known as partition-wise joins. Partition-wise joins can be applied when two tables are being joined together, and at least one of these tables is partitioned on the join key. Partition-wise joins break a large join into smaller joins of 'identical' data sets for the joined tables. 'Identical' here is defined as covering exactly the same set of partitioning key values on both sides of the join, thus ensuring that only a join of these 'identical' data sets will produce a result and that other data sets do not have to be considered. Oracle is using either the fact of already (physical) equi-partitioned tables for the join or is transparently redistributing (= “repartitioning”) one table at runtime to create equi-partitioned data sets matching the partitioning of the other table, completing the overall join in less time. This offers significant performance benefits both for serial and parallel execution.

G) Index partitioning with Oracle:

The first partitioned index method is called a LOCAL partition. A local partitioned index creates a one-for-one match between the indexes and the partitions in the table. Of course, the key value for the table partition and the value for the local index must be identical. The second method is called GLOBAL and allows the index to have any number of partitions.

The partitioning of the indexes is transparent to all SQL queries. The great benefit is that the Oracle query engine will scan only the index partition that is required to service the query, thus speeding up the query significantly. In addition, the Oracle parallel query engine will sense that the index is partitioned and will fire simultaneous queries to scan the indexes.

a) Local partitioned indexes:

Local partitioned indexes allow the DBA to take individual partitions of a table and indexes offline for maintenance (or reorganization) without affecting the other partitions and indexes in the table. In a local partitioned index, the key values and number of index partitions will match the number of partitions in the base table.

CREATE INDEX idx_year_txorder ON tranx_order (order_date) LOCAL;

OR

CREATE INDEX year_idx
on tranx_order (order_date)
LOCAL
(PARTITION tranx_order_p1 TABLESPACE tbs1,
PARTITION tranx_order_p2 TABLESPACE tbs2,
PARTITION tranx_order_p3 TABLESPACE tbs3);

Oracle will automatically use equal partitioning of the index based upon the number of partitions in the indexed table. For example, in the above definition, if we created four indexes on tranx_order, the CREATE INDEX would fail since the partitions do not match. This equal partition also makes index maintenance easier, since a single partition can be taken offline and the index rebuilt without affecting the other partitions in the table.

b) Global partitioned indexes

A global partitioned index is used for all other indexes except for the one that is used as the table partition key. Global indexes partition OLTP (online transaction processing) applications where fewer index probes are required than with local partitioned indexes. In the global index partition scheme, the index is harder to maintain since the index may span partitions in the base table.

For example, when a table partition is dropped as part of a reorganization, the entire global index will be affected. When defining a global partitioned index, the DBA has complete freedom to specify as many partitions for the index as desired.

Now that we understand the concept, let's examine the Oracle CREATE INDEX syntax for a globally partitioned index:

SQL> CREATE INDEX idx_item_tranx
on tranx (item_nbr)
GLOBAL
(PARTITION idx_item_tranx1 VALUES LESS THAN (1000),
PARTITION idx_item_tranx2 VALUES LESS THAN (2000),
PARTITION idx_item_tranx3 VALUES LESS THAN (3000),
PARTITION idx_item_tranx4 VALUES LESS THAN (4000),
PARTITION idx_item_tranx5 VALUES LESS THAN (5000));

Here, we see that the item index has been defined with five partitions, each containing a subset of the index range values. Note that it is irrelevant that the base table is in three partitions. In fact, it is acceptable to create a global partitioned index on a table that does not have any partitioning.

H) Basic Partitioning Strategies On Tables:

Oracle Partitioning offers three fundamental data distribution methods as basic partitioning strategies that control how data is

placed into individual partitions:

•  Range
•  Hash
•  List

a) Range Partitioning

Range partitioning was the first partitioning method supported by Oracle in Oracle 8. Range partitioning was probably the first partition method because data normally has some sort of logical range. For example, business transactions can be partitioned by various versions of date (start date, transaction date, close date, or date of payment). Range partitioning can also be performed on part numbers, serial numbers or any other ranges that can be discovered.

Examples-1: Using any column as range
CREATE TABLE employees
(
empid number(10) NOT NULL,
empname VARCHAR(30),
hired DATE DEFAULT SYSDATE,
job_code number(5) NOT NULL,
store_id number(2) NOT NULL,
CONSTRAINT empid_pk PRIMARY KEY (empid)
)
PARTITION BY RANGE(store_id)
(
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

In this partitioning scheme, all rows corresponding to employees working at stores 1 through 5 are stored in partition p0, to those employed at stores 6 through 10 are stored in partition p1, and so on. Note that each partition is defined in order, from lowest to highest. This is a requirement of the PARTITION BY RANGE syntax; One can think of it as being analogous to a series of if ... elseif ... statements in programming language in this regard.

Example-2: using year as range

Partition the table by RANGE, and for the partitioning expression, employ a function operating on a DATE, TIME, or DATETIME column and returning an integer value, as shown here:

SQL>
CREATE TABLE SALES_PART
(TIME_ID    NUMBER,
ORDER_DATE DATE,
SALES_QTY NUMBER(10,2),
SALES_AMOUNT NUMBER(12,2)
)
PARTITION BY RANGE (ORDER_DATE)
(
PARTITION p_first VALUES LESS THAN ('01-APR-2014'));

-- Set interval partitioning
SQL> alter table SALES_PART set INTERVAL (NUMTOYMINTERVAL(1,'month'));
OR
SQL> alter table SALES_PART set INTERVAL (NUMTOYMINTERVAL(1,'year'));

Example: 3 : using year as range with adding interval partition 

SQL> CREATE TABLE SALES_PART
(TIME_ID    NUMBER,
ORDER_DATE DATE,
SALES_QTY NUMBER(10,2),
SALES_AMOUNT NUMBER(12,2)
)
PARTITION BY RANGE (ORDER_DATE)
(
PARTITION p_first VALUES LESS THAN ('01-APR-2014'));

SQL> alter table SALES_PART set INTERVAL (NUMTOYMINTERVAL(1,'YEAR'));
-- Will be Inserted to first partition
insert into SALES_PART values (1,'01-JAN-2013',10,10);
insert into SALES_PART values (1,'01-JAN-2013',10,10);
insert into SALES_PART values (1,'01-FEB-2014',10,10);
insert into SALES_PART values (1,'01-MAR-2014',10,10);
-- will be inserted to next partition
insert into SALES_PART values (1,'01-APR-2014',10,10);
insert into SALES_PART values (1,'01-MAY-2014',10,10);
insert into SALES_PART values (1,'01-JAN-2015',10,10);
insert into SALES_PART values (1,'01-DEC-2015',10,10);
-- will be inserted to next partition
insert into SALES_PART values (1,'01-FEB-2015',10,10);
insert into SALES_PART values (1,'01-JAN-2016',10,10);
-- will be inserted to next partition
insert into SALES_PART values (1,'01-DEC-2016',10,10);

-- after Verification
select count(1) from SALES_PART partition(P_FIRST); -- 4
select count(1) from SALES_PART partition(SYS_P46); -- 4
select count(1) from SALES_PART partition(SYS_P47); -- 2
select count(1) from SALES_PART partition(SYS_P48); -- 1

select count(1) from SALES_PART partition(SYS_P48); -- 1
select count(1) from SALES_PART partition(SYS_P44); -- 1
select count(1) from SALES_PART partition(SYS_P45); -- 1

Example:4: Creating partitions in different table-spaces:

CREATE TABLE emp_dumy
(
empid number(10) NOT NULL,
empname VARCHAR(30),
hired DATE DEFAULT SYSDATE,
job_code number(5) NOT NULL,
store_id number(2) NOT NULL,
CONSTRAINT empid_pk PRIMARY KEY (empid)
)
PARTITION BY RANGE(store_id)
(
    PARTITION p0 VALUES LESS THAN (6) tablespace tbs1,
    PARTITION p1 VALUES LESS THAN (11) tablespace tbs2,
    PARTITION p2 VALUES LESS THAN (16) tablespace tbs3,
    PARTITION p3 VALUES LESS THAN (21) tablespace tbs4
);

b) Hash Partitioning

Oracle's hash partitioning distributes data by applying a proprietary hashing algorithm to the partition key and then assigning the data to the appropriate partition. By using hash partitioning, DBAs can partition data that may not have any logical ranges. Also, DBAs do not have to know anything about the actual data itself. Oracle handles all of the distribution of data once the partition key is identified.

Hash partitioning is useful when there is no obvious range key, or range partitioning will cause uneven distribution of data. The number of partitions must be a power of 2 (2, 4, 8, 16...) and can be specified by the PARTITIONS...STORE IN clause.

Example-1: 

The following examples illustrate two methods of creating a hash-partitioned table named dept. In the first example the number of partitions is specified, but system generated names are assigned to them and they are stored in the default tablespace of the table.

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
PARTITION BY HASH(deptno) PARTITIONS 16;

-- All partitions will be created in HR tablespace(assume).

OR
In the following example, names of individual partitions, and tablespaces in which they are to reside, are specified. The initial extent size for each hash partition (segment) is also explicitly stated at the table level, and all partitions inherit this attribute.

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
PARTITION BY HASH(deptno)
(PARTITION p1 TABLESPACE HR1, PARTITION p2 TABLESPACE HR1,
PARTITION p3 TABLESPACE HR2, PARTITION p4 TABLESPACE HR3);

-- To store in diff disk, assume tablespaces created in diff. diskgroup.

*Creating a Hash-Partitioned Global Index:

Hash-partitioned global indexes can improve the performance of indexes where a small number of leaf blocks in the index have high contention in multiuser OLTP environments. Hash-partitioned global indexes can also limit the impact of index skew on monotonously increasing column values. Queries involving the equality and IN predicates on the index partitioning key can efficiently use hash-partitioned global indexes.

The syntax for creating a hash partitioned global index is similar to that used for a hash partitioned table. For example, the following statement creates a hash-partitioned global index:

Example : Creating a hash-partitioned global index

CREATE INDEX hg_idx_tab ON tab (c1,c2,c3) GLOBAL
     PARTITION BY HASH (c1,c2)
     (PARTITION p1  TABLESPACE tbs1,
      PARTITION p2  TABLESPACE tbs2,
      PARTITION p3  TABLESPACE tbs3,
      PARTITION p4  TABLESPACE tbs4);

c) List Partitioning

List partitioning was added as a partitioning method in Oracle 9i, Release 1. List partitioning allows for partitions to reflect real-world groupings (e.g.. business units and territory regions). List partitioning differs from range partition in that the groupings in list partitioning are not side-by-side or in a logical range. List partitioning gives the DBA the ability to group together seemingly unrelated data into a specific partition.

Example-1: 
CREATE TABLE dept
(deptno number not null,
 deptname varchar2(20),
 state varchar2(50),
 locationid number(5))
 PARTITION BY LIST (locationid)
 (PARTITION loc1 VALUES (10101,10102),
 PARTITION loc2 VALUES (10103, 10104),
 PARTITION loc3 VALUES  (10105,10106, 10107),
 PARTITION loc4 VALUES (10108, 10109,10110),
  PARTITION loc5 VALUES (10111, 10112)) tablespace HR;

-- insert the values
insert into dept(deptno,deptname,state,locationid) values (1,'accounts','TN',10101);
insert into dept(deptno,deptname,state,locationid) values (2,'finance','AP',10103);
insert into dept(deptno,deptname,state,locationid) values (3,'human resource','UP',10109);
insert into dept(deptno,deptname,state,locationid) values (4,'operations','TN',10102);

-- Verify the records in partition
select count(1) from dept partition(loc1);  --output will be 2
select count(1) from dept partition(loc2);  --output will be 1
select count(1) from dept partition(loc3);  --output will be 1

I) Interval Partitioning: ( Oracle11g new feature)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Interval partitioning is an extension of range partitioning, where the system is able to create new partitions as they are required. The PARTITION BY RANGE clause is used in the normal way to identify the transition point for the partition, then the new INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point.

J) Partition Advisor :

The SQL Access Advisor in Oracle Database 11g has been enhanced to generate partitioning recommendations, in addition to the ones it already provides for indexes, materialized views and materialized view logs. Recommendations generated by the SQL Access Advisor – either for Partitioning only or holistically - will show the anticipated performance gains that will result if they are implemented. The generated script can either be implemented manually or submitted onto a queue within Oracle Enterprise Manager.

With the extension of partitioning advice, customers not only can get recommendation specifically for partitioning but also a more comprehensive holistic recommendation of SQL Access Advisor, improving the collective performance of SQL statements overall.

The Partition Advisor, integrated into the SQL Access Advisor, is part of Oracle's
Tuning Pack, an extra license option. It can be used from within Enterprise Manager or via a command line interface.

Click here to read more from oracle-base site.

In short about partition:

Partitioning is for all applications. Oracle Partitioning can greatly enhance the manageability, performance, and availability of almost any database application. Partitioning can be applied to cutting-edge applications and indeed partitioning can be a crucial technology ingredient to ensure these applications’ success. Partitioning can also be applied to more common place database applications in order to simplify the administration and costs of managing such applications. Since partitioning is transparent to the application, it can be easily implemented because no costly and time-consuming application changes are required. This is a pricing option with licence.