如何理解Oracle分区表

第 1 部分：什么是分区？

分区是将表或索引物理分解为更小、更易于管理的部分。

分区对应用程序是透明的，即对于访问数据库的应用程序来说，逻辑上只有一张表或一个索引（相当于应用程序“看到”的只有一张表）或一个索引）。索引），但实际上这个表或索引可能由数十个物理分区组成。

每个分区都是一个独立的对象，可以单独处理或作为更大对象的一部分进行处理。

--------------------------提示：分表和分区表---------- - ----------------

表分区就是分解一个la将一张表按照一定的规则分成多个具有独立存储空间的表。 实体表（子表）；

例如，如果订单表ORDER分为年表和月表，那么除了ORDER本身之外，还可能有很多其他表生成的，如ORDER_201601、ORDER_201602、ORDER_201603...等分表。

子表在逻辑上是多个不同的表，而分区表在逻辑上是一张表。

---------------------------------------------------- --- --------------------------

2：什么时候需要分区？

官网的两条建议：

1.应始终考虑对大于 2GB 的表进行分区。 （表数据大小大于2GB时应考虑分区）

2.包含历史数据的表，其中新数据添加到最新的分区中。一个典型的例子是历史表，其中只有当月的数据是可更新的，其他 11 个月是只读的。 （新数据添加到最新分区中用于存储历史数据的表中）

三：分区的好处

1. 提高数据可用性

a）由于各个分区的独立性，优化器在查询时会在必要时删除未使用的分区（这也称为消除分区）

这在 OLAP 系统中很有用。

------------------------延伸阅读：OLTP和OLAP系统-------------- --------

OLTP（On-Line Transaction Processing）：

联机事务处理，也称为面向事务的处理，其基本特征是前台接收到的用户数据可以立即传输到计算中心进行处理，短时间内给出处理结果，实现对用户操作的快速响应；

这样的系统对事务性要求非常高，一般都是高可用的在线系统，专注于小事务和小查询。在评估其系统时，通常会查看每秒执行的事务数和执行 SQL 数。单个数据库往往每秒处理数百或数千个Transaction，Select语句的执行量每秒数千甚至数万；

OLTP是传统关系型数据库的主要应用。典型的OLTP系统有电子商务系统、银行、证券系统等。

OLAP（On-Line Analytical Processing）：

在线分析处理是数据仓库的主要应用系统。所谓数据仓库，就是通过OLTP形成的一种数据。分析数据库就是我们负责处理商业智能和决策支持等重要决策信息；

数据仓库是数据库应用到一定程度后对历史数据的处理和分析。阅读更多，更新更少；

------------------------------------ ------ ----------------------------------

b) 分区还可以通过减少停机时间来提高可用性

例如：一个100GB的表，如果中间的数据损坏了，那么恢复简直是令人抓狂。

如果这张100GB的表被划分为50个2GB分区，当其中一个分区的数据损坏时，只需要恢复1个2GB分区的数据。

发生错误时的停机时间将大大减少，因为恢复所需的工作量显着减少。

2. 方便管理

将一个大对象分解为多个小对象。操作这些小物体显然比直接操作要容易操作原来的大对象。 ，并且占用资源更少。

3. 提高语句性能（主要针对OLAP系统）

a) 并行DML（Parallel DML）：

在Oracle 9i之前的版本中，PDML（Parallel DML）所需的分区；

在9i及以后的版本中，这个限制已经放宽，只有两种例外：

①希望在表上执行PDML，并且在表上有位图索引该表的 LOB 列。为了并行执行操作，必须对该表进行分区；

② 对于并行访问分区操作，需要访问的分区数量为Parallelism

------------------ ------延伸阅读：PDML（并行DML）------------ ----------

什么是并行技术？

对于大型任务，一般的做法是使用进程并串行执行。

但是如果系统资源充足的话，可以采用并行技术，将一个大的任务分成几个小任务，同时启用N个进程（或线程），并行处理这些小任务。 ，这些并发进程称为并行执行服务器，它们由一个称为并发协调进程的进程统一管理。

