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 include 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 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

Because the members table has a primary key column member_id, Oracle creates 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:

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:

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:

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.