SV: help on aggregate functions
- From: Jesper Haure Norrevang <jhn.aida@xxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 10 Feb 2004 07:52:37 +0100
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@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
