分区表：当表中的数据达到一定数量的时候就需要将一个表分成多个区 1. 什么时候创建分区表 当一个表的数据库量达到1G 就将普通表创建成分区表。 2. 分区表的特点 • 每一个分区的数据都可以分散存放来
分区表：当表中的数据达到一定数量的时候就需要将一个表分成多个区
1. 什么时候创建分区表
当一个表的数据库量达到1G 就将普通表创建成分区表。
2. 分区表的特点
• 每一个分区的数据都可以分散存放来分散IO
• 分区表的数据可以按照分区来备份
• 数据在访问的时候可以只访问某一个分区的数据，减少扫描的数据量
• 每一个分区的数据都可以放在不同的表空间中来保证数据的安全
3. 分区表的分类
• range 范围分区。当分区的每个值在某个范围内可以使用范围分区
create table <> (column datatype) partition by range(sal)
(partition par1 values less then (1000),
partition par2 values less then (2000),
……
partition <> values less then(maxvalue)
);
1 create table tmp_partition_1(name varchar2(20),sal number) partition by range(sal) 2 (partition p1 values less than (1000), 3 partition p2 values less than (2000), 4 partition p3 values less than (3000) 5* )SQL> /Table created.SQL> select * from tmp_partition_1;NAME SAL-------------------- ----------a 1000b 2000 1* select * from tmp_partition_1 partition(p2)SQL> /NAME SAL-------------------- ----------a 1000# 增加分区SQL> alter table tmp_partition_1 add partition values less than (4000);Table altered.SQL> select * from tmp_partition_1;NAME SAL-------------------- ----------a 1000b 2000b 3000
1个表下最大1023个分区
• hash 分区
create table <> (column datatype) partition by hash(sal)
(
partition p1,
partition p2
)
-- Create tablecreate table TMP_HASH_PRITITION_TABLE( name VARCHAR2(20), age NUMBER)partition by hash (NAME)( partition P1 tablespace USERS, partition P2 tablespace USERS);# hash 分区表已经建成功insert into tmp_hash_pritition_table select 'a', 1000 from dual union all select 'b', 3000 from dual union all select 'c', 5000 from dual;# 插入数据select * from tmp_hash_pritition_table partition (p1);NAME AGEc 5000select * from tmp_hash_pritition_table partition (p2);NAME AGEa 1000b 3000
• list 列表分区。分区的列的值是一个固定值可以使用list分区
create table <> (column datatype) partition by list(sal)
(
partition p1 values(1000),
partition p2 values(2000)，
partition p3 values(default)
);
create table tmp_list_pritition_table(name varchar2(20),sal number) partition by list(sal) (partition p1 values(1000) ,partition p2 values(2000),partition p3 values (default));insert into tmp_list_pritition_table select 'a', 1000 from dual union all select 'b', 3000 from dual union all select 'c', 5000 from dual union all select 'c', 7000 from dual;select * from tmp_list_pritition_table partition (p1);NAME SALa 1000select * from tmp_list_pritition_table partition (p3);NAME SALb 3000c 5000c 7000
伪列：虚拟列
create table <> (column datatype，hiredate date,hdate as to_char(hiredate,'yyyy')) partition by list(hdate)
(
partition p1 values(1000),
partition p2 values(2000)，
partition p3 values(default)
);
这里 hdate 就是虚拟列
desc user_tab_partitions; 可以看到所有的表分区
select a.tablespace_name, a.table_name, a.partition_name, a.segment_created from user_tab_partitions aTABLESPACE_NAME TABLE_NAME PARTITION_NAME SEGMENT_CREATEDUSERS TMP_HASH_PRITITION_TABLE P2 YESUSERS TMP_HASH_PRITITION_TABLE P1 YESUSERS TMP_LIST_PRITITION_TABLE P3 YESUSERS TMP_LIST_PRITITION_TABLE P1 YESUSERS TMP_LIST_PRITITION_TABLE P2 NOUSERS BIN$Qzm1jB79Pw3gUzYBhwq6IA==$0 P3 NOUSERS BIN$Qzm1jB79Pw3gUzYBhwq6IA==$0 P2 NOUSERS BIN$Qzm1jB79Pw3gUzYBhwq6IA==$0 P1 NO
create table <> (column datatype)
select * from <> partition (分区名字)
• 组合分区。上面的分区还不够，再在分区下再使用分区
（by list + by range
by range + by hash
by list + hast)
partition by list(column)
subpartition by range(column)
(
partition <> values()
(
subpartition <> values less than ()
subpartitiion <> values less than ()
)
partition <> values()
……
）
5. 分区表与普通表的转换
插入数据法
交换分区法
先创建一些分区表，在建一些和分区表相对的普通表
alter table t12 exchange partition p1 with table e1
# 先建一个普通表 create table tmp_list_pritition_table_b as select * from tmp_list_pritition_table where 1 = 2;# 再把分区表中的某个分区的数据转换到普通表中alter table tmp_list_pritition_table exchange partition p3 with table tmp_list_pritition_table_b;# 检查转换后的数据select * from tmp_list_pritition_table_b;NAME SALb 3000c 5000c 7000
在线重定义法
表中必须要有主键
dbms_redefinition
• 创建分区表
• 使用在线重定义表转换exec dbms_redefinition.start_redef_table('SCOTT'，‘EMP‘，‘LEMP’）
• exec dems_redefinition.sync_interim_table('SCOTT'，‘EMP‘，‘LEMP’)
•exec dems_redefinition.finish_redef_table('SCOTT'，‘EMP‘，‘LEMP’)
6. 查看表中所有的分区
1* select table_name,partition_name,tablespace_name from user_tab_partitionsSQL> /TABLE_NAME PARTITION_NAME TABLESPACE_NAME------------------------------ ------------------------------ ------------------------------TMP_PARTITION_1 P2 WZL_TABLESPACE_TMPTMP_PARTITION_1 P3 WZL_TABLESPACE_TMPTMP_PARTITION_1 SYS_P41 WZL_TABLESPACE_TMPTMP_PARTITION_1 P1 WZL_TABLESPACE_TMP7. 删除分区
7.1 先删除或者转移数据
7.2 在删除分区
alter table table_name drop partition p1;
SQL> delete from TMP_PARTITION_1 where sal >= 3000;1 row deleted.SQL> commit;# 先删除分区对于的数据SQL> alter table TMP_PARTITION_1 drop partition p3;Table altered.# 再删除分区
8 合并分区
alter table table_name merge partitions p1 ,p2 into partition p3;
SQL> alter table TMP_PARTITION_1 add partition pall values less than (10000);Table altered.SQL> alter table TMP_PARTITION_1 merge partitions P1,P2 into partition P2;Table altered.9 分区索引
9.1 分区表全局索引
create index index_name on table_name(colunm_name) global;
SQL> create index all_p_index on TMP_PARTITION_1(name) global;Index created.# 查看上面建的分区表全局索引SQL> select index_name,table_name from user_indexes where table_name = 'TMP_PARTITION_1' 2 ;INDEX_NAME TABLE_NAME------------------------------ ------------------------------ALL_P_INDEX TMP_PARTITION_19.2 分区表本地索引
create index index_name on table_name() local;
1* select index_name,table_name ,status from user_indexes where table_name = 'TMP_PARTITION_1'SQL> /INDEX_NAME TABLE_NAME STATUS------------------------------ ------------------------------ --------LOCAL_P_INDEX TMP_PARTITION_1 N/AALL_P_INDEX TMP_PARTITION_1 VALID# 在索引表里面能看到建的本地分区索引 但是状态 N/A 表示本地分区索引SQL> select index_name,tablespace_name,status from user_ind_partitions;INDEX_NAME TABLESPACE_NAME STATUS------------------------------ ------------------------------ --------LOCAL_P_INDEX WZL_TABLESPACE_TMP USABLELOCAL_P_INDEX WZL_TABLESPACE_TMP USABLELOCAL_P_INDEX WZL_TABLESPACE_TMP USABLE# 在分区本地索引表里面能看到，USABLE 表示可用，如果不可用则索引需要重建
