欢迎大家来到IT世界,在知识的湖畔探索吧!

oracle 分表分区

一、 查询表所占存储空间

每张表都是作为“段”来存储的，可以通过user_segments视图查看其相应信息。 段（segments）的定义：如果创建一个堆组织表，则该表就是一个段。

SELECT segment_name AS TABLENAME,BYTES FROM user_segments WHERE segment_name='表名';

欢迎大家来到IT世界,在知识的湖畔探索吧!

解释： segment_name 就是要查询的表名（大写），BYTES 为表存储所占用的字节数。本sql的意思就是查询出表名和表所占的存储空间大小。

二、 分表

如果历史表中存储了很多年的数据，会造成严重的数据冗余。那如果将历史表分表存储，比如每年创建一个表，数据存储到对应的年表中，必定会减少很多数据量。

三、 分区

1. 基础

Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。
Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。
When to Partition a Table什么时候需要分区表，官网的2个建议如下：

• Tables greater than 2GB should always be considered for partitioning.
• 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.

在oracle 10g中最多支持：1024k-1个分区： Tables can be partitioned into up to 1024K-1 separate partitions

2. 分区优点

减少SQL操作的数据量，从而提升查询效率。表分区后，逻辑上仍然是一张表，只不过将表中的数据在物理上存放到多个表空间上。这样在查询数据时，会查询相应分区的数据，避免了全表扫描。

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

3. 分区类型

• 水平分区
  就是对行进行分区，举个例子来说，就是一个表中有1000万条数据，每100万条数据划一个分区，这样就将表中数据分到10个分区中去。水平分区要通过某个特定的属性列进行分区，比如Date时间。
• 垂直分区
  通过对表垂直划分来减少表的宽度，从而提升查询效率。比如一个学生表中，有他相关的信息列，还有论文列以CLOB存储。这些以CLOB存储的论文并不会经常被访问到，这时候就要把这些不经常使用的CLOB划分到另一个分区，需要访问时再调用它。

4. 分区方法

• 1） Range分区
  Range分区是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。
  如按照时间划分，2010年1月的数据放到a分区，2月的数据放到b分区，在创建的时候，需要指定基于的列，以及分区的范围值。
  在按时间分区时，如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。 create table pdba (id number, time date) partition by range (time) ( partition p1 values less than (to_date(‘2010-10-1’, ‘yyyy-mm-dd’)), partition p2 values less than (to_date(‘2010-11-1’, ‘yyyy-mm-dd’)), partition p3 values less than (to_date(‘2010-12-1’, ‘yyyy-mm-dd’)), partition p4 values less than (maxvalue) )
• 2） Hash分区
  对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。
• 3） List分区
• 4） 组合分区
  如果某表按照某列分区之后，仍然较大，或者是一些其它的需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式。

四、 使用ORACLE在线重定义将普通表改为分区表

将普通表转换成分区表有4种方法：

• Export/import method
• Insert with a subquery method
• Partition exchange method
• DBMS_REDEFINITION
  另外，INTERVAL分区是Oracle11g新增的特性，它是针对Range类型分区的一种功能拓展。对连续数据类型的Range分区，如果插入的新数据值与当前分区均不匹配，Interval-Partition特性可以实现自动的分区创建。 INTERVAL分区：由range分区派生而来，以定长宽度创建分区（比如年、月、具体的数字（比如100、500等）），分区字段必须是number或date类型。用户其实根本不用关心其属于哪个分区，也感觉不到，Oracle会自动管理并使其发挥分区的作用。 具体参考：https://www.cnblogs.com/flowerszhong/p/4535206.html
  此处主要讲解在线重定义：DBMS_REDEFINITION。

1、首先建立测试表，并插入测试数据：

欢迎大家来到IT世界,在知识的湖畔探索吧!create table myPartition(id number,code varchar2(5),identifier varchar2(20));
insert into myPartition values(1,'01','01-01-0001-000001');
insert into myPartition values(2,'02','02-01-0001-000001');
insert into myPartition values(3,'03','03-01-0001-000001');
insert into myPartition values(4,'04','04-01-0001-000001');
commit;
alter table myPartition add constraint pk_test_id primary key (id);

