Since all columns in a table are handled in the same way, each column can be used as a search criterion. However, this does not mean that all search operations are equally efficient.
If you want to use a particular column to process search conditions, you should create an index for this column. This enables you to find the desired rows more quickly, since the database system attempts to use indices to search for entries.
To create an index, use the CREATE INDEX statement.
CREATE INDEX name_index ON customer (name)
You use this statement to create a single-column index with the name name_index.
CREATE INDEX full_name_index ON customer (name,firstname)
You use this statement to create a two-column index with the name full_name_index.
If you want to create an index that (like the key) ensures uniqueness, you have to use the keyword UNIQUE.
CREATE UNIQUE INDEX address_index ON customer (firstname,name,address)
See also:
CREATE INDEX Statement (create_index_statement)
You can define an UNIQUE index at the same time as you define the table, using the CREATE TABLE statement.
...
1.
Drop the person table.
DROP TABLE
person
2.
Create
the person table.
CREATE TABLE person
(pno FIXED(6) PRIMARY KEY,
name CHAR(20) CONSTRAINT name_index
UNIQUE,
city CHAR(20))
You use this statement to create a single-column UNIQUE index with the name name_index.
3.
Drop the person table.
DROP TABLE
person
4.
Create
the person table.
CREATE TABLE person
(pno FIXED(6) PRIMARY KEY,
name CHAR(20),
city CHAR(20),
CONSTRAINT address_index UNIQUE (name,city))
You use this statement to create a two-column UNIQUE index with the name address_index.
See also:
CREATE TABLE Statement (create_table_statement)
UNIQUE Definition (unique_definition)
To drop an index, use the DROP INDEX statement.
DROP INDEX full_name_index ON customer
You can use this SQL statement to drop the index definition and the data contained in the index; this does not affect the table contents.
See also:
DROP INDEX Statement (drop_index_statement)