深入学习Oracle分区表及分区索引

关于分区表和分区索引(About Partitioned Tables and Indexes)对于10gR2而言，基本上可以分成几类：

• Range(范围)分区

• Hash(哈希)分区

• List(列表)分区

• 以及组合分区：Range-Hash,Range-List。

对于表而言(常规意义上的堆组织表)，上述分区形式都可以应用(甚至可以对某个分区指定compress属性)，只不过分区依赖列不能是lob,long之类数据类型，每个表的分区或子分区数的总数不能超过1023个。

对于索引组织表，只能够支持普通分区方式，不支持组合分区，常规表的限制对于索引组织表同样有效，除此之外呢，还有一些其实的限制，比如要求索引组织表的分区依赖列必须是主键才可以等。

注：本篇所有示例仅针对常规表，即堆组织表!

对于索引，需要区分创建的是全局索引，或本地索引：

• 全局索引(global index)：即可以分区，也可以不分区。即可以建range分区，也可以建hash分区，即可建于分区表，又可创建于非分区表上，就是说，全局索引是完全独立的，因此它也需要我们更多的维护操作。

• 本地索引(local index)：其分区形式与表的分区完全相同，依赖列相同，存储属性也相同。对于本地索引，其索引分区的维护自动进行，就是说你add/drop/split/truncate表的分区时，本地索引会自动维护其索引分区。

Oracle建议如果单个表超过2G就最好对其进行分区，对于大表创建分区的好处是显而易见的，这里不多论述why，而将重点放在when以及how。

ORACLE对于分区表方式其实就是将表分段存储，一般普通表格是一个段存 储，而分区表会分成多个段，所以查找数据过程都是先定位根据查询条件定位分区范围，即数据在那个分区或那几个内部，然后在分区内部去查找数据，一个分区一 般保证四十多万条数据就比较正常了，但是分区表并非乱建立，而其维护性也相对较为复杂一点，而索引的创建也是有点讲究的，这些以下尽量阐述详细即可。

• range分区方式，也算是最常用的分区方式，其通过某字段或几个字段的组合的值，从小到大，按照指定的范围说明进行分区，我们在INSERT数据的时候就会存储到指定的分区中。

• List分区方式，一般是在range基础上做的二级分区较多，是一种列举方式进行分区，一般讲某些地区、状态或指定规则的编码等进行划分。

• Hash分区方式，它没有固定的规则，由ORACLE管理，只需要将值INSERT进去，ORACLE会自动去根据一套HASH算法去划分分区，只需要告诉ORACLE要分几个区即可。

WHEN

一、When使用Range分区

Range分区呢是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中，比如按照 时间划分，2008年1季度的数据放到a分区，08年2季度的数据放到b分区，因此在创建的时候呢，需要你指定基于的列，以及分区的范围值，如果某些记录 暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中，并且支持指定多列做为依赖列，后面在 讲how的时候会详细谈到。

二、When使用Hash分区

通常呢，对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你 指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖 列。

三、When使用List分区

List分区与range分区和hash分区都有类似之处，该分区与range分区类似的是也需要你指定列的值，但这又不同与range分区的 范围式列值---其分区值必须明确指定，也不同与hash分区---通过明确指定分区值，你能控制记录存储在哪个分区。它的分区列只能有一个，而不能像 range或者hash分区那样同时指定多个列做为分区依赖列，不过呢，它的单个分区对应值可以是多个。

你在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。

四、When使用组合分区

如果某表按照某列分区之后，仍然较大，或者是一些其它的需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式。

组合分区呢在10g中有两种：range-hash，range-list。注意顺序哟，根分区只能是range分区，子分区可以是hash分区或list分区。

提示：11g在组合分区功能这块有所增强，又推出了range-range,list-range,list-list,list-hash， 这就相当于除hash外三种分区方式的笛卡尔形式都有了。为什么会没有hash做为根分区的组合分区形式呢，再仔细回味一下第二点，你一定能够想明 白~~。

如何创建

如果想对某个表做分区，必须在创建表时就指定分区，我们可以对一个包含分区的表中的分区做修改，但不能直接将一个未分区的表修改成分区表(起码在10g是不行的，当然你可能会说，可以通过在线重定义的方式，但是这不是直接哟，这也是借助临时表间接实现的)。

