It is also known as:
- execution plan
- execution tree
Each step returns a row set. Each step either retrieves rows of data physically from the database and/or prepares them in some way for the user issuing the statement.
In some execution plans the steps are iterative and in others sequential. A plan is iterative when for instance the SQL engine moves from index to table to client and then repeats the steps.
When executed by the SQL virtual machine, the plan produces the result set.
You can get the execution plan through various mechanisms.
You can get:
- the plan that the optimizer chose before starting with
- the final plan with:
- Explain plan from the cursor cache (cursor loaded in memory)
You can influence the execution plan (e.g. the query optimizer) by
- setting the optimizer goal (either throughput or response time)
- gathering representative statistics
- using hints to instruct the optimizer about how a statement should be executed.
- changing some initialization parameters
The query plan might not be the same from one version of Oracle Database to the next. In recent versions, the optimizer might make different decisions, because better information is available.
Option 1 - Nested Loop Join
- the nested loop-join has O(n2) time complexity.
- square complexity
for each record i in Item for each record o in Order if o.order = i.order return matching pair
Option 2 - Data Structure Join
- Constant time
- The hash-join has O(n) time complexity
for each record i in Item insert into a data structure (such as a hash table) for each record o in Order: lookup corresponding records in hashtable return matching pair
Performance Tool kit
In single-user mode :
In multi-user mode :