I have a table that has 5 VARCHAR2 columns that I need to do a "text" search on. The WHERE clause used to be:
DIST_NUM = '088880'
AND UPPER(field1) LIKE '%SOMETHING%'
AND UPPER(field2) LIKE '%SOMETHINGELSE%'
AND UPPER(field3) LIKE '%HIMOM%'
AND UPPER(field4) LIKE '%WHATEVER%'
AND UPPER(field5) LIKE '%LASTONE%'
There are two tables to index this way, one that is static and has about 4 millions rows, and one that will be growing at a rate of about 200,000 a month with an initial count of about 1 million.
Obviously the LIKE clause causes a full table scan, and I tried a CONTEXT DOMAIN index but when switching the LIKE clauses to CONTAINS, it only seems to use one of the DOMAIN indexes. I read about CTXCAT, but I need multiple wildcard clauses. The VARCHAR2 data in each of these columns is not very large at all, maybe 255 at most.