Oracle SQL Index Basics and Creation Steps | Optimization and Tuning

Search is slow. The overall system response is poor. In such cases, the first thing to suspect is the “Index” configuration. This article explains the mechanism of Oracle Indexes, appropriate creation criteria, and practical verification steps directly linked to performance tuning. Let’s take the first step toward SQL optimization with steps that even beginners can implement without getting lost.

1. Conclusion: The Shortest Path (To-Do List)

First, here is the shortest route to creating an index and speeding up searches.

Select Target Columns: Choose columns frequently used in WHERE clauses or JOIN conditions, and those with high “variation of values (Cardinality).”
Check Status: Confirm if an index already exists.
Create Index: Execute the CREATE INDEX statement.
Verify Effect: Confirm improvement via the execution plan or processing time.

2. What is an Index? Mechanism and Benefits

Image of an Index

An index is like the “index at the back of a book.”

If there is no index, the database performs a “Full Table Scan,” turning every page (row) from the beginning to the end of the book to search. In contrast, if there is an index, it can instantly identify “Keyword → Page Number (ROWID)” and access the target data directly.

Merits and Demerits of Indexes

Feature	Merit	Demerit
Search (SELECT)	It allows pinpoint access to the target row, dramatically speeding up processing.	(None particular)
Update (DML)	(None particular)	Whenever data is added, updated, or deleted, the index must also be rewritten, increasing processing load (overhead).
Capacity	(None particular)	The index data itself consumes disk space.

Point: “Creating indexes on all columns just in case” is bad practice. Since it leads to slower update speeds, it is important to design indexes specifically for necessary columns.

3. Types of Indexes and Usage (Oracle Database)

Oracle has multiple index types, but the standard is the B-Tree Index.

1. B-Tree Index (Default)

Usage: Most common. For columns with high variation in values, such as ID, name, and dates.
Feature: It has a tree structure, keeping search complexity stable even as data volume increases.
SQL Example: CREATE INDEX idx_emp_id ON employees(emp_id);

2. Bitmap Index

Usage: For columns with few value types (Cardinality), such as “Gender (Male/Female)” or “Prefecture.” Used mainly in Enterprise Edition DWH environments.
Feature: Since data update (lock) contention is likely to occur, it is unsuitable for OLTP systems that are frequently updated.

3. Function-Based Index

Usage: When searching by results passed through a function, such as UPPER(name) or calculation formulas.
Feature: The search condition and the index definition must match.

4. Composite Index

Usage: When always searching by combining multiple columns, such as WHERE col1 = 'A' AND col2 = 'B'.
Note: The order of columns is important (place the column frequently used for filtering at the beginning).

4. Which Columns Should Be Created? (Selection Criteria)

The key to performance tuning is selecting “effective columns.”

Columns used for “Equality Conditions (=)” or “Range Conditions (BETWEEN, > <)” in WHERE clauses Columns that can significantly narrow down the search target are optimal.
Columns serving as keys for JOINs This speeds up access when joining tables.
Columns with High Cardinality (Variety of Values)
- Example: Employee ID, Email address (values close to unique).
- Reverse Example: Gender, Flags (Creating a B-Tree index on a column with only “0 or 1” has little effect).
Caution for Columns with many NULLs Standard Oracle B-Tree indexes do not include rows where all key columns are NULL. IS NULL searches require special consideration.

5. Practice: Index Creation and Deletion Steps

From here, we present steps executable on an actual machine (SQL*Plus, SQL Developer, etc.).

Prerequisite Confirmation

CREATE ANY INDEX privilege for the target schema, or be the owner of the table.
The table must not be locked.

Step 1: Check existing indexes

To prevent duplicate creation, first check the current status.

-- Check existing indexes for a specific table
SELECT index_name, index_type, uniqueness, status
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

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

Step 2: Create the index

Be careful not to use Japanese (multi-byte characters) within the SQL.

-- Basic B-Tree Index
CREATE INDEX idx_emp_name ON employees(name);

-- Composite Index (Order matters)
CREATE INDEX idx_dept_sal ON employees(department_id, salary);

