Re: UNION ALL is SLOW

Hi Chris L. Mason wrote:

> I have found that using UNION ALL, while significantly faster than
> UNION, still seems to take much longer than it should.
>
> Consider the following query:
>
> SELECT a, b FROM table1
> UNION ALL
> SELECT c, d FROM table2
>
> The first select query by itself takes 8.6 seconds, the second takes
> 1.7 seconds. The whole query take 38 seconds! The total number of
> rows returned is only 20,000. (btw, with UNION, it takes about
> 65 seconds)
>
> Also, note that table1 and table2 aren't just single tables, but more
> complicated queries using inline functions and subqueries. The first
> column is a NUMBER in both queries and the second is a VARCHAR2.
>
> Why would the UNION ALL take so long? Is there any other way to specify
> the concatenation of rows from two queries (while doing *no* processing
> or analysis)?
>
> I'm using Oracle 8.0.3.0 on Solaris 2.5.1

Check for any implicit conversion there could occur.

IF :

select a , b from table1 -- where a is number ,b is varchar2 UNION ALL
select c , d from table2 -- where c is varchar2 , d is number

you would have double conversion ( a to char and d to char )

Difference between UNION and UNION ALL is clear : UNION hast to compare any row in SET 1 against any row in SET 2 for not to double a row, whilst in UNION ALL every row in SET 1 and 2 is already a hit and no comparison is needed.

Regards
 Robo.

Received on Sun Jun 21 1998 - 12:15:24 CDT