SQL CREATE INDEX

An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order. The table index has pointers to the values stored in specified column or combination of columns of the table. These pointers are ordered depending on the sort order specified in the index.

Syntax:

CREATE INDEX [index_name]

ON [table_name] (column_namel, column_name2 ...);

From the Syntax, [index_name] is the name given to the Index. [table_name] is the name given to the table on which the index is created. [Column_namel n] are the columns to be used for indexing.

• Take the example of EMPLOYEES (see in View's topic) where the information is not stored in any order and we want to search Salary of the employee whose name is 'Jagdish'. Searching will be difficult if we manually search the record by scanning all the rows. So we need indexing to make searching fast. Use the following command to search the record.

SQL> CREATE INDEX RAMAN_DHILLON

2 ON EMPlOYEES(NAME);

Index Created.

Explanation: On execution it will create an index 'RAMAN_DHILLON' on the column 'NAME'. After making the index Oracle will perform the searching fast with more speed. It sorts the Name in ascending order after fetch from each row.