By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,557 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,557 IT Pros & Developers. It's quick & easy.

optimize DB2 query

P: n/a
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!

Apr 17 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.
Apr 17 '06 #2

P: n/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.
Apr 18 '06 #3

P: n/a
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

Apr 18 '06 #4

P: n/a
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.

Apr 19 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.