Re: View based on union fills up temporary tablespace

A copy of this was sent to "Primoz Javornik" <primozj_at_hotmail.com> (if that email address didn't require changing) On Fri, 15 Oct 1999 16:52:27 -0700, you wrote:

>Hi,
>
>I have created view which uses UNION on two tables. Table1 has more than 3.4
>million rows and Table2 is approx. 10% of Table1 size. When I select
>count(*) from Table1, query executes 26 minutes an temporary tablespace
>usage does not increase. When I select count(*) from query with UNION,
>temporary tablespace usage is increasing till query fails due to
>insufficient space.
>
>Oracle version is 7.3.4 on HP-UX
>
>Any ideas?
>
>Primoz
>

perhaps the problem you are seeing is that

select * from A union select * from B

is really:

A + ( B-A )

A union removes anything from B that is in A. In order to do that -- it'll require lots of temp space to create this 'b-a'.

Union All does not do that.

I would try:

select cnt1+cnt2
  from ( select count(*) cnt1 from A ),

       ( select count(*) cnt2 from B )
/

instead
instead.

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

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