Oracle表分区_按步骤来，实测可用

什么是表分区？
     类似于磁盘分区，想象一下如果电脑硬盘不分区，所有文件都堆在一个磁盘下，在检索文件时就非常麻烦，浪费时间。数据库在检索数据时是同样的道理。分区就是将数据按照一定的划分规则分割成多组数据分别存放在不同的数据段内以加快数据检索速度。
为什么要进行表分区
     在定义中就可以知道，表分区的根本目的在于加快数据检索速度。由于数据被划分到不同的逻辑数据段内，因此在进行数据检索时就可以直接去对应的数据段内进行查询，一个逻辑数据段相对于整个表的数据量会小很多，同时在一定程度上能够减少数据库的I/O竞争，因此能够明显提升数据检索效率，数据量越大的表提升效果越明显。
何时进行表分区
     根据经验及Oracle推荐，有两方面的判断标准：1、表对象大小超过2G；2、表数据量超过3千万。这并不是一个死标准，Oracle官方也只推荐了表对象的大小2GB的阀值，但是根据我以往对全国对家银行系统巡检的经验来看，我一般自己拟定的是3GB，数据量其实也是根据表大小而定；这里说3千万是指常规表，比如有些表字段有2百多个，可能一千万数据就已经超过3GB了，那就推荐分区了；同样我也遇见过表中4个字段数据量2.3亿也才3.6G，而且对该表的查询速度并不慢。所以这个分区的评判标准并不是一成不变的，需要根据实际使用情况进行综合分析。总之，一个目的，就是要提升查询速度。
     上面讲了那么多就是为了能够更合理的使用表分区的功能，并不是说任何表都适合分区的，需要根据实际业务情况定夺。
如何进行表分区
     以下步骤全部经过生产数据库的验证，真实可靠。对于一些刚入门的猿们请严格按照步骤来实施，由于表分区要对表结构进行变更，因此数据备份是至关重要的。
     表分区有两种方案，Oracle只有对新创建的表才能分区，已有数据的表无法进行分区；但Oracle提供了在线重定义的方式能够实现有数据表的分区方案，其实在线重定义只是用Oracle自己的方式执行我们自己步骤的一种转换，操作步骤的实质还是一样的，在线重定义使用的是PL/SQL包的形式进行的，个人在这里不推荐。对于很多人来说纵使这种方式显得高大上但是自己看不到实际操作的DDL和DML心里还是有些虚的，除了错误也不容易捕捉。所以还是用最原始最笨拙的方法实现最让人放心，况且也并没有浪费很多时间。
     接下来就该进入代码环节，以下代码以一张生产库中的审计表(AUDIT_INFO)为例进行演示：

1. 导出表进行备份，防止数据丢失

exp <username>/<password>@<SID> file=audit_info.dmp log=test.log full=n rows=y buffer=10240000 tables=<username>.AUDIT_INFO

     由于我们生产库中用的是GBK的字符集，与Linux默认字符集UTF-8不匹配，所以会有一个EXP-00091: Exporting questionable statistics的错误，这个错误可以忽略。
2. 创建临时表，再次备份（多备无患）

create table AUDIT_INFO_BAK tablespace users as select * from AUDIT_INFO

3. 根据主键校验数据是否有缺失

select * from AUDIT_INFO_BAK aib where not exists (select 1 from AUDIT_INFO ai where aib.serialno=ai.serialno )

4. 根据分区规则提前统计分区后个分区中数据量，分区后用作数据对比（此处以表中一个时间字段作为分区维度，实际使用中以时间维多进行分区是最最最常见的分区规则，别的像什么按行分区、列表分区、散列分区。。。适用场景太少）

select substr(audittime,1,4),count(1) from AUDIT_INFO_bak group by substr(audittime,1,4) order by substr(audittime,1,4)

5. 删除原表，删除前备份原表DDL，里边会有索引、存储规则、序列等其他信息分区完成后还要使用

