【DB笔试面试470】分区表有什么优点？分区表有哪几类？如何选择用哪种类型的分区表？

小麦苗DBA宝典

发布于 2019-09-30 16:48:15

1.2K0

发布于 2019-09-30 16:48:15

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

题目部分

分区表有什么优点？分区表有哪几类？如何选择用哪种类型的分区表？

答案部分

当表中的数据量不断增大时，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。当对表进行分区后，在逻辑上，表仍然是一张完整的表，只是将表中的数据在物理上可能存放到多个表空间或物理文件上。当查询数据时，不至于每次都扫描整张表。Oracle可以将大表或索引分成若干个更小、更方便管理的部分，每一部分称为一个分区，这样的表称为分区表。SQL语句使用分区表比全表能提供更好的数据处理与访问的性能。即使某些分区不可用，其它分区仍然可用，这叫做分区独立性。

分区表的一些限制条件：

①　簇表不能进行分区。

②　不能分割含有LONG或LONG RAW列的表。

③　索引组织表不能进行范围分区。

何时考虑分区？

对大表进行分区，将有益于大表操作的性能和大表的数据的维护。官方文档说通常当表的大小超过2GB，或对于OLTP系统，当表的记录超过1000万时，都应考虑对表进行分区。

分区表有什么优点？

分区表有如下的优点：

①　增强可用性：如果表的一个分区由于系统故障而不能使用，那么表的其余好的分区仍可以使用。

②　减少关闭时间：如果系统故障只影响表的一部分分区，那么只有这部分分区需要修复，可能比整个大表修复花的时间更少。

③　维护轻松：单独管理每个分区比管理单个大表要轻松得多。

④　均衡I/O：可以把表的不同分区分配到不同的磁盘来平衡I/O，改善性能。

⑤　改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快。

⑥　分区对用户透明，最终用户感觉不到分区的存在。

有哪些类型的分区？如何选择用哪种类型的分区表？

Oracle的分区可以分为：

● 范围分区（RANGE PARTITION）

● 哈希分区（HASH PARTITION）

● 列表分区（LIST PARTITION）

● 引用分区（REFERENCE PARTITION）

● 复合分区（组合分区）

● INTERVAL分区（间隔分区）

● 系统分区

INTERVAL分区和系统分区是Oracle 11g的新特性，由于篇幅原因，本书中不讲解这2个分区，读者可自行查阅官方文档进行学习。尤其对于INTERVAL分区在生产环境中还是比较实用的。下面作者将对范围、哈希、列表和复合分区分别进行讲解。

1、RANGE（范围）分区

在如下几种情况下会使用到范围分区：

（1）频繁地被一个时间范围谓词扫描。

（2）维护一个时间滚动的数据窗口（Rolling Window Of Data）。

（3）保存历史数据的表。

例如，在下面的例子中，给数据表SALE_DATA在时间字段SALES_DATE上按照每个月一个分区的方式来创建一个范围分区。

CREATE TABLE SALE_DATA
(SALE_ID NUMBER(5),
SALESMAN_NAME VARCHAR2(30),
SALES_AMOUNT NUMBER(10), 
SALES_DATE DATE)
PARTITION BY RANGE(SALES_DATE)
(
PARTITION SALES_2009_1 VALUES LESS THAN(TO_DATE('01/02/2009','DD/MM/YYYY')),
PARTITION SALES_2009_2 VALUES LESS THAN(TO_DATE('01/03/2009','DD/MM/YYYY')),
PARTITION SALES_2009_3 VALUES LESS THAN(TO_DATE('01/04/2009','DD/MM/YYYY')),
PARTITION SALES_2009_4 VALUES LESS THAN(TO_DATE('01/05/2009','DD/MM/YYYY')),
PARTITION SALES_2009_5 VALUES LESS THAN(TO_DATE('01/06/2009','DD/MM/YYYY')),
PARTITION SALES_2009_6 VALUES LESS THAN(TO_DATE('01/07/2009','DD/MM/YYYY')),
PARTITION SALES_2009_7 VALUES LESS THAN(TO_DATE('01/08/2009','DD/MM/YYYY')),
PARTITION SALES_2009_8 VALUES LESS THAN(TO_DATE('01/09/2009','DD/MM/YYYY')),
PARTITION SALES_2009_9 VALUES LESS THAN(TO_DATE('01/10/2009','DD/MM/YYYY')),
PARTITION SALES_2009_10 VALUES LESS THAN(TO_DATE('01/11/2009','DD/MM/YYYY')),
PARTITION SALES_2009_11 VALUES LESS THAN(TO_DATE('01/12/2009','DD/MM/YYYY')),
PARTITION SALES_2009_12 VALUES LESS THAN(TO_DATE('01/01/2010','DD/MM/YYYY')),
PARTITION SALES_2010_1 VALUES LESS THAN(TO_DATE('01/02/2010','DD/MM/YYYY'))
);

