optimize DB2 query

[bev.greg]

I want to start a query with wildcard at the beginning of a string e.g.
"select column1 from table1 where name like '%string'", or at both ends
of the string, e.g. "select column1 from table1 where name like
'%string%'", the speed is a lot slower than when the wildcard is only
at the end of the string 'string%', is there a way to get the query
faster? please help!

[Gert van der Kooij]

In article <11**********************@z34g2000cwc.googlegroups .com>,
be******@gmail.com says...

I want to start a query with wildcard at the beginning of a string e.g.
"select column1 from table1 where name like '%string'", or at both ends
of the string, e.g. "select column1 from table1 where name like
'%string%'", the speed is a lot slower than when the wildcard is only
at the end of the string 'string%', is there a way to get the query
faster? please help!

No, when you use the % at the start of a like predicate the optimizer
always decides to do a full table scan.

[Mark A]

"Gert van der Kooij" <no****@nl.invalid> wrote in message
news:MP************************@news.xs4all.nl...

No, when you use the % at the start of a like predicate the optimizer
always decides to do a full table scan.

Not necessarily. DB2 may do a complete scan of the index (assuming there is
an index on that column) by reading the index leaf pages sequentially
instead of using the b-tree. If no index is available, or the index is not
much smaller than the table, then a table scan would probably be used.

In order to use the b-tree of an index, the left most part of the index
needs to be supplied in the predicate (LIKE 'XXX%'). The same is true if a
predicate only supplies the 2nd column in a multi-column index.

[ChrisC]

Depending on how much work you want to do and how much control you have
over the database, you can at least speed up the ... like '%string'
query.

To do this, you would need to add an additional column to the table,
and store the contents of the first column in reverse order (so, if the
original column had 'string', then the new column would have 'gnirts').
Then, when you need to query for like '%string', you would instead use
the new column and query for 'gnirts%'.

Of course, for this to work, you need complete control over loading the
table and probably programatic control over the querying of the table
as well. Not a trivial task.

-Chris

[bev.greg]

Thanks everyone for your posts. All tables are created by a 3rd party
application, I have no control on the structure of the table but I can
define how to use the columns to save contents I want. Chris, I'm very
interested in your suggestion, and will definitely like to take a try.