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:
Create table AUTO (auto_make 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:
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:
Create table CARS (auto_make varchar(30),
PARTITION BY LIST (auto_make)
SUBPARTITION BY RANGE (Purchase_Date) INTERVAL
(subpartition sp1 values less than
(partition p_vw values (‘JETTA’));
Some Oracle12.1 partitioning new features include:
Some Oracle12.2 partitioning new features include:
ALTER table AUTO MODIFY
(partition P1 values (‘FORD’),
(partition P1 values (‘VW’),
(partition P2 values (‘SUBARU’),
(partition P3 values (‘TOYOTA’),
(partition P4 values (‘CHEVROLET’),
(partition P5 values (‘DODGE’),
In addition, Oracle12.2 partitioning supports filtering of data when archiving or splitting partitions.
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.
Purchase_Date date) READ ONLY
PARTITION BY List (auto_make)
(partition P4 values (‘CHEVROLET’) READ WRITE,
A few other Oracle12.2 new features named are:
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.