$Applaud$

SQL Indexes - Orapoint

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. For example, if you want to reference all pages in a book that discuss a certain topic, you first refer to the index, which lists all topics alphabetically and are then referred to one or more specific page numbers.

Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order.

The CREATE INDEX Command: The basic syntax of CREATE INDEX is as follows:

CREATE INDEX index_name 
ON table_name;

Single Column Indexes: A single-column index is one that is created based on only one table column. The basic syntax is as follows:

CREATE INDEX index_name 
ON table_name (column_name); Unique

Unique Indexes: Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table. The basic syntax is as follows:

CREATE INDEX index_name 
on table_name (column_name);

Composite Indexes: A composite index is an index on two or more columns of a table. The basic syntax is as follows:

CREATE INDEX index_name 
on table_name (column1, column2);

The DROP INDEX Command: An index can be dropped using SQL DROP command. Care should be taken when dropping an index because performance may be slowed or improved.

DROP INDEX index_name;

When should indexes be avoided?

Although indexes are intended to enhance a database’s performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered:

  • Indexes should not be used on small tables.
  • Tables that have frequent, large batch update or insert operations.
  • Indexes should not be used on columns that contain a high number of NULL values.
  • Columns that are frequently manipulated should not be indexed.