141 lines
8.7 KiB
HTML
141 lines
8.7 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="Sorted list probe" />
|
||
|
|
<meta name="abstract" content="A sorted list probe operation is used to retrieve rows from a temporary sorted list based upon a probe lookup operation." />
|
||
|
|
<meta name="description" content="A sorted list probe operation is used to retrieve rows from a temporary sorted list based upon a probe lookup operation." />
|
||
|
|
<meta name="DC.subject" content="definitions, sorted list probe access method, temporary sorted list, probe access method, sorted list probe, access method" />
|
||
|
|
<meta name="keywords" content="definitions, sorted list probe access method, temporary sorted list, probe access method, sorted list probe, access method" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqtempslist.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="c23nl.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="rzajqslistprobe" />
|
||
|
|
<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>Sorted list probe</title>
|
||
|
|
</head>
|
||
|
|
<body id="rzajqslistprobe"><a name="rzajqslistprobe"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Sorted list probe</h1>
|
||
|
|
<div><p>A sorted list probe operation is used to retrieve rows from a temporary
|
||
|
|
sorted list based upon a probe lookup operation.</p>
|
||
|
|
<div class="section"><p>The optimizer initially identifies the keys of the temporary sorted
|
||
|
|
list from the join criteria specified in the query. This is done so that
|
||
|
|
when the sorted list probe is performed, the values used to probe into the
|
||
|
|
temporary sorted list will be extracted from the join-from criteria specified
|
||
|
|
in the selection. Those values will be used to position within the sorted
|
||
|
|
list in order to determine if any rows have a matching value. All of the matching
|
||
|
|
join rows are then returned to be further processed by the query.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section">
|
||
|
|
<div class="tablenoborder"><table cellpadding="4" cellspacing="0" summary="" frame="border" border="1" rules="all"><caption>Table 1. Sorted list probe attributes</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">Sorted 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 sorted list is quickly probed based upon
|
||
|
|
the join criteria. </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>Provides very quick access to the selected rows that match probe criteria</li>
|
||
|
|
<li>Reduces the random I/O to the table generally associated with longer running
|
||
|
|
queries that otherwise use an index to collate the data </li>
|
||
|
|
<li>Selection can be performed before generating the sorted list
|
||
|
|
to subset the number of 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 ">Generally used to process non-equal join criteria. Can
|
||
|
|
perform poorly when the entire sorted list does not stay resident in memory
|
||
|
|
as it is being populated and processed.</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 data is required to be collated based upon a column or columns
|
||
|
|
for join processing</li>
|
||
|
|
<li>The join criteria was specified using a non-equals operator</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>SELECT * FROM Employee XXX, Department YYY
|
||
|
|
WHERE XXX.WorkDept > YYY.DeptNbr
|
||
|
|
OPTIMIZE FOR ALL ROWS</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 sorted list probe
|
||
|
|
can be indicated through the messages. The messages in this example illustrate
|
||
|
|
how the SQL Query Engine will indicate a sorted list probe was used. <ul><li>Optimizer Debug: <pre>CPI4327 -- File EMPLOYEE processed in join position 1.
|
||
|
|
CPI4327 -- File DEPARTMENT processed in join
|
||
|
|
position 2. </pre>
|
||
|
|
</li>
|
||
|
|
<li>PRTSQLINF: <pre>SQL4007 -- Query implementation for join
|
||
|
|
position 1 table 1.
|
||
|
|
SQL4010 -- Table scan access for table 1.
|
||
|
|
SQL4007 -- Query implementation for join
|
||
|
|
position 2 table 2.
|
||
|
|
SQL4010 -- Table scan access for table 2. </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 ">Sorted List Probe, Preload <p>Sorted List Probe Distinct</p>
|
||
|
|
<p>Sorted
|
||
|
|
List Probe Distinct, Preload</p>
|
||
|
|
</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="rzajq520.gif" alt="Sorted list probe icon" /><br /></td>
|
||
|
|
</tr>
|
||
|
|
</tbody>
|
||
|
|
</table>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>The sorted list probe access method is generally considered when
|
||
|
|
determining the implementation for a secondary table of a join. The sorted
|
||
|
|
list is created with the key columns that match the non-equal join criteria
|
||
|
|
for the underlying table. The sorted list probe allows the optimizer to choose
|
||
|
|
the most efficient implementation to select the rows from the underlying table
|
||
|
|
without regard for any join criteria. This single pass through the underlying
|
||
|
|
table can now choose to perform a Table Scan or use an existing index to select
|
||
|
|
the rows needed for the sorted list population.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Since sorted lists are constructed so that the majority of the
|
||
|
|
temporary object will remain resident within main memory, the I/O associated
|
||
|
|
with a sorted list is minimal. Additionally, if the sorted list was populated
|
||
|
|
with all necessary columns from the table, no additional Table Probe will
|
||
|
|
be required in order to finish processing this table, once again causing further
|
||
|
|
I/O savings.</p>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzajqtempslist.htm" title="The temporary sorted list is a temporary object that allows the optimizer to sequence rows based upon a column or set of columns. The sorted list can be either scanned or probed by the optimizer to satisfy different operations of the query.">Temporary sorted list</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relconcepts"><strong>Related concepts</strong><br />
|
||
|
|
<div><a href="c23nl.htm" title="DB2 Universal Database for iSeries provides a nested loop join method. For this method, the processing of the tables in the join are ordered. This order is called the join order. The first table in the final join order is called the primary table. The other tables are called secondary tables. Each join table position is called a dial.">Nested loop join implementation</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|