68 lines
5.3 KiB
HTML
68 lines
5.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="concept" />
|
||
|
|
<meta name="DC.Title" content="Indexing strategy" />
|
||
|
|
<meta name="abstract" content="There are two approaches to index creation: proactive and reactive. As the name implies proactive index creation involves anticipating which columns will be most often used for selection, joining, grouping and ordering; and then building indexes over those columns. In the reactive approach, indexes are created based on optimizer feedback, query implementation plan, and system performance measurements." />
|
||
|
|
<meta name="description" content="There are two approaches to index creation: proactive and reactive. As the name implies proactive index creation involves anticipating which columns will be most often used for selection, joining, grouping and ordering; and then building indexes over those columns. In the reactive approach, indexes are created based on optimizer feedback, query implementation plan, and system performance measurements." />
|
||
|
|
<meta name="DC.subject" content="index, strategy" />
|
||
|
|
<meta name="keywords" content="index, strategy" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="efindex.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqreact.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqproact.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="http://www.ibm.com/servers/enable/site/education/abstracts/indxng_abs.html" />
|
||
|
|
<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="rzajqindexstrat" />
|
||
|
|
<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>Indexing strategy</title>
|
||
|
|
</head>
|
||
|
|
<body id="rzajqindexstrat"><a name="rzajqindexstrat"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Indexing strategy</h1>
|
||
|
|
<div><p>There are two approaches to index creation: proactive and reactive.
|
||
|
|
As the name implies proactive index creation involves anticipating which columns
|
||
|
|
will be most often used for selection, joining, grouping and ordering; and
|
||
|
|
then building indexes over those columns. In the reactive approach, indexes
|
||
|
|
are created based on optimizer feedback, query implementation plan, and system
|
||
|
|
performance measurements.</p>
|
||
|
|
<p>It is useful to initially build indexes based on the database model and
|
||
|
|
application(s) and not any particular query. As a starting point, consider
|
||
|
|
designing basic indexes based on the following criteria: </p>
|
||
|
|
<ul><li>Primary and foreign key columns based on the database model</li>
|
||
|
|
<li>Commonly used local selection columns, including columns that are dependent,
|
||
|
|
such as an automobile's make and model</li>
|
||
|
|
<li>Commonly used join columns not considered primary or foreign key columns</li>
|
||
|
|
<li>Commonly used grouping columns</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<ul class="ullinks">
|
||
|
|
<li class="ulchildlink"><strong><a href="rzajqreact.htm">Reactive approach to tuning</a></strong><br />
|
||
|
|
To perform reactive tuning, build a prototype of the proposed application without any indexes and start running some queries or build an initial set of indexes and start running the application to see what gets used and what does not. Even with a smaller database, the slow running queries will become obvious very quickly.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rzajqproact.htm">Proactive approach to tuning</a></strong><br />
|
||
|
|
Typically you will create an index for the most selective columns and create statistics for the least selective columns in a query. By creating an index, the optimizer knows that the column is selective and it also gives the optimizer the ability to use that index to implement the query.</li>
|
||
|
|
</ul>
|
||
|
|
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="efindex.htm" title="DB2 Universal Database for iSeries provides two basic means for accessing tables: a table scan and an index-based retrieval. Index-based retrieval is typically more efficient than table scan when less than 20% of the table rows are selected.">Creating an index strategy</a></div>
|
||
|
|
</div>
|
||
|
|
<div class="relinfo"><strong>Related information</strong><br />
|
||
|
|
<div><a href="http://www.ibm.com/servers/enable/site/education/abstracts/indxng_abs.html" target="_blank">Indexing and statistics strategies for DB2 UDB for iSeries</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|