Jesper,
thanks, I'll pass this along to my friend. I'm not sure it will help,
as his manager knows that the query in question is a performance hog
and had taken it out of the production system because of that. It's
back in now because certain result sets are being promised to the
clients, and his manager has conveniently forgotten that the query is a
dog.
Rachel
- Jesper Haure Norrevang <jhn.aida_at_cbs.dk> wrote:
> Hi Rachel,
>
> Oracle has an interface, which makes it possible, that you can write
> =
> your
> own User-Defined Aggregate Functions. The hard way to get a feeling
> with =
> how
> aggregate functions are working is to read chapter 11 "User-Defined
> Aggregate Functions" in "Oracle9i Data Cartridge Developer's Guide,
> Part =
> No.
> A96595-01", and then try and test it.
>
> Anyway, let me try to explain how an aggregate function is processed
> =
> with a
> simple example
> select deptno, sum(sal)
> from emp
> where job !=3D 'MANAGER'
> group by deptno
> having sum(sal) > 6500
> order by deptno
>
> 1. A row source with the columns DEPTNO and SAL provides data from
> the =
> emp
> table - just like a normal query. Only rows with job !=3D 'MANAGER'
> are
> contained in the query. I.e. the WHERE-clause is evaluated first.
> There =
> are
> 14 rows in emp. After filtering with the WHERE-clause we have 11 rows
> =
> left.
>
> 2. The data is sorted by the DEPTNO column. I.e. the GROUP BY-clause
> is
> processed next.
>
> 3. For each different DEPTNO, the SUM is calculated. Any rows with =
> NULLs in
> SAL are ignored. COUNT(*) is actually an exception to the generel
> rule, =
> that
> NULL-values are ignored in aggregate functions. After this step we
> have =
> 3
> rows representing the departments 10, 20 and 30. (This step can be =
> broken
> down into 4 sub steps: Initialize, Iterate, Merge and Terminate. See
> the
> manual mentioned above for details).
>
> 4. The HAVING-clause is evaluated after calculating the aggregate =
> values.
> The HAVING-clause eliminates department 10, and two rows are left.
>
> 5. The ORDER BY-clause is executed. Do not rely on the sort in step
> 2. =
> If
> you want data in a specific order, the only way to be sure is to
> specify
> ORDER BY.
>
>
> Regards
> Jesper Haure N=F8rrevang
>
>
> -----Oprindelig meddelelse-----
> Fra: oracle-l-bounce_at_freelists.org =
> [mailto:oracle-l-bounce_at_freelists.org] P=E5
> vegne af Rachel Carmichael
> Sendt: 9. februar 2004 21:52
> Til: freelists oracle-l
> Emne: help on aggregate functions
>
>
> From a friend of mine:
>
> "what I'm looking for is a detailed breakdown on how aggregate
> functions process in the database. kind of a step-by-step to how they
> roll through the system"
>
>
> I know that *I* don't know the answer. But I know someone on this
> list
> likely does.
>
> Help please?
>
> Rachel
>
> __________________________________
> Do you Yahoo!?
> Yahoo! Finance: Get your refund fast by filing online.
> http://taxes.yahoo.com/filing.html
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Tue Feb 10 2004 - 05:10:58 CST
