Oracle text search is slow. Need help

From: <>
Date: Fri, 15 Feb 2008 04:31:12 -0800 (PST)
Message-ID: <>

We have oracle 10g r2 running on RHEL4 iwth 4g ram

We are using oracle text indexing feature to index LOB columns. But the query is taking more than a minute. Can someone please help me on this.

Here is the code

select * from arc_44 where FILE_DATE >= to_date('01/01/2001','dd-mm- yyyy') and FILE_DATE <= to_date('15/12/2001', 'dd-mm-yyyy') and ( contains(FULL_TEXT,'malaysia' ) > 0 or contains(CONTENT,'malaysia' ) > 0 or upper(META_DATA) LIKE upper('%malaysia%') ) ;

Here FULL_TEXT and CONTENT are CLOB datatypes.

Thanks in advance Received on Fri Feb 15 2008 - 06:31:12 CST

Original text of this message