Oracle 19C入门到精通之表分区

Oracle是最早支持物理分区的数据库管理系统供应商，表分区的功能是在Oracle 9.0版本中推出的。分区功能能够改善应用程序的性能、可管理性和可用性，是数据库管理中的一项非常关键的技术。尤其在今天，数据库应用系统的规模越来越大，还有海量数据的数据仓库系统。因此，几乎所有的Oracle数据库都通过使用分区功能来简化数据库的日常管理维护工作。

使用分区技术有如下优点：

减少维护工作量。独立管理每个分区比管理单张大表要轻松得多。

增强数据库的可用性。如果表的一个或几个分区由于系统故障而不能被使用，那么表其余的分区仍然可以使用；如果系统故障只影响表的一部分分区，那么，只有这部分分区需要修复，这就比修复整张大表耗费的时间少许多。

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

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

提高查询速度。对大表的查询、增加、修改等操作可以分解到表的不同分区中来并行执行，这样就可以加快运行速度，对数据仓库的TP查询尤其有用。

分区技术主要包括表分区和索引分区。

1. 创建表分区

1.1. 范围分区

创建范围分区的关键字是RANGE，创建该分区后，其中的数据可以根据分区键值指定的范围进行分布，当数据在范围内被均匀分布时，性能最好。

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

创建一个商品零售表，为该表按照销售日期所在的季度创建4个分区，代码如下：

create table ware_retail_part  --创建一个描述商品零售的数据表

(

id integer primary key,      --销售编号

retail_date date,            --销售日期

ware_name varchar2(50)       --商品名称

)

partition by range(retail_date)

(

--2023年第一个季度为par_01分区

partition par_01 values less than(to_date('2023-04-01','yyyy-mm-dd')) tablespace TBS_TEST_1,

--2023年第二个季度为par_02分区

partition par_02 values less than(to_date('2023-07-01','yyyy-mm-dd')) tablespace TBS_TEST_1,

--2023年第三个季度为par_03分区

partition par_03 values less than(to_date('2023-10-01','yyyy-mm-dd')) tablespace TBS_TEST_2,

--2023年第四个季度为par_04分区

partition par_04 values less than(to_date('2024-01-01','yyyy-mm-dd')) tablespace TBS_TEST_2

);

向表ware_retail_part中插入3条记录，代码如下：

insert into ware_retail_part values(1,to_date('2023-01-20','yyyy-mm-dd'),'平板电脑');

insert into ware_retail_part values(2,to_date('2023-04-15','yyyy-mm-dd'),'智能手机');

insert into ware_retail_part values(3,to_date('2023-07-25','yyyy-mm-dd'),'笔记本电脑');

在向ware_retail_part表中插入若干条记录之后，就可以通过分区表（即进行了分区的数据表）来查询数据了。这种方式的查询速度要比从整张表中查询快得多。

--查询数据表ware_retail_part的分区par_02中的全部记录

select * from ware_retail_part partition(par_02);

RANGE分区的字段可以是两个或者多个；

创建一个商品零售表，为该表按照销售编号和销售日期的组合创建3个分区，代码如下：

create table ware_retail_part2     --创建一个描述商品零售的数据表

(

id integer primary key,           --销售编号

retail_date date,                 --销售日期

ware_name varchar2(50)            --商品名称

)

partition by range(id,retail_date)  --按照销售序号和销售日期分区

(

--第一个分区par_01

partition par_01 values less than (10000, to_date('2023-12-01', 'yyyy-mm-dd')) tablespace TBS_TEST_1,

--第二个分区par_02

partition par_02 values less than (20000, to_date('2024-12-01', 'yyyy-mm-dd')) tablespace TBS_TEST_1,

--第三个分区par_03

partition par_03 values less than (maxvalue, maxvalue) tablespace TBS_TEST_2);

在上述例子中，partition by range(id,retail_date)作为分区方法，id和retail_date作为分区键，即按销售编号和销售日期的组合来进行区分。语句“partition par_01 values less than (10000, to_date('2023-12-01', 'yyyy-mm-dd')) tablespace TBS_TEST_1”表示一个分区的定义，当插入记录的销售日期小于2023年12月1日，并且销售编号小于10000时，将把该记录划为分区par_01并存储在TBS_TEST_1表空间上。

2. 散列分区

HASH分区也叫作散列分区，是在列的取值难以确定的情况下采用的分区方法。例如，按照身份证号进行分区，就很难确定身份证号的分区范围。HASH实际上是一种函数算法，当向表中插入数据时，系统会自动根据当前分区列的值计算出HASH值，然后确定应该将该行存储于哪个表空间中。

HASH分区通过指定分区编号将数据均匀分布在磁盘设备上，使得这些分区大小一致。这充分降低了I/O磁盘争用的情况，但是该分区方法对于范围查询或不等式查询起不到优化的作用。一般来说，下面几种情况可以采用HASH分区：

HASH分区可以由HASH键来分布。

DBA无法获知具体的数据值。

数据的分布由Oracle处理。

每个分区有自己的表空间。

创建一个商品零售表，将该表id列的值根据自身情况散列地存储在指定的两个表空间中，代码如下：

create table ware_retail_part3         --创建一个描述商品零售的数据表

(

id integer primary key,              --销售编号

retail_date date,                    --销售日期

ware_name varchar2(50)               --商品名称

)

partition by hash(id)

(

partition par_01 tablespace TBS_TEST_1,  --创建par_01分区

partition par_02 tablespace TBS_TEST_2   --创建par_02分区

);

