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

Question

I would 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 in order to be able to do the following effectively:

 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? (It seems to me that I need the ctxcat index, but I'm confused about what parameters I need to give)

I am using Oracle 10g.

Answer 1

I would use this (set the minimum and maximum length to match the 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') 

Oracle Text Reference options are described here.

and look at this question about how to manage the update and how the index can be no faster than a full scan with high power data:

PL / SQL performance tuning for LIKE '% ...%' Wildcard queries

Answer 2

Yes, you must create an environment before you can create domain indexes. To create it, you need the ctxsys user and the necessary ctxapp previliges. Follow the steps in the link below to have one for your environment. This user is not created by default when installing oracle.

http://www.oraclebin.com/2012/12/creating-environment-for-ctxsys.html

Once you have all the grants and packages, you can create the settings and indexes 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 the 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 that explains this with the execution plan.

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

Answer 3

Looking at your problem, if your database is large, you can use Sphinx Search

Sphinx is an open source full-text search engine designed from the ground up with efficiency, relevance (aka search quality) and easy integration. It is written in C ++ and runs on Linux (RedHat, Ubuntu, etc.), Windows, MacOS, Solaris, FreeBSD, and several other systems.

Answer 4

You can do this in Oracle only if the server has the intermedia / Oracle Text option ...

In your example you can use

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

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

For more information about the parameters and examples of this example, see http://download.oracle.com/docs/cd/A91202_01/901_doc/text.901/a90122/ind4.htm