76 lines
4.8 KiB
HTML
76 lines
4.8 KiB
HTML
|
<?xml version="1.0" encoding="UTF-8"?>
|
||
|
|
<!DOCTYPE html
|
||
|
|
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
||
|
|
<html lang="en-us" xml:lang="en-us">
|
||
|
|
<head>
|
||
|
|
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
|
||
|
|
<meta name="security" content="public" />
|
||
|
|
<meta name="Robots" content="index,follow" />
|
||
|
|
<meta http-equiv="PICS-Label" content='(PICS-1.1 "http://www.icra.org/ratingsv02.html" l gen true r (cz 1 lz 1 nz 1 oz 1 vz 1) "http://www.rsac.org/ratingsv01.html" l gen true r (n 0 s 0 v 0 l 0) "http://www.classify.org/safesurf/" l gen true r (SS~~000 1))' />
|
||
|
|
<meta name="DC.Type" content="reference" />
|
||
|
|
<meta name="DC.Title" content="Avoid the use of like patterns beginning with % or _" />
|
||
|
|
<meta name="abstract" content="The percent sign (%), and the underline (_), when used in the pattern of a LIKE (OPNQRYF %WLDCRD) predicate, specify a character string that is similar to the column value of rows you want to select. They can take advantage of indexes when used to denote characters in the middle or at the end of a character string." />
|
||
|
|
<meta name="description" content="The percent sign (%), and the underline (_), when used in the pattern of a LIKE (OPNQRYF %WLDCRD) predicate, specify a character string that is similar to the column value of rows you want to select. They can take advantage of indexes when used to denote characters in the middle or at the end of a character string." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqeffectindex.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1998, 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="avoidpatterns" />
|
||
|
|
<meta name="DC.Language" content="en-us" />
|
||
|
|
<!-- All rights reserved. Licensed Materials Property of IBM -->
|
||
|
|
<!-- US Government Users Restricted Rights -->
|
||
|
|
<!-- Use, duplication or disclosure restricted by -->
|
||
|
|
<!-- GSA ADP Schedule Contract with IBM Corp. -->
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ibmdita.css" />
|
||
|
|
<link rel="stylesheet" type="text/css" href="./ic.css" />
|
||
|
|
<title>Avoid the use of like patterns beginning with % or _</title>
|
||
|
|
</head>
|
||
|
|
<body id="avoidpatterns"><a name="avoidpatterns"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Avoid the use of like patterns beginning with % or _</h1>
|
||
|
|
<div><p>The percent sign (%), and the underline (_), when used in the pattern
|
||
|
|
of a LIKE (OPNQRYF %WLDCRD) predicate, specify a character string that is
|
||
|
|
similar to the column value of rows you want to select. They can take advantage
|
||
|
|
of indexes when used to denote characters in the middle or at the end of a
|
||
|
|
character string.</p>
|
||
|
|
<div class="section"><div class="p">For example, when using SQL, specify the following: <pre>… <strong>WHERE</strong> LASTNAME <strong>LIKE</strong> 'J%SON%'</pre>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>When using the OPNQRYF command, specify the following:</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><div class="p"> <pre>... QRYSLT('LASTNAME *EQ %WLDCRD(''J*SON*'')')</pre>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>However, when used at the beginning of a character string, they
|
||
|
|
can prevent <span class="keyword">DB2 Universal Database™ for iSeries™</span> from
|
||
|
|
using any indexes that might be defined on the LASTNAME column to limit the
|
||
|
|
number of rows scanned using index scan-key positioning. Index scan-key selection,
|
||
|
|
however, is allowed. For example, in the following queries index scan-key
|
||
|
|
selection can be used, but index scan-key positioning cannot.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><div class="p">In SQL: <pre>… <strong>WHERE</strong> LASTNAME <strong>LIKE</strong> '%SON'</pre>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>In OPNQRYF:</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><div class="p"> <pre>… QRYSLT('LASTNAME *EQ %WLDCRD(''*SON'')')</pre>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Ideally, you should avoid patterns with a % so that you can get
|
||
|
|
the best performance when you perform key processing on the predicate. If
|
||
|
|
possible, you should try to get a partial string to search so that index scan-key
|
||
|
|
positioning can be used. </p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>For example, if you were looking for the name "Smithers", but
|
||
|
|
you only type "S%," this query returns all names starting with "S." You should
|
||
|
|
adjust the query to return all names with "Smi%". By forcing the use of partial
|
||
|
|
strings, you may get better performance in the long term.</p>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzajqeffectindex.htm" title="The following topics provide suggestions that will help you to design code which allows DB2 Universal Database for iSeries to take advantage of available indexes:">Coding for effective indexes</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|