CREATE INDEX

This SQL statement can be executed in the following ways:	Through the CA Datacom Datadictionary Interactive SQL Service Facility (interactive)	In an application program prepared using a CA Datacom/DB SQL Preprocessor (embedded)	By using CA Dataquery (SQL & Batch Modes)
CREATE INDEX	YES	YES	YES

Note: YES indicates a valid execution method for this statement. To learn about using SQL keywords in CA Dataquery, see the CA Dataquery User Guide. For information about the access rights required to execute this statement, see the CA Datacom/DB Database and System Administration Guide.

The CREATE INDEX statement is a CA Datacom/DB extension. CREATE INDEX defines a new index on one or more columns of a base table. Adding an index to a table may improve the performance of some queries which reference that table.

When a CREATE INDEX statement successfully executes, a KEY entity-occurrence is defined in CA Datacom Datadictionary in PROD status. The five-character key name is generated for you by CA Datacom Datadictionary. The key's attributes include MASTER-KEY=N, NATIVE-KEY=N, INCLUDE-NIL-KEY=Y, and UNIQUE=N. For information about specifying SQL key selection override keys in either the correlation name or synonym name of a query, see Overriding SQL Key Selection.

CREATE INDEX causes all plans dependent on the indexed table to be marked invalid. You can run a Datadictionary Relationship Report to find out what plans are dependent on a table. See the CA Datacom Datadictionary Batch Reference Guide for information on Relationship Reports.

Note: When the NOMAINT option of the CA Datacom/DB Utility (DBUTLTY) ACCESS function is in force, a CREATE INDEX statement receives a CA Datacom/DB return code 94(87), where 87 is a decimal internal return code (hex 57) that tells you no maintenance statements are allowed while NOMAINT is in force. With regard to table partitioning, CREATE statements may not be issued against a table which is partitioned nor against a partition. For more information about table partitioning, see the CA Datacom/DB Database and System Administration Guide.

Following is the syntax diagram for the CREATE INDEX statement:

►►─ CREATE ─┬──────────┬─ INDEX ─┬────────────┬─ index-name ─ ON ─────────────►
            └─ UNIQUE ─┘         └─ auth-id. ─┘

                                   ┌─ , ───────────────────────┐
 ►─┬────────────┬─ table-name ─ ( ─▼─ column-name ─┬─────────┬─┴─ ) ──────────►
   └─ auth-id. ─┘                                  ├─ ASC ◄ ─┤
                                                   └─ DESC ──┘

 ►─┬───────────────────────┬──────────────────────────────────────────────────►◄
   └─ DATACOM NAME ─ name ─┘