CREATE INDEX statement
448
CREATE INDEX statement
Use this statement to create an index on a specified table. Indexes can
improve database performance.
CREATE [ UNIQUE ] INDEX
index-name
… ON [
owner
.]
table-name
… (
column-name
[ ASC | DESC ], … )
…[ WITH [ MAX | MAXIMUM ] HASH SIZE
hash-size
]
… [ { IN | ON }
dbspace-name
]
hash-size
:
integer
Must be the owner of the table or have either DBA authority or
REFERENCES permission.
The table must be a base table or a global temporary table.
Automatic commit.
"DROP statement" on page 505
"MAX_HASH_SIZE option" on page 197
"How indexes work" on page 817 of the book ASA User’s Guide
"Indexes on temporary tables" on page 819 of the book ASA User’s Guide
The CREATE INDEX statement creates a sorted index on the specified
columns of the named table. Indexes are automatically used to improve the
performance of queries issued to the database, and to sort queries with an
ORDER BY clause. Once an index is created, it is never referenced in a SQL
statement again except to validate it (VALIDATE INDEX) or delete it
(DROP INDEX).
You cannot create indexes on views.
♦
Index ownership There is no way of specifying the index owner in the
CREATE INDEX statement. Indexes are always owned by the owner of
the table. The index name must be unique for each owner.
♦
No indexes on views Indexes cannot be created for views.
♦
Index name space The name of each index must be unique for a
given table.
♦
Exclusive table use CREATE INDEX is prevented whenever the
statement affects a table currently being used by another connection.
CREATE INDEX can be time consuming and the server will not process
requests referencing the same table while the statement is being
processed.
Function
Syntax
Permissions
Side effects
See also
Description
To Next Page
Loading...