Re: Performance of REGEXP_LIKE vs LIKE?
- From: "Vladimir M. Zakharychev" <vladimir.zakharychev@xxxxxxxxx>
- Date: 19 Apr 2007 09:01:31 -0700
On Apr 19, 12:52 am, "zstringer...@xxxxxxxxx" <zstringer...@xxxxxxxxx>
wrote:
Hello fellow netters,
I'm curious if anyone has done any performance testing of REGEXP_LIKE
vs LIKE. There are certain situations where the syntax of REGEXP_LIKE
is cleaner and shorter than the comparable LIKE expression. A common
search request for us involves doing a case-insensitive, wildcard
search of a 3 million record table, for a series of text strings.
For example:
SELECT *
(> FROM big_table bt
WHERE UPPER(TRIM(bt.txt)) LIKE '%ABRASION%'
OR UPPER(TRIM(bt.txt)) LIKE '%DERMATOLOGICAL%'
OR UPPER(TRIM(bt.txt)) LIKE '%PSORIASIS%'
The equivalent regular expression search is:
SELECT *
FROM big_table bt
WHERE REGEXP_LIKE(UPPER(TRIM(bt.txt)),
'(ABRASION|DERMATOLOGICAL|PSORIASIS)')
The regular expression syntax is cleaner, especially when you have a
lot of strings to search for! However, the LIKE expression runs in 20
seconds, while the REGEXP_LIKE one runs in 60 seconds. Has anyone
else noticed this? Any way to speed it up?
BTW there's some kind of limit to the string you can pass to
REGEXP_LIKE. You'll get the error message: ORA-12733: regular
expression too long
Thanks,
Zstringer
How about Oracle Text? It's as simple as
CREATE INDEX IX$CTX$BIG_TABLE#TXT ON BIG_TABLE (TEXT) INDEXTYPE IS
CTXSYS.CONTEXT PARAMETERS ('FILTER NULL_FILTER SYNC ON COMMIT')
/
(be aware that SYNC ON COMMIT option is unsafe in 10.1 up to 10.1.0.5
- there are a couple of bugs with it that can corrupt the index on
subsequent optimization, fixed in 10.1.0.5 and, I believe, 10.2.0.2)
and then you query like this:
SELECT * FROM BIG_TABLE
WHERE CONTAINS(TXT,'abrasion OR dermatological OR psoriasis') > 0
or
.... CONTAINS(TXT,'about(abrasion) or about(psoriasis)') > 0
or ... well, I won't go into copying the Text Reference here, check it
out yourself... :) Using Text should reduce your query response times
dramatically while being much more flexible with regard to the way you
search for the right information in unstructured data in and outside
of the database. Just open the Text Reference and discover a whole new
world of powerful and efficient full text indexing and searching
supplied by Oracle out of the box. And it comes with all Oracle
editions for no extra cost. :)
Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
.
- References:
- Performance of REGEXP_LIKE vs LIKE?
- From: zstringer999@xxxxxxxxx
- Performance of REGEXP_LIKE vs LIKE?
- Prev by Date: Re: Performance of REGEXP_LIKE vs LIKE?
- Next by Date: java.sql.SQLException: No suitable driver
- Previous by thread: Re: Performance of REGEXP_LIKE vs LIKE?
- Next by thread: java.sql.SQLException: No suitable driver
- Index(es):
Relevant Pages
|
