Oracle 12 New Partitioning Features

Hi,

I’m still learn from other speakers!  Last week, Quest sponsored a Toad Maven seminar event downtown Chicago.  I did an Advanced Indexing presentation and showed tuning topics in Toad.  Watch for my blog announcements for future events.  Greg Martin (quite the Oracle expert) was the afternoon speaker and showed this link to Oracle documentation.

Oracle Database 12c: Interactive Quick Reference

Oracle12 Interactive Quick Reference

I just love the fifth tab:

Oracle12c Architecture Diagram

I have a similar but simpler architecture slide I created to use in my courses to discuss the Oracle architecture.  I just might switch over to this website.

***True Story***  For years, the TUSC Consulting Group created a poster that had the actual V$ and DBA views on them.  They would have pallets shipped to various trade shows and they would give them away at their booth.  The poster was setup something like this architecture diagram and had the view names and columns spelled out…much like the click of a mouse here.  I would put this up on the wall of my cubicle.  I would write useful scripts based on content on this poster.  Each new rev of the database, you could depend upon TUSC. to put up a new poster.  The obsolete posters are now wallpaper in my attic!

In the above diagram, you click on the various areas and it will allow you to drill to that part of the Oracle documentation!  Notice the other tabs such as DBA Views, Performance Views, Architecture Views, Multi-tenant Architecture (a recent blog), and Background Processes. 

OK, so this is an article on Oracle12 Partitioning New Features.  What does this have to do with that topic?

Plenty.  Check this out!

Oracle12 DBA Views on Partitioning

Click on a view and it shows you the columns.

You can use this information to easily research the types of partitions, stats on the partitions, partitioning key values, and so much more.  I’ve used this kind of information in the past to use SQL to create SQL…to create useful reports, to create scripts to re-create what currently exists

On with Oracle Partitioning.

Oracle introduced partitioning way back in Oracle8.  The original partitioning consisted of range partitioning along with local (uses table partitioning key values) and global partitioned indexes (other keys than the partitioning key value). Oracle8.1 introduced hash and composite partitioning (range and hash partitioning combined).  Oracle9 introduced list partitioning, Oracle9.2 introduced a range-list partitioning option.  Oracle10 introduced global hash partitioning.  Oracle10.2 allowed for 1 million partitions per table!  Oracle11 expanded upon their new virtual column and made a partition option out of it.  This release also introduced more composite options and reference partitioning. Oracle11.2 introduced hash and pruning along with expanded reference partitioning.  Oracle12 introduced interval-reference partitioning.

Oracle12 also introduced the ability to move partitions around online.  I will illustrate.

Oracle12.2 introduced multi-column list partitioning.  This feature is nice to segment co-related data such as auto make and model.  This feature supports up to 16 partition key columns.  This feature supports:

• Both partitions and subpartition strategies
• Heap tables
• External tables
• Reference partitioning and auto-list features

Sample Syntax:

Create table AUTO (auto_make  varchar(30),
                  Auto_model varchar(30)
PARTITION BY LIST (auto_make, auto_model)
     (partition P1 values (‘FORD’,’TAURUS’),
(partition P1 values (‘VW’,’JETTA’),
(partition P2 values (‘SUBARU’,’OUTBACK’),
(partition P3 values (‘TOYOTA’,’COROLLA’),
(partition P4 values (‘CHEVROLET’,IMPALLA’),
(partition P5 values (‘DODGE’,CHALLENGER’),
. . .
);

The auto-list, mentioned above, is also a new feature.  This feature is intended to help set up list partitioning when there are a lot of unique values to be partitioned.  Partitions are automatically created as different key value data arrives.

This feature has several items to take note of:

• There is no default partition
• The partition names are system generated
• Syntax allows the conversion of list partitioning into this auto-list partitioning

Sample Syntax:

Create table AUTO (auto_make  varchar(30),
                  Auto_model varchar(30)
PARTITION BY LIST (auto_make) AUTOMATIC
     (partition P1 values (‘FORD’));

Another Oracle12.2 new partitioning feature is interval subpartitioning.

Interval partitioning was introduced in Oracle12.1 and is expanded here.  Interval partitioning takes a number or date column and, for lack of a better term, is a way of sub-partitioning the data identified by the range clause.

Oracle12.2 extends this functionality by allowing interval sub-partitioning.  This feature is an extension of the range partitioning. It creates equal-sized range partitions, and the segments are actually created as new data arrives.

Some rules for interval sub-partitions include:

• No max-value
• No add partition
• Sub-partition template is mandatory
• Interval identical for all partitions
• Maximum of 1 million sub-partitions per table
  • Either 1 partition with 1 million sub-partitions
  • OR 1 million partitions with 1 sub-partition each

Sample Syntax:

Create table CARS (auto_make  varchar(30),
                  Auto_model varchar(30),
                  Purchase_date date)
PARTITION BY LIST (auto_make)
SUBPARTITION BY RANGE (Purchase_Date) INTERVAL
     (NUMTOYMINTERVAL(1,’month’))
     SUBPARTITION TEMPLATE
        (subpartition sp1 values less than
            TO_DATE(’15-03-2015’,’dd-mm-yyyy’)))
        (partition p_vw values (‘JETTA’));

Some Oracle12.1 partitioning new features include:

• Online support for frequently used DDL
  • Create index
  • Add column
  • Add constraint
  • Drop index
  • Drop constraint
  • Alter table …

Some Oracle12.2 partitioning new features include:

• Online support for
  • Alter table modify non-partitioned table to partitioned table
  • Alter table move online for heap tables
  • Alter table split partition online

 Sample Syntax:

Create table AUTO (auto_make  varchar(30),
                  Auto_model varchar(30);
 
ALTER table AUTO MODIFY
PARTITION BY LIST (auto_make)
     (partition P1 values (‘FORD’),
(partition P1 values (‘VW’),
(partition P2 values (‘SUBARU’),
(partition P3 values (‘TOYOTA’),
(partition P4 values (‘CHEVROLET’),
(partition P5 values (‘DODGE’),
. . .
) ONLINE;

In addition, Oracle12.2 partitioning supports filtering of data when archiving or splitting partitions.

Sample Syntax:

ALTER Table AUTO MOVE partitioning p1_2015 TABLESPACE archive_TS INCLUDING ROWS WHERE MAKE = ‘FORD’;
Yet another feature is Read Only partitions.  Now this attribute can be applied at the partition level.
Sample Syntax:
Create table AUTO (auto_make  varchar(30),
                  Auto_model varchar(30),
                  Purchase_Date date) READ ONLY
PARTITION BY List (auto_make)
     (partition P1 values (‘FORD’),
(partition P1 values (‘VW’),
(partition P2 values (‘SUBARU’),
(partition P3 values (‘TOYOTA’),
(partition P4 values (‘CHEVROLET’) READ WRITE,
(partition P5 values (‘DODGE’),
. . .
); 

A few other Oracle12.2 new features named are:

• Create Table for Exchange
• Partitioned External Tables
• Reduce Cursor Invalidations for DDL

Click here to see Oracle Documentation on Oracle12.2 New Features.

I hope this article helps educate you with the new partitioning features of Oracle12c.

Dan Hotka

Author/Instructor/Oracle Expert

www.DanHotka.com

Dan@DanHotka.com