Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Sincere Advice on Sql Plan - Thanks

Re: Sincere Advice on Sql Plan - Thanks

From: Ganesh Raja <ganesh.raja_at_gmail.com>
Date: Thu, 14 Oct 2004 11:36:01 +0100
Message-ID: <f754edf041014033621352dd@mail.gmail.com>


Lex,

Thanks for that ... I did include the order By On It and There is No Change in the plan or the Execution Time. The Only thing added to my plan is an Sort Order By stmt..

As regards the In List ... (I am answering to Marks Comments also)

This is Part of a Java Program and the Where clause is Built on the fly ... That is the Reason u see so many Tables there but none being used in the Where or Select List.

The IN List needs to be there since it also Dynamic in the sense the User May select more than one.

Thanks as always

Cheers
Ganesh

PS : WIll start following the Old and New Paradigm

On Thu, 14 Oct 2004 10:09:09 +0200, Lex de Haan <lex.de.haan_at_naturaljoin.nl> wrote:
> well, if I look at the two SQL statements, the first thing I see is that
> they are *not* equivalent:
> one has an ORDER BY, the other one doesn't. that can make a huge difference,
> and it does in this case.
>
> the expensive plan is using two temporary tables (see the VIEW steps) and
> 77M temp space for the sorting (see the SORT ORDER BY step) so what happens
> if you remove the ORDER BY?
>
> last but not least, if you are in the position to change the data model,
> spend some time on it. if I see SQL statements like this, with many *long*
> IN lists, I always get the feeling that there is room for improvement
> somewhere :-)
>
> by the way, I know it is common terminology -- but it is unfair/confusing to
> talk about Oracle syntax vs. ANSI syntax --
> because *both* join expressions are *both* ANSI/ISO syntax and Oracle
> syntax... I rather prefer to refer to "old" and "new" syntax, although I
> must admit that it took Oracle a very long time to implement the "new"
> ANSI/ISO join syntax.
>
> Kind regards,
> Lex.
>
> -------------------------------
> visit http://www.naturaljoin.nl <http://www.naturaljoin.nl>
> -------------------------------
> skype me <callto://lexdehaan>
>
>
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Ganesh Raja
> Sent: Wednesday, October 13, 2004 19:29
> To: lex.de.haan_at_naturaljoin.nl
> Cc: oracle-l_at_freelists.org
> Subject: Re: Sincere Advice on Sql Plan - Thanks
>
> Lex,
> Not sure If Oracle-L Accepts Attachments ..
>
> Here are the Queries and the Plan.
>
> Thanks for the help.
>
> Rgds,
> Ganesh
>
> On Wed, 13 Oct 2004 19:58:55 +0200, Lex de Haan
> <lex.de.haan_at_naturaljoin.nl> wrote:
> > you might want to provide the two statements you are talking about?
> > and maybe even the two corresponding execution plans?
> >
> > Kind regards,
> > Lex.
> >
> > -------------------------------
> > visit http://www.naturaljoin.nl
> > -------------------------------
> > skype me <callto://lexdehaan>
> >
> >
> > -----Original Message-----
> > From: oracle-l-bounce_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Ganesh Raja
> > Sent: Wednesday, October 13, 2004 18:54
> > To: jkstill_at_gmail.com
> > Cc: smishra_97_at_yahoo.com; oracle-l_at_freelists.org
> > Subject: Re: Sincere Advice on Sql Plan - Thanks
> >
> > Okay I am Going to Piggy Back on this ..
> >
> > I have an ANSI SQL which when Rewriiten Using Oracle Syntax it seems
> > to take a Better plan to execute it .. Any ideas why this is
> > happening.
> >
> > There is a Total Change in the plan and it works much faster than the
> > ANSI Counterpart
> >
> > Any help is appreciated.
> >
> > Thanks.
> >
> > Cheers
> > Ganesh R
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
>
>

>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 14 2004 - 05:31:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US