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 next 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.
Re: Text search in SQL query [message #362070 is a reply to message #362046] Sun, 30 November 2008 09:46 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

There could be some other better methods of doing it.

Don't you think you need only one OR condition as

AND ((MSGTEXT LIKE 'CPM%')
OR (MSGTEXT LIKE '%' || CHR (10) || 'CPM%'))


Smile
Rajuvan.
Re: Text search in SQL query [message #362088 is a reply to message #362046] Sun, 30 November 2008 13:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
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.

SCOTT@orcl_11g> CREATE TABLE test_tab
  2    (id	  NUMBER,
  3  	rcvd_dt   DATE,
  4  	msgtext   VARCHAR2(15),
  5  	toptext   VARCHAR2(15),
  6  	text_cols VARCHAR2(1))
  7  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO test_tab VALUES (1, SYSDATE - 1, 'CPM EK12', 'DXB', NULL)
  3  INTO test_tab VALUES (2, SYSDATE - 1, 'CPM EK12', 'WHATEVER', NULL)
  4  INTO test_tab VALUES (3, SYSDATE - 1, 'ABC DEF', 'WHATEVER', NULL)
  5  INTO test_tab VALUES (4, SYSDATE, 'CPM EK12', 'WHATEVER', NULL)
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('test_multi', 'MULTI_COLUMN_DATASTORE');
  3    CTX_DDL.SET_ATTRIBUTE ('test_multi', 'COLUMNS', 'msgtext, toptext');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX test_text_idx ON test_tab (text_cols)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('DATASTORE test_multi')
  4  /

Index created.

SCOTT@orcl_11g> SELECT *
  2  FROM   test_tab
  3  WHERE  CONTAINS (text_cols, 'CPM AND EK12 NOT DXB') > 0
  4  AND    TRUNC (RCVD_DT) = TRUNC (SYSDATE-1)
  5  /

        ID RCVD_DT   MSGTEXT         TOPTEXT         T
---------- --------- --------------- --------------- -
         2 29-NOV-08 CPM EK12        WHATEVER

SCOTT@orcl_11g> 


Re: Text search in SQL query [message #362234 is a reply to message #362088] Mon, 01 December 2008 06:51 Go to previous messageGo to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
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.."

What should I do?
I am using Oracle 9.2.

Thnx.
-Lenin.

Re: Text search in SQL query [message #362265 is a reply to message #362234] Mon, 01 December 2008 09:42 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps CTXSYS has to grant "execute" on CTX_DDL to the user which is supposed to use it?
Re: Text search in SQL query [message #362273 is a reply to message #362265] Mon, 01 December 2008 11:59 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Littlefoot wrote on Mon, 01 December 2008 07:42
Perhaps CTXSYS has to grant "execute" on CTX_DDL to the user which is supposed to use it?


Yes, either that or the CTXAPP role. This also assumes that you have Oracle Text installed, which can be confirmed by checking whether there is a ctxsys user.
Previous Topic: not contains and performance issue
Next Topic: Text index performance guidelines
Goto Forum:
  


Current Time: Thu Mar 28 07:25:51 CDT 2024