创建表或索引的语法就不说了，大家肯定比我还熟悉，而想在建表(索引)同时指定分区也非常容易，只需要把创建分区的子句放到";"前就行啦，同 时需要注意表的row movement属性，它用来控制是否允许修改列值所造成的记录移动至其它分区存储，有enable|disable两种状态，默认是disable row movement，当disable时，如果记录要被更新至其它分区，则更新语句会报错。

下面分别演示不同分区方式的表和索引的创建：

创建range分区

语法如下，需要我们指定的有：

• column:分区依赖列(如果是多个，以逗号分隔);

• partition:分区名称;

• values less than:后跟分区范围值(如果依赖列有多个，范围对应值也应是多个，中间以逗号分隔);

• tablespace_clause:分区的存储属性，例如所在表空间等属性(可为空)，默认继承基表所在表空间的属性。

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

表已创建。

要查询创建分区的信息，可以通过查询user_part_tables,user_tab_partitions两个数据字典(索引分区、组织分区等信息也有对应的数据字典，后续示例会逐步提及)。

user_part_tables：记录分区的表的信息;

user_tab_partitions：记录表的分区的信息。

例如：

JSSWEB> select table_name,partitioning_type,partition_count

     From user_part_tables where table_name='T_PARTITION_RANGE';

JSSWEB> select partition_name,high_value,tablespace_name

       from user_tab_partitions where table_name='T_PARTITION_RANGE'

       order by partition_position;

②　创建global索引range分区：

索引已创建。

由上例可以看出，创建global索引的分区与创建表的分区语句格式完全相同，而且其分区形式与索引所在表的分区形式没有关联关系。

注意：我们这里借助上面的表t_partition_range来演示创建range分区的global索引，并不表示range分区的表，只能创建range分区的global索引，只要你想，也可以为其创建hash分区的global索引。

查询索引的分区信息可以通过user_part_indexes、user_ind_partitions两个数据字典：

JSSWEB> select index_name, partitioning_type, partition_count

2 From user_part_indexes

3 where index_name = 'IDX_PARTI_RANGE_ID';

③　Local分区索引的创建最简单，例如：

仍然借助t_partition_range表来创建索引

--首先删除之前创建的global索引

JSSWEB> drop index IDX_PARTI_RANGE_ID;

索引已删除。

JSSWEB> create index IDX_PARTI_RANGE_ID on T_PARTITION_RANGE(id) local;

索引已创建。

查询相关数据字典：

可以看出，local索引的分区完全继承表的分区的属性，包括分区类型，分区的范围值即不需指定也不能更改，这就是前面说的：local索引的分区维护完全依赖于其索引所在表。

不过呢分区名称，以及分区所在表空间等信息是可以自定义的，例如：

索引已创建。

创建hash分区

语法如下：

语法看起来比range复杂，其实使用起来比range更简单，这里需要我们指定的有：

• column:分区依赖列(支持多个，中间以逗号分隔);

• partition:指定分区，有两种方式：

     --直接指定分区名，分区所在表空间等信息

     --只指定分区数量，和可供使用的表空间。

创建hash分区

表已创建。

要实现同样效果，你还可以这样：

表已创建。

这就是上面说的，直接指定分区数量和可供使用的表空间。

提示：这里分区数量和可供使用的表空间数量之间没有直接对应关系。分区数并不一定要等于表空间数。

要查询表的分区信息，仍然是通过user_part_tables,user_tab_partitions两个数据字典，这里不再举例。

①　Global索引hash分区

Hash分区索引的子句与hash分区表的创建子句完全相同，例如：

索引已创建。

查询索引的分区信息也仍是通过user_part_indexes、user_ind_partitions两个数据字典，不再举例。

②　创建Local索引

在前面学习range分区时，我们已经对Local索引的特性做了非常清晰的概述，因此这里也不再举例，如有疑问，建议再仔细复习range分区的相关示例，如果还有疑问，当面问我好了:)

综上：

Ø 对于global索引分区而言，在10g中只能支持range分区和hash分区，因此后续示例中不会再提及。

Ø 对于local索引分区而言，其分区形式完全依赖于索引所在表的分区形式，不管从创建语法还是理解难度均无技术含量，因此后续也不再提供示例。

Ø 注意，在创建索引时如果不显式指定global或local，则默认是global。

Ø 注意，在创建global索引时如果不显式指定分区子句，则默认不分区(废话)。

分区应用

