Database Reference
In-Depth Information
this audit trail). In that fashion, we can back up that tablespace once a month, after we move the partition in there;
ensure we have a good, clean, current readable copy of the tablespace; and then not back it up anymore that month.
We might have the following tablespaces for our audit trail:
•
A current online, read-write tablespace that gets backed up like every other normal tablespace
in our system. The audit trail information in this tablespace is not compressed, and it is
constantly inserted into.
•
A read-only tablespace containing “this year to date” audit trail partitions in a compressed
format. At the beginning of each month, we make this tablespace read-write, move and
compress last month's audit information into this tablespace, make it read-only again, and
back it up.
•
A series of tablespaces for last year, the year before, and so on. These are all read-only and
might even be on slow, cheap media. In the event of a media failure, we just need to restore
from backup. We would occasionally pick a year at random from our backup sets to ensure
they are still restorable (tapes go bad sometimes).
In this fashion, we have made purging easy (i.e., drop a partition). We have made archiving easy, too—we
could just transport a tablespace off and restore it later. We have reduced our space utilization by implementing
compression. We have reduced our backup volumes, as in many systems, the single largest set of data is
audit trail
data
. If you can remove some or all of that from your day-to-day backups, the difference will be measurable.
In short, audit trail requirements and partitioning are two things that go hand in hand, regardless of the
underlying system type, be it data warehouse or OLTP.
■
Consider using Oracle's Flashback Data archive feature for auditing requirements. When enabled for a table, the
Flashback Data archive will automatically create an underlying partitioned table to record transactional information.
Tip
Summary
Partitioning is extremely useful in scaling up large database objects in the database. This scaling is visible from the
perspective of performance scaling, availability scaling, and administrative scaling. All three are extremely important
to different people. The DBA is concerned with administrative scaling. The owners of the system are concerned
with availability, because downtime is lost money, and anything that reduces downtime—or reduces the impact of
downtime—boosts the payback for a system. The end users of the system are concerned with performance scaling. No
one likes to use a slow system, after all.
We also looked at the fact that in an OLTP system, partitions may not increase performance, especially if applied
improperly. Partitions can increase the performance of certain classes of queries, but those queries are generally not
applied in an OLTP system. This point is important to understand, as many people associate partitioning with “free
performance increase.” This does not mean that partitions should
not
be used in OLTP systems—they do provide
many other salient benefits in this environment—just don't expect a massive increase in throughput. Expect reduced
downtime. Expect the same good performance (partitioning
will not
slow you down when applied appropriately).
Expect easier manageability, which may lead to increased performance due to the fact that some maintenance
operations are performed by the DBAs more frequently because they can be.
