120 lines
7.6 KiB
HTML
120 lines
7.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 optimization algorithm" />
|
||
|
|
<meta name="abstract" content="The query optimizer must determine the join columns, join operators, local row selection, dial implementation, and dial ordering for a join query." />
|
||
|
|
<meta name="description" content="The query optimizer must determine the join columns, join operators, local row selection, dial implementation, and dial ordering for a join query." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="perf24.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="rzajqjoinoptalg" />
|
||
|
|
<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 optimization algorithm</title>
|
||
|
|
</head>
|
||
|
|
<body id="rzajqjoinoptalg"><a name="rzajqjoinoptalg"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Join optimization algorithm</h1>
|
||
|
|
<div><p>The query optimizer must determine the join columns, join operators,
|
||
|
|
local row selection, dial implementation, and dial ordering for a join query.</p>
|
||
|
|
<div class="section"><p>The join columns and join operators depend on the following situations:</p>
|
||
|
|
<ul><li>Join column specifications of the query</li>
|
||
|
|
<li>Join order</li>
|
||
|
|
<li>Interaction of join columns with other row selection</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Join specifications which are not implemented for the dial are
|
||
|
|
either deferred until they can be processed in a later dial or, if an inner
|
||
|
|
join was being performed for this dial, processed as row selection.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><div class="p">For a given dial, the only join specifications which are usable
|
||
|
|
as join columns for that dial are those being joined to a <em>previous</em> dial.
|
||
|
|
For example, for the second dial the only join specifications that can be
|
||
|
|
used to satisfy the join condition are join specifications which reference
|
||
|
|
columns in the primary dial. Likewise, the third dial can only use join specifications
|
||
|
|
which reference columns in the primary and the second dials and so on. Join
|
||
|
|
specifications which reference later dials are deferred until the referenced
|
||
|
|
dial is processed. <div class="note"><span class="notetitle">Note:</span> For OPNQRYF, only one type of join operator is allowed
|
||
|
|
for either a left outer or an exception join. That is, the join operator for
|
||
|
|
all join conditions must be the same.</div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>When looking for an existing index to access a secondary dial,
|
||
|
|
the query optimizer looks at the left-most key columns of the index. For a
|
||
|
|
given dial and index, the join specifications which use the left-most key
|
||
|
|
columns can be used. For example:</p>
|
||
|
|
<pre> <strong>DECLARE</strong> BROWSE2 <strong>CURSOR FOR</strong>
|
||
|
|
<strong>SELECT</strong> * <strong>FROM</strong> EMPLOYEE, EMP_ACT
|
||
|
|
<strong>WHERE</strong> EMPLOYEE.EMPNO = EMP_ACT.EMPNO
|
||
|
|
<strong>AND</strong> EMPLOYEE.HIREDATE = EMP_ACT.EMSTDATE
|
||
|
|
<strong>OPTIMIZE FOR</strong> 99999 <strong>ROWS</strong></pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>For the index over EMP_ACT with key columns EMPNO, PROJNO, and
|
||
|
|
EMSTDATE, the join operation is performed only on column EMPNO. After the
|
||
|
|
join is performed, index scan-key selection is done using column EMSTDATE.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>The query optimizer also uses local row selection when choosing
|
||
|
|
the best use of the index for the secondary dial. If the previous example
|
||
|
|
had been expressed with a local predicate as:</p>
|
||
|
|
<pre> <strong>DECLARE</strong> BROWSE2 <strong>CURSOR FOR</strong>
|
||
|
|
<strong>SELECT</strong> * <strong>FROM</strong> EMPLOYEE, EMP_ACT
|
||
|
|
<strong>WHERE</strong> EMPLOYEE.EMPNO = EMP_ACT.EMPNO
|
||
|
|
<strong>AND</strong> EMPLOYEE.HIREDATE = EMP_ACT.EMSTDATE
|
||
|
|
<strong>AND</strong> EMP_ACT.PROJNO = '123456'
|
||
|
|
<strong>OPTIMIZE FOR</strong> 99999 <strong>ROWS</strong></pre>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>The index with key columns EMPNO, PROJNO, and EMSTDATE are fully
|
||
|
|
utilized by combining join and selection into one operation against all three
|
||
|
|
key columns.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>When creating a temporary index, the left-most key columns are
|
||
|
|
the usable join columns in that dial position. All local row selection for
|
||
|
|
that dial is processed when selecting entries for inclusion into the temporary
|
||
|
|
index. A temporary index is similar to the index created for a select/omit
|
||
|
|
keyed logical file. The temporary index for the previous example has key columns
|
||
|
|
of EMPNO and EMSTDATE.</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Since the query optimizer attempts a combination of join and local
|
||
|
|
row selection when determining access path usage, it is possible to achieve
|
||
|
|
almost all of the same advantages of a temporary index by use of an existing
|
||
|
|
index. In the above example, using either implementation, an existing index
|
||
|
|
may be used or a temporary index may be created. A temporary index is built
|
||
|
|
with the local row selection on PROJNO applied during the index's creation;
|
||
|
|
the temporary index has key columns of EMPNO and EMSTDATE (to
|
||
|
|
match the join selection). If, instead, an existing index was used with key
|
||
|
|
columns of EMPNO, PROJNO, EMSTDATE (or PROJNO, EMP_ACT, EMSTDATE or EMSTDATE,
|
||
|
|
PROJNO, EMP_ACT or ...) the local row selection can be applied <strong>at the same
|
||
|
|
time</strong> as the join selection (rather than before the join selection, as
|
||
|
|
happens when the temporary index is created, or after the join selection,
|
||
|
|
as happens when only the first key column of the index matches the join column).</p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>The implementation using the existing index is more likely to
|
||
|
|
provide faster performance because join and selection processing are combined
|
||
|
|
without the overhead of building a temporary index. However, the use of the
|
||
|
|
existing index may have just slightly slower I/O processing than the temporary
|
||
|
|
index because the local selection is run many times rather than once. In general,
|
||
|
|
it is a good idea to have existing indexes available with key columns for
|
||
|
|
the combination of join columns and columns using equal selection as the left-most
|
||
|
|
keys.</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>
|
||
|
|
</body>
|
||
|
|
</html>
|