Searching within character fields efficiently without using Like

There are situation when you have Textual data that you want to search.  Normally people would use the LIKE operator with % (As a wild card).  The problem with % is that it inhibits the use of index if it is used in the leading part of the string i.e    LIKE (%xyz).

Oracle includes other approaches that i am familiar with since the 90s which was introduced as part of Oracle Text component.  This approach is suitable for searching really large chunks of data like those found is big PDF, Word  articles.  It uses  CONTEXT indexing.  Since this kind of indexing is meant for big articles, you cannot expect the index to work Online , otherwise once you add a new article, the insert operation will take long due to the associated indexing operation. The CONTEXT indexing is therefore performed in batch mode at a later stage.  Before the indexing operation is completed your search will not included the articles that are not yet indexed

I recently read about another approach that I liked very much because it is suitable for application which has a reasonably sized chunks of data (varchar2(2000)). Additionally, it index works online, meaning that as you add new data, the index works immediately and therefore, the user would immediately get the new data in the search results

CTXCAT Indexes

It is called CTXCAT index, I used successfully for search for names where all the syllables of the names are included in one field

here is an example


  id           NUMBER(10)      NOT NULL,
  name         VARCHAR2(200)   NOT NULL
Insert into names values (1, 'Ammar Wael Sajdi');
Insert into names values (2, 'Ammar Abdo');
Insert into name values (3,'Wael Sajdi');


Now to search for a field with Ammar you need to use the CATSERACH operator

Select * from names  WHERE  CATSEARCH (name ,'Ammar') > 0

To search for a field with Ammar or Sajdi

Select * from names  WHERE  CATSEARCH (name ,'Ammar or Sajdi') > 0    note : you can also use & | etc