How Oracle Indexes Work and How to Create Them! A Complete Guide to Reading Execution Plans

When you feel that an Oracle SQL search is slow, the first measure you should consider is utilizing an “Oracle Index.” By creating indexes appropriately, data access for tens of thousands to hundreds of millions of records can be completed in an instant.

In this article, we will thoroughly explain everything from the mechanism of indexes and the basics of the B-tree structure, to creation procedures using an actual environment (Oracle Database 19c), and how to correctly read execution plans using EXPLAIN PLAN FOR.

🇯🇵日本語版はこちら (Japanese version) >

Conclusion: Shortest Steps (To-Do List)

If you are in a hurry to improve SQL performance, proceed with the following steps:

Current Status Analysis: Identify the slow SQL and check the columns in the WHERE clause or JOIN conditions.
Create Index: Grant an index to the target columns using the CREATE INDEX statement.
Verify Effect: Obtain the execution plan using EXPLAIN PLAN and confirm that the access path has changed to something like INDEX RANGE SCAN.

What is an Index? Mechanism and Structure

“Table of Contents” for Search

An index plays the same role as a “Table of Contents” in a book.

When looking for a specific word in a book without a table of contents, you must search sequentially from the first page (Full Table Scan). However, if there is a table of contents, you can access the relevant page directly.

Databases are the same; without an index, all rows are read, but with an index, you can pinpoint and retrieve data using the “ROWID (row address).”

Structure of B-tree Indexes

The most commonly used index in Oracle is the “B-tree (Balanced Tree) Index.” It delivers stable performance because the number of searches can be calculated (logarithmic order) even as the data volume increases.

         [Root Block]
              ↓
       +--------------+
       | Branch Block |
       +------+-------+
              ↓
      +-------------------------------------------+
      | Leaf Block (Key Value + ROWID)            |
      | ----------------------------------------- |
      | 100 -> AAAB... (Physical Row Address)     |
      | 101 -> AAAC...                            |
      +-------------------------------------------+

Merits and Demerits (Operational Precautions)

Indexes are not a panacea. You need to understand the following trade-offs before creating them.

Item	Characteristics
Merits	・`SELECT` (Search), joins, and `ORDER BY` (Sort) become dramatically faster. ・Essential for enforcing unique constraints (Primary Key/Unique).
Demerits	・DML (`INSERT`/`UPDATE`/`DELETE`) becomes slower (because the index must be rewritten simultaneously with the data). ・Consumes disk space (tablespace). ・Unnecessary indexes can cause the optimizer to make poor decisions.

Types of Indexes

Oracle has multiple indexes depending on the purpose, but first, let’s grasp the basic B-tree.

B-tree IndexThe default format. Ideal for columns with high cardinality (variety of values) such as ID, name, and date.
Bitmap IndexFor columns with few types of values, such as gender or prefecture. Unsuitable for tables with high update frequency (causes lock contention).
Function-based IndexUsed when searching with a processed column state, such as UPPER(emp_name).
Composite IndexSpeeds up searches that combine multiple columns, such as WHERE col1 = ‘A’ AND col2 = ‘B’.

Implementation: Creating, Checking, and Dropping Indexes

Here, we will perform operations for the most basic B-tree index.

Note: Comments within SQL are written in English (to exclude multi-byte characters).

1. Index Creation (CREATE INDEX)

-- Syntax: CREATE INDEX index_name ON table_name(column_name);

-- 1. Simple B-tree Index
CREATE INDEX emp_idx ON employees(emp_id);

-- 2. Composite Index (Multi-column)
CREATE INDEX emp_name_dept_idx ON employees(emp_name, dept_id);

-- 3. Function-based Index
CREATE INDEX emp_upper_idx ON employees(UPPER(emp_name));

2. Checking Indexes

Created indexes are checked in the data dictionary USER_INDEXES or USER_IND_COLUMNS.

-- Check index status and validity
SELECT index_name, table_name, status 
FROM user_indexes 
WHERE table_name = 'EMPLOYEES';

-- Check columns in the index
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;

3. Dropping Indexes (DROP INDEX)

Delete indexes if they are no longer needed or need to be recreated.

-- Drop Index
DROP INDEX emp_idx;

Execution Example: Verifying Index Effects with Execution Plans

An index is meaningless if it is “just made.” You need to verify if Oracle is actually using it. Here, we will create dummy data and confirm changes in the execution plan.

Verification Environment: Oracle Database 19c Enterprise Edition (PDB environment)

Step 1: Preparation of Test Table and Data

First, create a simple heap table without settings like a primary key, and insert 100,000 rows of data.

-- 1. Create a test table
CREATE TABLE test_index_demo (
  id     NUMBER,
  name   VARCHAR2(100),
  code   VARCHAR2(10)
);

-- 2. Insert 100,000 rows
-- Using PL/SQL loop for clear demonstration
BEGIN
  FOR i IN 1..100000 LOOP
    INSERT INTO test_index_demo 
    VALUES (i, 'User_' || i, 'CD' || MOD(i, 100));
  END LOOP;
  COMMIT;
END;
/

Step 2: How to Get Execution Plan (EXPLAIN PLAN FOR)

Use the EXPLAIN PLAN FOR command to check how the SQL will be executed (the path).

Execution Procedure:

Add EXPLAIN PLAN FOR immediately before the SQL you want to analyze and execute it (this saves the plan).
Display the saved plan using the DBMS_XPLAN.DISPLAY function.

