<< RETURNS NULL ON NULL INPUT | DYNAMIC RESULT SETS integer >>

Statement dependency system

<< RETURNS NULL ON NULL INPUT | DYNAMIC RESULT SETS integer >>

Derby Reference Manual

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.

CREATE PROCEDURE statement

The CREATE PROCEDURE statement allows you to create Java stored procedures,
which you can then call using the CALL PROCEDURE statement.