Hi Guruji,
I need to search 3 different texts in 2 columns (Every data has 3000 characters in each column). It is taking huge amount of time. How could I increase the performance of the query?
1. Creating Index? (if index created on the text columns will help?).
2. Is there any regular functions like in 10g?

The following is the where clause in my query.
WHERE TRUNC (RCVD_DT) = TRUNC (SYSDATE-1)
AND (INSTR (SUBSTR (MSGTEXT, 1, 30), 'CPM') > 0 )
AND INSTR (MSGTEXT, 'EK12') > 0
AND TOPTEXT NOT LIKE '%.DXB%'
AND ((MSGTEXT LIKE 'CPM%')
OR (MSGTEXT LIKE '%' || CHR (10) || 'CPM%')
OR (MSGTEXT LIKE CHR (10) || 'CPM%'))
thank you in advance.
-Lenin.

Oracle TEXT is designed for such things. You can create a multi_column_datastore and use it as a parameter when creating a context index, then you can search quickly for various words in any of the columns in the datastore in various ways. The following is a simple example that searches for any rows that have both CPM and EK12 but not DXB plus the date condition. There are many other conditions that can be specified.

Hi Barbara,
thank you for your valuable assist.
When I try to excecute the CTX_DDL.CREATE_PREFERENCE and CTX_DDL.SET_ATTRIBUTE, the follwoing error is appeared.
"..PLS-00201: identifier 'CTX_DDL' must be declared.."