When the latest generation of the world’s most popular database became available in the market, everybody is keen to know what makes it different from the previous versions.
Tom Kyte, Oracle Vice President, shed light on the new features of Oracle Database 12c, a database that is designed for the cloud.
Advantages of the new partitioning system
- You can mix interval partitioning with reference partitioning.
In Oracle Database 11g, users cannot combine reference and interval partitioning. With 12c, you can mix these two capabilities.
- Perform Data Definition Language (DDL) operations on multiple partitions.
Using Oracle Database 12c enables you to do DDL operations on more than one partition in a table at a time. An example given by Kyte was the capability of 12c to drop and truncate multiple partitions in a table.
- Cascade operations from parent table to child table.
Oracle Database 12c allows some partition operations, like truncate and drop, to cascade. Users can truncate a parent-child partition and have it cascade down to the child.
- Global index maintenance capabilities.
In the past, if you were to perform any partition operation in a table that had a global index, the global index would immediately go invalid. You would either then need to rebuild that global index or Oracle would maintain it in real-time generating undo and re-do using lots of resources.
With Oracle Database 12c, you can perform partition operations against the table, and drop or truncate that partition immediately. You can also postpone the maintenance of the index.
Comparing how the 2 versions work: Oracle Database 11g versus 12c
Colin McGregor, Oracle Database information development manager, demonstrated how things work in 11g compared with 12c.
In the sample, he first illustrated the partitioning in 11g with a script that creates a large partitioned table, populates it, and creates a global index. The table was created with 99,999 rows, with one partition containing most of the data. After running the script, the table was created with four partitions, then a statement is inserted into the table. Each statement doubles the size of the table until it reached about 12 million rows. When he dropped the largest partition and updated the index at the same time, the drop operation was still running, which was the expected behavior in 11g. The database was busy cleaning up the index. The drop operation took 51 seconds. There were three partitions left with only 129 rows, and the index was valid.
When he switched to 12c to show the new capabilities, the drop partition operation took only an impressive 0.2 second. The scenario was the same – three partitions left, 129 rows, and the index was valid. However, in 12c, there were orphaned entries, which means there were some junks left in the index.
McGregor explained that the drop operation ran faster in 12c because unlike in 11g, the new version does not perform index cleanup at drop time. In 12c, you can postpone index maintenance and cleanup later. Cleanup is normally not necessary unless you have a relatively high percentage of orphaned entries.
As such, drop or truncate operations on partitioned tables perform much better in 12c when you are using global indexes. Oracle enables you to clean up your index later during your maintenance window.
In the end, the key features of Oracle Database 12c help customers in optimizing their IT resources while enhancing users’ service levels.
Contact Four Cornerstone now if you want to learn more about 12c partitioning improvements. Our Oracle Consulting in Dallas Fort Worth will show you the benefits of upgrading to the new multitenant architecture.
