Oracle分区详解和创建

Oracle在实际业务生产环境中,经常会遇到随着业务量的逐渐增加,表中的数据行数的增多,Oracle对表的管理和性能的影响也随之增大。对表中数据的查询、表的备份的时间将大大提高,以及遇到特定情况下,要对表中数据进行恢复,也随之数据量的增大而花费更多的时间。这个时候,Oracle数据库提供了分区这个机制,通过把一个表中的行进行划分,归为几部分。可以减少大数据量表的管理和性能问题。利用这种分区方式把表数据进行划分的机制称为表分区,各个分区称为分区表。


Oracle分区对于大型表(大数据量)非常有用,分区的作用主要有:

1、改善大型表的查询性能,因为可以通过查询对应分区表中对应的数据,而不需要查询整个表。

2、表更容易管理,因为分区表的数据存储在各个分区中,所以可以按照分区建,来管理对应分区当中的数据,可以按照分区加载和删除其中的数据,比在不分区情况下,更容易管理数据。以及在特定的事故情况下,通过备份好的分区,可以快速恢复对应分区当中的数据,也不需要对全表数据进行恢复。


Oracle创建分区

既然,Oracle分区有如此好处,我们在这里通过一个例子来讲解如何创建分区。在我们的学生信息系统案例当中,学生成绩表(SCORE)会随着学生的增多和课程的增多,表中的数据量会越来越大,所以可以考虑创建分区表来解决这个问题。

Oracle分区也是通过create table命令组成,但是对表进行分区时,得考虑一个字段作为分区建,通常按值的范围来划分分区,所以这里考虑使用成绩的录入时间进行分区。具体代码如下:

-- Create table
create table STUDENT.SCORE
(
  scoreid  VARCHAR2(18) not null,
  stuid    VARCHAR2(11),
  courseid VARCHAR2(9),
  score    NUMBER,
  scdate   DATE
)
partition by range(scdate)(
partition p_score_2018 values less than (TO_DATE('2019-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss'))
 TABLESPACE TS_2018,
partition p_score_2019 values less than (TO_DATE('2020-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss'))
 TABLESPACE TS_2019,
partition p_score_2020 values less than (MAXVALUE)
 TABLESPACE TS_2020
);
-- Add comments to the table 
comment on table STUDENT.SCORE
  is '学生成绩表';
-- Add comments to the columns 
comment on column STUDENT.SCORE.scoreid
  is '学生成绩id';
comment on column STUDENT.SCORE.stuid
  is '学生学号';
comment on column STUDENT.SCORE.courseid
  is '课程id(年度+上下学期+课程序列)';
comment on column STUDENT.SCORE.score
  is '成绩';
comment on column STUDENT.SCORE.scdate
  is '成绩录入时间';
-- Create/Recreate primary, unique and foreign key constraints 
alter table STUDENT.SCORE
  add constraint PK_SCORE primary key (SCOREID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

这里使用命令partition by range对成绩的录入日期(scdate)进行分区,如录入日期小于2019年的会被放入分区p_score_2018当中,2019年数据会被放入p_score_2019这个分区当中,大于2019年数据都会被放入到p_score_2020这个分区当中。

这里不必为最后一个分区指定最大值,maxvalue关键字会告诉Oracle使用这个分区来存储前面几个分区当中不能存储的数据。

上面实例展示的是Oracle按照值的范围进行分区,Oracle还支出散列分区,通过某一个字段,把表中的数据散列在各个分区中。可以通过关键字partition by hash,可以把分区散列到不同的表空间当中。

Oracle还支持列表分区(partition by list),它是通过按照指定分区建的值归并到各个分区,其实这里学生成绩表也可以考虑按照课程进行列表分区。

总结:Oracle分区对大型表(数据量大)有重大的性能提升,所以在表结构设计时,需要提前按照相关业务需求进行相应的改进。