Try OpenEdge Now
skip to main content
Basic Database Tools
Schema Menu Reference : Index Editor option
 

Index Editor option

Use the Index Editor option to define, rename, or delete indexes for the selected table. An index is a field or combination of fields used to rapidly retrieve a particular record in a table. A single index can be made up of multiple fields. For example, if you know you are always going to access the order-line table by using a combination of the Order-num and Line-num fields, you might want to create an index with those two fields as its components.
Note: The current table must contain fields before you can define an index.
When you choose this option, the Data Dictionary alphabetically lists all the tables defined for your database. When you select a table, a window similar to the one shown in the following figure appears.
Figure 36. Index Editor window
Use the Index Editor to display the existing indexes and their information for the selected table.
The Index Editor window contains the following fields:
*Name — Specifies the index name. It does not have to be the same name as the field name. Index names can be up to 32 character long and can consist of alphabetic characters, digits, and the characters $, &, #, %, –, and _. In addition, index names must begin with a letter (A–Z or a–z). You cannot use reserved ABL keywords as index names. Index names are not case sensitive.
*Unique/Non-Unique — Defines whether every entry in the index must be different. Enter yes if only one record in a table has a particular index value. For example, the customer table cannot contain two records with the same customer number, so customer number can be a unique index. Conversely, since many customers might be in the same sales region, sales-region should not be a unique index.
*ACTIVE — Defines whether the index is initially active, that is, whether it updates itself every time a new record is created, deleted, or modified. If you are defining an index in a very large table, it is more efficient to define the index as initially inactive. (When an index is inactive, you cannot use it to retrieve or order records.) You can subsequently activate the index by running the PROUTIL utility with the IDXBUILD qualifier. This is much faster than building the index from within the Data Dictionary when the database contains a large amount of data. For more information about PROUTIL, see OpenEdge Data Management: Database Administration.
*ROWID — For non-OpenEdge databases only. See the applicable OpenEdge DataServer guide for more information on the ROWID property.
*Word — Defines whether you can search on any word within a field. A word index contains all the words from a text field or array of text fields; thus, you can search for records that contain specific words or phrases. All words in the field are index entries. For more information about word indexes, see the chapter on database access in OpenEdge Getting Started: ABL Essentials.
*Seq — Defines the order in which fields form the index. For example, cust-order is a compound index, formed by the Cust-num and the Order-num fields. The Cust-num field has a sequence number of 1, and the Order-num field has a sequence number of 2.
*Field Name — Displays the field name.
*Type — Specifies the field data type. See Field Editor option in this chapter for more information about data types.
Note: BLOB and CLOB data types cannot participate in indexes.
*Asc/Dsc — Specifies how the index sorts records. Value is either asc for ascending or dsc for descending.
*Abbr — Specifies that you can search an index using the first few characters of a field without using the BEGINS phrase, if the field is a character data field. Indexes not comprised of character data require an exact match. If the index has more than one field, this setting applies only to the last field.
Besides the fields previously described, the Index Editor also lists options at the bottom of its display. The following table describes these options.
Table 23. Index Editor options
Option
Description
Next
Displays the next index in the table.
Prev
Displays the previous index in the table.
First
Displays the first index in the table.
Last
Displays the last index in the table.
Rename
Renames the selected index. The name you enter cannot already be used to name an index in the current database.
Add
Adds a new index to the table.
Delete
Deletes the selected index. You cannot delete a primary index without making another index the primary index.
ChangePrimary
Changes the primary index for the table.
Uniqueness
(Not supported for OpenEdge databases.)
MakeInactive
Makes an active index inactive. (When an index is inactive, you cannot use it to retrieve or order records.) To reactivate an index, you must run PROUTIL with the IDXBUILD qualifier.
Browse
Browses through the entire index component list. This is especially helpful when the index is made up of multiple fields.
SwitchTable
Shows the list of tables in the database, then switches to another table to edit its indexes.
GoField
Accesses the Field Editor.
Undo
Undoes changes you made to the index attributes during this session.
Exit
Saves your changes, exits the Index Editor, and returns to the Data Dictionary main menu.
Choose the field you want to edit, then choose a menu option. When you choose Add from the Index Editor, the Index Editor prompts you for the name of the new index.
When you enter data in the Name, Unique, Active, and Word fields, the Adding Index dialog box appears, as shown in the following figure:
Figure 37. Adding Index dialog box
The Index Editor lists the available fields and their data types. When you choose a field, the Index Editor prompts you to specify whether you want the index component to be ascending or descending. After you specify the sort order, the Index Editor lets you choose more fields. You can specify up to 16 fields to define an index.
The Index Editor lists the selected fields above the divider line on the window. Select all fields, then press GO. The Index Editor prompts you to specify whether you want to use the Abbreviate option for character fields then returns you to the main Index Editor window and adds the new index to the list.