这样就可以非常方便地来管理每个月的数据了。例如，要删除1年前的数据，只需要写一个JOB来做定时检查，如果发现有分区是1年以前的，那么就可以直接将它删除，这样的操作对用户是不可见的。

例如，要删除2009年第1个月的分区数据，就可以使用这样的命令：ALTER TABLE SALE_DATA DROP PARTITION SALES_2009_1;。

2、HASH（哈希）分区

HASH分区有如下的优点：

（1）提高了大表的高可用性和可管理性。

（2）可以避免数据倾斜，将数据均匀地分布在多个物理设备上，最大化I/O吞吐量。

（3）分区修剪和分区智能连接。

（4）要求分区键是高基数列。

（5）分区修剪不支持基于分区字段的范围查询，只支持等值查询或IN-LIST查询。

HASH分区能够很容易对数据进行分区，因为语法很简单，很容易实现。在下面这种情况下，使用HASH分区比RANGE分区更好：

（1）事先不知道需要将多少数据映射到给定范围的时候。

（2）分区的范围大小很难确定，或者很难平衡的时候。

（3） RANGE分区使数据得到不期望的聚集时。

（4）性能特性，如并行DML、分区修剪和分区连接很重要的时候。

下面的示例创建了一个HASH分区表：

CREATE TABLE T_HASH PARTITION BY HASH(OBJECT_ID) PARTITIONS 8 AS SELECT * FROM DBA_OBJECTS;

上面的SQL语句创建了一个8个分区的表T_HASH，分区类型为哈希分区，按照Oracle官方的要求，分区的数量应该是2的N次方为合适，例如2，4，8…，这里是8个分区。

从视图DBA_TAB_PARTITIONS中可以查询到分区的详细情况：

  SELECT *
  FROM   DBA_TAB_PARTITIONS D
  WHERE  D.TABLE_OWNER NOT IN ('SYS', 'SYSTEM') AND D.TABLE_NAME='T_HASH'
  ORDER  BY D.TABLE_NAME, D.PARTITION_POSITION, D.PARTITION_NAME;
SELECT PARTITION_NAME, COUNT(*)
FROM   USER_EXTENTS
WHERE  SEGMENT_NAME = 'T_HASH'
GROUP  BY PARTITION_NAME;
上面是每个分区物理上占用的EXTENTS数。
SELECT COUNT(*)
FROM   T_HASH PARTITION(SYS_P21)
UNION ALL
SELECT COUNT(*)
FROM   T_HASH PARTITION(SYS_P22)
UNION ALL
SELECT COUNT(*)
FROM   T_HASH PARTITION(SYS_P23)
UNION ALL
SELECT COUNT(*)
FROM   T_HASH PARTITION(SYS_P24)
UNION ALL
SELECT COUNT(*)
FROM   T_HASH PARTITION(SYS_P25)
UNION ALL
SELECT COUNT(*)
FROM   T_HASH PARTITION(SYS_P26)
UNION ALL
SELECT COUNT(*)
FROM   T_HASH PARTITION(SYS_P27)
UNION ALL
SELECT COUNT(*)
FROM   T_HASH PARTITION(SYS_P28);

在上面的例子中，不论是从EXTENT占用的空间数量，还是分区中的数据量，都可以看出，数据在各个分区上分布是非常均匀的。

3、列表分区

列表分区（LIST PARTITION）提供了一种按照字段的值来进行分区的方法，这种方法非常适合于有高重复率字段值的表。通过这种方法，可以非常方便地控制将某些特定的数值存放到一个分区。

