I tried to find the right pieces of doc to 'clearly' proove or disproove
either hypothesis but in vain...
Anybody remembers if the old way of
col-x >= :hv1-low-values and col-x<=hv2-low-values
is better than like predicates?
Seems that COL LIKE host variable is now indexable stage 1 if special chars
are not used as the first char.
Anybody can try a
w like hv0-something and x like hv1-low-values and y like hv2-low-values to
see if it makes a difference?
That would make all the predicates indexable-stage-1.
Here is a dump of some links i gathered while investigating the current
thread.
(for those interested)
http://publib.boulder.ibm.com/cgi-bi...STHIT#FIRSTHIT
2.21.6 LIKE predicate
....SNIP...
| When the pattern specified in a LIKE predicate is a parmeter marker
| and a fixed-length character host variable is used to replace the
| parameter marker, specify a value for the host variable that is the
| correct length. If you do not specify the correct length, the select
| does not return the intended results. For example, if the host
| variable is defined as CHAR(10) and the value WYSE% is assigned to
| that host variable, the host variable is padded with blanks on
| assignment. The pattern used is 'WYSE%,' which requests DB2 to search
| for all values that start with WYSE and end with five blank spaces. If
| you intended to search for only the values that start with 'WYSE,' you
| should assign the value 'WYSE%%%%%%' to the host variable.
http://www.gabrielledb2.com/pp.pdf
Around page 32-33
EXPLAIN of LIKE
EXPLAIN shows the use of a matching index scan when a CHAR column is
compared to a host
SELECT PN, PNAME
FROM P
WHERE PNAME LIKE :PNAME;
Value checked at run time
< If value begins with "%" or " ",
- Scan leaf pages if cluster ratio >= 80 %
- Tablespace scan if cluster ratio < 80 %
< Character frequency considered when using reoptimization as discussed in
HV section
http://publib.boulder.ibm.com/cgi-bi...RCHINDEX=INDEX
Stage 1, 2, 3 predicates related links :
Ranked Search Results for Book: dsnaph13 "DB2 UDB for OS/390 and z/OS V7
Application Programming and SQL Guide"
21 topics have matches for: stage 2
http://publib.boulder.ibm.com/cgi-bi...STHIT#FIRSTHIT
6.3.3.2 Summary of predicate processing
6.3.3.4.1 Default filter factors for simple predicates
http://publib.boulder.ibm.com/cgi-bi...165437#HDRD4I9
Col LIKE literal 1/10 (litteral, not host var...)
6.3.3.4.3 Interpolation formulas
http://publib.boulder.ibm.com/cgi-bi...65437#HDRD4I11
For LIKE or BETWEEN:
(High literal value - Low literal value) / (Total Entries)
....snip...
Interpolation for LIKE: DB2 treats a LIKE predicate as a type of BETWEEN
predicate. Two values that bound the range qualified by the predicate are
generated from the literal string in the predicate. Only the leading
characters found before the escape character ('%' or '_') are used to
generate the bounds. So if the escape character is the first character of
the string, the filter factor is estimated as 1, and the predicate is
estimated to reject no rows.
....snip...
etc. (other related stuff)
Other related links
'Programmers Only' column by Bonnie Baker
Predicate Evaluation: Part I, II and III
http://www.db2mag.com/db_area/archiv...grammers.shtml http://www.db2mag.com/db_area/archiv...grammers.shtml http://www.db2mag.com/db_area/archiv...grammers.shtml http://www.db2mag.com/showArticle.jh...cleID=12803232
PM