OraFAQ Forum: Text & interMedia » speed up 'like' queries

Home » Server Options » Text & interMedia » speed up 'like' queries - Oracle Text API? (Oracle Database 10g Release 10.2.0.3.0, Windows Server 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

speed up 'like' queries - Oracle Text API? [message #331092]

Wed, 02 July 2008 05:23

smora
Messages: 59
Registered: May 2006

Member

Hi

I have a table with sub_id, sub_username fields. To search for usernames, there is a query that uses the

sub_username like '%XXX%'

clause. This is extremely slow even with an index on the sub_username field, the table has ~2 million rows. Can this be speeded up somehow?

I looked into using Oracle Text API, but that seems to be for searching for a single word or phrase within a document, whereas I need to speed up searches on a column that contains a single word as its value.

Any help is appreciated.
SM

Report message to a moderator

[Message index]

		speed up 'like' queries - Oracle Text API? By: smora on Wed, 02 July 2008 05:23
		Re: speed up 'like' queries - Oracle Text API? By: ebrian on Wed, 02 July 2008 16:08
		Re: speed up 'like' queries - Oracle Text API? By: Barbara Boehmer on Wed, 02 July 2008 19:28

Previous Topic:	Displaying search results from table and CTX_DOC.SNIPPET - Returning error messages (merged by bb)
Next Topic:	Merge columns from dif tables in one index and markup the result

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 23:15:09 CDT 2024