Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted October 28, 2013

Oracle Database 12c: New Features for Partitioned Tables

By Karen Reliford

Table partitioning is a feature often used in large databases with very large tables as a means to help improve the performance of accessing data in those tables. Partitioning offers advantages such as being able to spread out and balance IO across multiple devices through placing partitions into separate tablespaces and partition pruning, where the cost based optimizer can read only the blocks in specific partitions into memory when the where clause criteria match the partitioning options that were specified when the table was created.

There are several partitioning methods available. Below is a list of the different partitioning methods that are available in the Oracle Database.

  • Range - Range partitioning distributes the rows based on a specific range of values in a column (very frequently used to partition on date based data). Range partitioning was introduced back in Oracle 8i.
  • Hash - Hash partitioning divides the rows based on an even but somewhat random distribution by specifying a particular column to be used for the hash key. Hash partitioning was the first type of supported partitioning and goes back to Oracle 8.
  • List - List partitioning divides the rows based on a discreet list of values for a column (often used to partition, based on geography such as state names). List partitioning was added as a new partitioning method in Oracle 9i.
  • Interval - Interval partitioning is an extension of range partitioning that instructs the database to automatically create a new range partition when data is added that does not belong to any of the existing partitions. Interval partitioning was a new feature introduced with Oracle 11g.
  • System - System partitioning is somewhat similar to hash partitioning but without the need of specifying a column to use as the hash key column. Like Interval partitioning, system partitioning was introduced in 11g.
  • Reference - Reference partitioning is a feature that partitions the rows in a child table to match how its parent table rows were partitioned without having to duplicate the partition key column in the child table. For example, if the orders table is partitioned by order date, the child table can have its rows partitioned to match, but without having to duplicate the order date in the child records. A formal foreign key constraint must be defined on the two tables in order to be able to configure reference partitioning on the child table. Reference partitioning is also an 11g feature.

In addition, composite partitioning is available where we can partition a table two ways, for example, first by range as the primary partition and then by list as sub-partitions within each primary partition. Over the different versions of Oracle from Oracle 9i through Oracle 11g, virtually every combination of composite partitioning is now supported.

In Oracle Database 12c, several new features have been added to enhance working with and maintaining partitioned tables.

Online Partition Move Enhancements

Prior to 12c, several partition maintenance commands would cause the partition to be locked against DML commands until the partition maintenance command completed. For example, issuing an ALTER PARTITION MOVE command would prevent DML from being able to be executed until the move was complete. If the table had very large partitions, this would impact the ability of users to do work while the partition maintenance operations were being done, which could be problematic in systems that needed to support 24x7 user interactivity.

In 12c, that same command can now be issued as an online command, which no longer prevents concurrent DML commands from being executed.

An often-used partition maintenance command is the ALTER TABLE MOVE command, which can be used to move older partitions to different storage devices by moving the partition to a different tablespace. One advantage of being able to move partitions like this, especially with range partitions, is that they can be moved to a lower cost and perhaps slower storage device once the data in the partition is old enough that it is no longer very frequently accessed. Additionally the move can incorporate compressing the data in the partition as it's being moved. Here are a couple of examples of the new online move syntax for moving partitions:

ALTER TABLE our_sales
 MOVE PARTITION our_sales_part_1
 TABLESPACE old_data_ts
 UPDATE INDEXES ONLINE;
 
 ALTER TABLE our_sales
 MOVE PARTITION our_sales_part_1
 COMPRESS {BASIC/FOR OLTP}
 UPDATE INDEXES ONLINE;

Reference Partitioning Enhancements

Reference partitioning is a very new partitioning style, and there were several limitations not only in setting up the child table, but also in doing partition maintenance operations on the parent table and ensuring that the child partition tables would be kept synchronized with the parent. In order to enhance working with reference partitioned tables, three new features have been added in Oracle Database 12c.

First, reference partitioning can now be specified on a child table where the parent table has been partitioned using interval partitioning. In 11g this was not supported.

Second, a new CASCADE option is available for TRUNCATE operations on the parent table, which will automatically also truncate the corresponding child table partitions. In 11g the TRUNCATE partition command would have be issued specifically on the child table.

Third, the CASCADE option has also been added to the partition EXCHANGE operation and will modify the child table partitions to match the parent table modifications. Like the TRUNCATE option, in 11g any EXCHANGE commands would have had to be specifically issued against the child table.

Multi-Partition Maintenance Operations

Prior to 12c, merge and split partition operations were limited to only two partitions at a time. This meant that if we wanted to take an existing partition and split it 12 times (for example take a year-long partition and split it into 12 monthly partitions) we would have to issue 11 separate SPLIT PARTITION DDL commands.

The same thing would pertain to trying to merge 12 separate partitions into one single partition would have required 11 MERGE PARTITION DDL commands.

ALTER TABLE my_part_tab SPLIT PARTITION part_1 INTO
 (part_one VALUES LESS THAN (50),
 part_two VALUES LESS THAN (100),
 part_three VALUES LESS THAN (150),
 part_four;
 
 ALTER TABLE my_part_tab
 MERGE PARTITIONS
 part_one, part_two, part_three, part_four
 INTO PARTITION part_1;

Not only have both MERGE and SPLIT commands been modified, but also the ALTER TABLE ADD PARTITION and ALTER TABLE TRUNCATE PARTITION and ALTER TABLE DROP PARTITION commands have been enhanced to allow multiple partitions to be added in the same command.

ALTER TABLE my_part ADD
 PARTITION (my_new_part_10) VALUES……
 PARTITION (my_new_part_11) VALUES……
 PARTITION (my_new_part_12) VALUES……;
 
 ALTER TABLE my_part TRUNCATE
 PARTITIONS (my_new_part_10, my_new_part_11);
 
 ALTER TABLE my_part DROP
 PARTITIONS (my_new_part_10, my_new_part_11, my_new_part_12);

Partitioned tables continue to be used in many systems as performance enhancing options on large tables, and with the new features that have been added, Oracle Database 12c has taken some interesting steps to make managing them more effective and efficient.

See all articles by Karen Reliford



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date