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
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
CREATE TABLE names ( id NUMBER(10) NOT NULL, name VARCHAR2(200) NOT NULL ) /
CREATE INDEX my_names_idx ON names(name) INDEXTYPE IS CTXSYS.CTXCAT
Insert into names values (1, 'Ammar Wael Sajdi');
Insert into names values (2, 'Ammar Abdo');
Insert into name values (3,'Wael Sajdi');
Commit;
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