Re: Replacing GROUP BY with Sub-queries

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)
>
Received on Wed Oct 29 2003 - 17:40:09 CST