Database Reference
In-Depth Information
REDO_GEN DB_BLOCK_GETS
---------- -------------
2459820 1495
If we re-run the same code in a 12 c database, we get the following when dropping the partition with UPDATE
GLOBAL INDEXES specified:
REDO_GEN DB_BLOCK_GETS
---------- -------------
9872 43
Compared to the 11 g example, a fraction of the redo is generated and blocks accessed when running this in an
Oracle 12 c database. The reason behind this is that Oracle doesn't immediately perform the index maintenance of
removing the index entries from the dropped partition. Rather these entries are marked as orphaned and will later be
cleaned up by Oracle. The existence of orphaned entries can be verified via the following:
EODA@ORA12CR1> select index_name, orphaned_entries, status from user_indexes
2 where table_name='PARTITIONED';
INDEX_NAME ORP STATUS
------------------------- --- --------
PARTITIONED_IDX_GLOBAL YES VALID
How do the orphaned entries get cleaned up? Oracle 12 c has an automatically scheduled PMO_DEFERRED_GIDX_
MAINT_JOB , which runs in a nightly maintenance window. If you don't want to wait for that job, you can manually
clean up the entries yourself:
EODA@ORA12CR1> exec dbms_part.cleanup_gidx;
PL/SQL procedure successfully completed.
In this way you can perform operations such as dropping and truncating partitions and still leave your global
indexes in a usable state without the immediate overhead of cleaning up the index entries as part of the drop/truncate
operation.
Tip
See MOS note 1482264.1 for further details on asynchronous global index maintenance.
OLTP and Global Indexes
An OLTP system is characterized by the frequent occurrence of many small read and write transactions. In general,
fast access to the row (or rows) you need is paramount. Data integrity is vital. Availability is also very important.
 
 
Search WWH ::




Custom Search