Oracle Coalesce Vs Index
What is the difference between rebuild and coalesce and UPDATE BLOCK
REFERENCES in INDEX.
What are the process happening?
alter index supplier_pk rebuild;
alter index supplier_pk coalesce;
ALTER INDEX supplier_pk UPDATE BLOCK REFERENCES;
REBUILD statement is used to reorganize or compact an existing
index or to change its storage characteristics. The REBUILD statement uses
the existing index as the source for the new index. As a result, the index
will consume twice as much space during the rebuild process.
COALESCE statement instructs Oracle to merge the contents of
index blocks where possible to free blocks for reuse. The index structure
itself is not affected.
Rebuild takes exclusive table locks, whereas a coalesce is a purely
The rebuild causes a completely new index segment to be constructed
Coalesce simply shuffles index entries around within the existing index
The practical significance of that difference is that a rebuild requires
potentially two lots of space for it to work.
Coalesce requires little or no extra space.
Coalesce leaves the overall size of your index unchanged.
It will shuffle index entries around between nodes so that everything
gets compacted, but the index structure will not change in overall size:
therefore, you will end up with empty blocks included in the finished structure.
They can be reused by new insertions and updates, of course, but still
if your aim is to actually reduce the size of your index, the coalesce
won't achieve it.
The "update block references" clause applies to the secondary indexes
on an index organized table (IOT). SUch indexes have a "block guess" as
part of the key structure to allow Oracle to jump directly to the block
in the IOT were the required row was when the secondary index was created.
But since an IOT is an index structure, leaf block splits can occur
in the IOT as new data arrives, and rows will gradually move to different
blocks. The "update block references" clause tells Oracle to bring the
block guesses in the secondary index up to date.
Have a Oracle Question
Do you have
an Oracle Question?
Certification, Database Administration, SQL, Application, Programming Reference
Application Hints and Tips
Database, SQL, Application, Programming Tips
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
All product names are trademarks of their respective
The site www.erpgreat.com is not affiliated with or endorsed
by any company listed at this site.
Every effort is made to ensure the content integrity.
Information used on this site is at your own risk.
The content on this site may not be reproduced
or redistributed without the express written permission of
www.erpgreat.com or the content authors.