Release 10.1A: OpenEdge Data Management:
SQL Development

> ## Working with indexes

> An index is a database object used to speed the time in which data is retrieved from a table. Although OpenEdge creates an index for any column to which you assign a unique constraint, you can also explicitly create an index.

> ##### Using the CREATE INDEX statement

> The `CREATE INDEX` statement creates an index on one or more columns of a table. You can specify an index in ascending order (`ASC`) or descending order (`DESC`).

> Note: For a complete description of the `CREATE INDEX` statement, see OpenEdge Data Management: SQL Reference .

> The `CREATE INDEX` statement uses the following syntax:

> Syntax
>     CREATE [ UNIQUE ] INDEX index_name
>       ON table_name
>       ( { column_name [ ASC | DESC ] } [, ... ] )
>       [ AREA area_name ]
>     PRO_ACTIVE { ‘N’ | ‘n’ };

> The index in this `CREATE INDEX` example is specified on the single column `empno`, and is of ascending order on the value of the column, as shown in Example 5–6.

> Example 5–6: CREATE INDEX statement
>      CREATE INDEX idx_emp ON SPORTS.employee (empno ASC) ;

> ##### DROP INDEX

> Use the `DROP INDEX` statement to drop a table index. An index can only be dropped from tables with more than one index. The initial index of a table cannot be dropped. Example 5–7 demonstrates the use of a `DROP INDEX` statement.

> Example 5–7: DROP INDEX statement
>     DROP INDEX idx_cust ON customer;

> For information on the use of indexes, see Chapter 6, " OpenEdge SQL Data Manipulation Language."

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