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

<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="">为了能够管理非常大的数据量，</span><span style="font-size: 12pt;" lang="EN-US"><span style="font-family: Times New Roman;">oracle</span></span><span style="">建立了分区表</span><span style="font-size: 12pt;" lang="EN-US"><span style="font-family: Times New Roman;">(partitioned table)</span></span><span style="">和分区索引</span><span style="font-size: 12pt;" lang="EN-US"><span style="font-family: Times New Roman;">(partitioned idex)</span></span><span style="">。</span><span style="font-size: 12pt;" lang="EN-US"></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt;"><span style="">通过使用分区表，允许将一张大表的数据分布到多个表分区段，通过使用分区索引，允许将一个大索引的数据分布到多个索引分区段。使用分区特征时，不同区彼此独立，从而提高表的可用性和性能。</span><span style="font-size: 12pt;" lang="EN-US"></span></p>
<p class="MsoNormal" style=""><span style="font-size: 12pt;" lang="EN-US"><span style=""><span style="font-family: Times New Roman;">1.<span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="">建立分区表</span><span style="font-size: 12pt;" lang="EN-US"></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="">分区表是指按照特定逻辑划分大表，最终将其数据部署到几个相对较小的分区段中。执行</span><span style="font-size: 12pt;" lang="EN-US"><span style="font-family: Times New Roman;">sql</span></span><span style="">语句访问分区表时，服务器进程可以直接访问某个分区段，而不需要访问整张表的所有数据，从而降低磁盘</span><span style="font-size: 12pt;" lang="EN-US"><span style="font-family: Times New Roman;">IO</span></span><span style="">。</span><span style="font-size: 12pt;" lang="EN-US"></span></p>
<p class="MsoNormal" style=""><span style="font-size: 12pt;" lang="EN-US"><span style=""><span style="font-family: Times New Roman;">2.<span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="">范围分区</span><span style="font-size: 12pt;" lang="EN-US"></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="">按照列值将表列的数据分布到不同的分区段中。如果表的数据可以按照逻辑访问进行划分，并在不同范围内分布比较均衡，那么可以使用范围分区。</span><span style="font-size: 12pt;" lang="EN-US"></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="">建立分区表：</span><span style="font-size: 12pt;" lang="EN-US"></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><strong><span style='font-size: 12pt; color: black; font-family: "Courier New";' lang="EN-US">create</span></strong><span style='font-size: 12pt; color: black; font-family: "Courier New";' lang="EN-US"> <strong>table</strong> sales(<br>customer_id <strong>number</strong>(</span><span style='font-size: 12pt; color: #0000f0; font-family: "Courier New";' lang="EN-US">3</span><span style='font-size: 12pt; color: black; font-family: "Courier New";' lang="EN-US">),<br>sales_amount <strong>number</strong>(</span><span style='font-size: 12pt; color: #0000f0; font-family: "Courier New";' lang="EN-US">10</span><span style='font-size: 12pt; color: black; font-family: "Courier New";' lang="EN-US">,</span><span style='font-size: 12pt; color: #0000f0; font-family: "Courier New";' lang="EN-US">2</span><span style='font-size: 12pt; color: black; font-family: "Courier New";' lang="EN-US">),<br>sales_date <strong>DATE</strong><br>)<strong>partition</strong> <strong>by</strong> <strong>range</strong>(sales_date)(<br><span style=""> </span><strong>partition</strong> p1 <strong>values</strong> <strong>less</strong> <strong>than</strong>(</span><span style='font-size: 12pt; color: #0000f0; font-family: "Courier New";' lang="EN-US">'01-APR-2010'</span><span style='font-size: 12pt; color: black; font-family: "Courier New";' lang="EN-US">)<br><span style=""> </span><strong>tablespace</strong> partition_tbs01,<br><span style=""> </span><strong>partition</strong> p2 <strong>values</strong> <strong>less</strong> <strong>than</strong>(</span><span style='font-size: 12pt; color: #0000f0; font-family: "Courier New";' lang="EN-US">'01-JUL-2010'</span><span style='font-size: 12pt; color: black; font-family: "Courier New";' lang="EN-US">)<br><span style=""> </span><strong>tablespace</strong> partition_tbs02,<br><span style=""> </span><strong>partition</strong> p3 <strong>values</strong> <strong>less</strong> <strong>than</strong>(</span><span style='font-size: 12pt; color: #0000f0; font-family: "Courier New";' lang="EN-US">'01-OCT-2010'</span><span style='font-size: 12pt; color: black; font-family: "Courier New";' lang="EN-US">)<br><span style=""> </span><strong>tablespace</strong> partition_tbs03,<br><span style=""> </span><strong>partition</strong> p4 <strong>values</strong> <strong>less</strong> <strong>than</strong>(</span><span style='font-size: 12pt; color: #0000f0; font-family: "Courier New";' lang="EN-US">'01-JAN-2011'</span><span style='font-size: 12pt; color: black; font-family: "Courier New";' lang="EN-US">)<br><span style=""> </span><strong>tablespace</strong> partition_tbs04<br>)</span></p>
<p class="MsoNormal" style=""><span style="font-size: 12pt;" lang="EN-US"><span style=""><span style="font-family: Times New Roman;">3.<span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="">在范围分区表上插入数据</span><span style="font-size: 12pt;" lang="EN-US"></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="">在使用</span><span style="font-size: 12pt;" lang="EN-US"><span style="font-family: Times New Roman;">INSERT</span></span><span style="">语句为范围分区表插入数据时，必须为分区列提供数据，并且分区列的数据必须符合相应的分区范围。当在分区表</span><span style="font-size: 12pt;" lang="EN-US"><span style="font-family: Times New Roman;">SALES</span></span><span style="">上执行</span><span style="font-size: 12pt;" lang="EN-US"><span style="font-family: Times New Roman;">INSERT</span></span><span style="">操作时，</span><span style="font-size: 12pt;" lang="EN-US"><span style="font-family: Times New Roman;">Oracle</span></span><span style="">会根据</span><span style="font-size: 12pt;" lang="EN-US"><span style="font-family: Times New Roman;">sales_date</span></span><span style="">值的范围在相应分区上插入数据。</span><span style="font-size: 12pt;" lang="EN-US"></span></p>
<p class="MsoNormal" style=""><span style="font-size: 12pt;" lang="EN-US"><span style=""><span style="font-family: Times New Roman;">4.<span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="">查询表的所有语句，与普通表的查询没有任何差别。</span><span style="font-size: 12pt;" lang="EN-US"></span></p>
<p class="MsoNormal" style=""><span style="font-size: 12pt;" lang="EN-US"><span style=""><span style="font-family: Times New Roman;">5.<span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="">查询某个分区的数据</span><span style="font-size: 12pt;" lang="EN-US"></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="">建立分区表后，通过在表明后指定分区名，可以显示分区的所有数据。</span><span style="font-size: 12pt;" lang="EN-US"></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: 12pt;" lang="EN-US"><span style="font-family: Times New Roman;">SELECT * FROM sales PARTITION(p1);</span></span></p>