大型数据库技术（五）表分区和索引分区

最新推荐文章于 2024-01-28 14:31:09 发布

passionfruit28175

最新推荐文章于 2024-01-28 14:31:09 发布

阅读量149

点赞数

分类专栏： oracle 文章标签：数据库

本文链接：https://blog.csdn.net/m0_74089390/article/details/133755674

版权

oracle 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

分区理由：

在当今，数据库应⽤系统的规模越来越⼤，还有海量数据的数据仓库系统，因此，⼏乎所有的Oracle 数据库都使⽤分区功能来提⾼查询性能，并且简化数据库的⽇常管理维护⼯作。

一、分区技术概述

分区功能能够改善应⽤程序的性能、可管理性和可⽤性，是数据库管理中⼀个⾮常关键的技术。尤其在今天，数区功能来提据库应⽤系统的规模越来越⼤，还有海量数据的数据仓储系统，因此，⼏乎所有的Oracle 数据库都使⽤分⾼查询的性能，并且简化数据库的⽇常管理维护⼯作。

分区技术优点：

［√］减少维护⼯作量，独⽴管理每个分区⽐管理单个⼤表要轻松得多。

［√］增强数据库的可⽤性，如果表的⼀个或⼏个分区由于系统故障⽽不能使⽤，⽽表其余的分区仍然可以使⽤；如果系统故障只影响表的⼀部分分区，那么，只有这部分分区需要修复，这就

⽐修复整个⼤表耗费的时间少许多。

［√］均衡 I/O ，减少竞争，通过把表的不同分区分配到不同的磁盘来平衡I/O 改善性能。

［√］分区对⽤户保持透明，最终⽤户感觉不到分区的存在。

［√］提⾼查询速度：对⼤表的查询、增加、修改等操作可以分解到表的不同分区中来并⾏执⾏，这样就可以加快运⾏速度，在数据仓库的TP 查询特别有⽤。

二、创建表分区

1、范围分区

创建范围分区关键字是 range

当表结构采⽤范围分区时，⾸先要考虑分区的列应该符合范围分区的⽅法；其次要考虑列的数据值的取值范围；最后考虑列的边界问题

基本语法

CREATE TABLE <table_name> 
(column_name data type，column_name data type，….) 
PARTITION BY RANGE (column_list)
(PARTITION <partition name> values LESS THAN <value_list>，
PARTITION <partition name> values LESS THAN <value_list>)；

例：

创建⼀个商品零售表，然后为该表按照销售⽇期所在的季度创建4 个分区

