当前位置：首页 > 数据库 > oracle > 详细页面

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

时间：2023-10-31来源：系统城装机大师作者：佚名

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

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

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

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

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

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 ---建表
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.执行增加分区语句

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

提示报错：

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

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

1
2
3
4
5
6
7
8
9
10 --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创建相应固定分区表

1
2
3
4
5
6
7
8
9
10
11
12
13
14 ----创建分区表
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之前重新插入。

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

1
2
3 ---查询插入的数据具体分区
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 新增一份分区

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

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

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

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