-- Function-Based Index (e.g., for case-insensitive search)
CREATE INDEX idx_upper_name ON employees(UPPER(name));

Step 3: Delete the index

Delete indexes that are no longer needed to reduce the update load.

-- Drop Index
DROP INDEX idx_emp_name;

6. Execution Example: Effectiveness Verification Demo

Let’s actually insert 100,000 records and experience the speed difference with and without an index. Note: Execution in a test environment is recommended.

Step 1: Preparation of Test Table and Data

-- 1. Create a table for testing
CREATE TABLE demo_employees (
    emp_id NUMBER,
    name VARCHAR2(100),
    dept_id NUMBER,
    salary NUMBER
);

-- 2. Insert 100,000 rows (PL/SQL loop)
BEGIN
    FOR i IN 1..100000 LOOP
        INSERT INTO demo_employees 
        VALUES (i, 'User' || TO_CHAR(i), MOD(i, 10), i * 10);
    END LOOP;
    COMMIT;
END;
/

Step 2: Search without Index

Measure the execution time.

-- Enable timing
SET TIMING ON;

-- Search without index (Full Table Scan)
SELECT * FROM demo_employees WHERE name = 'User50000';

Estimated Result: Depending on data volume, it takes from 0.1 seconds to several seconds, reading the entire table.

Step 3: Index Creation and Re-search

-- Create Index on 'name' column
CREATE INDEX idx_demo_name ON demo_employees(name);

-- Search with index (Index Range Scan or Unique Scan)
SELECT * FROM demo_employees WHERE name = 'User50000';

Estimated Result: Less than 0.01 seconds (a few milliseconds). It becomes dramatically faster.

7. Troubleshooting (Common Errors)

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

Error Code	Error Content	Cause and Solution
ORA-00955	name is already used by an existing object	An index or object with the same name already exists. Change the name or check the existing one.
ORA-01418	specified index does not exist	The name is incorrect during `DROP`, or you are trying to delete an index in another schema.
ORA-00001	unique constraint (…) violated	When executing `CREATE UNIQUE INDEX`, duplicate data already exists in the table. Resolve the duplication or use a non-unique index.
ORA-01654	unable to extend index … by … in tablespace	Disk space is insufficient. Request the DBA to extend the tablespace.

8. Operational and Monitoring Notes

Utilizing “Invisible Index”

In Oracle 11g and later (including 19c), there is a function to “temporarily disable (make invisible to the optimizer)” an index without deleting it. This is useful when checking “Will performance be affected if I delete this index?”

-- Make index invisible (Optimizer ignores it)
ALTER INDEX idx_demo_name INVISIBLE;

-- Make index visible again
ALTER INDEX idx_demo_name VISIBLE;

Updating Statistics

Even if you create an index, Oracle may not use it if the statistics are old. Although they are automatically collected immediately after creation, consider manual collection after replacing large amounts of data.

9. FAQ: Frequently Asked Questions

Q1. Is it better to have as many indexes as possible? A. No, it can be counterproductive. While referencing (SELECT) becomes faster, index maintenance occurs every time there is a registration, update, or deletion (INSERT/UPDATE/DELETE), degrading write performance. Unnecessary indexes should be deleted periodically.

Q2. I created an index, but it is not being used. Why? A. The main causes are as follows:

Low Cardinality: If more than 5-15% of the total rows are hit, Oracle may judge that a full table scan is faster than using an index.
Column Manipulation: If you use a function on a column like WHERE UPPER(name) = 'A', a normal index cannot be used (a function-based index is required).
Old Statistics: The optimizer may be making decisions based on outdated information.

Q3. Is an index necessary for the Primary Key? A. It is created automatically. When you define a Primary Key constraint or a Unique constraint, Oracle automatically creates a unique index. There is no need to create it separately.

10. Summary

To maintain the performance of an Oracle Database, appropriate index design is essential.

Identify columns that become Search Conditions or Join Conditions.
Select columns with high Cardinality (value variation).
Consider the balance between Creation/Update Costs (Merits vs. Demerits).
Check if it is actually being used in the Execution Plan.

First, create an index in a verification environment and experience the dramatic speed improvement.

[reference]
Oracle Database SQL Language Reference, 19c