Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

Best way to add new index to table with large count of records?

« Go Back

Information

 
Article Number000041377
EnvironmentProduct: OpenEdge
Version: All supported versions
OS: All supported platforms
Question/Problem Description
Best way to add new index to table with large count of records?
How to add a new index to table with large number of records.
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
The best way to add a new index to an existing database with a large number of records is to define the new index as inactive in the Data Dictionary or .df file. This will postpone the generation of the index keys.

It is strongly recommended that largefiles are enabled for the database to avoid hitting file maximum sizes within Progress / OpenEdge which requires the Enterprise RDBMS license which should be in use for large databases. Large files support was introduced in Progress 9.1C on most platforms. It is enabled by default since OpenEdge 12. Refer to Article 000020560  How to tell if proutil -C EnableLargeFiles has been run against a database?

To enable large files use the following command:
$   proutil <dbname> -C enablelargefiles

Method 1:  Run IDXBUILD offline

The quickest way to activate the index would be to take the database offline and perform an idxbuild operation against the index.
A verified backup of the database is required in the event the idxbuild fails which will render the database inaccessible until the IDXBUILD completes.

Example:
  1. proutil <dbname> -C idxbuild
  2. Choose the some option.
  3. Provide the table name
  4. Provide the index name
  5. Enter ! to end the table and index selection.
  6. Enter y to indicate sufficient sort space. Refer to Article 000001268, How to estimate disk space needed for sorting by IDXBUILD  
Example:
$   proutil sports2000 -C idxbuild -B 32000 -TB 64 -TM 32 -TMB 128 -TF 60 -SG 64 -thread 1 -threadnum 4 -mergethreads 4 -datascanthreads 4 -pfactor 90 -rusage -SS /path/pcs-base.srt
 
OpenEdge Release 10.2B08 as of Wed Jun 19 19:32:50 EDT 2013
 
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 Activation (v/V) - Rebuild selected active or inactive indexes
 
Quit     (q/Q) - Quit, do not rebuild
 
Enter your selection:
s
 
Enter the name of table containing the index.
Enter ! to indicate input is complete. (9176)
 
Table name:
customer
 
Index name:
name
 
Enter the name of table containing the index.
Enter ! to indicate input is complete. (9176)
 
Table name:
!
 
The following indexes will be rebuilt:
Owner: PUB
Table: customer
Index: name
 
Is this correct? (y/n)
y
Do you have enough disk space for index sorting? (y/n)
y


Method 2. Run IDXACTIVATE online 

If the database cannot be taken offline and the index is not the Primary Index, then add the index as INACTIVE and use IDXACTIVATE while the database is online.  For further information refer to Article 000013378, What is IDXACTIVATE?  

The syntax for IDXACTIVATE:
$  proutil <db-name> -C idxactivate [owner-name.]table-name.index-name [useindex index-name] [recs n] [refresh t]

Example:
$   proutil sports2000 -C idxactivate pub.customer.name recs 500
Workaround
Notes
Attachment 
Last Modified Date8/29/2019 7:07 AM