File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Delayed result with 'not like' clause in Where clause Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Delayed result with Watch "Delayed result with New topic

Delayed result with 'not like' clause in Where clause

Sajee Joseph
Ranch Hand

Joined: Jan 17, 2001
Posts: 200
Hello all,
I am new to database.I have a problem with my sql query. It takes 20 secs to execute (this return 127 rows). My query has a 'not like "%xyz%" ' clause in its where clause.
When i change this 'not like' clause to 'like' clause, it returns me 137, but does this just in 1 second.
The table has around 50000 records in it.
Can someone tell me how to tune my original query to execute faster?? Has anyone experienced any problems with 'not like' clause.??
SJ Adnams
Ranch Hand

Joined: Sep 28, 2001
Posts: 925
loads of things you can try.
be a bit more specific about your application, if your doing general word searching take a look at intermedia text searching.
If the 'not like' clause is always the same, then a function based index is the way to go.
50,000 isn't that big a table. how much ram do you have, sga size etc?
maybe ask in the forums also.
I agree. Here's the link:
subject: Delayed result with 'not like' clause in Where clause
It's not a secret anymore!