注意：

仅当您需要处理大型任务（例如需要几个小时的工作）并且有足够的系统时才执行此操作资源（包括CPU、内存、I/O等），应该考虑使用并行技术。

否则，在并发用户较多的环境下，系统本身的资源负担就很重。如果启用Parallel，某个会话会尝试占用所有资源，而其他会话则不得不等待，导致系统性能反而下降。

一般情况下，不要在OLTP系统中使用Parallel技术，但可以考虑在OLAP系统中使用。

PDML分类：

Parallel Query（并行查询） )

平行DML（并行DML语句执行）

并行DDL（并行DDL语句执行）

并行查询：并行查询允许将一个select语句分成多个较小的查询。查询的每个部分都是并发运行的，然后将每个部分的结果组合起来提供最终的结果。 （多用于全表扫描、索引全扫描等）

并行DML：并行DML包括插入、更新、删除、合并。在PDML期间，Oracle可以使用多个并行执行服务器（即并发进程）来执行插入、更新、删除和合并，多个会话同时执行。同时，每个会话（并发进程）都有自己的undo段，是一个独立的事务。这些事务要么由并发协调进程提交，要么同时回滚。

-------------------------------------------------------- ----- ----------------------------------------

b) 查询性能：

对于OLTP系统，分区操作需要谨慎使用，因为在传统的OLTP系统中，大多数查询很可能会立即返回因此，大部分数据可以通过较小的索引范围扫描来获得。因此，分区带来的性能优势在OLTP系统中可能根本体现不出来。

在OLTP系统中，如果分区应用不当，甚至可能会降低性能（分区可能会提高某些类型查询的性能，但这些查询通常不会在OLTP系统中使用）；

所以你必须明白一件事：分区并不总是与“性能改进”相关。

对于OLAP系统来说，分区消除和并行查询可能会带来效率的显着提升。

四：表分区机制

表分区的四种类型：

范围分区（Range）

哈希分区（Hash）

列表分区（List）

组合分区（Range – Hash 或 Range – List）

1. Range Partition：

Range分区将数据根据指定的分区键映射到各个分区。

这种分区方式最常用，经常使用日期作为分区键。

注意：

①每个分区需要有一个VALUES LESS THEN子句，该子句指定分区的上限（即分区的大小）最大可接受的分区键值）。当记录中分区键的值小于上限时，该记录将被放入分区中；当记录中分区键的值等于或大于上限时，该记录将被放入下一次上限更新中。在高隔断中。

② 所有分区中，除了第一个分区外，其他分区实际上都有一个隐含的下限值（即，分区可以接受的分区键的最小值）。该下限值是上限值。分区的上限。

③在最后一个分区中，可以将上限值定义为MAXVALUE（该值可以理解为所有分区中的最大上限值，包括空值）。当一条记录的分区键的值大于之前所有分区的上限时，该记录将被放置在最后一个分区中。

建表语句示例：

/******************************** * **********************范围分区示例************************ ******** ********************/ --创建示例表 create table range_example( id number(2), did_date date, data varchar2( 50)) --创建分区，分区键为示例表(range_example)中的done_date字段，按范围(done_date)分区(分区part_1值小于( to_date('20160901', 'yyyymmd)d') ), 分区part_2值小于( to_date('20161001', 'yyyymmdd') ), 分区part_3值小于( maxvalue )) --查看range_example表的分区信息 select * from user_tab_partitions where table_name = 'RANGE_EXAMPLE';

查看表分区信息

插入数据：可以看到示例表range_example已经被划分为三个分区。

记录1的done_date为2016/8/11，小于分区part_1的上限，则记录1将被放置在part_1分区中；

记录2的done_date为2016/9/8，大于分区part_1的上限但小于part_2的上限，则记录2将被放入part_2分区；< /p>

记录3的done_date是2016/10/20，大于前两个分区的上限值，所以会被放到最后一个maxvalue分区（part_3）中；