一般一张表超过2G的大小，ORACLE是推荐使用分区表的，分区一般都需要 创建索引，说到分区索引，就可以分为：全局索引、分区索引，即：global索引和local索引，前者为默认情况下在分区表上创建索引时的索引方式，并 不对索引进行分区（索引也是表结构，索引大了也需要分区，关于索引以后专门写点）而全局索引可修饰为分区索引，但是和local索引有所区别，前者的分区 方式完全按照自定义方式去创建，和表结构完全无关，所以对于分区表的全局索引有以下两幅网上常用的图解：

1、对于分区表的不分区索引（这个有点绕，不过就是表分区，但其索引不分区）：

创建语法(直接创建即可)：

CREATE INDEX <index_name> ON <partition_table_name>(<column_name>);

2、对于分区表的分区索引:

创建语法为：

CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1)

  GLOBAL PARTITION BY RANGE(COL1)(

         PARTITION IDX_P1 values less than (1000000),

         PARTITION IDX_P2 values less than (2000000),

         PARTITION IDX_P3 values less than (MAXVALUE)

  );

3、LOCAL索引结构：

创建语法为：

CREATE INDEX INX_TAB_PARTITION_COL1 ON TABLE_PARTITION(COL1) LOCAL;

也可按照分区表的的分区结构给与一一定义，索引的分区将得到重命名。

分区上的位图索引只能为LOCAL索引，不能为GLOBAL全局索引。

4、对比索引方式：

一般使用LOCAL索引较为方便，而且维护代价较低，并且LOCAL索引是在分区的基础上去创建索引，类似于在一个子表内部去创建索引，这样开销主要是区 分分区上，很规范的管理起来，在OLAP系统中应用很广泛；而相对的GLOBAL索引是全局类型的索引，根据实际情况可以调整分区的类别，而并非按照分区 结构一一定义，相对维护代价较高一些，在OLTP环境用得相对较多，这里所谓OLTP和OLAP也是相对的，不是特殊的项目，没有绝对的划分概念，在应用 过程中依据实际情况而定，来提高整体的运行性能。

4、常用视图：

1.查询当前用户下有哪些是分区表：

SELECT * FROM USER_PART_TABLES;

2.查询当前用户下有哪些分区索引：

SELECT * FROM USER_PART_INDEXES;

3.查询当前用户下分区索引的分区信息：

SELECT * FROM USER_IND_PARTITIONS T

WHERE T.INDEX_NAME=?

4.查询当前用户下分区表的分区信息：

SELECT * FROM USER_TAB_PARTITIONS T

WHERE T.TABLE_NAME=?;

5.查询某分区下的数据量：

SELECT COUNT(*) FROM TABLE_PARTITION PARTITION(TAB_PARTOTION_01);

6.查询索引、表上在那些列上创建了分区：

SELECT * FROM USER_PART_KEY_COLUMNS;

7.查询某用户下二级分区的信息（只有创建了二级分区才有数据）：

SELECT * FROM USER_TAB_SUBPARTITIONS;

维护操作

1、删除分区

 ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03;

如果是全局索引，因为全局索引的分区结构和表可以不一致，若不一致的情况下，会导致整个全局索引失效，在删除分区的时候，语句修改为：

ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_03 UPDATE GLOBAL INDEXES;

2、分区合并（从中间删除掉一个分区，或者两个分区需要合并后减少分区数量）

合并分区和删除中间的RANGE有点像，但是合并分区是不会删除数据的，对于LIST、HASH分区也是和RANGE分区不一样的，其语法为：

ALTER TABLE TABLE_PARTITION MERGE PARTITIONS    TAB_PARTOTION_01,TAB_PARTOTION_02 INTO PARTITION MERGED_PARTITION;

3、分隔分区（一般分区从扩展分区从分隔）

ALTER TABLE TABLE_PARTITION SPLIT PARTITION TAB_PARTOTION_OTHERE AT(2500000)

INTO (PARTITION TAB_PARTOTION_05,PARTITION TAB_PARTOTION_OTHERE);

4、创建新的分区（分区数据若不能提供范围，则插入时会报错，需要增加分区来扩大范围）

一般有扩展分区的是都是用分隔的方式，若上述创建表时没有创建TAB_PARTOTION_OTHER分区时，在插入数据较大时（按照上述建立规则，超过1800000就应该创建新的分区来存储），就可以创建新的分区，如：

为了试验，我们将扩展分区先删除掉再创建新的分区（因为ORACLE要求，分区的数据不允许重叠，即按照分区字段同样的数据不能同时存储在不同的分区中）：

ALTER TABLE TABLE_PARTITION DROP PARTITION TAB_PARTOTION_OTHER;

