I have a view:

create view all_alerts as
select 1 alert_level,alert_time,alert_details from alert_level_1
union all
select 2 alert_level,alert_time,alert_details from alert_level_2
union all
select 3 alert_level,alert_time,alert_details from alert_level_3;

when I query:

select * from (
select * from all_alerts where alert_level=3
order by alert_time desc);

it takes 5 seconds to get 1000 rows;

when I query to get the last 10 alerts:

select * from (
select * from my_view where alert_level=3
order by alert_time desc) where rownum<=10;

it takes 2 minutes (!);

It seems that the alert_level=3 predicate isn't being pushed into the view when the rownum is also there,
so all 3 tables are being queried.

I tried to use push_pred/merge hints but no luck.

Anyone?

Yoni

Edited by: yonim@acs.co.il on May 4, 2009 4:55 PM

These sqls are just pseuds. Is there a reason for rownum<=x to make oracle not push the inner predicates?

You close your own threads here.