Database Administration

Reference : PROUTIL Utility : PROUTIL IDXBUILD qualifier

PROUTIL IDXBUILD qualifier

Rebuilds an index, packing or consolidating index records to use disk space as efficiently as possible.

Caution: Back up your database prior to executing IDXBUILD. Under certain conditions, if the index rebuild crashes, the only method of recovery is to restore a backup.

Syntax

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]

Parameters

db-name

Specifies the database you are using.

all

Specifies that you want to rebuild all your indexes. PROUTIL automatically rebuilds all your indexes without asking about disk space requirements. If you also specify tenant or group on the command line with all, only the indexes belonging to the tenant or group are rebuilt.

activeindexes

Specifies that you want all currently active indexes rebuilt.

inactiveindexes

Specifies that you want all currently inactive indexes rebuilt. When an inactive index is rebuilt, it becomes active.

table [owner-name.]table-name

Specifies that you want to rebuild the indexes defined for the named table. When you specify table, PROUTIL automatically rebuilds the indexes defined for the table without asking about disk space requirements.

partition partition-name

Specifies that the IDXBUILD operation is to be performed only on the indexes of the partition-name table partition. partition-name must be a defined partition of [owner-name.]table-name. Specifying partition is only allowed on databases enabled for table partitioning.

composite initial

Specifies that the IDXBUILD operation is performed on the indexes of the composite initial partition for a partitioned table.

area area-name

Specifies that you want to rebuild all the indexes defined in the named area. When you specify area, PROUTIL automatically rebuilds the indexes defined for the area without asking about disk space requirements.

schema schema-owner

Specifies that you want to rebuild all the indexes owned by the named schema-owner. When you specify schema, PROUTIL automatically rebuilds the indexes owned by the schema-owner without asking about disk space requirements.

tenant tenant-name

Specifies that the IDXBUILD operation is to be performed only on the index partitions of tenant-name. If tenant-name does not own any partition of the specified indexes, IDXBUILD exits with an error. Specifying tenant is only allowed on databases enabled for multi-tenancy.

group group-name

Specifies that the IDXBUILD operation is to be performed only on the index partitions of group-name. If group-name does not own any partition of the specified indexes, IDXBUILD exits with an error. Specifying group is only allowed on databases enabled for multi-tenancy.

shared

Specifies that the IDXBUILD operation is to be performed only on the shared indexes.

-thread n

For Enterprise licenses, IDXBUILD is a multi-threaded operation by default. The -thread parameter allows you to turn off multi-threading. By default, n is equal to one (1), indicating that the IDXBUILD is multi-threaded. Setting n to zero (0) turns off multi-threading.

-threadnum n

For Enterprise licenses, when IDXBUILD is run in a multi-threaded mode, the -threadnum parameter allows you to control the maximum number of threads created. By default, the maximum number of threads, n, is equal to the number of CPUs. Setting n to zero (0) turns off multi-threading.

-datascanthreads n

Specifies the number of concurrent threads used to scan table data associated with the indexes being rebuilt. When specified, -datascanthreads invokes a multi-threaded data scan that spawns n threads for the data scan phase. The data being scanned must meet certain criteria for a multi-threaded data scan to occur; see the Notes section for details.

-mergethreads n

Specifies the number of concurrent threads to spawn during the sort/merge phase of index rebuild that merges the key entries in the sort blocks produced by the data scan phase for an individual index sort group. A value of 0 (the default) results in the original, pre-Release 11.1 behavior of one thread per index sort group. A value of 1 also results in the original behavior of one thread per index sort group but the sort/merge is performed by a newly spawned thread rather than by the thread assigned to the individual index sort group.

-T dir-name

Specifies the name of the directory where the temporary files are stored. If you do not use this parameter, PROUTIL places temporary files in the current working directory.

-SS sort-file-directory-specification

Identifies the location of a multi-volume sort file specification. If you use the Sort Directory Specification (-SS) parameter, PROUTIL does not use the Temporary Directory (-T) parameter.

-TB n

Specifies that the index rebuild will be performed using Speed Sort. n indicates the allocated block size, in kilobytes. The maximum value is 64; if not specified, the default is 8.

-TMB n

Specified the merge block size. This is the size of the blocks used to merge sorted blocks together. This value can be specified separately from the sort block size (-TB). The default value for -TMB is the -TB value. The minimum value is the -TB value. If -TMB is set to a value less than -TB, index rebuild will exit.

-TM n

