Re: Request in 9i slower than in 8i ...

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 20 May 2004 10:13:34 +0000 (UTC)
Message-ID: <c8i0ce$6pv$1@hercules.btinternet.com>

At the instance level, you can do:

alter system set "_unnest_subquery" = false;

That will get into your spfile.

Unnesting can be highly beneficial, though, so my preference would be to hint the examples where I found a problem.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"zeb" <spam_at_nowhere.com> wrote in message
news:40ac766f$0$14068$626a14ce_at_news.free.fr...


> > Oracle 9 automatically unnests subqueries

> > whenever it is legal to do so. This is why you

> > see the hash semi-join in the second plan.

> >

> > To stop this happening for a specific query,

> > you can put in the NO_UNNEST hint into

> > the subquery.

> >

> > Oracle 10g will cost both options are select

> > the cheaper.

> >

> OK, thanks a lot

> is there a way to do this at the instance level ( spfile ) ?

>

>

Received on Thu May 20 2004 - 05:13:34 CDT