Oracle分区知识介绍-上集

一、范围分区(Range partition )
–分区是将非常大的表或者索引分解成多个小的、可管理的部分。
–查询数据库中所有的分区表

select * from dba_tables where partitioned = 'YES';
select * from user_tables where partitioned = 'YES';

–查看数据库分区表信息

select * from dba_part_tables;

–查询分区明细信息

select * from dba_tab_partition;

–查看分区键

select * from dba_part_key_columns;

–查看子分区

select * from dba_tab_subpartitions;

–查看子分区键

select * from dba_subpart_key_columns;

–查询分区表的数据，range_hash_sales为表名，sales_1998为分区名，sales_1998_sub_1为子分区名

select * from range_hash_slaes; --查询表（所有数据）
select * from range_hash_slaes partition(sales_1998); --查询该表分区数据
select * from range_hash_slaes subpartition(sales_1998_sub_1) --查询该表分区下的子分区的数据

–查看分区存储信息，从表段观察（segment），一个表普遍只会生成一个段存储在数据库中，
–如果对该表分了4个区，那就会在数据库中生成4个一样的表段，以不同的partition_name进行存储。

select * from dba_segments where segment_name = '表名'; --如果该表创建了4个分区，那就有4条记录，

–否则正常来说只有一条。
–注意：没数据的空表是不会在dba_segments里面生成表段的，只有数据的表才会有表段。

–范围分区是最常用的分区，经常用于日期计算，语法：

create table aaa(
name varchar2(10),
id   number,
day_south date
)
partition range (day_south)
(
partition by partition_name1 values less than (to_date('2020-01-01')), --小于1月1日的数据都会保存在该分区
partition by partition_name2 values less than (to_date('2020-02-01')), --小于2月1日并大于等于1月1日的数据会保存在该分区
partition by partition_name3 values less than (to_date('2020-03-01')),                                                                         
partition by partition_name4 values less then (maxvalue) --大于等于3月1日-其他日期的数据会保存在该分区。
);

/***************************************************************************************************
补充：范围分区的多列条件 ：
根据以下的例子，要说明两点关于范围分区的概念，
1、多列组合的范围分区，会先判断第一个分区键，如果对应的键值<第一个分区键，则直接插入（不看后面的分区键）
2、多列组的的范围分区，会先判断第一个分区键，如果对应的键值>=第一个分区键，则会看第二个分区。
***************************************************************************************************/

partition range (column1,column2)
(
partition by partition_name1 values less than (to_date('2020-01-01'),2), --小于1月1日并小于2的数据都会保存在该分区
partition by partition_name2 values less than (to_date('2020-02-01'),3), --小于2月1日并大于等于1月1日和小于3的数据会保存在该分区
partition by partition_name3 values less than (to_date('2020-03-01'),4),                                                                         
partition by partition_name4 values less then (maxvalue,maxvalue) --大于等于3月1日并大于等于4的数值 至 其他日期的数据会保存在该分区。
);

二、列表分区(List partition)
/**************************************************************************************************
1、分区键只能包含一个column，而上述的范围分区和还未介绍的hash分区可以包含多个字段。
2、列表分区键的选择一般会对列上的重复值较多的情况下使用，假如某个列有100行，而100行里面有100个不相同的值，
这个情况就不适用于列表分区，另外一种情况，100行里面只有3-4个值不同，其余得值都为这3-4个值的重复值，这种情况
适用于列表分区。
3、列表分区的含义其实跟oracle case函数相似。
***************************************************************************************************/
–列表分区适用于值数量分布均匀的列，经常用于学生代号或者部分代号之类的列，语法：

create table list_salaes(
prod_id number(6), --部门ID
cust_id number,
time_id date,
amount_id number(10,2)
)
partition by list (prod_id)
(
	partition prod_id_01 values(2), --当部门ID为2的时候，将对应记录归属到prod_id_01分区。
	partition prod_id_02 values(3),
	partition prod_id_03 values(4),
	partition prod_id_04 values(default) --除了以上部门id，其余的部门ID记录默认归属到prod_id_04分区。
);

