SV: help on aggregate functions

From: Jesper Haure Norrevang <jhn.aida_at_cbs.dk>
Date: Tue, 10 Feb 2004 07:52:37 +0100
Message-id: <000001c3efa2$78935dd0$4a28e282@AIDA.local>

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

Received on Tue Feb 10 2004 - 00:52:37 CST