Re: OPTIMISATION PROBLEM!!!

In article <jmboivin.20.0010869E_at_cti.ulaval.ca>, jmboivin_at_cti.ulaval.ca (Jean-Marc Boivin) writes:
> We have a problem with a SQL statement using the EXISTS clause:
> Take the following SQL:
> SELECT ANNEE_REFERENCE
> FROM PERMIS_STATIONNEMENT a
> WHERE exists (select 'x'
> from vehicule ve
> , permission_stationner pn
> where pn.id_vehicule = ve.id_vehicule
> and pn.annee_reference = a.annee_reference
> and pn.numero_permis = a.numero_permis)

Note: Reformatted SQL so I could understand what you were doing, replaced

      "PERMIS_STATIONNEMENT" with the alias "a" to reduce visual 'noise', 
      and I put the pseudo constants "a.annee_reference" and 
      "a.numero_permis" on the right of the "=" sign (probably has no effect
      on parsing but it stands out more for me when doing maintenance work).

> I have indexes on all columns used in this statement.

I assume you have one composite index on the 3 fields rather than 3 separate indexes (one per column). A composite index narrows down the field REALLY quickly. Oracle will only use ONE index per table and it chooses the most cost (or rule) effective one during parsing.

> 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 a
> 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 pn.annee_reference = a.annee_reference
> and pn.numero_permis = a.numero_permis)

... reformatted script again ...

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

This would be because the sub-query is NOT using any index OR is doing a FULL TABLE scan on VEHICULE and then indexing into PERMISSION_STATIONNER OR doing a FULL TABL SCAN on both tables in the sub-query.

Some questions:

Is the IMMATRICULATION column also indexed?

Is it part of the same composite index as the other three fields?

Are you using the RULE or COST based optimiser?

Have you run ANALYZE against the tables (and indexes)?

Is IMMATRICULATION a NUMBER or VARCHAR2 field? If it's NOT a NUMBER field then Oracle will apply a to_number() function on the indexed field and ignore the index on that field altogether.

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

... reformatted again ...  

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

If you are using the RULE based optimiser then the order of the tables in the FROM clause can effect the optimiser's choice. Try putting VEHICULE first in the list and see if that causes the problem again.

Try reordering the tables in your sub-query and see what effect that has.

I would suggest that you use EXPLAIN and TKPROF against these queries so you can see EXACTLY what the optimiser is doing and that should give you some idea of what is happening and how you can help the optimizer make the right choices.

Hope the above is of some help to you.

Let me know how you go.

-- 

Bruce...        pihlab_at_cbr.hhcs.gov.au

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, ACT, Australia                        (W) 06-289-7056 *
*=================================================================*
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************

"The more complex the argument gets, the easier it is to refute."
"Killing is wrong!"  -- Trent 'The Uncatchable' Castanaveras