Feedback
Did this article resolve your question/issue?

   

Article

Can inactive indexes be built online?

Information

 
TitleCan inactive indexes be built online?
URL NameCan-inactive-indexes-be-built-online-000060410
Article Number000172503
EnvironmentProduct: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Question/Problem Description
How can indexes be built online?
What options are there for online index activation?
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
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.

Workaround
Notes
Last Modified Date11/20/2020 7:14 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.