Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> SQL fast, PL/SQL slow, optimizer confused.

SQL fast, PL/SQL slow, optimizer confused.

From: Bjorn Augestad <root_at_localhost.com>
Date: Wed, 18 Feb 2004 09:41:59 +0100
Message-ID: <szFYb.4806$rj4.68168@news2.e.nsc.no>


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
	)

end;

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US