Queries:

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;

Answer:

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 online operation. 
The rebuild causes a completely new index segment to be constructed from scratch.

Coalesce simply shuffles index entries around within the existing index segment. 
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. 

Note:

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?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips