I have a view that incorporates a union all.

create view1 as
query1
union all
query2

if query1 returns no rows and query2 returns 500000 rows then

select * from view1

returns 500000 rows

and
select * from view1, table1
where view1.key = table1.key

returns 500000 rows

and
select * from view1, table2
where view1.key2 = table2.key2

returns 500000 rows

however,

select * from view1, table1, table2
where view1.key = table1.key
and view1.key2 = table2.key2

returns NO rows

On the other hand,
if the view is written thus,

create view1 as
query2
union all
query1

and query1 still returns no rows while query2 still returns 500000
then the previous query:

select * from view1, table1, table2
where view1.key = table1.key
and view1.key2 = table2.key2

returns the 500000 rows that it should.

Can anyone tell me what to do about this? It seems that Oracle is ignoring the second query in the UNION when the first returns no rows. The problem does seem to resolve if I give it a hint of --+ RULE, but then it is understandably VERY slow.

Thank you