Oracle 分区表学习笔记

一、基本概念

  表空间：是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表， 所以称作表空间。
  分区表：当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。

二、具体作用

  1、提高数据可用性，改善语句性能（多针对OLAP系统）

  Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。

  2、方便管理

  分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。

三、适用场景

  1、表数据量大于2GB时应该考虑使用分区。

  2、包含历史数据的表，其中新数据被添加到最新的分区中。一个典型的例子是历史表，其中只有当前月份的数据是可更新的，其他11个月是只读的。

四、分区类型

1、范围分区（Range）

  将数据基于指定的分区键映射到每一个分区中。这种分区方式最为常用，且常常采用日期作为分区键。注意：

• 每一个分区都需要有一个 values less then 子句，它指定了该分区的上限值（即该分区能接受的分区键的最大值）。记录里分区键的值小于这个上限值时，该记录会被放入该分区；而当记录里分区键的值等于或大于这个上限值时该记录会被放入下一个上限值更高的分区中。
• 所有分区里，除了第一个分区，其他分区其实都有一个隐式的下限值（即该分区能接受的分区键的最小值），这个下限值就是上一个分区的上限值。
• 在最后一个分区中，可定义上限值为 maxvalue（该值可理解为所有分区中的一个最大上限值，包括空值），当记录分区键的值大于之前所有分区的上限值时，这条记录会被放入这最后一个分区中。

创建表语句：

create table emp(
  empno    number(4) primary ley,
  ename    varchar2(10) not null,
  hiredate date
)partition by range (hiredate)(
  partition p2001 values less than (to_date('2020-02', 'yyyy-mm')) tablespace users,
  partition p2002 values less than (to_date('2020-03', 'yyyy-mm')) tablespace users,
  partition p2003 values less than (to_date('2020-04', 'yyyy-mm')) tablespace users,
  partition p2004 values less than (to_date('2020-05', 'yyyy-mm')) tablespace users,
  partition p2005 values less than (to_date('2020-06', 'yyyy-mm')) tablespace users,
  partition p2006 values less than (to_date('2020-07', 'yyyy-mm')) tablespace users,
  partition p2007 values less than (to_date('2020-08', 'yyyy-mm')) tablespace users,
  partition p2008 values less than (to_date('2020-09', 'yyyy-mm')) tablespace users,
  partition p2009 values less than (to_date('2020-10', 'yyyy-mm')) tablespace users,
  partition p2010 values less than (to_date('2020-11', 'yyyy-mm')) tablespace users,
  partition p2011 values less than (to_date('2020-12', 'yyyy-mm')) tablespace users,
  partition p2012 values less than (to_date('2021-01', 'yyyy-mm')) tablespace users
  );

2、散列分区（Hash）（待补充）

3、列表分区（List）（待补充）

4、组合分区（Range – Hash   或者   Range - List）（待补充）

五、常用管理语句

  1、增加分区

alter table emp add partition p2101 values less than (to_date('2021-02', 'yyyy-mm')) tablespace users;

  2、删除分区

alter table emp drop partition p2101;

  3、清空分区

alter table emp truncate partition p2101;

  4、查询某个分区数据

select empno, ename, hiredate from emp partition (p2101);

  5、查询所有分区数据

select empno, ename, hiredate from emp;

  6、查询当前用户所有的分区表详细情况

select t.table_name,
       t.partition_position,
       t.partition_name,
       t.num_rows
  from user_tab_partitions t

  7、查询当前数据库所有的分区表情况

select t.table_owner,
       t.table_name,
       t.partition_position,
       t.partition_name,
       t.num_rows
  from dba_tab_partitions t

参考资料：

1、oracle表空间表分区详解及oracle表分区查询使用方法(转+整理) - 伊凡 - 博客园

2、Oracle分区表 - Dreamer-1 - 博客园