Idempotence of this function met with a concatenation ?

Hi,
(v.: 8.1.7.4)

I have table bcp that contains more than 1.100.000 records. It has a NAME_UPPER indexed field that contains standardized data (no blanks, no quote-signs, no hyphens etc.) fed through a trigger when NAME is inserted/updated.

I have an UPPER_REPLACE function that reads: FUNCTION UPPER_REPLACE (str VARCHAR2) RETURN VARCHAR2 deterministic
IS
BEGIN
  RETURN
UPPER(REPLACE(REPLACE(REPLACE(CONVERT(str,'US7ASCII'),'''',''),' ',''),'-',''));
END; For the following 6 statements below we have very different response times, depending on the way we perform the search. The 2 last queries last about 2 minutes before we get our list. The first 4 execute very quickly.

Whatever stmt run the explain plan is *always* the same: 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1823 Card=56474 Bytes=6325088)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BCP' (Cost=1823 Card=56474 Bytes=6325088)
2 1 INDEX (RANGE SCAN) OF 'BCP_STUF_I' (NON-UNIQUE) (Cost=50 Card=56474)

(bcp_stuf_i index is defined as:
CREATE INDEX BCP_STUF_I ON BCP (NAME_UPPER, FNAME_UPPER)...) What I can't understand is why the last 2 queries take up 2 minutes (whereas the others are immediate), in particular the last one when you concat '%' to 'upper_replace(:name)'. In this case a 10046 trace shows zillions of WAIT #1: nam='db file sequential read' ela= 0 p1=... lines...

Thanks a lot.

var name varchar2(30);
exec :name := '%STUFF%';

--QUICK, IMMEDIATE RESP. TIME:

select prsn,stuff from BCP where name_upper like :name;
--count(): 77

exec :name := 'STUFF%';

select prsn,stuff from BCP where name_upper like :name;
-- count(): 66

select prsn,stuff from BCP where name_upper like '%' || :name;
-- count(): 77

select prsn,stuff from BCP where name_upper like upper_replace(:name);
-- count(): 66

--VERY SLOW, ABOUT 2 MN TO GET THE DATA DISPLAYED IN
--SQL*Plus:

exec :name := '%STUFF%';

select prsn,stuff from BCP where name_upper like upper_replace(:name);
-- count(): 77

exec :name := 'STUFF%';
select prsn,stuff from BCP where name_upper like '%' || upper_replace(:name);
-- count(): 77
Received on Wed Oct 04 2006 - 04:03:25 CDT