Home » Server Options » Text & interMedia » Text search in SQL query (Oracle 9i)
Text search in SQL query [message #362046] Sun, 30 November 2008 04:34 Go to previous message
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.
 
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: not contains and performance issue
Next Topic: Text index performance guidelines
Goto Forum:
  


Current Time: Fri Mar 29 00:33:34 CDT 2024