oracle分区简介

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

        Oracle分区对于大型表（大数据量）非常有用，分区的作用主要有：

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

创建分区

        Oracle分区也是通过create table 命令组成，但对表进行分区时，得考虑一个字段作为分区建，通常按值的范围来划分分区，这里按照时间进行分区。

  1. -- create table
  2. create table student.score
  3. (
  4.   scoreid VARCHAR2(18) not null,
  5.   stuid VARCHAR2(11),
  6.   courseid VARCHAR2(9),
  7.   score    NUMBER,
  8.   scdate   DATE
  9. )
  10. partition by range(scdate)(
  11. partition p_score_2018 values less than (TO_DATE('2019-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss'))
  12.  TABLESPACE TS_2018,
  13. partition p_score_2019 values less than (TO_DATE('2020-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss'))
  14.  TABLESPACE TS_2019,
  15. partition p_score_2020 values less than (MAXVALUE)
  16.  TABLESPACE TS_2020
  17. );
  18. -- Add comments to the table 
  19. comment on table STUDENT.SCORE
  20.   is '学生成绩表';
  21. -- Add comments to the columns 
  22. comment on column STUDENT.SCORE.scoreid
  23.   is '学生成绩id';
  24. comment on column STUDENT.SCORE.stuid
  25.   is '学生学号';
  26. comment on column STUDENT.SCORE.courseid
  27.   is '课程id(年度+上下学期+课程序列)';
  28. comment on column STUDENT.SCORE.score
  29.   is '成绩';
  30. comment on column STUDENT.SCORE.scdate
  31.   is '成绩录入时间';
  32. -- Create/Recreate primary, unique and foreign key constraints 
  33. alter table STUDENT.SCORE
  34.   add constraint PK_SCORE primary key (SCOREID)
  35.   using index 
  36.   tablespace USERS
  37.   pctfree 10
  38.   initrans 2
  39.   maxtrans 255
  40.   storage
  41.   (
  42.     initial 64K
  43.     next 1M
  44.     minextents 1
  45.     maxextents unlimited
  46.   );

        这里使用命令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分区对大型表（数据量大）有重大的性能提升，所以在表结构设计时，需要提前按照相关业务需求进行相应的改进。

在分区表上创建索引

        在实际生产环境中，为了进一步的优化大型表（大数据量集）的查询效率，这个时候得考虑在分区表上某个字段创建索引。。分区表的索引和普通表的索引本质上是一样的，都是利用空间换取时间的方式，通过存储索引块来增加查询效率。但是有不同地方是，Oracle分区表索引可以分为局部（分区）索引和全局索引之分。

分区索引创建

分区索引指的是在子分区当中按照某个字段建立索引，例如，上一章创建的学生成绩表中（score）,可以对学生学号创建local索引，即分区索引。

  1. create index idx_score_stuid on student.score(stuid)
  2. local
  3. (
  4. partition idx_score_stuid_1 tablespace TS_2018,
  5. partition idx_score_stuid_2 tablespace TS_2019,
  6. partition idx_score_stuid_3 tablespace TS_2020
  7. )

注意local关键字。在这个create index命令中没有指定范围，而是由local 关键字告诉Oracle为score表的每一个分区创建一个单独的索引，因此，每一个表分区对应着一个索引分区。每一个索引分区存储在不同的表空间上，可以大大提高I/O和查询效率。

全局索引

Oracle分区表也可以创建全局索引，全局索引和普通表的索引一样，是对整表的数据进行创建索引。例如，可以对学生成绩表的（score）的课程ID（COURSEID）创建全局索引

  1. create index STUDENT.IDX_SCORE_COURSEID 
  2. on STUDENT.SCORE (courseid)
  3. global;

虽然分区索引比全局索引更容易管理，而且在分区当中查询效率更高，但是全局索引在全表进行唯一性检索时的速度可能会比局部索引更快，因为全局检索唯一性时，需要跨区。

注意：不能为子分区创建全局索引。

文档更新时间: 2020-10-20 10:23   作者：张尚