Re: Gathering group info in one row

From: Jochen Wiedmann <jochen.wiedmann_at_freenet.de>
Date: 23 Sep 2004 01:23:19 -0700
Message-ID: <c66df65e.0409230023.17a42c93@posting.google.com>

ed.prochak_at_magicinterface.com (Ed prochak) wrote in message

> 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.

You get what you want ... I'm simply unsure, that you like it. :-)

Ok, that forces me to use the actual table and column names. So I'll better start with a description of these:

The leading table is "DBAkte" (german abbreviation for "database case") with the primary key "aId". The group tables are "DBBeteiligte" ("database participant") with the columns "aAktenId" (foreign key referencing DBAkte), "aFilter" (group name, for example "Kläger", aka Plaintiff, and "Beklagter", aka Defendant). The columns being searched are "aName", "aVorname" (first name).

The (simplified) query is:

SELECT DBAkte.aId,

      klC.aName AS nameKlaeger, klC.aVorname AS vornameKlaeger,
      klC.NUM AS anzahlKlaeger, beC.aName AS nameBeklagter,
      beC.aVorname AS vornameBeklagter, beC.NUM AS anzahlBeklagter

FROM DBAkte,

    (SELECT klB.NUM, klB.aName, klB.aVorname, klB.aAktenId, klB.aId FROM

        (SELECT COUNT(*) OVER (PARTITION BY aAktenId) AS NUM, MIN(UPPER(aName))
            OVER (PARTITION BY aAktenId) AS MINANAME,
            MIN(UPPER(NVL(aVorname, ' '))) OVER (PARTITION BY aAktenId,
            UPPER(aName)) AS MINAVORNAME, MIN(aId) OVER (PARTITION BY aAktenId,
            UPPER(aName), UPPER(NVL(aVorname,' ')))  AS MINAID,
            klA.aName, klA.aVorname, klA.aAktenId, klA.aId FROM DBBeteiligte klA
            WHERE klA.aFilter='Klaeger') klB
        WHERE (klB.MINAVORNAME=UPPER(NVL(klB.aVorname, ' ')) AND
        klB.MINANAME=UPPER(klB.aName) AND klB.MINAID=klB.aId)) klC,
    (SELECT beB.NUM, beB.aName, beB.aVorname, beB.aAktenId, beB.aId FROM
        (SELECT COUNT(*) OVER (PARTITION BY aAktenId) AS NUM, MIN(UPPER(aName))
            OVER (PARTITION BY aAktenId) AS MINANAME,
            MIN(UPPER(NVL(aVorname, ' '))) OVER (PARTITION BY aAktenId,
            UPPER(aName)) AS MINAVORNAME, MIN(aId) OVER (PARTITION BY aAktenId,
            UPPER(aName), UPPER(NVL(aVorname,' ')))  AS MINAID, beA.aName,
            beA.aVorname, beA.aAktenId, beA.aId FROM DBBeteiligte beA
            WHERE beA.aFilter='Beklagter') beB
        WHERE (beB.MINAVORNAME=UPPER(NVL(beB.aVorname, ' ')) AND
        beB.MINANAME=UPPER(beB.aName) AND beB.MINAID=beB.aId)) beC

WHERE klC.aAktenId(+)=DBAkte.aId AND beC.aAktenId(+)=DBAkte.aId

An "EXPLAIN PLAN" returns (all null columns and time stamps omitted, editions for readability):

OPERATION | OPTIONS| OBJNAME |OBJINST.|OBJTYPE| OPTIM. | ID | PID | POS
SELECT    |        |         |        |       | CHOOSE | 0  |     | 848	
HASH JOIN | OUTER  |         |        |       |        | 1  | 0	  | 1	
HASH JOIN | OUTER  |         |        |       |        | 2  | 1	  | 1	
INDEX     | FAST   | aId     |        |UNIQUE |ANALYZED| 3  | 2	  | 1	
            FULL
            SCAN
VIEW      |        |         | 3      |       |        | 4  | 2	  | 2	
WINDOW	  | SORT   |         |	      |       |        | 5  | 4	  | 1	
TBL ACCESS| FULL   | DBBET.  | 4      |       |ANALYZED| 6  | 5	  | 1	
VIEW      |        |         | 6      |       |        | 7  | 1	  | 2	
WINDOW    | SORT   |         |	      |       |        | 8  | 7	  | 1	
TBL ACCESS| FULL   | DBBET.  | 7      |       |ANALYZED| 9  | 8	  | 1

Received on Thu Sep 23 2004 - 03:23:19 CDT