ALTER TABLE TABLE_PARTITION ADD PARTITION TAB_PARTOTION_06 VALUES LESS THAN(2500000);

在分区下创建新的子分区大致如下（RANGE分区，若为LIST或HASH分区，将创建方式修改为对应的方式即可）：

ALTER TABLE <table_name> MODIFY PARTITION <partition_name> ADD SUBPARTITION <user_define_subpartition_name> VALUES LESS THAN(....);

5、修改分区名称（修改相关的属性信息）

ALTER TABLE TABLE_PARTITION RENAME PARTITION MERGED_PARTITION TO MERGED_PARTITION02;

6、交换分区（快速交换数据，其实是交换段名称指针）

首先创建一个交换表，和原表结构相同，如果有数据，必须符合所交换对应分区的条件：

CREATE TABLE TABLE_PARTITION_2

AS SELECT * FROM TABLE_PARTITION WHERE 1=2;

然后将第一个分区的数据交换出去：

ALTER TABLE TABLE_PARTITION EXCHANGE PARTITION TAB_PARTOTION_01

WITH TABLE TABLE_PARTITION_2 INCLUDING INDEXES;

此时会发现第一个分区的数据和表TABLE_PARTITION_2做了瞬间交换，比TRUNCATE还要快，因为这个过程没有进行数据转存，只是段名称的修改过程，和实际的数据量没有关系。

如果是子分区也可以与外部的表进行交换，只需要将关键字修改为：SUBPARTITION 即可。

7、清空分区数据

ALTER TABLE <table_name> TRUNCATE PARTITION <partition_name>;

ALTER TABLE <table_name> TRUNCATE subpartition <subpartition_name>;

磁盘碎片压缩

对分区表的某分区进行磁盘压缩，当对分区内部数据进行了大量的UPDATE、DELETE操作后，一定时间需要进行磁盘压缩，否则在查询时，若通过FULL SCAN扫描数据，将会把空块也会扫描到，对表进行磁盘压缩需要进行行迁移操作，所以首先需要操作：

ALTER TABLE <table_name> ENABLE ROW MOVEMENT ;

对分区表的某分区压缩语法为：

ALTER TABLE <table_name>

modify partition <partition_name> shrink space;

对普通表压缩：

ALTER TABLE <table_name> shrink space;

对于索引也需要进行压缩，索引也是表：

ALTER INDEX <index_name> shrink space;

分区表重新分析以及索引重新分析

对表进行压缩后，需要对表和索引进行重新分析，对表进行重新分析，一般有两种方式：

在ORACLE 10G以前，使用：

  BEGIN

     dbms_stats.gather_table_stats(USER,UPPER('<table_name>'));

  END;

ORACLE 10G后，可以使用：

ANALYZE TABLE <table_name> COMPUTE STATISTICS;

索引重新分析，将上述两种方式分别修改一下，如第一种可以使用：gather_index_stats，而第二种修改为：ANALYZE INDEX即可，不过一般比较常用的是重新编译：

对于分区表并进行了索引分区的情况，需要对每个分区的索引进行重新编译,这里以LOCAL索引为例子（其每个索引的分区和表分区结构相同，默认分区名称和表分区名称相同）：

ALTER INDEX <index_name> REBUILD PARTITION <partition_name>;

对于全局索引，根据全局索引锁定义的分区名称修改即可，若没有分区，和普通单表索引重新编译方式相同：

 ALTER INDEX <index_name> REBUILD; 

1、关联对象重新编译

上述对表、索引进行重新编译，尤其对表进行了压缩后会产生行迁移，这个过程可能会导致一些视图、过程对象的失效，此时要将其重新编译一次。

2、扩展：HASH分区中，如果创建了新的分区，可以将其进行重新HASH分布：

ALTER TABLE <table_name> COALESCA PARTITION

回归总结

何时建分区，分区类别，索引，如何对应SQL

1、创建时机

上述已经说明，2G以上的表，ORACLE推荐创建分区。

分区的方式根据实际情况而定，才能提高整体性能。

分区的字段一定要是经常用以提取数据的字段，否则会在提取过程中导致遍历多个分区，这样比没有分区还要慢。

分区字段要选择合适，数据较为均匀分布到各个分区，不要太多也不要太少，而且根据分区字段可以很快定位到分区范围。

一般情况下，尽量然业务操作在同一个分区内部完成。

2、分区类别

分区主要有RANGE、LIST、HASH；

