Re: Concurrent table joining in Oracle

The developers are not correct. Not a bit.

You can show them a simple demonstration....

SQL> create or replace function stop_me
  2 return number parallel_enable is
  3 begin
  4 dbms_lock.sleep(0.1);
  5 return null;
  6 end;
  7 /

Function created.

SQL> create table t pctfree 90 pctused 10 as   2 select rpad('x', '200', 'x') x
  3 from dual
  4 connect by level <= 20;

Table created.

So if their theory is correct, the following select will execute in TWO seconds:

SQL> set timing on
SQL> select *
  2 from (
  3 select stop_me from t
  4 union all
  5 select stop_me from t
  6 ) v;

40 rows selected.

Elapsed: 00:00:04.29

...not a chance.

to make it parallel you need ... a parallel hint :)

SQL> select /*+ parallel(v 2) */ *
  2 from (
  3 select stop_me from t
  4 union all
  5 select stop_me from t
  6 ) v;

40 rows selected.

Elapsed: 00:00:02.28

On 6/6/07, Herring Dave - dherri <Dave.Herring_at_acxiom.com> wrote:
>
> Folks,
>
> Their response was "I always thought UNION and UNION ALL perform each
> UNION-ed set in parallel".
>
> Dave
>
>
>
> *_________**__________________________*
>
> *David C. Herring, DBA * | A c x i o m Delivery Center Organization
>
> 630-944-4762 *office** *| 630-430-5988 *wireless* | 630-944-4989 *fax***
>
>
>
> *************************************************************************
> The information contained in this communication is confidential, is
> intended only for the use of the recipient named above, and may be
> legally privileged.
>
> If the reader of this message is not the intended recipient, you are
> hereby notified that any dissemination, distribution or copying of this
> communication is strictly prohibited.
>
> If you have received this communication in error, please resend this
> communication to the sender and delete the original message or any copy
> of it from your computer system.
>
> Thank you.
> *************************************************************************
>
>
>

-- 
Alexander Fatkulin

--
http://www.freelists.org/webpage/oracle-l