Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> SQL fast, PL/SQL slow, optimizer confused.
I'm trying to tune a select statement which is called a lot. My new
version runs fast when written as a straight SQL statement, but runs
very slow when I wrap it in a PL/SQL block. I must be missing something
very obvious here, please help.
Environment:
- Oracle EE 8.0.5.2.1 with Objects option and PL/SQL 8.0.5.2.0
The where-clause has a construct like this:
where ( foo like 'XX-__1111111111' or foo like 'YY-__1111111111' or foo like 'ZZ-__1111111111' or foo like 'WW-__1111111111' )
The column foo has a non-unique index. I've checked the execution plan and the SQL version of my statement uses that index.
The PL/SQL version looks more or less like this: declare
key varchar2(10) := '1111111111'; key1 varchar2(160); key2 varchar2(160); key3 varchar2(160); key4 varchar2(160); begin key1 := 'XX-__' || key; key2 := 'YY-__' || key; key3 := 'ZZ-__' || key; key4 := 'WW-__' || key; select ... from ... where ( foo like key1 or foo like key2 or foo like key3 or foo like key4 )
This version performs a FTS. If I remove 3 of the 4 variables and just use one of the keys, e.g. key1 :
where foo like key1
then Oracle uses the index.
Does anyone know why Oracle behaves like this and hopefully what I can do about it?
Thanks,
Bjørn
Received on Wed Feb 18 2004 - 02:41:59 CST