Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdppt\tdppt_sqlopt.htm
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=us-ascii" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <meta http-equiv="Content-Script-Type" content="text/javascript" /> <title>Optimizing Data Access Paths</title> <meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1.1 Build 005" /> <meta name="date" content="2009-05-27T15:13:2Z" /> <meta name="robots" content="noarchive" /> <meta name="doctitle" content=" Optimizing Data Access Paths" /> <meta name="relnum" content="11g Release 2 (11.2)" /> <meta name="partnum" content="E10822-01" /> <link rel="copyright" href="./dcommon/html/cpyr.htm" title="Copyright" type="text/html" /> <link rel="stylesheet" href="./dcommon/css/blafdoc.css" title="Oracle BLAFDoc" type="text/css" /> <link rel="contents" href="toc.htm" title="Contents" type="text/html" /> <link rel="prev" href="tdppt_sqltune020.htm" title="Previous" type="text/html" /> <link rel="next" href="tdppt_sqlopt001.htm" title="Next" type="text/html" /> <script src="./callback.js" type="text/javascript"></script> <noscript>Your browser does not support JavaScript. This help page requires JavaScript to render correctly.</noscript> </head> <body> <div class="zz-skip-header"><a href="#BEGIN">Skip Headers</a></div> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <col width="86%" /> <col width="*" /> <tr valign="bottom"> <td align="left"></td> <td align="center"><a href="tdppt_sqltune020.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdppt_sqlopt001.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> <p><a id="BABGHCAE" name="BABGHCAE"></a><a id="TDPPT170" name="TDPPT170"></a></p> <h1>Optimizing Data Access Paths</h1> <p><a name="BEGIN" id="BEGIN"></a></p> <p>To achieve optimal performance for data-intensive queries, materialized views and indexes are essential for SQL statements. However, implementing these objects does not come without cost. Creation and maintenance of these objects can be time-consuming. Space requirements can be significant. SQL Access Advisor<a id="sthref297" name="sthref297"></a><a id="sthref298" name="sthref298"></a><a id="sthref299" name="sthref299"></a> enables you to optimize query access paths by recommending materialized views and view logs<a id="sthref300" name="sthref300"></a>, indexes, SQL profiles, and partitions<a id="sthref301" name="sthref301"></a> for a specific workload.</p> <p>A <span class="bold">materialized view</span> provides access to table data by storing query results in a separate schema object. Unlike an ordinary view, which does not take up storage space or contain data, a materialized view contains the rows from a query of one or more base tables or views. A <span class="bold">materialized view log</span> is a schema object that records changes to a master table's data, so that a materialized view defined on the master table can be refreshed incrementally. SQL Access Advisor recommends how to optimize materialized views so that they can be rapidly refreshed and make use of the query rewrite feature. To learn more about materialized views, see <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=CNCPT411','newWindow').focus()"><span class="italic">Oracle Database Concepts</span></a>.</p> <p>SQL Access Advisor also recommends bitmap, function-based, and B-tree indexes. A <span class="bold">bitmap index</span><a id="sthref302" name="sthref302"></a> reduces response time for many types of ad hoc queries and can also reduce storage space compared to other indexes. A <span class="bold">function-based index</span><a id="sthref303" name="sthref303"></a> derives the indexed value from the table data. For example, to find character data in mixed cases, a function-based index search for values as if they were all in uppercase. <span class="bold">B-tree indexes</span><a id="sthref304" name="sthref304"></a> are commonly used to index unique or near-unique keys.</p> <p>Using SQL Access Advisor involves the following tasks:</p> <ul> <li> <p><a href="tdppt_sqlopt001.htm#BABHDIFI">Running SQL Access Advisor</a></p> </li> <li> <p><a href="tdppt_sqlopt016.htm#CHDFDJEH">Reviewing the SQL Access Advisor Recommendations</a></p> </li> <li> <p><a href="tdppt_sqlopt021.htm#CIHEAGDG">Implementing the SQL Access Advisor Recommendations</a></p> </li> </ul> <div class="helpinfonotealso"> <h2>Related Topics</h2> <p><a href="tdppt_sqlid.htm#BABFHBFJ">Identifying High-Load SQL Statements</a></p> <p><a href="tdppt_sqltune.htm#CAEBFCFJ">Tuning SQL Statements</a> for information about SQL Tuning Advisor</p> <p><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=CNCPT721','newWindow').focus()"><span class="italic">Oracle Database Concepts</span></a> to learn about indexes</p> </div> <!-- Start Footer --> <div class="footer"> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <col width="86%" /> <col width="*" /> <tr> <td align="left"><span class="copyrightlogo">Copyright © 2007, 2009, Oracle and/or its affiliates. All rights reserved.</span><br /> <a href="./dcommon/html/cpyr.htm"><span class="copyrightlogo">Legal Notices</span></a></td> <td align="center"><a href="tdppt_sqltune020.htm"><img src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="tdppt_sqlopt001.htm"><img src="./dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> </div> <!-- class="footer" --> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de