| 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
A wild shot in the dark:
Guessing you have indexes on the three "in" clauses that generate filter operations in ANSI.
Add <= =>'s using the lowest of the in values and the greatest of the in
values for bounds.
(I can't remember if ANSI has between and I'm not looking it up).
Of course this implies you know the constants you're tossing in there, but
if you have them for the in clause
you should have them for the greater-than-equal and less-than-equal.
Oh and the one with a single in value could be an ='s (unless that varies from time to time).
Leave out any of the in's that you don't have a usable index for.
I'm not sure it will help, because maybe, for example, your 2 through 51
thingys is almost all the values.
Can't tell without a histogram type thing.
g'luck
mwf
-----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 5:27 PM
To: Stephane Faroult
Cc: lex.de.haan_at_naturaljoin.nl; oracle-l_at_freelists.org
Subject: Re: Sincere Advice on Sql Plan - Thanks
The ANSI Query runs for 20 Secs to get 20 Rows !!!! - The users are shouting Unacceptable !!! (i will do the same) .. If somebody can make sense of this . It will be real useful.
Cheers
Ganesh
On Wed, 13 Oct 2004 23:16:30 +0200, Stephane Faroult
<sfaroult_at_roughsea.com> wrote:
> (Sorry, had to remove the execution plans to avoid overquoting) That's an
> interesting one, and very illustrative of what I dislike in the ANSI
syntax.
> In the old syntax, all tables in the FROM clause are (roughly) equal. With
> the ANSI syntax, you SELECT from *one* table, which is joined to others -
> primum inter pares (in plain English, more equal than the others). You
> totally lose the symmetry. The ANSI syntax takes it (pretty obviously) as
> the driving table - not the Oracle syntax. I guess that finding the proper
> driving table with the ANSI syntax musn't be a piece of cake for the
> optimizer, because you put it, from the very start, on some rails. If the
> wrong table was specified in the FROM clause, tough luck.
>
> Stephane Faroult
>
>
>
> Ganesh Raja wrote:
> 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.
-----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-lReceived on Wed Oct 13 2004 - 16:52:53 CDT
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l
 |
 |