84 lines
6.8 KiB
HTML
84 lines
6.8 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="Creating an index strategy" />
|
||
|
|
<meta name="abstract" content="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." />
|
||
|
|
<meta name="description" content="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." />
|
||
|
|
<meta name="DC.subject" content="index, creating a strategy" />
|
||
|
|
<meta name="keywords" content="index, creating a strategy" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqkickoff.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqbinary.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="whatareevi.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqradixevi.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqindexopt.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqindexstrat.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="rzajqeffectindex.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="usesortseq.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="indexxmp.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="efindex" />
|
||
|
|
<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>Creating an index strategy</title>
|
||
|
|
</head>
|
||
|
|
<body id="efindex"><a name="efindex"><!-- --></a>
|
||
|
|
<!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Creating an index strategy</h1>
|
||
|
|
<div><p><span class="keyword">DB2 Universal Database™ for iSeries™</span> 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.</p>
|
||
|
|
<p>There are two kinds of persistent indexes: binary radix tree indexes, which
|
||
|
|
have been available since 1988, and encoded vector indexes (EVIs), which became
|
||
|
|
available in 1998 with V4R2. Both types of indexes are useful in improving
|
||
|
|
performance for certain kinds of queries.</p>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<ul class="ullinks">
|
||
|
|
<li class="ulchildlink"><strong><a href="rzajqbinary.htm">Binary radix indexes</a></strong><br />
|
||
|
|
A radix index is a multilevel, hybrid tree structure that allows a large number of key values to be stored efficiently while minimizing access times. A key compression algorithm assists in this process. The lowest level of the tree contains the leaf nodes, which contain the address of the rows in the base table that are associated with the key value. The key value is used to quickly navigator to the leaf node with a few simple binary search tests.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="whatareevi.htm">Encoded vector indexes</a></strong><br />
|
||
|
|
An encoded vector index (EVI) is an index object that is used by the query optimizer and database engine to provide fast data access in decision support and query reporting environments.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rzajqradixevi.htm">Comparing Binary radix indexes and Encoded vector indexes</a></strong><br />
|
||
|
|
DB2<sup>®</sup> UDB
|
||
|
|
for iSeries makes
|
||
|
|
indexes a powerful tool. </li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rzajqindexopt.htm">Indexes and the optimizer</a></strong><br />
|
||
|
|
Since the iSeries optimizer uses cost based optimization, the
|
||
|
|
more information that the optimizer is given about the rows and columns in
|
||
|
|
the database, the better able the optimizer is to create the best possible
|
||
|
|
(least costly/fastest) access plan for the query. With the information from
|
||
|
|
the indexes, the optimizer can make better choices about how to process the
|
||
|
|
request (local selection, joins, grouping, and ordering).</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rzajqindexstrat.htm">Indexing strategy</a></strong><br />
|
||
|
|
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.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="rzajqeffectindex.htm">Coding for effective indexes</a></strong><br />
|
||
|
|
The following topics provide suggestions that will help you to
|
||
|
|
design code which allows <span class="keyword">DB2 Universal Database for iSeries</span> to
|
||
|
|
take advantage of available indexes: </li>
|
||
|
|
<li class="ulchildlink"><strong><a href="usesortseq.htm">Using indexes with sort sequence</a></strong><br />
|
||
|
|
The following sections provide useful information about how indexes work with sort sequence tables.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="indexxmp.htm">Examples of indexes</a></strong><br />
|
||
|
|
The following index examples are provided to help you create effective indexes.</li>
|
||
|
|
</ul>
|
||
|
|
|
||
|
|
<div class="familylinks">
|
||
|
|
<div class="parentlink"><strong>Parent topic:</strong> <a href="rzajqkickoff.htm" title="The goal of database performance tuning is to minimize the response time of your queries and to make the best use of your server's resources by minimizing network traffic, disk I/O, and CPU time. This goal can only be achieved by understanding the logical and physical structure of your data, understanding the applications used on your server, and understanding how the many conflicting uses of your database may impact database performance.">Performance and query optimization</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|