分区表详解

Oracle分区表：
1.使用分区表的场景：
（1）大数据量的表
（2）包括历史数据的表（历史表只有当前月份的数据被修改，其他月份只能read-only）

2.分区表的好处：
（1）增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用
（2）维护方便：如果表的某个分区出现故障，需要修复数据，只需修改该分区即可
（3）均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能
（4）改善查询性能：提高检索速度

3.分区表的种类：
（1）范围分区表（range）：创建一个按字段数据范围分区的表，分区置于指定的不同表空间中

（2）列表分区表(list)：创建一个按字段数据列表固定可枚举值分区的表，插入记录分区字段的值必须在列表中，否则不能被插入

（3）哈希分区表(hash)：创建一个按字段数据hash值分区的表

（4）组合分区表：在分区中可以再建立子分区，以实现分区组合。可任意对上述各类分区进行组合分区
分为：
范围-哈希复合分区（range-hash）
范围-列表复合分区（range-list）

4.建立分区语句：
（例子：list分区表）
create table dm
(
    msisdn          varchar2(15) not null,                                  terminalid      varchar2(64),                                          

terminalgroupid varchar2(64),                                           submsisdn       varchar(8) generated always as (substr(msisdn,10,2)) virtual    )
partition by list (submsisdn)
(
  partition p_list00 values ('00') tablespace promotion,
  partition p_list01 values ('01') tablespace promotion,
  partition p_list02 values ('02') tablespace promotion,
  partition p_list03 values ('03') tablespace promotion,
  partition p_list04 values ('04') tablespace promotion,
  partition p_list05 values ('05') tablespace promotion
);

一.range,list,hash语法：
create table 表名(
字段1 类型1,
字段2 类型2,
...，
字段n 类型n
)
partition by range/list/hash（字段）
（
partition 分区名1 values ('value值1') tablespace 数据库名，
partition 分区名2 values ('value值2') tablespace 数据库名，
...，
partition 分区名n values ('value值n') tablespace 数据库名
）；

二.组合分区语法：
范围-哈希复合分区（range-hash）
范围-列表复合分区（range-list）

create table 表名（
字段1 类型1，
字段2 类型2，
...，
字段n 类型n
）
partition by range (表字段值1) subpartition by hash/list(表字段值2)
（
partition 分区名1 values （values值1）
（
subpartition 辅助分区名1 values (values值1)，
subpartition 辅助分区名2 values (values值2)，
...，
subpartition 辅助分区名n values (values值n)
），
...，
partition 分区名2 values （values值2）
（
subpartition 辅助分区名1 values (values值1)，
subpartition 辅助分区名2 values (values值2)，
...，
subpartition 辅助分区名n values (values值n)
  ）
）；

三.自动创建分区表：（interval）
create table 表名（
字段1 类型1，
字段2 类型2，
...，
字段n 类型n
）
partition by range/list/hash (表字段1) interval (表字段2)
（
partition 分区名 values (value值)，
partition 分区名 values (value值)
）；

观察自动创建的分区：
select table_name,partition_name from user_tab_partitions where

table_name='intervalpart';

 

5.增加单个分区语句：
alter table 表名 add partition 分区名 values (value值)；
例子：ALTER TABLE list_example ADD PARTITION   part04   VALUES ('TE');

6.增加单个分区表value值：
alter table 表名 modify partition 分区名 add values (value值)；
例子：ALTER TABLE list_example MODIFY PARTITION   part04   ADD VALUES('MIS');

7.删除单个分区表value值：
alter table 表名 modify partition 分区名 drop values (value值)；
例子：ALTER TABLE  list_example  MODIFY  PARTITION  part04  DROP  VALUES('MIS');

8.增加sbupartition
alter table 表名 modify partition 分区名 add subpartition 辅助分区名；
例子：ALTER TABLE range_hash_example MODIFY PARTITION part_1 ADD SUBPARTITION part_1_sub_4;

9.删除分区：
alter table 表名 drop partition 分区名；
例子：ALTER TABLE ... DROP PARTITION part_name;

10.转换分区：(将分区表转换成非分区表)
alter table 表名1 exchange partition 分区名 with table 表名2；
例子：ALTER TABLE hash_example EXCHANGE PARTITION part02 WITH TABLE hash_part02;

11.查询整个分区表

select * from user_tab_partitions;

12.查询表分区数据：
select * from 表名 partition (分区名)；