Embedded Usage

In an embedded Create Index statement, the following elements can be replaced with host language variables:

The preprocessor does not validate the with clause syntax. The with clause can be specified using a host string variable (with :hostvar).

Permissions

You must be the owner of a table. Users cannot update indexes directly. When a table is changed, the DBMS Server updates indexes as required. To create indexes on system tables, the effective user of the session must be $ingres. For information about the $ingres user, see the System Administrator Guide.

Locking

Creating an index on a table requires an exclusive lock on the table. This lock prevents other sessions, even those using the readlock=nolock option, from accessing the table until create index completes and the transaction containing it is completed.

Related Statements

Create Table

Drop

Modify

Examples: Create Index

The following are Create Index statement examples:

1. Create an index for the columns, ename and age, on the employee table. The index is recreated when the table is modified.

   create index ename on employee (ename, age)

       with persistence;

2. Create an index called ename and locate it on the area referred to by the location_name, remote.

   create index ename on employee (ename, age)

       with location = (remote);

3. Create a btree index on the ename and eage columns, keyed on ename with leaf index pages filled 50 percent.

   create index ename2 on employee (ename, eage)

       with key = (ename),

       structure = btree,

       leaffill = 50;

4. Create a unique index, specifying that uniqueness is checked after any update statements are completed.

   create unique index ename3 on employee (ename, empno)

       with key = (ename, empno),

       unique_scope = statement;

5. Create a single, unique index on column c1 in table tl.

   create index i1 on t1 (c1) unique

6. Create a unique index using the with clause to override default index structure.

   create unique index (i1 on t1(c1) with structure=hash)

7. Create multiple indexes at one time using the unique qualifier before the index keyword. All indexes created by this statement are unique.

   create unique index (i1 on t1(c1) with structure=hash,persistence),

       (i2 on t1(c2) with structure=btree)

8. Create multiple indexes at one time using the unique keyword within each index specification.

   create index (i1 on t1 (c1) unique with structure=hash,persistence), (i2 on

       t1(c2) unique with structure=btree)

9. Create both unique and non-unique indexes.

   create index (i1 on t1(c1) unique with structure=hash,persistence),

                  (i2 on t1(c2) with structure=btree)

Note: Examples 7 and 8 perform the same operation, while 9 demonstrates individual control of the UNIQUE attribute.