oracle 表分区

Oracle 表分区详解

一、表分区及表空间的概念

1.      表空间：是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，因为存放的都是表，所以称为表空间

2.      表分区：当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时候就应该对表进行分区。表分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间（物理文件上），这样查询的时候就不用扫描全表

3.      分区键：分区键就是决定表中的数据行，属于哪一个分区的一组数据列，在执行DML操作时，ORACLE会根据分区键选择分区。

二、表分区的具体作用

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

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

什么时候使用分区表：

1.      表的大小超过2GB

2.      表中包含历史数据，新的数据被增加到新的分区中

3.      除了包含LONG 和 LONG RAW字段的表无法使用分区外，其他表均可以使用分区，包括包含CLOB字段的表

三、表分区的优缺点

优点：

1.      改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提交检索速度

2.      增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用

3.      维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可

4.      均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能

       缺点：

已经存在的表没有办法转换成分区表，不过Oracle提供了在线重定义表的功能

四、表分区的几种类型及操作方法

1.      范围分区（rang partition）

范围分区将数据基于范围映射到每个分区，这个范围是你在创建分区时指定的分区键指定的，这种分区方式是最为常用的，并且分区建经常采用日期

当使用范围分区时，需要考虑以下几个规则：

a.      每一个分区都必须有一个VALUES LESS THEN 子句，它指定了一个不包括在该分区中的上限值。分区建的任何值等于或者大于这个上线值都会被加入到下一个高一些的分区中。

b.      所有分区，除了第一个，都会有一个隐式的下限值，这个下限值就是当前分区的前一个分区的上限值

c.      在最高的分区中，MAXVALUE被定义，MAXVALUE代表了一个不确定的值，这个值高于其他分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUES LESS THEN 的值，同时包括空值

d.      范围分区的数据分布可能不均匀

例子：

CREATE TABLE TEST(

ID NUMBER

NAMEVARCHAR2(20),

AGE NUMBER)

PARTITION BY RANGE(ID)(

PARTITION PART_CUS1VALUES LESS THEN(10000) TABLESPACE CUS_TS01,

PARTITION PART_CUS2VALUES LESS THEN(20000) TABLESPACE CUS_TS02,

PARTITION PART_CUS3VALUES LESS THEN(MAXVALUE) TABLESPACE CUS_TS03)

2.      列表分区（list partition）

列表分区的特点是某列的值只有几个，基于这样的特点我们可以使用列表分区

列表分区的特点：

a.      列表分区主要一句分区键定义时给出的取值列表，根据实际的取值，进行分区的选择，进而在相应的分区中存储数据

b.      列表分区比较适合列唯一取值有限，且较为固定的数据列

c.      列表分区的分布可能不均匀

列表分区定义规则：

a.      在定义分区范围时，每个分区定义必须使用values(value1,value2…)子句，表示该分区存储包含相关alue值的数据行

b.      在定义范围分区时，最后一个分区可以是values(default)。表示该分区存储没有在其他分区定义的数据行。

例子：

CREATETABLE TEST(

ID NUMBER,

STATUS VARCHAR2(20)

)

PARTITIONBY LIST(STATUS)(

PARTITIONPART_STATUS1 VALUES(‘ACTIVE’),

PARTITIONPART_STATUS2 VALUES(‘INACTIVE’),

PARTITIONPART_STATUS3 VLAUES(DEFAULT)

)

 

CREATETABLE TEST1(

ID NUMBER,

CITY VARCHAR2(20)

)

PARTITIONBY LIST(CITY)(

PARTITION PART_CITY1VALUES (‘SHENZHEN’,’GUANGZHOU’),

PARTITION PART_CITY2VALUES (‘BEIJING’,’SHANGHAI’)  ---后面可以加tablespace

)

3.      散列分区（hash partition）

散列分区也称HASH分区，这类分区是在列值上使用散列算法，以确定将行放入哪个分区中，当列的值没有合适的条件时，建议使用散列分区。散列分区通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。

Hash 分区最主要的机制是根据hash算法来计算具体某条记录应该插入到哪个分区中，hash算法中最重要的就是hash函数，Oracle中如果要使用hash分区，只需要指定分区的数量即可，一般推荐使用2的n次方（如2，4，6，8），这样可以使得各个分区的数据分布的更加均匀

Hash分区的特点：

a.      hash分区主要通过hash算法确定相应数据行应该被存放到哪个分区中

b.      hash分区比较适合列差异值很多的数据列

hash分区的注意事项：

a.      对于hash分区，无法控制一条数据在分区间的具体分布，具体的分区由hash算法来觉得

b.      对于hash分区，如果更改数据的数量，将导致所有数据在分区间重新分布

例子：

Create tabletest_hash_part(

ID NUMBER,

STATUS VARCHAR2(20),

Dt varchar2(20)

)

Partition byhash(dt)(

Partitionpart_hash01,

Partitionpar_hash02

);

或者（简写）

Create tabletest_hash_part(

ID NUMBER,

STATUS VARCHAR2(20),

Dt varchar2(20)

)

Partition by hash(dt)partitions 8

Store in (表空间)

8：初始化定义多少个分区，如果采用简写方式，则分区的命名是Oracle定义

