oracle10g系统管理之应对大表（三）

<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="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></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 gbl_sales_date ON sales(sales_date) GLOBAL;</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></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 lcl_sales_date ON sales(sales_date) LOCAL;</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;">Oracle</span></span><span style="">会将分区表的信息存放到数据字典中</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;">SELECT table_name,partitioning_type,subpartitioning_type</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;">FROM dba_part_tables </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;">WHERE owner=’SCOTT’;</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="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">SELECT index_name,partitioning_type,subpartitioning_type</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;">FROM dba_part_indexes</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;">WHERE owner=’SCOTT’ AND type_name=’SALES_REGION’;</span></span></p>