Database Reference
In-Depth Information
Chapter 13
Partitioning
Partitioning
, first introduced in Oracle 8.0, is the process of physically breaking a table or index into many smaller,
more manageable pieces. As far as the application accessing the database is concerned, there is logically only one
table or one index, but physically that table or index may comprise many dozens of physical partitions. Each partition
is an independent object that may be manipulated either by itself or as part of the larger object.
■
Partitioning is an extra cost option to the Enterprise Edition of the Oracle database. It is not available in the
Standard Edition.
Note
In this chapter, we will investigate why you might consider using partitioning. The reasons range from increased
availability of data to reduced administrative (DBA) burdens and, in certain situations, increased performance. Once
you have a good understanding of the reasons for using partitioning, we'll look at how you may partition tables and
their corresponding indexes. The goal of this discussion is not to teach you the details of administering partitions, but
rather to present a practical guide to implementing your applications with partitions.
We will also discuss the important fact that partitioning of tables and indexes is not a guaranteed
“fast = true” setting for the database. It has been my experience that many developers and DBAs believe that increased
performance is an automatic side effect of partitioning an object. Partitioning is just a tool, and one of three things
will happen when you partition an index or table: the application using these partitioned tables might run slower,
might run faster, or might not be affected one way or the other. I put forth that if you just apply partitioning without
understanding how it works and how your application can make use of it, then the odds are you will negatively impact
performance by just turning it on.
Lastly, we'll investigate a very common use of partitions in today's world: supporting a large online audit trail in
OLTP and other operational systems. We'll discuss how to incorporate partitioning and segment space compression
to efficiently store online a large audit trail and provide the ability to archive old records out of this audit trail with
minimal work.