part_2的隐式下限值实际上是上一个分区part_1的上限值；

2. 哈希分区：

当对表进行哈希分区时，Oracle会对分区键应用哈希（Hash）函数来确定数据应该放置在N个分区中的哪一个

Oracle建议N为2的幂（如N=2、4、8、16等），这样表数据可以获得最佳整体分布。

当列值不具备合适的范围条件时，建议使用哈希分区。

注意：

如果更改哈希分区的数量（在哈希分区表中添加或删除分区时），数据将在所有分区之间重新分配，即：所有数据都将被重写，因为每一行现在可能属于不同的分区。

为表选择的哈希键（分区键）应该是唯一的列或具有多个不同值的列集，以便行可以被匹配跨多个分区。均匀分布在该区域。

如果您使用哈希分区，您无法控制一行数据最终位于哪个分区（由哈希函数控制）。

建表语句示例：

/******************************** * *************哈希分区示例************************************ *** ******************/ --创建示例表 create table hash_example( id number(2), did_date date, data varchar2(50)) --创建哈希分区，分区键是表 (hash_example) 中的 example did_date 字段，partition by hash (done_date)(分区part_1, 分区part_2) select * from user_tab_partitions where table_name = 'HASH_EXAMPLE';

3. 列表分区：

列表分区可以根据分区键的值明确指定哪些值数据应该放在哪个分区中。

注意：

如果列表分区中指定了默认分区，则记录其分区ion键值不在任何分区值列表中，将被放置在默认分区中；

默认分区创建后，您不能向该表添加更多分区；

p>

如果没有指定默认分区，则该分区将被插入到分区值列表中，当该键值不在分区值列表中的任何记录中时，Oracle会报错（ORA-14400：插入的分区键不映射）到任何分区）。

建表语句示例：

/******************************** * *************列表分区示例************************************ ********* **********/ --创建示例表 create table list_example( id number(2), name varchar(30), data varchar2(50)) --创建列表分区，分区键为示例表（list_example）中的id字段partition by list（id）（分区part_1的值（'1','3','5','7'）,分区part_2值('2','4','6','8'),分区part_default值(默认))select *from user_tab_partitions where table_name = 'LIST_EXAMPLE';

分区信息：

如上，分区键（即list_example表中的id和字段的记录）值为1， 3、5、7将被放置在part_1分区中；

分区键值为2、4、6、8的记录将被放置在part_2分区中；

分区键值为其他值的记录将被放置在最后一个part_default分区中。

4. 组合分区：

组合分区是范围分区和哈希分区的组合，或者是范围分区和列表分区的组合。

在组合分区中，顶层分区机制总是范围分区，二级分区机制可能是哈希分区或者列表分区；

数据在物理上是物理的存储在子分区段上，分区（顶级范围分区）成为逻辑分区cal 容器，或指向实际子分区的容器；

每个顶级分区不具有相同数量的子分区。

范围-哈希组合分区建表语句示例：

/************************ * ******************范围-哈希分区**************************** *** **********/ create table range_hash_example(id number(2), did_date date, data varchar2(50)) --顶级range分区的分区键为done_date字段在 range_hash_example 表中； - 不。二级哈希分区的分区键是range_hash_example表中的id字段；按范围分区 (done_date) 按哈希 (id) 子分区（分区part_1值小于（to_date('20160901', 'yyyymmdd')））（子分区part_1_sub_1，子分区part_1_sub_2），分区part_2值小于（to_date（ '20161001', 'yyyymmdd') ) (子分区part_2_sub_1,子分区part_2_sub_2),分区part_3 v值小于 ( maxvalue ) (子分区part_3_sub_1, 子分区part_3_sub_2)) select * from user_tab_partitions where table_name = 'RANGE_HASH_EXAMPLE'; 

分区信息：

在上面的范围-哈希组合分区中，Oracle首先会应用范围（Range）分区规则来找出数据属于哪个区间（即首先通过done_date字段判断该记录是属于part_1、part_2还是part_3）；

