SQL Explain Plan in Oracle

 

The SQL Explain Plan has been confusing for many Oracle DBAs. I would like to explain it in a simple way as follows.

Whenever we write an SQL query—especially a SELECT statement—the database must determine the most efficient way to retrieve the requested data. To achieve this, the database relies on what is known as a SQL Execution Plan.

An execution plan acts like a navigation map for the database engine. Similar to how applications such as Google Maps determine the most efficient route to reach a destination, the database optimizer evaluates multiple possible paths and chooses the most optimal way to execute the query. This chosen path is referred to as the query execution plan.

However, just like a navigation system performs better with well-structured roads, a database performs better when its objects are properly designed and organized. Database developers and administrators can help the optimizer choose better execution plans by designing efficient table structures, indexes, and statistics. Proper database design significantly improves how easily the optimizer can traverse data and retrieve results.

As the title suggests, this discussion focuses on Query Execution Plans in the Oracle Database. Over the years, Oracle has evolved significantly, and modern versions of the database include a highly advanced Cost-Based Optimizer (CBO) capable of automatically selecting efficient execution plans in most situations.

That said, understanding execution plans remains an essential skill for SQL developers and DBAs. Even though the optimizer is powerful, professionals must still analyze query plans when diagnosing performance issues or tuning SQL statements.

In the following sections, we will explore some of the key metrics and components of an Oracle SQL execution plan to better understand how the database processes a query.

NOTE:
Below is a simple SQL example used to explain some of the key terms that help us understand how an SQL execution plan is generated.

SELECT * FROM dept WHERE department_id = 20;

In this query, the condition department_id = 20 is referred to as a predicate. A predicate is an expression within the WHERE clause that specifies the condition used to filter rows from a table.

In this case, the predicate tells the database that we are interested only in rows where the value of department_id equals 20.

Because the predicate uses the equality operator (=), it is known as an Equality Predicate. Equality predicates are typically very efficient because the database optimizer can easily use an index on the column (if one exists) to quickly locate the matching rows.

In simple terms, the predicate defines the filtering condition that the database uses to determine which data should be returned by the query.

What is SQL execution plan?

I would say Oracle SQL execution plan is a map to traverse through different datasets like Tables and Indexes. At a high level, like an ordinary Map, this will have different routes (Table Access), logistics(different Join Type) and the travel expense (Query Cost).

How do we get execution plan of a SQL?

Most common ways of generating query plan are using EXPLAN_PLAN command and DBMS_XPLAN.DISPLAY function.

Using EXPLAN_PLAN command :-

EXPLAIN_PLAN

Using DBMS_XPLAN.DISPLAY function :-

What’s there in execution plan?

In general, we have SQL execution plan which is highly categorized into Id, Operation, Object Name, Cost and Cardinality. For better understanding, I have tried to categorize them like below.

1. Cost
2. Cardinality
3. Table Access
4. Join Type

Now let’s discuss them in detail.

1. Cost : Oracle Optimizer is a cost-based optimizer. When we fire a SQL to Oracle DB, it takes out multiple query execution plans, and the one out of many is selected by the Optimizer and used as a path to fetch the data. In same the context, cost is calculative representation of how much CPU, IO and network resources are used for Optimizer to run different operations to get the data.

Cost is the estimated amount of work the plan will do.

Please refer below image.

The cost of the entire plan (indicated on line 0) and each individual operation is displayed in the execution plan.

Optimizer always chooses the plan with lowest cost. The lower the cost the more efficient the plan is expected to be.

2. Cardinality : Cardinality is rough estimate of number of rows returned per operation. This gets defines by different aspects like Dynamic Sampling, Data Distribution in the joining column.

A higher cardinality =>you’re going to fetch more rows => you’re going to do more work => the query will take longer. Thus the cost is (usually) higher.

3. Table Access:

The access method — or access path — shows how the data will be accessed from each table (or index). The access method is shown in the operation field of the explain plan. Please refer below.

Oracle supports below table access

