Oracle range时间范围自动分区的创建方式

Oracle range时间范围自动分区

Oracle11G之前的版本，分区大概分为4种：范围分区：range 、列表分区：list、哈希分区：hash、复合分区：range+list or hash)；

分区的优点：

1.改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。

2.增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;

3.维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可;可单独备份某分区；

4.均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能。

分区的缺点：

1.已经存在的表不能直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。

分区的特殊性：

1.含有 LONG、LONGRAW 数据类型的表不能进行分区，一般分区类型为varchar,varchar2,number,date2.每个表的分区或子分区数的总数不能超过 1023 个。

使用分区的场景：

1.单表过大，当表大小超过2G，或对于OLTP（On-Line Transaction Processing联机事务处理过程(OLTP)，也称为面向交易的处理过程，其基本特征是前台接收的用户数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果，是对用户操作快速响应的方式之一）系统，表的记录超过1000万。

2.历史数据需要分离出来，新的数据被加到新的分区中。

3.表数据被使用时特征明显，例如当年，整月之类。

4.基于这类表大部分的查询都是只查询其中一部分数据。

5.按时间段删除成批的表数据。

6.经常执行并行查询的表

7.对其中一部分分区表数据可用性要求高

1.创建测试表. 分区字段CDAT NUMBER()

---建表
create table FQ_TEST 
(contract_no varchar2(20),
loan_st varchar2(8),
amt  decimal(20,6)
);
--创建自增序列contract_no
create sequence SEQ_FQTEST_con 
minvalue 1 
maxvalue 999999   
increment by 1    
start with 1;
--创建触发器
create or replace trigger TRIG_FQTEST_con
before insert on FQ_TEST /*触发条件：当表FQ_TEST执行插入操作时触发此触发器*/
for each row   /*对每一行都检测是否触发*/
begin
/*触发后执行的动作，在此是取得序列SEQ_FQTEST_con的下一个值插入到表BC_ES_IK_HOT_WORD中的id字段中*/
select SEQ_FQTEST_con.nextval into :new.CONTRACT_NO from dual;
end;

2.执行增加分区语句

ALTER TABLE FQ_TEST ADD PARTITION P1 VALUES LESS THAN (20220131); 

提示报错：

经过分析：提示ORA-14501: object is not partitioned对象未分区。

3.创建新的分区测试空表

--DROP  TABLE FQ_TEST_partition;
create table FQ_TEST_partition
(contract_no varchar2(20),
loan_st varchar2(8),
amt  decimal(20,6)
)
partition by range (loan_st)
(
partition P1 values less than (20220131)
);

报错：

分析：Oracle未启用Partitioning功能，查询当前版本是否支持

SELECT VALUE FROM V$OPTION WHERE UPPER(PARAMETER)= 'PARTITIONING';

值为FALSE则不支持。注意标准版是不支持分区操作的，企业版才支持。

----------使用企业版ORACLE进行测试分区以及分区自增长---------

1.使用固定的分区，每月新增数据需要手工增加对应分区

1.1创建相应固定分区表

----创建分区表
drop table    rp_report_test_xy;
-- Create table
create table rp_report_test_xy
(
contract_code           VARCHAR2(300),
cdat                     NUMBER(8)
)
partition by range(cdat)-- interval (NUMTOYMINTERVAL(1,'MONTH'))
(
partition p1 values less than('20150101'),
partition p2 values less than('20220201'),
partition p3 values less than('20220501')
);

1.2 将原有表中2022年底数据插入（原表有202201-202205的月底数据）

报错原因：由于原表有20220531的数据不在已有的分区内插入数据报错，将数据限制在20220501之前重新插入。

---查询现有分区
select * from user_tab_partitions where table_name='RP_REPORT_TEST_XY'

---查询插入的数据具体分区
select distinct cdat from rp_report_test_xy  partition(P2);---20220131
select distinct cdat from rp_report_test_xy  partition(P3)---20220430,20220228,20220331

1.3 新增一份分区

ALTER TABLE rp_report_test_xy ADD PARTITION P4 VALUES LESS THAN('20220531');

1.4 插入大于20220501的数据实际就是原表中CDAT是20220531的数据

1.5 删除新增的分区，再次新建分区日期是20220601

--删除分区
ALTER TABLE rp_report_test_xy DROP PARTITION P4;
---重新插入分区
ALTER TABLE rp_report_test_xy ADD PARTITION P4 VALUES LESS THAN('20220601');

1.6 再次插入大于20220501的数据实际就是原表中CDAT是20220531的数据

--查询当前新分区数据
select distinct cdat from rp_report_test_xy  partition(P4)---20220531

2.自增长分区

2.1创建一张表，分区字段为CDAT,字段类型为NUMBER;

原有未分区的表数据基数为3000多万

执行报错：

将创建表语句中CDAT由NUMBER改为DATE则创建成功。

2.2使用insert语句将原未分区表数据插入分区测试表。

---查询现有分区
select * from user_tab_partitions where table_name='RP_REPORT_TEST_XY'

插入数据：注意原NUMBER在插入时要TO_DATE转换。原表取CDAT是2022年的所有数据插入。

--查看当前分区的数据
select * from  rp_report_test_xy partition(SYS_P76);

发现20220131的数据划分到了20220201的区间，20220228划分到了20220301的区间。

--查看当前分区的数据
select * from  rp_report_test_xy partition(SYS_P76);

结果 ：cdat是20221130的数据。

--删除某分区
ALTER TABLE  rp_report_test_xy DROP PARTITION SYS_P76;

关于ORACLE分区（range时间范围自动分区）的文章就介绍至此，更多相关oracle分区内容请搜索编程教程以前的文章，希望以后支持编程教程！