Database Management
Register
Advertisement

An index is a formal list ordered in a particular manner, typically alphabetically or numerically. In terms of Databases, an index serves that same primary function but in addition, increases the speed of operations in a table (locating rows and columns more quickly)[1].

Basics[]

Indices are created in an already existing table, which the users do not see. Its size is much smaller than the actual table, as it does not reflect the data within the table, just provides the means to locate that data within the table quicker.

There are many types of indices, these include sparse (also known as B-tree), bitmap, partitioned, function, domain, clustered, and dense. An index can also be unique, in that a row cannot contain the same index value that is shared with another row[2]. Conversley, when the index is not unique, it allows duplicate values.

Clustered Index[]

The difference between a clustered and non-clustered index is actually very significant. For the clustered index, the data is actually stored inside of the index, rather than containing a 'bookmark' to the data[4].

A clustered index is very useful when you want to retrieve many rows of data, a range of data, or when the BETWEEN clause is used in the WHERE clause[5].

Creating an Index[]

Prerequisites[3][]

There are several prerequisites where at least one of them has to be true before an index can be created. These are:

 1) The table to be indexed must be in your own schema.
 2) You must have the INDEX object privilege on the table to be indexed.
 3) You must have the CREATE ANY INDEX system privilege.

Creation[]

Given that at least one of the above is true, to create an index in SQL, perform the following command:

CREATE INDEX the_index_name ON the_table_name (the_column_name)

The words in all CAPS are SQL specific keywords, while the lowercase words imply user defined values or names. Keep in mind (the_column_name) must be within parenthesis.

This will create a very simple, non-unique index named “the_index_name” on the table “the_table_name” for the attribute (or column) “the_column_name.”

See “General Guidelines” below on why it’s a good practice to only create indices for columns rather than rows. It’s also important to note that when you DELETE a table, you also delete its indices.

General Guidelines[]

It’s a good practice to only create indices for columns rather than rows, because the indices need to be updated when the table is updated as well[2].

References[]

[1] http://en.wikipedia.org/wiki/Index_%28database%29

[2] http://www.w3schools.com/sql/sql_create.asp

[3] http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_5010.htm#i2084975

[4] Headgate, Christoffer. <http://www.quest-pipelines.com/newsletter-v4/1103_B.htm>

[5] Vijayakuma, G. Dec 15, 2004. <http://www.sql-server-performance.com/articles/per/index_data_structures_p6.aspx>

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
Advertisement