RANGE通过值的范围分区，也是最常用的分区，这种分区注意在一种变长数字字符串中，很多人会导致认为是数字类型，而按照数字区分区，这样会分布十分不均匀的现象发生。

LIST是列举方式进行分区，一般作为二级分区而存在（当然也可以自己分区，ORACLE 11G后在分区上也可以作为主分区而存在），在RANGE基础上，若数据需要继续分区，并且在RANGE基础上数据量较为固定，只是较大，可以按照一定规则进一步分区。

HASH只指定分区个数，分区细节由ORACLE完成，增加HASH分区可以重新分布数据。

注意：分区字段不能使用函数转换后在分区，如，将某数字字符串字段，先TO_NUMER(COL_NAME)后分区。

3、索引类别

大致分：GLOBAL索引和LOCAL索引，钱和可以分：GLOBAL不分区索引，和GLOBAL分区索引。

GLOBAL不分区索引一般不太推荐，因为是用一颗大的索引树来映射一个表，这个过程，这样速度不见得比不分区快。

GLOBAL分区索引，查找数据若通过要通过索引，是先定位了索引内部的分区，然后在这个分区索引中找到ROWID，然后回表提取数据。

LOCAL索引是和分区的个数逐个对应的，可以说先定位分区表的分区也可以说先定位索引的分区，因为他们是一一对应的，找到对应分区后，分区内部索引数据集合。

4、对应应用

分区表、索引、分区索引，要利用其性能优势，最基本就是要提取数据时，要通过它首先将数据的范围缩小到一个即使做全盘扫描也不会太慢的情况。

所以SQL一定要有分区上的这个字段的一个WHERE条件，将数据迅速定位到分区内部，而且尽量定位到一个分区里面（这个和创建分区的规则有关系）。

建立分区本身不提要性能，要用好才可提高性能，在必要的RAC集群中，若存在多分区提取数据，适当采用并行提取可以提高提取的速度。

对于索引部分，这里也只提到分区索引的创建方式以及常见索引的维护方式，对于索引原理理解后会更容易认识到提取数据时的技巧。

实战

分区表和一般表一样可以建立索引，分区表可以创建局部索引和全局索引。当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。

1 局部索引分区的建立：

看查询的执行计划，从下面的执行计划可以看出，系统已经使用了索引：

SQL> select * from dinya_test partition(part_01) t where t.item_id=12;

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=187)

   1    0   TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'DINYA_TEST' (Cost=

          2 Card=1 Bytes=187)

   2    1     INDEX (RANGE SCAN) OF 'DINYA_IDX_T' (NON-UNIQUE) (Cost=1

           Card=1)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        334  bytes sent via SQL*Net to client

        309  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          2  rows processed

SQL>

2 全局索引分区的建立。

全局索引建立时global 子句允许指定索引的范围值，这个范围值为索引字段的范围值：

SQL> create index dinya_idx_t on dinya_test(item_id)

  2  global partition by range(item_id)

  3  (

  4     partition idx_1 values less than (1000) tablespace dinya_space01,

  5     partition idx_2 values less than (10000) tablespace dinya_space02,

  6     partition idx_3 values less than (maxvalue) tablespace dinya_space03

  7  ); 

Index created.

SQL>

本例中对表的item_id字段建立索引分区，当然也可以不指定索引分区名直接对整个表建立索引，如：

SQL> create index dinya_idx_t on dinya_test(item_id);

Index created.

SQL>

同样的，对全局索引根据执行计划可以看出索引已经可以使用：

SQL> select * from dinya_test t where t.item_id=12;

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=561)

   1    0   TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'DINYA_TEST' (Cost

          =2 Card=3 Bytes=561)

   2    1     INDEX (RANGE SCAN) OF 'DINYA_IDX_T' (NON-UNIQUE) (Cost=1

           Card=3)

Statistics

----------------------------------------------------------

          5  recursive calls

          0  db block gets

         10  consistent gets

          0  physical reads

          0  redo size

        420  bytes sent via SQL*Net to client

        309  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          3  sorts (memory)

          0  sorts (disk)

          5  rows processed

SQL>

其实分区的管理很简单，难点在于分区方式的选择和分区表上面索引的选择。  

Global Index全局索引和Local Index局部索引，Global partitioned index和global nonpartitioned index，

选择多，就越迷惑。

（本文来源网络，作者佚名，版权归原作者所有）

长按二维码关注公众号AIX专家俱乐部