Data Warehousing: "Union" vs. "Union All"

From: Brian P. Mac Lean <brian.maclean_at_sendero.fiserv.com>
Date: 1997/12/23
Message-ID: <34A01D6B.69A47E97@sendero.fiserv.com>

I have been taking a serious look at using partition views (multiple period tables joined using a select/union view). All the books I look at say to use the "Union All" in the view definitions instead of just "Union". You would think that the "Union" operation would take longer than "Union All" because of the extra de-duping done to eliminate duplicate rows. NOT! For selective queries the "Union All" can take 10% to 20% longer, for full table scans it can be 100% longer.

QUESTIONS:
1) If I can guarantee that I have no duplicate rows, is there any reason that I should continue to use the "Union All" in the view definition? 2) Anybody know why Iâ€™m seeing â€œUnion Allâ€ taking longer? 3) Anybody working with the Oracle8 â€œpartition Tableâ€ feature? Have any timings?

Below are the details of what I see as relevant parameters, and two queries with their execution plans, timings, and stats. The examples are the same except for the first query uses a â€œUnionâ€ view while the second query uses a â€œUnion Allâ€ view. The first query is 50% faster than the second (see the timing results inline):

All (24) tables and (48) indexes have been analyzed; optimizer_mode = choose;
partition_view_enabled = true;
v733_plans_enabled = true;
compatible = 7.3 (Oracle version is 7.3.2 for HP_UX 10.10); using 24 period tables all around 20meg for a total size of 540meg;

UNION VIEW VFACT_P_01_24_U ---------
UNION VIEW VFACT_P_01_24_U ---------
UNION VIEW VFACT_P_01_24_U --------- SQL> select /*+ COST */ 2 br_nbr, acct_nbr, to_char(trunc(prd_date),'YYYYMMDD'), trns_type, 3 sum(trns_total), avg(trns_total), sum(trns_cnt), avg(trns_cnt) 4 from VFACT_P_01_24_U 5* group by br_nbr, acct_nbr, to_char(trunc(prd_date),'YYYYMMDD'), trns_type

23948 rows selected.

real: 886985

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=162294 Card=5601600 Bytes=829036800)

1 0 SORT (GROUP BY)
2 1 VIEW OF 'VFACT_P_01_24_U' (Cost=162294 Card=5601600 Bytes=829036800)

   3    2       SORT (UNIQUE)
   4    3         UNION-ALL
   5    4           TABLE ACCESS (FULL) OF 'FACT_P_01' (Cost=451
Card=195200 Bytes=16396800)
   6    4           TABLE ACCESS (FULL) OF 'FACT_P_02' (Cost=469
Card=203200 Bytes=16865600)
   7    4           TABLE ACCESS (FULL) OF 'FACT_P_03' (Cost=468
Card=202800 Bytes=17035200)
   8    4           TABLE ACCESS (FULL) OF 'FACT_P_04' (Cost=942
Card=409600 Bytes=34406400)
   9    4           TABLE ACCESS (FULL) OF 'FACT_P_05' (Cost=466
Card=202200 Bytes=16782600)
  10    4           TABLE ACCESS (FULL) OF 'FACT_P_06' (Cost=442
Card=191600 Bytes=15902800)
  11    4           TABLE ACCESS (FULL) OF 'FACT_P_07' (Cost=457
Card=198200 Bytes=16648800)
  12    4           TABLE ACCESS (FULL) OF 'FACT_P_08' (Cost=467
Card=202400 Bytes=17001600)
  13    4           TABLE ACCESS (FULL) OF 'FACT_P_09' (Cost=924
Card=402000 Bytes=33366000)
  14    4           TABLE ACCESS (FULL) OF 'FACT_P_10' (Cost=459
Card=198600 Bytes=16682400)
  15    4           TABLE ACCESS (FULL) OF 'FACT_P_11' (Cost=458
Card=198600 Bytes=16682400)
  16    4           TABLE ACCESS (FULL) OF 'FACT_P_12' (Cost=458
Card=198600 Bytes=16682400)
  17    4           TABLE ACCESS (FULL) OF 'FACT_P_13' (Cost=451
Card=195200 Bytes=16396800)
  18    4           TABLE ACCESS (FULL) OF 'FACT_P_14' (Cost=934
Card=406400 Bytes=33731200)
  19    4           TABLE ACCESS (FULL) OF 'FACT_P_15' (Cost=468
Card=202800 Bytes=17035200)
  20    4           TABLE ACCESS (FULL) OF 'FACT_P_16' (Cost=473
Card=204800 Bytes=17203200)
  21    4           TABLE ACCESS (FULL) OF 'FACT_P_17' (Cost=466
Card=202200 Bytes=16782600)
  22    4           TABLE ACCESS (FULL) OF 'FACT_P_18' (Cost=442
Card=191600 Bytes=15902800)
  23    4           TABLE ACCESS (FULL) OF 'FACT_P_19' (Cost=911
Card=396400 Bytes=33297600)
  24    4           TABLE ACCESS (FULL) OF 'FACT_P_20' (Cost=467
Card=202400 Bytes=17001600)
  25    4           TABLE ACCESS (FULL) OF 'FACT_P_21' (Cost=464
Card=201000 Bytes=16683000)
  26    4           TABLE ACCESS (FULL) OF 'FACT_P_22' (Cost=458
Card=198600 Bytes=16682400)
  27    4           TABLE ACCESS (FULL) OF 'FACT_P_23' (Cost=458
Card=198600 Bytes=16682400)
  28    4           TABLE ACCESS (FULL) OF 'FACT_P_24' (Cost=458

Card=198600 Bytes=16682400)

