ORACLE-INDEX

ORACLE-INDEX- Learn about different types of index available  in oracle and how to create index ,rename index and drop index.

ORACLE-INDEX

• Index sorts the data and assigns identification to each row.
• The index table have only 2 columns, one is row id and another is indexed column(ordered).
• When trying to retrieve the data from the table based on the indexed column, the index pointer searches the rowid and quickly finds the position.

Points to Notice:

1. Indexes are used to to find and retrieve the data in a table very faster.
2. Indexes are applied on columns for faster retrieval of data from a table if table have thousands of records.
3. Indexes take less time to fetch data from table when using select queries.
4.  Table updation will take more time when using indexes. While updating the data,indexes on the table also updated.
5.  Performance of an index can be recognized much when tables with large amount of data.
6. One or more indexes can be created on a table.

SQL Create Index

SQL Create Index syntax

CREATE [UNIQUE] INDEX [index_name] on [table_name](column_names);

• Index can be created on single and multiple columns.
• Index can be created on both unique key and non unique key columns.
• In non unique key columns duplicate values are allowed and in unique key columns duplicate values are not allowed.

SQL Create Index syntax on non- Unique Columns

CREATE INDEX [index_name] on [table_name](column_names);

SQL Query example to Create Index on single column

CREATE INDEX idx_title ON BOOKS(TITLE);

SQL Query example to Create Index on multiple columns

CREATE INDEX idx_fullname ON BOOKS(AUTHORFIRSTNAME,AUTHORLASTNAME);

SQL Query example to create index on non-Unique Columns

CREATE INDEX idx_fullname ON BOOKS(AUTHORFIRSTNAME,AUTHORLASTNAME);

Output:

Query OK, 0 rows affected (0.19 sec)

SQL Query example to create index on Unique Columns

CREATE UNIQUE INDEX idx_title ON BOOKS(TITLE);

Output

Query OK, 0 rows affected (0.43 sec)

SQL RENAME INDEX

SQL Rename Index syntax:

ALTER TABLE [table_name]
DROP INDEX [index_name],
ADD INDEX [newindex_name] (column_names);

or

ALTER TABLE [table_name]
RENAME INDEX [index_name] to [newindex_name];

Note: The above syntax works only for Mysql 5.7 and Newer versions.

SQL Query example to Rename Index.

ALTER TABLE BOOKS
DROP INDEX IDX_TITLE,
ADD INDEX IDX_BOOKTITLE(TITLE);

or
ALTER TABLE BOOKS
RENAME INDEX idx_title to IDX_BOOKTITLE;

SQL DROP INDEX

SQL Drop Index syntax

DROP INDEX [idx_name] ON [table_name];

SQL Query example to Drop Index

DROP INDEX IDX_BOOKTITLE ON BOOKS;