然后应用散列（Hash）函数来确定数据最终将被放置在哪个子分区（物理分区） in（即通过 id 字段确定该记录属于某个分区下的哪个子分区）

range-list组合分区表创建语句示例：

/* *************** *********************************范围- ListPartition**************** ************************/ 创建表 range_list_example( id number(2) , done_date date, data varchar2(50)) --pa顶级范围分区的rtition key是range_list_example表中的done_date字段； --二级列表分区的分区键为range_list_example表中的id字段；按范围分区（done_date）按列表（id）子分区（分区part_1值小于（to_date（'20160901'，'yyyymmdd'））（子分区part_1_sub_1值（'1'，'3'，'5' )、子分区part_1_sub_2值('2','4','6')),分区part_2值小于(to_date('20161001','yyyymmdd'))(子分区part_2_sub_1值('11) ', '13', '15', '17' ), 子分区part_2_sub_2值( '12', '14' ), 子分区part_2_sub_3值( '16', '18' ) ), 分区part_3值​​小于 ( maxvalue ) (子分区part_3_sub_1值('21','23','25'),子分区part_3_sub_2值('22','24','26'))) select * from user_tab_partitions where table_name = 'RANGE_LIST_EXAMPLE';

分区信息：

如图，每个顶级范围分区可以有不同数量的子分区。

5.总结

一般来说，如果需要按照某个值对数据进行逻辑聚合，往往会用到范围分区。例如，基于时间数据按“年”和“月”进行分区就是一个典型的例子。在许多情况下，范围分区可以利用分区消除功能（= >= <= Between...and 以及其他过滤条件）。

如果在表中找不到合适的属性来基于该属性完成范围分区，但又想享受分区带来的性能和可用性提升，可以考虑使用哈希分区。 （适合使用= IN等过滤条件）

如果数据中存在一列或者一组离散值，并且分区有意义通过这个专栏，那么这样的数据是非常有用的。适合列表分区。

如果某些数据c逻辑上可以进行范围分区，但是得到的范围分区仍然太大而无法有效管理，可以考虑使用组合分区。

注意：

分区是在最初创建表时创建的。如果以后想改变分区策略，需要先重建表。

--------------------------延伸阅读：自动递增（自增）分区------ ---------------------------------

前面提到过，我们提到了基于时间按“年”和“月”进行范围分区的典型示例数据。 ，这里是另一个应用场景：

假设有一个产品销售记录表（products_table），简单记录了产品的ID号、名称、销售时间；

当根据销量随着时间的推移进行范围分区时，由于表中的记录不断增加（产品每售出一次就会增加一条记录），可以考虑创建此时会添加一个自增分区；

顾名思义，当插入一条新记录时，Oracle会根据需要自动添加一个新分区来存储新记录（当该分区的值新插入的记录中的key不在任何现有分区范围内，Oracle会自动创建一个新分区）

可以根据需要指定自增分区的自增策略，如按日自增、按周自增、按月自增、按年自增等（具体自增声明可以百度搜索找到）；

示例在产品销售记录表中创建每月增加的范围分区：

create table products_table( id number(2), name varchar2(50) , sale_date date)partition by range(sale_date) Interval (numtoyminterval( 1,'month'))  (分区p_month_1值小于(to_date('2016-01-01','yyyy-mm-dd')) )

如图re，以products_table中的sale_date列为分区键，创建按钮每月自增分区；

所有销售时间在'2016-01-01'之前的记录都会放入p_month_1分区；< /p>

‘2016-01-01’之前的销售时间，后续插入记录时，Oracle会自动为该记录所属的月份创建分区；

例如，当有销售时，分别是January 20, 2016和2016 2 当插入两条1月20日的记录时，Oracle会创建一个上限为'2016-01-31'的分区和一个上限为'2016-01-31'的分区存储这些记录的上限为“2016-02-29”。两条记录

这就是如何理解Oracle分区表。希望以上内容能够对大家有所帮助，能够学到更多的知识。如果您觉得文章不错，可以分享出去，让更多的人看到。