分区表：当表中的数据达到一定数量的时候就需要将一个表分成多个区
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 1000
b 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 1000
b 2000
b 3000
1个表下最大1023个分区
• hash 分区
create table <> (column datatype) partition by hash(sal)
(
partition p1,
partition p2
)
-- Create table
create 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 AGE
c 5000
select * from tmp_hash_pritition_table partition (p2);
NAME AGE
a 1000
b 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 SAL
a 1000
select * from tmp_list_pritition_table partition (p3);
NAME SAL
b 3000
c 5000
c 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_CREATED
USERS TMP_HASH_PRITITION_TABLE P2 YES
USERS TMP_HASH_PRITITION_TABLE P1 YES
USERS TMP_LIST_PRITITION_TABLE P3 YES
USERS TMP_LIST_PRITITION_TABLE P1 YES
USERS TMP_LIST_PRITITION_TABLE P2 NO
USERS BIN$Qzm1jB79Pw3gUzYBhwq6IA==$0 P3 NO
USERS BIN$Qzm1jB79Pw3gUzYBhwq6IA==$0 P2 NO
USERS 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 SAL
b 3000
c 5000
c 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_partitions
SQL> /
TABLE_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TMP_PARTITION_1 P2 WZL_TABLESPACE_TMP
TMP_PARTITION_1 P3 WZL_TABLESPACE_TMP
TMP_PARTITION_1 SYS_P41 WZL_TABLESPACE_TMP
TMP_PARTITION_1 P1 WZL_TABLESPACE_TMP
7. 删除分区
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_1
9.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/A
ALL_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 USABLE
LOCAL_P_INDEX WZL_TABLESPACE_TMP USABLE
LOCAL_P_INDEX WZL_TABLESPACE_TMP USABLE
# 在分区本地索引表里面能看到，USABLE 表示可用，如果不可用则索引需要重建
1549
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