Specifies the merge number. n indicates the number of blocks or streams to be merged during the sort process.

-TF n

Specifies the memory usage factor. This is the percentage of system memory that can be allocated for merging sort buffers when sorting index keys during the index rebuild process. In the past, there was only one sort merge buffer, and when the sort merge buffer became full, the contents were written to the file system. The default value of -TF is 50 (for 50%). Setting -TF 0 results in the pre-10.2B04 behavior.

Caution: If the memory configuration limits for the process do not allow access to the memory requested, an unpredictable failure may occur, such as being unable to spawn helper threads or other memory allocation failures. Such an error will cause index rebuild to terminate. It is therefore imperative to set the ulimit values appropriately for the index rebuild process.

-freeTF n

Specifies whether to free the merge buffer memory explicitly or leave the free to the operating system at the completion of IDXBUILD.

Value of n	Action
0	Do not explicitly free merge buffer memory. The default for Windows 64-bit.
1	Explicitly free merge buffer memory. The default for all Unix platforms and Windows 32-bit.

To improve performance, the merge buffers in the -TF pool are pointer swapped rather than using strcpy(), which results in the list of allocated memory chunks being in sort order rather than allocated order. When the buffers in the -TF pool are freed in an order other than they are allocated, there can be severe performance problems on Windows 64-bit. By setting -freeTF 0 on Windows 64-bit, the allocated memory is freed by the operating system when IDXBUILD completes.

-B n

Specifies the number of blocks in the database buffers.

-SG n

specifies that the index rebuild uses index grouping. n must be a value between 8 and 64, and indicates the number of index groups used by IDXBUILD. The default value is 48. Note that a temporary file is created for each index group.

A large -SG value requires more memory allocation and more file handles. To determine the amount of memory (in kilobytes) needed for each index group, add 1 to the merge number (the value of -TM) and multiply the sum by the speed sort block size (the value of -TB). Memory consumption for each index group equals (-TM + 1) * -TB.

-pfactor n

Specifies that IDXBUILD will use a packing factor. n must be a value between 60 and 100, and indicates the maximum percentage of space used in an index block. The default value is 100.

-rusage

Specifies that IDXBUILD report resource statistics. When specified, index rebuild reports resource statistics including user and system CPU usage and approximate disk reads and write I/O statistics.

If you do not specify all, table, area, or schema, 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:

Note: Depending on the features enabled on your database, some menu items do not appear.

The table below describes PROUTIL IDXBUILD options.

Table 99. PROUTIL IDXBUILD options
Option	Action
All	Prompts you to verify whether you have enough disk space for index sorting.
Some	Prompts you for the indexes you want to rebuild by first entering the table name, and then the index name. IDXBUILD then prompts you to verify whether you have enough disk space for index sorting.
By Area	Prompts you for the area containing the indexes you want to rebuild, then prompts you for the indexes in the area, then prompts you to verify whether you have enough disk space for index sorting.
By Schema	Prompts you for the schema owner of the indexes you want to rebuild, then prompts you for the indexes, then prompts you to verify whether you have enough disk space for index sorting.
By Table	Prompts you for the table containing the indexes you want to rebuild, then prompts you for the indexes, then prompts you to verify whether you have enough disk space for index sorting.
By Partition	Prompts you for the table partitions containing the indexes you want to rebuild, then prompts you for the indexes, then prompts you to verify whether you have enough disk space for index sorting.
By Activation	Prompts you to choose active or inactive indexes, then prompts you for the indexes, then prompts you to verify whether you have enough disk space for index sorting.
Multi- Tenancy	Prompts you to choose tenants or groups, and then prompts you for the specific tenants or groups. This menu option is only available for databases enabled for multi-tenancy when a tenant, group, or shared was not specified on the command line.
Quit	Quits without rebuilding any indexes.

In addition to rebuilding an index, IDXBUILD also:

Compresses index blocks to minimize space usage

Activates deactivated indexes in the database.

Repairs corrupted indexes in the database (index corruption is typically signaled by error messages)

Notes

Use the Temporary Directory (-T) startup parameter to identify or redirect temporary files created by the PROUTIL utility to a specified directory when sorting and handling space issues.

Use the Speed Sort (-TB), Merge Number (-TM), Sort Grouping (-SG) and Blocks in Database Buffers (-B) startup parameters to improve index rebuild performance.

IDXBUILD does not repair corrupted record data.

Use the following formulas to determine whether you have enough free space to sort the indexes:

