Table partitions in Oracle
can be extremely useful and becoming proficient in managing them doesnt take
too long to accomplish. The benefits of using partitions (and you should
realize them since you must pay extra for this feature) are listed in various
places, but the short and sweet of it is shown below (source):
Faster PerformanceLowers query times from minutes to seconds
Increases Availability24 by 7 access to critical information
Improves ManageabilityManage smaller 'chunks' of data
Enables Information Lifecycle ManagementCost-efficient use of
Just how much extra do you
have to pay for partitioning? That depends on where you start from.
Partitioning is available only with the Enterprise Edition, so if youre
starting from a lower level edition, youll have to factor in the edition
differential. Even if you're already starting at the EE level, your cost is
around $5,000 for the named user option, but more than likely, $10,000 for the
This article isnt directly
about the cost of partitioning. Cost is mentioned because if youre going to
pay extra for this feature, there are some partition pitfalls you should avoid,
and knowing what they are ahead of time (and even if you're already using
partitioning) can help prevent hours of down time. Down time is another type of
cost you may not be able to afford.
Partitioning option by Oracle version
Partitioning seems like
something Oracle should have been able to do since day one, but interestingly
enough, Oracle8 was its introduction.
Hash and Composite
Enhanced features, fast
split, IOT, indexes
Partition operations were
admittedly difficult (an iLearning video even says so) in earlier versions, and
this difficulty especially in maintenance operations - made many users leery
of implementing this feature.
Table partition options are
straightforward, and five of them are: range, list, hash, composite range-hash,
and composite range-list. Options for index-organized tables include range,
list and hash. Partitioned indexes dont seem to get nearly as much attention
as partitioned tables, and a lack of understanding how the index options differ
can lead to significant pitfalls.
You have three choices when
it comes to indexes: local, global partitioned, and global non-partitioned. A
local index maps one-to-one with the partitions of the table. A global
partitioned index can be based on a different key and even have a different
number of partitions. A global non-partitioned index is essentially one big
index on the partitioned table. The diagram below illustrates the differences
(table partitions are the shaded boxes, indexes are the curly braces).
Where do the pitfalls arise?
Two seemingly simple operations can blow away an index and leave your
partitioned table in a state of disrepair. There wont be any data loss, but if
an application is dependent on having an index, the loss of that index can
bring a session to its knees, that is, what was a .01 seconds lookup can now
take 40 minutes. On an OLTP-type database, youre dead in the water.
The global non-partitioned (GNP)
index is the index of interest for the pitfall examples. Think of the GNP index
as a map of data, and you should, because that is exactly what it is. What
happens when a hole is punched in the middle of the map? In this case, index
data is lost, so without knowing the complete structure of the index, Oracle
declares the index to be unusable, and there goes your performance out the
window. From personal experience, rebuilding a 250 million plus records index
took almost six hours. A partition, and a very small one at that, needed to be
repopulated with data, and there were two choices to clean out the questionable
data: simply delete and commit, or truncate the partition. The global index
contained the index for the primary key, and the operation to clear the data
blew the index, so to speak.
Truncate a partition
How do you truncate a table
partition? Does truncate table X partition Y work? Actually, the syntax
involves alter table truncate partition. The key part of the alter table
statement is to include two or three words (depending on the type of table and
index), and those words are update <global> indexes.
Buried in the
Administrators Guide, applying what is stated in the sentence below means the
difference between something routine and something potentially expensive (loss
of up time, failure to meet an SLA, slow down on a production line, and so on).
Unless you specify
INDEXES, any global indexes are marked
and must be rebuilt.
Personally, I think this
statement should be highlighted much better than it currently is, or placed in
an indented note or warning/caution statement.
How can you wind up in a
situation where a global non-partitioned index is related to a partitioned
table? Actually, it is quite easy to create this situation. Partition a table
by one key or value, and then create an index on another attribute. An example
would be a partition key based on a list (states, departments, subassemblies,
etc.) and then base the primary key on, say, a part number. Recall that when
creating a primary key (and what other constraint?) you get an index for free.
You now have a global non-partitioned index on a partitioned table.
Splitting a partition
The same blown index problem
can occur when splitting a partition. At least here, the documentation does a
better job of pointing out what happens and how you can prevent it from
occurring in the first place.
This example splits a
partition (what type of partitioning is being used here?) and keeps global
alter table coupons
split partition p4_coupons values ('415')
into (partition p415, partition p4_coupons)
update global indexes;
Adding update global
indexes to either operation (truncation and splitting) makes all the
difference in the world.
Breaking an index
How do you know what type of
index is being used? If youre using TOAD, a partitioned index sticks out
because of an icon. If youre in SQL*Plus, then you have to be a bit more
clever to determine the partition type. Lets take a look at the SH sample
schema via TOAD. Shown below are the indexes for the SALES table.
The data is eligible to be
contained in 28 partitions, and the partition key is TIME_ID. Not all
partitions have records in them, and were going to create that situation in one
other partition by truncating it. To help illustrate the global non-partitioned
index example, well create a pseudo primary key based on a sequence, and then
take another look at the indexes.
Refresh the list of indexes
in TOAD and now the free index named PK_SALES_ID appears.
Lets pick SALES_Q2_1998 as
the guinea pig. You can copy the data off into a backup table first if you want
to have the SH schema intact afterwards. The picture below shows both steps
copying the data and truncating the partition.
Now that the partition named
SALES_Q2_1998 has been truncated, what is the state of our indexes? Refresh the
list of indexes in TOAD to see the result.
Red Xs in TOAD are symbols
you generally do not want to see, as they represent something that is broken or
invalid. To fix the index, we have to rebuild it. We can do that through TOAD
or via the command line (and TOAD will show you the SQL syntax if so desired).
Again, refresh the list of
indexes in TOAD and the PK_SALES_ID index is now in a good state. Lets
truncate another partition, but this time add the update indexes clause. But
first, just for grins, what happens if we truncate an empty partition and do
not use the update indexes clause? Hopefully nothing happens as no index
information (because of its associated data) was lost, and you can test the
veracity of that statement for yourself.
SQL> create table sales_q3_1998 as
2 select * from sales partition (sales_q3_1998);
SQL> alter table sales truncate partition sales_q3_1998
2 update global indexes;
SQL> select object_name from user_objects
2 where status = 'INVALID'
3 and object_type = 'INDEX';
no rows selected
The results indicate that
the partition was truncated and that no indexes were marked as unusable (or
have a status of INVALID). The same types of examples using a SPLIT
operation are easily demonstrated using the same procedures as what we did for
the truncate partition operation.
The time to rebuild the
PK_SALES_ID index is around a minute, and that is for about a million rows.
Perhaps a million rows per minute is a rough estimate of how fast an index can
be rebuilt (of course your mileage may vary). Extrapolating that rate to the
250 million count table, the time estimate/rate of more than four hours isnt
too far off, and what it does do is point out the fact that it may take hours
and hours to rebuild an index. If you can afford the down time for the rebuild
operation, youre fortunate, but what happens when it is a live production
system that is crippled for several hours? Hopefully, the point of this article
using the update indexes clause will help you avoid being caught in that
type of predicament.
See All Articles by Columnist Steve Callan