Background documentationIndexes Locate this document in the navigation structure

 

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 can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

Creating an Index

To create an index, use the CREATE INDEX statement.

CREATE INDEX 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 city_state ON hotel.city (state)

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

CREATE INDEX 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 a UNIQUE index at the same time as you define the table, using the CREATE TABLE statement.

  1. Delete the person table, if necessary.

    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.

  1. 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),

    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), 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 Information

More examples for Data Definition