Sql Engine - Logical Plan (Query)

Oracle Database Sql Processing

About

A execution plan is an ordered set of steps to execute a query

During SQL processing, the row source generator receives the optimal execution plan from the optimizer and produces an iterative plan, called the query plan, that is usable by the rest of the database.

The iterative plan is a binary program that, when executed by the SQL virtual machine, produces the result set.

The actual execution plan is produced by the optimizer whereas the query plan is an iterative execution plan produced after in the SQL processing steps by the row source generator.

Also known as SQL Engine - How to read a physical plan (Execution Plan | Execution Tree)

Logical Query Plan Example

Read data from storage, and then do join on some join keys, and finally aggregate with some aggregation keys

SQL
SELECT
  item.brand,
  sum(price)
FROM
  sales,
  item
WHERE
  sales.item_key =
  item.item_key
GROUP BY
  item.brand
Logical Query Plan Logical Query Plan
Distributed Query Plan (Master Worker)

Distributed Query Plan

Relational algebra

Every query is represented as a tree of relational operators. You can translate from SQL to relational algebra, or you can build the tree directly.

Planner rules transform expression trees using mathematical identities that preserve semantics. For example, it is valid to push a filter into an input of an inner join if the filter does not reference columns from the other input.

For instance, you can optimizes queries by repeatedly applying planner rules to a relational expression. A cost model guides the process, and the planner engine generates an alternative expression that has the same semantics as the original but a lower cost.

A query with an Aggregate, and a Filter:

final RelNode node = builder
  .scan("EMP")
  .aggregate(builder.groupKey("DEPTNO"),
      builder.count(false, "C"),
      builder.sum(false, "S", builder.field("SAL")))
  .filter(
      builder.call(SqlStdOperatorTable.GREATER_THAN,
          builder.field("C"),
          builder.literal(10)))
  .build();
System.out.println(RelOptUtil.toString(node));

is equivalent to SQL

SELECT deptno, count(*) AS c, sum(sal) AS s
FROM emp
GROUP BY deptno
HAVING count(*) > 10

and produces the following query plan

LogicalFilter(condition=[>($1, 10)])
  LogicalAggregate(group=[{7}], C=[COUNT()], S=[SUM($5)])
    LogicalTableScan(table=[[scott, EMP]])

Documentation / Reference





Discover More
Card Puncher Data Processing
Calcite - Logical Plan (Logical algebra)

in Calcite A logical plan is a relational expression with only logical operator. Logical algebra has no implementation of the relational operator and therefore can't run. The logical plan is the first...
Imperative Vs Functional
Code - Functional programming (FP) - Collection Operations

Functional programming (FP) defines standard operations on collections. It is a declarative paradigm that treats computation as the evaluation of mathematical functions. Most of the operations, you perform...
Bi Server Architecture
OBIEE - Query Plan

The query plan in OBIEE To disable the query cache plan: For on query or session, you can set the following system session variable to 1 For the whole system, set the MAX_QUERY_PLAN_CACHE_ENTRIES...
Card Puncher Data Processing
Oracle Database - (Actual|Final) Execution Plan

The actual or final plan is the execution that was executed in order to retrieve the result of a SQL. optimizerquery planrow source generator V view contains the actual plan for a query that...
Relational Algebra Between Sql And Query Plan
Relational Algebra - Expression and Operators

Relational algebra is based upon the fact that you can pass tabular data through a set of data operators (select, filter, join, sort, union, etc.) in a algebraic structure. It means that: the output...
Oracle Database Sql Execution Parse Tree
SQL Engine - How to read a query plan ?

How to read a query plan (ie a execution tree) ? Each row in the output table corresponds to a single step in the execution plan. The first step is the furthest indented to the right. As it's...
Oracle Database Sql Processing
SQL Engine - Row source generator

The row source generator performs the row source generation steps of an SQL processing. It receives the optimal execution plan from the optimizer and produces an iterative plan, called the query plan,...
Query Optimizer Process
SQL Engine - The Query Transformer

The query transformer is a component of the query optimizer and take as input a parsed query, which is represented by a set of query blocks. The query blocks are nested or interrelated to each other....
Spark Query Plan Generation
Spark Engine - Logical Plan

Logical Plan in Spark. Each data structure represents a logical plan that describes the computation required to produce the data. When an action is invoked, Spark's query optimizer optimizes the...



Share this page:
Follow us:
Task Runner