Progress
Basic Database
Tools


Schema>Index Editor

Choose Schema Index Editor to define, rename, or delete indexes for the selected table. An index is a field or combination of fields Progress uses 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.

When you choose this option, Progress alphabetically lists all the tables defined for your database. When you select a table, a window similar to the one shown in Figure 9–14 appears.

Figure 9–14: Index Editor Window

NOTE: The current table must contain fields before you can define an index.

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 Progress keywords as index names. Index names are not case sensitive.

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 the Progress Database Administration Guide and Reference.

ROWID

For non-Progress databases only. See your Progress 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 the Progress Programming Handbook.

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 the "Schema>Field Editor" section in this chapter for more information about data types.

Asc

Specifies that the index sorts records in ascending order.

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.

Table 9–16 describes the options for the Index Editor window.

Table 9–16: 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.
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. The following section describes the Add option in more detail.

Add Option

When you choose the Add option from the Index Editor, Progress prompts you for the name of the new index. When you enter data in the Name, Unique, Active, and Word fields and press RETURN, a window similar to the one shown in Figure 9–15 appears.

Figure 9–15: Index Add Option Window

Progress lists the available fields and their data types. When you choose a field, Progress prompts you to specify whether you want the index component to be ascending or descending. After you specify the sort order, Progress lets you choose more fields. You can specify up to 10 fields to define an index.

Progress lists the selected field above the divider line on the window. Select all your fields, then press GO. Progress prompts you to specify whether you want to use the Abbreviate option for character fields then returns you to the Index Editor and adds the new index to the list.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095