Database Reference
In-Depth Information
processing on your system was not necessarily interrupted. Sure, you as the DBA might be looking at your screen
for a longer period of time, but the really important work that takes place on your system was still taking place. You
need to see if this tradeoff makes sense for you. If you have an eight-hour maintenance window overnight in which to
load new data, then by all means, use the rebuild approach if that makes sense. However, if you have a mandate to be
available continuously, then the ability to maintain the global indexes will be crucial.
One more thing to consider is the redo generated by each approach. You will find that the
UPDATE GLOBAL
INDEXES
generates considerably more redo (due to the index maintenance) and you should expect that to only go up
as you add more and more global indexes to the table. The redo generated by the
UPDATE GLOBAL INDEXES
processing
is unavoidable and cannot be turned off via
NOLOGGING
, since the maintenance of the global indexes is not a complete
rebuild of their structure but more of an incremental maintenance. Additionally, since you are maintaining the live
index structure, you must generate undo for that—in the event the partition operation fails, you must be prepared
to put the index back the way it was. And remember, undo is protected by redo itself, so some of the redo you see
generated is from the index updates and some is from the rollback. Add another global index or two and you would
reasonably expect these numbers to increase.
So,
UPDATE GLOBAL INDEXES
is an option that allows you to trade off availability for resource consumption. If
you need to provide continuous availability, it's the option for you. But you have to understand the ramifications and
size other components of your system appropriately. Specifically, many data warehouses have been crafted over time
to use bulk direct path operations, bypassing undo generation and, when permitted, redo generation as well. Using
UPDATE GLOBAL INDEXES
cannot bypass either of those two elements. You need to examine the rules you use to size
your redo and undo needs before using this feature, so you can assure yourself it can work on your system.
Asynchronous Global Index Maintenance
As shown in the prior section, starting with Oracle9
i
and higher you can maintain global indexes while dropping or
truncating partitions via the
UPDATE GLOBAL INDEXES
clause. However, as shown previously, such operations come at
a cost in terms of time and resource consumption.
Starting with Oracle 12
c
, when dropping or truncating table partitions, Oracle postpones the removal of the
global index entries associated with the dropped or truncated partitions. This is known as
asynchronous global index
maintenance
. Oracle postpones the maintenance of the global index to a future time while keeping the global index
usable. The idea being that this improves the performance of dropping/truncating partitions while keeping any global
indexes in a usable state. The actual cleanup of the index entries is done later (asynchronously) either by the DBA or
by an automatically scheduled Oracle job. It's not that less work is being done, rather it's the cleanup of index entries
is decoupled from the
DROP/TRUNCATE
statement.
A small example will demonstrate asynchronous global index maintenance. To set this up, we create a table in an
11
g
database, populate it with test data, and create a global index:
EODA@ORA11GR2> CREATE TABLE partitioned
2 ( timestamp date,
3 id int
4 )
5 PARTITION BY RANGE (timestamp)
6 (PARTITION fy_2014 VALUES LESS THAN
7 (to_date('01-jan-2015','dd-mon-yyyy')),
8 PARTITION fy_2015 VALUES LESS THAN
9 ( to_date('01-jan-2016','dd-mon-yyyy')));
EODA@ORA11GR2> insert into partitioned partition(fy_2014)
2 select to_date('31-dec-2014','dd-mon-yyyy')-mod(rownum,364), rownum
3 from dual connect by level < 100000;
99999 rows created.
