Re: How is "UNION ALL " work in ORACLE ?

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Fri, 29 Jan 1999 10:48:57 +0100
Message-ID: <36B18408.4207630F@sybrandb.demon.nl>

Look at the explain plan of this and similar statement (or use set autotrace on explain in sqlplus)
and you will see there are two separate selects, which result in two separate subsets, the two subsets are sortmerged in a final step. It should be possible to execute those two subselects in parallel. This can be forced in several ways

- per instance by setting init.ora parameters
- per table by issuing alter table parallel commands
- on statement level by specifying hints

I would prefer the second method, as this doesn't require Oracle specific features on statement level.

Hth,

Sybrand Bakker, Oracle DBA

yewpc_at_my-dejanews.com wrote:

> Hi,
> I don't understand how actually Oracle execute a SQL statement
> that has UNION ALL condition on it.
> Let say I have the following SQL :
>
> Select a, b, c
> from tb_txn
> where txn_code = 'OLN'
> UNION ALL
> Select a, b, c
> from tb_txn_hist
> where txn_code = 'OLN';
>
> Is it Oracle will execute the 1st select and put the result into
> a buffer first then execute the 2nd select and put the result into
> the same buffer then only fetch all the select records out ?
>
> Is there a way to force it to execute the 1st and 2nd select parallelly ?
>
> What I want is to improve the performance. Currently the txn_code is indexed
> and it may have up to 300 rows of same the value out of 50 millions records.
>
> Anyone have any idea or suggestion ?
>
> Thank you very much .
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Received on Fri Jan 29 1999 - 03:48:57 CST