CSE Roadmap

Core tier · about 2 hours · 6 parts

Part 2 of 6 · 2 min read

Reading an execution plan

Think of an execution plan like a recipe's steps, listed in the order the kitchen actually performs them. Each step is labeled with how many portions it expects to produce and how much effort it costs.

In plain words: An execution plan lists the operations the engine will actually perform, nested to show which step feeds into which, along with the engine's own estimate of how many rows each step will produce and a relative cost number used to compare plans. Alongside the plan, predicate information shows exactly which part of the WHERE clause each individual step applies, which matters because a query can filter on more than one column while only part of that filtering happens during an index lookup, with the rest applied afterward once more columns of the row are available.

A worked example. A query filtering on both employee_id and subsidiary_id together, where both columns form the primary key, produces a plan with one INDEX UNIQUE SCAN step whose predicate shows both conditions being applied directly during the index lookup itself, estimating exactly one matching row.

The key lines.

SELECT first_name, last_name
  FROM employees
 WHERE employee_id = 123
   AND subsidiary_id = 30;
---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |    2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |    2 |
|*2 | INDEX UNIQUE SCAN          | EMPLOYEES_PK |    1 |    1 |
---------------------------------------------------------------
Predicate Information:
   2 - access("EMPLOYEE_ID"=123 AND "SUBSIDIARY_ID"=30)

Execution plan, predicate information

An execution plan is the ordered set of physical operations an engine will perform to answer a query, annotated with estimated row counts and a relative cost for comparing plans. Predicate information shows exactly which filter condition each individual plan step actually applies.

Example: in the plan above, the predicate information line shows both employee_id and subsidiary_id being applied directly inside the index unique scan step.