RE: Re[4]: HI

From: Singer, Phillip (P.W.) <psinger1_at_ford.com>
Date: Mon, 20 Sep 2004 14:38:08 -0400
Message-ID: <A45063A7D336504580F0161CEB7FEBE204547D86@na1fcm60.dearborn.ford.com>

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Lex de Haan
> Sent: Monday, September 20, 2004 1:54 PM
> To: jonathan_at_gennick.com
> Cc: oracle-l_at_freelists.org
> Subject: RE: Re[4]: HI

>=20
>=20

> actually, although the optimizer is one of my hobbies,
> and I happen to know a little bit about the Oracle optimizer,
> in this case I am mainly interested in language elegance
> and not in performance, for a change ;-)
> but anyway, I am indeed looking for GROUP BY statements
> (completely specified, including data structures and constraints)
> to check whether they can be rewritten without a GROUP BY.

>=20

> not only correlated subqueries in the SELECT clause come to mind,
> but also the analytical functions applied on row windows
> (both features being fully ANSI/ISO standard, by the way)
>=20

O.K., I'll bite.

I have always been under the opinion that

SELECT col1, col2, col3, aggregate function() from foo group by col1, col2, col3

was equivalent to (gave the same results as)

SELECT distinct col1, col2, col3, same_thing_but_the_analytic = function_version()=20
over (partition by col1, col2, col3)

However, I have never used the latter syntax (except if I really needed the analytic function) because (1) nobody else around would understand = it, and
(2) I was never that comfortable with it myself. So, I really don't = know how far
off the truth I am.

But I do know that if Lex and Johnathan were to expand this into a full length article I would finally feel really comfortable using them. =20

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 20 2004 - 13:34:01 CDT