Oracle Create Index

Summary: in this tutorial, you will learn how to use the Oracle CREATE INDEX statement to create a new index for a table.

Introduction to Oracle CREATE INDEX statement

To create a new index for a table, you use the CREATE INDEX statement as follows:

CREATE INDEX index_name ON table_name(column1[,column2,...])
Code language: SQL (Structured Query Language) (sql)

In this syntax:

  • First, specify the name of the index. The index name should be meaningful and includes table alias and column name(s) where possible, along with the suffix _I such as:
    <table_name>_<column_name>_I
    
  • Second, specify the name of the table followed by one or more indexed columns surrounded by parentheses.

By default, the CREATE INDEX statement creates a btree index.

When you create a new table with a primary key, Oracle automatically creates a new index for the primary key columns.

Unlike other database systems, Oracle does not automatically create an index for the foreign key columns.

Oracle CREATE INDEX examples

The following statement creates a new table named members that stores members’ data:

CREATE TABLE members( member_id INT GENERATED BY DEFAULT AS IDENTITY, first_name VARCHAR2(100) NOT NULL, last_name VARCHAR2(100) NOT NULL, gender CHAR(1) NOT NULL, dob DATE NOT NULL, email VARCHAR2(255) NOT NULL, PRIMARY KEY(member_id) );
Code language: SQL (Structured Query Language) (sql)

To load data into the  members table, you use the following script:

Download load_member_data Script

The members table has a primary key column, therefore,member_id Oracle created a new index for this column. To view all indexes of a table, you query from the all_indexes view:

SELECT index_name, index_type, visibility, status FROM all_indexes WHERE table_name = 'MEMBERS';
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Oracle Create Index - Index for the primary key

Creating an index on one column example

Suppose, you often want to look up members by the last name and you find that the query is quite slow. To speed up the lookup, you create an index for the last_name column:

CREATE INDEX members_last_name_i ON members(last_name);
Code language: SQL (Structured Query Language) (sql)

Now, showing the indexes again, you will find that the members table has two indexes:

SELECT index_name, index_type, visibility, status FROM all_indexes WHERE table_name = 'MEMBERS';
Code language: SQL (Structured Query Language) (sql)

The output is:

Oracle Create Index - Index for one column

The following statement finds members whose last name is Harse:

SELECT * FROM members WHERE last_name = 'Harse';
Code language: SQL (Structured Query Language) (sql)

To check if a query uses the index for lookup or not, you follow these steps:

First, add the EXPLAIN PLAN FOR clause immediately before the SQL statement:

EXPLAIN PLAN FOR SELECT * FROM members WHERE last_name = 'Harse';
Code language: SQL (Structured Query Language) (sql)

This explains the execution plan into the plan_table table.

Then, use the DBMS_XPLAN.DISPLAY() procedure to show the content of the plan_table:

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

Oracle Create Index - Plan for execution

Removing an index

To remove an index, you use the DROP INDEX statement:

DROP INDEX index_name;
Code language: SQL (Structured Query Language) (sql)

For example, to drop the members_last_name_i index, you use the following statement:

DROP INDEX members_last_name_i;
Code language: SQL (Structured Query Language) (sql)

You will learn more about how to drop an index in the next tutorial.

Creating an index on multiple columns example

The following example creates an index on both last name and first name columns:

CREATE INDEX members_name_i ON members(last_name,first_name);
Code language: SQL (Structured Query Language) (sql)

The following query finds members whose last name starts with the letter A and first name starts with the letter M:

SELECT * FROM members WHERE last_name LIKE 'A%' AND first_name LIKE 'M%';
Code language: SQL (Structured Query Language) (sql)

This statement definitely uses the members_name_i index as shown in the execution plan below:

EXPLAIN PLAN FOR SELECT * FROM members WHERE last_name LIKE 'A%' AND first_name LIKE 'M%'; SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use Oracle CREATE INDEX statement to create a new index on one or more columns of a table.

Was this tutorial helpful?