Re: Oracle text search is slow. Need help

On Feb 15, 6:08 pm, spamb..._at_milmac.com (Doug Miller) wrote:
> In article <da296d0b-8953-4eb7-bace-58e572e55..._at_s19g2000prg.googlegroups.com>, 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 default_storage') ; Received on Sat Feb 16 2008 - 00:22:39 CST