Oracle-性能优化篇-分区表

🍓Oracle分区表

• 📌优点：
  1. 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度（个人评价只针对条件中带分区字段的查询性能）。
  2. 增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用（鸡蛋不放一个篮子里的概念）。
  3. 维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可（各数据文件之间相互独立，互不影响）。
  4. 均衡I/O：可以把不同的分区映射到不同磁盘以平衡I/O，改善整个系统性能（这个针对整体的查询性能，但是分区必须放在不同的表空间，每个表空间尽量分布在不同的磁盘上，这样可以充分利用各个磁盘的I/O来提高效率）。
• 📌缺点：
  分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。

🍓分区表

🍒范围分区（Range）

适合于有明确的时间线或者表里有能表示范围的字段的大批量数据。

create table T_USERS_INFO(
--userID设置的主键，这里就不展示主键设置语句了
userID number,
name varchar2(100),
age number,
birthday date
)
--范围分区
partition by range(birthday) (
--不想指定分区的表空间，可以去掉tablespace xxx
partition p_2020 values less than(to_date('2021-01-01','yyyy-MM-dd')) tablespace p_userinfo_2020,
partition p_2021 values less than(to_date('2022-01-01','yyyy-MM-dd')) tablespace p_userinfo_2021,
partition p_2022 values less than(to_date('2023-01-01','yyyy-MM-dd')) tablespace p_userinfo_2022,
partition p_max values less than(maxvalue) tablespace p_userinfo_max  --兜底，如果不加这个，超出范围的数据插入则将报错。包括空值都是放到这个兜底里面的。
)

--查询表的分区信息
select table_name,partition_name,tablespace_name from dba_tab_partitions where table_name = 'T_USERS_INFO';

--查询指定分区的数据
select * from T_USERS_INFO partition(p_2022)  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

🍒List分区

适合于表里存在固定值的字段，如性别、年级等

create table T_USERS_INFO2(
name varchar2(100),
age number,
sex varchar2(10)
)
--指明它是list分区
partition by list(sex)(
--这里设的是固定值，不是范围
partition p_man values ('男') ,
partition p_woman values('女'),
partition p_max values(default)  --兜底，如果不加这个，超出范围的数据插入则将报错。 包括空值都是放到这个兜底里面的。
)
1
2
3
4
5
6
7
8
9
10
11
12

🍒Hash散列分区

适合于表里没有任何规律的字段。

create table T_USERS_INFO3
(
  name VARCHAR2(100),
  age  NUMBER,
  sex  VARCHAR2(10)
)
--指明它是hash分区
partition by hash (AGE)(
partition P1 tablespace p_user_info_1,
partition P2 tablespace p_user_info_2  --hash散列分区不需要兜底分区
);
1
2
3
4
5
6
7
8
9
10
11

🍓引用分区

引用分区是给子表分区，适合与已存在的分区表有关联关系的表。

--创建引用分区，引用分区父表T_USERS_INFO
create table T_USER_PERSON(
userInfoID number not null,
pname varchar2(100),
relation varchar2(100),
--注意：子表的外键必须对应分区父表的主键，但是主键不是必须的分区字段
constraint userid_re foreign key(userInfoID) references T_USERS_INFO(userID)
)
partition by reference(userid_re);
1
2
3
4
5
6
7
8
9

🍓组合分区

根据多个字段进行分区（用于单个字段分区后每个分区的数据依然很多，使用组合分区可以对分区进行再分区）

create table T_USERS_INFO4(
name varchar2(100),
age number,
sex varchar2(10) 
)
--首先按照age范围分区
partition by range(age)
--在age范围分区中再按照sex进行list分区
subpartition by list(sex)(
partition p_18 values less than(18) tablespace p_userinfo4_18(
subpartition p_18_man values ('男') ,
subpartition p_18_woman values ('女'),
subpartition p_18_default values(default) 
),
partition p_30 values less than(30) tablespace p_userinfo4_30(
subpartition p_30_man values ('男') ,
subpartition p_30_woman values ('女'),
subpartition p_30_default values(default) 
),
partition p_50 values less than(50) tablespace p_userinfo4_50(
subpartition p_50_man values ('男') ,
subpartition p_50_woman values ('女'),
subpartition p_50_default values(default) 
),
partition p_70 values less than(70) tablespace p_userinfo4_70(
subpartition p_70_man values ('男') ,
subpartition p_70_woman values ('女'),
subpartition p_70_default values(default) 
),
partition p_max values less than(maxvalue) tablespace p_userinfo4_max(
subpartition p_max_man values ('男') ,
subpartition p_max_woman values ('女'),
subpartition p_max_default values(default) 
)
)

#查询子分区的数据
select * from T_USERS_INFO4 subpartition (p_18_man)

#查询子分区的信息
select table_owner,table_name,partition_name,subpartition_name,tablespace_name from dba_tab_subpartitions where table_name = 'T_USERS_INFO4';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

🍓分区索引

分区表一般创建本地索引，本地索引在分区表中就是分区索引，各个分区之间的索引相互独立，可以避免因为删除某个分区而导致索引失效的问题。

--创建本地索引
create index user_info_age on T_USERS_INFO(age) local;

--查看索引状态
--STATUS：索引状态（VALID：有效索引，N/A：有效分区索引，UNUSABLE：无效索引）
--PARTITIONED：是否分区索引
select STATUS, PARTITIONED from all_indexes where table_name = 'T_USERS_INFO'
1
2
3
4
5
6
7

注意： 唯一索引、主键索引，要创建本地索引时，需要带上分区字段进行创建。

🍓分区的新增与删除

🍒手动新增分区

以时间为范围分区的，随着时间的推移我们需要不断给数据库添加新的分区，或者清理数据库中久远的存量分区。

--新增分区（新增的分区会将P_MAX中符合条件的数据自动归集到该新分区中去）
alter table 表名 split partition P_MAX at(to_date(分区界限的时间,'YYYY/mm/dd')) into (partition 分区名,partition P_MAX);
	--demo
	alter table T_USERS_INFO split partition P_MAX at(to_date('2024-01-01','yyyy-MM-dd')) into (partition p_2023,partition p_MAX);

--删除分区数据
alter table 表名 truncate partition 分区名称;

--删除分区
alter table 表名 drop partition 分区名称;

--注意：truncate和drop都会导致索引失效，建议把分区表的索引建成本地索引（分区索引），防止这种情况发生！
1
2
3
4
5
6
7
8
9
10
11
12

🍒oracle自动创建分区

在创建表时，带上interval(NUMTOYMINTERVAL(1, 'YEAR/MONTH/DAY'))便可以自动创建分区了，加上这个的分区表只需要在创表时创建一个初始分区即可，禁止创建兜底分区与该语句一起使用。

create table T_USERS_INFO(
--userID设置的主键，这里就不展示主键设置语句了
userID number,
name varchar2(100),
age number,
birthday date
)
--范围分区
partition by range(birthday) interval(NUMTOYMINTERVAL(1, 'YEAR'))(
--不想指定分区的表空间，可以去掉tablespace xxx
partition p_2022 values less than(to_date('2023-01-01','yyyy-MM-dd')) tablespace p_userinfo_2022
)
1
2
3
4
5
6
7
8
9
10
11
12