How to Create and Use INDEX in Oracle with Examples

In this tutorial, we will learn what are indexes and how they are used and created in Oracle.

We might have noticed an index at the end of a technical book which contains all the important keywords and concepts listed alphabetically, so that we can quickly search and find what we are looking for. In absence of an index, we might have to go through the entire book page by page to find a keyword or concept.

Indexes in Oracle work on the same principle and allow faster retrieval of data especially when table contains more than a million records.

Syntax

CREATE INDEX index_name
ON table_name(column_name);

Here,

index_name – It is the name of index.

table_name – It is the name of table on which we want to create index.

column_name – We usually create index on one or more columns in a table. Here, column_name is the name of column in table on which we create index.

To summarize, we can say that an Index-

• Can be created on a column or multiple columns of a database table.
• Is generally used when the final outcome is less than 5% of entire original data set.
• Ensures faster retrieval of data from the table using the column on which index is defined.
• Is automatically managed by Oracle server in background once the Index is created by user.

Let’s understand how to use and create these indexes using these examples.

Oracle Index Examples

Let’s create a table named ‘Intern_table’ for these examples and insert some sample data into it.

CREATE TABLE intern_table
(
intern_id NUMBER(10) PRIMARY KEY,
first_name VARCHAR(10),
last_name VARCHAR(10),
salary NUMBER(10),
commission NUMBER(10),
gender CHAR(1),
us_citizen CHAR(1)
)

INSERT INTO intern_table VALUES(101, 'Walter', 'White',
9000, 200, 'M', 'Y');
INSERT INTO intern_table VALUES(102, 'Jesse', 'Pinkman',
15000, 300, 'M', 'Y');
INSERT INTO intern_table VALUES(103, 'Gus', 'Fring',
12000, 100, 'M', 'N');
INSERT INTO intern_table VALUES(104, 'Jane', 'Margolis',
6000, 200, 'F', 'Y');
INSERT INTO intern_table VALUES(105, 'Skyler', 'White',
14000, 50, 'F', 'N');
INSERT INTO intern_table VALUES(106, 'Saul', 'Goodman',
15000, 300, 'M', 'N');
INSERT INTO intern_table VALUES(107, 'Walter', 'Badman',
14000, 250, 'M', 'N');

Let’s see the contents of this table using SELECT query:

SELECT * FROM intern_table

Result:

Explain Plan

Explain Plan Statement is used for getting the execution plan of Query. It gives us a rough idea about how Oracle executes the query in background and how much CPU resources are required for its execution. This is especially useful when we are trying to optimize our query for big data sets containing millions of records.

The following 2 queries are used in sequence for first explaining the plan and then displaying it:

EXPLAIN PLAN FOR SELECT * FROM intern_table;

--Output is stored in Plan Table

SELECT * FROM table (dbms_xplan.display)

Result:

The following plan shows that the query requires full table access for execution and also gives us idea about CPU consumption and time required to execute a query.

We won’t get much meaningful data for our small table, but this is especially handy when query processes a big data set.

To see the impact of indexes on performance parameters, let’s create an index on a sample query:

The following query returns all employees named ‘ Walter’ in our table”

SELECT * FROM intern_table
WHERE first_name = 'Walter';

Result:

Explain Plan

Let’s see the execution plan for this query:

EXPLAIN PLAN FOR
SELECT * FROM intern_table
WHERE first_name = 'Walter';

SELECT * FROM table (dbms_xplan.display)

Result:

Create Simple Index

We create an index on a column in query to speed up the lookup and to improve and optimize performance of that query:

Now, let’s create an index on first_name column in the intern_table to optimize the query that we created above.

CREATE INDEX intern_idx
ON intern_table(first_name);

Result:

Now, that index has been created on first_name column, let’s measure its impact on performance by using Explain Plan Statement for above query:

EXPLAIN PLAN FOR
SELECT * FROM intern_table
WHERE first_name = 'Walter';

SELECT * FROM table (dbms_xplan.display)

Result:

As we can see from this execution plan, that cost consumption of CPU has reduced and also the same query now does an index range scan instead of ‘full table scan’ to find 2 interns named Walter.

Data Dictionary for Index

The indexes we create are stored in Oracle database as objects and can be accessed through data dictionary views.

The following queries help us in finding out more information about the existing indexes in a user schema and all the meta data associated with it.

SELECT * FROM USER_INDEXES;
SELECT * FROM USER_IND_COLUMNS;

SELECT ic.index_name, ic.column_name,
ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND lower(ic.table_name) = 'intern_table';

Result:

Drop Index

The following query is used for dropping an Index in Oracle:

DROP INDEX intern_idx;

In this tutorial, we learned about the concept of Index in Oracle and how it can help us optimize and improve performance of a query. We learned how to create, use and drop index in this tutorial.