“Divide and Conquer” with partitioning in OracleFebruary 29, 2012 at 5:25 pm | Posted in Oracle, Technical Tips | Leave a comment
Tags: Oracle, oracle partitioning data management optimizer range list hash
One of the best features of the Oracle RDBMS over the last ten years has been partitioning. If your shop needs to handle large volumes of data, if you are running OLAP applications, if you often need to move datasets around, if you are responsible for staging data for a rolling twelve-month period, or if you want to improve the performance of you SQL statements, then this is the tool for you!
The basic idea behind partitioning is “divide and conquer”. We’re going to take a very large object (such as a table with six million rows), divide it into logical piece parts, and then manage the smaller pieces. Oracle provides us with a number of different strategies on how we can divide our data. If your data represented orders from your customers, and that was a stored column in the table, you could partition your data by order date. You could break the data into twelve partitions, one for order data for each month over the past year. You could also break your table data into partitions by a selected range. For example, all customers whose last name begins with A – C could go in partition 1, D – H into partition 2, etc.
Another way to “divide and conquer” is to partition by a list when you have discrete values. For example, customers who live in Illinois and Indiana go into partition 1; Pennsylvania, Ohio, and Kentucky go into partition 2; and so on.
Now that you’ve got the idea, let’s talk about the benefits of this tactic. If you can divide your data into 10 partitions, each partition can go into its own tablespace. Radical, huh? That means if you need to move the customers whose last names begin with A – C, you only need to move that single partition, not the entire table. If the data is divided into ten partitions of approximately the same number of row, you will only need to move 10% of the data, instead of 100%. The same is true if you only need to back up one specific partition; your typical O/S tasks would only take 10% of the time they used to take.
The optimizer is also aware of partitioning. If you query your customer table looking for a customer whose last name is Baker, the optimizer knows that it only needs to search in Partition1, since this partition contains customers whose last names range from A – C. You don’t have to spend any time searching through the other nine partitions, because Oracle knows it will never find what you’re looking for in any of the remaining partitions. You can also choose to create an index on just that partition.
There’s still a lot more to know about partitioning, and I encourage you to take a look at it, especially if the size of your Oracle datafiles is starting to become unmanageable, and you’re seeing a degradation of performance at the same time.
Start with the following resources:
I hope that you can learn whether this technique is something that could benefit you and your organization.