Index

Index

• SQL INDEX is used to quickly find data without searching every row in a database table.
• SQL INDEX is improved the speed of search operation on a database table. 
• It is a copy of the selected column of the database table to store additional duplicate copies of the data.
• End users do not know for indexes is created on the 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 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 the INDEX column.

Simple INDEX

• Simple INDEX creates only one selected column of the database table.
• Syntax

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

• Storage setting specifies the tablespace explicitly. 
• This is the optional storage setting if you do not specify automatically the default storage setting used.
• Example

SQL> CREATE INDEX index_user_name
    ON userinfo (name);

• We are creating a simple index on the 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 a composite index on no, name column of the userinfo table. 
• Duplicate values are allowing for creating indexes.

Unique INDEX

• Unique INDEX creates on a 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 create a unique index on the name column of the userinfo table. 
• Duplicate name value 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.