113 lines
6.3 KiB
HTML
113 lines
6.3 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="Cost estimation" />
|
||
|
|
<meta name="abstract" content="At runtime, the Optimizer chooses an optimal access method for the query by calculating an implementation cost based on the current state of the database." />
|
||
|
|
<meta name="description" content="At runtime, the Optimizer chooses an optimal access method for the query by calculating an implementation cost based on the current state of the database." />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzaikoptimizer.htm" />
|
||
|
|
<meta name="copyright" content="(C) Copyright IBM Corporation 1999, 2006" />
|
||
|
|
<meta name="DC.Rights.Owner" content="(C) Copyright IBM Corporation 1999, 2006" />
|
||
|
|
<meta name="DC.Format" content="XHTML" />
|
||
|
|
<meta name="DC.Identifier" content="costest" />
|
||
|
|
<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>Cost estimation</title>
|
||
|
|
</head>
|
||
|
|
<body id="costest"><a name="costest"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Cost estimation</h1>
|
||
|
|
<div><p>At runtime, the Optimizer chooses an optimal access method for
|
||
|
|
the query by calculating an implementation cost based on the current state
|
||
|
|
of the database.</p>
|
||
|
|
<div class="section"><p>The Optimizer models the access cost of each of the following:
|
||
|
|
</p>
|
||
|
|
<ul><li>Reading rows directly from the table (dataspace scan processing)</li>
|
||
|
|
<li>Reading rows through an access path (using either key selection or key
|
||
|
|
positioning)</li>
|
||
|
|
<li>Creating an access path directly from the dataspace</li>
|
||
|
|
<li>Creating an access path from an existing access path (index-from-index)</li>
|
||
|
|
<li>Using the query sort routine (if conditions are satisfied)</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>The cost of a particular method is the sum of:</p>
|
||
|
|
<ul><li>The start-up cost</li>
|
||
|
|
<li>The cost associated with the given optimization mode. The OPTIMIZE FOR
|
||
|
|
n ROWS clause indicates to the query Optimizer the optimization goal to be
|
||
|
|
achieved. The Optimizer can optimize <span class="keyword">SQL</span> queries
|
||
|
|
with one of two goals: <ol><li>Minimize the time required to retrieve the first buffer of rows from the
|
||
|
|
table. This goal biases the optimization towards not creating an index. <div class="note"><span class="notetitle">Note:</span> This
|
||
|
|
is the default if you do not use OPTIMIZE FOR n ROWS.</div>
|
||
|
|
<div class="p">Either a data
|
||
|
|
scan or an existing index is preferred. This mode can be specified by: <ul><li>The OPTIMIZE FOR n ROWS allowing the users to specify the number of rows
|
||
|
|
they expect to retrieve from the query. <p>The Optimizer using this value
|
||
|
|
to determine the percentage of rows that will be returned and optimizes accordingly.
|
||
|
|
A small value instructs the Optimizer to minimize the time required to retrieve
|
||
|
|
the first n rows.</p>
|
||
|
|
</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
</li>
|
||
|
|
<li>Minimize the time to process the whole query assuming that all selected
|
||
|
|
rows are returned to the application. This does not bias the Optimizer to
|
||
|
|
any particular access method. Specify this mode by using OPTIMIZE FOR n ROWS,
|
||
|
|
which allows the users to specify the number of rows they expect to retrieve
|
||
|
|
from the query. <p>The Optimizer uses this value to determine the percentage
|
||
|
|
of rows that will be returned and optimizes accordingly. A value greater than
|
||
|
|
or equal to the expected number of resulting rows instructs the Optimizer
|
||
|
|
to minimize the time required to run the entire query.</p>
|
||
|
|
</li>
|
||
|
|
</ol>
|
||
|
|
</li>
|
||
|
|
<li>The cost of any access path creations.</li>
|
||
|
|
<li>The cost of the expected number of page faults to read the rows and the
|
||
|
|
cost of processing the expected number of rows. <p>Page faults and number
|
||
|
|
of rows processed may be predicted by statistics the Optimizer obtains from
|
||
|
|
the database objects, including: </p>
|
||
|
|
<ul><li>Table size</li>
|
||
|
|
<li>Row size</li>
|
||
|
|
<li>Index size</li>
|
||
|
|
<li>Key size</li>
|
||
|
|
</ul>
|
||
|
|
<p>A weighted measure of the expected number of rows to process. This
|
||
|
|
is based on what the relational operators in the row selection predicates
|
||
|
|
(default filter factors) are likely to retrieve:</p>
|
||
|
|
<ul><li>10% for equal</li>
|
||
|
|
<li>33% for less-than, greater-than, less-than-equal-to, or greater-than-equal-to</li>
|
||
|
|
<li>90% for not equal</li>
|
||
|
|
<li>25% for BETWEEN range</li>
|
||
|
|
<li>10% for each IN list value</li>
|
||
|
|
</ul>
|
||
|
|
<p><strong>Key range estimate</strong> is a method that the Optimizer uses to gain
|
||
|
|
more accurate estimates of the number of expected rows that are selected from
|
||
|
|
one or more selection predicates. The Optimizer estimates by applying the
|
||
|
|
selection predicates against the left-most keys of an existing index. The <strong>default
|
||
|
|
filter factors</strong> then can be further refined by the estimate based on the
|
||
|
|
key range. If the left-most keys in an index match columns that are used in
|
||
|
|
row-selection predicates, use that index to estimate the number of keys that
|
||
|
|
match the selection criteria. The estimate of the number of keys is based
|
||
|
|
on the number of pages and key density of the machine index. It is performed
|
||
|
|
without actually accessing the keys. Full indexes over columns that are used
|
||
|
|
in selection predicates can significantly help optimization.</p>
|
||
|
|
</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzaikoptimizer.htm" title="Optimizer is an important module of the i5/OS Query component because it makes the key decisions for good database performance. Its main objective is to find the most efficient access path to the data.">Optimizer</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|