If you rebuild all the indexes in your database, sorting the indexes requires free space that can be as much as 75 percent of the total database size.

If you rebuild an individual index, sorting that index requires free space that can be as large as three times the size of one index entry times the number of records in the file.

The Index Rebuild utility rebuilds an index or a set of indexes in three phases:

The utility scans the database by area, clearing all index blocks that belong to the indexes and rebuilds and adds those blocks to the free-block list.

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.

If you indicated that you have enough space to sort the indexes, the utility compacts the index by sorting the index entries in the sort file into groups and entering those entries in one index at a time.

See Managing Performance for more information about using the IDXBUILD qualifier.

If db-name is a UTF-8 database, you must specify -cpinternal UTF-8 on the command line. If not specified, an error message is generated and IDXBUILD exits.

During the index rebuild, the _index records for the indexes to be rebuilt are first changed to "inactive". After the rebuild is complete, they are changed to "active".

If there is a audit policy defined to record updates on _index records for the indexes to be rebuilt, auditing does not record updates. The reason is, if the indexes are in a rebuild state, the indexes used to get auditing policies might be invalid and the schema and policy records needed to record the event may not be accessible.

For databases enabled for multi-tenancy, if you specify a tenant or group only those indexes belonging to the tenant or group are rebuilt. If you specify shared, only shared indexes are rebuilt.

For databases enabled for table partitioning, the Choose Indexes screen provides an additional column to identify indexes. A partitioned table can have global (G) and local(L) indexes, as indicated in the G/L column. If the column is blank, the index does not belong to a partitioned table.

For databases enabled for table partitioning, index partition selection is governed as follows:

When By Table is first selected, if a table and local index are selected, all partitions are selected. You can not select individual partitions.

When By Area is first selected, only the index partitions in the selected areas are available for selection. Consequently if a local index has multiple partitions in multiple areas, only the index partitions in the selected areas are selectable.

When By Schema is selected, all partitions of the local indexes are available for selection.

When By Partition is selected, only local indexes belonging to the selected partitions are available for selection.

When the Some option is selected, if a local index is entered, you are warned that the index is partition-aligned, and prompted to enter a partition or continue without specifying a partition. If you do not specify a partition, all partitions of the index are processed.

Use IDXFIX to repair and IDXCOMPACT to compress online.

For databases enabled for multi-tenancy, you can use MTIDXBUILD to rebuild an index for a tenant or group online.

For databases enabled for table partitioning, you can use TPIDXBUILD to rebuild an index for a partition online.

When -datascanthreads is specified, it invokes a multi-threaded data scan that spawns n threads for the data scan phase. The data being scanned, must meet all of the following criteria for a multi-threaded data scan to occur:

The data area being scanned must be a Type II area

No index within the data area being scanned is also being rebuilt

No index associated with the data area being scanned is a word index

Index rebuild must be run with the "sort" option, that is answering "y" when asked if you have enough room for sorting.

If any requirement is not met, the data scan is performed using the original single threaded mechanism. If not specified, the -datascanthreads value defaults to 0, also indicating the original single threaded mechanism.

You can confirm that the data scan phase is multi-threaded by observing the following messages during the data scan phase of index rebuild, as shown:

Processing area 689 : (11463)
Start 4 threads for the area. (14536)
Area 689: Multi-threaded record scan (Type II) complete.
Elapsed time: 6.740

Prior to Release 11.1, the threading during the sort/merge phase of index rebuild uses one thread per index sort group by setting the -thread 1 and -threadnum n parameters. Specifying -mergethreads allows multiple threads to merge data within an individual index sort group. The number of threads spawned during this phase is the number of merge threads multiplied by the lesser of the -threadnum value or the number of index sort groups in use per area.

There is no restriction on area or index type for invoking the -mergethreads behavior. The index rebuild process must be run with the sort option however since when running index rebuild without the sort option there is no sort/merge phase, and -thread 1 and -threadnum n must also be specified.

You can confirm that the sort/merge phase of index rebuild is processing with the new merge threads option by observing the following messages during the sort/merge phase if an index rebuild:

Spawning n threads for merging of group n.

Note: If your index is small, you may not see the spawning message.

For both -datascanthreads and -mergethreads, if the requested number of threads can not be spawned, index rebuild exits with an error, for example:

Unable to run with requested thread configuration of 48 threads due to
system memory restrictions.
Additional memory required: 100663296 Limit: 134217728 In use: 41036736
Threads requested: Data scan: 0, sort: 8, merge: 5, sort groups: 47