三、哈希分区(Hash partition)
/*
ORACLE哈希函数:ora_hash(value1,value2); --函数解析：给指定参数生成一个唯一对应的值，永远不会改变。
oracle会有多出地方使用HASH值，例如会判断前端返回的查询与缓存中的HASH是否相匹配，如果匹配则直接返回
查询结果，称之为软解析，如果没有匹配相同的hash值，则重新解析、执行再返回结果，这个步骤称之为硬解析。
/
/******************************************************************************************************
1、哈希分区是ORACLE在分区健上执行内部的哈希算法，以期实现数据自动均匀地分散到各个分区中。
2、当往哈希分区写入数据时，Oracle尝试随机、均匀地写入分区中，开发人员不用特意指定插入哪个分区，Oracle应用
内部的哈希算法执行插入规则；插入分区的数据分布数会根据分区数量增加或者减少；例如100W条数据库要写入四个哈希
分区，根据Oracle的哈希写入规则，那四个分区会均匀持有25W条数据，如果这时删除了两个分区，那么剩余的两个分区
数据量各为50W条。
3、哈希分区适用场景：当适用范围分区或列表分区的时候，发现某个分区因特殊原因导致某个分区数据量特别大，其他
分区数量极少，但是已经按照分区规则划分还是体现不到减少数据量优化的效果，这时就可以考虑哈希分区，让每个分区
数据均匀的分布在每个分区上。
4、不适合用哈希分区的场景，当某列的值分布重复值过多的时候，假如某列有100行，散列值只有2到3个，其余都是这2
到3个值的重复值，那这样就会导致分区分布数量不均匀，一般使用哈希分区会推荐在列重复值较少的情况下使用，能生成
独立的HASH值进行写入。
********************************************************************************************************/
–哈希分区表创建语句：

create table hash_salaes(
prod_id number(6), --部门ID
cust_id number,
time_id date,
amount_id number(10,2)
)
partition by hash (prod_id) --根据prod_id的值均匀写入到两个分区，建议采用重复值较少的列。
partition 2; --创建两个哈希分区

/***************************************************************************************************
补充：
1、上述两个分区我们都是指定分区名称创建，而哈希分区的名称是通过系统创建的，所以我们需要通过dba_tab_partition
去查询分区名称，才可知道哈希分区的内容。 select * from hash_salaes partition(查询到的分区名称);
2、Hash分区也是和范围分区一样，同样支持多列分区，但是分区规则不是按照范围分区那种多列规则进行，还是由
Oracle内部Hash规则自行决定。
***************************************************************************************************/

四、复合分区表(Composite partition)
/**************************************************************************************************
概念：
1、当我们采用上述3点分区策略分区之后仍然较大，或者是有特殊需求，还可以通过在分区内再创建子分区的方式
将分区再分区，即为复合分区的方式;
2、复合分区就是将分区再次分区的分区方式，也即为两层分区（一般都会到两层分区，分区——>子分区，很少还会
在子分区上再创建子分区，视业务场景而定）
3、复合分区的第一层分区，称为分区（partition）,内层分区称为子分区（subpartition）
4、在复合分区中，数据物理地存储在子分区（subpartition）段上，分区（partition）称为了一个逻辑容器，是
一个指向实际子分区（subpartition）的容器
5、每个顶层分区（partition）包含的子分区（subpartition）数量可以不相等。
***************************************************************************************************/
–在Oracle10G中，只支持Rang_Hash和Rang_List两种子分区创建方式，以下为创建语句

复合分区（Rang_Hash分区）创建语句：
create table range_hash_sales(
name varchar2(10),
id   number,
day_south date
)
partition range (day_south) --分区指定为范围分区先进行第一次数据划分
subpartition by hash(id)    --子分区指定为哈希分区进行第二次数据划分
(
partition by partition_name1 values less than (to_date('2020-01-01')), --小于1月1日的数据都会保存在该分区
	(subpartition partition_name1_sub_1,
	 subpartition partition_name1_sub_2), --创建两个Hash子分区，对小于1月1日的数据里的id列再进行Hash分区
partition by partition_name2 values less then (maxvalue) --大于等于1月1日的数据都会存放在该分区
	(subpartition partition_name2_sub_1,
	 subpartition partition_name2_sub_2,
	 subpartition partition_name2_sub_3)  --创建三个Hash子分区，上述概念有说道上层分区下的子分区数量可以不相同。
);

