Re: Views

From: Michael Ho <infoage_at_hk.super.net>
Date: 1997/04/04
Message-ID: <3343DFB3.E58@hk.super.net>#1/1

Ken Whitaker wrote:
>
> Have an interesting problem,
>
> select count(*) from table1; 2mins ~1 millon rows
>
> select count(*) from table2; 1min 10 sec ~500,000 rows
>
> view #1 select * from table1 union select * from table2
>
> select count(*) from view#1; 10mins ~1.5 Millons rows
>
> view #2 select * from table1 union all select * from table2
>
> select count(*) from view#2; 7mins ~1.5 Millons rows
>
> Well one may conclude the sga too small etc; both tables inserted into a
> big one.
>
> select count(*) from tab#1_tab#2; 3mins ~1.5 millon rows
>
> Any one have any thoughts on views (Oracle 7.2.3 HPUX 9.x)
>
> kwhitake_at_moon.jic.com

You try explaining the query, you will understand more than I explain.

Anyway :
When you use set operation, Oracle will form temp area with the content from
both table and count the record in temp area.

If you use UNION ALL it save the time Oracle sorting the temp area. Received on Fri Apr 04 1997 - 00:00:00 CST