列表分区有如下特点：

（1）列表值是离散的。

（2）列表值是无序的，例如：PARTITION PART_01 VALUES('A','E','F')等。

（3）分区键仅能包含一个列。

（4）列表值是低基数的。

下面的例子创建了一个列表分区：

CREATE TABLE SALES_LIST
(SALESMAN_ID NUMBER(5),
SALESMAN_NAME VARCHAR2(30),
SALES_STATE VARCHAR2(20),
SALES_AMOUNT NUMBER(10),
SALES_DATE DATE)
PARTITION BY LIST(SALES_STATE)
(PARTITION SALES_WEST VALUES('CALIFORNIA', 'HAWAII') ,
PARTITION SALES_EAST VALUES('NEW YORK', 'VIRGINIA', 'FLORIDA'),
PARTITION SALES_CENTRAL VALUES('TEXAS', 'ILLINOIS') ,
PARTITION SALES_OTHER VALUES(DEFAULT)
);

在字段SALES_STATE上创建了列表分区，同时设定了这样的对应关系：

SALES_WEST('CALIFORNIA','HAWAII')
SALES_EAST('NEWYORK','VIRGINIA', 'FLORIDA')
SALES_CENTRAL('TEXAS','ILLINOIS')

从分区的名字上就能猜出里面的内容了，例如SALES_WEST（西部地区）包含了“CALIFORNIA”和“HAWAII”，这对于内容重复率很高，并且可能有一些共性的字段，使用列表分区是有用处的，它有利于数据的管理，例如想导出或者备份西部地区的数据，可以直接操作SALES_WEST分区，非常方便。

4、复合分区

如果某表按照某列分区之后，仍然较大，或者是有一些其它的需求，那么还可以通过在分区内再建子分区的方式将分区再分区，即复合分区的方式。

复合分区在Oracle 11g之前有两种：RANGE-HASH与RANGE-LIST。需要注意的是其顺序，根分区只能是RANGE分区，子分区可以是HASH分区或LIST分区，而Oracle 11g在复合分区功能这块有所增强，又推出了RANGE-RANGE、LIST-RANGE、LIST-LIST和LIST-HASH，这就相当于除HASH外三种分区方式的笛卡尔形式都有了，即目前一共有6种分区，但根分区只能是RANGE分区或LIST分区。

在某些时候按照业务要求，上面的几种分区也可以按照一定的目的创建组合分区，或者叫子分区。下面的例子创建了一个范围-列表组合分区：

CREATE TABLE T_RANGE_LIST(ID INT,NAME VARCHAR2(100))
PARTITION BY RANGE(ID)
SUBPARTITION BY LIST(NAME)
(
PARTITION P1 VALUES LESS THAN(5)
(SUBPARTITION SP1 VALUES ('A,B,C'),
SUBPARTITION SP2 VALUES ('D,E,F')
),
PARTITION P2 VALUES LESS THAN(10)
(
SUBPARTITION SP3 VALUES ('A,B,C'),
SUBPARTITION SP4 VALUES ('D,E,F')
),
PARTITION P3 VALUES LESS THAN(15)
);
SELECT * FROM DBA_PART_TABLES D WHERE D.TABLE_NAME = 'T_RANGE_LIST';

对于海量数据的数据库设计，分区的设计非常重要。例如，对于一个大表，应该采用哪种类型的分区，对于以后数据库的性能和管理至关重要。

其实，范围分区、HASH分区和列表分区这三种分区的特点都非常明显，如下所示：

l 如果需要进行数据的过期化处理，那么范围分区基本上是唯一的选择。

l 如果需要数据的均匀分布，那么可以考虑使用HASH分区。

l 如果数据的值可以很好地对应于某个分区，那么就可以考虑使用列表分区。

在上面的原则基础上，再结合性能的影响因素，来最终确定使用哪种类型的分区。

如果选择的分区不能确保各分区内数据量的基本平均，那么这种分区方式有可能是不恰当的。比如对于RANGE分区，假设分了10个分区，而其中一个分区中的记录数占总记录数的90%，其它9个分区只占总记录数的10%，则这个分区方式就起不到数据平衡的作用。