-- 1. Generate execution plan (Does not execute the query, only plans it)
EXPLAIN PLAN FOR
SELECT * FROM test_index_demo WHERE name = 'User_99999';

-- 2. Display the plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Step 3: How to Read the Execution Plan (For Beginners)

When you execute the above, a table-formatted result like the one below is output. It looks complex at first, but there are only three points to watch.

[Example of Execution Plan Before Index Creation]

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM test_index_demo WHERE name = 'User_99999';

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 4127716390

-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |     6 |   432 |   103   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_INDEX_DEMO |     6 |   432 |   103   (1)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME"='User_99999')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

17 rows selected.

Checkpoints:

Operation: This is the most important part.
- TABLE ACCESS FULL: Full Table Scan (Reading all data without using an index). This becomes slow if the data volume is large.
- INDEX RANGE SCAN / UNIQUE SCAN: Using an index.
Rows: The number of rows Oracle predicted “might be hit by this process.”
Cost: A numerical value quantifying the weight of the process. Lower is faster (basically). Here it is 103.

Step 4: Changes After Index Creation

Now, create an index on the name column and check the plan again.

-- Create Index on 'name' column
CREATE INDEX idx_test_name ON test_index_demo(name);

-- Check Plan again
EXPLAIN PLAN FOR
SELECT * FROM test_index_demo WHERE name = 'User_99999';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

[Example of Execution Plan After Index Creation]

SQL> CREATE INDEX idx_test_name ON test_index_demo(name);

Index created.

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM test_index_demo WHERE name = 'User_99999';

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 2000019570

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |     1 |    72 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_INDEX_DEMO |     1 |    72 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_TEST_NAME   |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NAME"='User_99999')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

18 rows selected.

Points of Change:

Operation: TABLE ACCESS FULL disappeared and became INDEX RANGE SCAN. This means “an index range scan was performed to find the relevant key, and that ROWID was used to access the table.”
Name: The created index name IDX_TEST_NAME is displayed.
Cost: Dropped drastically from 103 to 2. This is proof of performance improvement.

Troubleshooting (Common Errors and Solutions)

Here are errors frequently encountered during index operations and how to deal with them.

Error Code	Error Message	Cause and Solution
ORA-01418	specified index does not exist	You tried to execute `DROP INDEX` etc., but the index name is wrong or it has already been dropped. Check `USER_INDEXES`.
ORA-00001	unique constraint (…) violated	Duplicate data exists in the column where you tried to create a unique index. Remove duplicates or change to a normal (non-unique) index.
ORA-01654	unable to extend index … by … in tablespace	The tablespace storing the index is full. Extend the data file or delete unnecessary objects.

Cases When “Index is Created But Not Used”

In the following cases, the Oracle optimizer may determine that “it is faster not to use the index (or it cannot be used).”

Search target covers a large portion of all records (e.g., 20% or more): Because a Full Table Scan requires less I/O.
Not a prefix match with LIKE operator: WHERE name LIKE '%User' (middle or suffix match) prevents the index from working.
Performing calculations on a column: e.g., WHERE UPPER(name) = 'USER_1'. In this case, a Function-based Index is required.
Statistics are stale: Update statistics with DBMS_STATS.GATHER_TABLE_STATS.

Operational and Security Precautions

Index Fragmentation and Rebuild

If INSERT / DELETE operations are repeated frequently, the interior of the B-tree index becomes sparse (fragmentation), leading to performance degradation. Consider “Rebuild” periodically, but be aware that it involves locking.

-- Rebuild index (Caution: Locks the table unless ONLINE is specified)
ALTER INDEX idx_test_name REBUILD ONLINE;

Note: By adding the ONLINE keyword, you can rebuild without stopping references/updates (Enterprise Edition feature).

Invisible Index

When dropping an index, if you are afraid to DROP it suddenly, it is recommended to make it “Invisible” to check the impact.

-- Make index invisible to optimizer
ALTER INDEX idx_test_name INVISIBLE;

FAQ: Frequently Asked Questions

Q1. How many indexes can I create on one table?

A. There is no clear upper limit, but it is generally recommended to aim for about 3 to 5. Too many will significantly degrade INSERT/UPDATE performance. Select only what is necessary.

Q2. Is it necessary to create an index on the Primary Key?

A. It is not necessary. When you define a primary key constraint, Oracle automatically creates a unique index (or an existing index may be reused).

Q3. How long does it take to create an index?

A. It depends on the data volume and server performance. If there are millions of rows, it may take several minutes to tens of minutes. When performing this in a production environment, it is recommended to do it during night hours when there are few users, or use the ONLINE option.

Summary

In Oracle performance tuning, indexes are the most effective and fundamental means.

Mechanism: Like a book’s table of contents, it accesses data at high speed using ROWID.
Basic: Usually, use B-tree Indexes.
Creation: Create with CREATE INDEX and delete with DROP INDEX.
Verification: Always obtain an EXPLAIN PLAN and confirm that the Operation changed from TABLE ACCESS FULL (Full Table Scan) to INDEX RANGE SCAN, etc.
Caution: Creating too many lowers update performance, so narrow it down to necessary columns.

Start by using EXPLAIN PLAN FOR in your development environment to check if your SQL is using indexes as intended.

This article explains concepts targeting Oracle Database 19c (screens and default values may differ in other versions).

[reference]
CREATE INDEX