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?