–复合分区（Rang_List分区）创建语句：

create table range_hash_sales(
name varchar2(10),
id   number,
day_south date
)
partition range (day_south) --分区指定为范围分区先进行第一次数据划分
subpartition by hash(id)    --子分区指定为列表分区进行第二次数据划分
(
partition by partition_name1 values less than (to_date('2020-01-01')), --小于1月1日的数据都会保存在该分区
	(subpartition partition_name1_sub_1 values (2,4), --当id等于2或者4的值时，会划分到该子分区
	 subpartition partition_name1_sub_2 values (default)), --除了2或者4两个ID值以外的值都存放在该子分区
partition by partition_name2 values less then (maxvalue) --大于等于1月1日的数据都会存放在该分区
	(subpartition partition_name1_sub_1 values (2,4), --当id等于2或者4的值时，会划分到该子分区
	 subpartition partition_name1_sub_2 values (default)) --除了2或者4两个ID值以外的值都存放在该子分区
);

–在Oracle11G中，增加了四种新的复合分区类型：range-range，list-range，list-list，list-hash
以下列举列表-范围（list-range）分区创建方式：

create table list_range_sales(
name varchar2(10),
id   number,
day_south date
)
partition by list (id) --分区指定为列表分区先进行第一次数据划分
subpartition by range(day_south)    --子分区指定为范围分区进行第二次数据划分
(
partition list01 values(2,4) --id等于2或4的值纳入list01分区
	(subpartition list01_2019 values less than (to_date('2020-01-01')), --当id等于2或4并且日期小于2020年的数据存放在该子分区
	 subpartition list01_no2019 values less than (maxvalue) ), --大于等于2020年的数据并且包括2或者4两个ID值的数据都存放在该子分区
partition list02 values(default) --除了id等于2或4的值以外的数据都存放在该分区
	(subpartition list01_2019 values less than (to_date('2020-01-01')), --当id不等于2或4并且日期小于2020年的数据存放在该子分区
	 subpartition list01_no2019 values less than (maxvalue)	)			--大于等于2020年的数据并且不包括2或者4两个ID值的数据都存放在该子分区
);

五、间隔分区表（Interval Partitioning）
/******************************************************************
介绍间隔分区表前先举个例子，让大家更容易理解
例1：

create table aaa(
name varchar2(10),
id   number,
day_south date
)
partition range (day_south)
(
partition by partition_name1999 values less than (to_date('2020-01-01')), --小于1月1日的数据都会保存在该分区
partition by partition_name2001_01 values less than (to_date('2020-02-01')), --小于2月1日并大于等于1月1日的数据会保存在该分区
partition by partition_name2001_02 values less than (to_date('2020-03-01')),                                                                         
);

从上述中我们知道partition_name1分区是存放小于20200101的数据，partition_name2分区是存放小于20200201的数据，以此类推，
基本上插入的数据都会写入对应的月份分区中，但是目前只创建了3个分区，如果这个时候到20200405了，这个时候4月份的数据插入
就会报错，错误代码ORA-14400（没有对应分区存放该值）这种错误已经很明显告知了错的地方，像上述介绍范围分区的时候，我们都会在
分区键的最后加上partition by Others values less then (maxvalue) 这样一句话，意思是除了已创建指定的分区外的日期所对应的数据
都会存放在该分区中，没错，此时我们再重新插入一条4月份的数据，不会再出现ORA-14400的错误，但是违反了我们分区的本质，日复一日，
Others分区存放的数据只会越来越大，并没有体现到分区的意义，但是又不肯能每个月都手动去增加对应的分区，假设00张表就要手动增加
100次，很麻烦；这次Oracle11g新特性就推出了一个支持自动创建分区的新特性来解决这个问题，通过Interval分区可以很好地解决此问题。
/
/
概念：Interval分区是Oracle11g新增的特性，它是针对Range分区类型的一种功能扩展，对范围分区，如果插入的数据与当前所有的分区均
不匹配，Interval分区可以实现自动分区创建，也就是可以应对上述例1中我们遇到的情况。
******************************************************************/
Interval分区创建语法：

