Progress
Database Administration
Guide and Reference
Using 4GL Tools To Save Space By Deactivating Indexes
You can free up disk space for an application by deactivating its indexes. Also, if you are dumping and loading a large database, you can speed up the operation by deactivating the indexes in one or more database tables. This technique should be used only in situations where indexes are used infrequently, such as in an application that generates reports only once or twice a year. There are two ways to deactivate indexes:
You can deactivate (but not reactivate) a single index from either Data tool. If you create a new unique index on an existing file, consider deactivating the index. If existing table data yields duplicate keys, all changes made during the current session are backed out when you try to save them. Create or reactivate a unique index after you have ensured that all key values are unique.
To activate an index, use the PROUTIL IDXBUILD utility. See "Database Administration Utilities," for more information about activating indexes with the PROUTIL IDXBUILD utility.
Once an index is deactivated, you cannot use it to retrieve information from the records it normally points to. If you attempt to use a FOR, FOR EACH, or CAN-FIND statement in connection with a deactivated index, the database engine terminates the currently executing program. However, you can create, delete, or update a record that a deactivated index points to.
The database engine does not examine the active status of an index when it makes a search. Nor does the active status of an index affect the time stamp on the _Index file. As a result, precompiled programs do not require compilation if the only change to the schema is index activation or deactivation.
Deactivating Indexes
Follow these steps to deactivate an individual index or all of the indexes:
- Access the appropriate Data tool (the Data Administration tool if you are using a graphical interface or the Data Dictionary if you are using a character interface).
- Choose Utilities
Deactivate Indexes. The Index Deactivation dialog box appears.
- Choose OK. Progress lists the tables in the database.
- Type all to deactivate all indexes, or select the indexes you want to deactivate. The Data tool prompts you to verify that you want to deactivate all the indexes.
- Verify that you want to deactivate the specified indexes.
If you are using a graphical interface, follow these steps in the Data Dictionary to deactivate an individual index:
- Access the Data Dictionary.
- Choose the Index icon.
- Choose the index you want to deactivate.
- Choose the Index Properties button. The Index Properties dialog box appears.
- Select the table that contains the index you want to deactivate. The Data Dictionary lists the indexes defined for the selected database.
- Activate the Active toggle box.
- Choose the Save button. The Data Dictionary prompts you to verify that you want to deactivate the index.
- Verify that you want to deactivate the index. The Data Dictionary deactivates the index.
Deactivating Indexes With a Progress 4GL Procedure
You can also deactivate an index from a Progress 4GL procedure. Search through the _Index file until Progress finds the index you want to deactivate. Then set the _Active field equal to No. The following example uses this technique:
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |