RE: View merging

From: Sam P. Roberts (ZADCO ITIS) <roberts_at_zadco.co.ae>
Date: Wed, 24 Jan 2001 07:08:15 +0400
Message-Id: <10750.127338@fatcity.com>

A quirk of the optimizer,ive noticed this, if you create views on views tends to go for full table scan.

sam

-----Original Message-----
From: Djordje Jankovic [mailto:djankovic_at_corp.attcanada.ca] Sent: Tuesday, January 23, 2001 7:56 PM
To: Multiple recipients of list ORACLE-L Subject: 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 ?

Thanks.

Djordje

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Djordje Jankovic
INET: djankovic_at_corp.attcanada.ca

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jan 23 2001 - 21:08:15 CST