Why is:
select * from
( with dat as (select level lv, rpad('X',500,'X') txt from dual connect by level <= 20000)
select lv
, REGEXP_COUNT(txt, '(ABC|DEF)([[:digit:]]){5}') as occurrences
from dat
--where REGEXP_COUNT(txt, '(ABC|DEF)([[:digit:]]){5}') > 1
)
where rownum > 1
0.019 seconds and
select * from
( with dat as (select level lv, rpad('X',500,'X') txt from dual connect by level <= 20000)
select lv
, REGEXP_COUNT(txt, '(ABC|DEF)([[:digit:]]){5}') as occurrences
from dat
where REGEXP_COUNT(txt, '(ABC|DEF)([[:digit:]]){5}') > 1
)
where rownum > 1
6.7 seconds. Oracle computes the regexp_count in both executions. So there must be a difference in the evaluation is in the where part or not.
Best regards
Thomas