146 lines
9.2 KiB
HTML
146 lines
9.2 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="Row number list probe" />
|
|||
|
|
<meta name="abstract" content="A row number list probe operation is used to test row numbers generated by a separate operation against the selected rows of a temporary row number list. The row numbers can be generated by any operation that constructs a row number for a table. That row number is then used to probe into a temporary row number list to determine if that row number matches the selection used to generate the temporary row number list." />
|
|||
|
|
<meta name="description" content="A row number list probe operation is used to test row numbers generated by a separate operation against the selected rows of a temporary row number list. The row numbers can be generated by any operation that constructs a row number for a table. That row number is then used to probe into a temporary row number list to determine if that row number matches the selection used to generate the temporary row number list." />
|
|||
|
|
<meta name="DC.subject" content="definitions, row number list probe access method, temporary row number list, probe access method, row number list probe, access method" />
|
|||
|
|
<meta name="keywords" content="definitions, row number list probe access method, temporary row number list, probe access method, row number list probe, access method" />
|
|||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqtemprnl.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="rzajqrnumlistprobe" />
|
|||
|
|
<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>Row number list probe</title>
|
|||
|
|
</head>
|
|||
|
|
<body id="rzajqrnumlistprobe"><a name="rzajqrnumlistprobe"><!-- --></a>
|
|||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
|||
|
|
<h1 class="topictitle1">Row number list probe</h1>
|
|||
|
|
<div><p>A row number list probe operation is used to test row numbers generated
|
|||
|
|
by a separate operation against the selected rows of a temporary row number
|
|||
|
|
list. The row numbers can be generated by any operation that constructs a
|
|||
|
|
row number for a table. That row number is then used to probe into a temporary
|
|||
|
|
row number list to determine if that row number matches the selection used
|
|||
|
|
to generate the temporary row number list.</p>
|
|||
|
|
<div class="section"><p>The use of a row number list probe operation allows the optimizer
|
|||
|
|
to generate a plan that can take advantage of any sequencing provided by an
|
|||
|
|
index, but still use the row number list to perform additional selection before
|
|||
|
|
any Table probe operations.</p>
|
|||
|
|
</div>
|
|||
|
|
<div class="section"><p>A row number list probe is identical to a bitmap probe operation.
|
|||
|
|
The only difference between the two operations is that a row number list probe
|
|||
|
|
is performed over a list of row addresses while the bitmap probe is performed
|
|||
|
|
over a bitmap that represents the row addresses.</p>
|
|||
|
|
</div>
|
|||
|
|
<div class="section">
|
|||
|
|
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" frame="border" border="1" rules="all"><caption>Table 1. Row number list probe</caption><thead align="left"><tr><th valign="top" width="30.96446700507614%" id="d0e44">Data access method</th>
|
|||
|
|
<th valign="top" width="69.03553299492386%" id="d0e46">Row number list probe</th>
|
|||
|
|
</tr>
|
|||
|
|
</thead>
|
|||
|
|
<tbody><tr><td valign="top" width="30.96446700507614%" headers="d0e44 "><strong>Description</strong></td>
|
|||
|
|
<td valign="top" width="69.03553299492386%" headers="d0e46 ">The temporary row number list is quickly probed based
|
|||
|
|
upon the row number generated by a separate operation.</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr><td valign="top" width="30.96446700507614%" headers="d0e44 "><strong>Advantages</strong></td>
|
|||
|
|
<td valign="top" width="69.03553299492386%" headers="d0e46 "><ul><li>The temporary row number list only contains a rows' address, no data,
|
|||
|
|
so the temporary can be efficiently probed within memory</li>
|
|||
|
|
<li>The row numbers represented within the row number list are sorted to provide
|
|||
|
|
efficient lookup processing to test the underlying table</li>
|
|||
|
|
<li>Selection is performed as the row number list is generated to subset the
|
|||
|
|
number of selected rows in the temporary object</li>
|
|||
|
|
</ul>
|
|||
|
|
</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr><td valign="top" width="30.96446700507614%" headers="d0e44 "><strong>Considerations</strong></td>
|
|||
|
|
<td valign="top" width="69.03553299492386%" headers="d0e46 ">Since the row number list only contains the addresses
|
|||
|
|
of the selected rows in the table, a separate Table Probe operation must be
|
|||
|
|
performed in order to fetch the table rows </td>
|
|||
|
|
</tr>
|
|||
|
|
<tr><td valign="top" width="30.96446700507614%" headers="d0e44 "><strong>Likely to be used</strong></td>
|
|||
|
|
<td valign="top" width="69.03553299492386%" headers="d0e46 "><ul><li>When the use of temporary results is allowed by the query environmental
|
|||
|
|
parameter (ALWCPYDTA) </li>
|
|||
|
|
<li>When the cost of creating and probing the row number list is justified
|
|||
|
|
by reducing the number of Table Probe operations that must be performed</li>
|
|||
|
|
<li>When multiple indexes over the same table need to be combined in order
|
|||
|
|
to minimize the number of selected rows</li>
|
|||
|
|
</ul>
|
|||
|
|
</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr><td valign="top" width="30.96446700507614%" headers="d0e44 "><strong>Example SQL statement</strong></td>
|
|||
|
|
<td valign="top" width="69.03553299492386%" headers="d0e46 "><pre>CREATE INDEX X1 ON Employee (WorkDept)
|
|||
|
|
CREATE ENCODED VECTOR INDEX EVI2 ON
|
|||
|
|
Employee (Salary)
|
|||
|
|
CREATE ENCODED VECTOR INDEX EVI3 ON
|
|||
|
|
Employee (Job)
|
|||
|
|
|
|||
|
|
SELECT * FROM Employee
|
|||
|
|
WHERE WorkDept = 'E01' AND Job = 'CLERK'
|
|||
|
|
AND Salary = 5000
|
|||
|
|
ORDER BY WorkDept</pre>
|
|||
|
|
</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr><td valign="top" width="30.96446700507614%" headers="d0e44 "><strong>Messages indicating use</strong></td>
|
|||
|
|
<td valign="top" width="69.03553299492386%" headers="d0e46 ">There are multiple ways in which a row number list probe
|
|||
|
|
can be indicated through the messages. The messages in this example illustrate
|
|||
|
|
how the SQL Query Engine will indicate a row number list probe was used. <ul><li>Optimizer Debug: <pre>CPI4328 -- Access path of file X1 was used by query.
|
|||
|
|
CPI4338 -– 2 Access path(s) used for bitmap
|
|||
|
|
processing of file EMPLOYEE. </pre>
|
|||
|
|
</li>
|
|||
|
|
<li>PRTSQLINF: <pre>SQL4008 -- Index X1 used for table 1.
|
|||
|
|
SQL4011 -- Index scan-key row positioning
|
|||
|
|
used on table 1.
|
|||
|
|
SQL4032 -- Index EVI2 used for bitmap
|
|||
|
|
processing of table 1.
|
|||
|
|
SQL4032 -- Index EVI3 used for bitmap
|
|||
|
|
processing of table 1. </pre>
|
|||
|
|
</li>
|
|||
|
|
</ul>
|
|||
|
|
</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr><td valign="top" width="30.96446700507614%" headers="d0e44 "><strong>SMP parallel enabled</strong></td>
|
|||
|
|
<td valign="top" width="69.03553299492386%" headers="d0e46 ">Yes</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr><td valign="top" width="30.96446700507614%" headers="d0e44 "><strong>Also referred to as</strong></td>
|
|||
|
|
<td valign="top" width="69.03553299492386%" headers="d0e46 ">Row Number List Probe, Preload</td>
|
|||
|
|
</tr>
|
|||
|
|
<tr><td valign="top" width="30.96446700507614%" headers="d0e44 "><strong>Visual Explain icon</strong></td>
|
|||
|
|
<td valign="top" width="69.03553299492386%" headers="d0e46 "><br /><img src="rzajq525.gif" alt="Row number list probe icon" /><br /></td>
|
|||
|
|
</tr>
|
|||
|
|
</tbody>
|
|||
|
|
</table>
|
|||
|
|
</div>
|
|||
|
|
</div>
|
|||
|
|
<div class="section"><p>Using the example above, the optimizer created a temporary row
|
|||
|
|
number list for each of the encoded vector indexes. Additionally, an index
|
|||
|
|
probe operation was performed against the radix index X1 to satisfy the ordering
|
|||
|
|
requirement. Since the ORDER BY clause requires that the resulting rows be
|
|||
|
|
sequenced by the WorkDept column, the temporary row number list can no longer
|
|||
|
|
be scanned to process the selected rows. However, the temporary row number
|
|||
|
|
list can be probed using a row address extracted from the index X1 used to
|
|||
|
|
satisfy the ordering. By probing the temporary row number list with the row
|
|||
|
|
address extracted from index probe operation, the sequencing of the keys in
|
|||
|
|
the index X1 is preserved and the row can still be tested against
|
|||
|
|
the selected rows within the row number list.</p>
|
|||
|
|
</div>
|
|||
|
|
</div>
|
|||
|
|
<div>
|
|||
|
|
<div class="familylinks">
|
|||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzajqtemprnl.htm" title="The temporary row number list is a temporary object that allows the optimizer to sequence rows based upon their row address (their row number). The row number list can be either scanned or probed by the optimizer to satisfy different operations of the query.">Temporary row number list</a></div>
|
|||
|
|
</div>
|
|||
|
|
</div>
|
|||
|
|
</body>
|
|||
|
|
</html>
|