Re: Replacing GROUP BY with Sub-queries

True .. what other posters suggested.
The key is that in select col1, (select ...), the (select ..) subquery will be executed for each parent row returned.

So that would in essence throw a red flag over using that approach when the parent query does not return distinct/unique key id's.

After all using distinct will in most/all cases make the query slower (usig subquery approach) and thus eliminate using that as an altrnative to group by.

Anurag

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1067473473.804043_at_yasure...
> VC wrote:
>
> >Hello Daniel,
> >
> >Your approach is correct -- you've just forgotten the 'distinct' operator to
> >eliminate duplicates from your second query result:
> >
> > SELECT DISTINCT table_name, (
> > SELECT COUNT(*)
> > FROM all_indexes ai2
> > WHERE ai2.table_name = ai1.table_name) TAB_CNT
> > FROM all_indexes ai1;
> >
> >
> >
> >Your second original query behaviour is similar to its analytical
> >counterpart:
> >
> >select table_name, count(*) over (partition by table_name) cnt from
> >all_indexes;
> >
> >although the analytical one is much faster and equivalent to the 'group by'
> >query in the sense of performance.
> >
> >Because of performance implications the scalar subquery is probably
> >interesting only theoretically (as I mentioned elsewhere) or for quick and
> >dirty requests against smallish tables. I've not seen a scalar subquery
> >outperforming its semantic equivalents yet.
> >
> >Rgds.
> >
> >"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> >news:1067466836.934863_at_yasure...
> >
> >
> >>Avarma posted something a few days back that I have tried to turn into a
> >>demo for
> >>my students with very bad results. Here's what I did:
> >>
> >>-- The group by example
> >>
> >>SELECT table_name, COUNT(*) TAB_CNT
> >>FROM all_indexes
> >>GROUP BY table_name;
> >>-- returns 424 rows
> >>
> >>Then I tried to emulate the example and did this.
> >>
> >>SELECT table_name, (
> >> SELECT COUNT(*)
> >> FROM all_indexes ai2
> >> WHERE ai2.table_name = ai1.table_name) TAB_CNT
> >>FROM all_indexes ai1;
> >>-- returns 573 rows
> >>
> >>Unfortunately it does not produce the same result.
> >>
> >>Any help offered will be greatly appreciated. What
> >>I need is a simple demo of a basic group by to a basic
> >>sub-query that runs with all_indexes or a similar DD
> >>view.
> >>
> >>Thank you,
> >>--
> >>Daniel Morgan
> >>http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> >>http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> >>damorgan_at_x.washington.edu
> >>(replace 'x' with a 'u' to reply)
> >>
> >>
> >>
> Thanks to both of you. I copied the sample too closely.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Received on Wed Oct 29 2003 - 22:28:18 CST