Progress
Database Administration
Guide and Reference


Rebuilding Indexes

Use the IDXBUILD (Index Rebuild) qualifier of the PROUTIL utility to:

NOTE: When you run the Index Rebuild qualifier, the database must not be in use.

To run the IDXBUILD qualifier with PROUTIL, enter the following command:

proutil db-name -C idxbuild [ all ]  [ -TB ]  [ -TM ] 

db-name

Specifies the name of the database whose indexes you want to build.

To improve performance, use the Merge Number (-TM) and Speed Sort (-TB) startup parameters. For details, see the "Maximizing Index Rebuild Performance" section.

When you enter this command without the all qualifier, the following menu appears:

Index Rebuild Utility 
======================== 
Select one of the following: 
All - Rebuild all the indexes 
Some - Rebuild only some of the indexes 
Quit - Quit, do not rebuild 
Enter your selection: 

Use the Some option to rebuild only specific indexes. Use the All option to rebuild all indexes. After you enter a selection and you name those indexes you want to rebuild, the utility prompts if you have enough disk space for index sorting. If you enter yes, the utility sorts the indexes you are rebuilding, generating the indexes in order by their keys. This sorting results in a faster index rebuild and better space use in the index blocks.

To estimate whether you have enough free space to sort the indexes or not, use the following formulas:

The Index Rebuild qualifier with PROUTIL rebuilds an index or set of indexes in a series of three phases:

  1. The utility scans the database file, clearing all index blocks that belong to the indexes you are rebuilding and adding those blocks to the free block list.
  2. The utility scans the database file and rebuilds all the index entries for every data record. If you chose to sort the index, the utility writes the index entries to the sort file. Otherwise, the utility writes the index entries to the appropriate index at this point.
  3. This phase only occurs if you chose to sort the indexes. In this phase, the utility sorts the index entries in the sort file into groups and enters those entries into their respective entries in order, one index at a time.

The Index Rebuild qualifier accomplishes most of its work without displaying messages, unless it encounters an error condition.

If the index rebuild is interrupted while rebuilding selected indexes, the list of selected indexes is retained in a file named dbname.xb. This XB file is used when the utility is restarted. You do not have to enter the list of indexes manually if the XB file exists.

Overcoming SRT Size Limitations

When you perform the Index Rebuild utility and choose the Sort option, you might encounter space limitations that can cause the utility to terminate. These are some of the reasons that you might reach the limit:

For example, if you want to rebuild the index for the sports database and you want the speed sort to have access to 300K of space available in the /user2/db1/first directory, 400K in the user3/junk directory, and unlimited space in the /user4/last directory, then the sports.srt looks like this on UNIX:

300  /user2/db1/first/ 
400  /user3/junk/ 
0    /user4/last/ 

and looks like this for Windows:

300  \user2\db1\first\ 
400  \user3\junk\ 
0    \user4\last\ 

The Index Rebuild utility accesses the files in the order in which they are listed in the dbname.srt file. So, if you specify an amount of space that is not available, when the disk is filled, then Index Rebuild terminates and the next directory specification is not used. Thus, if a disk has only 200K of space and the dbname.srt specifies 300K, when the 200K is exhausted the Index Rebuild terminates. For example, if /user2/db1/first above does not get 300K of data, Index Rebuild never processes /user3/junk. In addition, if you specify a directory size of 0, any directories specified after it in the dbname.srt are not processed. For these reasons, you should verify that the space you specify in the dbname.srt file is available before running index rebuild.

The Index Rebuild utility opens the files for each of the directories before it actually starts the sort process. As a result, one of the following messages is displayed for each file:

Temporary sort file at pathname used up to nK of disk space. 

or:

Temporary sort file at:pathname will use the available disk space. 

The previous message occurs even if the .srt file was not found.

When the sort completes, the following message is displayed for each file:

Temporary sort file at pathname used nK of disk space. 

In some cases the message displays OK. This simply means that the sort took place completely in memory.

If Index Rebuild does not find a dbname.srt file, then by default, it uses the directory supplied by either the -T parameter or the current working directory.

Maximizing Index Rebuild Performance

To speed up index rebuild operations, do the following:

The database engine uses the following algorithm to rebuild indexes. For each record, read the index key fields and store in the first available SRT file block. Allocate additional SRT file blocks of the specified block size as required to hold all index keys. Sort the keys in each block then merge the keys to produce a sorted file. A similar technique is used to sort records when there is no index to satisfy a BY clause.

A larger block size can improve index rebuild performance considerably. A larger block size means less SRT block allocation overhead and fewer quicksort operations on individual blocks.

You might have to run the application several times using different block size values to determine the optimal value. If you experience extreme memory shortages when running Progress, try setting the block size to 1 to reduce memory consumption.

During index rebuild, try setting -TB to 31, if memory and disk space are available. If the index rebuild fails, try successively smaller values. Remember, a larger value for -TB improves sort performance but uses more memory. The -TB setting has a significant impact on the size of the SRT temporary file. The SRT file size depends on the number of session compile files, and the number and size of sort operations. (See the description of the Directory Size (-D) startup parameter in Database Startup Parameters."

Memory usage depends on the number of nested FOR EACH statements doing sorts (number of simultaneous sorts occurring). You can estimate memory usage as follows, where M is the estimated memory usage:

M=(sort-block-size)*(number-of-nested-sorts+Merge-Number(-TM)parameter)

Index rebuild always requires eight simultaneous sorts, so during index rebuild:

M=(sort-block-size)*(8+(-TM) parameter)

Therefore, in the default case:

M=(2*(8+5))=26K

Reactivating Unique Indexes

When reactivating a unique index, IDXBUILD displays the following error message each time it encounters a duplicate index key:

Fix RECID recid, table-name already exists with field-name value. 

You must change the record data to eliminate duplicate keys. Use another index on the table (if one exists):

FOR EACH table-name USE-NDEX index-without-duplicate-keys: 
UPDATE table-name. 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095