6

Question: What is the most performant way to do a case-insensitive search in a CLOB column?

I've simplified the query to easily reproduce the problem. The filedata table can have millions of rows. Many rows can have the same fileid. The fileid column is indexed. In cases where a particular fileid matches many rows, the proc is really slow.

select * from filedata where fileid=2148102100 AND REGEXP_LIKE (dataClobfield, 'Some Text Here', 'i');

This query completes in 130 seconds. 16 rows are returned by this query, but there 400k rows matching the fileid value (in other words, if I removed the regexp_like where clause, I would get 400k with a fileid of 2148102100).

If I change this to

select * from filedata where fileid=2148102100 AND dataClobfield like '%Some Text Here%';

It completes in 32 seconds but the search is case-sensitive, which does not meet the requirements.

I've also tried

select * from filedata where fileid=2148102100 AND LOWER(dataClobfield) like '%some text here%';

This returns in 83 seconds... better than the regexp_like, but still not great.

Other things I've read about haven't worked: setting the session NLS_SORT=BINARY_CI (doesn't work with CLOBs), using the DBMS_LOB.instr function (case sensitive). Ideally I'd like to get this down to 30 seconds. Any ideas?

7
  • Are there any other non-*LOB fields besides fileid that you could use to filter your results even more?
    – JayJay
    Jun 2 '17 at 18:27
  • 3
    Any reason why you're not using Oracle Text?
    – mustaccio
    Jun 2 '17 at 18:59
  • @JayJay No, unfortunately.
    – pandoh
    Jun 5 '17 at 13:01
  • @mustaccio I had assumed Oracle Text required additional licensing, but from what I can tell it comes as part of Oracle. I could look into that for a future version of our product, but let's assume for now that I'm stuck NOT using Oracle Text. Any other ideas?
    – pandoh
    Jun 5 '17 at 13:03
  • 3
    I wouldn't trust much of what's posted on Mr. Burleson's site (when it's not lifted off Oracle documentation, anyway). You can choose the schedule of text index synchronisation.
    – mustaccio
    Jun 5 '17 at 13:28
0

Searching without any kind of index seems to be not a good idea. And an analysis without further details is difficult. But you can add resources to improve your speed. It seems that the bottleneck of your query is CPU and not the disks. If disk speed is the limitation there would not be a difference between regexp and LIKE.

  1. You can try to parallelize your query. If the work is done by 4 processes instead of one, it is possible that the query response tim is a quarter of the original time.

  2. you can add an additional clob field that contains the content of dataClobfield in uppercase. this field is used for searching the records you are interested in

-3

What about using or to match both lower- and uppercase?

select *
from filedata
where fileid = 2148102100 and 
      (dataClobfield like '%some text here%' or dataClobfield like '%SOME TEXT HERE%');

I might perform better than the 83-second case, as it avoids operation on column values.

1
  • 2
    Case insensitive is not limited to only upper- and only lower-case, it may contain a mixture. Feb 24 '20 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.