Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

ALTER INDEX

Statements

NAME

ALTER INDEX: change an index's options (extension)

SYNOPSIS

<alter index statement> ::=
    ALTER INDEX <index name>
    { SET ( <index option> [ { , <index option> }... ] )
    | REBUILD [ ONLINE ] [ PARAMETERS ( <parameter> [ { , <parameter> }... ] ) ]
    | MERGE DELTA | LOAD | UNLOAD }

<index option> ::= <option name> = { <option value> | DEFAULT }

DESCRIPTION

ALTER INDEX sets options of an index, DEFAULT an option's default. An option its method takes in place changes the index at once; any other builds the index again with it, while other transactions go on reading and writing the table, and then puts it in the old one's place. It is made at once, apart from any transaction: no ROLLBACK undoes it.

The options are those the index's method declares and those plug-ins declare for every index; of the built-in methods, btree, hash, rtree, xml_path, xml_value, xml_property and xml_primary declare none, and fulltext declares those of Full-Text Search. INFORMATION_SCHEMA.INDEX_OPTIONS shows an index's options.

Of a fulltext index, FULLTEXT.K1, FULLTEXT.B and FULLTEXT.RANKING change in place, as the index's entries do not depend on how its rows are ranked. FULLTEXT.RANKING names BM25 or a ranking the program registered, and Rank scores by it from the next statement on. FULLTEXT.LANGUAGE and FULLTEXT.STOP_WORDS build the index again.

REBUILD, MERGE DELTA, LOAD and UNLOAD are of a vector index, and are described with CREATE VECTOR INDEX.

RULES

  1. The session has no transaction open: COMMIT or ROLLBACK first.
  2. The index exists, and its user owns the schema of its table.
  3. Each option is declared, given once, of a value it accepts.

PRIVILEGES

The schema of the index's table is its user's.

ERRORS

SQLSTATE SQLCODE When
25000 -917 The session has a transaction open
2A000 -103 A rule above is broken
2A000 -104 Its user does not own the schema of the index's table

EXAMPLES

Of the index STAFF_NAME of CREATE INDEX, of the method hash, which declares no options:

ALTER INDEX STAFF_NAME SET (FILL = 50);
turborel: SQLCODE -103 (SQLSTATE 2A000): hash takes no option FILL (at character 28)

STANDARD

An extension: neither SQL-86 nor SQL-92 has indexes.

SEE ALSO

CREATE INDEX, CREATE VECTOR INDEX, DROP INDEX