UNION ALL is SLOW

From: Chris L. Mason <cmason_at_wyrex.com>
Date: 21 Jun 1998 16:36:15 GMT
Message-ID: <6mjcpv$hn8$1@demon.uunet.ca>

Hi,

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

Thanks for any suggestions,

Chris Received on Sun Jun 21 1998 - 11:36:15 CDT