【SQL开发】Oracle 11g 分区技术

一 分区技术概述

分区技术（Partitioning）可以将大表、大索引分解为更小、易于管理的块，这些块称为分区（Partition），通过分区技术可以有效的解决大表、大索引带来的问题，对分区表执行的SQL查询或DML语句与普通数据表的语句一样。但是定义了分区后，DML语句可以访问、操作一个单独的分区，而不是整个表或索引，这样通过分区技术就能简化对大数据库对象的管理工作。

使用分区技术，有以下优势：

• 提高数据的可用性；
• 将大段分解为小段，从而减轻管理的负担；
• 改善某些查询的性能（分区修剪）；
• 可以把数据的修改分布到多个分区上，从而减少I/O竞争；

二 分区表的分类

1 范围分区

1）范围分区介绍

创建范围分区的关键字是Range，创建该分区后，其中的数据可以根据分区键指定的范围进行分布，当数据在范围内均匀分布时，性能最好。

当表结构采用范围分区时，首先要考虑分区的列应该符合范围分区的方法；其次要考虑列的数据值的取值范围；最后考虑列的边界问题。

2）语法结构

3）示例

--创建范围分区表
create table T_RANGE
(
  ID      NUMBER,
  NAME    VARCHAR2(100),
  CDATE   DATE default sysdate,
  CREATOR VARCHAR2(20) default 'system'
)
partition by range (CDATE)
(
  partition P1 values less than (TO_DATE('2018-05-21', 'YYYY-MM-DD')),
  partition P2 values less than (TO_DATE('2018-05-22', 'YYYY-MM-DD')),
  partition P3 values less than (TO_DATE('2018-05-23', 'YYYY-MM-DD')),
  partition P4 values less than (TO_DATE('2018-05-24', 'YYYY-MM-DD')),
  partition P5 values less than (maxvalue)
 
);
--创建序列
create sequence seq_range;
--初始化范围分区表数据
insert into t_range values(seq_range.nextval,'Jack',to_date('2018-05-20', 'YYYY-MM-DD'),'system');
insert into t_range values(seq_range.nextval,'Peter',to_date('2018-05-21', 'YYYY-MM-DD'),'system');
insert into t_range values(seq_range.nextval,'Geo',to_date('2018-05-22', 'YYYY-MM-DD'),'system');
insert into t_range values(seq_range.nextval,'Lucy',to_date('2018-05-23', 'YYYY-MM-DD'),'system');
insert into t_range values(seq_range.nextval,'Smith',to_date('2018-05-24', 'YYYY-MM-DD'),'system');
insert into t_range values(seq_range.nextval,'James',to_date('2018-05-25', 'YYYY-MM-DD'),'system');
insert into t_range values(seq_range.nextval,'Tom',to_date('2018-05-26', 'YYYY-MM-DD'),'system');
commit;
--查看表数据
select * FROM t_range;

select * FROM t_range partition(p1);
--查看分区信息
SELECT * FROM user_part_tables t WHERE t.table_name = 'T_RANGE';

SELECT * FROM User_Tab_Partitions t WHERE t.table_name='T_RANGE';

SELECT * FROM user_segments t WHERE t.segment_name = 'T_RANGE';

2 散列分区

1）散列分区介绍

对于散列分区表，Oracle会使用一个散列函数对每一条插入数据的分区列值计算出其散列值，以此确定数据应当放在N个分区中的哪个分区中。Oracle建议N是2的一个幂（2、4、8、16等），这样表中的数据才能最好的分布在所有的分区上。散列分区可用于大强度更新、具有高度争用的环境，使用散列分区后，更新不在集中于一个热点段中，而是分散到16个分区上，每个分区都能进行数据的修改。

2）语法结构

3）示例

--创建哈希分区表
create table T_HASH
(
  id      NUMBER,
  name    VARCHAR2(100),
  cdate   DATE default SYSDATE,
  creator VARCHAR2(100) default 'system'
)
partition by hash (ID)
(
  partition P1
    tablespace USERS,
  partition P2
    tablespace TEST
);
--创建序列
CREATE SEQUENCE seq_hash;
--初始化散列表
 BEGIN
   FOR j IN 1..10 LOOP
     INSERT INTO t_hash(ID,NAME) VALUES(seq_hash.nextval,'HASH'||j);
   END LOOP;
   COMMIT;
 END;
--查看表数据
SELECT * FROM t_hash;
SELECT * FROM t_hash PARTITION(p1);
--查看分区信息
SELECT * FROM User_Part_Tables t WHERE t.table_name='T_HASH';
SELECT * FROM User_Tab_Partitions t WHERE t.table_name='T_HASH';
SELECT * FROM User_Segments t WHERE t.segment_name='T_HASH';

4）注意事项

• 使用哈希分区，无法控制一行最终放在哪个分区中，Oracle会应用散列函数，并依据散列的结果来确定行会放在哪里；
• 改变散列分区的个数，数据会在所有分区中重新分布，向一个散列分区表增加或删除一个分区时，将导致所有数据都会重写，因为现在每一行都可能属于一个不同的分区；
• 分区数应该是2的幂。如果分区数是2的幂，那么分区将会均匀分布，如果不是，那么分区将会不均匀分布。

3 列表分区

1）列表分区介绍

列表分区为每个分区指定了一个离散值列表，每行数据根据其分区键值所属列表归到相应的分区中。

2）语法结构