Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Bruno.

Asked: September 04, 2008 - 10:23 am UTC

Answered by: Tom Kyte - Last updated: March 31, 2020 - 6:12 am UTC

Category: Developer - Version: 9.2.0.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Analytic functions are really cool, and I can see great benefits from using it. Despite its benefits, does Analytics Functions has any drawbacks, like performance issues, or any details one should be aware of when choosing to use analytics, like performance Issues, or something? Does calculations made within its windows work just like another SELECT statement, or is it any different?

Thanks!
Bruno.

and we said...

They are a tool, understand what they must do in order to process and you'll be able to answer something like this yourself.

consider;

select a, b, c, 
       rank() over (order by a), 
       rank() over (order by b), 
       rank() over (order by c)
from t


pretty simple query - does a TON of work though, the data must be sorted three times.

Analytics can make possible the impossible - and do so efficiently. But you need to understand that they will sort, they will use temp and if you make them sort 50 times in a single query - so be it, they will (but then your question is forcing them to...)


In general, analytics are great for answering "really big" questions or questions against "small sets"

You have to remember though - the order of processing is

a) where clause
b) analytic
c) then order by/aggregates

so, if you hide an analytic in a view, eg:

select emp.*, sum(sal) over (partition by deptno) deptno_sum_sal
from emp;

and you do a query like:


select * from V where empno = 1234;

don't expect that to use an index on empno - for you see, we have to compute the sum(sal) for the entire DEPTNO, not just for that empno - this is something people frequently overlook.

You get the right answer - but they expect the index to be used - but we cannot, for if we did - we'd compute the wrong sum(sal)...

and you rated our response

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Clarification

September 05, 2008 - 11:37 am UTC

Reviewer: Amit from New Haven, CT

a) where clause
b) analytic
c) then order by/aggregates

I thought that the order of execution was

1. where clause
2. group and aggregate functions (group by rollup ,cube etc)
3. Analytic function
4. Order by

is that correct or my understanding was wrong
Tom Kyte

Followup  

September 05, 2008 - 12:35 pm UTC

wrong

analytics are decidely "aggregate unfriendly"

you cannot group by an analytic, you cannot not group by them - so really, just drop the aggregates out of the list, you'd have to use an inline view and explicitly apply the aggregates before or after.

More clarification

September 05, 2008 - 11:37 am UTC

Reviewer: Amit from New Haven, CT

a) where clause
b) analytic
c) then order by/aggregates

I thought that the order of execution was

1. where clause
2. group and aggregate functions (group by rollup ,cube etc)
3. Analytic function
4. Order by

is that correct or my understanding was wrong

And by the way where does the model clause fit into this execution order ?

Order or processing

September 05, 2008 - 6:47 pm UTC

Reviewer: Rob van Wijk from Utrecht, Netherlands

Hello Tom,

My initial reaction was the same as Amit's. If an analytic function is processed earlier than an aggregate, then how would you explain these results:

SQL> select deptno
  2       , sum(sal)
  3       , sum(sum(sal)) over (order by deptno)
  4    from emp
  5   group by deptno
  6  /

    DEPTNO   SUM(SAL) SUM(SUM(SAL))OVER(ORDERBYDEPTNO)
---------- ---------- --------------------------------
        10       8750                             8750
        20      10875                            19625
        30       9400                            29025

Regards,
Rob.

Tom Kyte

Followup  

September 08, 2008 - 8:52 am UTC

Ok, I did not think of analytics on top of aggregates. I stand corrected.

For Clarification from the SQL reference manual

September 05, 2008 - 11:55 pm UTC

Reviewer: Tom

Regarding the order of processing...

Analytic functions are the last set of operations performed in a query except for the
final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are
completed before the analytic functions are processed. Therefore, analytic functions
can appear only in the select list or ORDER BY clause.

Page 5-10 Oracle SQL Reference

Has this order changed and not been updated?
Tom Kyte

Followup  

September 08, 2008 - 8:59 am UTC

nope, I was thinking about it *wrong*

I wasn't thinking about analytics of an aggregation, but analytics in general:
ops$tkyte%ORA10GR2> select deptno, sum(sal) over () from scott.emp group by deptno;
select deptno, sum(sal) over () from scott.emp group by deptno
                   *
