Oracle Index Management

What is an Index in Oracle?

An index in Oracle is a performance tuning structure that improves the speed of data retrieval on a table. It works like a lookup—instead of scanning every row, Oracle uses the index to find data faster.

Why Use Indexes?

• Speed up SELECT, JOIN, and WHERE clauses.
• Help enforce uniqueness with unique indexes.
• Reduce I/O for large datasets.

Think of it like an index in a book—jump directly to what you need.

Real-Time Example

-- Without index: full table scan
SELECT * FROM employees WHERE email = 'HR@EXAMPLE.COM';

-- Create index to speed it up
CREATE INDEX emp_email_idx ON employees(email);

Types of Indexes (Just Names Here; Explained Later)

Oracle provides multiple index types to optimize different query patterns. The right index improves performance by reducing I/O and speeding up data access.

Why So Many Types?

Each index type is designed for specific data characteristics or workload patterns.
Knowing the difference helps you create faster and leaner systems.

Common Index Types and Use Cases

Quick Tips

• Use B-tree for most OLTP cases.
• Choose Bitmap for reporting and static data.
• Go Function-based when filters use expressions.
• Make indexes Invisible to test without dropping.
• Compress large indexes to save space.

Guidelines for Managing Indexes in Oracle

Efficient indexing improves query performance, reduces I/O, and optimizes storage. These best practices will help you maintain effective, high-performance indexes.

1. Index Creation Strategy

Create Indexes After Inserting Table Data

Creating indexes after bulk inserts improves performance and avoids unnecessary index maintenance during data load.

-- Load data first
INSERT INTO employees ...

-- Then create index
CREATE INDEX emp_name_idx ON employees(last_name);

Consider Parallel and NOLOGGING Options

Speed up index creation for large tables.

CREATE INDEX emp_idx ON employees(email) PARALLEL 4 NOLOGGING;

2. Choose the Right Columns to Index

Index Only Frequently Queried Columns

Focus on columns used in WHERE, JOIN, or ORDER BY clauses.

Don’t index:

• Columns with few distinct values (e.g., YES/NO)
• Columns rarely used in queries

Order Columns Smartly in Composite Indexes

Put the most selective column first in multi-column indexes.

CREATE INDEX emp_comp_idx ON employees(department_id, job_id);

3. Manage Index Overhead

Limit the Number of Indexes Per Table

Too many indexes = slow DML (INSERT/UPDATE/DELETE).
Keep only what is necessary for performance.

Drop Unused Indexes

Use index usage monitoring to decide what to drop.

-- Start monitoring
ALTER INDEX emp_idx MONITORING USAGE;

-- Check usage
SELECT * FROM V$OBJECT_USAGE WHERE INDEX_NAME = 'EMP_IDX';

-- Drop if unused
DROP INDEX emp_idx;

4. Storage and Tablespace Planning

Specify Tablespace for Indexes

Separate index I/O and improve manageability.

CREATE INDEX emp_idx ON employees(email) TABLESPACE idx_tbs;

Estimate Index Size

Helps plan storage needs and avoid surprises.

-- Use DBMS_SPACE to estimate
SELECT * FROM TABLE(DBMS_SPACE.CREATE_INDEX_COST(...));

5. Special Index Features

Use Unusable Indexes for Large Loads

Mark index unusable to skip maintenance during loads.

ALTER INDEX emp_idx UNUSABLE;

Rebuild after data load:

ALTER INDEX emp_idx REBUILD;

Use Invisible Indexes for Safe Testing

Invisible indexes don’t affect execution plans unless hinted.

CREATE INDEX emp_test_idx ON employees(email) INVISIBLE;

Avoid Duplicate Indexes

Oracle 12.2+ allows multiple indexes on same columns (e.g., B-tree & bitmap), but avoid unless for controlled testing.

6. Index Maintenance and Optimization

Coalesce or Rebuild Fragmented Indexes

• Coalesce: Lightweight; merges leaf blocks.
• Rebuild: Recreates the index from scratch.

ALTER INDEX emp_idx COALESCE;
ALTER INDEX emp_idx REBUILD;

7. Be Careful with Constraints

⚠️ Dropping Constraints Also Drops Indexes

If you drop a unique or primary key constraint, the index is also dropped unless it’s user-defined.

ALTER TABLE employees DROP CONSTRAINT emp_email_uk;
-- Unique index on 'email' may be gone

8. Advanced Considerations

Leverage Deferred Segment Creation

Index segment is not created until data is inserted—saves space for empty tables.

SELECT SEGMENT_CREATED FROM USER_INDEXES WHERE INDEX_NAME = 'EMP_IDX';

Reduce Indexes with In-Memory Column Store

Oracle Database In-Memory eliminates the need for many indexes for analytics.

ALTER TABLE employees INMEMORY;

Summary Table

Creating Indexes in Oracle

Indexes boost performance by allowing Oracle to find rows faster. Here’s how to create different types of indexes efficiently, with the right options and use cases.

✅ Prerequisites for Creating Indexes

You must have:

• CREATE INDEX privilege
• Enough tablespace (if specified)
• Valid column data types (not LOB directly)