Full Table Scans : Reads all rows from a table and filters out those that do not meet the where clause predicates. Mind you, this is expensive scan. Full Table Scans will be used when

• No index exists.
• The query predicate applies a function to the indexed column.
• A SELECT COUNT(*) query is issued, and an index exists, but the indexed column contains nulls.
• The query predicate does not use the leading edge of a B-tree index.
• The table statistics are stale.
• The table is small.
• The table has a high degree of parallelism.
• The query uses a full table scan hint.

A Full Table Scan occurs when the database reads all rows of a table and filters them based on the WHERE clause predicates.

This is generally an expensive operation because every block of the table must be read.

When the Optimizer Chooses Full Table Scan

• No index exists on the predicate column.

• The predicate uses a function on an indexed column.

• A query such as SELECT COUNT(*) is executed and the indexed column contains NULL values.

• The predicate does not use the leading column of a B-tree index.

• Table statistics are stale or inaccurate.

• The table is very small.

• Parallel query is enabled.

• A full table scan hint is used.

Example Query

SELECT * FROM departments WHERE location_id = 1700;

Explain Plan Example

--------------------------------------------------------------------------------
| Id | Operation          | Name        | Rows | Cost |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |             |   10 |  12  |
|  1 |  TABLE ACCESS FULL | DEPARTMENTS |   10 |  12  |
--------------------------------------------------------------------------------

 

Table access by ROWID : The rowid of a row specifies the data file and data block containing the row and the location of the row in that block. Locating a row by specifying its rowid is the fastest way to retrieve a single row because it specifies the exact location of the row in the database. When the Optimizer Chooses Table Access by Rowid

In most cases, the database accesses a table by rowid after a scan of one or more indexes.

Table Access by ROWID

Every row in Oracle has a unique ROWID, which specifies:

• Datafile

• Block

• Row location

Fetching rows using ROWID is the fastest method because the database knows the exact physical location of the row.

Usually this occurs after an index scan.

Example Query

SELECT * FROM departments WHERE department_id = 20;

Explain Plan Example

--------------------------------------------------------------------------------
| Id | Operation                      | Name        |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |             |
|  1 |  TABLE ACCESS BY INDEX ROWID   | DEPARTMENTS |
|  2 |   INDEX UNIQUE SCAN            | DEPT_PK     |

-------------------------------------------------------------------------------- 

Index Unique Scan : An Index Unique Scan returns at most 1 rowid. When the Optimizer Chooses Index Unique Scan

It will be used when there is an equality predicate on a unique (B-tree) index or an index created as a result of a primary key constraint.

 

An Index Unique Scan retrieves at most one ROWID.

It occurs when the query uses:

• Primary Key

• Unique Index

• Equality predicate (=)

Example Query

SELECT * FROM departments WHERE department_id = 10;

Explain Plan

--------------------------------------------------------------------------------
| Id | Operation          | Name    |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |         |
|  1 |  INDEX UNIQUE SCAN | DEPT_PK |
--------------------------------------------------------------------------------

Index Range Scan : An Index Range Scan is an ordered scan of values. The optimizer typically chooses a range scan for selective queries.

By default, the database stores indexes in ascending order, and scans them in the same order. For example, a query

select * from department where department_id >= 20

Above with the predicate department_id >= 20 uses a range scan to return rows sorted by index keys 20, 30, 40, and so on. If multiple index entries have identical keys, then the database returns them in ascending order by rowid, so that 0,AAAPvCAAFAAAAFaAAa is followed by 0,AAAPvCAAFAAAAFaAAg, and so on.

An index range scan descending is identical to an index range scan except that the database returns rows in descending order. Usually, the database uses a descending scan when ordering data in a descending order, or when seeking a value less than a specified value.

When the optimizer chooses Index Range Scan

One or more leading columns of an index are specified in conditions.
A condition specifies a combination of one or more expressions and logical (Boolean) operators and returns a value of TRUE, FALSE, or UNKNOWN. Examples of conditions include:
department_id = :id
department_id < :id
department_id > :id
AND combination of the preceding conditions for leading columns in the index, such as department_id > :low AND department_id < :hi.
0, 1, or more values are possible for an index key.
NOTE: For the optimizer to consider a range scan, wild-card searches of the form col1 LIKE '%ASD' must not be in a leading position.

