OracleBlog

i have implemented the oracle regexp in a a system we are currently developing. our system is fairly tranactional and the query that contains the regular expressions runs on the order of 50K times in a short period of time (as fast a possible).

we placed the actual regexp call in a function that simpley returns 1 or 0 for a hit or not, the function accepts the string to be tested, the rows in the table selected over with the regexp_like actually contain the regexp, reversed from most examples.

are they any performance considerations for using regexp. we have moved to a much larger database box disk wise and have little or no I/O contention, but the CPUs are more or less the the same (Dual, dual core 3.0GHz intel). The evaluation the the regexp queries has more or less remained unchanged in the migration.


FUNCTION isValidItemId(pItemId varchar2, pCriteriaExpression varchar2) RETURN integer is
retval integer;
expectedCount integer;
hitCount integer;

begin
if (pCriteriaExpression is not null) then
begin
-- see how many matches this item should have
select criteria_count
into expectedCount
from my_item mi
where mi.id = pMyItemId;

if (expectedCount > 0) then
begin
-- now see how many we get with the criteria expreesion
select count(ic.id)
into hitCount
from item_criteria ic, item_item_criteria iic
where ic.id = iic.item_criteria_id
and iic.item_id = pItemId
and regexp_like(pCriteriaExpression, iic.criteria_regexp);

if (hitCount = expectedCount) then
retval:=1;
else
retval:=0;
end if;
end;
else
retval:=1;
end if;
end;
else
retval:= 1;
end if;
return (retval);
end;

 

Sorry, can't help you. I'm still using Oracle 9 (no regexp).

You can study the Oracle 10g documentation, and run some of your own tests. Or you could Ask Tom:

http://asktom.oracle.com

Beyond that, try posting your questions in a discussion forum.
 

Hi Robert,
still running Oracle 9i?

Well now you can practice with 10G regular expressions at the on-line Regular Expression Workbench for Oracle 10G.

So, you have no more excuses now.

;-)

Flavio