SQL-J Language Reference
Page 12 of 121

CREATE INDEX statement

A CREATE INDEX statement creates an index on a table. Indexes can be on one or more columns in the table.

Syntax

CREATE [UNIQUE] [ BTREE] INDEX IndexName
ON TableName ( SimpleColumnName
    [ , SimpleColumnName ] * )
[ PROPERTIES clause ]

A column must not be named more than once in a single CREATE INDEX statement. Different indexes can name the same column.

Cloudscape can use indexes to improve the performance of data manipulation statements (see Tuning Cloudscape). Also, UNIQUE indexes provide a form of data integrity checking.

Index names are unique within a schema. (Some database systems allow different tables in a single schema to have indexes of the same name.)

Both index and table are assumed to be in the same schema if a schema name is specified for one of the names, but not the other. If schema names are specified for both index and table, an exception will be thrown if the schema names are not the same. If no schema name is specified for either table or index, the current schema is used.

For information about how indexes affect query optimization, see Tuning Cloudscape.

A PROPERTIES clause allows you to specify page size for the index and overrides the default page size set on a database-wide basis. See PROPERTIES clause.

Indexes cannot be created in the SYS schema.

Indexes and Constraints

Unique, primary key, and foreign key constraints generate indexes that enforce or "back" the constraint (and are thus sometimes called backing indexes). If a column or set of columns has a UNIQUE or PRIMARY KEY constraint on it, you do not need to create an index on those columns. Cloudscape has already created it for you with a system-generated name. System-generated names for indexes that back up constraints are easy to find by querying the system tables if you name your constraint, as recommended by Cloudscape. For example, to find out the name of the index that backs a constraint called FLIGHTS_PK:

SELECT conglomeratename
FROM sys.sysconglomerates, sys.sysconstraints
WHERE isconstraint=true
AND sys.sysconglomerates.tableid = sys.sysconstraints.tableid AND constraintname = 'FLIGHTS_PK'

See CONSTRAINT clause for more information about constraints.

Indexes on Java Data Types

You cannot create indexes on Java data type columns unless they are orderable. For information about orderable Java data types, see Orderable Java Data Types and Orderable Java Data Types and Indexes.

CREATE INDEX Examples

CREATE BTREE INDEX OrigIndex ON Flights(orig_airport)

CREATE INDEX large_index ON PageContent (large_text_column)
PROPERTIES cloudscape.storage.pageSize=8192

-- create an index on a column
-- that stores an Orderable Java data type
CREATE INDEX ci ON Cities(city)

Page Size and Key Size

NOTE: The size of the key columns in an index must be equal to or smaller than half the page size. If the length of the key columns in an index is larger than half the page size of the index, creating an index on those key columns for the table fails. For existing indexes, an insert of new rows for which the key columns are larger than half of the index page size causes the insert to fail.

Dependency System

Prepared statements that involve SELECT, INSERT, UPDATE, UPDATE WHERE CURRENT, DELETE, and DELETE WHERE CURRENT on the table referenced by the CREATE INDEX statement are invalidated when the index is created. Open cursors on the table are not affected.