Re: Gathering group info in one row

jochen.wiedmann_at_freenet.de (Jochen Wiedmann) wrote in message news:<c66df65e.0409220001.43b620fe_at_posting.google.com>...
> Hi,
>
> we have a multitude of queries that all follow the same scheme, and
> are, unfortunately, tediously slow. Thus, it quite makes some sense to
> optimize them.
>
> The query combines two tables, that are in an 1:n relation. I'll call
> them A
> and B:
>
> Table A id name
> 0 Someone
> 1 Anyone
>
> Table B idA group name
> 0 0 Jim
> 0 0 George
> 0 1 Jack
> 0 2 Sam
>
> There is a predefined set of groups (about six or seven). For any
> group and type, there are between 0 and 6 entries in table B.
>
> The desired query result should look like this:
>
> id name name0 count0 name2 count2 name3 count3
> 0 Someone George 2 Sam 1 <null> 0
> 1 Anyone Bob 1 <null> 0 James 4
>
> In words: Select certain rows from A (that can be quite a lot, several
> thousand). For any such row, and for certain groups, (in the example
> 0, 2, and 3) select
> - the alphabetically first entry in B (nameX), and
> - the number of entries in B (countX)
>
> I have a query, which does the above, but it is tediously slow
> (includes two subselects for any group).
>
>
> Regards,
>
> Jochen
>
>
> P.S: Is c.d.o.misc the right group for this type of questions?

This is the appropriate group.

Run an EXPLAIN PLAN. I'm guessing (since you didn't post your SQL) that you are using two sub queries when one will do. But again that is a WILD A** GUESS.

If you cannot solve it from the EXPLAIN PLAN, then repost with your query source, the PLAN info, and the platform info (Orace version, OS).
Then we may be able to help you.

 Ed. Received on Wed Sep 22 2004 - 13:50:21 CDT