elgaard@freja.diku.dk (Niels Elgaard Larsen) (12/20/89)
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.dkjsc@sequent.UUCP (J. Scott Carr) (12/22/89)
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
larrys@sequent.UUCP (Larry Scheurich) (01/05/90)
Does anyone out there know of an oracle utility that gives statistics about data in a table. In Ingres, there is the optimizedb command. It provides nice statistics that the query optimizer uses. I was wondering if anyone knows of or has written a utility like this for Oracle. I'm not looking to use it as a query optimizer, but we are trying to find a way to capture statistics on our data to double check indexes and data integrity. Thanks, Larry Scheurich uunet!sequent!larrys Sequent Computer Systems (503)-526-4240