Table fragmetation question

Hi, I have a nonfragmented table with 55,000 rows and about 350 bytes wide on an OLTP system. I need to add functionality to search on a text field which is 50 bytes wide. The user will be searching the text field using the like or contains clause. So it is probable that sequential scan can occur. I am looking for recommendations on how I can speed up a contains (like '%x%') clause query. I can put an index on the field but a contains clause will initiate a sequential scan. I have tried fragmenting the table over 3 dbspaces, but performance has not improved much. How can I speed up this query, without turning on PDQ? select client,nm from client where nm like '%myclient%' and stat!='C' TIA. Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

Well, the whole table is about 20 megabytes. Buy enough memory, set BUFFERS high enough to hold it. Doesn't get any faster than that. With memory at $1 to a few bucks a megabyte these days, who needs finesse?? Greg jsmith112469@my-deja.com wrote: > > Hi, > > I have a nonfragmented table with 55,000 rows and about 350 bytes wide > on an OLTP system. I need to add functionality to search on a text > field which is 50 bytes wide. The user will be searching the text > field using the like or contains clause. So it is probable that > sequential scan can occur. I am looking for recommendations on how I > can speed up a contains (like '%x%') clause query. I can put an index > on the field but a contains clause will initiate a sequential scan. I > have tried fragmenting the table over 3 dbspaces, but performance has > not improved much. How can I speed up this query, without turning on > PDQ? > > select client,nm > from client > where nm like '%myclient%' > and stat!='C'> > TIA. > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.