Re: HELP Tuning SQL statements

huynuye_at_statcan.ca wrote:

> When I use the EXPLAIN PLAN to view the execution plan for my statement.
> The optimizer ignores my index for:
>
> 1) Select * from employees where employee_id = uid;
> uid is a variable from my PL/SQL procedure.
> The Explain show a full table access even when I use Hints.
>

In all probability, the "uid" variable was defined in your PL/SQL as a data type that is not compatible with the data type of the column employee_id. In this case, the SQL translator goes drowsy and does the equivalent of you typing:

WHERE TO_CHAR(EMPLOYEE_ID) = :uid

which sends the optimizer off the wall in trying to use ANY index on column EMPLOYEE_ID. Common problem. Try doing the opposite.

Code: WHERE EMPLOYEE_ID = TO_NUMBER [or to_whatever] (:uid)

You'll find this time the optimizer will pick up your index.

HTH
Cheers
Nuno Souto
nsouto_at_acay.com.au Received on Tue Feb 17 1998 - 00:00:00 CET