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?

  • 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
  • 1
    Any reason why you're not using Oracle Text? – mustaccio Jun 2 '17 at 18:59
  • @JayJay No, unfortunately. – jubil 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? – jubil Jun 5 '17 at 13:03
  • 2
    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

Your Answer

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

Browse other questions tagged or ask your own question.