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.

score 7 · Accepted Answer · edited May 23 '17 at 11:54

7

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

edited May 23 '17 at 11:54

Community

1
1

answered Oct 13 '11 at 07:24

Kevin Burton

11,676
2
24
37

1

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? – Clinton Oct 13 '11 at 09:26
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. – Kevin Burton Oct 13 '11 at 09:55

score 1 · Answer 2 · edited Apr 04 '18 at 14:36

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~~

score 0 · Answer 3 · answered Oct 13 '11 at 04:25

0

Looking at your problem if your database is big then you can use Sphinx Search

Sphinx is an open source full text search server, designed from the ground up with performance, relevance (aka search quality), and integration simplicity in mind. It's written in C++ and works on Linux (RedHat, Ubuntu, etc), Windows, MacOS, Solaris, FreeBSD, and a few other systems

answered Oct 13 '11 at 04:25

Wazy

8,822
10
53
98

This looks interesting, but sadly I'm stuck with Oracle 10g. – Clinton Oct 13 '11 at 04:29

score 0 · Answer 4 · answered Oct 13 '11 at 04:37

0

You can do that in Oracle only if you have intermedia/Oracle Text option on the server...

For your example you could use

create index t_index_data on t(data) 
indextype is ctxsys.context 
parameters ('DATASTORE CTXSYS.DEFAULT_DATASTORE');

I am not sure if you need to change the type from varchar2(100) to clob .

For details and options/example regarding this sort of indexes see http://download.oracle.com/docs/cd/A91202_01/901_doc/text.901/a90122/ind4.htm

answered Oct 13 '11 at 04:37

Yahia

69,653
9
115
144

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? – Clinton Oct 13 '11 at 04:41
you could be right regarding the preference - I don't have an Oracle server with those options available so I can't test it... – Yahia Oct 13 '11 at 04:44

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

4 Answers4

Linked