Oracle using variable in where like

Question

I'm trying to run SQL in a PL/SQL procedure. If I were to say:

SELECT * FROM myTable WHERE FNAME like 'Joe%' AND dept = 'xyz';

this runs instantly.

if I try putting 'Joe' in a variable it takes forever to run, about 3 minutes. Here is some syntax I found for using like with variables.

PROCEDURE myProcedure(firstName IN VARCHAR,
                      cEmployees OUT cursor_type)
IS
BEGIN
     OPEN cEmployees FOR
     SELECT * FROM myTable WHERE FNAME like firstName || '%' AND dept = 'xyz';
END myProcedure;

what am I doing wrong? thanks.

Answer 1

I haven't worked on Oracle for some time.
However, you could avoid this

SELECT * FROM myTable WHERE FNAME like firstName || '%'

Instead, set the firstName variable before the above statement.
e.g. firstName = firstName || '%' (pardon the syntax)
and then SELECT * FROM myTable WHERE FNAME like firstName

Answer 2

(sorry, this was too long for a comment so I'm adding it as an "answer")

Travis, I suspect you haven't got it quite right yet. You've changed the query and it "fixed it" (i.e. ran in less than 3 minutes), but you don't know why. Later on you may find that the new procedure will suffer from the same problem again.

The reason is that when the query is reparsed (e.g. when you make a small change to it, like adding parentheses), Oracle generates a new plan for the query. When it generates the query, it is probably using bind variable peeking to see what you are searching on. When it parses it with the value 'Joe', it creates the best plan for that particular value, and it runs quickly. If, however, the query gets re-parsed later on (which it likely will from time to time as the query gets aged out of the shared pool), a different value might be presented (e.g. 'Tom') - and Oracle will optimize the query for that value, which might very well be quite a different plan. Then, all of a sudden, the query on "Joe" runs much slower.

Bottom line: if you don't know why it became faster, you don't know if the improvement is permanent or temporary.

Disclaimer: the above is general advice only - if you specify your database version, and provide the explain plan output for the two queries, you may get more specific advice.