ERROR at line 1:
ORA-00979: not a GROUP BY expression



if you analytic an aggregate - it works OK
ops$tkyte%ORA10GR2> select deptno, sum(sum(sal)) over () from scott.emp group by deptno;

    DEPTNO SUM(SUM(SAL))OVER()
---------- -------------------
        10           118763.25
        20           118763.25
        30           118763.25


but I did not think of it like that - because I would not myself write the query that way - I would have used an inline view


select deptno, sum(sum_sal) over ()
from (select deptno, sum(sal) sum_sal from emp group by deptno)


(matter of style made me miss this one)


Analytics : Order or processing

September 17, 2008 - 2:05 pm UTC

Reviewer: Ravi Vedala from USA

Not sure if we can compare these two queries, as their end result is different.

This query would return the incremental sum of each department and only the last record would have the cumulative total.

select   deptno, sum (sum (sal)) over (order by deptno) sal
    from scott.emp
group by deptno


Where as the query with inline view gives the cumulative total on each record :

select deptno, sum(sum_sal) over ()
from (select deptno, sum(sal) sum_sal from scott.emp group by deptno)


To get the same results any way we have to change the first query to :
select deptno, max(sal) over() from (
select   deptno, sum (sum (sal)) over (order by deptno) sal
    from scott.emp
group by deptno)


So, I believe Tom's approach is much simpler and straight forward.

- Ravi Vedala.

Analytics into PLSQL packages

October 17, 2008 - 4:02 am UTC

Reviewer: Mohamed Houri from FRANCE

Dear Tom,

Analytical functions can not be used within PLSQL packages and procedures (at least for ORACLE 8.1.7.4).
Of course we can use dynamic sql in order to be able to use them within PLSQL.

However, doing as such, we will loose the kind of "Auto Bind variable" one can profit from when using static sql (PLSQL packages).

Could you please give me concrete situations where using analytical functions are useful.

In my actual work 90% of the oracle code is done via PLSQL package.

Thanks for your answer
Tom Kyte

Followup  

October 17, 2008 - 9:32 pm UTC

do you have access to my book Expert one on one Oracle - entire chapter on analytics, all about 'concrete examples', else I'll ask you to just search around on asktom here - there are literally hundreds of examples.

And it is not really any harder to use/not use binds with native dynamic sql, you would:

open 'select ......
from t
where x = :x and y = :y'
USING p_x, p_y;


it is very straight forward.

To get the full TOTAL instead of instead of cumulative total

May 05, 2010 - 5:14 pm UTC

Reviewer: Logan Palanisamy from Sunnyvale, USA

This is in response to "Analytics : Order or processing" by Ravi Vedala from USA dated September 17, 2008 - 2pm


There is no need to MAX etc to get the full total. Just remove the "order by dept" in SUM

LPALANI@l11gr2>select deptno, max(sal) over() from (
  2  select      deptno, sum (sum (sal)) over (order by deptno) sal
  3      from scott.emp
  4  group by deptno);

          DEPTNO   MAX(SAL)OVER()
---------------- ----------------
              10           29,025
              20           29,025
              30           29,025

LPALANI@l11gr2>
LPALANI@l11gr2>select   deptno, sum (sum (sal)) over () sal
  2      from scott.emp
  3  group by deptno
  4  order by 1;

          DEPTNO              SAL
---------------- ----------------
              10           29,025
              20           29,025
              30           29,025

Analytical functions

March 31, 2020 - 4:38 am UTC

Reviewer: Ana from Moldova

What I discovered is, if you put in a view the following function

create or replace view objects_view:
select first_value(field1) over(partition by field2 order by field3 desc ) from objects

And after that make :

select * from objects_view where id_object = 123

Then it will be very slow but if you change the view by adding:

create or replace view objects_view:
select
first_value(field1) over(partition by field2,id_object order by field3 desc ) from objects

The performance is really improved.
Connor McDonald

Followup  

March 31, 2020 - 6:12 am UTC

Without DDL and execution plans we can't really comment, but analytic functions inside a view can *appear* to create slowness because we have to respect the *intent* of the function.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.