Hi,
I am working on Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit.
I am running below queries against a table which has approx. 1 million rows.
SELECT COUNT (1)
FROM tab1
WHERE col1 LIKE '%ABC%' OR col1 LIKE '%DEF%' OR col1 LIKE '%GHI%' OR col1 LIKE '%JKL%';
Above query getting result in less than a sec. But, below query is taking 8 sec. Each search term taking 2 sec. I have tried with 10 terms, it is taking 20 sec. Where as, above query still taking less than 2 sec. I need to use REGEXP_LIKE (due to query length limitation), because constructing SQL query dynamically and it may have 80 to 100 search terms.
SELECT COUNT(1)
FROM tab1
WHERE REGEXP_LIKE (col1, 'ABC|DEF|GHI|JKL');
Can any one kindly let me know how to improve improve performance of REGEXP_LIKE query.
Thanks a lot for the help.