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:
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:
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:
- If you rebuild all the indexes in your database, sorting the indexes requires up to 75 percent of the total database size in free space.
- If you rebuild an individual index, sorting that index requires as much as the following amount of free space:
(size of one index entry) * (number of records in file) * 3
The Index Rebuild qualifier with PROUTIL rebuilds an index or set of indexes in a series of three phases:
- 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.
- 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.
- 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:
- The temporary sort file allocated to do the sort reaches the operating system limit for file size (2GB on most systems).
To overcome this limitation, simply create a file that contains specifications for the directories and the amount of space per directory that you want the SRT file to have access to during the Index Rebuild. The file that contains the specifications must be a text file, must have the same name as the database with an extension of .srt (dbname
.srt
), and must reside in the same directory as the .db file. In addition, the contents of the file must follow these conventions:- List the directory and the amount of space that you want to allocate to the index rebuild sort on separate lines.
The size that you specify in the dbname
.srt
directory specification is the maximum (in 1024 byte units) that the file can grow. Specifying 0 for any directory indicates that you want to allow unlimited growth.When you provide a file size that is larger than the maximum allowed by your operating system, Index Rebuild ignores the value you specified in the file and uses the maximum size allowed by the operating system. When you specify a nonzero file size that is less then the temporary block size (-
TB
), Index Rebuild ignores the value you specified in the file and uses the temporary block size (-TB
) value.- Separate the directories from the size by at least one blank.
- Terminate the line with a slash (/) followed by end of line.
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 theuser3/junk
directory, and unlimited space in the /user4/last directory, then thesports.srt
looks like this on UNIX:
and looks like this for Windows:
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 thedbname.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:
or:
The previous message occurs even if the .srt file was not found.
When the sort completes, the following message is displayed for each file:
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:
- Answer yes when prompted whether you have enough disk space for sorting.
- Increase the Speed Sort (-
TB
) startup parameter to 24K. (If you are very short of memory, use 16K or 8K.) This improves sort performance; however, it also uses more memory and disk space. See the Progress Startup Command and Parameter Reference for more information about the -TB
startup parameter.- Increase the Merge Number (-
TM
) startup parameter to 32 (unless memory is scarce). The -TM
parameter is described in the Progress Startup Command and Parameter Reference .- Change the Temporary Directory (-
T
) startup parameter to store the temporary files on another disk. See the Progress Startup Command and Parameter Reference for more information on the -T
parameter.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:
You must change the record data to eliminate duplicate keys. Use another index on the table (if one exists):
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |