For databases enabled for multi-tenancy, the MTIDXBUILD utility allows database administrators to rebuild an index or indexes for a specific tenant or group while the database remains online, in addition to offline. MTIDXBUILD only rebuilds indexes for multi-tenant tables.
Prior to rebuilding a multi-tenant index, you must enable your database for the rebuild. The general process is as follows:
1. Enable database for multi-tenant index rebuild with PROUTIL ENABLEMTIDXBLD (online or offline). See PROUTILENABLEMTIDXBLD qualifier.
3. Disable the database for multi-tenant index rebuild with PROUTIL DISABLEMTIDXBLD (offline only). See PROUTIL DISABLEMTIDXBLD qualifier for details.
Multi-tenant index rebuild acquires an exclusive table partition lock when performing the rebuild, blocking other processes from creating or deleting index objects. Once the rebuild starts, clients cannot use the index in a query, locked or no-lock.
To prevent users from accessing a rebuilt index with a stale cursor, ABL clients, whose login tenant or group matches the table MTIDXBUILD is working on, or whose login id is a super tenant, with a timestamp older than a current timestamp from MTIDXBUILD, must log out.
Multi-tenant index rebuild has significant differences from the full index rebuild (PROUTIL IDXBUILD). Those differences include:
Multi-tenant index rebuild can operate while the database is online.
You can still access the database if the index rebuild fails. If the index rebuild fails, the index is left offline and must be brought online with an index utility (index rebuild or activation) because not all of the index rebuild activity is logged. Users can access the database after a failed multi-tenant index rebuild, provided the new process goes through crash recovery.
You cannot use multi-tenant index rebuild to clear a tainted index flag. Only the full index rebuild can do that.
Multi-tenant index rebuild is not multi-threaded.
To improve performance, multi-tenant index rebuild deletes and rebuilds the index.
There is no menu-driven option to MTIDXBUILD, you must completely specify the rebuild on the command line.
You can track the progress of MTIDXBUILD using the User status (_UserStatus) Virtual System Table.
Multi-tenant index rebuild can operate while the database is online.