Database Reference
In-Depth Information
So, our choices after performing this partition operation with global indexes are
•
Skip the index, either transparently as Oracle is doing in this example or by setting the session
parameter
SKIP_UNUSABLE_INDEXES=TRUE
in 9
i
(Oracle 10
g
defaults this setting to
TRUE
). But
then we lose the performance the index was giving us.
•
Have queries receive an error, as they would without
SKIP_UNUSABLE_INDEXES
set to
FALSE
in
9
i
and before or queries that explicitly request to use a hint will in 10
g
. We need to rebuild this
index to make the data truly usable again.
The sliding window process, which so far has resulted in virtually no downtime, will now take a very long time to
complete while we rebuild the global index. Runtime query performance of queries that relied on these indexes will
be negatively affected during this time—either they will not run at all or they will run without the benefit of the index.
All of the data must be scanned and the entire index reconstructed from the table data. If the table is many hundreds
of gigabytes in size, this will take considerable resources.
“Live” Global Index Maintenance
Starting in Oracle9
i
, another option was added to partition maintenance: the ability to
maintain the global indexes
during the partition operation using the
UPDATE GLOBAL INDEXES
clause. This means that as you drop a partition, split
a partition, perform whatever operation necessary on a partition, Oracle will perform the necessary modifications to
the global index to keep it up to date. Since most partition operations will cause this global index invalidation to occur,
this feature can be a boon to systems that need to provide continual access to the data. You'll find that you sacrifice
the raw speed of the partition operation, but with the associated window of unavailability immediately afterward as
you rebuild indexes, for a slower overall response time from the partition operation but coupled with 100 percent
data availability. In short, if you have a data warehouse that cannot have downtime, but must support these common
data warehouse techniques of sliding data in and out, then this feature is for you—but you must understand the
implications.
Revisiting our previous example, if our partition operations had used the
UPDATE GLOBAL INDEXES
clause when
relevant (in this example, it would not be needed on the
ADD PARTITION
statement since the newly added partition
would not have any rows in it), we would have discovered the indexes to be perfectly valid and usable both
during
and
after
the operation:
EODA@ORA12CR1> alter table partitioned
2 exchange partition fy_2014
3 with table fy_2014
4 including indexes
5 without validation
6 UPDATE GLOBAL INDEXES
7 /
Table altered.
EODA@ORA12CR1> alter table partitioned drop partition fy_2014
2
update global indexes;
Table altered.
EODA@ORA12CR1> alter table partitioned
2 add partition fy_2016
3 values less than ( to_date('01-jan-2017','dd-mon-yyyy') )
4 /
Table altered.
