Database Reference
In-Depth Information
Types of Operations
There are hundreds of different operations. Of course, to fully understand an execution plan, you should know what
each operation it's made of does. For our purpose of walking through an execution plan, you need to consider only
four major types of operations:
stand-alone operations
,
iterative operations
,
unrelated-combine operations
, and
related-combine operations
. Basically, each type has a particular behavior, and knowing it is sufficient for reading
execution plans.
■
i coined the terms used here for the four types of operations while writing a presentation about the query
optimizer in 2007. Don't expect to find these terms used elsewhere.
Caution
In addition to these four types, operations can be separated into blocking operations and nonblocking
operations. Simply put, blocking operations process data in bulk, whereas nonblocking operations process data row
by row. For example, a sort operation is blocking because it's able to return the output rows only when all input rows
have been fully processed (sorted)—since the first output row could be anywhere in the input set. On the other hand,
a filter applying a simple restriction is nonblocking because it evaluates each row independently. It goes without
saying that for blocking operations, data must be buffered either in memory (PGA) or on disk (temporary tablespace).
For simplicity, while walking through an execution plan, you can consider all operations to be blocking. Remember,
though, that most of the operations are in fact nonblocking and that the SQL engine, for obvious reasons, tries to avoid
the buffering of data as much as possible.
Stand-Alone Operations
I identify as
stand-alone operations
all operations having at most one child and that are not
iterative operations
(covered in the next section). Most operations are stand-alone. This makes the interpretation of execution plans easier
because less than two dozen operations aren't of this type. The rules governing the working of stand-alone operations
are the ones described in the “Parent-Child Relationship” section, with the following addition:
•
Here's an example of a query and its execution plan based on the output generated by the
stand-alone.sql
script (Figure
10-3
provides a graphical representation of its parent-child relationships):
A child operation is executed at most once.
SELECT deptno, count(*)
FROM emp
WHERE job = 'CLERK' AND sal < 1200
GROUP BY deptno
--------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 |
| 1 | HASH GROUP BY | | 1 | 2 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 3 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_I | 1 | 4 |
--------------------------------------------------------------------
2 - filter("SAL"<1200)
