Text search in SQL query [message #362046] |
Sun, 30 November 2008 04:34 |
catchme_lenin
Messages: 35 Registered: January 2008 Location: Dubai
|
Member |
|
|
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.
|
|
|