create table bbb(
name varchar2(10),
id   number,
day_south date
)
partition range (day_south)
INTERVAL(Numtoyminterval(1,'year')) --INTERVAL分区只争对于范围分区使用，这句话意思表示每次有新一年的数据差距都会按照年份创建一个新的分区
(
partition by partition_name1999 values less than (to_date('2000-01-01','YYYY-MM-DD')), 
partition by partition_name2000 values less than (to_date('2001-01-01','YYYY-MM-DD')), 
partition by partition_name2001 values less than (to_date('2002-01-01','YYYY-MM-DD'))                                                                 
);

/*上述语句中，我们插入1999，2000，2001的数据都是没问题的，但是插入2002年的数据如果没有指定INTERVAL分区语句，就会报错，
不存在该分区(ORA-014400)，此时我们已经指定了INTERVAL分区语句,当2002年数据插入时就会自动创建一个2002年的分区，非常简便
同时也方便维护。
当插入完2002年的数据后，我们可以通过
select * from dba_tab_partitions t where t.table_name=‘bbb’; --查询系统分区表根据新插入数据所创建的分区名和我们定义的分区名
select * from dba_part_tables t where t.table_name=‘bbb’ --查看Interval列可以知道间隔分区的指定类型，如果Interval为空则不是间隔分区只是普通的范围分区。
是不同的，查看INTERVAL列就会是YES而我们手动创建的就是NO。
间隔类型：NUMTOYMINTERVAL/NUMTODSINTERVAL函数（前面表示年月/后面表示天时分秒，两个函数代表着不同的意义）。
年/月的间隔指定：
INTERVAL(NUMTOYMINTERVAL(1,‘year’)) --一年分一个区，同年份的数据不会再自动创建分区。
INTERVAL(NUMTOYMINTERVAL(1,‘month’)) --一月分一个区，同月份的数据不会再自动创建分区。
天的间隔指定：
INTERVAL(NUMTODSINTERVAL(1,‘day’)) – 一天分一个区，当天的数据不会再自动创建分区。
还有时分秒的创建方式，可以线下自行去了解一下，按小时创建的分区已经很夸张了。
*/
–刚刚有说到，INTERVAL分区是基于范围分区创建的，所以我们还可以把范围分区转换为INTERVAL分区表。

将范围分区表转换为Interval分区表：
alter table aaa set INTERVAL(NUMTOYMINTERVAL(1,'year')); --将范围分区表设置为按年自动创建分区
alter table bbb set INTERVAL(); --将Interval分区表设置为范围分区

–同时Interval间隔分区也是支持复合分区表：

create table range_hash_sales(
name varchar2(10),
id   number,
day_south date
)
partition by range (day_south) --分区指定为范围分区先进行第一次数据划分
INTERVAL(NUMTOYMINTERVAL(1,'year')) --只需要在复合范围分区里加上这条间隔分区的类型，就形成了复合间隔分区表。
subpartition by hash(id)    --子分区指定为列表分区进行第二次数据划分
(
partition by partition_name1999 values less than (to_date('2000-01-01','YYYY-MM-DD')),
	(subpartition partition_name1_sub_1 values (2,4), 
	 subpartition partition_name1_sub_2 values (default)), 
partition by partition_name2000 values less then ('2001-01-01','YYYY-MM-DD') 
	(subpartition partition_name1_sub_1 values (2,4), 
	 subpartition partition_name1_sub_2 values (default))
);

–range-range，list-range，rang都同时支持间隔分区类型的指定。其他两种特性不支持。

总结：
范围分区：会根据值与值之间的范围，进行分区写入。
列表分区：会根据散列的固定值，进行分区写入。
哈希分区：会根据Oracle内部的哈希算法规则由其自行控制分区写入。
复合分区：当表的分区策略不满足承载现有数据压力的情况下，可以对现有分区再进行分区。
间隔分区：其实间隔分区就类似范围分区的一个插件，本质上还是范围分区，提供自动创建分区的新特性，只有Oracle11g以上才有。