SQL Engine - Row source generator

Oracle Database Sql Processing

About

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, that is usable by the rest of the database.

Oracle Database Sql Processing

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

The query plan takes the form of a combination of steps. Each step returns a row set. The rows in this set are either used by the next step or, in the last step, are returned to the application issuing the SQL statement.

Row Source

Definition

A row source is a row set returned by a step in the execution plan along with a control structure that can iteratively process the rows.

The row source can be:

  • a table,
  • view,
  • or result of a join
  • or grouping operation.

Tree

The row source generator produces a row source tree, which is a collection of row sources. The row source tree shows the following information:

  • An ordering of the tables referenced by the statement
  • An access method for each table mentioned in the statement
  • A join method for tables affected by join operations in the statement
  • Data operations such as filter, sort, or aggregation

The example below shows the execution plan of a SELECT statement when AUTOTRACE is enabled. The statement selects the last name, job title, and department name for all employees whose last names begin with the letter A. The execution plan for this statement is the output of the row source generator.

SELECT e.last_name, j.job_title, d.department_name 
FROM   hr.employees e, hr.departments d, hr.jobs j
WHERE  e.department_id = d.department_id
AND    e.job_id = j.job_id
AND    e.last_name LIKE 'A%' ;
Execution Plan
----------------------------------------------------------
Plan hash value: 975837011
 
---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     3 |   189 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN                    |             |     3 |   189 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN                   |             |     3 |   141 |     5  (20)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |     3 |    60 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | EMP_NAME_IX |     3 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL          | JOBS        |    19 |   513 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL           | DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   2 - access("E"."JOB_ID"="J"."JOB_ID")
   4 - access("E"."LAST_NAME" LIKE 'A%')
       filter("E"."LAST_NAME" LIKE 'A%')





Discover More
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...
Oracle Database Sql Processing
SQL Engine - SQL processing (Sql Compiler)

In which way the database processes: DDL statements to create objects, DML to modify data, and queries to retrieve data. Depending on the statement, the database may omit some of these...
Logical Query Plan
Sql Engine - Logical Plan (Query)

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



Share this page:
Follow us:
Task Runner