邪恶八进制信息安全团队技术讨论组 » 常用数据库类{ Database Security } » 巨人脚下[ Oracle ] » [转载]Oracle中组合索引的使用详解

ring04h 2006-6-27 17:48

[转载]Oracle中组合索引的使用详解

<p>信息来源: 邪恶八进制信息安全团队</p><p>在Oracle中可以创建组合索引，即同时包含两个或两个以上列的索引。在组合索引的使用方面，Oracle有以下特点：</p><p /><p /><div id="2"> 1、 当使用基于规则的优化器（RBO）时，只有当组合索引的前导列出现在SQL语句的where子句中时，才会使用到该索引；</div><p /><p /><div id="3"> 2、 在使用Oracle9i之前的基于成本的优化器（CBO）时， 只有当组合索引的前导列出现在SQL语句的where子句中时，才可能会使用到该索引，这取决于优化器计算的使用索引的成本和使用全表扫描的成本，Oracle会自动选择成本低的访问路径（请见下面的测试1和测试2）；</div><p /><p /><div id="4"> 3、 从Oracle9i起，Oracle引入了一种新的索引扫描方式——索引跳跃扫描（index skip scan），这种扫描方式只有基于成本的优化器（CBO）才能使用。这样，当SQL语句的where子句中即使没有组合索引的前导列，并且索引跳跃扫描的成本低于其他扫描方式的成本时，Oracle就会使用该方式扫描组合索引（请见下面的测试3）；</div><p /><p /><div id="5"> 4、 Oracle优化器有时会做出错误的选择，因为它再“聪明”，也不如我们SQL语句编写人员更清楚表中数据的分布，在这种情况下，通过使用提示（hint），我们可以帮助Oracle优化器作出更好的选择（请见下面的测试4）。</div><p /><p /><div id="6"> 关于以上情况，我们分别测试如下：</div><p /><p /><div id="7"> 我们创建测试表T，该表的数据来源于Oracle的数据字典表all_objects，表T的结构如下：</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="8">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">desc</span><span style="COLOR: #000000"> t
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />名称 是否为空? 类型
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">--------------------------------------- -------- ---------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />OWNER </span><span style="COLOR: #808080">NOT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">NULL</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #000000">VARCHAR2</span><span style="COLOR: #000000">(</span><span style="FONT-WEIGHT: bold; COLOR: #800000">30</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #ff00ff">OBJECT_NAME</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">NOT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">NULL</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #000000">VARCHAR2</span><span style="COLOR: #000000">(</span><span style="FONT-WEIGHT: bold; COLOR: #800000">30</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />SUBOBJECT_NAME </span><span style="FONT-WEIGHT: bold; COLOR: #000000">VARCHAR2</span><span style="COLOR: #000000">(</span><span style="FONT-WEIGHT: bold; COLOR: #800000">30</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #ff00ff">OBJECT_ID</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">NOT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">NULL</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #000000">NUMBER</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />DATA_OBJECT_ID </span><span style="FONT-WEIGHT: bold; COLOR: #000000">NUMBER</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />OBJECT_TYPE </span><span style="FONT-WEIGHT: bold; COLOR: #000000">VARCHAR2</span><span style="COLOR: #000000">(</span><span style="FONT-WEIGHT: bold; COLOR: #800000">18</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />CREATED </span><span style="COLOR: #808080">NOT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">NULL</span><span style="COLOR: #000000"> DATE
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />LAST_DDL_TIME </span><span style="COLOR: #808080">NOT</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">NULL</span><span style="COLOR: #000000"> DATE
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #000000">TIMESTAMP</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #000000">VARCHAR2</span><span style="COLOR: #000000">(</span><span style="FONT-WEIGHT: bold; COLOR: #800000">19</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />STATUS </span><span style="FONT-WEIGHT: bold; COLOR: #000000">VARCHAR2</span><span style="COLOR: #000000">(</span><span style="FONT-WEIGHT: bold; COLOR: #800000">7</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">TEMPORARY</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #000000">VARCHAR2</span><span style="COLOR: #000000">(</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />GENERATED </span><span style="FONT-WEIGHT: bold; COLOR: #000000">VARCHAR2</span><span style="COLOR: #000000">(</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />SECONDARY </span><span style="FONT-WEIGHT: bold; COLOR: #000000">VARCHAR2</span><span style="COLOR: #000000">(</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />表中的数据分布情况如下：
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"> object_type,</span><span style="COLOR: #ff00ff">count</span><span style="COLOR: #000000">(</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">) </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> t </span><span style="COLOR: #0000ff">group</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">by</span><span style="COLOR: #000000"> object_type;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />OBJECT_TYPE </span><span style="COLOR: #ff00ff">COUNT</span><span style="COLOR: #000000">(</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">---------------- ----------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />CONSUMER </span><span style="COLOR: #0000ff">GROUP</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">20</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />EVALUATION CONTEXT </span><span style="FONT-WEIGHT: bold; COLOR: #800000">10</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">FUNCTION</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">360</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">INDEX</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">69</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />LIBRARY </span><span style="FONT-WEIGHT: bold; COLOR: #800000">20</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />LOB </span><span style="FONT-WEIGHT: bold; COLOR: #800000">20</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />OPERATOR </span><span style="FONT-WEIGHT: bold; COLOR: #800000">20</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />PACKAGE </span><span style="FONT-WEIGHT: bold; COLOR: #800000">1210</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">PROCEDURE</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">130</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />SYNONYM </span><span style="FONT-WEIGHT: bold; COLOR: #800000">16100</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">180</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />TYPE </span><span style="FONT-WEIGHT: bold; COLOR: #800000">2750</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">VIEW</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">8600</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />已选择13行。
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">count</span><span style="COLOR: #000000">(</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">) </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> t;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #ff00ff">COUNT</span><span style="COLOR: #000000">(</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">--------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">29489</span></div>
</div></pre></div><p /><div id="9"> 我们在表T上创建如下索引并对其进行分析：</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="10">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">SQL</span><span style="COLOR: #000000">></span><span style="COLOR: #000000"> create index indx_t on t(object_type,object_name);
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />索引已创建。
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />SQL</span><span style="COLOR: #000000">></span><span style="COLOR: #000000"> ANALYZE TABLE T COMPUTE STATISTICS
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">2</span><span style="COLOR: #000000"> FOR TABLE
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">3</span><span style="COLOR: #000000"> FOR ALL INDEXES
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">4</span><span style="COLOR: #000000"> FOR ALL INDEXED COLUMNS
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">5</span><span style="COLOR: #000000"></span><span style="COLOR: #000000">/</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />表已分析。</span></div>
</div></pre></div><div id="ParagraphCount" style="DISPLAY: none">10</div><div style="DISPLAY: none">现在让我们编写几条SQL语句来测试一下Oracle优化器对访问路径的选择： <p /><p /><div id="2"> 测试1）</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="3">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">set</span><span style="COLOR: #000000"> autotrace traceonly
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> T </span><span style="COLOR: #0000ff">WHERE</span><span style="COLOR: #000000"> OBJECT_TYPE</span><span style="COLOR: #808080">=</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">LOB</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />已选择20行。
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />Execution </span><span style="COLOR: #0000ff">Plan</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">--------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"> STATEMENT Optimizer</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">CHOOSE (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">22</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">20</span><span style="COLOR: #000000"> Bytes</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1740</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"> ACCESS (</span><span style="COLOR: #0000ff">BY</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INDEX</span><span style="COLOR: #000000"> ROWID) </span><span style="COLOR: #0000ff">OF</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">T</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000"> (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">22</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">20</span><span style="COLOR: #000000"> Bytes</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1740</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INDEX</span><span style="COLOR: #000000"> (RANGE SCAN) </span><span style="COLOR: #0000ff">OF</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">INDX_T</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000"> (NON</span><span style="COLOR: #808080">-</span><span style="COLOR: #0000ff">UNIQUE</span><span style="COLOR: #000000">) (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">20</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span></div>
</div></pre></div><p /><div id="4"><br /> 正如我们所期望的，由于使用了组合索引的前导列并且访问了表中的少量记录，Oracle明智地选择了索引扫描。那么，如果我们访问表中的大量数据时，Oracle会选择什么样的访问路径呢？请看下面的测试：</div><p /><p /><div id="5"> 测试2）</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="6">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> T </span><span style="COLOR: #0000ff">WHERE</span><span style="COLOR: #000000"> OBJECT_TYPE</span><span style="COLOR: #808080">=</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">SYNONYM</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />已选择16100行。
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />Execution </span><span style="COLOR: #0000ff">Plan</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">--------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"> STATEMENT Optimizer</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">CHOOSE (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">38</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">16100</span><span style="COLOR: #000000"> Bytes</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1400700</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"> ACCESS (</span><span style="COLOR: #0000ff">FULL</span><span style="COLOR: #000000">) </span><span style="COLOR: #0000ff">OF</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">T</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000"> (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">38</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">16100</span><span style="COLOR: #000000"> Bytes</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1400700</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">Statistics</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">--------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> recursive calls
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> db block gets
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1438</span><span style="COLOR: #000000"> consistent gets
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">13</span><span style="COLOR: #000000"> physical reads
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> redo size
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">941307</span><span style="COLOR: #000000"> bytes sent via SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">12306</span><span style="COLOR: #000000"> bytes received via SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1075</span><span style="COLOR: #000000"> SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net roundtrips </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #808080">/</span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> sorts (memory)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> sorts (</span><span style="COLOR: #0000ff">disk</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">16100</span><span style="COLOR: #000000"> rows processed</span></div>
</div></pre></div><p /><div id="7"><br /> 很明显，即使使用了组合索引的前导列，但是由于访问了表中的大量数据，Oracle选择了不使用索引而直接使用全表扫描，因为优化器认为全表扫描的成本更低，但事实是不是真的这样的？我们通过增加提示（hint）来强制它使用索引来看看：</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="8">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img id="Codehighlighter_11_32_Open_Image" onclick="function anonymous()
{
this.style.display='none'; Codehighlighter_11_32_Open_Text.style.display='none'; Codehighlighter_11_32_Closed_Image.style.display='inline'; Codehighlighter_11_32_Closed_Text.style.display='inline';
}" src="http://cms.it168.com/Images/OutliningIndicators/ExpandedBlockStart.gif" align="top" /><img id="Codehighlighter_11_32_Closed_Image" style="DISPLAY: none" onclick="function anonymous()
{
this.style.display='none'; Codehighlighter_11_32_Closed_Text.style.display='none'; Codehighlighter_11_32_Open_Image.style.display='inline'; Codehighlighter_11_32_Open_Text.style.display='inline';
}" src="http://cms.it168.com/Images/OutliningIndicators/ContractedBlock.gif" align="top" /><span style="COLOR: #000000">SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span id="Codehighlighter_11_32_Closed_Text" style="BORDER-RIGHT: #808080 1px solid; BORDER-TOP: #808080 1px solid; DISPLAY: none; BORDER-LEFT: #808080 1px solid; BORDER-BOTTOM: #808080 1px solid; BACKGROUND-COLOR: #ffffff">/**/</span><span id="Codehighlighter_11_32_Open_Text"><span style="COLOR: #008080">/*</span><span style="COLOR: #008080">+ INDEX (T INDX_T)</span><span style="COLOR: #008080">*/</span></span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">FROM</span><span style="COLOR: #000000"> T </span><span style="COLOR: #0000ff">WHERE</span><span style="COLOR: #000000"> OBJECT_TYPE</span><span style="COLOR: #808080">=</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">SYNONYM</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />已选择16100行。
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />Execution </span><span style="COLOR: #0000ff">Plan</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">--------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"> STATEMENT Optimizer</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">CHOOSE (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">16180</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">16100</span><span style="COLOR: #000000"> Bytes</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1400700</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"> ACCESS (</span><span style="COLOR: #0000ff">BY</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INDEX</span><span style="COLOR: #000000"> ROWID) </span><span style="COLOR: #0000ff">OF</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">T</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000"> (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">16180</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">16100</span><span style="COLOR: #000000"> Bytes</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1400700</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INDEX</span><span style="COLOR: #000000"> (RANGE SCAN) </span><span style="COLOR: #0000ff">OF</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">INDX_T</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000"> (NON</span><span style="COLOR: #808080">-</span><span style="COLOR: #0000ff">UNIQUE</span><span style="COLOR: #000000">) (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">80</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">16100</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">Statistics</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">--------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> recursive calls
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> db block gets
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">17253</span><span style="COLOR: #000000"> consistent gets
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">16</span><span style="COLOR: #000000"> physical reads
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> redo size
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">298734</span><span style="COLOR: #000000"> bytes sent via SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">12306</span><span style="COLOR: #000000"> bytes received via SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1075</span><span style="COLOR: #000000"> SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net roundtrips </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #808080">/</span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> sorts (memory)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> sorts (</span><span style="COLOR: #0000ff">disk</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">16100</span><span style="COLOR: #000000"> rows processed</span></div>
</div></pre></div><p /><div id="9"> 从以上结果可以看出，在访问大量数据的情况下，使用索引确实会导致更高的执行成本，这从statistics部分的逻辑读取数（consistent gets）就可以看出，使用索引导致的逻辑读取数是不使用索引导致的逻辑读的10倍还多。因此，Oracle明智地选择了全表扫描而不是索引扫描。</div><div>下面，让我们来看看where子句中没有索引前导列的情况：<p /><p /><div id="2"> 测试3）</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="3">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> t </span><span style="COLOR: #0000ff">where</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">object_name</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">DEPT</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />已选择10行。
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />Execution </span><span style="COLOR: #0000ff">Plan</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">--------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"> STATEMENT Optimizer</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">CHOOSE (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">29</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">14</span><span style="COLOR: #000000"> Bytes</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1218</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"> ACCESS (</span><span style="COLOR: #0000ff">BY</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INDEX</span><span style="COLOR: #000000"> ROWID) </span><span style="COLOR: #0000ff">OF</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">T</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000"> (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">29</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">14</span><span style="COLOR: #000000"> Bytes</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1218</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INDEX</span><span style="COLOR: #000000"> (SKIP SCAN) </span><span style="COLOR: #0000ff">OF</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">INDX_T</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000"> (NON</span><span style="COLOR: #808080">-</span><span style="COLOR: #0000ff">UNIQUE</span><span style="COLOR: #000000">) (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">14</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">14</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">Statistics</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">--------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> recursive calls
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> db block gets
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">24</span><span style="COLOR: #000000"> consistent gets
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> physical reads
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> redo size
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1224</span><span style="COLOR: #000000"> bytes sent via SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">503</span><span style="COLOR: #000000"> bytes received via SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"> SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net roundtrips </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #808080">/</span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> sorts (memory)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> sorts (</span><span style="COLOR: #0000ff">disk</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">10</span><span style="COLOR: #000000"> rows processed</span></div>
</div></pre></div><p /><div id="4"> OK！由于只查询了10条数据，即使没有使用前导列，Oracle正确地选择了索引跳跃扫描。我们再来看看如果不使用索引跳跃扫描，该语句的成本：</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="5">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img id="Codehighlighter_11_34_Open_Image" onclick="function anonymous()
{
this.style.display='none'; Codehighlighter_11_34_Open_Text.style.display='none'; Codehighlighter_11_34_Closed_Image.style.display='inline'; Codehighlighter_11_34_Closed_Text.style.display='inline';
}" src="http://cms.it168.com/Images/OutliningIndicators/ExpandedBlockStart.gif" align="top" /><img id="Codehighlighter_11_34_Closed_Image" style="DISPLAY: none" onclick="function anonymous()
{
this.style.display='none'; Codehighlighter_11_34_Closed_Text.style.display='none'; Codehighlighter_11_34_Open_Image.style.display='inline'; Codehighlighter_11_34_Open_Text.style.display='inline';
}" src="http://cms.it168.com/Images/OutliningIndicators/ContractedBlock.gif" align="top" /><span style="COLOR: #000000">SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span id="Codehighlighter_11_34_Closed_Text" style="BORDER-RIGHT: #808080 1px solid; BORDER-TOP: #808080 1px solid; DISPLAY: none; BORDER-LEFT: #808080 1px solid; BORDER-BOTTOM: #808080 1px solid; BACKGROUND-COLOR: #ffffff">/**/</span><span id="Codehighlighter_11_34_Open_Text"><span style="COLOR: #008080">/*</span><span style="COLOR: #008080">+ NO_INDEX(T INDX_T)</span><span style="COLOR: #008080">*/</span></span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> t </span><span style="COLOR: #0000ff">where</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">object_name</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">DEPT</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />已选择10行。
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />Execution </span><span style="COLOR: #0000ff">Plan</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">--------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"> STATEMENT Optimizer</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">CHOOSE (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">38</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">14</span><span style="COLOR: #000000"> Bytes</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1218</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"> ACCESS (</span><span style="COLOR: #0000ff">FULL</span><span style="COLOR: #000000">) </span><span style="COLOR: #0000ff">OF</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">T</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000"> (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">38</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">14</span><span style="COLOR: #000000"> Bytes</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">1218</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">Statistics</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">--------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> recursive calls
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> db block gets
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">375</span><span style="COLOR: #000000"> consistent gets
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">17</span><span style="COLOR: #000000"> physical reads
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> redo size
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1224</span><span style="COLOR: #000000"> bytes sent via SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">503</span><span style="COLOR: #000000"> bytes received via SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"> SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net roundtrips </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #808080">/</span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> sorts (memory)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> sorts (</span><span style="COLOR: #0000ff">disk</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">10</span><span style="COLOR: #000000"> rows processed</span></div>
</div></pre></div><p /><div id="6"> 正如我们所料，不使用索引所导致的逻辑读（375）确实比使用索引的逻辑读多（24），达到10倍以上。</div><div>继续我们的测试，现在我们来看看Oracle不选择使用索引的情况：<p /><p /><div id="2"> 测试4）</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="3">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /><span style="COLOR: #000000">SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> t </span><span style="COLOR: #0000ff">where</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">object_name</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">LIKE</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">DE%</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />已选择180行。
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />Execution </span><span style="COLOR: #0000ff">Plan</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">--------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"> STATEMENT Optimizer</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">CHOOSE (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">38</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">37</span><span style="COLOR: #000000"> Bytes</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">3219</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"> ACCESS (</span><span style="COLOR: #0000ff">FULL</span><span style="COLOR: #000000">) </span><span style="COLOR: #0000ff">OF</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">T</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000"> (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">38</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">37</span><span style="COLOR: #000000"> Bytes</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">3219</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">Statistics</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">--------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> recursive calls
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> db block gets
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">386</span><span style="COLOR: #000000"> consistent gets
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">16</span><span style="COLOR: #000000"> physical reads
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> redo size
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">12614</span><span style="COLOR: #000000"> bytes sent via SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">624</span><span style="COLOR: #000000"> bytes received via SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">13</span><span style="COLOR: #000000"> SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net roundtrips </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #808080">/</span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> sorts (memory)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> sorts (</span><span style="COLOR: #0000ff">disk</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">180</span><span style="COLOR: #000000"> rows processed</span></div>
</div></pre></div><p /><div id="4"> 这次只选择了180条数据，跟表T中总的数据量29489条相比，显然只是很小的一部分，但是Oracle还是选择了全表扫描，有386个逻辑读。这种情况下，如果我们强制使用索引，情况会怎样呢？</div><p /><div style="SCROLLBAR-HIGHLIGHT-COLOR: buttonhighlight; OVERFLOW: auto; WIDTH: 500px"><pre style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 4px; BORDER-TOP: black 1px solid; PADDING-LEFT: 4px; PADDING-BOTTOM: 4px; BORDER-LEFT: black 1px solid; PADDING-TOP: 4px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ededed"><div id="5">
<div><!--
Code highlighting produced by Actipro CodeHighlighter (freeware)
[url]http://www.CodeHighlighter.com/[/url]
--><img id="Codehighlighter_11_31_Open_Image" onclick="function anonymous()
{
this.style.display='none'; Codehighlighter_11_31_Open_Text.style.display='none'; Codehighlighter_11_31_Closed_Image.style.display='inline'; Codehighlighter_11_31_Closed_Text.style.display='inline';
}" src="http://cms.it168.com/Images/OutliningIndicators/ExpandedBlockStart.gif" align="top" /><img id="Codehighlighter_11_31_Closed_Image" style="DISPLAY: none" onclick="function anonymous()
{
this.style.display='none'; Codehighlighter_11_31_Closed_Text.style.display='none'; Codehighlighter_11_31_Open_Image.style.display='inline'; Codehighlighter_11_31_Open_Text.style.display='inline';
}" src="http://cms.it168.com/Images/OutliningIndicators/ContractedBlock.gif" align="top" /><span style="COLOR: #000000">SQL</span><span style="COLOR: #808080">></span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">select</span><span id="Codehighlighter_11_31_Closed_Text" style="BORDER-RIGHT: #808080 1px solid; BORDER-TOP: #808080 1px solid; DISPLAY: none; BORDER-LEFT: #808080 1px solid; BORDER-BOTTOM: #808080 1px solid; BACKGROUND-COLOR: #ffffff">/**/</span><span id="Codehighlighter_11_31_Open_Text"><span style="COLOR: #008080">/*</span><span style="COLOR: #008080">+ INDEX(T INDX_T)</span><span style="COLOR: #008080">*/</span></span><span style="COLOR: #000000"></span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> t </span><span style="COLOR: #0000ff">where</span><span style="COLOR: #000000"></span><span style="COLOR: #ff00ff">object_name</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">LIKE</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">DE%</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000">;
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />已选择180行。
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />Execution </span><span style="COLOR: #0000ff">Plan</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">--------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">SELECT</span><span style="COLOR: #000000"> STATEMENT Optimizer</span><span style="COLOR: #808080">=</span><span style="COLOR: #000000">CHOOSE (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">182</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">37</span><span style="COLOR: #000000"> Bytes</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">3219</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">TABLE</span><span style="COLOR: #000000"> ACCESS (</span><span style="COLOR: #0000ff">BY</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INDEX</span><span style="COLOR: #000000"> ROWID) </span><span style="COLOR: #0000ff">OF</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">T</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000"> (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">182</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">37</span><span style="COLOR: #000000"> Bytes</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">3219</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">2</span><span style="COLOR: #000000"></span><span style="FONT-WEIGHT: bold; COLOR: #800000">1</span><span style="COLOR: #000000"></span><span style="COLOR: #0000ff">INDEX</span><span style="COLOR: #000000"> (</span><span style="COLOR: #0000ff">FULL</span><span style="COLOR: #000000"> SCAN) </span><span style="COLOR: #0000ff">OF</span><span style="COLOR: #000000"></span><span style="COLOR: #ff0000">'</span><span style="COLOR: #ff0000">INDX_T</span><span style="COLOR: #ff0000">'</span><span style="COLOR: #000000"> (NON</span><span style="COLOR: #808080">-</span><span style="COLOR: #0000ff">UNIQUE</span><span style="COLOR: #000000">) (Cost</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">144</span><span style="COLOR: #000000"> Card</span><span style="COLOR: #808080">=</span><span style="FONT-WEIGHT: bold; COLOR: #800000">37</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #0000ff">Statistics</span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #008080">--</span><span style="COLOR: #008080">--------------------------------------------------------</span><span style="COLOR: #008080">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="COLOR: #000000">
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> recursive calls
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> db block gets
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">335</span><span style="COLOR: #000000"> consistent gets
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> physical reads
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> redo size
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">4479</span><span style="COLOR: #000000"> bytes sent via SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">624</span><span style="COLOR: #000000"> bytes received via SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net </span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">13</span><span style="COLOR: #000000"> SQL</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">Net roundtrips </span><span style="COLOR: #0000ff">to</span><span style="COLOR: #808080">/</span><span style="COLOR: #0000ff">from</span><span style="COLOR: #000000"> client
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> sorts (memory)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">0</span><span style="COLOR: #000000"> sorts (</span><span style="COLOR: #0000ff">disk</span><span style="COLOR: #000000">)
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" />
<img src="http://cms.it168.com/Images/OutliningIndicators/None.gif" align="top" /></span><span style="FONT-WEIGHT: bold; COLOR: #800000">180</span><span style="COLOR: #000000"> rows processed</span></div>
</div></pre></div><p /><div id="6"> 通过添加提示（hint），我们强制Oracle使用了索引扫描（index full scan），执行了335个逻辑读，比使用全表扫描的时候少了一些。</div><p /><p /><div id="7"> 由此可见，Oracle优化器有时会做出错误的选择，因为它再“聪明”，也不如我们SQL语句编写人员更清楚表中数据的分布，在这种情况下，通过使用提示（hint），我们可以帮助Oracle优化器作出更好的选择。</div></div></div></div>

查看完整版本: [转载]Oracle中组合索引的使用详解

© 1999-2008 EvilOctal Security Team