Path: utzoo!utgpu!jarvis.csri.toronto.edu!clyde.concordia.ca!uunet!samsung!zaphod.mps.ohio-state.edu!think!mintaka!bloom-beacon!eru!luth!sunic!dkuug!freja!elgaard
From: elgaard@freja.diku.dk (Niels Elgaard Larsen)
Newsgroups: comp.databases
Subject: ORACLE optimizer
Keywords: forms
Message-ID: <5068@freja.diku.dk>
Date: 20 Dec 89 12:08:08 GMT
Distribution: comp
Organization: DIKU, U of Copenhagen, DK
Lines: 28


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?
Is the Ver. 6 optimizer better?
-- 
                           Niels Elgaard Larsen
                          Institute of Datalogy,
                          University of Copenhagen
                          E-mail: elgaard@freja.diku.dk