Create an Index Explicitly

Creates a non-unique index.

CREATE INDEX emp_dept_idx ON employees(department_id);

Create a Unique Index

Ensures indexed column(s) contain unique values.

CREATE UNIQUE INDEX emp_email_uk_idx ON employees(email);

🔹 Oracle automatically creates these if a UNIQUE constraint is defined.

Index with a Constraint

Oracle auto-creates index with PRIMARY KEY or UNIQUE constraints.

ALTER TABLE employees ADD CONSTRAINT emp_pk PRIMARY KEY (employee_id);
-- Implicitly creates a unique index

Create a Large Index

Use PARALLEL and NOLOGGING to speed up creation and reduce redo.

CREATE INDEX big_sales_idx ON sales(transaction_date) PARALLEL 4 NOLOGGING TABLESPACE idx_tbs;

Create Index Online

Avoid table locking—ideal for production.

CREATE INDEX emp_job_idx ON employees(job_id) ONLINE;

✅ Allows concurrent DML during creation.

Function-Based Index

Pre-computes values of expressions or functions.

CREATE INDEX emp_upper_name_idx ON employees(UPPER(last_name));

🔸 Enables efficient search on transformed values.

Compressed Index

Compresses repeated column values to save space.

CREATE INDEX emp_multi_col_idx ON employees(department_id, job_id) COMPRESS 1;

• COMPRESS 1 compresses all but the last column.

Create an Unusable Index

Created but not available for use until rebuilt. Useful for deferred builds.

CREATE INDEX emp_temp_idx ON employees(temp_col) UNUSABLE;
-- Later: ALTER INDEX emp_temp_idx REBUILD;

Create an Invisible Index

Invisible to optimizer unless hinted—great for testing.

CREATE INDEX emp_test_idx ON employees(email) INVISIBLE;

Multiple Indexes on Same Columns

Since Oracle 12.2, allowed in special cases (e.g., different index types).

-- One B-tree, one bitmap (if allowed on same columns)
CREATE INDEX emp_btree_idx ON employees(email);
CREATE BITMAP INDEX emp_bmap_idx ON employees(email);

❗ Use sparingly—Oracle chooses only one during optimization.

Summary Table

Altering Indexes in Oracle

Indexes aren’t “set and forget”—you may need to rebuild, rename, hide, monitor, or drop them as data and access patterns evolve.

About Altering Indexes

Index changes help optimize performance, reclaim space, or control usage. Oracle allows many runtime adjustments.

Altering Storage of an Index

Change tablespace, storage, or parallel settings:

ALTER INDEX emp_idx REBUILD TABLESPACE fast_idx PARALLEL 2;

Rebuilding an Index

Fix fragmentation or make an unusable index usable.

ALTER INDEX emp_idx REBUILD;
ALTER INDEX emp_idx REBUILD ONLINE;

Make an Index Unusable

Disable without dropping. Good for bulk operations.

ALTER INDEX emp_idx UNUSABLE;
-- Rebuild later when needed

Make Index Invisible/Visible

Invisible indexes are ignored by the optimizer (unless hinted).

ALTER INDEX emp_idx INVISIBLE;
ALTER INDEX emp_idx VISIBLE;

Rename an Index

Change index name without recreating.

ALTER INDEX emp_idx RENAME TO emp_name_idx;

Monitor Index Usage

Track if an index is used in queries.

ALTER INDEX emp_idx MONITORING USAGE;
SELECT * FROM V$OBJECT_USAGE;

Monitor Space Use

Check index size and bloat:

SELECT SEGMENT_NAME, BYTES/1024/1024 AS MB
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'INDEX' AND SEGMENT_NAME = 'EMP_IDX';

Drop an Index

Remove when no longer needed.

DROP INDEX emp_idx;

⚠ If it’s part of a PRIMARY KEY or UNIQUE constraint, drop the constraint first.

Automatic Indexing (Oracle 19c+)

Oracle can auto-create, monitor, and drop indexes using internal intelligence. It’s optional but powerful.

About Automatic Indexing

• Runs in the background (Autonomous or manually enabled)
• Learns query patterns
• Only keeps beneficial indexes

How It Works

1. Creates indexes in INVISIBLE mode
2. Tests performance
3. Promotes or drops based on benefit

Configure Automatic Indexing

Enable or disable:

-- Enable
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

-- Disable
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

Generate Auto Index Reports

Check what Oracle’s doing:

SELECT DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY FROM DUAL;
SELECT DBMS_AUTO_INDEX.REPORT_ACTIVITY(SYSDATE-1, SYSDATE) FROM DUAL;

Views for Auto Index Info

Recap: What You Can Do

Oracle Indexes Data Dictionary Views

Sample Queries:

-- List indexes on a table
SELECT index_name, index_type FROM USER_INDEXES WHERE table_name = 'EMPLOYEES';

-- Show indexed columns
SELECT index_name, column_name FROM USER_IND_COLUMNS WHERE table_name = 'EMPLOYEES';

-- See if an index was used (after enabling monitoring)
SELECT * FROM USER_OBJECT_USAGE WHERE index_name = 'EMP_EMAIL_IDX';