SQL> create table ware_retail_part //创建
⼀个描述商品零售的数据表
 2 (
 3 id integer primary key, //销售编号
 4 retail_date date, //销售⽇期
 5 ware_name varchar2(50) //商品名称
 6 )
 7 partition by range(retail_date)
 8 (
 9 --2011年第⼀个季度为par_01分区
 10 partition par_01 values less than(to_date('2011-04-01','yyyy-mm-dd')) tablespace TBSP_1,
 11 --2011年第⼆个季度为par_02分区
 12 partition par_02 values less than(to_date('2011-07-01','yyyy-mm-dd')) tablespace TBSP_1,
 13 --2011年第三个季度为par_03分区
 14 partition par_03 values less than(to_date('2011-10-01','yyyy-mm-dd')) tablespace TBSP_2,
 15 --2011年第四个季度为par_04分区
 16 partition par_04 values less than(to_date('2012-01-01','yyyy-mm-dd')) tablespace TBSP_2

在为商品零售表 ware_retail_part 创建了 4 个范围分区之后，下⾯向该表中插⼊若⼲条记录。

SQL> insert into ware_retail_part values(1,to_date('2011-01-20','yyyy-mm-dd'),'平板电脑');
已创建 1 ⾏
SQL> insert into ware_retail_part values(2,to_date('2011-04-15','yyyy-mm-dd'),'s智能⼿机');
已创建 1 ⾏
SQL> insert into ware_retail_part values(3,to_date('2011-07-25','yyyy-mm-dd'),'sMP5');
已创建 1 ⾏

在向 ware_retail_part 表中插⼊若⼲条记录之后，⽤户就可以通过分区表（即进⾏了分区的数据表）来查询数据了，这种⽅式的查询速度要⽐从整个表中查询快得多，使⽤分区表查看数据的例⼦如下：

查询数据表 ware_retail_part 中分区 par_02 中的全部记录，代码如下：

SQL> select * from ware_retail_part partition(par_02);

另外，Range分区的字段可以是两个或者多个，来看下⾯的例⼦。

SQL> create table ware_retail_part2 
//创建⼀个描述商品零售的数据表
 2 (
 3 id integer primary key, 
 //销售编号
 4 retail_date date, 
 //销售⽇期
 5 ware_name varchar2(50) 
 //商品名称
 6 )
 7 partition by range(id,retail_date) 
 //按照销售序号和销售⽇期分区
 8 (
 9 --第⼀个分区par_01
 10 partition par_01 values less than(10000,to_date('2011- 12-01','yyyy-mm-dd')) tablespace TBSP_1,
 11 --第⼀个分区par_02
 12 partition par_02 values less than(20000,to_date('2012-12-01','yyyy-mm-dd')) tablespace TBSP_1,
 13 --第⼀个分区par_03
 14 partition par_03 values less than(maxvalue,maxvalue) tablespace TBSP_2 15 );
表已创建

对基于范围分区的说明

• 分区关键字可为多列。

• 所插入的数据自动分配到相应分区。

• 所插数据不得大于LESS THAN中的最大值。

• 可以使用MAXVALUE。

• 在分区表中不能包含LONG、LOB和对象类型列。

• 聚集表不能分区。

• 可以为每个分区指定表空间。不指定表空间时，则使 用该用户的DEFAULT TABLESPACE。

2、散列分区

HASH 分区，也叫做散列分区，是在列的取值难以确定的情况下采⽤的分区⽅法。⽐如，按照⾝份证号进⾏分区，就很难确定⾝份证号的分区范围。HASH 实际上是⼀种函数算法，当向表中插⼊数据时，系统会⾃动根据当前分区列的值计算出HASH 值，然后确定应该将该⾏存放于哪个表空间中。

HASH 分区通过指定分区编号将数据均匀分布在磁盘设备上，使得这些分区⼤⼩⼀致，这充分降低了I/O 磁盘争⽤的情况，但是，对于范围查询或不等式查询起不到优化的作⽤。

⼀般，下⾯⼏种情况可以采⽤ HASH 分区。

［√］ HASH 分区可以由 HASH 键来分布。

［√］ DBA ⽆法获知具体的数据值。

［√］数据的分布由 Oracle 处理。

［√］每个分区有⾃⼰的表空间。

例：

将表students中的记录分别存储在4个分区中。

--直接指定区域数量，不命名，系统自动生成分区名，store in表空间名

CREATE TABLE students (
id NUMBER(5) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
major VARCHAR2(30),
current_credits NUMBER(3)
)
PARTITION BY HASH (id)
PARTITIONS 4
STORE IN (stu1,stu2,stu3,stu4);

--一个一个创建存储在特定表空间

CREATE TABLE HASH_TABLE 
( 
COL NUMBER(8), 
INF VARCHAR2(100) 
) 
PARTITION BY HASH (COL) 
( 
PARTITION PART01 TABLESPACE HASH_TS01, 
PARTITION PART02 TABLESPACE HASH_TS02, 
PARTITION PART03 TABLESPACE HASH_TS03 
)

在创建 HASH 分区表时，⽤户还可以指定所有分区的初始分配空间

SQL> create table goods //定义包含商品信息的表
 2 (
 3 id number, //编号
 4 goodname varchar2(50) //名称
 5 )
 6 storage(initial 2048k) //定义表分区的初始化空间⼤⼩为2048KB
 7 partition by hash(id) //创建id列作为分区键的hash表分区
 8 (
 9 partition par1 tablespace tbsp_1, //表分区par1
 10 partition par2 tablespace tbsp_2 //表分区par2
 11 );
表已创建

上例指定了各个分区的名字及其所存放的表空间，所有分区的⼤⼩都继承表空间的初始分配参数值——2048KB 。

查询插入的记录在哪个分区

select * from @tablename partition<@分区名>

3、列表分区

关键字是 LIST ，如果表的某个列的值可以枚举，则可以考虑对表进⾏列表分区。⽐如客户表clients ，那么就可以按照客户所在的省份进⾏分区，该表的列表分区可以分为partition shandong （⼭东省）、partition guangdong （⼴东省）与 partiton yunnan （云南省）等

• 通过建立离散的值列表告诉数据库特定的值属于哪一 个分区。

• 分区关键字只能为单列。

例：

⾸先创建⼀个⽤于保存客户信息的表 clients ，然后以province列为分区键创建列表分区

SQL> create table clients //创建客户表
 2 (
 3 id integer primary key, //客户编号
 4 name varchar2(50), //客户名称
 5 province varchar2(20) //客户所在省份
 6 )
 7 partition by list(province) //以province列为分区键创建列表分区
 8 (
 9 partition shandong values(' ⼭ 东 省 '), //⼭东省分区
 10 partition guangdong values(' ⼴ 东 省 '), //⼴东省分区
 11 partition yunnan values(' 云 南 省 ') //云南省分区
 12 );
表已创建

4、组合分区

结合两个数据分区的⽅法可以成为⼀个组合分区⽅法。⾸先⽤第⼀个数据分布⽅法对表格进⾏分区，然后再⽤第⼆个数据分区⽅法对每个分区进⾏⼆次分区。

可以先其他后散列

不可以先散列后其他

⽬前的Oracle仅⽀持对索引组织表（索引和数据⼀起的表格）进⾏范围分区、列表分区或散列分区，但不⽀持对其进⾏组合分区。

例：先范围再散列

SQL> create table person2 //创建以⼀个描述个⼈信息的表
 2 (
 3 id number primary key, //个⼈的编号
 4 name varchar2(20), //姓名
 5 sex varchar2(2) //性别
 6 )
 7 partition by range(id) //以id作为分区键创建范围分区
 8 subpartition by hash(name) //以name列作为分区键创建hash⼦分区
 9 subpartitions 2 store in(tbsp_1,tbsp_2) //hash⼦分区共有两个，分别存储在两个不同的命名空间中
 10 (
 11 partition par1 values less than(5000), //范围分区，id⼩于5000
 12 partition par2 values less than(10000), //范围分区，id⼩于10000
 13 partition par3 values less than(maxvalue) //范围分区，id不⼩于10000
 14 );
表已创建

CREATE TABLE rooms (
room_id NUMBER(5) PRIMARY KEY,
building VARCHAR2(15),
room_number NUMBER(4),
number_seats NUMBER(4),
description VARCHAR2(50)
)
PARTITION BY RANGE (room_id)
SUBPARTITION BY HASH (number_seats)
SUBPARTITIONS 2
(PARTITION rooms_1 VALUES LESS THAN (30000),
PARTITION rooms_2 VALUES LESS THAN (60000),
PARTITION rooms_3 VALUES LESS THAN (maxvalue))；

先范围再列表：

CREATE TABLE SALES (
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
STATUS VARCHAR2(20) )
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS) (
PARTITION P1 VALUES LESS THAN(TO_DATE(‘2018-01-01','YYYY-MM- DD'))
( SUBPARTITION P1SUB1 VALUES ('ACTIVE'), 
SUBPARTITION P1SUB2 VALUES ('INACTIVE') ), 
PARTITION P2 VALUES LESS THAN (TO_DATE(‘2019-01-01','YYYY-MM-DD'))
( SUBPARTITION P2SUB1 VALUES ('ACTIVE'), 
SUBPARTITION P2SUB2 VALUES ('INACTIVE')) 
)

4、Interval分区

三、表分区策略

对表进⾏分区设计时，⾸先要考虑和分析分区表中每个分区的数据量，其次要为每个分区创建相应的表空间。

1、识别⼤表⼀般来说，数据占⽤存储空间⼤的表就是⼤表，系统架构师要做到的就是如何确定哪些表属于⼤表。如果要在⽬前运⾏的系统上进⾏表数据量分析，那么主要采⽤ANALYZE TABLE 语句进⾏分析，然后查询数据字典获得相应的数据量；如果是⼀个正在进⾏需求分析的表，则只能采⽤估计的⽅法了。

2.、 ⼤表如何分区

⼤表⼀般可以按时间分区。⽐如，如果按照⽉份分区，则需要为每个⽉创建⼀个数据表空间；如果按照季度分区，则⼀年要创建4 个表空间；如果要存放5 年⽤的表空间，则需要创建 20 个表空间。

3.、 分区的表空间规划

分区⽅法确定后，就要着⼿创建表空间，创建表空间前要对每表空间的⼤⼩进⾏估算。如若每个季度的数据为100MB ，则最好创建120MB的季度⽤表空间。另外，还要考虑数据量的增⻓，如当年的数据每季度是100MB ，则是下⼀年可能要增⻓ 20%~30% ，这些变化都要在表空间的⼤⼩上给予考虑。

四、管理表分区

1、添加表分区

对于已经存在表分区的某个表，如果要添加⼀个新的表分区，通常使⽤ALTER TABLE...ADD PARATITION 语句

例：

在客户信息表 clients 中，添加⼀个省份为 “ 河北省 ” 的表分区

SQL> alter table clients
 2 add partition hebei values('河北省')
 3 storage(initial 10K next 20k) tablespace tbsp_1
 4 nologging

ALTER TABLE RS_audit 
ADD PARTITION rsaudit_201401 
VALUES LESS THAN (to_date('20140701 ','YYYYMMDD')) 
TABLESPACE ts3

2、合并表分区

［√］在合并分区时， HASH 列函数将分区内容分布到⼀个或多个保留分区中。

［√］原来内容所在的分区完全被清除。

［√］与分区对应的索引也被清除。

［ √ ］将⼀个或多个索引的本地索引分区标识为不可⽤ UNSABLE）。

［√］需要对不可⽤的索引进⾏重建。

①合并散列分区

使⽤ ALTER TABLE...COALESCE PARTITION 语句可以完成 HASH列分区的合并

alter table person coalesce partition;

②合并复合分区

可以使⽤ ALTER TABLE ...MODIFY 语句实现将某个⼦分区的内容重新分配到⼀个或者多个保留的⼦分区中

例：

把 person2 分区表中的 par3 分区合并到其他保留的⼦分区中

alter table person2 modify partition par3 coalesce subpartition;

3、删除分区

可以从范围分区或复合分区中删除分区。但是散列分区和复合分区的散列⼦分区，只能通过合并来达到删除的⽬的。

①删除一个表分区

alter table ware_retail_part drop partition par_04;

②删除有数据和全局索引的表分区

如果分区表中包含了数据，并且在表中定义了⼀个或者多个全局索引，可以使⽤ALTER TABLE... DROP PARTITION 语句删除表分区，这样可以保留全局索引，但是索引会被标识为不可⽤（UNUSABLE ），因⽽需要重建索引，下⾯来看⼀个例⼦。

SQL> alter table ware_retail_part drop partition par_04;
表已更改
SQL> alter index ware_index rebuild;
索引已更改

如果 ware_index 是范围分区的全局索引，那么就需要重建所有索引的分区

alter index ware_index rebuild index_01;
alter index ware_index rebuild index_02;
alter index ware_index rebuild index_03;

③使⽤DELETE和ALTER TABLE...DROP PARTITION语句

在执⾏ ALTER TABLE...DROP PARTITION 语句前⾸先执⾏ DELETE 语句来删除分区的所有数据⾏，然后执⾏ ALTER TABLE...DROP PARTITION语句，但是执⾏ DELETE 语句时需要更新全局索引

SQL> delete from ware_retail_part where retail_date >=to_date('2011-10-01','yyyy-mm-dd');
已删除513⾏
SQL> alter table ware_retail_part drop partition par_04;
表已更改

④删除具有完整性约束的分区

如果分区的表具有完整性约束，则可以采⽤以下两种办法：

（ 1 ）⾸先禁⽌完整性约束，然后执⾏ ALTER TABLE...DROP PARTITION，最后激活约束

SQL> alter table books_1 disable constraints BOOK_PK;
表已更改
SQL> alter table books_1 drop partition part_01;
表已更改
SQL> alter table books_1 enable constraints BOOK_PK;
表已更改

（ 2 ）⾸先执⾏ DELETE 语句删除分区中的⾏，然后⽤ ALTER TABLE...DROP PARTITION语句删除分区，下⾯来看⼀个例⼦。

SQL> delete from books_1 where bookno < 1000;
已删除12⾏
SQL> alter table books_1 drop partition part_01;
表已更改

4、并入分区

⽤户可以使⽤ MERGE PARTITION 语句将相邻的范围分区合并在⼀起变为⼀个新的分区，需要注意，该分区继承原来两个分区的边界；原来的两个分区与相应的索引⼀起被删除；如果被合并的分区⾮空，则该分区被标识为UNSABLE ；不能对 HASH 分区表执⾏ MERGE PARTITION语句。

并⼊范围分区是将两个以上的分区合并到⼀个存在的分区中，合并后⼀般索引要重建。为了便于⼤家对并⼊操作的理解，下⾯来看⼀个⽐较完整的例⼦。

合并分区：

alter table sales merge partitions part_sea3,part_sea4into partition part_sea4;

重新建立局部索引：

alter table sales modify partition part_sea4 rebuild unusable local indexes;
alter tabel @tablename modify partition @parname rebuild unusable local indexes;

5、查询表分区

• 在分区表中插入记录时，记录基于指定的分区键，与 预先定义的分区边界进行比较，从而被分配到表的分 区中。

• 对分区表进行查询时，可以针对整张表进行查询，也 可以对某个分区进行查询。

例子：

查询所有的学生： 
 SELECT * FROM students
查询所有历史专业的学生：
 SELECT * FROM students PARTITION（history）
查询已取得了12个学分的历史专业的学生： 
 SELECT * FROM students PARTITION（history）ss WHERE ss.current_credits=12

6、复制表分区

CREATE TABLE music_students AS SELECT * FROM students PARTITION（music）

7、增加表分区

• 分区需加在最后一个分区高值之后。

• 使用MAXVALUE时，该表不可增加分区。

例：

给表RS_audit增加一个分区，并将该分区存储在表空 间ts3中。

ALTER TABLE RS_audit 
ADD PARTITION rsaudit_201401 
VALUES LESS THAN (to_date('20140701 ','YYYYMMDD')) 
TABLESPACE ts3

8、重命名表分区

将表rooms的分区rooms_1改名为rooms_30000。

ALTER TABLE rooms RENAME PARTITION rooms_1 TO rooms_30000

9、移动表分区

可以将分区数据移动到另一个表空间上

ALTER TABLE RS_audit MOVE PARTITION rsaudit_201302 TABLESPACE bak_201302;

10、分区拆分

例如，将表rooms的分区rooms_2拆为两个分区，将 room_id在30000至45000之间的记录划分到分区 rooms_21中，room_id在45000至60000之间的记录划分 到分区rooms_22中。

ALTER TABLE rooms SPLIT PARTITION rooms_2 AT (45000) 
INTO (PARTITION rooms_21, PARTITION rooms_22)