Oracel索引创建之后多久生效

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">1.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">索引</span><span lang="EN-US"><span style=""><span style="font-family: Times New Roman;"> </span></span></span><span style="">索引是用于加速数据存取的数据库对象。合理地使用索引可以大大降低</span><span lang="EN-US"><span style="font-family: Times New Roman;">I/O</span></span><span style="">次数从而提高数据访问性能。</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">2.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">按照存储方式分类：</span></span></p> 

<p class="MsoNormal" style=""><span style="font-family: Times New Roman;"><span style="" lang="EN-US"><span style=""><span style="font-size: small;">1)</span><span style='font: 7pt "Times New Roman";'> </span></span></span><span lang="EN-US"><span style="font-size: small;">B*-</span></span></span><span style=""><span style="font-size: small;">树索引</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">2)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">反向索引</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">3)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">位图索引</span></span></p> 

<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style=""><span style="font-size: small;">按照索引列的个数：</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">1)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">单列索引</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">2)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">复合索引</span></span></p> 

<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style=""><span style="font-size: small;">按照索引列值的唯一性</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">1)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">唯一索引</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">2)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">非唯一索引</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">3.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">单列索引和复合索引：</span></span></p> 

<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style=""><span style="font-size: small;">单列索引是基于单个列所建立的索引；复合索引是基于两列或多列所建立的索引。可以在同一张表上建立多个索引，但是要求列的组合必须不同，列的顺序不同也可以。</span></span></p> 

<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE INDEX emp_idx1 ON emp(ename,job);</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">4.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">唯一索引和非唯一索引</span></span></p> 

<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span style="">唯一索引是索引列值不能重复的索引；非唯一索引是索引列值可以重复的索引。无论是唯一索引还是非唯一索引，索引列都允许</span><span lang="EN-US"><span style="font-family: Times New Roman;">null</span></span><span style="">。当定义主键约束和唯一约束时，</span><span lang="EN-US"><span style="font-family: Times New Roman;">Oracle</span></span><span style="">都会自动在相应的约束列上建立唯一索引。</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">5.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">索引管理的指导方针</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">1)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">使用</span><span lang="EN-US"><span style="font-family: Times New Roman;">Oracle</span></span><span style="">工具</span><span lang="EN-US"><span style="font-family: Times New Roman;">SQL*Loader</span></span><span style="">、</span><span lang="EN-US"><span style="font-family: Times New Roman;">IMPDP</span></span><span style="">和</span><span lang="EN-US"><span style="font-family: Times New Roman;">IMP</span></span><span style="">装载数据时，应该先导入数据，后建立索引。</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">2)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">索引正确的表和列</span><span lang="EN-US"><span style=""><span style="font-family: Times New Roman;"> </span></span></span><span style="">建立和规划索引时，必须选择合适的表和列。如果选择的表和列不合适，不仅无法提高查询速度，反而会极大地降低</span><span lang="EN-US"><span style="font-family: Times New Roman;">DML</span></span><span style="">操作的速度。建立索引的指导方针如下：</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">A.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">索引经过建立在</span><span lang="EN-US"><span style="font-family: Times New Roman;">WHERE</span></span><span style="">子句经常应用的表列上。如果在大表上频繁使用某列或者几列作为条件执行索引操作，并且检索操作行数低于总行数的</span><span lang="EN-US"><span style="font-family: Times New Roman;">15%</span></span><span style="">，那么应该考虑在这些列上建立索引。</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">B.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">为了提高多表连接的性能，应该在连接列上建立索引。</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">C.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">不要在小表上建立索引。</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">D.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">如果经常基于某列或某几列执行排序操作，那么通过这些列上建立索引，可以加快数据排序的速度。</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">3)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">限制表的索引个数</span><span lang="EN-US"><span style=""><span style="font-family: Times New Roman;"> </span></span></span><span style="">索引主要用于加速查询速度，但会降低</span><span lang="EN-US"><span style="font-family: Times New Roman;">DML</span></span><span style="">操作的速度。索引越多，</span><span lang="EN-US"><span style="font-family: Times New Roman;">DML</span></span><span style="">操作的速度越慢，尤其会极大影响</span><span lang="EN-US"><span style="font-family: Times New Roman;">INSERT</span></span><span style="">操作和</span><span lang="EN-US"><span style="font-family: Times New Roman;">DELETE</span></span><span style="">操作的速度。因此规划索引时，必须仔细权衡查询和</span><span lang="EN-US"><span style="font-family: Times New Roman;">DML</span></span><span style="">的需求。</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">4)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">使用</span><span lang="EN-US"><span style="font-family: Times New Roman;">NOLOGING</span></span><span style="">选项建立索引</span></span></p> 

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">5)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">建立索引时，既可以将索引和基表放在相同的表空间中，也可以放在不同的表空间中。将表和索引部署到相同表空间中，可以简化表空间的管理。将表和索引部署到不同表空间，可以提高性能。</span></span></p>