Oracle Text Concept-- Like operator Replaced with Oracle Text which improves better performance

In this process i  want to replace like operator in where clause which is going for full table scan in some package. Oracle suggested replace the like operator with oracle text concepts. There are two ways to implement this 

·          Catsearch

·          Context

Catsearch:

           Catsearch is the best way to implement because  here the index will be auto updated  and we will get more matches in record. In our case it failed because its giving more record than which we are getting in like operator. Because its searching by fragment by fragment. But in our case it should start from start of the letter.

Context:

           This is done by contains clause. Here the index is not auto updated and we can  make it auto-commit by giving sync commit while creating the index. Created datastore and wordlist to perform index more better. And set attributes on the column which need to be indexed and prefix_index which would improve the performance. And set wildcard terms to 5000 which would avoid conversion error.

Example:

begin

 ctx_ddl.create_preference('spm_full_ename_ds', 'multi_column_datastore');

 ctx_ddl.set_attribute('spm_full_ename_ds', 'columns', '''xxstart'',spm_full_ename');

 ctx_ddl.set_attribute('spm_full_ename_ds', 'delimiter', 'NEWLINE');

 ctx_ddl.create_preference('spm_full_ename_wl', 'BASIC_WORDLIST');

ctx_ddl.set_attribute('spm_full_ename_wl','PREFIX_INDEX','TRUE');

ctx_ddl.set_attribute('spm_full_ename_wl','PREFIX_MIN_LENGTH',1);

ctx_ddl.set_attribute('spm_full_ename_wl','PREFIX_max_LENGTH',15);

 CTX_DDL.SET_ATTRIBUTE ('spm_full_ename_wl', 'WILDCARD_MAXTERMS', 50000);

end;

Now while creating index  added nationality in the filter, because in some cases nationality is not considered as index and going for full table so made  nationality in the filter by and now it as composite index.

Index:

Create index idx_cont_spm_ename on list_master(spm_full_ename) indextype is ctxsys.context  filter by nat_code_curr_nat

parameters ('wordlist spm_full_ename_wl  datastore spm_full_ename_ds SYNC(ON COMMIT)') local;

If our system is RAC node oracle suggested to  alter the index to parallel to improve the performance

alter index idx_cont_spm_ename parallel 1;

Now after creating the index we will face response time is more for the query for that oracle suggested  some hints which would return the records in faster manner and optimize the cost of the query.

/*+ domain_index_no_sort */  and /*+ first_rows(1) */

First_rows() hint:

we can also optimize for response time using the related FIRST_ROWS hint. Like FIRST_ROWS(n), when queries are optimized for response time, Oracle Text returns the first rows in the shortest time possible.

 domain_index_no_sort:

Example:

SELECT /*+ first_rows(1)  domain_index_no_sort  */ id sub_sys_individual_id,

             spm_full_ename nm_e,

             spm_full_aname nm_a,

            -- func_get_moidata_gender(spm_person_no) sex,

             nat_code_curr_nat prs_nat,

             spm_person_no person_no,

             null person_tp,

             'NA' prog_where_not_allowed,

             spm_person_no udb_no

        FROM list_master

       WHERE (ls_code IN (2, 3))

    and contains(spm_full_ename,'xxstart AYOUB GHOLAMREZA AHMADI%') >0

        AND (((date_from <= trunc(SYSDATE) OR date_from IS NULL) AND

             (date_to >= trunc(SYSDATE) OR date_to IS NULL) AND

             (duration_flag = 2)) OR (duration_flag = 1));

ctx_ddl.create_preference and Set_attribute:

To create a datastore, lexer, filter, classifier, wordlist, or storage preference,we can  use the CTX_DDL.CREATE_PREFERENCE procedure.

Multi column Datastore: Data is stored in a text table in more than one column. Columns are concatenated to create a virtual document, one for each row.

we can also set attributes with the CTX_DDL.SET_ATTRIBUTE procedure.

Newline attribute:

Specify TRUE for Oracle Text to add no newline character after each detail row.

Prefix_index attribute:

In our case we are  using right-truncated predicate. For that oracle suggested to use the prefix_idex attribute by setting min and max values.

WILDCARD_MAXTERMS:

As wre are wild card character in our search % we are setting this  wildcard maxteerms as 5000. In wordlist we are setting this.

When we execute a query using wildcard(%), the term you used in a query is used to generate another list of terms (say match_list) from token_list.

Match_list consists of all the terms which have matched the wildcarded term - whith 0 or more characters to the query term in place of wildcard.

SYNC(ON COMMIT):

As we know context  search is not auto update we are updating the index on commit. So this sync( on commit) is used.

Local:

Since our table is partitioned  by creating the index as local. As we are using first_rows and domain_index_no_sort hint we cam improve the query performance and the response time. If a table is range or hash partition only we can create this index as local.v