truncate table AUDIT_INFO
drop table AUDIT_INFO

6. 重建表（分区表）

create table AUDIT_INFO tablespace users partition by range(AUDITTIME)
(
partition AUDIT_INFO_2010 values less than ('2010/12/31 23:59:59') tablespace users,
partition AUDIT_INFO_2012 values less than ('2012/12/31 23:59:59') tablespace users,
partition AUDIT_INFO_2014 values less than ('2014/12/31 23:59:59') tablespace users,
partition AUDIT_INFO_2016 values less than ('2016/12/31 23:59:59') tablespace users,
partition AUDIT_INFO_MAX values less than (MAXVALUE) tablespace users 
) AS 
SELECT * FROM AUDIT_INFO_BAK

     注： 在创建分区表时需要设置行移动alter table AUDIT_INFO enable row movement;这是因为在进行数据更新时如果更新字段为分区字段，并且更新后该行数据不再原来的分区中，就会触发一次delete/insert操作，Oracle会删除原来分区中该条数据并在新的分区中插入，但是该操作不会触发触发器；如果不开启该选项则在更新时会报错。
7. 统计各分区表中数据量与第4步中查询到的数据量进行对比

select count(1) from AUDIT_INFO partition (AUDIT_INFO_2016)

8. 创建索引，表分区中索引有全局索引和本地索引之分。两种索引没有优劣之分，需要根据业务定夺使用哪种合适，如果在业务中还需要进行跨多个分区时间段的查询操作建议使用全局索引，如果业务场景中最多的是对单个分区内数据查询的建议使用本地索引

CREATE INDEX IDX1_AUDIT_INFO_LOCAL ON AUDIT_INFO(SERIALNO,AUDITTIME) LOCAL--本地索引
CREATE INDEX IDX1_AUDIT_INFO_GLOBAL ON AUDIT_INFO(SERIALNO,AUDITTIME,RECORDTYPE) GLOBAL--全局索引

9. 添加新分区，由于是按时间分区总是有超过分区维度的时间，因此对于maxvalue分区就需要考虑在一定时间后进行分割

ALTER TABLE AUDIT_INFO SPLIT PARTITION AUDIT_INFO_MAX AT ('2017/12/31 23:59:59') INTO (PARTITION AUDIT_INFO_2017,PARTITION AUDIT_INFO_MAX);--设置了MAXVALUE
ALTER TABLE AUDIT_INFO ADD PARTITION AUDIT_INFO_2017 VALUES LESS THAN ('2017/12/31 23:59:59') tablespace users; --未设置MAXVALUE

     后记：一个同事在使用表分区时用在了存储过程中，根据业务每个月要添加一个新分区，再删除一个最老的分区。由于时间一直在变化，因此他使用了SQL的动态参数绑定，部分代码片段形式如下：

declare 
  v_delmonth varchar2(30);
begin
  select concat('AUDIT_INFO_',TO_CHAR(ADD_MONTHS(TO_DATE('20191111','YYYYMMDD'),-23),'YYYY')) INTO v_delmonth from dual;   
  execute immediate 'ALTER TABLE AUDIT_INFO DROP PARTITION :1' using  v_delmonth;
end;

     在进行测试时一直会报一个ORA-14006:invalid partition name的错误，这是由于存储过程不支持DDL语句，Oracle也不推荐在存储过程中使用DDL；在DDL中是无法进行变量绑定的，因此要把SQL语句拼完整才能执行，应修改如下：

declare 
  v_delmonth varchar2(30);
  sqlstr varchar2(1000);
begin
  select concat('AUDIT_INFO_',TO_CHAR(ADD_MONTHS(TO_DATE('20191111','YYYYMMDD'),-23),'YYYY')) INTO v_delmonth from dual;
  sqlstr:='ALTER TABLE AUDIT_INFO DROP PARTITION '||v_delmonth;
  execute immediate sqlstr;
end;