105 lines
6.6 KiB
HTML
105 lines
6.6 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="Join order optimization" />
|
||
<meta name="abstract" content="The join order is fixed if any join logical files are referenced. The join order is also fixed if the OPNQRYF JORDER(*FILE) parameter is specified or the query options file (QAQQINI) FORCE_JOIN_ORDER parameter is *YES." />
|
||
<meta name="description" content="The join order is fixed if any join logical files are referenced. The join order is also fixed if the OPNQRYF JORDER(*FILE) parameter is specified or the query options file (QAQQINI) FORCE_JOIN_ORDER parameter is *YES." />
|
||
<meta name="DC.subject" content="join order, optimization, join secondary dials, costing" />
|
||
<meta name="keywords" content="join order, optimization, join secondary dials, costing" />
|
||
<meta name="DC.Relation" scheme="URI" content="perf24.htm" />
|
||
<meta name="DC.Relation" scheme="URI" content="../cl/opnqryf.htm" />
|
||
<meta name="DC.Relation" scheme="URI" content="../cl/chgqrya.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="rzajqjoinorder" />
|
||
<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>Join order optimization</title>
|
||
</head>
|
||
<body id="rzajqjoinorder"><a name="rzajqjoinorder"><!-- --></a>
|
||
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
<h1 class="topictitle1">Join order optimization</h1>
|
||
<div><p>The join order is fixed if any join logical files are referenced.
|
||
The join order is also fixed if the OPNQRYF JORDER(*FILE) parameter is specified
|
||
or the query options file (QAQQINI) FORCE_JOIN_ORDER parameter is *YES.</p>
|
||
<div class="section"><p>Otherwise, the following join ordering algorithm is used to determine
|
||
the order of the tables: </p>
|
||
</div>
|
||
<div class="section"> <ol><li>Determine an access method for each individual table as candidates for
|
||
the primary dial.</li>
|
||
<li id="rzajqjoinorder__lend2"><a name="rzajqjoinorder__lend2"><!-- --></a>Estimate the number of rows returned for each table based on
|
||
local row selection. <p>If the join query with row ordering or group by processing
|
||
is being processed in one step, then the table with the ordering or grouping
|
||
columns is the primary table.</p>
|
||
</li>
|
||
<li id="rzajqjoinorder__lend4"><a name="rzajqjoinorder__lend4"><!-- --></a>Determine an access method, cost, and expected number of rows
|
||
returned for each join combination of candidate tables as primary and first
|
||
secondary tables. <div class="p">The join order combinations estimated for a four table
|
||
inner join would be: <pre>1-2 2-1 1-3 3-1 1-4 4-1 2-3 3-2 2-4 4-2 3-4 4-3</pre>
|
||
</div>
|
||
</li>
|
||
<li id="rzajqjoinorder__joinit1"><a name="rzajqjoinorder__joinit1"><!-- --></a>Choose the combination with the lowest join cost and number
|
||
of selected rows or both.</li>
|
||
<li id="rzajqjoinorder__lend1"><a name="rzajqjoinorder__lend1"><!-- --></a>Determine the cost, access method, and expected number of rows
|
||
for each remaining table joined to the previous secondary table.</li>
|
||
<li>Select an access method for each table that has the lowest cost for that
|
||
table.</li>
|
||
<li id="rzajqjoinorder__joinite"><a name="rzajqjoinorder__joinite"><!-- --></a>Choose the secondary table with the lowest join cost and
|
||
number of selected rows or both.</li>
|
||
<li>Repeat steps 4 through 7 until the lowest cost join order is determined.</li>
|
||
</ol>
|
||
</div>
|
||
<div class="section"> <div class="note"><span class="notetitle">Note:</span> After dial 32, the optimizer uses a different method to determine
|
||
file join order, which may not be the lowest cost.</div>
|
||
</div>
|
||
<div class="section"><p>When a query contains a left or right outer join or a right exception
|
||
join, the join order is not fixed. However, all from-columns of the ON clause
|
||
must occur from dials previous to the left or right outer or exception join.
|
||
For example:</p>
|
||
</div>
|
||
<div class="section"><div class="p"> <pre><strong>FROM</strong> A <strong>INNER JOIN</strong> B <strong>ON</strong> A.C1=B.C1
|
||
<strong>LEFT OUTER JOIN</strong> C <strong>ON</strong> B. C2=C.C2</pre>
|
||
</div>
|
||
</div>
|
||
<div class="section"><p>The allowable join order combinations for this query would be:</p>
|
||
</div>
|
||
<div class="section"><p>1–2–3, 2–1–3, or 2–3–1</p>
|
||
</div>
|
||
<div class="section"><p>Right outer or right exception joins are implemented as left outer
|
||
and left exception, with files flipped. For example:</p>
|
||
</div>
|
||
<div class="section"><div class="p"> <pre><strong>FROM</strong> A <strong>RIGHT OUTER JOIN</strong> B <strong>ON</strong> A.C1=B.C1</pre>
|
||
is
|
||
implemented as B LEFT OUTER JOIN A ON B.C1=A.C1. The only allowed join order
|
||
is 2–1.</div>
|
||
</div>
|
||
<div class="section"><p>When a join logical file is referenced or the join order is forced
|
||
to the specified table order, the query optimizer loops through all of the
|
||
dials in the order specified, and determines the lowest cost access methods.
|
||
</p>
|
||
</div>
|
||
</div>
|
||
<div>
|
||
<div class="familylinks">
|
||
<div class="parentlink"><strong>Parent topic:</strong> <a href="perf24.htm" title="A join operation is a complex function that requires special attention in order to achieve good performance. This section describes how DB2 Universal Database for iSeries implements join queries and how optimization choices are made by the query optimizer. It also describes design tips and techniques which help avoid or solve performance problems.">Join optimization</a></div>
|
||
</div>
|
||
<div class="relinfo"><strong>Related information</strong><br />
|
||
<div><a href="../cl/opnqryf.htm">Open Query File (OPNQRYF) command</a></div>
|
||
<div><a href="../cl/chgqrya.htm">Change Query Attributes (CHGQRYA) command</a></div>
|
||
</div>
|
||
</div>
|
||
</body>
|
||
</html> |