Re: Problem with union in Oracle 7.3

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/23
Message-ID: <34cbfd07.23080467@192.86.155.100>#1/1

On Fri, 23 Jan 1998 14:15:31 +0100, "Hein van Vroonhoven" <Hein.vanVroonhoven_at_ahm.akzonobel.nl> wrote:

>We have two personell tables with a number of equal columns.
>I have made views on each of these tables.
>
>I have to union these views to make some reports. Therefore I made a new
>view.
>When I use the separate tables in the reports there is no problem. But when
>I use the view with the union, the performance decreases dramatically.
>
>I can't merge the two tables. But can anyone help me bypassing this
>problem.
>Is there a way to use the two tables in one SQL statement, but not using
>the UNION operator.
>
>Thanks in Advance.
>
>Hein v. Vroonhoven (Hein.vanVroonhoven_at_AHM.akzonobel.nl)

thats not a lot of info to go on (query plans, table structures, queries would help) but....

Try using a UNION ALL instead of just a UNION view. UNION ALL is a concatention, UNION is not.

For example, consider the following:

SQL> set autotrace on exp
SQL> select * from dual union select * from dual;

D
-
X

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (UNIQUE)

   2    1     UNION-ALL
   3    2       TABLE ACCESS (FULL) OF 'DUAL'
   4    2       TABLE ACCESS (FULL) OF 'DUAL'

SQL> select * from dual union all select * from dual;

D
-
X
X

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 UNION-ALL

   2    1     TABLE ACCESS (FULL) OF 'DUAL'
   3    1     TABLE ACCESS (FULL) OF 'DUAL'

Notice how UNION returns 1 row while UNION ALL returns 2. Also, in the query plan you can see a sort (unique) (expensive) step.

If what you really want is a UNION ALL not a UNION it might help....  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Jan 23 1998 - 00:00:00 CST