4.      组合分区（composite partition）:

组合分区的特点：组合分区中，主要通过不同的列上，使用范围分区，列表分区，hash分区不同的组合方式，进而实现组合分区；组合分区中，分区本身没有相应的segment ,可以认为是一个逻辑容器，只有子分区拥有相应的segment ,用于存放数据

组合分区的注意事项：

1.在11G以前，组合分区主要有两种组合方式“RANGE-LIST”和“RANGE-HASH”

2.在11G以后，组合分区新增了四种组合方式：“RANGE-RANGE”，

“LIST-RANGE”，“LIST-HASH”，“LIST-LIST”;

3.关于表空间的指定，可以放在分区一级指定，也可以放在子分区一级指定；

4.对于同一张组合分区表而言，子分区的名字不能相同，即使在不同的分区里

5.如下例子所示，列表的长度必须要不一样

6.如果在default子分区已经存在时，无法通过alter再添加子分区

           例子：

                                     Createtable emp(

                                      "EMPNO" NUMBER(4,0),

 "ENAME"VARCHAR2(10),

 "JOB" VARCHAR2(9),

 "MGR" NUMBER(4,0),

 "HIREDATE" DATE,

 "SAL" NUMBER(7,2),

 "COMM" NUMBER(7,2),

 "DEPTNO"NUMBER(2,0)

)

Partition by list(DEPTNO)

subpartition by list(job)(

 partition part_dept_10values(10) tablespace test_01(

         subpartitionpart_job_10_magager values (‘magager’),

         subpartitionpart_job_10_default values(default)

),

 Partition part_dept_20 values(20)tablespace test_02(

         Subpartitionpart_job_20_ magager values (‘magager’),

         Subpartitionpart_job_20_default values(default)

)

);

 

五、有关表分区的一些操作（这些操作都会引起全局索引失效，需要注意）

A.     添加分区

ALTER TABLE 表名 ADD PARTITION 分区名 VALUES (值);

如果是范围分区，添加的分区应该高于最后一个分区界限：

ALTER TABLE 表名 ADD PRATITION 分区名 VALUES LESS THEN (值);

如果是组合分区：

ALTER TABLE 表名 MODIFY PARTITION 一级分区名 ADD SUBPARTITION 二级分区名 VALUES(值)

B.     删除分区

ALTER TABLE 表名 DROP PARTITION 分区名;

删除子分区：

ALTER TABLE 表名 DROP SUBPARTITION 分区名;

注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，如果想要删除分区，则必须删除表

C.     截断分区

截断某个分区是指删除某个分区的数据，并不会删除分区，也不会删除其他分区中的数据。当表中只有一个分区时，也可以截断该分区。但是需要特别注意的是：截断分区会使表的全局索引失效

ALTER TABLE 表名 TRUNCATE PARTITION 分区名;

截断子分区：

ALTER TABLE 表名 TRUNCATE SUBPARTITION 分区名;

D.     合并分区

合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，注意：不能将分区合并到界限较低的分区。

以范围分区表为例：

ALTER TABLE 表名 MERGE PARTITIONS P1,P2 INTO PARTITION P2;

如果PARTITIONS 后面的两个分区的顺序为：高界限分区,低界限分区，则会报ORA-14273：必须首先指定下界分区；

如果最后面的分区为界限较低的分区，则会报ORA-14275：不能将下界分区作为结果分区重用；

E.      拆分分区

拆分分区是将一个分区拆分为两个新分区，拆分后原来的分区不再存在，注意不能对HASH类型的分区进行拆分。

ALTER TABLE 表名 SPLIT PARTITON P1 AT(2) INTO (PARTITION P001,PARTITION P002)

F.      接合分区

接合分区是将散列分区中的数据接合到其他分区中，当散列返去数据比较大时，可以增加散列分区，然后进行接合。但是接合分区只能用于散列分区。

ALTER TABLE 表名 COALESCA PARTITION;

G.     重命名表分区

ALTER TABLE表名RENAME PARTITION P1 TO P2;

六、常用的一些相关查询

a.      跨分区查询

SELECT * FROM TABLE_NAME PARTITION (P1)

UNION

SELECT * FROM TABLE_NAME PARTITION (P2)

b.      查询表上有多少分区（表名要大写）

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = ‘TABLENAME’;

c.      查询索引信息

SELECT OBJECT_NAME,OBJECT_TYPE,TABLESPACE_NAME,SUM(VALUE)FROM V$SEGMENT STATISTICS WHERE STATISTIC_NAME IN(‘physical reads’,’physicalwrite’,’logical reads’) and OBJECT_TYPE=’INDEX’ GROUP BYOBJECT_NAME,OBJECT_TYPE,TABLESPACE_NAME ORDER BY 4 DESC;

d.      显示数据库所有分区表的信息

SELECT * FROM DAB_PART_TABLES;

e.      显示当前用户可以访问的分区表的信息

f.        

七、 

   

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

扩展：

Segment（数据文件空间）：段是指占用数据文件空间的通称，或数据库对象使用的空间的集合；段可以有表段，索引段，回滚段，临时段和高速缓存段

在数据库中，一段指包含一个或多个区域的一部分，各个区域由称为区的单元构成，一个或者多个段构成一个表空间