RE: pl/sql question and owa_pattern question

Guang,

   I agree with your analysis, looping on characters is not the faster you can do, simply because there is a significant overhead (compared to C code for instance) in a language such as PL/SQL - which might be perfectly acceptable in some circumstances, much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C, might improve performance. However, in my view the best performance gains you may get is by, so to speak, pushing the bulk of the processing deeper into the kernel (which isn't by the way exclusive of native compiling). Using a function such as INSTR() will be much more efficient than looping on characters.  I would suggest something such as :

• First use TRANSLATE() to replace all the characters you want to get rid of by a single, well identified character, say # (use CHR() || ... for non printable characters - you can build up the string of characters to translate in the initialisation section of a package rather than typing it).
• Start with initializing your string to LTRIM(string, '#')
• Then as long as pos := INSTR(string, '#') isn't 0, get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos + 1), '#') to string (very similar to what you were planning to do with owa).

This will be probably much faster than a character-by-character loop and calls to an owa package.

HTH, Stephane Faroult

>----- ------- Original Message ------- -----
>From: Guang Mei <gmei_at_incyte.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Thu, 20 Nov 2003 19:39:55
>
>Hi:
>
>In my pl/sql program, I want to process each "word"
>in a string. The
>string is selected from a varchar2(300) column. The
>delimit that separates
>the words is not necessary space character. The
>definition of the delimit
>in this program is set as
>
>1. Any character that is NOT AlphaNumerical (0-9,
>A-Z,a-z)
>and
>2. the character is not one of these: '-.,/<*>_'
>
>Now my program is basically checking each
>character, find the delimit, and
>rebuild each word. After that I process each
>"word". The code looks like
>this:
>
>-------
>str := "This will be a long string with length
>upto 300 characters, it
>may contain some invisible characters';
>len := length(str)+1;
> for i in 1..len loop
> ch := substr(str,i,1);
> if (not strings.isAlnum(ch) and
>instr('-.,/<*>_', ch)<1) then
> if word is not null then
> -- do some processing to variable word !
> word := null; -- reset it
> end if;
> else
> word := word || ch; -- concat ch to word
> end if;
> end loop;
>
>-------
>
>I think It's taking too long because it loops
>through each characters. I
>hope I could find a way to speed it up. I don't
>have experiience in
>owa_pattern, but I thought there might be a way to
>do it here:
>
>----
>str := "This will be a long string with length
>upto 300 characters, it
>may contain some invisible characters';
>newstr := str;
>pos := 1;
>while pos != 0 loop
> pos := owa_pattern.amatch(newstr, 1, '\W');
>-- how can I mask out
>these '-.,/<*>_' ???
> word := substr(newstr, 1, pos-1);
> -- do some processing to variable word !
> if pos != 0 then
> newstr := substr(newstr, pos+1);
> end if;
>end loop;
>------
>
>My simple tests showed that owa_pattern call is
>much slower than direct
>string manupilation. But I would like to try it in
>this case if I could
>easily get the "wrods" from the string. Any
>suggestions?
>
>TIA.
>
>Guang
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).