SQL INDEX

What is an index in SQL? SQL INDEX are used to quickly find data without searching every rows in a database table.

SQL INDEX is improve the speed of search operation on a database table. But additional you need more storage space to maintain duplicate copy of the database.

INDEX is a copy of the selected column of the database table to store additionally duplicate copy of the data.

End users does not know for indexes is created on table, only they are searching data more quickly and efficiently.

Type of SQL INDEX

CREATE INDEX statement to create indexes on a table. following 3 type indexes you are create on a table.

• Simple INDEX : Create INDEX on one column.
• Composite INDEX : Create INDEX on multiple columns.
• Unique INDEX : Create INDEX on column for restrict duplicate values on INDEX column.

Simple INDEX

Simple INDEX create only one selected column of the database table.

Syntax

CREATE INDEX index_name 
    ON table_name (column_name)
    [ storage_setting ];

Storage setting specifies the table space explicitly. This are the optional storage setting if you are not specifies automatically default storage setting used.

Example

SQL> CREATE INDEX index_user_name
    ON userinfo (name);

We are creating simple index on name column of the userinfo table. In this column allow duplicate values of the column.

Composite INDEX

Composite INDEX create on multiple selected column of the database table.

Syntax

CREATE INDEX index_name 
    ON table_name (column_name, column_name)
    [ storage_setting ];

Example

SQL> CREATE INDEX index_userinfo
    ON userinfo (no, name);

We are creating composite index on no, name column of the userinfo table. Duplicate values are allowing for creating indexes.

Unique INDEX

Unique INDEX create on selected column of the database table and does not allow duplicate values of that indexes column.

Syntax

CREATE UNIQUE INDEX index_name 
    ON table_name (column_name)
    [ storage_setting ];

Example

SQL> CREATE UNIQUE INDEX index_user_name
    ON userinfo (name);

We are create unique index on name column of the userinfo table. Duplicate name value are does not allow again for creating indexes.

RENAME INDEX

Syntax

ALTER INDEX index_name 
    RENAME TO new_index_name;

Example

SQL> ALTER INDEX user_name
    RENAME TO index_username;

We are renaming the above created index name index_user_name to a new index name index_username.

DROP INDEX

Syntax

DROP INDEX index_name;

Example

SQL> DROP INDEX index_username;

In this statement we are dropping index_username INDEX.

• « Previous
• Next »