Re: Oracle text search is slow. Need help
Date: Sat, 16 Feb 2008 20:40:12 GMT
In article <b8261900-f4f8-429f-8323-753407201418_at_d4g2000prg.googlegroups.com>, sunny <sunilg33_at_gmail.com> wrote:
>On Feb 15, 6:08 pm, spamb..._at_milmac.com (Doug Miller) wrote:
>> In article
> sunil..._at_gmail.com wrote:
>> >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.
>> What indexes, if any, do you have on arc_44?
>> What does the execution plan show?
>I use the following query to index the arc_44 table
>create index ind_full_text_arc_23 on arc_23(full_text) indextype is
>ctxsys.context parameters('Wordlist wildcard_pref storage
That looks more like an index on the arc_23 table, not arc_44.
What indexes, if any, do you have on arc_44? What does the execution plan show?
-- Regards, Doug Miller (alphageek at milmac dot com) It's time to throw all their damned tea in the harbor again.Received on Sat Feb 16 2008 - 14:40:12 CST