Index Range Scan

An Index Range Scan occurs when Oracle retrieves multiple index entries within a range.

This is commonly used when the predicate uses:

• >

• <

• BETWEEN

• LIKE 'ABC%'

Example Query

SELECT *
FROM departments
WHERE department_id >= 20;

Explain Plan

--------------------------------------------------------------------------------
| Id | Operation          | Name        |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |             |
|  1 |  INDEX RANGE SCAN  | DEPT_IDX    |
--------------------------------------------------------------------------------

Example Conditions

department_id = :id
department_id < :id
department_id > :id
department_id BETWEEN :low AND :high

⚠️ If the predicate uses:

LIKE '%ABC'

Oracle cannot use the index efficiently because the leading column is not specified. 

Index Full Scans : A full index scan does not read every block in the index structure, contrary to what its name suggests. An index full scan processes all of the leaf blocks of an index, but only enough of the branch blocks to find the first leaf block. It is used when all of the columns necessary to satisfy the statement are in the index and it is cheaper than scanning the table. It uses single block IOs. It may be used in any of the following situations.

A predicate references a column in the index. This column need not be the leading column.
No predicate is specified, but all of the following conditions are met:
All columns in the table and in the query are in the index.
At least one indexed column is not null.

A query includes an ORDER BY on indexed non-nullable columns.

Index Full Scan

An Index Full Scan reads the entire index in sorted order.

Unlike a Full Table Scan, Oracle reads only index blocks, not table blocks.

Used When

• Query columns are fully covered by the index

• Oracle wants sorted results

• Predicate references indexed columns

Example Query

SELECT department_id FROM departments ORDER BY department_id;

Explain Plan

--------------------------------------------------------------------------------
| Id | Operation          | Name        |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |             |
|  1 |  INDEX FULL SCAN   | DEPT_IDX    |

--------------------------------------------------------------------------------

 

Index Fast Full Scans : An Index Fast Full Scans reads the index blocks in unsorted order, as they exist on disk. This scan does not use the index to probe the table, but reads the index instead of the table, essentially using the index itself as a table.

When Optimizer chooses Index Fast Full Scan

This is an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. It cannot be used to eliminate a sort operation, because the data access does not follow the index key. It will also read all of the blocks in the index using multiblock reads, unlike a full index scan

Index Fast Full Scan

An Index Fast Full Scan reads the entire index using multiblock I/O.

Unlike Index Full Scan, it:

• Does not maintain index order

• Reads blocks faster

• Works similar to a full table scan but on index

Example Query

SELECT department_id
FROM departments;

If all required columns exist in the index.

Explain Plan

--------------------------------------------------------------------------------
| Id | Operation                | Name        |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT         |             |
|  1 |  INDEX FAST FULL SCAN    | DEPT_IDX    |

-------------------------------------------------------------------------------- 

Index Skip Scans : An index created on multiple table columns is known as a multi-column, concatenated, or composite index. An Index Skip Scans occurs when the initial column of a composite index is “skipped” or not specified in the query.

Index Skip Scan

An Index Skip Scan occurs when the query does not specify the leading column of a composite index.

Example index:

CREATE INDEX emp_idx ON employees(department_id, job_id);

Query

SELECT * FROM employees WHERE job_id = 'SA_REP';

Here the optimizer skips department_id and scans index partitions internally.

Explain Plan

--------------------------------------------------------------------------------
| Id | Operation         | Name    |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT  |         |
|  1 |  INDEX SKIP SCAN  | EMP_IDX |
--------------------------------------------------------------------------------

Index Join Scans : 

An Index Join Scans is a hash join of multiple indexes that together return all columns requested by a query. The database does not need to access the table because all data is retrieved from the indexes.

