Term: CREATE INDEX

Definition:
The CREATE INDEX statement is how index are created for tables in the database. Indexes are vital to optimal performance in any database, and they become more and more important as the number of rows in the tables increase. It is not uncommon for performance gains of 30 to 50% to be achieved simply by creating indexes on frequently used tables in the database.

Example Syntax:

CREATE INDEX

   idx_user

ON

   users (city)

TABLESPACE

   index_tbs;

This statement creates an index called 'IDX_USER'. This index is built on the CITY column of the USERS table. The tablespace that the index will be created in is set by the TABLESPACE keyword. In this instance the index was created in the INDEX_TBS TABLESPACE.

Indexes can also be created on multiple columns in a table. This is known as a concatenated or composite index. If we wanted to create an index on the USERS table on the columns CITY and STATE, we would create it like this:

CREATE INDEX idx_user ON users (city, state) TABLESPACE index_tbs;

From time to time you may need to rebuild the index (typically for performance reasons). The index we created above could be rebuilt with this command:

ALTER INDEX idx_user REBUILD TABLESPACE;

The REBUILD keyword is what tells Oracle to rebuild the index. Oracle will create the rebuilt index in the same tablespace by default, but by using a name after the TABLESPACE keyword we can force the index to be built in a new tablespace, as in this example:

ALTER INDEX idx_user REBUILD TABLESPACE user_index;

To remove an index, use the DROP INDEX statement, followed by the index name to be dropped:

DROP INDEX idx_user;