分区表学习"/>
oracle的分区表学习
目录
一、分区表应用场景介绍
（1）什么是分区
（2）什么时候需要分区
（3）分表和分区表的区别
（4）分区表的类型
二、范围分区表
（1）分区键
（2）范围分区
（3）案例1（分区键为单列）
（4）案例2（分区键为多列）
（5）案例3（分区键字段插入空值）
三、列表分区
（1）列表分区
（2）案例1
（3）案例2（列表多列分区（报错））
四、哈希分区
（1）哈希函数
（2）哈希分区
（3）案例1（分区键为单列）
（4）案例2（分区键为多列）
五、复合分区
（1）复合分区
（2）oracle 10g中的复合分区类型
（2.1）范围-哈希分区表
（2.2）范围-列表分区表
（3）oracle 11g中的新复合分区类型
（3.1）列表-范围复合分区表
六、interval分区表
（1）从一个range分区表的案例开始
（2）案例改进之后
（3）interval分区表
（4）interval分区表和范围分区表的相互转化
（5）带有间隔分区的复合分区表
七、分区裁剪
（1）分区裁剪
（2）分区裁剪之分区消除
（3）分区裁剪之子分区消除
八、本地分区索引
（1）分区索引
（2）本地分区索引
（3）本地前缀索引
（4）本地非前缀索引
（5）索引上的分区裁剪
九、全局分区索引
（1）全局分区索引
（2）案例1
3）案例2（在普通表上创建全局分区索引）
（4）全局分区索引上的分区裁剪
十、分区表的常用维护操作
（1）创建分区表
（2）添加分区
（3）删除分区/子分区
（4）合并分区
（5）拆分分区
（6）截断分区
（7）重命名分区
（8）移动分区
（9）分区信息查询
一、分区表应用场景介绍
（1）什么是分区
在Oracle数据库中，为了提升对大表/大索引的可管理性、可维护性以及性能，Oracle引入了分区表机制，可以将大表/大索引分解为多个小的、可管理的分区。多个分区相对独立，有独立的存储结构，共同构成了整个完整的表/索引。分区对应用透明，即对访问数据库的应用而言，逻辑上讲只有一个表或一个索引（相当于应用看到的仍然只是一个表或索引），但在物理上这个表或索引可能由数个物理分区组成。
（2）什么时候需要分区
Oracle官网的建议以下情形采用分区表：
1. Tables greater than 2GB should always be considered for partitioning.（表数据量大于2GB时应该考虑使用分区）；
2. Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.（新数据加入至最新分区中的用于存储历史数据的表）。
（3）分表和分区表的区别
为了分散存储大表/索引的数据，另外一种途径是分表，分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，例如可以将客户表customer拆分成两个表，一个用于存储个人客户 customer_person，另一个存储对公客户 customer_company。分表完全独立，在逻辑上是多张不同的表，而分区表在物理上是一张表。分区表对应用透明易于管理维护，分表不易于管理维护。
（4）分区表的类型
范围分区(Range Partitioning)
列表分区(List Partitioning)
哈希分区(Hash Partitioning)
组合分区(Composite Partitioning)
基本分区是指仅仅使用了一层分区(范围分区、列表分区、哈希分区)的分区表。
如果对分区表进行多层级的分区，则称为组合分区。
二、范围分区表
（1）分区键
分区是将非常大的表或者索引分解成多个小的、可管理的部分。分区表是通过分区键来分解分区的。分区键决定了分区表中的每一行数据流向哪个分区。分区表中的每一行数据根据分区键，隐式地被分配到一个分区中。
（2）范围分区
范围分区按照分区键，根据数据行落在分区键中不同的范围值来划分分区。
（3）案例1（分区键为单列）
| CREATE TABLE time_range_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) (PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')), PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')), PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')), PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE) ); |
| insert into time_range_sales values(116,11393,to_date('1999-06-05','yyyy-mm-dd'),'2',999,1,12.18); insert into time_range_sales values(40,100530,to_date('1998-11-30','yyyy-mm-dd'),'9',33,1,44.99); insert into time_range_sales values(118,133,to_date('2001-06-06','yyyy-mm-dd'),'2',999,1,17.12); insert into time_range_sales values(133,9450,to_date('2000-12-01','yyyy-mm-dd'),'2',999,1,31.28); insert into time_range_sales values(36,4523,to_date('1999-01-27','yyyy-mm-dd'),'3',999,1,53.89); insert into time_range_sales values(125,9417,to_date('1998-02-04','yyyy-mm-dd'),'3',999,1,16.86); insert into time_range_sales values(30,170,to_date('2001-02-23','yyyy-mm-dd'),'2',999,1,8.8); insert into time_range_sales values(24,11899,to_date('1999-06-26','yyyy-mm-dd'),'4',999,1,43.04); insert into time_range_sales values(35,2606,to_date('2000-02-17','yyyy-mm-dd'),'3',999,1,54.94); insert into time_range_sales values(45,9491,to_date('1998-08-28','yyyy-mm-dd'),'4',350,1,47.75); |
| 查询分区表的数据: SELECT * FROM time_range_sales; SELECT * FROM time_range_sales partition(SALES_1998);
查询系统中的所有分区表 select * from dba_tables where partitioned='YES' AND table_name='TIME_RANGE_SALES';
查询分区表信息 select * from dba_part_tables;
查询分区 select * from dba_tab_partitions;
查询分区键 select * from dba_part_key_columns; |
（4）案例2（分区键为多列）
分区键为2列时，如何判断数据如何流向哪个分区呢？
1.先判断第一个分区键，如果 < 第一个分区键的，则插入（不看第二分区键)
2.先判断第一个分区键，如果 = 第一个分区键的，则看第二分区键。
| CREATE TABLE time_range_sales2 ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id,prod_id) (PARTITION SALES_1998_01 VALUES LESS THAN (TO_DATE('19990101','YYYYMMDD'),30), PARTITION SALES_1998_02 VALUES LESS THAN (TO_DATE('19990101','YYYYMMDD'),100), PARTITION SALES_1999_01 VALUES LESS THAN (TO_DATE('20000101','YYYYMMDD'),30), PARTITION SALES_1999_02 VALUES LESS THAN (TO_DATE('20000101','YYYYMMDD'),60), PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('20010101','YYYYMMDD'),100), PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE,MAXVALUE) ); |
| insert into time_range_sales2 values(40,100530,to_date('1998-11-30','yyyy-mm-dd'),'9',33,1,44.99);
insert into time_range_sales2 values(40,100530,to_date('1999-01-01','yyyy-mm-dd'),'9',33,1,44.99); |
SELECT * FROM Dba_Tab_Partitions WHERE table_name='TIME_RANGE_SALES2';
（5）案例3（分区键字段插入空值）
| --创建测试范围分区表 create table range_test (id number not null PRIMARY KEY, test_date date ) partition by range (test_date) ( partition p_2020_01_01 values less than (to_date('2020-02-01', 'yyyy-mm-dd')) );
--插入测试数据 insert into range_test values(10,to_date('2020-01-03','yyyy-mm-dd')); insert into range_test values(11,null); --结果如下：
|
--添加一个新分区，再次测试
alter table range_test add partition p_max values less than (maxvalue);
查看数据分区在了哪一个分区上？
三、列表分区
（1）列表分区
列表分区将分区键划分为多个分散的值，根据散列的分区键值匹配一行数据流向哪个分区（类似Oracle中的case语句），不同于范围反区表，列表分区的分区键只能包含一个列。
（2）案例1
| CREATE TABLE list_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY LIST (channel_id) (PARTITION channels_01 VALUES (2), PARTITION channels_02 VALUES (3,9)， PARTITION channels_other VALUES(DEFAULT) ); |
| insert into list_sales values(116,11393,to_date('1999-06-05','yyyy-mm-dd'),'2',999,1,12.18); insert into list_sales values(40,100530,to_date('1998-11-30','yyyy-mm-dd'),'9',33,1,44.99); insert into list_sales values(118,133,to_date('2001-06-06','yyyy-mm-dd'),'2',999,1,17.12); insert into list_sales values(133,9450,to_date('2000-12-01','yyyy-mm-dd'),'2',999,1,31.28); insert into list_sales values(36,4523,to_date('1999-01-27','yyyy-mm-dd'),'3',999,1,53.89); insert into list_sales values(125,9417,to_date('1998-02-04','yyyy-mm-dd'),'3',999,1,16.86); insert into list_sales values(30,170,to_date('2001-02-23','yyyy-mm-dd'),'2',999,1,8.8); insert into list_sales values(24,11899,to_date('1999-06-26','yyyy-mm-dd'),'4',999,1,43.04); insert into list_sales values(35,2606,to_date('2000-02-17','yyyy-mm-dd'),'3',999,1,54.94); insert into list_sales values(45,9491,to_date('1998-08-28','yyyy-mm-dd'),'4',350,1,47.75); |
（3）案例2（列表多列分区（报错））
| CREATE TABLE list_sales2 ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY LIST (prod_id,channel_id) (PARTITION channels_01 VALUES (116,2), PARTITION channels_01 VALUES (40,9), PARTITION channels_other VALUES(DEFAULT,DEFAULT) ); |
四、哈希分区
（1）哈希函数
哈希函数，或者叫哈希算法，是对给定的参数生成一个唯一的与之对应的结果值的过程。Oracle数据库中也提供了哈希函数ORA_HASH(object,max_bucket, seed_value)。Oracle数据库中多个地方用到了哈希算法，例如哈希连接、sql语句解析的哈希值、哈希分区表。
（2）哈希分区
哈希分区是Oracle在分区键上执行内部的哈希算法，以期实现数据自动均匀地分散到各个分区中。当往哈希分区表插入数据时，Oracle尝试随机、均匀地写入分区中。开发人员不用指定如何插入分区，Oracle应用内部哈希算法自动完成。
（3）案例1（分区键为单列）
| CREATE TABLE hash_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY HASH (prod_id) PARTITIONS 2; |
| insert into hash_sales values(116,11393,to_date('1999-06-05','yyyy-mm-dd'),'2',999,1,12.18); insert into hash_sales values(40,100530,to_date('1998-11-30','yyyy-mm-dd'),'9',33,1,44.99); insert into hash_sales values(118,133,to_date('2001-06-06','yyyy-mm-dd'),'2',999,1,17.12); insert into hash_sales values(133,9450,to_date('2000-12-01','yyyy-mm-dd'),'2',999,1,31.28); insert into hash_sales values(36,4523,to_date('1999-01-27','yyyy-mm-dd'),'3',999,1,53.89); insert into hash_sales values(125,9417,to_date('1998-02-04','yyyy-mm-dd'),'3',999,1,16.86); insert into hash_sales values(30,170,to_date('2001-02-23','yyyy-mm-dd'),'2',999,1,8.8); insert into hash_sales values(24,11899,to_date('1999-06-26','yyyy-mm-dd'),'4',999,1,43.04); insert into hash_sales values(35,2606,to_date('2000-02-17','yyyy-mm-dd'),'3',999,1,54.94); insert into hash_sales values(45,9491,to_date('1998-08-28','yyyy-mm-dd'),'4',350,1,47.75); |
| SELECT * FROM Dba_Tab_Partitions WHERE table_name='HASH_SALES'; SELECT * FROM HASH_SALES PARTITION(SYS_P61); |
（4）案例2（分区键为多列）
| CREATE TABLE multi_key_hash_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY HASH(prod_id,channel_id) PARTITIONS 5; |
| SELECT * FROM Dba_Tab_Partitions WHERE table_name='MULTI_KEY_HASH_SALES'; |
五、复合分区
（1）复合分区
Range partition,List partition,Hash partition是Oracle分区表中的基本分区类型。
如果表按照上述分区策略分区之后仍然较大，或者是因其他需求，还可以通过分区内再创建子分区的方式将分区再分区，即复合分区的方式。
复合分区就是将分区再次分区的分区方式，也即两层分区。
复合分区的第一层分区，称为分区(partition) ，内层分区称为子分区(subpartition) 。
在复合分区中，数据物理地存储在子分区(subpartition)段上，分区(partition)成为了一个逻辑容器，或者是一个指向实际子分区(subpartition)的容器。
每个顶层分区(partition)包含的子分区(subpartition) 数可以不相等。
（2）oracle 10g中的复合分区类型
在Oracle 10g中，只有两种复合分区方式: range-hash，range-list。
（2.1）范围-哈希分区表
| CREATE TABLE range_hash_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) SUBPARTITION BY HASH(prod_id) ( PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('1999-01-01','YYYY-MM-DD') ) ( subpartition SALES_1998_sub_1, subpartition SALES_1998_sub_2 ), PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE) ( subpartition SALES_2001_sub_1, subpartition SALES_2001_sub_2 ) ); |
| insert into range_hash_sales values(116,11393,to_date('1999-06-05','yyyy-mm-dd'),'2',999,1,12.18); insert into range_hash_sales values(40,100530,to_date('1998-11-30','yyyy-mm-dd'),'9',33,1,44.99); insert into range_hash_sales values(118,133,to_date('2001-06-06','yyyy-mm-dd'),'2',999,1,17.12); insert into range_hash_sales values(133,9450,to_date('2000-12-01','yyyy-mm-dd'),'2',999,1,31.28); insert into range_hash_sales values(36,4523,to_date('1999-01-27','yyyy-mm-dd'),'3',999,1,53.89); insert into range_hash_sales values(125,9417,to_date('1998-02-04','yyyy-mm-dd'),'3',999,1,16.86); insert into range_hash_sales values(30,170,to_date('2001-02-23','yyyy-mm-dd'),'2',999,1,8.8); insert into range_hash_sales values(24,11899,to_date('1999-06-26','yyyy-mm-dd'),'4',999,1,43.04); insert into range_hash_sales values(35,2606,to_date('2000-02-17','yyyy-mm-dd'),'3',999,1,54.94); insert into range_hash_sales values(45,9491,to_date('1998-08-28','yyyy-mm-dd'),'4',350,1,47.75); |
（2.2）范围-列表分区表
| CREATE TABLE range_list_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) SUBPARTITION BY LIST(channel_id) ( PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('1999-01-01','YYYY-MM-DD')) ( subpartition SALES_1998_sub_1 VALUES (2,4), subpartition SALES_1998_sub_2 VALUES(DEFAULT) ), PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE) ( subpartition SALES_2001_sub_1 VALUES (2,4), subpartition SALES_2001_sub_2 VALUES(DEFAULT) ) );
insert into RANGE_LIST_SALES values(116,11393,to_date('1999-06-05','yyyy-mm-dd'),'2',999,1,12.18); insert into RANGE_LIST_SALES values(40,100530,to_date('1998-11-30','yyyy-mm-dd'),'9',33,1,44.99); insert into RANGE_LIST_SALES values(118,133,to_date('2001-06-06','yyyy-mm-dd'),'2',999,1,17.12); insert into RANGE_LIST_SALES values(133,9450,to_date('2000-12-01','yyyy-mm-dd'),'2',999,1,31.28); insert into RANGE_LIST_SALES values(36,4523,to_date('1999-01-27','yyyy-mm-dd'),'3',999,1,53.89); insert into RANGE_LIST_SALES values(125,9417,to_date('1998-02-04','yyyy-mm-dd'),'3',999,1,16.86); insert into RANGE_LIST_SALES values(30,170,to_date('2001-02-23','yyyy-mm-dd'),'2',999,1,8.8); insert into RANGE_LIST_SALES values(24,11899,to_date('1999-06-26','yyyy-mm-dd'),'4',999,1,43.04); insert into RANGE_LIST_SALES values(35,2606,to_date('2000-02-17','yyyy-mm-dd'),'3',999,1,54.94); insert into RANGE_LIST_SALES values(45,9491,to_date('1998-08-28','yyyy-mm-dd'),'4',350,1,47.75); |
（3）oracle 11g中的新复合分区类型
在Oracle11g中,增加了四中新的复合分区类型:range-range，list-range，list-list，list-hash。
（3.1）列表-范围复合分区表
| CREATE TABLE list_range_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY LIST(channel_id) SUBPARTITION BY RANGE (time_id) ( PARTITION LIST01 VALUES(2,4) ( subpartition LIST01_1998 VALUES LESS THAN (TO_DATE('1999-01-01','YYYY-MM-DD')), subpartition LIST01_NON1998 VALUES LESS THAN (MAXVALUE) ), PARTITION LIST02 VALUES(DEFAULT) ( subpartition LIST02_1998 VALUES LESS THAN (TO_DATE('1999-01-01','YYYY-MM-DD')), subpartition LIST02_NON1998 VALUES LESS THAN (MAXVALUE) ) ); |
六、interval分区表
（1）从一个range分区表的案例开始
| CREATE TABLE range_sales_v1 ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) ( PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('1999-01-01','YYYY-MM-DD')), PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('2000-01-01','YYYY-MM-DD')), PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('2001-01-01','YYYY-MM-DD')), PARTITION SALES_2001 VALUES LESS THAN (TO_DATE('2002-01-01','YYYY-MM-DD')) ); |
| insert into range_sales_v1 values(116,11393,to_date('1999-06-05','yyyy-mm-dd'),'2',999,1,12.18); insert into range_sales_v1 values(40,100530,to_date('1998-11-30','yyyy-mm-dd'),'9',33,1,44.99); insert into range_sales_v1 values(118,133,to_date('2001-06-06','yyyy-mm-dd'),'2',999,1,17.12); insert into range_sales_v1 values(133,9450,to_date('2000-12-01','yyyy-mm-dd'),'2',999,1,31.28);
--测试 insert into range_sales_v1
|
（2）案例改进之后
| 改写升级，加入MAXVALUE CREATE TABLE range_sales_v2 ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) ( PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('1999-01-01','YYYY-MM-DD')), PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('2000-01-01','YYYY-MM-DD')), PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('2001-01-01','YYYY-MM-DD')), PARTITION SALES_2001 VALUES LESS THAN (TO_DATE('2002-01-01','YYYY-MM-DD')), PARTITION SALES_others VALUES LESS THAN (MAXVALUE) ); |
（3）interval分区表
在实际工作中，经常会遇到上述问题，在Oracle 11g之前，通常是由DBA手动进行分区定义维护。
Oracle 11g开始，Oracle新增的Interval分区方式可以很好的解决这个问题。
Interval分区是Oracle 11g新增的特性，它是针对Range分区类型的一种功能扩展。
对范围分区，如果插入的数据与当前所有的分区均不匹配，Interval分区可以实现自动的分区创建。
| CREATE TABLE range_sales_v3 ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) INTERVAL(Numtoyminterval(1,'year')) ( PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('1999-01-01','YYYY-MM-DD')), PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('2000-01-01','YYYY-MM-DD')) ); |
常用的间隔类型：
间隔类型NUMTOYMINTERVAL/NUMTODSINTERVAL函数:
年/月的间隔指定:
INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
天的间隔指定:
INTERVAL(NUMTODSINTERVAL(1,'DAY')));
（4）interval分区表和范围分区表的相互转化
将范围分区表转换为Interval分区表:
Alter table range_sales_v1 SET INTERVAL(Numtoyminterval(1,'year'));
将Interval 分区表转换为范围分区表:
Alter table range_sales_v3 SET INTERVAL();
可以通过视图dba_part_tables中字段interval查看是否是interval分区表。
| CREATE TABLE range_sales_v1 ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id) ( PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('1999-01-01','YYYY-MM-DD')), PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('2000-01-01','YYYY-MM-DD')), PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('2001-01-01','YYYY-MM-DD')), PARTITION SALES_2001 VALUES LESS THAN (TO_DATE('2002-01-01','YYYY-MM-DD')) ); |
| select owner,table_name,partitioning_type,subpartitioning_type,partition_count,INTERVAL from dba_part_tables WHERE table_name='RANGE_SALES_V1'; |
Alter table RANGE_SALES_V1 SET INTERVAL(Numtoyminterval(1,'year'));
但是通过测试，range分区中有maxvalue分区的不能转换为interval分区表。
（5）带有间隔分区的复合分区表
| CREATE TABLE interval_range_list_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY RANGE (time_id)INTERVAL(Numtoyminterval(1,'year')) SUBPARTITION BY LIST(channel_id) ( PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('1999-01-01','YYYY-MM-DD')) ( subpartition SALES_1998_sub_1 VALUES (2,4), subpartition SALES_1998_sub_2 VALUES(DEFAULT) ), PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('2000-01-01','YYYY-MM-DD')) ( subpartition SALES_1999_sub_1 VALUES (2,4), subpartition SALES_1999_sub_2 VALUES(DEFAULT) ) ); |
| --插入测试数据 insert into interval_range_list_sales values(116,11393,to_date('1999-06-05','yyyy-mm-dd'),'2',999,1,12.18);
commit; select owner,table_name,partitioning_type,subpartitioning_type,partition_count,INTERVAL from dba_part_tables WHERE table_name='INTERVAL_RANGE_LIST_SALES';
SELECT * FROM dba_tab_partitions WHERE table_name='INTERVAL_RANGE_LIST_SALES'; --再次插入数据 insert into interval_range_list_sales values(40,100530,to_date('2018-11-30','yyyy-mm-dd'),'9',33,1,44.99); 可以看到自动生成了一个新的分区。 --完整的ddl语句 create table INTERVAL_RANGE_LIST_SALES ( prod_id NUMBER(6), cust_id NUMBER, time_id DATE, channel_id CHAR(1), promo_id NUMBER(6), quantity_sold NUMBER(3), amount_sold NUMBER(10,2) ) partition by range (TIME_ID) subpartition by list (CHANNEL_ID) ( partition SALES_1998 values less than (TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans 255 ( subpartition SALES_1998_SUB_1 values ('2', '4') tablespace SYSTEM, subpartition SALES_1998_SUB_2 values (DEFAULT) tablespace SYSTEM ), partition SALES_1999 values less than (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans 255 ( subpartition SALES_1999_SUB_1 values ('2', '4') tablespace SYSTEM, subpartition SALES_1999_SUB_2 values (DEFAULT) tablespace SYSTEM ), partition SYS_P69 values less than (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans 255 ( subpartition SYS_SUBP68 values (DEFAULT) tablespace SYSTEM ) ); |
七、分区裁剪
（1）分区裁剪
分区裁剪(Partition Pruning)，又叫分区修剪、分区消除，是指在对Oracle分区表进行查询时，优化器会检查谓词条件中是否存在对分区段的过滤，如果存在，则可以仅访问符合条件的分区，即裁剪掉无用的分区。
分区裁剪(Partition Pruning)是根据查询语句中的谓词条件来进行分区消除的。分区裁剪既可以在分区级别消除，也可以在分区上的索引级别。
范围和列表分区上可以在谓词上使用等于,>,<,Like和In 运算符进行分区消除。
哈希分区表上只能使用等于和In运算符进行分区消除。
（2）分区裁剪之分区消除
| 以上面创建的表time_range_sales为例进行演示。 SELECT * FROM dba_part_tables WHERE table_name='TIME_RANGE_SALES';
SELECT * FROM dba_tab_partitions WHERE table_name='TIME_RANGE_SALES';
select * from TIME_RANGE_SALES;
EXPLAIN PLAN FOR SELECT * FROM TIME_RANGE_SALES WHERE time_id<TO_DATE('1999-01-01', 'YYYY-MM-DD');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Pstart,pstop指的就是从分区1开始到分区1结束的意思。 |
（3）分区裁剪之子分区消除
| 以上面的表range_list_sales为例进行演示。 SELECT * FROM dba_part_tables WHERE table_name='RANGE_LIST_SALES'; SELECT * FROM dba_tab_partitions WHERE table_name='RANGE_LIST_SALES'; select * from RANGE_LIST_SALES;
EXPLAIN PLAN FOR SELECT * FROM range_list_sales WHERE time_id<TO_DATE('1999-01-01', 'YYYY-MM-DD') AND channel_id='2';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); |
八、本地分区索引
（1）分区索引
同分区表类似，索引也可以分区，称为分区索引。分区索引又分为本地分区索引和全局分区索引。
本地分区索引就是按照分区表的分区，在每个分区上创建相应的索引。
每个表分区都有一个相应的索引分区，并且只索引该表分区的数据。
全局分区索引不依赖于分区表的分区，在全表上创建索引。
全局分区索引也可以对索引进行分区， 但可以和分区表的分区不一致。
本地分区索引通常用在DSS系统，可维护性高。
全局分区索引通常用于OLTP系统，有更好的随机选择性能。
（2）本地分区索引
本地分区索引又分为两类，本地前缀索引和本地非前缀索引。
本地前缀索引，索引的第一列必须是分区键。
本地非前缀索引，分区键没有作为索引定义的第一列。
判断本地分区索引是前缀还是非前缀的依据是分区键是否作为索引的引导列。
（3）本地前缀索引
创建本地前缀分区索引:
create index hash_sales_idx1 on time_range_sales(time_id) local;
create index hash_sales_idx2 on time_range_sales(time_id,channel_id) local;
查看索引信息:
SELECT * FROM DBA_PART_INDEXES WHERE table_name='TIME_RANGE_SALES';
SELECT * FROM DBA_IND_PARTITIONS WHERE index_name='HASH_SALES_IDX2';
（4）本地非前缀索引
创建本地非前缀分区索引:
create index hash_sales_idx3 on time_range_sales(channel_id) local;
create index hash_sales_idx4 on time_range_sales(channel_id,time_id) local;
（5）索引上的分区裁剪
分区裁剪除了可以在表分区级别发生，也可以在索引分区级别发生。与表分区上的分区消除类似，当Oracle优化器通过分区索引访问数据，并且通过谓词条件确定可以消除不需要的索引分区时，便会进行索引上的分区裁剪。
收集下表的统计信息：
exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'TIME_RANGE_SALES',estimate_percent => 30,method_opt=> 'for all indexed columns');
EXPLAIN PLAN FOR SELECT time_id FROM TIME_RANGE_SALES WHERE time_id<TO_DATE('1999-01-01', 'YYYY-MM-DD');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
九、全局分区索引
（1）全局分区索引
全局分区索引和分区表的分区信息独立不相干，是在全表级别创建的索引类型。
全局分区索引非常适合OLTP系统，能够极大地提高查询性能。
全局分区索引可以以表中任意的列为索引列，索引列可以不是分区表的分区键。
全局分区索引必须是前缀的(索引的分区键必须是索引的第一列).
（2）案例1
| CREATE INDEX time_channel_sales_idx ON time_range_sales (channel_id) GLOBAL PARTITION BY RANGE (channel_id) (PARTITION p1 VALUES LESS THAN (3), PARTITION p2 VALUES LESS THAN (4), PARTITION p3 VALUES LESS THAN (MAXVALUE)); |
3）案例2（在普通表上创建全局分区索引）
| CREATE TABLE SALES_TB ( PROD_ID NUMBER(6) , CUST_ID NUMBER , TIME_ID DATE , CHANNEL_ID CHAR(1) , PROMO_ID NUMBER(6) , QUANTITY_SOLD NUMBER(3) , AMOUNT_SOLD NUMBER(10,2) );
CREATE INDEX SALES_TB_IDX1 ON SALES_TB (CHANNEL_ID) GLOBAL PARTITION BY RANGE (CHANNEL_ID) ( PARTITION P1 VALUES LESS THAN (3), PARTITION P2 VALUES LESS THAN (4), PARTITION P3 VALUES LESS THAN (MAXVALUE));
|
（4）全局分区索引上的分区裁剪
| exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'TIME_RANGE_SALES',estimate_percent => 100,method_opt=> 'for all indexed columns',cascade=>TRUE);
EXPLAIN PLAN FOR SELECT * FROM time_range_sales WHERE channel_id='9';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); |
十、分区表的常用维护操作
（1）创建分区表
| 分区表的分区可以在不同的表空间 CREATE TABLE list_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY LIST (channel_id) ( PARTITION channels_01 VALUES (1) tablespace TLBB_DATA, PARTITION channels_02 VALUES (2) tablespace TLBB_DATA, PARTITION channels_03 VALUES (3) tablespace TLBB_DATA, PARTITION channels_04 VALUES (4) tablespace TLBB_IDX );
insert into list_sales values(116,11393,to_date('1999-06-05','yyyy-mm-dd'),'2',999,1,12.18); insert into list_sales values(118,133,to_date('2001-06-06','yyyy-mm-dd'),'2',999,1,17.12); insert into list_sales values(133,9450,to_date('2000-12-01','yyyy-mm-dd'),'2',999,1,31.28); insert into list_sales values(36,4523,to_date('1999-01-27','yyyy-mm-dd'),'3',999,1,53.89); insert into list_sales values(125,9417,to_date('1998-02-04','yyyy-mm-dd'),'3',999,1,16.86); insert into list_sales values(30,170,to_date('2001-02-23','yyyy-mm-dd'),'2',999,1,8.8); insert into list_sales values(24,11899,to_date('1999-06-26','yyyy-mm-dd'),'4',999,1,43.04); insert into list_sales values(35,2606,to_date('2000-02-17','yyyy-mm-dd'),'3',999,1,54.94); insert into list_sales values(45,9491,to_date('1998-08-28','yyyy-mm-dd'),'4',350,1,47.75); |
SELECT * FROM dba_tab_partitions WHERE table_name='LIST_SALES';
（2）添加分区
添加新的分区:
alter table list_sales add partition channels_05 values (5) tablespace tlbb_data;
如果分区表有default分区，则不能再往分区表中添加新分区。
| drop table list_sales; CREATE TABLE list_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY LIST (channel_id) ( PARTITION channels_01 VALUES (2), PARTITION channels_02 VALUES (3,9)， PARTITION channels_other VALUES(DEFAULT) );
alter table list_sales add partition channels_05 values (5) tablespace tlbb_data; |
（3）删除分区/子分区
删除分区:
alter table list_sales drop partition channels_other;
删除子分区:
alter table list_sales drop subpartition p2sub3;
（4）合并分区
合并分区命令
将分区channels_02中的数据合并到分区channels_01
alter table list_sales merge partitions channels_01, channels_02 into partition channels_01;
SELECT * FROM LIST_SALES;
--合并前分区channels_01没有数据
--合并后查看数据
--查看现有分区
SELECT * FROM dba_tab_partitions WHERE table_name='LIST_SALES';
--再次插入符和分区2的数据
可以看到分区合并后，被合并的分区已经不存在，数据存储到了合并分区里面。如果此时再次插入符和原来分区channels_02的数据会存在channels_01分区里面。
（5）拆分分区
| CREATE TABLE list_sales ( prod_id NUMBER(6) , cust_id NUMBER , time_id DATE , channel_id CHAR(1) , promo_id NUMBER(6) , quantity_sold NUMBER(3) , amount_sold NUMBER(10,2) ) PARTITION BY LIST (channel_id) ( PARTITION channels_01 VALUES (1) tablespace TLBB_DATA, PARTITION channels_02 VALUES (2,3,4) tablespace TLBB_DATA, PARTITION channels_03 VALUES (5) tablespace TLBB_DATA, PARTITION channels_04 VALUES (9) tablespace TLBB_IDX );
insert into list_sales values(116,11393,to_date('1999-06-05','yyyy-mm-dd'),'2',999,1,12.18); insert into list_sales values(118,133,to_date('2001-06-06','yyyy-mm-dd'),'2',999,1,17.12); insert into list_sales values(133,9450,to_date('2000-12-01','yyyy-mm-dd'),'2',999,1,31.28); insert into list_sales values(36,4523,to_date('1999-01-27','yyyy-mm-dd'),'3',999,1,53.89); insert into list_sales values(125,9417,to_date('1998-02-04','yyyy-mm-dd'),'3',999,1,16.86); insert into list_sales values(30,170,to_date('2001-02-23','yyyy-mm-dd'),'2',999,1,8.8); insert into list_sales values(24,11899,to_date('1999-06-26','yyyy-mm-dd'),'4',999,1,43.04); insert into list_sales values(35,2606,to_date('2000-02-17','yyyy-mm-dd'),'3',999,1,54.94); insert into list_sales values(45,9491,to_date('1998-08-28','yyyy-mm-dd'),'4',350,1,47.75); insert into list_sales values(45,9491,to_date('1998-08-28','yyyy-mm-dd'),'5',350,1,47.75); insert into list_sales values(45,9491,to_date('1998-08-28','yyyy-mm-dd'),'9',350,1,47.75);
--分区channels_04中的数据如下： |
拆分分区命令:
--将分区channels_02中的一部分数据就是channel_id等于3和4的数据拆分到新分区channels_05分区里。
ALTER TABLE list_sales SPLIT PARTITION channels_02 VALUES (3,4) INTO (PARTITION channels_05, PARTITION channels_02);
插入新数据看存储在哪些分区：
insert into list_sales
values(300,11393,to_date('1999-06-05','yyyy-mm-dd'),'3',999,1,12.18);
insert into list_sales
values(333,11393,to_date('1999-06-05','yyyy-mm-dd'),'4',999,1,12.18);
insert into list_sales
values(666,11393,to_date('1999-06-05','yyyy-mm-dd'),'2',999,1,12.18);
select * from list_sales partition(channels_02);
（6）截断分区
| 截断分区命令: alter table list_sales truncate partition channels_02;
截断子分区命令: alter table list_sales truncate subpartition p2sub2; |
（7）重命名分区
| 重命名分区命令: alter table list_sales rename partition channels_other to channels_others;
重命名子分区命令: alter table list_sales rename subpartition pxx to channels_others; |
（8）移动分区
| 移动分区命令: alter table list_sales move partition channels_02 tablespace example;
移动子分区命令: alter table list_sales move partition channels_02 tablespace example; |
（9）分区信息查询
| 分区表/分区索引相关数据字典: SELECT * FROM DBA_TABLES WHERE PARTITIONED='YES'; SELECT * FROM DBA_SEGMENTS WHERE SEGMENT_NAME='LIST_SALES'; SELECT * FROM DBA_PART_TABLES; SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='LIST_SALES'; SELECT * FROM DBA_TAB_SUBPARTITIONS; SELECT * FROM DBA_PART_KEY_COLUMNS; SELECT * FROM DBA_SUBPART_KEY_COLUMNS; SELECT * FROM DBA_PART_INDEXES; SELECT * FROM DBA_IND_PARTITIONS; |
更多推荐
oracle的分区表学习
发布评论