The cost of retrieving rows from the table is higher than reading the indexes without retrieving rows from the table. An index join is often expensive. For example, when scanning two indexes and joining them, it is often less costly to choose the most selective index, and then probe the table.

Index Join Scan

An Index Join Scan occurs when Oracle retrieves all required columns from multiple indexes without accessing the table.

Oracle performs a hash join between indexes.

Example

SELECT employee_id, department_id
FROM employees
WHERE department_id = 10;

If:

• Index exists on employee_id

• Index exists on department_id

Explain Plan

--------------------------------------------------------------------------------
| Id | Operation            | Name |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |      |
|  1 |  VIEW                |      |
|  2 |   HASH JOIN          |      |
|  3 |    INDEX RANGE SCAN  | IDX1 |
|  4 |    INDEX RANGE SCAN  | IDX2 |

--------------------------------------------------------------------------------

 

Bitmap Index Scans : Bitmap index uses a set of bits for each key values and a mapping function that converts each bit position to a rowid. Oracle can efficiently merge bitmap indexes that correspond to several predicates in a WHERE clause, using Boolean operations to resolve AND and OR conditions.

Bitmap Index Scan

A Bitmap Index stores bits for each row representing values of a column.

Bitmap indexes are ideal for low-cardinality columns.

Example columns:

• Gender

• Status

• Country

Example Query

SELECT *
FROM employees
WHERE gender='M'
AND status='ACTIVE';

Explain Plan

--------------------------------------------------------------------------------
| Id | Operation              | Name           |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |                |
|  1 |  BITMAP CONVERSION TO ROWIDS           |
|  2 |   BITMAP AND                            |
|  3 |    BITMAP INDEX RANGE SCAN | GENDER_IDX |
|  4 |    BITMAP INDEX RANGE SCAN | STATUS_IDX |
--------------------------------------------------------------------------------

Oracle combines bitmap indexes using Boolean operations (AND, OR).

Summary of Access Methods

Access Path	Purpose
Full Table Scan	Reads entire table
Table Access by ROWID	Fastest row retrieval
Index Unique Scan	Returns single row
Index Range Scan	Retrieves range of values
Index Full Scan	Reads entire index in order
Index Fast Full Scan	Fast scan of entire index
Index Skip Scan	Uses composite index without leading column
Index Join Scan	Combines multiple indexes
Bitmap Index Scan	Efficient for low-cardinality columns

-----------------------------------------------------------

4 Join Type:

Join Types in Oracle Execution Plan

In the Oracle Database execution plan, the join method describes how rows from two data sources are combined to produce the final result set.

You can identify the join method used by the optimizer by looking at the OPERATION column in the Explain Plan.

Oracle primarily uses the following join methods:

• Hash Join

• Nested Loop Join

• Sort Merge Join

• Cartesian Join

Each join method is chosen by the optimizer depending on table size, indexes, join conditions, and statistics.

The join method describes how data from two data producing operators will be joined together. You can identify the join methods used in a SQL statement by looking in the operations column in the explain plan.

1.Hash Joins : Hash joins are used for joining large data sets. The optimizer uses the smaller of the two tables or data sources to build a hash table, based on the join key, in memory. It then scans the larger table, and performs the same hashing algorithm on the join column(s). It then probes the previously built hash table for each value and if they match, it returns a row.

Hash Join

A Hash Join is typically used when joining large datasets and when the join condition is an equality condition (=).

How Hash Join Works

1. Oracle selects the smaller table.

2. It creates a hash table in memory using the join key.

3. Oracle scans the larger table.

4. For each row, it applies the same hashing algorithm.

5. Matching rows are returned.

Example Query

SELECT e.employee_id,
       d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

Example Explain Plan

--------------------------------------------------------------------------------
| Id | Operation            | Name        |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |             |
|  1 |  HASH JOIN           |             |
|  2 |   TABLE ACCESS FULL  | EMPLOYEES   |
|  3 |   TABLE ACCESS FULL  | DEPARTMENTS |
--------------------------------------------------------------------------------

When Oracle Uses Hash Join

