Oracle Tutorial

What is Oracle
SQL Keywords

Oracle Wildcards

Oracle Wildcards

Oracle Aliases

Oracle Aliases

MySQL Tutorial

MySQL Tutorial

PL/SQL Tutorial

PL/SQL Tutorial

Oracle Interview Questions

Oracle Interview Questions and Answers


Oracle INDEX Constraint

In Oracle, INDEX constraint are used to speed up data retrieval from database table. To create INDEX in Oracle, CREATE INDEX statement is used to create indexes in tables. Oracle allows to create multiple-column INDEX.

Oracle INDEX Syntax

To create indexes in tables using CREATE INDEX statement, use the following syntax:

Syntax

CREATE INDEX indexname
ON tablename (col1);

Parameters:

indexname: Name of the INDEX, you want to create.

tablename: The table name from which you want to fetch records.

col1: The column name on which you want to create index.

Oracle INDEX Constraint example

The following Oracle, creates a INDEX on "custId" column for "tblCustomer" table:

Example

CREATE INDEX idex_custId
ON tblCustomer (custId);

Oracle UNIQUE INDEX Syntax

To create unique index in table using CREATE UNIQUE INDEX statement, use the following syntax:

Syntax

CREATE UNIQUE INDEX indexname
ON tablename (col1);

Oracle UNIQUE INDEX Constraint example

The following Oracle, creates a UNIQUE INDEX on "custId" column for "tblCustomer" table:

Example

CREATE UNIQUE INDEX idex_custId
ON tblCustomer (custId);

Oracle DROP a INDEX Constraint example

The following Oracle, dropping INDEX constraint "idex_custId" from "tblCustomer" table:

Example

ALTER TABLE tblCustomer
DROP INDEX idex_custId;