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.

Solution 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

Solution 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

Comments

[Clinton]

This looks interesting, but sadly I'm stuck with Oracle 10g.

[Clinton]

Are you sure this indexes substrings? I read the document, and it seems like I need a "preference" "SUBSTRING_INDEX" or something. Can you give a code example that does what I'm looking for including the substring index?

[Yahia]

you could be right regarding the preference - I don't have an Oracle server with those options available so I can't test it...

[Clinton]

Thanks for the answer. Just a few questions: Is prefix_index for abc% queries and 'substring_index' for %abc% queries? And does the substring index by default index all substrings? Also, how do I use this index? Will contains(col, '%abc%') and contains(col, 'abc%') do the trick?

[Kevin Burton]

Yes those searches will work, have updated with a link that can explain it better than me, but essentially the parameters are used to improve the performance of exactly that type of search.