I am using the below to get the top count of pages visited in past 60 days, where I need a match in the url for 'abc.cfm' or '/entity/'. Is there a way to do this using regex? Is the below method ok to use or is there a better way. Thanks in advance.
SELECT url, count(URL)
FROM tableone
WHERE ( INSTR(url, 'abc.cfm') > 0
or
INSTR(url, '/entity/') > 0 )
and VIEW_DT > sysdate - 60
group by URL
order by count(URL) desc
Just use like. This will usually be a bit faster than instr and much faster than regexp_like:
WHERE ( url like '%abc.cfm%'
or url like '%/entity/%'
)
I tested on a table with 1.8M rows of which 700 matched the criteria. The approximate timings from repeated tests were:
instr 0.9 seconds
regexp_like 15.0 seconds
like 0.6 seconds
I would only use regexp_like for expressions that are too complex for like.
Try
regexp_like(url,'(abc.cfm|/entity/)')
instead of
( INSTR(url, 'abc.cfm') > 0
or
INSTR(url, '/entity/') > 0 )
