Partitioning and DBMS_REDEFINITION

Unscientific research and Twitter gossip reveals that fewer than 15% of folks out there take advantage of Oracle’s partitioning (Oracle Docs) support for tables and indexes. Folks that build their own solutions in-house are more likely to discover and use partitioning. If you use a packaged 3rd-party database solution and it uses partitioning, then I would love to hear about it!

Disclaimer: Partitioning, like indexing, is a performance tool. It is not a silver bullet.

Partitioning allows for the data in a table to be stored across tablespaces. There are multiple schemes you can employ, including:

Range (Oracle Docs), most popular for organizing data by date ranges, e.g. financial year or quarters

List (Oracle Docs), useful for organizing data that is not contiguous, e.g. numbers 1, 3, 5, 7, 11 or fruit by apple, orange, banana

Hash (Oracle Docs), useful for organizing strings, Oracle does the work for you, you just supply the column and number of partitions you want

You can go crazy and use composite and sub-partitions, but I’m guessing that would be an even smaller chunk of folks that that partition their data at all.

If you learn better by example, than take a look at SH.SALES or SH.COSTS. These are both Range-Partitioned tables supplied by Oracle in the SH demo schema. If you still rely on SCOTT or HR, then I recommend you ditch these for SH – in your development instance of course.

What About SQL Server and How Much Does This Cost?

Your Problem: Lots of data, poor performance.
The Solution: Partition the data, spread the I/O load across multiple storage containers.

If all of your queries are of the nature ‘SELECT * FROM …’ and you don’t know how to write a WHERE clause, then stop here. A poorly partitioned table is probably going to be worse than a non-partitioned table.

If you are working with a table (or index) that has grown too big for it’s britches, then you may want to PARTITION it. Spread the data around. Let the folks who just want to see sales data from 2006 go grab that information directly instead of writing a WHERE clause. Have all that data come off the slower/cheaper storage space than the SSDs you have reserved for the current quarter’s data. Unfortunately, you cannot partition a table using an ALTER.

Introducing DBMS_REDEFINITION (Oracle Docs)
Online table redefinition allows you to restructure your table in production without making the data unavailable. You may be comfortable using temp tables to move data around, but even staging the data and moving it around will make the table/data unavailable for a period of time. The DBMS_REDEFINITION package allows you to get around this problem.

@ORACLEBASE (twitter | blog) has a great blog post on how to you this package for partitioning an existing table.

Don’t forget to test your performance before and after your changes!

What About SQL Server and How Much Does This Cost?