Creating Indices

The CREATE INDEX statement creates an index on one or more columns of a table. The existence of an index improves the accessing speed for rows in a table. The following example shows how to use the CREATE INDEX statement in an ESQL program:

Example Creating Indices

...

/* connect to a default database */

EXEC SQL CONNECT TO DEFAULT ;

EXEC SQL

     CREATE INDEX idx_cust

     ON customer (CUST_NO ASC) ;

if (sqlca.sqlcode)

{

  fprintf (stderr,

  "Create index statement failed (%ld : %s) \n",

  sqlca.sqlcode, sqlca.sqlerrm);

  EXEC SQL ROLLBACK WORK ;

  EXEC SQL DISCONNECT DEFAULT ;

  exit (1);

}

/* Commit changes */

EXEC SQL COMMIT WORK ;

printf ("Index idx_cust created. \n");

/* Disconnect from the database */

EXEC SQL DISCONNECT DEFAULT ;

...

The index in the previous example is specified on only one column, CUST_NO, and the index is specified to be of ascending order on the value of the CUST_NO column. The index can also be specified to be descending on a column value using the keyword DESC.