Message-Id: <10750.127290@fatcity.com> From: "Mohan, Ross" Date: Tue, 23 Jan 2001 12:01:30 -0500 Subject: RE: View merging This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C0855E.21C5B900 Content-Type: text/plain; charset="iso-8859-1" some versions of the optimizer need a tweak...or tweak removed. check the v$parameter for something like "complex_view_merging" and see if, with a bit of experimentation, it works wonders. this parameter is part of a "package" of parameters ( something like "new_optimizer_features"?) and you don't need to set the whole package, but can "shop" within it... hth Ross Mohan -----Original Message----- From: Koivu, Lisa [mailto:lkoivu@qode.com] Sent: Tuesday, January 23, 2001 11:42 AM To: Multiple recipients of list ORACLE-L Subject: RE: View merging Have you tried writing another view with all the joins in it? Put the index hint in the view and it should work. Views can screw up the optimizer. This is one case. -----Original Message----- From: Djordje Jankovic [ mailto:djankovic@corp.attcanada.ca ] Sent: Tuesday, January 23, 2001 10:56 AM 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@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@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). ------_=_NextPart_001_01C0855E.21C5B900 Content-Type: text/html; charset="iso-8859-1" RE: View merging
some versions of the optimizer need a tweak...or tweak removed.
 
check the v$parameter for something like "complex_view_merging" and see
if, with a bit of experimentation, it works wonders.
 
this parameter is part of a "package" of parameters ( something like "new_optimizer_features"?)
and you don't need to set the whole package, but can "shop" within it...
 
hth
 
Ross Mohan
-----Original Message-----
From: Koivu, Lisa [mailto:lkoivu@qode.com]
Sent: Tuesday, January 23, 2001 11:42 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: View merging

Have you tried writing another view with all the joins in it?  Put the index hint in the view and it should work.

Views can screw up the optimizer.  This is one case.


-----Original Message-----
From: Djordje Jankovic [mailto:djankovic@corp.attcanada.ca]
Sent: Tuesday, January 23, 2001 10:56 AM
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@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@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