81 lines
5.2 KiB
HTML
81 lines
5.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="Access plan validation" />
|
||
|
|
<meta name="abstract" content="An access plan is a control structure that describes the actions necessary to satisfy each query request. It contains information about the data and how to extract it. For any query, whenever optimization occurs, the query optimizer develops an optimized plan of how to access the requested data." />
|
||
|
|
<meta name="description" content="An access plan is a control structure that describes the actions necessary to satisfy each query request. It contains information about the data and how to extract it. For any query, whenever optimization occurs, the query optimizer develops an optimized plan of how to access the requested data." />
|
||
|
|
<meta name="DC.subject" content="access plan, validation, definitions, miniplan, Query Definition Template (QDT), QDT" />
|
||
|
|
<meta name="keywords" content="access plan, validation, definitions, miniplan, Query Definition Template (QDT), QDT" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="per0001.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="apvalid" />
|
||
|
|
<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>Access plan validation</title>
|
||
|
|
</head>
|
||
|
|
<body id="apvalid"><a name="apvalid"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Access plan validation</h1>
|
||
|
|
<div><p>An access plan is a control structure that describes the actions
|
||
|
|
necessary to satisfy each query request. It contains information about the
|
||
|
|
data and how to extract it. For any query, whenever optimization occurs, the
|
||
|
|
query optimizer develops an optimized plan of how to access the requested
|
||
|
|
data. </p>
|
||
|
|
<div class="section"><p>To improve performance, an access plan is saved (see exceptions
|
||
|
|
below) once it is built so as to be available for potentially future runs
|
||
|
|
of the query. However, the optimizer has dynamic replan capability. This means
|
||
|
|
that even if previously built (and saved) plan is found, the optimizer may
|
||
|
|
rebuild it if it determines that a more optimal plan is possible. This allows
|
||
|
|
for maximum flexibility while still taking advantage of saved plans.</p>
|
||
|
|
<ul><li>For dynamic SQL, an access plan is created at prepare or open time. However,
|
||
|
|
optimization uses the host variable values to determine an optimal plan. Therefore,
|
||
|
|
a plan built at prepare time may be rebuilt the first time the query is opened
|
||
|
|
(when the host variable values are present).</li>
|
||
|
|
<li>For an <span class="keyword">iSeries™</span> program
|
||
|
|
that contains static embedded SQL, an access plan is initially created at
|
||
|
|
compile time. Again, since optimization uses the host variable values to determine
|
||
|
|
an optimal plan, the compile time plan may be rebuilt the first time the query
|
||
|
|
is opened.</li>
|
||
|
|
<li>For Open Query File (OPNQRYF), an access plan is created
|
||
|
|
but is not saved. A new access plan is created each time the OPNQRYF command
|
||
|
|
is processed.</li>
|
||
|
|
<li>For Query/400, an access plan is saved as part of the query definition
|
||
|
|
object.</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>In all cases above where a plan is saved above, including static
|
||
|
|
SQL, dynamic replan can still apply as the queries are run over time. </p>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>The access plan is validated when the query is opened. Validation
|
||
|
|
includes the following: </p>
|
||
|
|
<ul><li>Verifying that the same tables are referenced in the query as in the access
|
||
|
|
plan. For example, the tables were not deleted and recreated or that the
|
||
|
|
tables resolved by using *LIBL have not changed.</li>
|
||
|
|
<li>Verifying that the indexes used to implement the query, still exist.</li>
|
||
|
|
<li>Verifying that the table size or predicate selectivity has not changed
|
||
|
|
significantly.</li>
|
||
|
|
<li>Verifying that QAQQINI options have not changed.</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="per0001.htm" title="This overview of the query optimizer provides guidelines for designing queries that will perform and will use server resources more efficiently.">Processing queries: Overview</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|