RE: Sincere Advice on Sql Plan - Thanks
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <ganesh.raja@xxxxxxxxx>
- Date: Thu, 14 Oct 2004 10:09:09 +0200
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Ganesh Raja
Sent: Wednesday, October 13, 2004 19:29
To: lex.de.haan@xxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Ganesh Raja
> Sent: Wednesday, October 13, 2004 18:54
> To: jkstill@xxxxxxxxx
> Cc: smishra_97@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> 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
>
> --
> //www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
