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: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 13 Oct 2004 17:55:58 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKCEOKFHAA.mwf@rsiz.com>


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.



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-l
Received on Wed Oct 13 2004 - 16:52:53 CDT

Original text of this message

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