Uncategorized

What Oracle Database 12c’s new partitioning features can do for your business

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

  1. 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.

  1. 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.

  1. 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.

  1. 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.

AI Governance

What is AI TRiSM?

If reading now about AI TRiSM for the first time, it might be good read on and learn what it’s about because it leads the...

Keep Reading

Cloud Blog

4 Ways To Benefit from…

One of the benefits you get when you work with cloud applications is that you often have quarterly updates that are packed with features. This...

Keep Reading

Artificial Intelligence

Data and Analytics: Cross the…

  Artificial intelligence is a manna sent from digital heaven. That’s how blessed your business can get if you immerse into the AI of things....

Keep Reading

Business Intelligence Blog

How Brand Names Survive in…

  The age of digital marketplace has made it possible for unknown and smaller companies to compete with better-known and well-established brands. Take for example...

Keep Reading

Artificial Intelligence

The Phenomenon That Is Artificial…

  Artificial intelligence is when a machine does cognitive functions that are more associated with humans, such as thinking, learning, problem solving, and reasoning. As...

Keep Reading

Cloud

Digital Transformation in Banking: Shift…

  The start of a new decade is already shaping up to be exciting for financial services, especially for banks as they continue to compete...

Keep Reading

Business Intelligence Blog

Data Science and Its Economic…

It would seem that artificial intelligence is the focus of businesses that are looking to future proof their organizations and stay competitive. And why not?...

Keep Reading

Data Blog

Ensure Efficient Data Science and…

In an ever-expanding landscape called Internet of Things and the exploding development of artificial intelligence, we are bombarded with complex methods of integrating data science...

Keep Reading

Business Intelligence Blog

Cybersecurity: The Top 5 Expectations…

  If you think about it, cybersecurity is closely tied to human rights, privacy, freedom, and even basic safety. As a whole, we have become...

Keep Reading

Business Intelligence Blog

Tech Security Should Be Easy…

  IT professionals know that tech security is important in everything that they do. And that there are a lot of products, tools, innovations, and...

Keep Reading

Live Chat | Emergency