Rachel
You might find http://tinyurl.com/ypn8r or Tom's first book if you have it =
helpful, if analytics would help your friend out here.
Niall Litchfield
Oracle DBA
Audit Commission
+44 117 975 7805=20
> -----Original Message-----
> From: wisernet100_at_yahoo.com=20
> Sent: 10 February 2004 11:10
> To: oracle-l_at_freelists.org; wisernet100_at_yahoo.com
> Subject: Re: SV: help on aggregate functions
>=20
>=20
> Jesper,
>=20
> 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=20
> query is a
> dog.
>=20
> Rachel
>=20
> --- Jesper Haure Norrevang <jhn.aida_at_cbs.dk> wrote:
> > Hi Rachel,
> >=20
> > Oracle has an interface, which makes it possible, that you can write
> > =3D
> > your
> > own User-Defined Aggregate Functions. The hard way to get a feeling
> > with =3D
> > how
> > aggregate functions are working is to read chapter 11 "User-Defined
> > Aggregate Functions" in "Oracle9i Data Cartridge Developer's Guide,
> > Part =3D
> > No.
> > A96595-01", and then try and test it.
> >=20
> > Anyway, let me try to explain how an aggregate function is processed
> > =3D
> > with a
> > simple example
> > select deptno, sum(sal)
> > from emp
> > where job !=3D3D 'MANAGER'
> > group by deptno
> > having sum(sal) > 6500
> > order by deptno
> >=20
> > 1. A row source with the columns DEPTNO and SAL provides data from
> > the =3D
> > emp
> > table - just like a normal query. Only rows with job !=3D3D 'MANAGER'
> > are
> > contained in the query. I.e. the WHERE-clause is evaluated first.
> > There =3D
> > are
> > 14 rows in emp. After filtering with the WHERE-clause we=20
> have 11 rows
> > =3D
> > left.
> >=20
> > 2. The data is sorted by the DEPTNO column. I.e. the GROUP=20
> BY-clause
> > is
> > processed next.
> >=20
> > 3. For each different DEPTNO, the SUM is calculated. Any=20
> rows with =3D
> > NULLs in
> > SAL are ignored. COUNT(*) is actually an exception to the generel
> > rule, =3D
> > that
> > NULL-values are ignored in aggregate functions. After this step we
> > have =3D
> > 3
> > rows representing the departments 10, 20 and 30. (This step can be =3D
> > broken
> > down into 4 sub steps: Initialize, Iterate, Merge and Terminate. See
> > the
> > manual mentioned above for details).
> >=20
> > 4. The HAVING-clause is evaluated after calculating the aggregate =3D
> > values.
> > The HAVING-clause eliminates department 10, and two rows are left.
> >=20
> > 5. The ORDER BY-clause is executed. Do not rely on the sort in step
> > 2. =3D
> > If
> > you want data in a specific order, the only way to be sure is to
> > specify
> > ORDER BY.
> >=20
> >=20
> > Regards
> > Jesper Haure N=3DF8rrevang
> >=20
> >=20
> > -----Oprindelig meddelelse-----
> > Fra: oracle-l-bounce_at_freelists.org =3D
> > [mailto:oracle-l-bounce_at_freelists.org] P=3DE5
> > vegne af Rachel Carmichael
> > Sendt: 9. februar 2004 21:52
> > Til: freelists oracle-l
> > Emne: help on aggregate functions
> >=20
> >=20
> > From a friend of mine:
> >=20
> > "what I'm looking for is a detailed breakdown on how aggregate
> > functions process in the database. kind of a step-by-step=20
> to how they
> > roll through the system"
> >=20
> >=20
> > I know that *I* don't know the answer. But I know someone on this
> > list
> > likely does.
> >=20
> > Help please?
> >=20
> > Rachel
> >=20
> > __________________________________
> > 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
> > -----------------------------------------------------------------
> >=20
> > ----------------------------------------------------------------
> > 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
> > -----------------------------------------------------------------
>=20
>=20
> __________________________________
> 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
> -----------------------------------------------------------------
>=20
>=20
This email contains information intended for
the addressee only. It may be confidential
and may be the subject of legal and/or
professional privilege. Any dissemination,
distribution, copyright or use of this
communication without prior permission of
the sender is strictly prohibited.
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:29:27 CST