2.检查下这张表是否可以在线重定义，无报错表示可以，报错会给出错误信息：

--管理员权限执行begin
SQL> exec dbms_redefinition.can_redef_table('scott', 'myPartition');
PL/SQL procedure successfully completed
–管理员权限执行end

3. 建个和源表表结构一样的分区表，作为中间表：

欢迎大家来到IT世界,在知识的湖畔探索吧!create table t_temp(id number,code varchar2(5),
identifier varchar2(20)) partition by range(id)(  
          partition TAB_PARTOTION_01 values less than (2),  
          partition TAB_PARTOTION_02 values less than (3),  
          partition TAB_PARTOTION_03 values less than (4),  
          partition TAB_PARTOTION_04 values less than (5),  
          partition TAB_PARTOTION_OTHER values less THAN (MAXVALUE)  
);

alter table t_temp add constraint pk_temp_id2 primary key (id);

技巧：使用Navicat导出源表的结构sql，改下源表名为新表名，在命令行上跑这些sql语句即可。

4.启动在线重定义：

--管理员权限执行sql命令行执行
exec dbms_redefinition.start_redef_table('scott', 'myPartition', 't_temp');
--管理员权限执行sql命令行执行

这里dbms_redefinition包的start_redef_table模块有3个参数，分别是SCHEMA名字、原表的名字、中间表的名字。

5.启动在线重定义后，中间表就可以查到原表的数据。

select * from t_temp;

6.由于在生成系统中，在线重定义的过程中原数据表可能会发生数据改变，向原表中插入数据模拟数据改变。

insert into myPartition values(5,'05','05-01-0001-000001');
commit;

7.此时原表被修改，中间表并没有更新。

select * from myPartition;
select * from t_temp;

8.使用dbms_redefinition包的sync_interim_table模块刷新数据后，中间表也可以看到数据更改

--管理员权限执行sql命令行执行,同步两边数据
exec dbms_redefinition.sync_interim_table('scott', 'myPartition', 't_temp');
--管理员权限执行sql命令行执行

查询同步后的两边数据是否一致：

select * from myPartition;
select * from t_temp;

9.结束在线重定义

--管理员权限执行sql命令行执行，结束重定义
exec dbms_redefinition.finish_redef_table('scott', 'myPartition', 't_temp');
--管理员权限执行sql命令行执行

10.验证数据

select * from myPartition;
select * from t_temp;

11.查看各分区数据是否正确

-- table_name必须大写
select table_name, partition_name from user_tab_partitions where table_name = 'myPartition';

select * from myPartition partition(TAB_PARTOTION_01);

12.在线重定义后，中间表已经没有意义，可留作备份或者删掉

drop table t_temp purge; 

13.转成分区表后，原普通表的增删改查语句可以一成不动，可以平稳过渡。

*注意: * 如果执行在线重定义的过程中出错,可以在执行dbms_redefinition.start_redef_table之后到执行dbms_redefinition.finish_redef_table之前的时间里执行：DBMS_REDEFINITION.abort_redef_table(‘test’, ‘t’, ‘t_new’)以放弃执行在线重定义。

五、 本地索引和全局索引

分区表创建好了之后，如果需要最大化分区表的性能就需要结合索引的使用，分区表有两种索引：本地索引和全局索引。既然存在着两种的索引类型，相信存在即合理。既然存在就会有存在的原因，也就是在特定的场景中就更能发挥出索引的性能的

• 当查询的条件是需要跨分区查询内容的时候，LOCAL INDEX的效率比GLOBAL INDEX的效率要低
• 如果查询的条件是在单个分区里面查询的时候，那么LOCAL INDEX的效率比GLOBAL INDEX的效率要高。
  参考链接: https://blog.csdn.net/sunbocong/article/details/80648209