Re: How to get a distinct count of result set of multople table joins?

From: Chris ( Val ) <chrisval_at_gmail.com>
Date: Wed, 27 Feb 2008 13:48:45 -0800 (PST)
Message-ID: <1d6fae53-8c5f-4cf2-a66e-64b9463db4ec@e6g2000prf.googlegroups.com>

On Feb 28, 12:05 am, Ed Prochak <edproc..._at_gmail.com> wrote:
> On Feb 26, 11:26 pm, "Chris ( Val )" <chris..._at_gmail.com> wrote:
>
> > Hi everyone,
>
> > I haven't played with SQL for a while, but the following SQL returns
> > ~700,000 records ( a lot of duplicates ), and I used a UNION with the
> > same query to reduce the records down to ~250,000. Is there a more
> > efficient solution, as the UNION takes some time to run? ~3 mins.
> []
>
> > Thanks in advance,
> > Chris
>
> What does the EXPLAIN PLAN show?
>
> Performance issues are always data dependent. IOW, the phrase:
> KNOW THY DATA
> is one to take to heart.
>
> Come back with the PLAN and we may have some suggestions.
>    Ed

Hi Ed,

Please note that performance is of secondary importance to me. Being able to
obtain a correct count of the rows returned by the UNION query is my main
concern - In any case, here is the PLAN as requested:

Execution Plan

