Re: Idempotence of this function met with a concatenation ?

Spendius wrote:
> 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

An index range scan ( as your execution plan shows ) is how oracle usually handles the queries that involve LIKE.

The LIKE operator is effective in returning data when you start with some characters at the beginning LIKE 'LINCOLN%'. It can very quickly find index blocks that are relevant ( they have LINCOLN as the first few characters of the key.

An index range scan doesn't work well as an execution plan when you don't give oracle some idea "where to start" with the index. You gave a %LINCOLN% ... so it will have to read all of the index entries ... it doesn't have anywhere specific to start.

If you application really needs to look "anywhere" within the name ( support wild carding at the beginning and at the end ) then you will want to consider an oracle text based index instead of a simple basic oracle index on that field. Received on Wed Oct 04 2006 - 08:36:40 CDT