[Oralce] 分区表和索引

古城微笑少年丶 2022-07-10 23:59 234阅读 0赞

Oracle分区表的三种常用的类型为range分区， hash分区, 和list分区。  间隔分区（interval）、外键分区（reference）使用较少。

以下为这三种分区表的创建语法，及它们索引的创建方法。

**创建一个标准的range分区表：**

<table style="table-layout: fixed; width: 95%; border: #cccccc 1px dotted;"> 
 <tbody> 
  <tr> 
   <td style="WORD-WRAP: break-word">　　JSSWEB&gt;&nbsp;create&nbsp;table&nbsp;t_partition_range&nbsp;(id&nbsp;number,name&nbsp;varchar2(50)) <br>　　2&nbsp;partition&nbsp;by&nbsp;range(id)( <br>　　3&nbsp;partition&nbsp;t_range_p1&nbsp;values&nbsp;less&nbsp;than&nbsp;(10)&nbsp;tablespace&nbsp;tbspart01, <br>　　4&nbsp;partition&nbsp;t_range_p2&nbsp;values&nbsp;less&nbsp;than&nbsp;(20)&nbsp;tablespace&nbsp;tbspart02, <br>　　5&nbsp;partition&nbsp;t_range_p3&nbsp;values&nbsp;less&nbsp;than&nbsp;(30)&nbsp;tablespace&nbsp;tbspart03, <br>　　6&nbsp;partition&nbsp;t_range_pmax&nbsp;values&nbsp;less&nbsp;than&nbsp;(maxvalue)&nbsp;tablespace&nbsp;tbspart04 <br>　　7&nbsp;);</td> 
  </tr> 
 </tbody> 
</table>

**     创建global索引range分区：(默认即为global)**

**创建hash分区表**

(索引创建语法可参见range分区) **HASH索引在Where子句中确切匹配(需要指定一个确定的值而不是一个值范围)的情况下非常有用。**

**创建list分区表**

<table style="table-layout: fixed; width: 95%; border: #cccccc 1px dotted;"> 
 <tbody> 
  <tr> 
   <td style="WORD-WRAP: break-word">　　JSSWEB&gt;&nbsp;create table t_partition_list (id number,name varchar2(50)) <p>　　&nbsp;&nbsp;&nbsp;&nbsp;partition by list(id)(</p> <p>　　&nbsp;&nbsp;&nbsp; partition t_list_p1 values (1,2,3,4,5,6,7,8,9) tablespace tbspart01,</p> <p>　　&nbsp;&nbsp;&nbsp; partition t_list_p2 values (10,11,12,13,14,15,16,17,18,19) tablespace tbspart02,</p> <p>　　&nbsp;&nbsp;&nbsp; partition t_list_p3 values (20,21,22,23,24,25,26,27,28,29) tablespace tbspart03,</p> <p>　　&nbsp;&nbsp;&nbsp; partition t_list_pd values (default) tablespace tbspart04);</p> </td> 
  </tr> 
 </tbody> 
</table>

(索引创建语法可参见range分区)

**分区表和索引的维护**

