Oracle execution plan

An execution plan is an action performed by an SQL statement running in Oracle in a certain order. You can use the explain PLANFOR statement to view the execution plan. In plsqldevelope

An execution plan is an action performed by an SQL statement running in Oracle in a certain order. You can use the explain plan for statement to view the execution plan. In plsql develope

Execution Plan

An execution plan is an action performed by an SQL statement running in Oracle in a certain order.

You can use the explain plan for statement to view the execution plan. In the plsql developer tool, you can directly use the explain plan window to view the execution plan of the SQL statement.

The SQL statement is:

The execution plan is as follows:

How to read the execution plan
The execution plan should be: run SQL statements from top to bottom in the order from right to left.

From top to bottom: Generally, an execution plan contains multiple nodes at the same level (or in parallel). The execution plan is based on the top and the bottom.

Right-to-left: Multiple subnodes exist under a node. The execution starts from the rightmost subnode.

Table access methods

1. Full Table Scan (FTS) Full Table Scan

2. Index Lookup Index Scanning

There are 5 methods of index lookup:

Index unique scan -- unique index scan

Method for looking up a single key value via a unique index. always returns a single value, You must supply at least the leading column of the index to access data via the index.

Index range scan -- partial index scan

Index range scan is a method for accessing a range values of a particle column. at least the leading column of the index must be supplied to access data via the index. can be used for range operations (e.g.> <> = <= ).

Index full scan -- index Global scan

Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan wocould be a good idea or not. we choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. for example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order.

Index fast full scan -- index quick global scan, which is usually generated without order

Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED = TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock I/o, can be executed in parallel, can be used to access second column of concatenated indexes. this is because we are selecting all of the index.

Index skip scan -- index skip scan. where condition columns are frequently generated when they are non-index leading columns.

Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column (s) during the search.

3. Rowid physical ID scan

This is the quickest access method available. Oracle retrieves the specified block and extracts the rows it is interested in. -- Rowid scan is the fastest way to access data