• Large tables

• Equality join condition

• No useful indexes

• Sufficient memory available

2. Nested Loop Joins :Nested loops joins are useful when small subsets of data are being joined and if there is an efficient way of accessing the second table (for example an index look up). For every row in the first table (the outer table), Oracle accesses all the rows in the second table (the inner table). Consider it like two embedded FOR loops.

Nested Loop Join

A Nested Loop Join is efficient when small datasets are involved and when the inner table can be accessed quickly using an index lookup.

You can think of it as two nested loops.

How Nested Loop Join Works

1. Oracle reads a row from the outer table.

2. It searches for matching rows in the inner table.

3. This process repeats for each row of the outer table.

Example Query

SELECT e.employee_id,
       d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE e.employee_id = 100;

Example Explain Plan

--------------------------------------------------------------------------------
| Id | Operation                     | Name        |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |             |
|  1 |  NESTED LOOPS                 |             |
|  2 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES   |
|  3 |    INDEX UNIQUE SCAN          | EMP_PK      |
|  4 |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS |
|  5 |    INDEX UNIQUE SCAN          | DEPT_PK     |
--------------------------------------------------------------------------------

When Oracle Uses Nested Loop

• Small result sets

• Index exists on join column

• Highly selective predicate

3. Sort Merge Join : Sort merge joins are useful when the join condition between two tables is an in-equality condition such as, <, <=, >, or >=. Sort merge joins can perform better than nested loop joins for large data sets. The join consists of two steps:

Sort join operation: Both the inputs are sorted on the join key.

Merge join operation: The sorted lists are merged together.

Sort Merge Join

A Sort Merge Join is useful when joining large tables where the join condition involves range conditions such as <, <=, >, >=.

How Sort Merge Join Works

The join consists of two phases:

1. Sort Phase

Both tables are sorted on the join key.

2. Merge Phase

Oracle merges the sorted data sets to produce the result.

Example Query

SELECT *
FROM orders o
JOIN shipments s
ON o.order_date <= s.shipment_date;

Example Explain Plan

--------------------------------------------------------------------------------
| Id | Operation              | Name      |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |           |
|  1 |  MERGE JOIN            |           |
|  2 |   SORT JOIN            |           |
|  3 |    TABLE ACCESS FULL   | ORDERS    |
|  4 |   SORT JOIN            |           |
|  5 |    TABLE ACCESS FULL   | SHIPMENTS |
--------------------------------------------------------------------------------

When Oracle Uses Sort Merge Join

• Inequality join conditions

• Large datasets

• When hash join is not possible

4. Cartesian join : Cartesian Joins occur when Optimizer joins every row from one data source to every other row from another data source. This is usually a sign of a bad plan. We definitely need to dig further in Query to avoid this type of join.

Cartesian Join

A Cartesian Join occurs when Oracle combines every row of one table with every row of another table.

This happens when no join condition exists between tables.

Example Query

SELECT *
FROM employees e,
     departments d;

If no join condition is specified, Oracle returns:

employees_rows × departments_rows

Example Explain Plan

--------------------------------------------------------------------------------
| Id | Operation           | Name        |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |             |
|  1 |  MERGE JOIN CARTESIAN             |
|  2 |   TABLE ACCESS FULL | EMPLOYEES   |
|  3 |   TABLE ACCESS FULL | DEPARTMENTS |
--------------------------------------------------------------------------------

Why Cartesian Join is Dangerous

If:

• Employees = 1000 rows

• Departments = 100 rows

Result = 100,000 rows

This usually indicates:

• Missing join condition

• Incorrect query design

---

Summary of Join Methods

Join Type	Best Use Case
Hash Join	Large datasets
Nested Loop Join	Small datasets with indexes
Sort Merge Join	Inequality joins
Cartesian Join	Usually a query mistake

---

Simple Visual Comparison

Nested Loop

for each row in table A
search matching rows in table B

Hash Join

build hash table (small table)
scan large table
probe hash table


Sort Merge Join

sort table A
sort table B
merge sorted results