Problem solve Get help with specific problems with your technologies, process and projects.

Estimate or monitor the time to build an index

This code will help to estimate the time needed to build a large index or monitor the progress of the index.

With the help of the following code, you can monitor the progress of indexing or estimate the time to do a "create...

index" of a large table. There are three phases of index creation:

  1. Table scan: Index Fast Full Scan, Table Scan, Rowid Range Scan
  2. Sorting the data: Sort/Merge
  3. Writing the output: Sort Output
The output will be based on the number of blocks written and the number of blocks needed to process for each phase.

This script will not show any entry if index creation is less than 10 sec. It will not filter the old entries, so in order to cope with this problem, create each index in different sessions (of course followed by the completion of the previous one). It has been tested on Oracle 9i.

define sid = <   >
select opname, sum(sofar), sum(totalworks) 
from v$session_longops 
where sid = &sid group by opname;

Reader Feedback

Brian Peasland writes: This is a really good tip, but the query in the tip is missing something. The query needs both SID and SERIAL# in the WHERE clause to work correctly. The author only has SID, which can give misleading results in the case of two sessions at two different times that had the same SID. The SID,SERIAL# combination guarantees the results will be for one session only.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

Dig Deeper on Oracle database backup and recovery