Path: utzoo!utgpu!watmath!clyde!att!osu-cis!tut.cis.ohio-state.edu!cwjcc!hal!ncoast!allbery
From: allbery@ncoast.UUCP (Brandon S. Allbery)
Newsgroups: comp.databases
Subject: Re: Is order important in SQL?
Message-ID: <13087@ncoast.UUCP>
Date: 13 Nov 88 18:26:16 GMT
References: <344@sunny.UUCP>
Reply-To: allbery@ncoast.UUCP (Brandon S. Allbery)
Followup-To: comp.databases
Distribution: na
Organization: Cleveland Public Access UN*X, Cleveland, Oh
Lines: 53

As quoted from <344@sunny.UUCP> by poage@sunny.UUCP (Tom Poage):
+---------------
| 	Is the order of entries in the "select", "from", and 
| 	"where" clauses important in terms of performance?
| 
| For example,
| 
| 	select a,b,c,d,x,y,z,...
| 	from t1,t2,t3,...
| 	where t1.a = t2.a
| 		and t1.x = t2.x
| 		and z in <1,2,4,10>
| 		and ...
| 	/
+---------------

I don't know what the ANSI standard says, but it's moot:  Unify SQL makes no
claim of ANSI compliance.

I've never seen any effect of ordering in Unify SQL.  (I *have* seen an
effect in Informix-SQL:  "where" clauses seem to be executed in "bottom-up"
order of conjunctions.)

+---------------
| names?  Further, can candidate selections can be eliminated
| "earlier" in the qualification process by placing certain
| comparisons before others in the where clause?  I.e., reduce the
| number of comparisons before deciding on rejection based on my
| knowledge of the data distribution.
+---------------

In theory, the query optimizer is better able to judge the distribution of
data then you are, assuming that commonly-used join fields are indexed (note
that explicit relationships and hashed primary keys count as a form of
"indexing").  In practice, Unify does a pretty good job no matter what the
order of conjunctions in a "where" clause is.

+---------------
| I assume that order should, in a theoretical sense anyway,
| make no difference in this type of HLL.  However, you
| never know....
+---------------

Theoretical, h*ll.  If an SQL doesn't optimize the WHERE clause properly,
it's broken.

++Brandon
-- 
Brandon S. Allbery, comp.sources.misc moderator and one admin of ncoast PA UN*X
uunet!hal.cwru.edu!ncoast!allbery  <PREFERRED!>	    ncoast!allbery@hal.cwru.edu
allberyb@skybridge.sdi.cwru.edu	      <ALSO>		   allbery@uunet.uu.net
comp.sources.misc is moving off ncoast -- please do NOT send submissions direct
      Send comp.sources.misc submissions to comp-sources-misc@<backbone>.