Re: Views

From: Guy Harrison <gharriso_at_werple.net.au>
Date: 1997/03/31
Message-ID: <01bc3d8c$1f88b520$2d2a11cb@gharriso>#1/1

Ken,

What might be happening is that the optimizer is using a unique index to perform the count(*) queries on table1 and table2. When you create a union view, the optimizer can't resolve this using just the indexes and so has to perform full table scans instead of full index scans. I did a quick test and this is what happens on my 7.2.3 system.

The UNION ALL outperforms the UNION query because UNION requires a sort of all rows retrieved in order to eliminate duplicates.

Regards,

Guy

-- 
Guy Harrison

gharriso@werple.net.au || http://werple.net.au/~gharriso || 613 419377964



Ken Whitaker <kwhitake_at_moon.jic.com> wrote in article
<01bc3b0a$18f125a0$43cf87d0_at_kwhitake.jic.om>...


> 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

> 

> 

> 

>

Received on Mon Mar 31 1997 - 00:00:00 CST