Re: basic question about analytic functions

From: D.Y. <dyou98_at_aol.com>
Date: 19 Feb 2003 22:02:59 -0800
Message-ID: <f369a0eb.0302192202.4b6327fd@posting.google.com>

udo456_at_yahoo.com (Udo Diekmann) wrote in message news:<5757f268.0302191451.3e77c9a4_at_posting.google.com>...
> I have heard that analytic functions can give a performance boost and want
> to actually time a standard query versus an analytic query which returns
> the exact same data. Thus the need for the same query as an analytic
> function query. So all I want to do is to translate the query using
> analytic functions and see if it's faster.
>

There are situations where analytic functions make things a lot easier and faster, such as complex DSS queries which used to require multi-pass SQLs and now can be handled by analytic functions in a single pass. But to say analytic functions are always better is incorrect. In fact, for a simple query like the one you are testing, analytic functions almost always make it slower.

Use analytic functions for what they are good at. Oracle's Data Warehouse Guide explains how they work and where they should be used. It's a new feature and can be a little confusing at first. But it's definitely worth spending time to understand it. When used properly, these functions can give you huge performance boost.

To answer your question, this (not tested) should be equivalent to your original query,

select distinct sales_div_no, sales_div_name,   sum(t_total_sales_units) over
  (partition by sales_div_no,sales_dev_name) "T UNITS",   sum(l_total_sales_units) over
  (partition by sales_div_no,sales_dev_name) "L UNITS" from (select dev1.sales_div_no, dev1.sales_div_name,

        fact.t_total_sales_units,fact.l_total_sales_units
      from dim_dev1_plan dev1, fact_weekly_plan fact
      where dev1.dev1_id=fact.dev1_id
      and fact.per_id between 440 and 540
      and dev1.sales_div_no between 300 and 500)

order by sales_div_no, sales_div_name;

> JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0302181557.126a1de6_at_posting.google.com>...
> > Why converting? Is you query not giving you the results you want? If
> > it is, there is no reason to convert. If it is not, then explain what
> > you need and people will be able to help you. Analytical functions
> > give you particular fomat of output which may or may not be what you
> > are after. It is neat but not a magic bullet.
> >
> >
> > - Jusung Yang
> >
> >
> > udo456_at_yahoo.com (Udo Diekmann) wrote in message news:<5757f268.0302181059.7b98c9d9_at_posting.google.com>...
> > > I got a basic query that I need to turn into a analytic function query.
> > >
> > > Can someone help me translate it? I tried several ideas (using over() etc.
> > > but I'm no guru at this and have failed). I'm sure it's simple.
> > >
> > > Here is a normal query that I need to translate as an analytic function query:
> > >
> > > select dev1.sales_div_no, dev1.sales_div_name,
> > > sum(fact.t_total_sales_units) "T UNITS",
> > > sum(fact.l_total_sales_units) "L UNITS"
> > > from dim_dev1_plan dev1, fact_weekly_plan fact
> > > where dev1.dev1_id=fact.dev1_id
> > > and fact.per_id between 440 and 540
> > > and dev1.sales_div_no between 300 and 500
> > > group by dev1.sales_div_no, dev1.sales_div_name
> > > order by dev1.sales_div_no, dev1.sales_div_name;
> > >
> > > Thanks in advance for any assistance.
> > >
> > > Udo.
Received on Thu Feb 20 2003 - 00:02:59 CST