Cost based optomizer problem. CBO won't merge view

[Mario]

I'm having a serious problem with the cost based optimizer in Oracle
8i.

I've created a view off of a couple of tables using a "union all".
i.e.
create view a_anti_a_view
select a.id1, b.name from a, b where a.id1 = b.id
union all
select a.id2, b.name2 from a, b where a.id2 = b.id


Table 'a' has 100K rows but 'b' has < 300.

when I run the following query I get a 50msec response:
select *
from a_anti_a_view av
where av.asset_id = 822775;

however this one takes around 2 minutes.
select *
from a_anti_a_view av
where av.asset_id in (select id from temp_as);

Even though temp_as has only one row!


Looking at the explain plan it's clear the optomizer isn't merging
the sub select into the view! It therefore does full table scans on
the selects within the view and then hash-joins to the temp_as table.

Has anyone had this problem? Does anybody have any suggestions?

I've tried a number of things, including using hints (although I'm
not very familiar with them), analyzing the tables and indexes and
doing a join instead of an 'in' operation. none help.

Any help would be fantastic. This is a large project and I'm pretty
far under the gun.

[Geomancer]

Re: Cost based optomizer problem. CBO won't merge view

select *

from a_anti_a_view av
where av.asset_id in (select id from temp_as);

You might try re-writing query as standard join and force a nested loop join:


select /*+ use_nl */
col_list
from
a_anti_a_view av,
temp_as t
where
av.asset_id =t.temp_as;