chap14_7.html

来自「Oracle资料大集合」· HTML 代码 · 共 415 行 · 第 1/2 页

<td><a href="http://www.itlibrary.com/reference/dir.contentmanagement.html"><font face="verdana,helvetica" size="1" color="000000"><b>Content Management</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.productivityapplications1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Productivity Applications</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.hardware1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Hardware</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.funandgames1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Fun &amp; Games</b></font></td>
</tr>
</table>


<!-- <IMG SRC="/images/leftnav.gif" WIDTH=111 HEIGHT=404 ALT="nav" border="0"  ISMAP USEMAP="#leftnav"> -->
<p>
<a href="http://www.earthweb.com/jobs/" target=new><IMG SRC="../../../images/dice-105x30.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.earthwebdirect.com/" target=new><IMG SRC="../../../images/earthwebdirect.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.fatbrain.com/home.html?from=UUX592" target=new><IMG SRC="../../../images/fatbrain.gif" WIDTH=105 HEIGHT=30 ALT="Fatbrain" border="0"></a>
<a href="http://auctions.earthweb.com" target=new><IMG SRC="../../../images/auctions_105x30.gif" WIDTH=105 HEIGHT=30 ALT="Auctions" border="0"></a>
<a href="http://www.supportsource.com" target=new><IMG SRC="../../../images/ss2000sneak2.gif" WIDTH=105 HEIGHT=30 ALT="Support Source Answers" border="0"></a>
<P>

<b><font face="verdana,helvetica" size="2">EarthWeb sites</FONT></b><br>
  <FONT SIZE="1">
  <a href="http://www.crossnodes.com"><font face="verdana,helvetica" size="1" color="000000">Crossnodes</font></a><br>
  <a href="http://www.datamation.com"><font face="verdana,helvetica" size="1" color="000000">Datamation</font></a><br>
  <a href="http://www.developer.com"><font face="verdana,helvetica" size="1" color="000000">Developer.com</FONT></A><br>
  <a href="http://www.dice.com"><font face="verdana,helvetica" size="1" color="000000">DICE</font></a><br>
  <a href="http://www.earthweb.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb.com</font></a><br>
  <a href="http://www.earthwebdirect.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb Direct</font></a><br>
  <a href="http://www.erphub.com"><font face="verdana,helvetica" size="1" color="000000">ERP Hub</font></a><br>
  <a href="http://www.gamelan.com"><font face="verdana,helvetica" size="1" color="000000">Gamelan</font></a><br>
  <a href="http://www.gocertify.com"><font face="verdana,helvetica" size="1" color="000000">GoCertify.com</font></a><br>
  <a href="http://www.htmlgoodies.com"><font face="verdana,helvetica" size="1" color="000000">HTMLGoodies</font></a><br>
  <a href="http://www.intranetjournal.com"><font face="verdana,helvetica" size="1" color="000000">Intranet Journal</font></a><br>
  <a href="http://www.itknowledge.com"><font face="verdana,helvetica" size="1" color="000000">IT Knowledge</font></a><br>
  <a href="http://www.itlibrary.com"><font face="verdana,helvetica" size="1" color="000000">IT Library</font></a><br>
  <a href="http://www.javagoodies.com"><font face="verdana,helvetica" size="1" color="000000">JavaGoodies</font></a><br>
  <a href="http://www.jars.com"><font face="verdana,helvetica" size="1" color="000000">JARS</font></a><br>
  <a href="http://www.javascripts.com"><font face="verdana,helvetica" size="1" color="000000">JavaScripts.com</font></a><br>
  <a href="http://www.opensourceit.com"><font face="verdana,helvetica" size="1" color="000000">open source IT</font></a><br>
  <a href="http://www.roadcoders.com"><font face="verdana,helvetica" size="1" color="000000">RoadCoders</font></a><br>
  <a href="http://www.Y2Kinfo.com"><font face="verdana,helvetica" size="1" color="000000">Y2K Info</font></a>
</FONT>

</td>



<!--End Left Navigation column -->
<!-- spacer column  -->

<TD width="40">
<img src="../../../images/dotclear.gif" WIDTH="40" HEIGHT="1" border=0>
</TD>

<!--Begin Content Column -->

<TD VALIGN="TOP" width="500">

<P>




