Oracle表分区概念和实战

YYOracle 11g分区表创建（自动按年、月、日分区）

现在有一张回单表，每天以十万数据的文件形式推送，定时读取文件写入回单数据，而且是只增不该。所以表数据会越来越多，所以使用分区来进行提高查询性能，提供维护性能。

概念

一、为什么使用分区

1、一般一张表超过2G的大小，ORACLE是推荐使用分区表的。

2、这张表主要是查询，而且可以按分区查询，只会修改当前最新分区的数据，对以前的不怎么做删除和修改。

3、数据量大时查询慢。

4、便于维护，可扩展：11g 中的分区表新特性：Partition（分区）一直是 Oracle 数据库引以为傲的一项技术，正是分区的存在让 Oracle 高效的处理海量数据成为可能，在 Oracle 11g 中，分区技术在易用性和可扩展性上再次得到了增强。

5、与普通表的 sql 一致，不需要因为普通表变分区表而修改我们的代码。

二、如何按年、月、日自动分区

A.按年创建

   numtoyminterval(1, 'year')      

--按年创建分区表
create table test_part
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'year'))
(partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));

--创建主键
alter table test_part add constraint test_part_pk primary key (ID) using INDEX;
-- Create/Recreate indexes 
create index test_part_create_time on TEST_PART (create_time);

B.按月创建

        numtoyminterval(1, 'month')

--按月创建分区表
create table test_part
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));

--创建主键
alter table test_part add constraint test_part_pk primary key (ID) using INDEX;

C.按日创建

        NUMTODSINTERVAL(1, 'day')

--按天创建分区表
create table test_part
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL(1, 'day'))
(partition part_t01 values less than(to_date('2018-11-12', 'yyyy-mm-dd')));

--创建主键
alter table test_part add constraint test_part_pk primary key (ID) using INDEX;

D.按周创建

        NUMTODSINTERVAL (7, 'day')        

--按周创建分区表
create table test_part
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL (7, 'day'))
(partition part_t01 values less than(to_date('2018-11-12', 'yyyy-mm-dd')));

--创建主键
alter table test_part add constraint test_part_pk primary key (ID) using INDEX;

测试：

--查询当前表有多少分区
select table_name,partition_name from user_tab_partitions where table_name='TEST_PART';

--查询这个表的某个（SYS_P21）里的数据
select * from TEST_PART partition(SYS_P21);

三.默认分区

partition part_t01 values less than(to_date('2018-11-01', 'yyyy-mm-dd'))。

表示小于 2018-11-01 的都放在 part_t01 分区表中。

四.给已有表分区

-- 1. 重命名
alter table test_part rename to test_part_temp;

-- 2. 创建 partition table
create table test_part
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t1 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));


-- 3. 创建主键
alter table test_part add constraint test_part_pk_1 primary key (ID) using INDEX;

-- 4. 将 test_part_temp 表里的数据迁移到 test_part 表中
insert into test_part_temp select * from test_part;

-- 5. 为分区表设置索引
-- Create/Recreate indexes 
create index test_part_create_time_1 on TEST_PART (create_time); 

-- 6. 删除老的 test_part_temp 表
drop table test_part_temp purge;

-- 7. 作用是：允许分区表的分区键是可更新。
-- 当某一行更新时，如果更新的是分区列，并且更新后的列植不属于原来的这个分区，
-- 如果开启了这个选项，就会把这行从这个分区中 delete 掉，并加到更新后所属的分区，此时就会发生 rowid 的改变。
-- 相当于一个隐式的 delete + insert ，但是不会触发 insert/delete 触发器。
alter table test_part enable row movement;

实战

自己手动实现分区

现在有一张回单表yy_receipt_detail

对已有表的操作进行分区

分区前查看要表分区的结构

select dbms_metadata.get.ddl ('TABLE','YY_RECEIPT_DETAIL')  FROM DUAL;

alter table yy_receipt_detail rename to yy_receipt_detail_temp;

为了方便就不列举那么多字段

CREATE TABLE YY_RECEIPT_DETAIL (

MCH_ID VARCHAR2 (20) NOT NULL,

BILL_DATE VARCHAR2 (20) NOT NULL,

TRADE_ID VARCHAR2 (20) NOT NULL,

TRADE_TYPE VARHCAR (20) NOT NULL,

......

)

PARTITION BY RANGE (BILL_DATE)

(PARTITION P_1 VALUES LESS THAN ('2021-01-01'),

PARTITION P_2 VALUES LESS THAN ('2021-02-01'),

PARTITION P_3 VALUES LESS THAN ('2021-03-01'),

PARTITION P_4 VALUES LESS THAN ('2021-04-01'));

--增加主键

ALTER TABLE YY_RECEIPT_DETAIL ADD PRIMARY KEY  ("TRADE_ID", "TRADE_TYPE");

CREATE INDEX YY_RECEIPT_DETAIL_INDEX0 ON YY_RECEIPT_DETAIL (CREATE_STAMP) LOCAL;

注：增加索引要加local

插入数据：

INSERT INTO YY_RECEIPT_DETAIL SELECT * FROM YY_RECEIPT_DETAIL_TEMP;

查询数据：

SELECT * FROM YY_RECEIPT_DETAIL PARTITION(P_1);

sql脚本：

-- 创建备份表
create table yy_receipt_detail_20220224 as select * from yy_receipt_detail;
-- 备份数据插入
alter table yy_receipt_detail rename to yy_partition_temp;
-- 为临时表增加分区字段
alter table yy_partition_temp add PARTITON_TIME varchar2(64);
-- 为临时表进行赋值
update yy_parition_temp set PARTITION_TIME = SUBSTR(CREATE_STAMP, 0, 19);

--创建分区
create table yy_receipt_detail (
mch_id varchar2(64) not null,
........
partition_time varchar2 (64))
partition by range (partiton_time)(
partiton p_20210101 values less than ('2021-01-01 23:59:59'),
.......
parition p_20990101 values less than ('2099-01-01 23:59:59'));
-- 查询是否完成分区
select * from user_tab_partitions where table_name ='yy_receipt_detail';

--增加局部主键索引
alter table yy_receipt_detail add constraint pk_yy_receipt_detail primary key (trade_id, trade_type, partition_time) using index local;

--如果对主键不要求 可以使用唯一索引
create unique index yy_receipt_detail_index0 on yy_receipt_detail (trade_id, trade_type,partition_time) local;

--数据插入
insert into yy_receipt_detail select * from yy_partition_temp;

--查询数据是否加入到分区
select * from yy_receipt_detail partition(p_20210101);


--注意：
如果在创建索引的时候 如果想要创建主键索引或者唯一索引的时候 需要将分区字段作为其中的条件，如果是想要做一般索引 可以不用使用分区字段 否则的获取会报错

目前是使用手动创建分区
如果到了峰值 需要手动增加分区 并且将峰值的分区插入到增加的分区中

--删除分区：
alter tbale yy_receipt_detail drop parttion(p_20990101);
-- 增加分区
alter table yy—_receipt_detail add parttion p_20550101 values less than ('2055-01-01 23:59:59');