（1）分区表的作用：
在大量业务数据处理的项目中，可以考虑使用分区表来提高应用系统的性能并方便数据管理，本文详细介绍了分区表的使 用。在大型的企业应用或企业级的数据库应用中，要处理的数据量通常可以达到几十到几百GB，有的甚至可以到TB级。虽然存储介质和数据处理技术的发展也很快，但是仍然不能满足用户的需求，为了使用户的大量的数据在读写操作和查询中速度更快，Oracle提供了对表和索引进行分区的技术，以改善大型应用系统的性能。
（2）使用分区的优点：
增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能；
改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
（3）Oracle数据库提供对表或索引的分区方法：
范围分区（range）；
哈希分区（hash）；
列表分区（list）；
范围－哈希复合分区（range-hash）；
范围－列表复合分区（range-list）。
一、范围分区：
CREATE TABLE range_example ( range_key_column date , data varchar2(20) )
PARTITION BY RANGE (range_key_column)
( PARTITION part_1 VALUES LESS THAN (to_date('01/01/2005','dd/mm/yyyy')),
PARTITION part_2 VALUES LESS THAN (to_date('01/01/2006','dd/mm/yyyy'))
)
二、散列分区：
create table t_partition_hash (id number,name varchar2(50))
partition by hash(id)(
partition t_hash_p1 tablespace GCOMM,
partition t_hash_p2 tablespace GCOMM,
partition t_hash_p3 tablespace GCOMM);
同样也可以用如下语句创建（直接指定要分区个数及存储的表空间）
create table t_partition_hash2 (id number,name varchar2(50))
partition by hash(id)
partitions 3 store in(GCOMM,GMAPDATA);--3个分区，第三个没指定表空间即存储在默认的表空间
散列分区数应该使用2的幂
--例子来进行说明
create or replace procedure hash_proc(p_nhash in number,
p_cursor out sys_refcursor) authid current_user as
l_text long;
l_template long := 'select $POS$ oc, ''p$POS$'' pname,count(*) cnt ' ||
'from t partition($PNAME$) union all ';
begin
begin
execute immediate 'drop table t';
exception
when others then
null;
end;
execute immediate 'create table t(id) partition by hash(id) partitions ' ||
p_nhash || ' as select rownum from all_objects';
for x in (select partition_name pname, partition_position pos
from user_tab_partitions
where table_name = 'T'
order by partition_position) loop
l_text := l_text || replace(replace(replace(l_template, '$POS$', x.pos),
'p$POS$',
x.pname),
'$PNAME$',
x.pname);
end loop;
open p_cursor for 'select pname,cnt,substr( rpad(''*'',30*round(cnt/max(cnt)over(),2),''*''),1,30) hg from (' || substr(l_text,
1,
length(l_text) - 11) || ')order by oc';
end;
SQL> variable x refcursor
SQL> set autoprint on
SQL> exec hash_proc( 4, :x ); --四个分区
PL/SQL 过程已成功完成。
PN CNT HG
-- ---------- ------------------------------ 数据在每个区分布很均匀
p1 21500 *****************************
p2 21508 *****************************
p3 21811 ******************************
p4 21403 *****************************
SQL> exec hash_proc( 5, :x );--5个分区
PL/SQL 过程已成功完成。
PN CNT HG
-- ---------- ----------------------------------- 数据分布不均匀
p1 10837 ***************
p2 21511 *****************************
p3 21812 ******************************
p4 21403 *****************************
p5 10665 **************
SQL> exec hash_proc( 6, :x );--6个分区
PL/SQL 过程已成功完成。
PN CNT HG
-- ---------- --------------------------------- 数据分布不均匀
p1 10838 ***************
p2 10857 ***************
p3 21812 ******************************
p4 21405 *****************************
p5 10666 **************
p6 10657 **************
SQL> exec hash_proc( 7, :x );--7个分区
PL/SQL 过程已成功完成。
PN CNT HG
-- ---------- -------------------------------
p1 10838 ***************
p2 10857 ***************
p3 10895 ***************
p4 21408 ******************************
p5 10666 ***************
p6 10658 ***************
p7 10921 ***************
SQL> exec hash_proc( 8, :x );--8个分区
PL/SQL 过程已成功完成。
PN CNT HG
-- ---------- -------------------------------
p1 10841 *****************************
p2 10857 *****************************
p3 10897 ******************************
p4 10726 *****************************
p5 10666 *****************************
p6 10658 *****************************
p7 10922 ******************************
p8 10685 *****************************
--总结：从以上的结果可以看出，分区数为2的n次方，数据可以均匀分布到各个区。
三、列表分区：
create table list_example
( state_cd varchar2(2), data varchar2(20))
partition by list(state_cd)
( partition part_1 values ( 'ME', 'NH', 'VT', 'MA' ),
partition part_2 values ( 'CT', 'RI', 'NY' )
)
--如果state_cd字段的值不在定义两个分区的值之内，则无法插入
insert into list_example values ( 'VA', 'data' );
--无法插入，提示ORA-14400：插入的分区关键字未映射到任何分区
解决办法：增加一个默认分区
alter table list_example add partition part_3 values ( DEFAULT );
insert into list_example values ( 'VA', 'data' );--成功插入
但是，如果存在一个默认分区，就无法再增加一个具体其他值的分区：
alter table list_example add partition part_4 values( 'CA', 'NM' );
--提示ORA-14323：在default分区已存在时无法添加分区
解决办法：先把default分区删除，再添加需要的分区，最后再增加default分区
alter table list_example drop partition part_3
四、组合分区：
--range和hash组合分区
CREATE TABLE composite_example ( range_key_column date, hash_key_column int, data varchar2(20))
PARTITION BY RANGE (range_key_column)--范围分区
subpartition by hash(hash_key_column) subpartitions 2 --hash分区
( PARTITION part_1 VALUES LESS THAN(to_date('01/01/2005','dd/mm/yyyy'))
(subpartition part_1_sub_1, subpartition part_1_sub_2),
PARTITION part_2 VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy'))
(subpartition part_2_sub_1, subpartition part_2_sub_2 )
)
--range和list组合分区
CREATE TABLE composite_range_list_example ( range_key_column date, code_key_column int, data varchar2(20))
PARTITION BY RANGE (range_key_column)
subpartition by list(code_key_column)
(PARTITION part_1 VALUES LESS THAN(to_date('01/01/2005','dd/mm/yyyy'))
(subpartition part_1_sub_1 values( 1, 3, 5, 7 ), subpartition part_1_sub_2 values( 2, 4, 6, 8 )),
PARTITION part_2 VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy'))
(subpartition part_2_sub_1 values ( 1, 3 ), subpartition part_2_sub_2 values ( 5, 7 ), subpartition part_2_sub_3 values ( 2, 4, 6, 8 ))
)
行移动：即更新后其值从第一个分区移到了第二个分区时必须启用行移动才能更新成功
insert into range_example( range_key_column, data )
values( to_date( '15-12-2004 00:00:00','dd-mm-yyyy hh24:mi:ss' ),'application data...' );
insert into range_example ( range_key_column, data )
values ( to_date( '01-1-2005 00:00:00', 'dd-mm-yyyy hh24:mi:ss' )-1/24/60/60, 'application data...' );
select * from range_example partition(part_1);
RANGE_KEY_COLUMN DATA
---------------- --------------------
2004-12-15 application data...
2004-12-31 23:59 application data...
update range_example
set range_key_column = trunc(range_key_column)
where range_key_column = to_date( '31-12-2004 23:59:59', 'dd-mm-yyyy hh24:mi:ss' );
--如果更新时超出了本区则会提示：ORA-14402：更新分区关键字列将导致分区的更改
update range_example
set range_key_column = to_date('02-1-2005','dd-mm-yyyy')
where range_key_column = to_date('31-12-2004','dd-mm-yyyy');
select rowid from range_example where range_key_column = to_date('31-12-2004','dd-mm-yyyy');--AAAWlCAAGAADxMlAAB
--解决办法：启动该表的行移动
alter table range_example enable row movement;
select rowid from range_example where range_key_column = to_date('02-1-2005','dd-mm-yyyy');--AAAWlDAAGAADxMtAAA
--总结：对于有分区的表，进行更新时，如果更新后其分区发生改变，则必须启动该表的行移动，由以上也可以看到，更新前后其rowid是不同的。
扫一扫
2661
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
