OPTIMISATION PROBLEM!!!

We have a problem with a SQL statement using the EXISTS clause:

Take the following SQL:
SELECT
ANNEE_REFERENCE FROM PERMIS_STATIONNEMENT WHERE exists
(select 'x' from vehicule ve, permission_stationner pn where pn.id_vehicule = ve.id_vehicule
and permis_stationnement.annee_reference = pn.annee_reference and permis_stationnement.numero_permis
= pn.numero_permis )

I have indexes on all columns used in this statement. If i run the statement it works fine and fast, but if a add a condition with a constant values like this:
SELECT
ANNEE_REFERENCE FROM PERMIS_STATIONNEMENT WHERE exists
(select 'x' from vehicule ve, permission_stationner pn where pn.id_vehicule = ve.id_vehicule
AND VE.IMMATRICULATION = 500 <----- I add this condition and permis_stationnement.annee_reference = pn.annee_reference and permis_stationnement.numero_permis
= pn.numero_permis )

then the execution is VERY, VERY, VERY long. Don't forget that their all the indexes needed.

After that, i tried to execute a equivalent SQL but without EXISTS like that: SELECT
ANNEE_REFERENCE FROM PERMIS_STATIONNEMENT ,  vehicule ve, permission_stationner pn
 where
pn.id_vehicule = ve.id_vehicule
AND VE.IMMATRICULATION = 500 <----- I add this condition and permis_stationnement.annee_reference = pn.annee_reference and permis_stationnement.numero_permis
= pn.numero_permis

and HOOOOO surprise!, it is very fast and execute without performance problem.

What's the problem with this optimizer???? Is there a bug with the EXISTS clause???

We have Oracle 7.015 on SCO Unix.

Thank's.

Jean-Marc Boivin
University Laval
St-Foy, Quebec
Canada
jmboivin_at_cti.ulaval.ca Received on Wed Sep 21 1994 - 23:31:26 CEST