| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> UpdateRE: 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_01C0856A.786B0100
Content-Type: text/plain;
charset="iso-8859-1"
Update on previous "init.ora" thoughts.....(independent of its applicability in solving the particular problem at hand)
the parameter in question is "optimizer_features_enable" and the value it takes is a three position, dot-separated version number:
e.g.
optimizer_features_enable = 8.1.6
-----Original Message-----
From: Koivu, Lisa [mailto:lkoivu_at_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_at_corp.attcanada.ca
<mailto:djankovic_at_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.comReceived on Tue Jan 23 2001 - 12:29:49 CST
<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). optimizer_features_enable ------_=_NextPart_001_01C0856A.786B0100 Content-Type: text/html; charset="iso-8859-1"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>RE: View merging</TITLE>
<META content="MSHTML 5.50.4611.1300" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=249322818-23012001><FONT face=Arial color=#0000ff size=2>Update
on previous "init.ora" thoughts.....(independent of its applicability</FONT></SPAN></DIV>
<DIV><SPAN class=249322818-23012001><FONT face=Arial color=#0000ff size=2>in
solving the particular problem at hand) </FONT></SPAN></DIV>
<DIV><SPAN class=249322818-23012001><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=249322818-23012001><FONT face=Arial color=#0000ff size=2>the
parameter in question is "optimizer_features_enable" and the value it</FONT></SPAN></DIV>
<DIV><SPAN class=249322818-23012001><FONT face=Arial color=#0000ff size=2>takes
is a three position, dot-separated version number:</FONT></SPAN></DIV>
<DIV><SPAN class=249322818-23012001><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=249322818-23012001><FONT face=Arial color=#0000ff
size=2>e.g.</FONT></SPAN></DIV>
<DIV><SPAN class=249322818-23012001><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=249322818-23012001><FONT face=Arial color=#0000ff
size=2>optimizer_features_enable = 8.1.6</FONT></SPAN></DIV>
<DIV><SPAN class=249322818-23012001></SPAN><FONT face="Times New Roman"><FONT
size=2><SPAN class=249322818-23012001><FONT face=Arial color=#0000ff> </FONT></SPAN></FONT></FONT></DIV>
<DIV><FONT face="Times New Roman"><FONT size=2><SPAN
class=249322818-23012001></SPAN></FONT></FONT> </DIV>
<DIV><FONT face="Times New Roman"><FONT size=2><SPAN
class=249322818-23012001> </SPAN>-----Original Message-----<BR><B>From:</B> Koivu, Lisa [mailto:lkoivu_at_qode.com]<BR><B>Sent:</B> Tuesday, January 23, 2001 11:42 AM<BR><B>To:</B> Multiple recipients of list ORACLE-L<BR><B>Subject:</B> RE: View merging<BR><BR></DIV></FONT></FONT>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<P><FONT size=2>Have you tried writing another view with all the joins in it? Put the index hint in the view and it should work. </FONT></P> <P><FONT size=2>Views can screw up the optimizer. This is one case.</FONT> </P><BR> <P><FONT size=2>-----Original Message-----</FONT> <BR><FONT size=2>From: Djordje Jankovic [<A href="mailto:djankovic_at_corp.attcanada.ca">mailto:djankovic_at_corp.attcanada.ca</A>]</FONT> <BR><FONT size=2>Sent: Tuesday, January 23, 2001 10:56 AM</FONT> <BR><FONT size=2>To: Multiple recipients of list ORACLE-L</FONT> <BR><FONT size=2>Subject: View merging</FONT> </P><BR> <P><FONT size=2>Hi everybody,</FONT> </P> <P><FONT size=2>I have a weird problem. A query does not want to merge a where clause with</FONT> <BR><FONT size=2>the view (as far as I know this should be default behavior). </FONT></P> <P><FONT size=2>So, I have something like</FONT> </P> <P><FONT size=2>create view xxxx as</FONT> <BR><FONT size=2> select table_a.field1, table_a.field_c, table_b.field2</FONT> <BR><FONT size=2> from table_a, table_b</FONT> <BR><FONT size=2> where table_a.field_a = table_b.field_b;</FONT> </P> <P><FONT size=2>When I run the query against the view:</FONT> </P> <P><FONT size=2>select * </FONT><BR><FONT size=2> from xxxx </FONT><BR><FONT size=2> where field_c = 'aaa';</FONT> </P> <P><FONT size=2>where field_c is an indexed field from table_a, oracle does not use the</FONT> <BR><FONT size=2>index.</FONT> </P><BR> <P><FONT size=2>However when I run (add the where clause to the view defining query):</FONT> </P> <P><FONT size=2>select table_a.field1, table_b.field2</FONT> <BR><FONT size=2> from table_a, table_b</FONT> <BR><FONT size=2> where table_a.field_a = table_b.field_b</FONT> <BR><FONT size=2> and table_a.field_c = 'aaa';</FONT> </P> <P><FONT size=2>The index is used.</FONT> </P> <P><FONT size=2>I tried both COST and RULE optimizer, tried giving hints (including the /*+</FONT> <BR><FONT size=2>MERGE */ ) but nothing helped.</FONT> </P> <P><FONT size=2>Any ideas, suggestions ?</FONT> </P> <P><FONT size=2>Thanks.</FONT> </P> <P><FONT size=2>Djordje </FONT></P> <P><FONT size=2>-- </FONT><BR><FONT size=2>Please see the official ORACLE-L FAQ: <A target=_blank href="http://www.orafaq.com">http://www.orafaq.com</A></FONT> <BR><FONT size=2>-- </FONT><BR><FONT size=2>Author: Djordje Jankovic</FONT> <BR><FONT size=2> INET: djankovic_at_corp.attcanada.ca</FONT> </P> <P><FONT size=2>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051</FONT> <BR><FONT size=2>San Diego, California -- Public Internet access / Mailing Lists</FONT> <BR><FONT size=2>--------------------------------------------------------------------</FONT> <BR><FONT size=2>To REMOVE yourself from this mailing list, send an E-Mail message</FONT> <BR><FONT size=2>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in</FONT> <BR><FONT size=2>the message BODY, include a line containing: UNSUB ORACLE-L</FONT> <BR><FONT size=2>(or the name of mailing list you want to be removed from). You may</FONT> <BR><FONT size=2>also send the HELP command for other information (like subscribing).</FONT> <FONT
 |
 |