Database Reference
In-Depth Information
Now we can exchange the prior two tables in one transaction into the reference partitioned tables. Notice the
CASCADE
option is specified:
EODA@ORA12CR1> alter table orders
2 exchange partition part_2016
3 with table part_2016
4 without validation
5 CASCADE
6 update global indexes;
Table altered.
That's it. With one DDL statement, we simultaneously exchanged two tables related by a foreign key constraint
into a reference partitioned table. Anybody accessing the database will see the parent and child table partitions added
seamlessly as one unit of work.
Auditing and Segment Space Compression
Not too many years ago, U.S. government constraints such as those imposed by the HIPAA act
(
http://www.hhs.gov/ocr/hipaa
)
were not in place. Companies such as Enron were still in business, and another U.S.
government requirement for Sarbanes-Oxley compliance did not exist. Back then, auditing was considered something
that “we might do someday, maybe.” Today, however, auditing is at the forefront, and many DBAs are challenged to
retain online up to seven years of audit trail information for their financial, business, and health care databases.
Audit trail information is the one piece of data in your database that you might well insert but never retrieve
during the normal course of operation. It is there predominantly as a forensic, after-the-fact trail of evidence. We need
to have it, but from many perspectives, it is just something that sits on our disks and consumes space—lots and lots of
space. And then every month or year or some other time interval, we have to purge or archive it. Auditing is something
that if not properly designed from the beginning can kill you at the end. Seven years from now when you are faced
with your first purge or archive of the old data is not when you want to be thinking about how to accomplish it. Unless
you designed for it, getting that old information out is going to be painful.
Enter two technologies that make auditing not only bearable, but also pretty easy to manage and consume less
space. These technologies are partitioning and segment space compression, as we discussed in Chapter 10. That
second one might not be as obvious since
basic
segment space compression only works with large bulk operations
like a direct path load (OLTP compression is a feature of the Advanced Compression Option—not available with all
database editions), and audit trails are typically inserted into a row at a time, as events happen. The trick is to combine
sliding window partitions with segment space compression.
Suppose we decide to partition the audit trail by month. During the first month of business, we just insert
into the partitioned table; these inserts go in using the conventional path, not a direct path, and hence are not
compressed. Now, before the month ends, we'll add a new partition to the table to accommodate next month's
auditing activity. Shortly after the beginning of next month, we will perform a large bulk operation on last month's
audit trail—specifically, we'll use the
ALTER TABLE
command to move last month's partition, which will have the effect
of compressing the data as well. If we, in fact, take this a step further, we could move this partition from a read-write
tablespace, which it must have been in, into a tablespace that is normally read-only (and contains other partitions for
