Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\reference\useindx.08.5.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 xml:lang="en" lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <!-- MOTW-DISABLED saved from url=(0014)about:internet --> <title>Deciding Which Indexes to Create</title> <link rel="StyleSheet" href="css/useindx.css" type="text/css" media="all" /> <link rel="StyleSheet" href="css/webworks.css" type="text/css" media="all" /> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/context.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/towwhdir.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/wwhpagef.js"></script> <script type="text/javascript" language="JavaScript1.2"> <!-- var WebWorksRootPath = ""; // --> </script> <script type="text/javascript" language="JavaScript1.2"> <!-- // Set reference to top level help frame // var WWHFrame = WWHGetWWHFrame("", true); // --> </script> <script type="text/javascript" language="JavaScript1.2" src="scripts/expand.js"></script> </head> <body class="" style="background-color: #FFFFEE;" onload="WWHUpdate();" onunload="WWHUnload();" onkeydown="WWHHandleKeyDown((document.all||document.getElementById||document.layers)?event:null);" onkeypress="WWHHandleKeyPress((document.all||document.getElementById||document.layers)?event:null);" onkeyup="WWHHandleKeyUp((document.all||document.getElementById||document.layers)?event:null);"> <br /> <div class="WebWorks_Breadcrumbs" style="text-align: left;"> <a class="WebWorks_Breadcrumb_Link" href="useindx.08.1.html#106215">6 Using Indexes</a> : Deciding Which Indexes to Create</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="106215">Deciding Which Indexes to Create</a></div> <div class="Body"><a name="106216">Before you create indexes for a database table, consider how you will use the table. The </a>most common operations on a table are:</div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106217">Inserting, updating, and deleting rows</a></div> </td> </tr> </table> </div> <div class="list_bulleted_single_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_single_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_single_inner"><a name="106218">Retrieving rows</a></div> </td> </tr> </table> </div> <div class="Body"><a name="106219">If you most often insert, update, and delete rows, then the fewer indexes associated with the </a>table, the better the performance. This is because the driver must maintain the indexes as well as the database tables, thus slowing down the performance of row inserts, updates, and deletes. It may be more efficient to drop all indexes before modifying a large number of rows, and re-create the indexes after the modifications.</div> <div class="Body"><a name="106220">If you most often retrieve rows, you must look further to define the criteria for retrieving rows </a>and create indexes to improve the performance of these retrievals. Assume you have an employee database table and you will retrieve rows based on employee name, department, or hire date. You would create three indexesâ??one on the dept field, one on the hire_date field, and one on the last_name field. Or perhaps, for the retrievals based on the name field, you would want an index that concatenates the last_name and the first_name fields (see <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('reference', 'useindx.08.4.html#106198', '');">â??Indexing Multiple Fieldsâ??</a></span> for details).</div> <div class="Body"><a name="106224">Here are a few rules to help you decide which indexes to create:</a></div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106225">If your row retrievals are based on only one field at a time (for example, </a><span class="Syntax">dept='D101'</span>), create an index on these fields.</div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106226">If your row retrievals are based on a combination of fields, look at the combinations.</a></div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106227">If the comparison operator for the conditions is And (for example, </a><span class="Syntax">city = 'Raleigh' </span><span class="Syntax">AND state = 'NC'</span>), then build a concatenated index on the city and state fields. This index is also useful for retrieving rows based on the city field.</div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106228">If the comparison operator is OR (for example, </a><span class="Syntax">dept = 'D101' OR hire_date > </span><span class="Syntax">{01/30/89}</span>), an index does not help performance. Therefore, you need not create one.</div> </td> </tr> </table> </div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106229">If the retrieval conditions contain both AND and OR comparison operators, you can use </a>an index if the OR conditions are grouped. For example:</div> </td> </tr> </table> </div> <div class="syntax_embed_first"><a name="106230">dept = 'D101' AND (hire_date > {01/30/89} OR exempt = 1)</a></div> <div class="Body_Indent"><a name="106232">In this case, an index on the dept field improves performance.</a></div> <div class="list_bulleted_outer" style="margin-left: 0pt;"> <table border="0" cellspacing="0" cellpadding="0" summary=""> <tr style="vertical-align: baseline;"> <td> <div class="list_bulleted_inner" style="width: 18pt; white-space: nowrap;">â? </div> </td> <td width="100%"> <div class="list_bulleted_inner"><a name="106233">If the AND conditions are grouped, an index does not improve performance. For </a>example:</div> </td> </tr> </table> </div> <div class="syntax_embed_first"><a name="106234">(dept = 'D101' AND hire_date) > {01/30/89}) OR exempt = 1</a></div> <script type="text/javascript" language="JavaScript1.2"> <!-- // Clear related topics // WWHClearRelatedTopics(); document.writeln(WWHRelatedTopicsInlineHTML()); // --> </script> </blockquote> <script type="text/javascript" language="JavaScript1.2"> <!-- document.write(WWHRelatedTopicsDivTag() + WWHPopupDivTag() + WWHALinksDivTag()); // --> </script> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de