April 2006

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

April 2006
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: Rebuilding vs. Coalescing Indexes

You’re invited to submit your Oracle-related questions to us at askdbspecialists04@dbspecialists.com. Please include your name and telephone number in your email.

This month’s question comes to us from Eric in Boston, MA: What are the differences between rebuilding an index and coalescing an index? Under what circumstances would you want to perform one versus the other?

Hamid Minoui of the Database Specialists team responds: You can rebuild or coalesce an index when it becomes fragmented. Both operations reorganize the index and improve space utilization and query performance. However, there are major differences in time and space requirements. Understanding the operational differences between the two methods and the cost and benefits associated with each will help you choose the appropriate action for the desired outcome.

If the goal is to relocate an index from one tablespace to another or otherwise alter its physical storage properties, then the clear choice is to rebuild the index. Rebuilding an index completely reconstructs the index segment

The cost of rebuilding an index is higher than coalescing it, because the entire index needs to be built as a temporary index segment before the original index structure is de-allocated and space is reclaimed. Only at this point the newly rebuilt index structure takes the name of the old index and the relevant dictionary tables are updated. For this reason, roughly twice the storage is needed during an index rebuild operation.

If an index is rebuilt in the same tablespace, which is the default behavior of the ALTER INDEX…REBUILD statement, free extents in the tablespace will be allocated to the new index segment before the extents used by the existing index segment are freed. Side effects associated with this behavior include the raising of the high water mark of allocated blocks within a data file, possible auto-extension of a data file, and the possible fragmentation of free space in a dictionary-managed tablespace.

Coalescing an index, meanwhile, performs an in-place re-organization without the use of additional storage space. When coalescing an index, Oracle reduces fragmentation in the leaf blocks of the index by merging the contents of adjacent leaf blocks where possible. (If three adjacent leaf blocks are each only 60% full, Oracle may be able to free up one of the leaf blocks for reuse elsewhere in the index structure by filling the other two leaf blocks more fully.) The height of an index is not changed during a coalesce operation.

Leaf blocks in an index can accumulate unused space when widespread deletions remove many of the entries in the leaf blocks. As long as at least one entry remains in a leaf block, the block will stay that way until an update or insert statement adds more entries to the index near the lone entry. Index coalescing transforms partially full leaf blocks into fewer, fuller leaf blocks. This leaves some leaf blocks completely empty so that they can be repurposed elsewhere in the index. These empty blocks remain allocated to the index segment.

Coalescing an index is faster than rebuilding, and does not impose the locking issues that rebuilding does. Rebuilding an index causes exclusive table locks—even an online index rebuild requires a very brief exclusive lock.

Rebuilding an index is quite different from coalescing it. Rebuilding creates a whole new index segment, allowing you to change physical storage properties and reclaim space no longer needed by the index. Coalescing simply merges nearby leaf blocks, making blocks within the index segment available for future inserts or updates. Coalescing is faster and does not impose exclusive table locks, but it does not allow you to change physical storage properties or immediately reclaim space no longer used by the index.

Rebuilding and coalescing each has its appropriate uses. Understanding the difference between these two operations will enable you to choose the right action depending on the circumstances and what you are trying to accomplish.

Oracle Streams Feature Overview

Oracle Streams, first introduced in Oracle 9i release 2, provides a flexible and customizable log-based replication facility. Oracle Streams represents a major change in architecture from multi-master replication, introduced back in Oracle 7. While multi-master replication used internal triggers to capture data changes and write them into queues within the database, Oracle Streams instead mines the redo log stream in order to capture data changes, DDL, and events.

Streams seems to be the future of replication in Oracle, since its log-based approach is more efficient and offers greater flexibility. You’ll find a feature overview of Oracle Streams on Oracle’s website at: http://www.oracle.com/technology/products/dataint/htdocs/streams_fo.html.

Tom Kyte of Oracle’s AskTom has also been busy answering a lot of questions about Oracle Streams in his regular column. When you have time to browse and learn more, here’s a link to dozens of questions about Streams, along with Tom’s answers: http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:14672061404704.

NoCOUG Conference on May 18

Mark your calendar for the upcoming Northern California Oracle Users Group event taking place in Sunnyvale, California on Thursday, May 18. Jonathan Lewis will give a keynote presentation on testing, along with a technical session on optimizer hints and how to use them. Jonathan, a popular NoCOUG speaker, has recently published a new book entitled, Cost-Based Oracle Fundamentals. Additional great technical sessions will be taking place throughout the day, including:

  • What’s Up with dbms_stats? by Terry Sutton, Database Specialists
  • Performance Diagnostics Using STATSPACK Data by Tim Gorman, SageLogix
  • Religion, Revelation, Revolution! Best Practices and Projects for Managing Databases by Steve Lemme, Computer Associates
  • Database Security – the Past, the Present, the Future by Mark Kraynak, Imperva, Inc.

For more information and registration information, check out http://www.nocoug.org/next.html.

Backup and Recovery Planning

Developing, deploying, and periodically testing a proper backup and recovery plan is one of the most important responsibilities of a DBA. However, too often, this critical aspect of database administration is not given enough thought—causing companies hours of downtime, lost data, and potentially lost customers and revenue.

Check out http://www.oracle.com/technology/deploy/availability/htdocs/BR_Overview.htm for a comprehensive overview on the topic of backup and recovery. The paper covers, at a fairly technical level, the elements of a backup and recovery strategy, how Oracle’s backup and recovery mechanisms work, the different files that need to be backed up, types of failure that can occur, and available backup methods. Links in the paper refer to other papers on related topics such as Recovery Manager, Enterprise Manager, and Data Pump.

Many factors come into play when selecting the proper backup and recovery plan for your company. Be sure you’ve done your research well and developed a plan to meet the specific needs of your environment and business requirements. And—most importantly—make sure you test your recovery plan regularly.


Leave a Reply

Your email address will not be published. Required fields are marked *