Indexes in SQL

Index in SQL

– Index is a schema object
– Index can be used by the Oracle server to speed up the retrieval of rows by using a pointer
– Index can reduce disk input/output (I/O) by using a rapid path access method to locate data quickly
– Index is independent of the table that it indexes
– Index is used and maintained automatically by the Oracle server

Two ways to create index

Automatically:
A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.
Manually:
Users can create non unique indexes on columns to speed up access to the rows.

Creating index

Create an index on one or more columns

CREATE INDEX index
ON table(column[, column]...);

Example:
Improve the speed of query access to the FIRST_NAME column in the EMPLOYEES

CREATE INDEX EMP_FIRST_NAME
ON employees(FIRST_NAME);

Guidelines for index creation

Do not create an index In below case:

  • The columns are not often used as a condition in the query
  • The table is small or most queries are expected to retrieve more than 2% to 4% of the rows in the table
  • The table is updated frequently
  • The indexed columns are referenced as part of an expression

Create index when

  • A column contains a wide range of values
  • A column contains a large number of null values
  • One or more columns are frequently used together in a WHERE clause or a join condition
  • The table is large and most queries are expected to retrieve less than 2%to 4% of the rows in the table

Removing an Index

syntax:

DROP INDEX index_name

Example:

DROP INDEX EMP_FIRST_NAME;

Leave a Reply

Your email address will not be published. Required fields are marked *