Re: OPTIMISATION PROBLEM!!!

jmboivin_at_cti.ulaval.ca (Jean-Marc Boivin) writes:
|> 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.

You should take a look at the explain plan for both statements. It could be that Oracle is choosing a less selective index with the new condition in there.

You might try changing the condition slightly to ... AND VE.IMMATRICULATION + 0 = 500
But that's just a guess based on the information given.

Roderick Manalac
Oracle Corporation Received on Sat Sep 24 1994 - 02:15:20 CEST