<table> 
 <tbody> 
  <tr> 
   <td>分区表</td> 
   <td>Range</td> 
   <td>List</td> 
   <td>Hash</td> 
   <td>Range-Hash</td> 
   <td>Range-List</td> 
   <td>是否带来IO操作</td> 
  </tr> 
  <tr> 
   <td>增加分区(addpartition)</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>除hash类型外，均不变带来大量IO</td> 
  </tr> 
  <tr> 
   <td>收缩分区(coalescepartitions)</td> 
   <td>/</td> 
   <td>/</td> 
   <td>支持</td> 
   <td>分区：/子分区：支持</td> 
   <td>/</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>删除分区(droppartition)</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>/</td> 
   <td>分区：支持子分区：/</td> 
   <td>支持</td> 
   <td>无</td> 
  </tr> 
  <tr> 
   <td>交换分区(exchangepartition)</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>无</td> 
  </tr> 
  <tr> 
   <td>合并分区(mergepartition)</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>/</td> 
   <td>分区：支持子分区：/</td> 
   <td>支持</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>修改默认属性(modifydefaultattributes)</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>无</td> 
  </tr> 
  <tr> 
   <td>修改分区当前属性(modifypartition)</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>无</td> 
  </tr> 
  <tr> 
   <td>List分区增加值(modifypartitionaddvalues)</td> 
   <td>/</td> 
   <td>支持</td> 
   <td>/</td> 
   <td>/</td> 
   <td>分区：/子分区：支持</td> 
   <td>无</td> 
  </tr> 
  <tr> 
   <td>List分区删除值(modifypartitiondropvalues)</td> 
   <td>/</td> 
   <td>支持</td> 
   <td>/</td> 
   <td>/</td> 
   <td>分区：/子分区：支持</td> 
   <td>单纯删除操作无，但可能为了实现成功删除，之前的准备操作会带来一定量的IO</td> 
  </tr> 
  <tr> 
   <td>修改子分区模板(setsubpartitiontemplate)</td> 
   <td>/</td> 
   <td>/</td> 
   <td>/</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>无</td> 
  </tr> 
  <tr> 
   <td>移动分区(movepartition)</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>分区：支持子分区：/</td> 
   <td>分区：支持子分区：/</td> 
   <td>有</td> 
  </tr> 
  <tr> 
   <td>重命名分区(renamepartition)</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>无</td> 
  </tr> 
  <tr> 
   <td>分隔分区(splitpartition)</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>/</td> 
   <td>分区：支持子分区：/</td> 
   <td>支持</td> 
   <td>有</td> 
  </tr> 
  <tr> 
   <td>截断分区(truncatepartition)</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>无</td> 
  </tr> 
 </tbody> 
</table>

注：上述IO列的评估建立在假设分区中均存在一定量数据，并忽略修改数据字典可能触发的IO，忽略造成的索引的重编译带来的IO。

分区索引的操作也有一张表黑黑，如下

<table> 
 <tbody> 
  <tr> 
   <td>分区索引</td> 
   <td>索引类型</td> 
   <td>Range</td> 
   <td>List</td> 
   <td>Hash</td> 
   <td>组合分区</td> 
   <td>是否带来IO操作</td> 
  </tr> 
  <tr> 
   <td> <p>　　增加分区</p> <p>　　(addpartition)</p> </td> 
   <td>全局</td> 
   <td>/</td> 
   <td>/</td> 
   <td>支持</td> 
   <td>/</td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>本地</td> 
   <td>/</td> 
   <td>/</td> 
   <td>/</td> 
   <td>/</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td> <p>　　删除分区</p> <p>　　(droppartition)</p> </td> 
   <td>全局</td> 
   <td>支持</td> 
   <td>/</td> 
   <td>/</td> 
   <td>/</td> 
   <td>无</td> 
  </tr> 
  <tr> 
   <td>本地</td> 
   <td>/</td> 
   <td>/</td> 
   <td>/</td> 
   <td>/</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td> <p>　　修改默认属性</p> <p>　　(modifydefaultattributes)</p> </td> 
   <td>全局</td> 
   <td>支持</td> 
   <td>/</td> 
   <td>/</td> 
   <td>/</td> 
   <td>无</td> 
  </tr> 
  <tr> 
   <td>本地</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>无</td> 
  </tr> 
  <tr> 
   <td> <p>　　修改分区当前属性</p> <p>　　(modifypartition)</p> </td> 
   <td>全局</td> 
   <td>支持</td> 
   <td>/</td> 
   <td>/</td> 
   <td>/</td> 
   <td>无</td> 
  </tr> 
  <tr> 
   <td>本地</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>无</td> 
  </tr> 
  <tr> 
   <td> <p>　　重编译分区</p> <p>　　(rebuildpartition)</p> </td> 
   <td>全局</td> 
   <td>支持</td> 
   <td>/</td> 
   <td>/</td> 
   <td>/</td> 
   <td>有</td> 
  </tr> 
  <tr> 
   <td>本地</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>有</td> 
  </tr> 
  <tr> 
   <td> <p>　　重命名分区</p> <p>　　(renamepartition)</p> </td> 
   <td>全局</td> 
   <td>支持</td> 
   <td>/</td> 
   <td>/</td> 
   <td>/</td> 
   <td>无</td> 
  </tr> 
  <tr> 
   <td>本地</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>支持</td> 
   <td>无</td> 
  </tr> 
  <tr> 
   <td> <p>　　分隔分区</p> <p>　　(splitpartition)</p> </td> 
   <td>全局</td> 
   <td>支持</td> 
   <td>/</td> 
   <td>/</td> 
   <td>/</td> 
   <td>有</td> 
  </tr> 
  <tr> 
   <td>本地</td> 
   <td>/</td> 
   <td>/</td> 
   <td>/</td> 
   <td>/</td> 
  </tr> 
 </tbody> 
