91 lines
6.0 KiB
HTML
91 lines
6.0 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="Encoded vector indexes" />
|
||
|
|
<meta name="abstract" content="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." />
|
||
|
|
<meta name="description" content="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." />
|
||
|
|
<meta name="DC.subject" content="index, encoded vector index, definitions" />
|
||
|
|
<meta name="keywords" content="index, encoded vector index, definitions" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="efindex.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="howeviworks.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="whentouseevis.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="evimaintenance.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="recommendationsforevi.htm" />
|
||
|
|
<meta name="DC.Relation" scheme="URI" content="../db2/rbafzmstxcindx.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="whatareevi" />
|
||
|
|
<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>Encoded vector indexes</title>
|
||
|
|
</head>
|
||
|
|
<body id="whatareevi"><a name="whatareevi"><!-- --></a>
|
||
|
|
<img src="./delta.gif" alt="Start of change" /><!-- Java sync-link --><script language="Javascript" src="../rzahg/synch.js" type="text/javascript"></script>
|
||
|
|
<h1 class="topictitle1">Encoded vector indexes</h1>
|
||
|
|
<div><p>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. </p>
|
||
|
|
<p>EVIs are a complementary alternative to existing index objects (binary
|
||
|
|
radix tree structure - logical file or SQL index) and are a variation on bitmap
|
||
|
|
indexing. Because of their compact size and relative simplicity, EVIs provide
|
||
|
|
for faster scans of a table that can also be processed in parallel.</p>
|
||
|
|
<p>An EVI is a data structure that is stored as two components:
|
||
|
|
</p>
|
||
|
|
<ul><li>The symbol table contains statistical and descriptive information about
|
||
|
|
each distinct key value represented in the table. Each distinct key is assigned
|
||
|
|
a unique code, either 1, 2 or 4 bytes in size.</li>
|
||
|
|
<li>The vector is an array of codes listed in the same ordinal position as
|
||
|
|
the rows in the table. The vector does not contain any pointers to the actual
|
||
|
|
rows in the table.</li>
|
||
|
|
</ul>
|
||
|
|
<div class="section"><p>Advantages of EVIs:</p>
|
||
|
|
<ul><li>Require less storage</li>
|
||
|
|
<li>May have better build times than radix, especially if the number of unique
|
||
|
|
values in the column(s) defined for the key is relatively small.</li>
|
||
|
|
<li>Provide more accurate statistics to the query optimizer</li>
|
||
|
|
<li>Considerably better performance for certain grouping types of queries</li>
|
||
|
|
<li>Good performance characteristics for decision support environments.</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
<div class="section"><p>Disadvantages of EVIs:</p>
|
||
|
|
<ul><li>Cannot be used in ordering</li>
|
||
|
|
<li>Use for grouping is specialized </li>
|
||
|
|
<li>Use with joins always done in cooperation with hash table processing</li>
|
||
|
|
<li>Some additional maintenance idiosyncrasies</li>
|
||
|
|
</ul>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<div>
|
||
|
|
<ul class="ullinks">
|
||
|
|
<li class="ulchildlink"><strong><a href="howeviworks.htm">How the EVI works</a></strong><br />
|
||
|
|
EVIs work in different ways for costing and implementation.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="whentouseevis.htm">When to create EVIs</a></strong><br />
|
||
|
|
There are several instances when you should consider creating EVIs.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="evimaintenance.htm">EVI maintenance</a></strong><br />
|
||
|
|
There are unique challenges to maintaining EVIs. The following table shows a progression of how EVIs are maintained and the conditions under which EVIs are most effective and where EVIs are least effective based on the EVI maintenance characteristics.</li>
|
||
|
|
<li class="ulchildlink"><strong><a href="recommendationsforevi.htm">Recommendations for EVI use</a></strong><br />
|
||
|
|
Encoded vector indexes are a powerful tool for providing fast data access in decision support and query reporting environments, but to ensure the effective use of EVIs, you should implement EVIs with the following guidelines:</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="../db2/rbafzmstxcindx.htm">SQL Create Index statement</a></div>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
<img src="./deltaend.gif" alt="End of change" /></body>
|
||
|
|
</html>
|