Message-ID: <F001.0058F855.20030505075208@fatcity.com>
Date: Mon, 05 May 2003 07:52:08 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: "Stephane Paquette" <stephane.paquette@standardlife.ca>
Subject: RE: which should be faster
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain;	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

I've check on asktom and he is positive that the order of predicate has no
impact (DB version for that comment is 815).
>From the site :

No, the order of evaluation of a predicate in SQL is purposely
"indeterminate".

You should not, cannot rely on a specific order or evaluation.  You cannot
even
rely on short-circut evaluation.  The optimizer will evaluate the predicate
in
the order it feels is best -- that is its job.

SQL is a wholly non-procedural language, you are trying to attribute
procedural
language to it.  You should not have any need to have a predicate evaluated
in
some order.


If anyone can come up with a good reason for needing a predicate evaluated
in
some specific order -- put it in the comments and I'll address them as they
come
in.


Stephane
-----Original Message-----
Sent: Saturday, May 03, 2003 1:52 PM
To: Multiple recipients of list ORACLE-L


Is the predicate order relavent for 8.1.7.3? Im doing quite a bit of tuning
and am wondering whether its worth the effort to reorganize the predicate?

So is the rule of thumb on predicates.

 Filters before joins..

If I have col1 = 'ABC', put that before my join?

BTW, logically I would think it would help to put filters into inline views,
but Im not seeing any performance improvements. You have any luck with this?

select col1, b.col2
from a,
(select col2
from tabb
where col2 = 'blah') b
where a.col1 = b.col1

Someone told me this can help. Any good rules of thumb on this or documents?
All the books seem to only focus on the basic things.
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L@fatcity.com>
Sent: Saturday, May 03, 2003 11:11 AM


>
> Did you also test with index compression in place
> on the organization column ?
>
> If your tests were:
>           organization = 'XXX'
>     and inv_date between ....
>
> did you also test
>           inv_date between ....
> and    organization = 'XXX'
>
> Under Oracle 8, the predicate order
> can make a difference - although in
> this case I don't think you would see
> much difference, as neither predicate
> is an eliminator at the filter level.
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
>   The educated person is not the person
>   who can answer the questions, but the
>   person who can question the answers -- T. Schick Jr
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
> ____Denmark__May 21-23rd
> ____Sweden___June
> ____Finland__September
> ____Norway___September
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK_(Manchester)_May x 2
> ____Estonia___June (provisional)
> ____Australia_June (provisional)
> ____USA_(CA, TX)_August
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L@fatcity.com>
> Sent: 02 May 2003 22:31
>
>
> > Actually I found a note on tom kyte's book(expert one on one)  that
> it
> > shouldn't matter weather u index c1,c2 or c2,c1 . Although my
> scenerio is
> > not exact . But its kinda similer . I did a little testing and found
> that
> > just invdate is faster compare to org , indate .
> >
> > Thanks ,
> > -ak
> >
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jonathan Lewis
>   INET: jonathan@jlcomp.demon.co.uk
>
> Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
> San Diego, California        -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ryan
  INET: rgaffuri@cox.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Paquette
  INET: stephane.paquette@standardlife.ca

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).