A CREATE INDEX statement creates
an index on a table. Indexes can be on one or more columns in the table.
Syntax
CREATE [UNIQUE] INDEX index-Name
ON table-Name ( Simple-column-Name [ ASC | DESC ]
[ , Simple-column-Name [ ASC | DESC ]] * )
The
maximum number of columns for an index key in Derby is
16.
An index name cannot exceed 128 characters.
A column must
not be named more than once in a single CREATE INDEX statement. Different
indexes can name the same column, however.
Derby can
use indexes to improve the performance of data manipulation statements (see Tuning Derby). In addition, 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, but Derby does
not.) 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.
By default, Derby uses
the ascending order of each column to create the index. Specifying ASC after
the column name does not alter the default behavior. The DESC keyword after
the column name causes Derby to
use descending order for the column to create the index. Using the descending
order for a column can help improve the performance of queries that require
the results in mixed sort order or descending order and for queries that select
the minimum or maximum value of an indexed column.
If a qualified index
name is specified, the schema name cannot begin with SYS.
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 can not
create an index on those columns. Derby 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. Adding a PRIMARY KEY or UNIQUE
constraint when an existing UNIQUE index exists on the same set of columns
will result in two physical indexes on the table for the same set of columns.
One index is the original UNIQUE index and one is the backing index for the
new constraint.
To find out the name of the index that backs a constraint
called FLIGHTS_PK:
SELECT CONGLOMERATENAME FROM SYS.SYSCONGLOMERATES,
SYS.SYSCONSTRAINTS WHERE
SYS.SYSCONGLOMERATES.TABLEID = SYSCONSTRAINTS.TABLEID
AND CONSTRAINTNAME = 'FLIGHTS_PK'
CREATE INDEX OrigIndex ON Flights(orig_airport);
-- money is usually ordered from greatest to least,
-- so create the index using the descending order
CREATE INDEX PAY_DESC ON SAMP.EMPLOYEE (SALARY);
-- use a larger page size for the index
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','8192');
CREATE INDEX IXSALE ON SAMP.SALES (SALES);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize',NULL);
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 existing row in a table is larger than half the page
size of the index, creating an index on those key columns for the table will
fail. This error only occurs when creating an index if an existing row in
the table fails the criteria. After an index is created, inserts may fail
if the size of their associated key exceeds the criteria.
Statement 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.
