×
☰ See All Chapters

Oracle Indexes

  1. It is a DB object. Simply put, an index is a reference to data in a table. An index in a database is similar to an index in the back of a book. Indexes allow the database to find data fast, without reading the whole table. 

  2. Indexes consume a physical space and grow up with the data of table and can often grow larger than the table.  Indexes should be created only on columns where lots of read operations are happening. But never create index for columns where lot of CUD (Create, Update, Delete) operations are happening. 

  3. When a Primary Key or Unique Key is added on a column, that column is indexed implicitly. 

  4. An index’s main purpose is to improve the performance of data retrieval. With index data retrieval is faster that full table scan does not happen and directly read the data by index reference. An index is faster because it typically stores information in an orderly treelike format. A full table scan occurs if there is no index on the table. 

Syntax to create indexes in Oracle

CREATE INDEX <index-name> ON <table-name> (<column1>, <column2>);

 

Oracle indexes example

CREATE TABLE CUSTOMER

(

CUSTOMER_ID INT,

CUSTOMER_NAME VARCHAR2(30) ,

EMAIL VARCHAR2(30) ,

DOB DATE

)

CREATE INDEX IDX_CUSTOMER_NAME ON CUSTOMER (CUSTOMER_NAME);

Syntax to drop index

DROP INDEX IDX_CUSTOMER_NAME

All Chapters
Author