How to create text index for '%abc%' search?

Question

I'd like to index queries like x like '%abc%'

If I have a table like the following

create table t
(
  data varchar(100)
);

I want to create an index to be able to do the following efficiently:

select * from t where contains('%abc%');

And this:

select * from t where contains('abc%');

I also want this table to be updated live.

How do I create such an index? (I have a feeling I need a ctxcat index, but I'm confused about what options I need to give it)

I'm using Oracle 10g.

Answer 1

I would use this (set you min and max length to appropiate values)

BEGIN
    ctx_ddl.create_preference  ('FT_WL', 'BASIC_WORDLIST');
    ctx_ddl.set_attribute      ('FT_WL', 'substring_index',   'YES');
    ctx_ddl.set_attribute      ('FT_WL', 'prefix_index',      'YES');
    ctx_ddl.set_attribute      ('FT_WL', 'prefix_min_length', 1);
    ctx_ddl.set_attribute      ('FT_WL', 'prefix_max_length', 6);
  END;

CREATE INDEX fulltext_idx ON tmp_fulltext (fulltext)
 INDEXTYPE IS CTXSYS.CTXCAT
 PARAMETERS ('WORDLIST FT_WL')

The parameters are explained here Oracle Text Reference

and see this question on how to manage the refresh and how the index may not be quicker than a full scan with high cardinality data:

PL/SQL Performance Tuning for LIKE '%...%' Wildcard Queries

Answer 2

Yes, you need to create an environment before you can create domain indexes. You need to have ctxsys user and necessary ctxapp privileges to create it. Follow the steps explained in this link to have one for your environment. This user is not created by default while installing Oracle.

Once you have the all the grants and packages you can create preferences and index as shown.

SQL> begin
  2  ctx_ddl.create_preference('SUBSTRING_PREF', 'BASIC_WORDLIST');
  3  ctx_ddl.set_attribute('SUBSTRING_PREF', 'SUBSTRING_INDEX','TRUE');
  4  end;
  5  /

Now create a domain index as shown.

 SQL> create index test_idx on test(object_name)
   2  indextype is ctxsys.context parameters ('wordlist SUBSTRING_PREF MEMORY 50M');

Index created.

select * from test where contains( object_name,'%EXEC%') > 0;

See the link below which explains this with the execution plan. Update 2018: The original link is dead and not backed up on archive.org, unfortunately.

http://www.oraclebin.com/2012/12/oracle-text-and-domain-indexes.html