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

Hi Brian,

I've been using partition views for a while on Oracle 7.3.2.3.1 and 7.3.3.3 and I would recommend that you take a very close look at them, as they have some entertaining features.

In your example, I would suspect that the sort is making the group by more efficient in some way - are you using parallel query ?

Also, be aware that the view in your initial example is not a partition view - the execution plan does not include the 'PARTITION' keyword, so I would not expect queries using the partition column to eliminate partitions.

Regards

John Roberts

Brian P. Mac Lean <brian.maclean_at_sendero.fiserv.com> wrote in article <34A01D6B.69A47E97_at_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 Sat Dec 27 1997 - 00:00:00 CST