View merging

Hi everybody,

I have a weird problem. A query does not want to merge a where clause with the view (as far as I know this should be default behavior).

So, I have something like

create view xxxx as

   select table_a.field1, table_a.field_c, table_b.field2
     from table_a, table_b
    where table_a.field_a = table_b.field_b;

When I run the query against the view:

select *
  from xxxx
 where field_c = 'aaa';

where field_c is an indexed field from table_a, oracle does not use the index.

However when I run (add the where clause to the view defining query):

select table_a.field1, table_b.field2
  from table_a, table_b
 where table_a.field_a = table_b.field_b
   and table_a.field_c = 'aaa';

The index is used.

I tried both COST and RULE optimizer, tried giving hints (including the /*+ MERGE */ ) but nothing helped.

Any ideas, suggestions ? Received on Tue Jan 23 2001 - 09:57:24 CST