Statistics

        292  recursive calls
       1855  db block gets
     134208  consistent gets
     135796  physical reads
       5461  redo size
    1002673  bytes sent via SQL*Net to client
      11637  bytes received via SQL*Net from client
       1599  SQL*Net roundtrips to/from client
          0  sorts (memory)
          2  sorts (disk)
      23948  rows processed





-------- UNION VIEW VFACT_P_01_24_UA ---------

UNION VIEW VFACT_P_01_24_UA ---------
UNION VIEW VFACT_P_01_24_UA --------- 1 select /*+ COST */ 2 br_nbr, acct_nbr, to_char(trunc(prd_date),'YYYYMMDD'), trns_type, 3 sum(trns_total), avg(trns_total), sum(trns_cnt), avg(trns_cnt) 4 from VFACT_P_01_24_UA 5* group by br_nbr, acct_nbr, to_char(trunc(prd_date),'YYYYMMDD'), trns_type

23948 rows selected.

real: 1660988

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12911 Card=5601600 Bytes=470534400)

1 0 SORT (GROUP BY)
2 1 VIEW OF 'VFACT_P_01_24_UA' (Cost=12911 Card=5601600 Bytes=470534400)

   3    2       UNION-ALL (PARTITION)
   4    3         TABLE ACCESS (FULL) OF 'FACT_P_01' (Cost=451
Card=195200 Bytes=14444800)
   5    3         TABLE ACCESS (FULL) OF 'FACT_P_02' (Cost=469
Card=203200 Bytes=15036800)
   6    3         TABLE ACCESS (FULL) OF 'FACT_P_03' (Cost=468
Card=202800 Bytes=15007200)
   7    3         TABLE ACCESS (FULL) OF 'FACT_P_04' (Cost=942
Card=409600 Bytes=30310400)
   8    3         TABLE ACCESS (FULL) OF 'FACT_P_05' (Cost=466
Card=202200 Bytes=14962800)
   9    3         TABLE ACCESS (FULL) OF 'FACT_P_06' (Cost=442
Card=191600 Bytes=14178400)
  10    3         TABLE ACCESS (FULL) OF 'FACT_P_07' (Cost=457
Card=198200 Bytes=14666800)
  11    3         TABLE ACCESS (FULL) OF 'FACT_P_08' (Cost=467
Card=202400 Bytes=14977600)
  12    3         TABLE ACCESS (FULL) OF 'FACT_P_09' (Cost=924
Card=402000 Bytes=29748000)
  13    3         TABLE ACCESS (FULL) OF 'FACT_P_10' (Cost=459
Card=198600 Bytes=14696400)
  14    3         TABLE ACCESS (FULL) OF 'FACT_P_11' (Cost=458
Card=198600 Bytes=14696400)
  15    3         TABLE ACCESS (FULL) OF 'FACT_P_12' (Cost=458
Card=198600 Bytes=14696400)
  16    3         TABLE ACCESS (FULL) OF 'FACT_P_13' (Cost=451
Card=195200 Bytes=14444800)
  17    3         TABLE ACCESS (FULL) OF 'FACT_P_14' (Cost=934
Card=406400 Bytes=30073600)
  18    3         TABLE ACCESS (FULL) OF 'FACT_P_15' (Cost=468
Card=202800 Bytes=15007200)
  19    3         TABLE ACCESS (FULL) OF 'FACT_P_16' (Cost=473
Card=204800 Bytes=15155200)
  20    3         TABLE ACCESS (FULL) OF 'FACT_P_17' (Cost=466
Card=202200 Bytes=14962800)
  21    3         TABLE ACCESS (FULL) OF 'FACT_P_18' (Cost=442
Card=191600 Bytes=14178400)
  22    3         TABLE ACCESS (FULL) OF 'FACT_P_19' (Cost=911
Card=396400 Bytes=29333600)
  23    3         TABLE ACCESS (FULL) OF 'FACT_P_20' (Cost=467
Card=202400 Bytes=14977600)
  24    3         TABLE ACCESS (FULL) OF 'FACT_P_21' (Cost=464
Card=201000 Bytes=14874000)
  25    3         TABLE ACCESS (FULL) OF 'FACT_P_22' (Cost=458
Card=198600 Bytes=14696400)
  26    3         TABLE ACCESS (FULL) OF 'FACT_P_23' (Cost=458
Card=198600 Bytes=14696400)
  27    3         TABLE ACCESS (FULL) OF 'FACT_P_24' (Cost=458

Card=198600 Bytes=14696400)

Statistics

        146  recursive calls
        710  db block gets
     134163  consistent gets
     134684  physical reads
       2695  redo size
    1009195  bytes sent via SQL*Net to client
      11638  bytes received via SQL*Net from client
       1599  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
      23948  rows processed

MAILTO:brian.maclean_at_ips-sendero.com Received on Tue Dec 23 1997 - 00:00:00 CST