---

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11436 Card=48542 Bytes=38445264)

   1	0   SORT (UNIQUE) (Cost=11436 Card=48542 Bytes=38445264)
   2	1     UNION-ALL
   3	2	HASH JOIN (RIGHT OUTER) (Cost=1668 Card=24271 Bytes=19222632)

   4	3	  TABLE ACCESS (FULL) OF 'TBL_PARENT' (TABLE) (Cost=21

Card=4043 Bytes=384085)

   5 3 HASH JOIN (RIGHT OUTER) (Cost=1647 Card=19120 Bytes=13326640)

   6 5 TABLE ACCESS (FULL) OF 'TBL_CUSTOM1' (TABLE) (Cost=3 Card=79 Bytes=5609)

   7	5	    FILTER
   8	7	      HASH JOIN (RIGHT OUTER) (Cost=1643 Card=15974

Bytes=9999724)

   9 8 TABLE ACCESS (FULL) OF 'TBL_CUSTOM2' (TABLE)(Cost=3 Card=75 Bytes=5250)

  10 8 HASH JOIN (RIGHT OUTER) (Cost=1639 Card=14696 Bytes=8170976)

  11 10 TABLE ACCESS (FULL) OF 'TBL_ENTITY' (TABLE) (Cost=55 Card=9326 Bytes=1147098)

  12 10 HASH JOIN (RIGHT OUTER) (Cost=1331 Card=9092 Bytes=3936836)

  13 12 TABLE ACCESS (FULL) OF 'TBL_ACCOUNT' (TABLE) (Cost=55 Card=10746 Bytes=988632)

  14 12 HASH JOIN (RIGHT OUTER) (Cost=1116 Card=6303 Bytes=2149323)

  15 14 TABLE ACCESS (FULL) OF 'TBL_PERIOD' (TABLE) (Cost=3 Card=19 Bytes=228)

  16   14		      FILTER
  17   16			HASH JOIN (RIGHT OUTER) (Cost=1113 Card=6303

Bytes=2073687)

  18 17 TABLE ACCESS (FULL) OF 'TBL_VIEW' (TABLE) (Cost=3 Card=5 Bytes=85)

  19 17 HASH JOIN (Cost=1109 Card=6303 Bytes =1966536)

  20 19 TABLE ACCESS (FULL) OF 'TBL_CUSTOM3' (TABLE) (Cost=14 Card=2 Bytes=184)

  21 19 HASH JOIN (Cost=1094 Card=57332 Bytes=12613040)

  22 21 TABLE ACCESS (FULL) OF 'TBL_CUSTOM4' (TABLE) (Cost=3 Card=1 Bytes=66)

  23 21 HASH JOIN (Cost=1090 Card=90776Bytes=13979504)

  24 23 TABLE ACCESS (FULL) OF 'TBL_VALUE' (TABLE) (Cost=3 Card=1 Bytes=22)

  25 23 HASH JOIN (Cost=1085 Card=272327 Bytes=35947164)

  26 25 TABLE ACCESS (FULL) OF 'TBL_SCENARIO' (TABLE) (Cost=3 Card=1 Bytes=48)

  27 25 HASH JOIN (Cost=1079 Card=272327 Bytes=22875468)

  28 27 TABLE ACCESS (FULL) OF 'TBL_ICP' (TABLE) (Cost=6 Card=1 Bytes=26)

  29 27 TABLE ACCESS (FULL) OF 'TBL_FACT' (TABLE) (Cost=1069 Card=544653 Bytes=31589874)

  30 2 HASH JOIN (RIGHT OUTER) (Cost=1668 Card=24271 Bytes=19222632)

  31 30 TABLE ACCESS (FULL) OF 'TBL_PARENT' (TABLE) (Cost=21 Card=4043 Bytes=384085)

  32 30 HASH JOIN (RIGHT OUTER) (Cost=1647 Card=19120 Bytes=13326640)

  33 32 TABLE ACCESS (FULL) OF 'TBL_CUSTOM1' (TABLE) (Cost=3 Card=79 Bytes=5609)

  34   32	    FILTER
  35   34	      HASH JOIN (RIGHT OUTER) (Cost=1643 Card=15974

Bytes=9999724)

  36 35 TABLE ACCESS (FULL) OF 'TBL_CUSTOM2' (TABLE)(Cost=3 Card=75 Bytes=5250)

  37 35 HASH JOIN (RIGHT OUTER) (Cost=1639 Card=14696Bytes=8170976)

  38 37 TABLE ACCESS (FULL) OF 'TBL_ENTITY' (TABLE) (Cost=55 Card=9326 Bytes=1147098)

  39 37 HASH JOIN (RIGHT OUTER) (Cost=1331 Card=9092Bytes=3936836)

  40 39 TABLE ACCESS (FULL) OF 'TBL_ACCOUNT' (TABLE) (Cost=55 Card=10746 Bytes=988632)

  41 39 HASH JOIN (RIGHT OUTER) (Cost=1116 Card=6303 Bytes=2149323)

  42 41 TABLE ACCESS (FULL) OF 'TBL_PERIOD' (TABLE) (Cost=3 Card=19 Bytes=228)

  43   41		      FILTER
  44   43			HASH JOIN (RIGHT OUTER) (Cost=1113 Card=6303

Bytes=2073687)

  45 44 TABLE ACCESS (FULL) OF 'TBL_VIEW'(TABLE) (Cost=3 Card=5 Bytes=85)

  46 44 HASH JOIN (Cost=1109 Card=6303 Bytes=1966536)

  47 46 TABLE ACCESS (FULL) OF 'TBL_CUSTOM3' (TABLE) (Cost=14 Card=2 Bytes=184)

  48 46 HASH JOIN (Cost=1094 Card=57332 Bytes=12613040)

  49 48 TABLE ACCESS (FULL) OF 'TBL_CUSTOM4' (TABLE) (Cost=3 Card=1 Bytes=66)

  50 48 HASH JOIN (Cost=1090 Card=90776Bytes=13979504)

  51 50 TABLE ACCESS (FULL) OF 'TBL_VALUE' (TABLE) (Cost=3 Card=1 Bytes=22)

  52 50 HASH JOIN (Cost=1085 Card=272327 Bytes=35947164)

  53 52 TABLE ACCESS (FULL) OF 'TBL_SCENARIO' (TABLE) (Cost=3 Card=1 Bytes=48)

  54 52 HASH JOIN (Cost=1079 Card=272327 Bytes=22875468)

  55 54 TABLE ACCESS (FULL) OF 'TBL_ICP' (TABLE) (Cost=6 Card=1 Bytes=26)

  56 54 TABLE ACCESS (FULL) OF 'TBL_FACT' (TABLE) (Cost=1069 Card=544653 Bytes=31589874)

Thanks for your help.

--
Chris

Received on Wed Feb 27 2008 - 15:48:45 CST