Andrew Fraser DBA

Oracle Full Text simple example for like % grep wildcard

The equivalent of the grep command in oracle is like:

SELECT * FROM dba_source s WHERE LOWER ( s.text ) LIKE '%within group%' ;

That works fast enough for querying dba_source, which is a small dataset. But for even medium sized tables, you really want a fast indexed query, which the “LIKE ‘%” syntax ordinarily rules out.

Oracle’s fast indexed equivalent of grep is catsearch:

SELECT * FROM dba_source s WHERE CATSEARCH ( s.text , 'within group' , NULL ) > 0 ;

That last command will fail with:

ORA-20000: Oracle Text error: DRG-10599: column is not indexed

until a full text index is created on the column:

CREATE TABLE t1 AS SELECT * FROM dba_source ;
CREATE INDEX i1 ON t1 ( t1.text ) INDEXTYPE IS CTXSYS.CTXCAT ;
SELECT * FROM t1 WHERE CATSEARCH ( t1.text , 'within group' , NULL ) > 0 ;

Once that index is created, the catsearch query runs without error, and runs very fast.

Catsearch is case insensitive, but it does behave differently from “LIKE ‘%term%'” in that it searches for occurrences of words rather than text strings. A catsearch of ‘within group’ is the same as “grep -i within | grep -i group”, rather than “grep -i ‘within group'”. Word boundaries are enforced with catgroup, so the below catsearch returns only rows 1-3, misses out row 4 even though that would be caught by “LIKE ‘%smith%'” type conditions:

INSERT INTO t1 ( text ) VALUES ( '1 Customer Smith info' ) ;
INSERT INTO t1 ( text ) VALUES ( '2 Customer smith info' ) ;
INSERT INTO t1 ( text ) VALUES ( '3 Customer-Smith-info' ) ;
INSERT INTO t1 ( text ) VALUES ( '4 CustomerSmithInfo' ) ;
SELECT * FROM t1 WHERE CATSEARCH ( t1.text , 'smith' , NULL ) > 0 

For most applications, that word boundary behaviour would be acceptable or even desirable.

The ctxsys.ctxcat index is fully transaction aware by default (unlike some other full text index types), even detecting changes in current session prior to commit.

The create index statement above at the same time, under the covers, creates an associated table and trigger. So the index owner needs (at least temporarily) the create table and create trigger privileges, either granted directly or via a role.

Without those two privileges the create index statement will fail with:

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvxtabc.create_indexes:TC
ORA-01031: insufficient privileges
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CATINDEXMETHODS", line 99

The index does take up significantly more space than a standard b-tree index, but likely a justifiable use of space if you have leading wildcard “LIKE ‘%string%'” type queries.