Cost based optomizer problem. CBO won't merge view

From: Mario <googlegroups_at_davirro.org>
Date: 27 Jun 2003 13:28:39 -0700
Message-ID: <99a49a42.0306271228.282fe713_at_posting.google.com>


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. Received on Fri Jun 27 2003 - 22:28:39 CEST

Original text of this message