Oracle TEXT search question
Posted on 2016-11-01
Have a need for the TEXT search condition. After I indexed the column description in T1 with Oracle TEXT, I use the syntax "contains"
select count(1) from t1 where contains( lower( description), 'abcd', 1) >0;
However I got the error complaining "lower( description)" is not indexed. I knew it was not for this, but how should I deal with this situation? If I do the following, it is fast, but it is not the way to form the sql.
select count(1) from t1 where contains( description, 'abcd', 1) >0;
select count(1) from t1 where contains( description, 'Abcd', 1) >0;
(for normal where clause: select count(1) from t1 where lower( description) like 'abcd'; when the table is big, it is too slow)