75 lines
5.1 KiB
HTML
75 lines
5.1 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="topic" />
|
||
|
|
<meta name="DC.Title" content="Predicates generated through transitive closure" />
|
||
|
|
<meta name="abstract" content="For join queries, the query optimizer may do some special processing to generate additional selection. When the set of predicates that belong to a query logically infer extra predicates, the query optimizer generates additional predicates. The purpose is to provide more information during join optimization." />
|
||
|
|
<meta name="description" content="For join queries, the query optimizer may do some special processing to generate additional selection. When the set of predicates that belong to a query logically infer extra predicates, the query optimizer generates additional predicates. The purpose is to provide more information during join optimization." />
|
||
|
|
<meta name="DC.subject" content="predicate, transitive closure, definitions, isolatable" />
|
||
|
|
<meta name="keywords" content="predicate, transitive closure, definitions, isolatable" />
|
||
|
|
<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="rzajqpredtrans" />
|
||
|
|
<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>Predicates generated through transitive closure</title>
|
||
|
|
</head>
|
||
|
|
<body id="rzajqpredtrans"><a name="rzajqpredtrans"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Predicates generated through transitive closure</h1>
|
||
|
|
<div><p>For join queries, the query optimizer may do some special processing
|
||
|
|
to generate additional selection. When the set of predicates that belong to
|
||
|
|
a query logically infer extra predicates, the query optimizer generates additional
|
||
|
|
predicates. The purpose is to provide more information during join optimization.</p>
|
||
|
|
<div class="section"><p>See the following examples:</p>
|
||
|
|
<pre><strong>SELECT</strong> * <strong>FROM</strong> EMPLOYEE, EMP_ACT
|
||
|
|
<strong>WHERE</strong> EMPLOYEE.EMPNO = EMP_ACT.EMPNO
|
||
|
|
<strong>AND</strong> EMPLOYEE.EMPNO = '000010'</pre>
|
||
|
|
<p>The optimizer will
|
||
|
|
modify the query to be:</p>
|
||
|
|
<pre><strong>SELECT</strong> * <strong>FROM</strong> EMPLOYEE, EMP_ACT
|
||
|
|
<strong>WHERE</strong> EMPLOYEE.EMPNO = EMP_ACT.EMPNO
|
||
|
|
<strong>AND</strong> EMPLOYEE.EMPNO = '000010'
|
||
|
|
<strong>AND</strong> EMP_ACT.EMPNO = '000010' </pre>
|
||
|
|
<p>The following rules
|
||
|
|
determine which predicates are added to other join dials:</p>
|
||
|
|
<img src="./delta.gif" alt="Start of change" /><ul><li>The dials affected must have join operators of equal.</li>
|
||
|
|
<li>The predicate is <strong>isolatable</strong>, which means that a false condition
|
||
|
|
from this predicate omits the row.</li>
|
||
|
|
<li>One operand of the predicate is an equal join column and the other is
|
||
|
|
a constant or host variable.</li>
|
||
|
|
<li>The predicate operator is not LIKE (OPNQRYF %WLDCRD, or *CT).</li>
|
||
|
|
<li>The predicate is not connected to other predicates by OR.</li>
|
||
|
|
</ul><img src="./deltaend.gif" alt="End of change" />
|
||
|
|
<p>The query optimizer generates a new predicate, whether a predicate
|
||
|
|
already exists in the WHERE clause (OPNQRYF QRYSLT parameter).</p>
|
||
|
|
<p>Some
|
||
|
|
predicates are redundant. This occurs when a previous evaluation of other
|
||
|
|
predicates in the query already determines the result that predicate provides.
|
||
|
|
Redundant predicates can be specified by you or generated by the query optimizer
|
||
|
|
during predicate manipulation. Redundant predicates with predicate operators
|
||
|
|
of =, >, >=, <, <=, or BETWEEN (OPNQRYF *EQ, *GT, *GE, *LT, *LE,
|
||
|
|
or %RANGE) are merged into a single predicate to reflect the most selective
|
||
|
|
range.</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>
|