Actually, you can have the % at the front. And for an index only,
you will get
an index match of 1 and indexscan.
From what Pat Bossman tells me, the problem is that there aren't
for DB2 to use the index if there is an index/data search, unless I
where clause as NAME = :HV. Then there will be an index scan and
retrieval. DB2 thinks that a tablespace scan is more efficient than
scan and subsequent random table I/O. Random I/O is very costly and
to avoid it at all cost, thus the preference for tablespace
Pat suggested four alternatives:
1) using a host variable:
2) using OPTHINT to fool DB2 into using the index
3) Partitioning the tablespace into multiple parts, thus achieving
4) Using the DB2 Text Extender to accomplish this.
The last suggestion actually intrigues me the most. I'm going to
implementing that this week.
--- Jackson Reavill <[login to unmask email]> wrote:
> Hey Myron,
> I think what Dave meant was that you have to specify the like
> like... 'value%'. You can't have the percent at the front.
> It kinda goes along the same lines as having to have the first
> specified of a multiple column index to be able to get a
matchcol. So in
> the case where it's index only it will use the index, but if
it has to go
> to the data anyway then why mess with the index. I guess it
> make use of some index screening. BTW, what is your matchcol
when it is
> using the index?
> Jay Reavill
> [login to unmask email]
> IBM Global Services
> Tampa, Florida
> Tel: (813) 356-5317, Tie Line 8-697-5317
> Happiness is not around the corner.
> Happiness is the Contour (SVT) of the road.
> The early bird gets the worm,
> but the second mouse gets the cheese.
> Myron Miller
> <[login to unmask email] To:
> [login to unmask email]
> OO.COM> cc:
> Sent by: DB2 Data Subject: Re: Indexing Like
> Base Discussion
> <[login to unmask email]
> 12/20/02 12:15 PM
> Please respond to
> DB2 Data Base
> Discussion List
> Actually my example was much simplified. We can't break it
into its own
> and actually satisfy all the requirements. Its been tried and
> work without awful complex and innummerable subqueries.
> Also, even when the first characters of the column match, it
doesn't do the
> index scan if index data apply. Only uses the index for
INDEXONLY and no
> From what I can guess, it looks like for index/data queries,
> is always assuming that it will return more than 25% of the
entire table in
> result set for each INDEX/DATA query and therefore a
tablespace scan is
> efficient. Again, even here, I don't understand why as the
> over 125 columns and almost 2k of data per row. So I'd think
as long as
> than 50% of the table were returned that an INDEX SCAN would
be more cost
> I've tried OPTIMIZE for ONE ROW with no difference in results
> --- David Nance <[login to unmask email]> wrote:
> > Myron,
> > The caveat on index useage by a like statement is you
have to provide
> > first character(s). In your case you are not providing
> > of the column, so you get non-matching index scans, so
when you are just
> > looking for that column, you scan every row in the index
to find your
> > When you start asking for columns that are not in the
index, DB2 looks at
> > as being better to just scan every row in the tablespace.
It seems to me
> > you would be better off, actually breaking the name out
of this column
> > putting it into its own column, then you could get the
> > that you are wanting.
> > Dave Nance
> > First Health Services, Corp.
> > (804)527-6841
> > >>> [login to unmask email] 12/20/02 10:44:26 AM
> > According to the DB2 manual for V7, Like clauses are
stage 1 and will
> use an
> > index if available. But what I've found is that it only
uses an index if
> > an index only request. Such as the following:
> > select name from tablea
> > where name like '%abc%';
> > If you do something like this, you get a tablespace
> > select name, address, state from tablea
> > where name like '%abc%';
> > and tablea is defined as
> > id int,
> > name varchar 100
> > address char 30
> > state char 2
> > var1 char 20
> > var2 char 10
> > var3 int;
> > Index 1 unique id
> > index 2 name
> > The name value is not the first few characters of the
name field but is
> > within
> > the field, usually in the first thirty or so but not
> > I have a table that looks more or less like this with
about 750,000 rows
> > I've been testing this type of query on and everytime I
> > are not in the index so that I'd get an index/data
retrieval I get
> > scans. I've tried this with complete detailed stats on
> > Does anybody have any ideas why DB2 doesn't perform index
> there is
> > a
> > index/data retrievals versus just index only
> > I've got a PMR open with IBM but haven't heard much from
> > __________________________________________________
> > Do you Yahoo!?
> > Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> > http://mailplus.yahoo.com
> > DB2-L webpage at http://listserv.ylassoc.com.
owners of the list can
> > This message, including any attachments, is intended
solely for the use
> > of the named recipient(s) and may contain confidential
> > privileged information. Any unauthorized review, use,
> > distribution of this communication(s) is expressly
> > If you are not the intended recipient, please contact the
> > reply e-mail and destroy any and all copies of the
> > Thank you.
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> the DB2-L webpage at http://listserv.ylassoc.com.
owners of the list
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.