Re: Replacing GROUP BY with Sub-queries

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)