Analytical Function order of processing within SQL.

Personally Believe, Analytical functions in Oracle are Prime addition and provide great advantage in processing for required data projections.
In current post will try to explain SQL order of processing for Analytical Functions.

Most of us must be aware on below SQL processing order.

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

Point of discussion in current blog is, when Analytical function will get processed.
More in context with GROUP BY and HAVING Clause.

Lets try to understand it better with field work.
Aggregating Employee count, department wise.

SELECT Deptno, Count(*)
FROM Emp
GROUP BY Deptno
ORDER BY deptno;

Output :

DEPTNO COUNT(*)
—— ———-
10 3
20 5
30 6

Now lets add analytical function to above SQL.

Select Deptno, Count(*) , count(*) over () -- analytical function
From Emp
Group By Deptno
order by deptno;

DEPTNO COUNT(*) COUNT(*)OVER()
———- ———- ————–
10 3 3
20 5 3
30 6 3

Before discussing on column data projected, lets try to understand underlying plan for same.
To understand order of processing, it always good to check underlying plan of SQL.

select * from table(dbms_xplan.display_cursor);

SQL_ID fuf4fzn14hhqm, child number 0
————————————-
“Select Deptno, Count(*) , count(*) over ()
From Emp Group By Deptno ”
order by deptno

Plan hash value: 4215643104

—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————-
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | WINDOW BUFFER | | 3 | 9 | 3 (0)| 00:00:01 |
| 2 | SORT GROUP BY | | 3 | 9 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| 00:00:01 |
—————————————————————————-

As per Plan, below were the order of processing for SQL statement.

Step 1: “TABLE ACCESS FULL”
Access/Fetch Data table

Step 2: “SORT GROUP BY” –> GROUP BY + ORDER BY
For GROUP BY and ORDER BY Defined, Optimizer perform “SORT GROUP BY” to aggregate results as per Deptno.
For more insight on SORT GROUP BY, please check here

Step 3: “WINDOW BUFFER”
Analytical function is evaluated at this step.
It will acts on rows return after Aggregate functions is evaluated.
Analytical functions “count(*) over ()” acted on below rows i.e. result of aggregation.

DEPTNO COUNT(*)
------ ----------
10 3
20 5
30 6

As Aggregate function output comprises Three Rows with Aggregations hence analytical functions was showing projected output as “THREE”.
For analytical function”Count(*) over()”, Window define comprise of Only THREE Rows not the complete table EMP data.It acted after GROUP BY Clause so for analytical functions count(*) was applied on above aggregated sets. Hence column “Count(*) over()” output is 3.

Order by processing before analytical functions won’t make much difference for analytical functions.As analytical functions has it own WINDOW with ORDER with in it.

Clearly “GROUP BY” Takes precedence over Analytical functions.

One more example to understand it better.

Select Deptno, Count(*) , count(*) over (partition by deptno) -- analytical function
From Emp
Group By Deptno
order by deptno;

DEPTNO COUNT(*) COUNT(*)OVER(PARTITIONBYDEPTNO)
———- ———- ——————————-
10 3 1
20 5 1
30 6 1

Column “COUNT(*)OVER(PARTITIONBYDEPTNO)” showing output as “1”, as it acted on already aggregated result sets i.e. 1 aggregated/summarize records for each deptno.
Hence partition by clause got only one record for each partition as per deptno.

Conclusion :
Analytical function, is processed after GROUP BY Clause and indirectly also after HAVING Clause.

Select Deptno, count(*),Count(*) Over () -- analytical function
From Emp
Group By Deptno
having count(*) > 4;

Output :
DEPTNO COUNT(*) COUNT(*)OVER()
———- ———- ————–
30 6 2
20 5 2

As we can clearly see, Deptno = 10 was not projected due to “HAVING” Clause condition.

Plan for same.

Plan hash value: 802907668
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | WINDOW BUFFER | | 1 | 3 | 3 (0)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | HASH GROUP BY | | 1 | 3 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(COUNT(*)>4)

“FILTER” Clause was processed before “WINDOW BUFFER”.
Filter comprise of restriction as part of HAVING Clause, (COUNT(*)>4).

Analytical function “COUNT(*)OVER()” was processed against window comprise of TWO Result sets after “GROUP BY And HAVING Clause”, hence analytical function’s count(*) output is TWO.

So clearly, SQL order of processing with analytical functions is as below.

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. Analytical Clause.
6. Select Clause.

Intentionally have not included order by clause in processing order.
Order by Clause, can also be processed as part of analytical functions.

Let check below SQL with underlying plan.

Select Deptno , Count(*) Over () From Emp
order by deptno ;

SQL_ID c1dufc0nr0u2c, child number 0
————————————-
Select Deptno , Count(*) Over () From Emp order by deptno

Plan hash value: 3145491563

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | WINDOW SORT | | 14 | 42 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| 00:00:01 |
—————————————————————————

Check at Step 2: “WINDOW SORT”
Sorting or ordering is processed as part of analytical processing.

For “order by clause”, correct order can’t be defined, it is interchangeable. It can be processed after or before or with analytical processing.It purely depends on underlying SQL plan steps.

Hope information shared was useful, please feel free to share any feedback at comments.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , , , . Bookmark the permalink.

One Response to Analytical Function order of processing within SQL.

  1. Pingback: Multiplication of Rows Data and Analytical Functions. | Oracle Insight and Performance concepts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s