Oracle Partitioning Fundamentals


It’s a topic which a large amount of information could be written. There are so many ways to partition, good ways, bad ways, complicated ways and out right ugly ways! If you have read through the Oracle documentation you will know that there is a document written purely for very large databases and partitioning. It’s quite large…

What is Partitioning and Why Would you Want to Partition?

I’d like to use an analogy here to explain partitioning. Why? Because I think that in this case it will help to explain more clearly the benefits of partitioning and why you would use it. So, let’s imagine that you have a house and you need to renovate it. The builders come in and say that you have two options: 1) Move out of the house while every room is renovated at the same time. 2) Renovate 1 room at a time but have use of the others while this is happening. So, what’s the meaning of this…? Let me explain.

Think of the house as your table/index/Index Organised Table (IOT). In other words the segment which is partitioned. Now imagine that you need to perform some kind of management operation (renovation/move) on this segment. If it is partitioned you can move one partition at a time without losing access to the others. If it wasn’t partitioned you would have to move the whole segment in one go meaning that it is offline and unavailable for users. This, in short, summarises one of the biggest reasons for using partitioning; ease of administration.

You can drop partitions very quickly without causing large amounts of UNDO and REDO like when you do a delete statement. You can truncate partitions just in the same way you truncate a table. All while the other partition segments remain available. It allows you to divide up your data into more manageable chunks. And the best part is that you don’t have to re-design your application to use it; the application queries don’t even need to know whether or not a table has been partitioned. As an added benefit you can change the storage characteristics for each partition. For example, you might decide that you want to move all sales records older than 1 year onto slower disk.

What I’d like to do now is just make a few bullet point lists about the basics, benefits and strategies of partitioning.

Basics of Partitioning

  • To the DBA, a partition can be managed as a whole or at the partition level. This gives them flexibility in the operations that they can perform against the object
  • Every row has to be in one and only one partition, so there must be a key value (column or columns) which define it uniquely and which it is partitioned on
  • Old data types such as LONG and LONG RAW are not supported for partitioning so it’s time to migrate them to LOBs
  • If your data will become read only in older partitions you could consider enabling compression to save disk space and buffer cache usage, albeit at some CPU cost

Benefits of Partitioning

  • Increases availability of the application data because administrative tasks can be performed at a lower level of granularity than the whole table
  • Manageability becomes easier because the segments (partitions) are much smaller in size
  • Performance in SQL queries can improve if scanning only a single partition instead of the entire table. This is known as partition pruning
  • Loading of data into a data warehouse becomes very easy because you don’t have to touch the main table while loading data into a new table and then you can use partition exchange to swap the data from a new table into a partition of the existing table

Partitioning Strategies

There are three main strategies which you can use to partition a segment:

  1. Hash – Distribute rows evenly among all partitions based on an Oracle supplied hashing algorithm
  2. Range – Partition by a value, such as date range
  3. List – A user defined list determines where rows will placed, such as regions like London, New York, Tokyo, etc

Composite Partitioning

This allows the combination of two of the above strategies together. So, what this means is that you might have the data initially range partitioned by date and then within each of those date range partitions have it further subdivided (partitioned again) into list partitions based on region.

Interval Partitioning

New to Oracle 11g in the partitioning department is the Interval Partitioning approach. Using this option you can create a partitioned table with as little as one partition and specify the interval at which you want to partition the table. For example, monthly partitions. Then, as the days go by and the data being inserted into your table increases beyond the end of the month Oracle will automatically create a new partition for you. There are some restrictions:

  • You can only have one partition key column and it must be of NUMBER or DATE data type
  • You can’t use it on IOTs
  • Domain level indexes are not permitted
  • Composite partitioning is only allowed using Interval-range, hash and list options

Reference Partitioning

Another new feature which 11g brings to the table is Reference Partitioning. Using this approach you can use referential constraints to partition the parent and child tables. It allows the child table to be partitioned based on the primary key partition without the need to duplicate the parent key, partitioned column into the child table. For example, if you have an ORDERS table which is partitioned on a date range and a child table, ITEMS, which hold the items ordered and they are linked by an ORDER_ID column (ORDER_ID being the primary key in the ORDERS table and the foreign key in the ITEMS table), you can partition both the ORDERS and ITEMS tables on the ORDER_ID column without having to store the ORDER_ID in the ITEMS table.

It should be noted that you cannot use Interval Partitioning with Reference Partitioning.

Virtual Column-Based Partitioning

It’s now possible to use a non-existing column to partition a table. The partition key can actually be the result of a function based on other values stored within the table. For example, you could have a long account number as an existing column in your database table. You may want to partition based on the first n digits of the account number. You could use Virtual Column-Based Partitioning to SUBSTR the first three characters and partition based on that value.

With all of the above strategies at your disposal there really are a huge number of combinations of partitioning strategies available to you. If you have large tables and want to make them more manageable then partitioning is probably a good way to deal with them. Also, I would highly recommend putting partitioning in place BEFORE you roll anything to your live environment. Once it’s gone live it is much harder to retro fit a partitioning approach than it is when you are designing and implementing them in the first instance.

Now we’ve covered the table side of partitioning let’s have a look at how to partition indexes and what options you have available to use.

Local Partitioned Indexes

  • Each index partition maps directly to one of table partitions
  • Partition maintenance operations do not invalidate the entire index, only the local index which is being worked on
  • The addition, dropping and splitting of local indexes is completed by the database
  • A local index can be unique but the partitioning key of the table must be part of the index’s key columns

Global Partitioned Indexes

  • You can have globally partitioned indexes which means that the partitioning is separate from the underlying table partitioning method
  • You must have a MAXVALUE partition so that all rows in the table can be indexed if they fall outside the range of one of the partitions (range partitions only)
  • Using hash partitioning spreads out the index block contention for monotonically increasing values like a sequence number

NOTE: If you need to perform some kind of maintenance operation which will invalidate your global index it might be worth looking at using the UPDATE GLOBAL INDEXES clause to the operation, such as with DROP, EXCHANGE, MERGE, MOVE or SPLIT PARTITION operations. This will take considerably longer but it will mean that your index remains online and usable throughout the operation. If you have an application which is supposed to be available 24*7 it’s worth taking the hit of a longer running operation but full availability over downtime but a faster operation.

  • You can use bitmap indexes on partitioned tables but they have to be locally partitioned.

Want a specific example? I have written up an article on a specific example of how I have solved a problem with partitioning for a 24*7 application over on my blog. The article is called Oracle Partition Exchange Vs Partition Splitting.

Like it, share it...

Category: Articles

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *