| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Sincere Advice on Sql Plan - Thanks
sounds like a wonderful new paradigm, this Java approach :-)
generating an enourmous FROM list with many outer joins, just in case...
a real generator would clean the statement up before sending it to the
database?
and even if you want to generate WHERE clauses dynamically,
in my humble opinion long IN-lists often point to missing attributes in the
data model.
an IN-list with 34 values is weird and should be avoided when possible;
especially if you see that the 34 values are all integers between 2 and 52
...
the more I look at the two statements, the more I doubt that they are
logically equivalent.
one plan is showing 5 outer joins and 1 inner join, while the other plan
shows 4 and 2.
one more thing I noticed, looking at the execution plans again: how is it possible that one plan expects to retrieve 297K rows, and the other one 391? There is roughly a factor 1000 involved here...
are you testing these two statements against exactly the same objects, same database, same statistics, same session settings?
last but not least, looking at all the predicates against the fact table,
why don't you replace the indexes with bitmap indexes? that would probably
result
in a much more efficient access path to that table, in spite of all those
nasty IN-lists.
Kind regards,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Ganesh Raja
Sent: Thursday, October 14, 2004 11:36
To: Lex de Haan
Cc: oracle-l_at_freelists.org
Subject: Re: Sincere Advice on Sql Plan - Thanks
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 -- http://www.freelists.org/webpage/oracle-lReceived on Thu Oct 14 2004 - 06:44:23 CDT
 |
 |