Automatic Partitioning with Autonomous Database

February 22, 2022 | 3 minute read
Hermann Baer
Senior Director Product Management
Nigel Bayliss
Product Manager
Text Size 100%:

As you probably know, finding an appropriate partitioning strategies requires deep knowledge of the application workload and the data distribution. When you perform manual partitioning, you must analyze your workload and make choices about how to apply partitioning to tables and indexes to improve the performance of applications.

You might be wondering: why can’t Autonomous Database work all this out for me?

So here is an exciting announcement that rolled out a while ago that you might have missed.

Autonomous Database now comes with an Auto Partitioning feature, which uses spare “brain”, or CPU, cycles to find an optimal partitioning strategy for your large tables. It’s a partitioning-expert-in-a-box.

How does Auto Partitioning work?

Here’s a summary of the process. Start at the top and follow the flow clockwise. It outlines how Auto Partitioning works and illustrates how the database does all the work for you:

Automatic Partitioning - Workflow

Here’s some more detail:  

Capture: A background task captures workload SQL and stores it in the automatic SQL tuning set. You don’t have to worry about understanding your workload in theory. The database collects it automatically for you and tracks what work is done on your system.

Analyze: After we have a workload, what are we supposed to do with it, and what tables shall I look at? Don’t worry. The smarts to analyze candidate tables and to come up with an optimal partitioning scheme are taken care of. That is the database’s job and is performed inside the PL/SQL dbms_auto_partition.recommend_partition_method function. That’s a purely theoretical exercise inside the database using the information about your workload, your largest tables, and your data distribution.

Verify: After the database thinks to know what makes your application faster, it actually wants to know it for sure: Oracle will take a selection of workload queries and test-execute them against a hidden, partitioned copy of your candidate table(s), putting its findings to the test. We take this very seriously since we are dealing with your most precious assets – your tables. Yes, it takes time and resources, but afterward, you will know what performance you can expect from Auto Partitioning. Again, the function dbms_auto_partition.recommend_partition_method takes care of this (you can specify a time limit if you like).

Recommend: After the verification, the database knows precisely what Auto Partitioning will do to your application. Based on its real-world findings, it will recommend applying Auto Partitioning to some of your tables – or not. If you want to see a report, you can use dbms_auto_partition.report_last_activity.

Implement: Assuming that Auto Partitioning identifies optimal partitioning strategies for some of your tables, it’s time to modify your nonpartitioned tables to adopt the identified partitioning strategy. Your tables are partitioned on-line using dbms_auto_partition.apply_recommendation.

You can control when everything happens and how many of the previous steps will be completely automated. For example, you can:

  • Use Auto Partitioning to only look at one or some specific tables or schemas for analysis or figure that out itself.
  • Configure Auto Partitioning to perform everything in one shot inside recommend_partition_method.
  • Execute recommend_partition_method on a clone database, copy the DDL and execute generated in the recommend step, and execute this on the source database whenever you want.

Auto partitioning delivers the most significant value-add for large tables. Still, for just getting you to know and understand how it works, we’ve made some changes in Oracle’s Always Free 19c Database environment so you can experiment with this feature on a smaller dataset than usual.

How can I try auto partitioning?

Now it’s time to see all of this in reality. To help you explore how this new feature really works we have created a new Live Lab to demonstrate Auto Partitioning.

The live lab takes you through a few simple steps, from a workload you’ve executed against a non-partitioned table all the way to a shiny-new partitioned table.

The lab is completely free and it only takes about 30 minutes to explore how this amazing new feature works.

Enjoy, and let us know what you think!

Hermann Baer

Senior Director Product Management

Hermann is a Senior Director of Product Management in the Oracle Database organization. He and his team focus on Oracle's core functionality, such as Oracle Partitioning, the Oracle Optimizer, and analytical SQL. His team also looks after semi-structured data processing, such as the relational SQL/JSON capabilities, Oracle Text, and more recently Autonomous JSON Database and the Oracle Database API for Mongo DB.

Hermann has held several positions at Oracle in Sales Consulting and Consulting, dealing with the database for quite some time

Nigel Bayliss

Product Manager

Nigel is the product manager for the Oracle Optimizer. He's worked with Oracle technology since 1988 and joined Oracle in 1996. He's been involved in a wide variety of roles including development, benchmarking, consulting and pre-sales. 

Previous Post

Autonomous Database Newsletter - Feb 1, 2022

Keith Laker | 49 min read

Next Post

Autonomous Database Newsletter - March 08, 2022

Keith Laker | 35 min read
Everything you need to know about data warehousing with the world's leading cloud solution provider