There are various options for
online index building, each has it's pro's and con's which need to be evaluated:
1.
ASCII load (ie through the Data Administration) will build indexes if they're
active at the time.
2.
BINARY load (ie through
proutil -C load) will build ALL indexes online with the
'build indexes' option whether they are active or inactive at the time.
However since the performant improvements to IDXBUILD starting 10.2B06 it is faster to binary load data first and IDXBUILD later as the binary load has not been enhanced to take advantage of these new IDXBUILD parameters (-TMB -TF -thread 1 -threadnum 4 -mergethreads 4 -datascanthreads 4 -pfactor 90)
3.
PROUTIL Online and Offline using a combination of:
- IDXFIX (Option 3) online for the inactive index(es). This will not activate the index, it will prebuild the entries. The time to build the index entries is directly related to how complex the index and how many records key field entries there are.
- IDXFIX (Option 3) offline to complete the activation. This will usually activate the index (much) faster with less downtime. Since the idxfix was used online to build the index previously, then the time to activate the index is usually very small - related to how many additional index changes may have occurred after the first idxfix completed.
- Alternatively IDXACTIVATE can be used online as long as primary indexes are active. IDXFIX online will not activate the index, however it will pre-build the index key entries. Running IDXACTIVATE afterwards will then be faster particularly when there are no active unique indexes. (see below)
4.
IDXACTIVATE online
- While the IDXACTIVATE feature was introduced in OpenEdge 10.1A, OpenEdge 10.2B08, 11.2.1, 11.3.0 or later is recommended. Previously there were issues where interrupting an IDXACTIVATE during the key build phase, leaves the index as active (but it's not built) and another that left admin locks behind when interrupted.
- IDXACTIVATE should only be used when certain that the index used to build the inactive index is in a good state. IDXCHECK should be run against this index before-hand to verify it's integrity.
- The primary index will be used by default as the index to mimic when building other indices. If the primary index is not active then then the "useindex" parameter must be specified with an index name other than the primary . The value provided after the useindex parameter should only be the index name, not the table-name.index-name pairing.
Consider specifying a Unique Index to build inactive indexes:IDXACTIVATE turns on
table locking, which means that no-one can execute CUD (OLTP) activity against this table until it completes:
- If the use-index is a unique index, it releases the lock for the remainder of its operations.
- If the use-index is non-unique, it re-acquires a lock on the table/partition.
Regardless of the use-index, IDXACTIVATE acquires a table/partition lock while deleting existing index blocks (if this index were previously active before it was marked as inactive for example)
Prior to activating the index, IDXACTIVATE checks to ensure that there are no users with a schema timestamp that is earlier than the schema timestamp of the index. If any such users are connected to the database, IDXACTIVATE cannot proceed. An option prompt to wait or cancel the index activation with a list of the current users blocking the activation procedure.
Example:
Activating Index <name> at Database timestamp 1431516775. (12811)
Usr Name Type Pid Timestamp
5 uname1 ABL 3124 1431516708
6 uname2 ABL 3220 1431516708
Connections with timestamps older than 1431516775 exist. (13246)
Do you wish to continue waiting..... Type y to continue (y/n). (13247)
If the option to wait is chosen, IDXACTIVATE will wait until all the users with an earlier schema timestamp to disconnect/reconnect the database in order to refresh their sessions, re-prompting at the -refresh interval (default 60 seconds), which specifies the number of seconds between displays of blocking users, as needed.
This is because a schema lock is necessary in order to activate an index and if there are any users connected to the database that have accessed the earlier version of the schema this lock cannot be achieved.
In OpenEdge 11.7 when Database Client Notification (-usernotifytime) is enabled, IDXACTIVATE waits until all connected clients respond to the notification, and then proceeds without the user having to take action.
Only one index can be activated at a time, if multiple indexes are being activated with IDXACTIVATE in separate command lines, the schema timestamp check will follow for each activation.
For further information refer to Article: What is IDXACTIVATE?
NOTE: The above actions can be used on the source database which is enabled for OpenEdge Replication however any of the above actions can trigger a significant amount of AI notes (if the number of records within the table is large) so make sure an ample supply of empty AI files is available or if using variable length AI files, make sure ample space is available in the file system for the AI files to grow.
