I am not using function based indexes or materialized views. If I set
this parameter=FALSE, will cost optimzer do something?. What are the
advantages of setting this parameter Query_rewtite_enabled=TRUE.

On Jun 14, 1:10 pm, zigzag...@yahoo.com wrote:
> I am not using function based indexes or materialized views. If I set
> this parameter=FALSE, will cost optimzer do something?. What are the
> advantages of setting this parameter Query_rewtite_enabled=TRUE.

Give us the release of Oracle you're using and we can tell you. I can
tell you that from 8.1.6 onwards query_rewrite_enabled is no longer
necessary when using function-based indexes. From 8.1.6 through
9.2.0.8 setting that parameter to TRUE changes the rules for
transitive closure (this is fixed in 10g) causing the estimated
cardinality for affected queries, and thus the execution plans, to be
incorrect.

Post the release of Oracle you're using, to four numbers (8i, 9i, 10g
are not proper responses). You'll get an accurate answer after you do
so.


David Fitzjarrell

On Jun 14, 2:30 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
> On Jun 14, 1:10 pm, zigzag...@yahoo.com wrote:
>
> > I am not using function based indexes or materialized views. If I set
> > this parameter=FALSE, will cost optimzer do something?. What are the
> > advantages of setting this parameter Query_rewtite_enabled=TRUE.
>
> Give us the release of Oracle you're using and we can tell you. I can
> tell you that from 8.1.6 onwards query_rewrite_enabled is no longer
> necessary when using function-based indexes. From 8.1.6 through
> 9.2.0.8 setting that parameter to TRUE changes the rules for
> transitive closure (this is fixed in 10g) causing the estimated
> cardinality for affected queries, and thus the execution plans, to be
> incorrect.
>
> Post the release of Oracle you're using, to four numbers (8i, 9i, 10g
> are not proper responses). You'll get an accurate answer after you do
> so.
>
> David Fitzjarrell

Sorry, I am using Oracle 9.2.0.6 on Windows 2003.

zigzagdna@yahoo.com wrote:
> On Jun 14, 2:30 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
>> On Jun 14, 1:10 pm, zigzag...@yahoo.com wrote:
>>
>>> I am not using function based indexes or materialized views. If I
>>> set this parameter=FALSE, will cost optimzer do something?. What
>>> are the advantages of setting this parameter
>>> Query_rewtite_enabled=TRUE.
>>
>> Give us the release of Oracle you're using and we can tell you. I
>> can tell you that from 8.1.6 onwards query_rewrite_enabled is no
>> longer necessary when using function-based indexes. From 8.1.6
>> through
>> 9.2.0.8 setting that parameter to TRUE changes the rules for
>> transitive closure (this is fixed in 10g) causing the estimated
>> cardinality for affected queries, and thus the execution plans, to be
>> incorrect.
>>
>> Post the release of Oracle you're using, to four numbers (8i, 9i, 10g
>> are not proper responses). You'll get an accurate answer after you
>> do so.
>>
>> David Fitzjarrell
>
> Sorry, I am using Oracle 9.2.0.6 on Windows 2003.

Here's a good blog on the transitive closure issue mentioned by David:
http://jaffardba.blogspot.com/2006/0...iour-when.html

--
Jeroen

On Jun 14, 2:18 pm, zigzag...@yahoo.com wrote:
> On Jun 14, 2:30 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
>
>
>
>
>
> > On Jun 14, 1:10 pm, zigzag...@yahoo.com wrote:
>
> > > I am not using function based indexes or materialized views. If I set
> > > this parameter=FALSE, will cost optimzer do something?. What are the
> > > advantages of setting this parameter Query_rewtite_enabled=TRUE.
>
> > Give us the release of Oracle you're using and we can tell you. I can
> > tell you that from 8.1.6 onwards query_rewrite_enabled is no longer
> > necessary when using function-based indexes. From 8.1.6 through
> > 9.2.0.8 setting that parameter to TRUE changes the rules for
> > transitive closure (this is fixed in 10g) causing the estimated
> > cardinality for affected queries, and thus the execution plans, to be
> > incorrect.
>
> > Post the release of Oracle you're using, to four numbers (8i, 9i, 10g
> > are not proper responses). You'll get an accurate answer after you do
> > so.
>
> > David Fitzjarrell
>
> Sorry, I am using Oracle 9.2.0.6 on Windows 2003.- Hide quoted text -
>
> - Show quoted text -

Then the transitive closure issue affects you should you decide to set
query_rewrite_enabled to TRUE. Jonathan Lewis wrote about this in
"Cost-Based Oracle Fundamentals", pp. 141-144.


David Fitzjarrell

zigzagdna@yahoo.com wrote:
> I am not using function based indexes or materialized views. If I set
> this parameter=FALSE, will cost optimzer do something?. What are the
> advantages of setting this parameter Query_rewtite_enabled=TRUE.

It will definitely do something. Whether that is something you want
is quite another question.

The solution is to set up a testing environment and use it.

What you have not indicted is why you think this is a good idea.
What is happening to lead you to that conclusion. Explain Plan
examples would be helpful.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Jun 14, 4:20 pm, DA Morgan <damor...@psoug.org> wrote:
> zigzag...@yahoo.com wrote:
> > I am not using function based indexes or materialized views. If I set
> > this parameter=FALSE, will cost optimzer do something?. What are the
> > advantages of setting this parameter Query_rewtite_enabled=TRUE.
>
> It will definitely do something. Whether that is something you want
> is quite another question.
>
> The solution is to set up a testing environment and use it.
>
> What you have not indicted is why you think this is a good idea.
> What is happening to lead you to that conclusion. Explain Plan
> examples would be helpful.
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Oarcel provides a hinted called UNNESTED which will "rerewite" query
to merge sub query to driving query. Does this hint reqyure this
paremetr to be TRUE.

zigzagdna@yahoo.com wrote:
> On Jun 14, 4:20 pm, DA Morgan <damor...@psoug.org> wrote:
>> zigzag...@yahoo.com wrote:
>>> I am not using function based indexes or materialized views. If I set
>>> this parameter=FALSE, will cost optimzer do something?. What are the
>>> advantages of setting this parameter Query_rewtite_enabled=TRUE.
>> It will definitely do something. Whether that is something you want
>> is quite another question.
>>
>> The solution is to set up a testing environment and use it.
>>
>> What you have not indicted is why you think this is a good idea.
>> What is happening to lead you to that conclusion. Explain Plan
>> examples would be helpful.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor...@x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org
>
> Oarcel provides a hinted called UNNESTED which will "rerewite" query
> to merge sub query to driving query. Does this hint reqyure this
> paremetr to be TRUE.

In what version? Test it!
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org