在为商品零售表ware_retail_part3创建了两个HASH分区之后，接下来向该表中插入一条记录。

--向表ware_retail_part3中插入一条记录

insert into ware_retail_part3 values(99,to_date('2023-11-11','yyyy-mm-dd'),'台式电脑');

上述插入的记录到底被分配到哪个分区中呢？用户无法直接判断，而是由Oracle系统通过计算id的HASH值（这里是99），然后系统按照均匀分布的原则自动分配的。通过SELECT语句来查询该记录所在的分区：

select * from ware_retail_part3 partition(par_01);

select * from ware_retail_part3 partition(par_02);

在上述若干个创建表分区的例子中，都为表分区指定了名称，在Oracle中，可以实现由系统自动分配分区名。

创建一个表person，然后为该表创建HASH列分区（分区列为id），要求创建的两个分区由系统自动生成分区名，并分别放置在表空间TBS_TEST_1和TBS_TEST_2中，代码如下：

create table person       --创建一个描述个人信息的表

(

id number primary key,  --个人的编号

name varchar2(20),      --姓名

sex varchar2(2)         --性别

)

partition by hash(id)     --使用id作为HASH分区的键值

partitions 2              --创建两个分区，分区名由系统自动给出

store in(TBS_TEST_1,TBS_TEST_2);  --指定两个不同的命名空间

在上述例子中，HASH分区表的每个分区均保存在不同的表空间中，这样HASH数据也就被存储在不同的表空间中了。

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

创建一个goods表，为该表创建HASH列分区（分区列为id），并为创建的表分区指定初始化空间，大小为2048 KB，代码如下：

create table goods      --定义包含商品信息的表

(

id number,            --编号

goodname varchar2(50) --名称

)

storage(initial 2048k)  --定义表分区的初始化空间大小为2048

partition by hash(id)   --创建id列作为分区键的HASH表分区

(

partition par1 tablespace TBS_TEST_1,  --表分区par1

partition par2 tablespace TBS_TEST_2   --表分区par2

);

在上述例子中，指定了各个分区的名称及其所存储的表空间，所有分区的大小都继承表空间的初始分配参数值，即2048 KB。

3. 列表分区

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

创建一个用于保存客户信息的表clients，以province列为分区键创建列表分区，代码如下：

create table clients          --创建客户表

(

id integer primary key,     --客户编号

name varchar2(50),          --客户名称

province varchar2(20)       --客户所在省份

)

partition by list(province)   --以province列为分区键创建列表分区

(

partition shandong values('山东省'),   --山东省份区

partition guangdong values('广东省'),  --广东省份区

partition yunnan values('云南省')      --云南省份区

);

在为客户表clients创建了列表分区之后，接下来向该表中插入一条记录。

--向表clients中插入一条记录

insert into clients values(19,'顺达','山东省');

由于插入记录的province字段的值为“山东省”，因此该记录被存储到名称为shandong的表分区中。通过SELECT语句来查询shandong表分区中的记录，查询结果如图所示：

select * from clients partition(shandong);

4. 组合分区

综合运用两种数据分区方法，可以进行组合分区。首先用第一种数据分区方法对表格进行分区，然后用第二种数据分区方法对每个分区进行二次分区。

Oracle支持6种组合分区方案：组合范围和范围分区、组合列表和范围分区、组合范围和散列分区、组合范围和列表分区、组合列表和列表分区、组合列表和散列分区。

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

创建一个保存人员信息的数据表person2，然后创建3个范围分区，每个分区又包含两个子分区，子分区没有名称，由系统自动生成，并要求将其分布在两个指定的表空间中，代码如下：

create table person2                       --创建一个描述个人信息的表

(

id number primary key,                    --个人的编号

name varchar2(20),                        --姓名

sex varchar2(2)                           --性别

)

partition by range(id)                      --以id作为分区键创建范围分区

subpartition by hash(name)                  --以name列作为分区键创建HASH子分区

subpartitions 2 store in(TBS_TEST_1,TBS_TEST_2) --HASH子分区共有两个，分别存储在两个不同的命名空间中

(

partition par1 values less than(5000),    --范围分区，id小于5000

partition par2 values less than(10000),   --范围分区，id小于10000

partition par3 values less than(maxvalue) --范围分区，id不小于10000

);

上述代码首先按照范围进行分区，然后对子分区按照HASH进行分区，根据name列的HASH值确定该行分布在TBS_TEST_1或TBS_TEST_2某个表空间上。

5. Interval分区

Interval分区的关键字是INTERVAL，它是范围分区的一种增强功能，可以实现equi_sized范围分区的自动化。该方法创建的分区作为元数据，只有最开始的分区是永久分区。随着数据的增加会分配更多的部分，并自动创建新的分区和本地索引。

创建一个saleRecord表，为该表创建Interval分区，代码如下：

create table saleRecord

(

id number primary key,                --编号

goodsname varchar2(50),               --商品名称

saledate date,                        --销售日期

quantity number                       --销售量

)

partition by range(saledate)          --以销售日期为分区键

interval (numtoyminterval(1,'year'))  --Interval分区实现按年份进行自动分区

(

--设置分区键值日期小于2023-01-01

partition par_fist values less than (to_date('2023-01-01','yyyy-mm-dd'))

);

在上述代码中，函数NUMTOYMINTERVAL的功能是将数字转换成YEAR或者MONTH。

进行Interval分区的表格有传统的范围部分和自动生成的Interval部分。对于已经进行了范围分区的表格，可以通过使用ALTER TABLE命令的SET INTERVAL选项扩展成为Interval分区的表格。