Path: utzoo!utgpu!jarvis.csri.toronto.edu!clyde.concordia.ca!uunet!zephyr.ens.tek.com!tektronix!sequent!jsc
From: jsc@sequent.UUCP (J. Scott Carr)
Newsgroups: comp.databases
Subject: Re: ORACLE optimizer
Keywords: forms
Message-ID: <26788@sequent.UUCP>
Date: 22 Dec 89 00:16:07 GMT
References: <5068@freja.diku.dk>
Reply-To: jsc@crg4.UUCP (J. Scott Carr)
Distribution: comp
Organization: Sequent Computer Systems, Inc
Lines: 46

In article <5068@freja.diku.dk> elgaard@freja.diku.dk (Niels Elgaard Larsen) writes:
>
>I'm using ORACLE ver. 5. 
>I find it frustrating that the SQL-optimizer don't use constant-folding.
>
>The query:
>
>	SELECT 'x' from T1,T2 where 1=2, 
>
>takes a LOT of time (INGRES can of optimize this).
>Of cource you don't often write this in SQL, but in SQL*FORMS I often
>write ORDERING clauses like:
>
>WHERE (key in (SELECT key from T1 where key=:field) or :field IS NULL)
>
>This is very inefficient. The best I have come up with is, assuming key is 
>numeric, positive and less than some number.
>
>WHERE key in (SELECT key from T1 
>      WHERE key between nvl(:field,0) and decode(nvl(:field,0),0,99999,:field))
>
>This is more efficient, but not very pretty.
>
>Is there a better way?

One thing that's been noted is the 'in' clause is much less efficient than
'exists'.  The where clause

WHERE exists (SELECT key FROM T1 WHERE key=:field) or :field IS NULL

performs much better.  I remeber seeing an article in Database Programming
and Design that details the mechanics behind the difference.  The jest of
which I remeber being the number of full table scans and temporary tables
that are built.  Maybe someone can fill in for me here . . . Of course, this 
doesn't address other general shortcomings of the ORACLE optimizer.

>Is the Ver. 6 optimizer better?

No.  As far as I know, the optimizers are identical from V5 to V6.  Its
rumored that a new version will be introduced in V7.


--------

Scott Carr   				 uunet!sequent!jsc
Sequent Computer Systems		 (503) 526-5940