Use the IDXBUILD (Index Rebuild) qualifier of the PROUTIL utility to:
Compress index blocks to minimize space usage
Activate all deactivated indexes in the database
Repair corrupted indexes in the database (index corruption is normally signaled by error messages)
When you run the Index Rebuild, the database must not be in use.
For multi-tenant databases, you can rebuild an index for a tenant or group while the database remains online. See Multi-tenantindex rebuild for details.
For table-partitioned databases, you can rebuild an index for a partition while the database remains online. See Table-partitioned index rebuild for details.
You perform a backup of your database immediately prior to running an Index Rebuild. Should the Index Rebuild crash due to data corruption, the only method of recovery is a restore from backup.
To run PROUTIL IDXBUILD, enter the following command:
proutil db-name -C idxbuild
[ all | activeindexes | inactiveindexes | table [owner-name.]table-name [ partition partition-name | composite initial]| area area-name | schema schema-owner] [ tenant tenant-name| group group-name | shared ] [ -thread n ][ -threadnum n ] [ -datascanthreads n ][ -mergethreads n ] [ -T dir-name | -SS sort-file-directory-specification ] [ -TB blocksize ][ -TM n ][ -TMB n ] [ -TF n ][ -freeTF n ][ -B n ] [ -SG n ] [ -pfactor n ][ -rusage]
When you enter this command without the all, table, area, or schema qualifiers, the following menu appears:
Index Rebuild Utility
=====================
Select one of the following:
All (a/A) - Rebuild all the indexes
Some (s/S) - Rebuild only some of the indexes
By Area (r/R) - Rebuild indexes in selected areas
By Schema (c/C) - Rebuild indexes by schema owners
By Table (t/T) - Rebuild indexes in selected tables
By Partition (p/P) - Choose indexes in selected table partitions
By Activation (v/V) - Rebuild selected active or inactive indexes
-------------------
Multi-Tenancy (m/M) - Choose tenants or groups
-------------------
Quit (q/Q) - Quit, do not rebuild
Enter your selection:
Make your selection, considering the following:
Use the All option to rebuild all indexes.
Use the Some option to rebuild only specific indexes.
Use the By Area option to rebuild indexes specific to one or more areas.
Use the By Schema option to rebuild indexes owned by one or more schema owners.
Use the By Table option to rebuild indexes specific to one or more tables.
Use the By Partition option to rebuild indexes specific to one or more table partitions.
Use the By Activation option to select active or inactive indexes.
Use the Multi-Tenancy option to refine your selection to specific tenants or groups.
After you enter a selection and you qualify 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 or not you have enough free space to sort the indexes, 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 can require as much as the following amount of free space:
(size of one index entry) * (number of records in file) * 3
PROUTIL IDXBUILD rebuilds an index or set of indexes in a series of three phases:
1. The utility scans the database by area, 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 by area 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. 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, building a compacted index. This phase only occurs if you chose to sort the indexes.
Index Rebuild accomplishes most of its work without displaying messages, unless it encounters an error condition.
For Enterprise database licenses, index rebuild is multi-threaded by default. You can specify the maximum number of threads created using the -threadnumnparameter. If not specified, the maximum number of threads created will equal the system's number of CPUs. The actual number of threads created will not exceed the number of index groups in an area if this value is smaller than the maximum. During a multi-threaded index rebuild, separate threads are assigned the external merging of each index group during Phase 2. Once the main process has created all the threads for Phase 2, it immediately begins building the index tree for Phase 3, enabling Phase 3 to be executed in parallel with Phase 2. If an area has only one index to rebuild, the work will be executed without the use of threads.
If you do not want your index rebuild to be multi-threaded, specify -threads 0. This directs the index rebuild to execute in an unthreaded mode.
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.
Compress index blocks to minimize space usage
