CODEWITHSUNDEEP
sqloracle-databasefull-text-searchindexing
Clinton
Clinton

Reputation: 23145

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

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.

Upvotes: 6

Views: 15586

Answers (4)

Sushant Butta
Sushant Butta

Reputation: 530

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

Upvotes: 1

Kevin Burton
Kevin Burton

Reputation: 11924

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

Upvotes: 7

Yahia
Yahia

Reputation: 70369

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

Upvotes: 0

Wasim Karani
Wasim Karani

Reputation: 8886

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

Upvotes: 0

Related Questions