<blockquote>
<p>
<b><font size="+1">	14.7 How do I匬ass hints to the optimizer?</font></b>
<p>
<B>Problem</B>
<P>
I have used EXPLAIN PLAN, the AUTOTRACE option, or SQL*Trace梡resented in How-To抯 14.2, 14.3, and 14.5, respectively梩o identify some poorly performing SQL statements. I think that the optimizer is making the wrong decision about how to execute some of these statements, degrading performance. How do I suggest an alternate execution path to the optimizer?
<p>
<B>Technique</B>
<p>
Optimizer hints reside in comment blocks; must follow the SELECT, UPDATE, INSERT, or DELETE keywords; and change the execution plan for the statement containing the hint. The syntax of an optimizer hint is /*+ hint */. Table 14.11 lists Oracle8 optimizer hints. Use the AUTOTRACE option, EXPLAIN PLAN, or SQL*Trace to determine how optimizer hints change the execution plan.
<p>
Table 14.11 Oracle8 optimizer hints
<p>
<table cellpadding=3 cellspacing=3 border=0 bgcolor="cdcdcd">

<TR>
<td>Value</td>
<td>Effect</td>
</TR>

<TR>
<td>ALL_ROWS</td>
<td>Optimize the statement for best total throughput.</td>
</TR>

<TR><td>AND_EQUAL</td>
<td>Use index merging on the specified table.</td></TR>

<TR><td>APPEND</td>
<td>Append data to a table, do not use existing free space.</td></TR>

<TR><td>CACHE</td>
<td>Look in the most recently used end of the buffer cache LRU list when performing a full table scan.</td></TR>

<TR><td>CHOOSE</td>
<td>Use cost-based optimization if ANALYZE statistics exist, otherwise use rule-based optimization.</td></TR>

<TR><td>FIRST_ROWS</td>
<td>Optimize the SQL statement for best response time.</td></TR>

<TR><td>CLUSTER</td>
<td>Use a cluster scan.</td></TR>

<TR><td>COST</td>
<td>Use cost-based optimization.</td></TR>

<TR><td>FULL</td>
<td>Use a full table scan on the specified table.</td></TR>

<TR><td>HASH</td>
<td>Use a hash search on the specified table. </td></TR>

<TR><td>HASH_AJ</td>
<td>Use a hash anti-join instead of NOT IN</td></TR>

<TR><td>INDEX</td>
<td>Use the specified index on the specified table.</td></TR>

<TR><td>INDEX_ASC</td>
<td>Use the specified index in ascending order.</td></TR>

<TR><td>INDEX_DESC</td>
<td>Use the specified index in descending order. </td></TR>

<TR><td>INDEX_COMBINE</td>
<td>Use a combination of bitmap indexes.</td></TR>

<TR><td>INDEX_FFS</td>
<td>Use a fast full index scan instead of a full table scan.</td></TR>

<TR><td>MERGE_AJ</td>
<td>Use a merge anti-join instead of a NOT IN subquery.</td></TR>

<TR>
<td>NO_APPEND</td>
<td>Do not append data to a table, use existing free space.</td>
</TR>

<TR>
<td>NO_CACHE</td>
<td>Look in the least recently used end of the buffer cache LRU list when performing a full table scan.</td>
</TR>

<TR><td>NO_MERGE</td>
<td>Join each table with a sort-merge join. </td></TR>

<TR><td>NO_PARALLEL</td>
<td>Do not scan the table in parallel.</td></TR>

<TR><td>ORDERED</td>
<td>Use the join sequence from the FROM clause.</td></TR>

<TR><td>PARALLEL</td>
<td>Specify the number of slave processes with which to accomplish the operation.</td></TR>

<TR><td>PARALLEL_INDEX</td>
<td>Parallelize fast full index scans.</td></TR>

<TR><td>PUSH_SUBQ</td>
<td>Evaluate non-merged subqueries as early in the execution plan as possible.</td></TR>

<TR><td>ROWID</td>
<td>Use the ROWID access method.</td></TR>

<TR><td>RULE</td>
<td>Use rule-based optimization.</td></TR>

<TR><td>STAR</td>
<td>Join the large table last with a nested loops join on an index.</td></TR>

<TR><td>USE_HASH</td>
<td>Join each specified table with a hash join.</td></TR>

<TR><td>USE_MERGE</td>
<td>Use sort-merge join techniques on the specified table.</td></TR>

<TR><td>USE_NL</td>
<td>Use nested-loop join techniques on the specified table.</td></TR>

<TR><td>USE_CONCAT</td>
<td>Use UNION ALL instead of the combined OR condition in the WHERE clause.</td></TR>
</table>

Steps
<p>
1.	Run SQL*Plus and connect as the WAITE user. CHP14_14.SQL, shown in <A HREF="image/14oht14.gif" TARGET="_blank">Figure 14.14</A>, creates a sample table used in this How-To. Run this script using the START command.

<p>

The first SQL statement creates a table named EMP14_7. The second statement creates an index on the EMPNO column of the table, and the third statement block, an anonymous PL/SQL procedure, populates the table with 250 rows of sample data.

<p>

2.	If you haven抰 already done so, follow the directions in the 揅omments