Re: Long query execution times for union

Select * from T
union
Select * from T

will return the contents of table T.

select * from T
union ALL
select * from T

will return 2 times the contents of table T.

In your example, the first query is executed, then each row in the second query is checked against the rows in the first to make sure it hasn't already been seen.

If you want to concatenate the two result sets, use "UNION ALL" and no such filter will take place. The performance hit is the "UNION" of the two result sets which is really equal to QUERY1 + (QUERY2-QUERY1).

On Tue, 13 Aug 1996 15:36:25 -0500, "C. Eric Ladner" <clad_at_chevron.com> wrote:

>I've got two selects that are unioned together. If I execute the two
>selects seperatly, they each execute in about 5 seconds. Together (in a
>view with a union) they take for ever (10+ minutes). What's the deal
>with that?
>
>I am adding an extra condition to the selects which I'm also adding to
>the view. But, the name of the column that I'm restricting is different
>in each view. How does that work anyway?
>
>Basically I've got this:
>
>create view foo as
> select calendar_date, pasbal_code, value (etc, etc..)
> union
> select reading_date, pasbal_code, value (etc, etc..)
>;
>
>The reading/calendar date is the field I'm searching on. In the
>description of the view, it shows up as calendar_date.
>
>Any hints, clues, suggestions?
>
>========================================================================
>Eric Ladner | UNIX/Oracle/Passport Sys-Administration, General
>clad_at_chevron.com | Applications Development, etc., etc.
>========================================================================

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Tue Aug 13 1996 - 00:00:00 CEST