Sql Engine - (Physical|Execution) Plan

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > RDBMS - Sql Processing (Engine)

1 - About

The output from the optimizer is an execution plan (also known as query plan of simply plan) that describes an optimum method of execution.

The execution plan takes the form of a combination of steps that the Database uses to execute a SQL statement.

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:

You can influence the execution plan (e.g. the query optimizer) by

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.


3 - Operations and Pseudo Code

For the below SQL,

  ORDER o, item i
  o.order = i.order

You have two type of join.

The nested join will go faster than a hash join for a few records because it don't need to create the data structure

3.1 - 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

3.2 - 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

4 - Reviewing plans and tuning

It's important when reviewing and tuning queries to emulate what your application does. You cannot tune a query with literals and expect a query that contains bind variables to have the same performance characteristics.

Tuning this sql :

SELECT * FROM some_table WHERE COLUMN = 55

is different of tuning this one :

SELECT * FROM some_table WHERE COLUMN = :bind_variable

4.1 - Performance Tool kit

4.2 - Others