</table>

另外local索引前头我们多次提到了，其维护会在oracle操作表分区的时候自动进行，需要注意的是global索引，当global索引所在表执行alter table涉及下列操作时，会导至该索引失效：

Ø ADD PARTITION | SUBPARTITION

Ø COALESCE PARTITION | SUBPARTITION

Ø DROP PARTITION | SUBPARTITION

Ø EXCHANGE PARTITION | SUBPARTITION

Ø MERGE PARTITION | SUBPARTITION

Ø MOVE PARTITION | SUBPARTITION

Ø SPLIT PARTITION | SUBPARTITION

Ø TRUNCATE PARTITION | SUBPARTITION

因此，建议用户在执行上述操作sql语句后附加update indexes子句，oracle即会自动维护全局索引，当然，需要注意这中间有一个平衡，你要平衡操作ddl的时间和重建索引哪个时间更少，以决定是否需要附加update indexes子句。

<table style="table-layout: fixed; width: 95%; border: #cccccc 1px dotted;"> 
 <tbody> 
  <tr> 
   <td style="WORD-WRAP: break-word">　　JSSWEB&gt;&nbsp;create&nbsp;index&nbsp;idx_parti_range_id&nbsp;on&nbsp;t_partition_range(id) <br>　　2&nbsp;global&nbsp;partition&nbsp;by&nbsp;range(id)( <br>　　3&nbsp;partition&nbsp;i_range_p1&nbsp;values&nbsp;less&nbsp;than&nbsp;(10)&nbsp;tablespace&nbsp;tbspart01, <br>　　4&nbsp;partition&nbsp;i_range_p2&nbsp;values&nbsp;less&nbsp;than&nbsp;(40)&nbsp;tablespace&nbsp;tbspart02, <br>　　5&nbsp;partition&nbsp;i_range_pmax&nbsp;values&nbsp;less&nbsp;than&nbsp;(maxvalue)&nbsp;tablespace&nbsp;tbspart03);</td> 
  </tr> 
 </tbody> 
</table>

**    创建local索引range分区：(分区信息完全依赖于所在分区表的定义)**

<table style="table-layout: fixed; width: 95%; border: #cccccc 1px dotted;"> 
 <tbody> 
  <tr> 
   <td style="WORD-WRAP: break-word"> <p>　　JSSWEB&gt;&nbsp;create index IDX_PARTI_RANGE_ID on T_PARTITION_RANGE(id) local;</p> <p>或者：</p> <p>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;JSSWEB&gt;&nbsp;create&nbsp;index&nbsp;IDX_PART_RANGE_ID&nbsp;ON&nbsp;T_PARTITION_RANGE(id)&nbsp;local&nbsp;( <br>　　2&nbsp;partition&nbsp;i_range_p1&nbsp;tablespace&nbsp;tbspart01, <br>　　3&nbsp;partition&nbsp;i_range_p2&nbsp;tablespace&nbsp;tbspart01, <br>　　4&nbsp;partition&nbsp;i_range_p3&nbsp;tablespace&nbsp;tbspart02, <br>　　5&nbsp;partition&nbsp;i_range_pmax&nbsp;tablespace&nbsp;tbspart02 <br>　　6&nbsp;);</p> </td> 
  </tr> 
 </tbody> 
</table>