Optimizing union based view

From: Mathias Waack <M.Waack_at_gmx.de>
Date: Mon, 03 Mar 2008 13:48:30 GMT
Message-ID: <OkTyj.372$9W3.24816@se2-cb104-9.zrh1.ch.colt.net>


Hi all,

I'm having a performance problem with a view based on the union of two tables:

create table t1(c1 varchar(10), recno number); alter table t1 add primary key (recno);
insert into t1old values ('r1', 1);
insert into t1old values ('r2', 2);
create table t1old(c1 varchar(10), recno number); alter table t1old add primary key (recno); insert into t1old values ('r0', 0);
create view t1view as select * from t1 union all select * from t1old;

The application using this schema accesses records by its 'recno', so a common query is:

select /*+ first_rows(1) */ * from t1 where recno >= 1 order by recno;

Some tables growing very fast, so I've started to expire old records into backup tables, thats why we have t1 and t1old. Thus t1 union all t1old gives us the original tables.

Now starts my problem: if the application runs the same query as above on the view:

select /*+ first_rows(1) */ * from t1view where recno >= 1 order by recno;

Oracle performs a full table scan over both tables after applying the filter, thus we get something like this:

sort
  union all
    select * from t1 where recno >= 1
    select * from t1old where recno >= 1.

Usually the application fetches only one record (thats why the optimizer hint), but in a very few cases it fetches more than one row. In the common case the query is very slow. I know, that

select count(*) from t1view = select count(*) from t1 + select count(*) from t1old

and

select max(recno) from t1old < select min(recno) from t1

I think Oracle needs both information to perform better - but how to tell it?

Mathias Received on Mon Mar 03 2008 - 07:48:30 CST

Original text of this message