Indexes 

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 indexes to search for entries.

Prerequisites

You require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

Creating an Index

To create an index, use the CREATE INDEX statement.

 

CREATE INDEX hotel.name_index ON hotel.customer (name)

You use this statement to create a single-column index with the name name_index in the hotel schema.

 

CREATE INDEX hotel.city_state ON hotel.city (state)

You use this statement to create a single-column index with the name city_state.

 

CREATE INDEX hotel.full_name_index ON hotel.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 hotel.customer (firstname,name,address)

 

See also:

SQL Reference Manual, 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.      If necessary, delete the person table.
DROP TABLE hotel.person

       2.      Create the person table.
CREATE TABLE hotel.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.      Delete the person table.
DROP TABLE hotel.person

       4.      Create the person table.
CREATE TABLE hotel.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:

SQL Reference Manual, CREATE TABLE Statement (create_table_statement)

SQL Reference Manual, UNIQUE Definition (unique_definition)

 

Deleting an Index

To delete an index, use the DROP INDEX statement.

 

DROP INDEX hotel.full_name_index ON hotel.customer

You can use this SQL statement to delete the index definition and the data contained in the index; this does not affect the table contents.

 

See also:

SQL Reference Manual, DROP INDEX Statement (drop_index_statement)

More examples for Data Definition