Oracle SQL Certification 1Z0-071 Course | Section 15: Managing Indexes

In this section, you will learn how indexes work, how they are created automatically and manually, and how they influence query performance.

Indexes are one of the most important tools for improving database performance. They help the Oracle database engine locate rows much faster when executing queries.

Without indexes, the database often has to perform a full table scan, reading every row to find the data it needs. With indexes, Oracle can quickly navigate to the correct rows, similar to how an index page in a book helps you find topics quickly.

Lesson 1: Indexes – Overview

An index is a separate database object that stores references to rows in a table, allowing Oracle to retrieve data more efficiently.

Indexes are linked to specific columns in a table and act as a fast lookup mechanism for queries.

Why Indexes Are Important

Indexes help the database:

  • Retrieve rows faster
  • Reduce the need for full table scans
  • Improve performance of SELECT queries

However, indexes come with a trade-off.

Because indexes must be updated whenever data changes, they can slow down:

  • INSERT operations
  • UPDATE operations
  • DELETE operations

This means indexes improve query performance, but they add overhead to data modification operations.

Indexes as Separate Objects

Indexes are stored separately from the table they reference. They contain:

  • Indexed column values
  • Pointers to the corresponding table rows

This structure allows Oracle to jump directly to the relevant rows instead of scanning the entire table.

Implicit vs Explicit Indexes

There are two main ways indexes are created.

Implicit indexes

Created automatically by Oracle when certain constraints are defined.

Explicit indexes

Created manually by developers or database administrators using SQL statements.

When the Optimizer Uses Indexes

Oracle has a query optimizer that decides whether using an index will improve performance.

Even if an index exists, Oracle may still choose a full table scan if it believes it will be faster.

Understanding this behavior is important when designing efficient queries.

Lesson 2: Implicit Index Creation

Oracle automatically creates indexes when certain constraints are defined on a table.

This process is known as implicit index creation.

Primary Key Indexes

When you create a PRIMARY KEY constraint, Oracle automatically generates a unique index to enforce it.

Example:

ALTER TABLE employees
ADD CONSTRAINT emp_pk PRIMARY KEY (employee_id);

Oracle automatically creates an index for employee_id.

Unique Constraint Indexes

The same happens when you define a UNIQUE constraint.

Example:

ALTER TABLE employees
ADD CONSTRAINT emp_email_uk UNIQUE (email);

Oracle creates an index to guarantee that the column contains only unique values.

Checking Created Indexes

Oracle provides several data dictionary views to inspect indexes.

Useful views include:

USER_INDEXES

Shows information about indexes owned by the user.

USER_IND_COLUMNS

Displays which columns belong to each index.

USER_CONSTRAINTS

Shows table constraints and their relationship to indexes.

These views allow you to see what the database has created automatically.

Lesson 3: Single Column Index

A single column index is the simplest type of manually created index.

It indexes only one column in a table and helps speed up queries that filter or search using that column.

Creating an Index

Example:

CREATE INDEX emp_salary_idx
ON employees(salary);

Naming convention typically includes:

  • table name
  • column name
  • _idx suffix
Dropping an Index

If an index is no longer needed, it can be removed.

DROP INDEX emp_salary_idx;
When the Optimizer Uses an Index

Oracle decides whether to use an index based on the query conditions.

Indexes are commonly used with:

  • equality comparisons (=)
  • range conditions (>, <, BETWEEN)
  • prefix searches with LIKE

Indexes are often not used when:

  • functions are applied to indexed columns
  • NOT EQUAL conditions are used
  • a large percentage of rows must be retrieved
Rule of Five

A common best practice suggests limiting indexes to about five per table.

Too many indexes can slow down data modification operations significantly.

Lesson 4: Composite and Unique Indexes

More advanced indexing techniques involve unique indexes and composite indexes.

Unique Index

A unique index ensures that all values in a column are distinct.

Example:

CREATE UNIQUE INDEX emp_email_idx
ON employees(email);

If a duplicate value is inserted, Oracle raises an error.

Composite Index

A composite index indexes multiple columns together.

Example:

CREATE INDEX emp_name_dept_idx
ON employees(last_name, department_id);

This type of index is useful when queries frequently filter using multiple columns.

Column Order Matters

In composite indexes, the order of columns is critical.

Example:

(last_name, department_id)

Oracle can efficiently use the index when queries include:

  • last_name
  • last_name and department_id

But the index may not be used if only department_id is referenced.

The first column in the index has the highest priority.

Lesson 5: Visible and Invisible Indexes

Oracle allows indexes to be marked as visible or invisible.

An invisible index still exists in the database but is ignored by the query optimizer.

Why Invisible Indexes Are Useful

Invisible indexes are commonly used for:

  • performance testing
  • troubleshooting query plans
  • safely evaluating whether an index is needed

Instead of dropping an index, you can temporarily hide it from the optimizer.

Creating an Invisible Index

Example:

CREATE INDEX emp_salary_idx
ON employees(salary)
INVISIBLE;
Changing Visibility

You can toggle visibility using:

ALTER INDEX emp_salary_idx VISIBLE;

or

ALTER INDEX emp_salary_idx INVISIBLE;
Checking Index Visibility

Index visibility can be verified using Oracle data dictionary views such as USER_INDEXES.

This allows administrators to monitor how indexes are configured and used.

Final Thoughts

Indexes are one of the most powerful tools for improving database performance. When used correctly, they can dramatically speed up queries and reduce database workload.

However, they must be designed carefully. Too many indexes can slow down data modifications and increase maintenance overhead.

Understanding how Oracle creates, manages, and uses indexes will help you build faster queries and more efficient database systems, which is essential both for the Oracle 1Z0-071 certification exam and for real-world database development.