Jun 25 2008, 08:08 AM
What is the difference between COALESCE and REBUILD
SQL> ALTER index IND_ACC_TR COALESCE;
SQL> ALTER index IND_ACC_TR rebuild;
Jun 25 2008, 10:10 AM
I would suggest that you read this: http://www.dbspecialists.com/specialists/s...ist2006-04.html
Jun 25 2008, 11:04 AM
With COALESCE the index structure isn`t altered. The index keys in leaf blocks are spread in the leaf blocks that aren`t full and the empty leaf blocks are freed.
With REBUILD the blevel can be reduced as not only the empty leaf blocks are freed but also the branch blocks that only have one pointer.
REBUILD has more cost than COALESCE.