Complete Guide to Oracle Indexes: Structure, Types, Creation, and Usage Verification Explained with Diagrams

Indexes are essential in Oracle Database to speed up data retrieval. This article provides a comprehensive and visual explanation of indexes, including how they work, the different types, how to create them, and how to check if they are being used.

What is an Index?
Benefits of Indexes
Types of Indexes in Oracle
How to Create an Index
How to Check if an Index is Being Used
Sample Table and Index Creation (for testing)
When Not to Use Indexes
Summary

What is an Index?

An index is a database object that helps Oracle locate rows faster. Think of it like an index in a book — instead of scanning every page, you can quickly jump to the section you need.

┌────────────┐
│ 　　　 EMP Table 　　　│
├──────┬─────┤
│ 　EMPNO　　│　NAME　  │
├──────┼─────┤
│ 　 7369　　│　SMITH　 │
│  　7499　　│　ALLEN　 │
│  　7521　　│　WARD　  │
└──────┴─────┘

┌───────────────┐
│ 　　　Index on EMPNO　　　　 │
├──────┬────────┤
│　EMPNO　　 │　　　ROWID　   │
├──────┼────────┤
│ 　7369　　 │　　　AAABBB　  │
│ 　7499　　 │　　　AAACCC 　 │
└──────┴────────┘

With the index on EMPNO, Oracle can directly find the ROWID for a specific employee number without full table scan.

Benefits of Indexes

Improves query performance
Avoids full table scans
Supports quick filtering and sorting
Enables efficient JOIN operations

Types of Indexes in Oracle

Type	Description
B-tree Index	Default type, good for equality and range searches
Bitmap Index	Efficient for columns with low cardinality
Function-based	Indexes based on functions (e.g., UPPER(name))
Composite Index	Multi-column index
Unique Index	Ensures column(s) have unique values

How to Create an Index

Example: Create a simple index on EMPNO

CREATE INDEX emp_empno_idx ON emp(empno);

Create a unique index

CREATE UNIQUE INDEX emp_email_uk ON emp(email);

Create a composite index

CREATE INDEX emp_name_idx ON emp(last_name, first_name);

How to Check if an Index is Being Used

1. Enable SQL Trace (autotrace)

SET AUTOTRACE ON
SELECT * FROM emp WHERE empno = 7369;

→ Check that the execution plan shows INDEX access.

2. Use EXPLAIN PLAN

EXPLAIN PLAN FOR
SELECT * FROM emp WHERE empno = 7369;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

3. Use V$SQL_PLAN

SELECT sql_id, operation, options, object_name
FROM v$sql_plan
WHERE object_name = 'EMP_EMPNO_IDX';

Sample Table and Index Creation (for testing)

CREATE TABLE emp (
  empno NUMBER PRIMARY KEY,
  ename VARCHAR2(30),
  job   VARCHAR2(30),
  deptno NUMBER
);

INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 30);
COMMIT;

CREATE INDEX emp_job_idx ON emp(job);

When Not to Use Indexes

On columns with high update/delete frequency (maintenance cost)
On columns with very low selectivity (e.g., gender)
For small tables (full scan may be faster)

Summary

Indexes enhance query performance by avoiding full table scans
Multiple types exist to suit different access patterns
Use AUTOTRACE, EXPLAIN PLAN, or V$SQL_PLAN to confirm usage
Be mindful of maintenance overhead and proper design

[reference]
CREATE INDEX