【杂】Oracle使用记录：分区表及执行计划

　　不想深究，只想记录使用过程涉及的一些点，方便以后查阅，所以描述可能不准确也不全面，有些方法并没有用过所以，样例直接参考别人。

　　国庆将至，就将一些日常的使用笔记来硬混……后续应该会整理一下标签

1 Oracle分区表

　　Oracle分区表的描述比较复杂，包含一些手动分配表空间之类的配置过程，不同的资料给出的针对不同情况的解决方案也是五花八门。每次涉及的时候查找资料，就会感觉自己手里的这个表似乎没有必要做那么复杂的配置了。所以的话，只会往简单里说。

1.1 什么时候需要分区表？

　　什么时候需要分区表？当你搜索分区表的时候应该就是意识到需要用到分区表了。一般来说，一个是数据量太大，一个是因为有历史数据，需要更新的数据及历史数据放在不同分区会比较方便。

1.2 创建分区表

　　表分区有四种类型：

  • 范围分区(Range)：指定上限进行分区

  • 散列分区(Hash)：数据随机放入不同分区

  • 列表分区(List)：指定某列的值来决定分区

  • 组合分区(Range-Hash 或者 Range -list)

1.2.1 范围分区

/******范围分区*****
范围分区通过指定分区的上限来进行分区
可设置一个上限为maxvalue的分区
*******/
--创建示例表
create table range_example
(
 id number(2),
 done_date date,
 data varchar2(50)
)
--创建分区，分区键为示例表（range_example）中的 done_date 字段
partition by range (done_date)
(
  partition part_1 values less than ( to_date('20160901', 'yyyymmdd') ),
  partition part_2 values less than ( to_date('20161001', 'yyyymmdd') ),
  partition part_3 values less than ( maxvalue )
);
​
--查看range_example表的分区信息
select * from user_tab_partitions where table_name = 'RANGE_EXAMPLE';
​

1.2.2 散列分区

/********散列分区*****
散列分区通过hash函数对指定列进行加工后决定将数据放在哪个分区
无法控制数据具体放在哪个分区
****/
--创建示例表
create table hash_example
(
 id number(2),
 done_date date,
 data varchar2(50)
)
--创建散列分区，分区键为示例表（hash_example）中的 done_date 字段
partition by hash (done_date)
(
  partition part_1,
  partition part_2
);
​
select * from user_tab_partitions where table_name = 'HASH_EXAMPLE';
​

1.2.3 列表分区

/**列表分区*****
通过指定列的值来创建分区
可创建default分区，不属于其他分区的数据都会放进default
但是创建了default分区之后不可以再添加更多的分区了
*********************/
--创建示例表
create table list_example
(
 id number(2),
 name varchar(30),
 data varchar2(50)
)
​
--创建列表分区，分区键为示例表（list_example）中的 id 字段
partition by list (id)
(
  partition part_1 values ( '1', '3', '5', '7' ),
  partition part_2 values ( '2', '4', '6', '8' ),
  partition part_default values ( default )
);
​
select * from user_tab_partitions where table_name = 'LIST_EXAMPLE';

1.2.4 组合分区：范围-散列分区

/****组合分区：范围-散列分区******
组合分区就是将上面三种分区套在一起，在分区中创建子分区
其中主分区是指向子分区的逻辑地址，子分区是物理地址
可以有范围-散列分区和范围-列表分区
****/
create table range_hash_example
(
 id number(2),
 done_date date,
 data varchar2(50)
)
​
--顶层范围分区的分区键为 range_hash_example 表中的 done_date 字段；
--第二层散列分区的分区键为 range_hash_example 表中的 id 字段；
partition by range (done_date) subpartition by hash (id) 
(
  partition part_1 values less than ( to_date('20160901', 'yyyymmdd') )
  (
    subpartition part_1_sub_1,
    subpartition part_1_sub_2
  ),
  
  partition part_2 values less than ( to_date('20161001', 'yyyymmdd') )
  (
    subpartition part_2_sub_1,
    subpartition part_2_sub_2
  ),
  
  partition part_3 values less than ( maxvalue )
  (
    subpartition part_3_sub_1,
    subpartition part_3_sub_2
  )
);
​
select * from user_tab_partitions where table_name = 'RANGE_HASH_EXAMPLE';

1.2.5 组合分区：范围-列表分区

/****组合分区：范围-列表分区**************************************/
create table range_list_example
(
 id number(2),
 done_date date,
 data varchar2(50)
)
​
--顶层范围分区的分区键为 range_list_example 表中的 done_date 字段；
--第二层列表分区的分区键为 range_list_example 表中的 id 字段；
partition by range (done_date) subpartition by list (id) 
(
  partition part_1 values less than ( to_date('20160901', 'yyyymmdd') )
  (
    subpartition part_1_sub_1 values ( '1', '3', '5' ),
    subpartition part_1_sub_2 values ( '2', '4', '6' )
  ),
  
  partition part_2 values less than ( to_date('20161001', 'yyyymmdd') )
  (
    subpartition part_2_sub_1 values ( '11', '13', '15', '17' ),
    subpartition part_2_sub_2 values ( '12', '14' ),
    subpartition part_2_sub_3 values ( '16', '18' )
  ),
  
  partition part_3 values less than ( maxvalue )
  (
    subpartition part_3_sub_1 values ( '21', '23', '25' ),
    subpartition part_3_sub_2 values ( '22', '24', '26' )
  )
);
​
select * from user_tab_partitions where table_name = 'RANGE_LIST_EXAMPLE';

1.3 针对分区表的一些操作

我这里的应用主要是针对列表分区。

表名my_table，以字段month_part(字符类型)作为分区字段，分区命名month_part_yyyymm

1.3.1 查询表分区情况

--查询分区表存在情况
SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME
FROM USER_TAB_PARTITIONS
where upper(TABLE_NAME)=upper('my_table');

1.3.2 从分区表查询数据

对于数据量巨大的表，每次查询都需要进行全表查询，创建分区表之后，使用合适的方法，仅仅需要在对应分区查询就可以了，极大提高了查询的效率。

可以使用指定partition的方法，也可以使用where，但是使用where的时候需要注意，分区字段不能够进行转换，譬如说字符类型的分区字段，使用=数字类型数字，隐含了转换过程，还是会进行全表扫描的。

--从分区表查询数据
select * from my_table partition(MONTH_PART_202107);--分区必须存在
select * from my_table where month_part='202107'; --仅在分区查询
select * from my_table where month_part=202107; --由于分区字段是字符类型，中间进行了转换，所以是全表查询

1.3.3 向已存在的分区插入数据

向分区插入数据的前提分区已存在，散列分区不存在分区不存在的问题，范围分区和列表分区可以设置默认分区来避免分区不存在的报错。

数据插入分区表，可以指定分区，也可以不指定分区，都是能正常插入对应分区的。只试过列表分区，散列分区不确定能不能指定分区插入。

---插入数据
insert into my_table select * from my_table_t where month_part='202109';
insert into my_table partition(MONTH_PART_202109) select * from my_table_t where month_part='202109';

1.3.4 添加分区及删除分区

需要注意的是，列表分区如果已经创建了default分区，不可再添加新的分区，需要删除。

添加及删除分区的前提是该表已经是分区表。

--删除分区
alter table my_table DROP PARTITION(MONTH_PART_202109);
alter table my_table DROP PARTITION(MONTH_PART_other);--之前创建的默认分区
​
--新建分区
alter table my_table add partition MONTH_PART_202109 values ('202109');
​

​

1.3.5 清空分区数据

如果一个表需要频繁删除数据插入数据，使用delete+insert的话，表内会产生大量的内存碎片，表空间会膨胀，查询速度也会变慢。所以建议使用truncate+insert。

如果每次删除数据时还需要保留一部分，在允许的情况下可以使用分区表的truncate+insert。

--清空分区数据
alter table my_table TRUNCATE PARTITION(MONTH_PART_202109);

1.3.6 合并分区

ALTER TABLE TABLENAME MERGE PARTITIONS P1,P2 INTO PARTITION P2;

1.3.7 复制分区表的分区结构和索引

现在找到的唯一方式是：想办法生成原表建表包含的全部结构的sql语句。Oracle-如何使用plsql导出建表语句提供了返回创建指定表的结构的sql语句（仅包含结构，不包含数据），会包含分区表开始及后续添加的分区的详细情况、索引情况、授权情况。

上面连接给的是英文版plsql导出sql语句的方法，即在sql运行界面右键对应表，选择【view】，在弹出页面右下角【View SQL】即可。

中文版，在sql运行界面右键对应表，选【查看】，在弹出页面右下角【查看SQL】即可。

 

1.3.8 分区表和索引

参考：分区表尽量不要建主键

说的是因为建主键的同时会建一个唯一性的全局索引，在drop分区表时如果不指定update global indexes则会使索引失效，导致数据无法入库。

文章说可以将主键建成本地索引，但是我并没有深入了解。而且建立分区表的原因之一是数据量过大，而数据量过大的时候维护索引也是很大的开销。

2、普通表转分区表

普通表转换为分区表，Oracle给我们提供了以下的方式：

  • 数据泵导入

  • 子查询方式插入

  • 分区交换

  • 在线重定义

  • ALTER TABLE…MODIFY…方式（12.2之后支持）

其中除了ALTER TABLE…MODIFY…方式可以直接操作源表更改为分区表并保留索引，数据泵导入、子查询方式插入、分区交换、在线重定义都需要额外创建一个同结构的分区表，然后将原表数据及索引等结构以各种方式转到新的分区表中。

我使用的是子查询插入，即

  1. 使用语句创建同结构分区表

  2. 添加分区

  3. 将数据插入分区表

我这里是因为数据还不多，分区还不多，如果分区过多，可以考虑使用存储过程来进行这个过程。

3、执行计划

3.1 有关sql优化的看法

无论是创建索引还是创建分区表，都是为了优化SQL语句，提高查询效率，只是表结构更改之后进行查询，除了自己感受到的查询速度的变化，如何能更清楚直观的看待SQL语句优化后的效果呢？就像是分区表中字段值分区后，因为使用select * from my_table where month_part=202107并没有使用到分区查询不是白费力气？

本来是为了找如何分析sql语句执行过程的，找了半天，我找到了文章基于oracle的sql优化，除了我要找的东西以外，还有一些SQL优化相关的分析内容，后续感兴趣也可以进行一下整理。

3.2 Oracle执行计划

3.2.1 SQL语句

执行计划是一条查询语句在Oracle中执行过程或者访问路径的描述。

执行计划分析的过程如下所示：

EXPLAIN PLAN FOR select * from dual;
​
select * from table(dbms_xplan.display);

结果：

 

3.2.2 执行计划解释

借用下图，其中横向列出来的基数是指返回结果集行数，字节是执行该步骤后返回的字节数，耗费是Oracle估计的该步骤的执行成本，用于说明SQL执行的代价，理论上越小越好。可以与上图对应。

  

而对象(Name)指向操作的对象，Description(Operation)则是执行的操作，包含访问表及访问表的方式。

这些操作的执行顺序：缩进越多的最先执行，缩进相同时，最上面的最先执行。

3.2.3 访问表的方式

  • TABLE ACCESS FULL：全表扫描，Oracle会读取表中的所有行，并检查是否满足where语句中的条件。当数据量太大时，建议避免全表扫描。

  • TABLE ACCESS BY ROWID：通过rowid的表存取。

    • select rowid from dual

    • rowid：oracle会自动加在表的每一行的最后一列伪列，表中并不会物理存储rowid的值，一旦一行数据插入后，则其对应的rowid在该行的生命周期内是唯一的，即使发生行迁移，该行的rowid值也不变。

  • 行迁移：前面说“即使发生行迁移，该行的rowid值也不变”是我在文章中看到的一个说法，但是在其他文章有说法，如果需要改变rowid值，那么需要启用row movement特性，rowid发生变化的前提是启用row movement特性。

  • TABLE ACCESS BY INDEX SCAN：索引扫描。在索引块中即存储每个索引的键值，也存储具有改键值所对应的rowid。索引的扫描分为两步：首先是找到索引所对应的rowid，其次通过rowid读取该行数据。索引扫描又分五种：

    • INDEX UNIQUE SCAN：索引唯一扫描。针对唯一索引的扫描，每次至多只返回一条记录，主要针对该字段为主键或者唯一。

    • INDEX RANGE SCAN：索引范围扫描。使用一个索引存取多行数据。发生范围索引扫描有三种情况：1)在唯一索引上使用了范围操作符(如:>,<,<>,>=,<=,between) 2) 在组合索引上，只是用部分进行查询(查询时必须包含前导列，否则会走全表扫描) 3)对非唯一索引列进行的任何查询。

    • INDEX FULL SCAN：索引全扫描，进行全索引扫描时，查询出的数据都必须从索引中可以直接得到。

    • INDEX FAST FULL SCAN：索引快速扫描。扫描索引中的所有的数据块，与INDEX FULL SCAN类似，但是一个显著的区别是它不对查询出的数据进行排序。

    • INDEX SKIP SCAN：索引跳跃扫描。Oracle 9i后提供，有时候复合索引的前导列（索引包含的第一列）没有在查询语句中出现，oralce也会使用该复合索引，这时候就使用的INDEX SKIP SCAN;

      当Oracle发现前导列的唯一值个数很少时，会将每个唯一值都作为常规扫描的入口，在此基础上做一次查找，最后合并这些查询；

    其实认真看来，有些描述并不准确，而且还有许多如分区、并行等等相关的并未详细整理，但是应该能看懂其执行计划的结果了。

3.3 分区表查询语句执行计划分析

前面并没有提到的有关分区的访问方式：

  • partition list single：扫描单个分区

  • partition list iterator ：连续扫描N个分区

  • partition list inlist：不连续扫描N个分区

  • partition list full：扫描全部分区

表名:MY_TABLE，分区字段MONTH_PART，与分区字段同值的非分区字段MONTH_ID。分区情况如下。

TABLE_NAMEPARTITION_NAMETABLESPACE_NAME
MY_TABLE MONTH_PART_202106 ST_BIG_TAB_03
MY_TABLE MONTH_PART_202107 ST_BIG_TAB_03
MY_TABLE MONTH_PART_202108 ST_BIG_TAB_03
MY_TABLE MONTH_PART_202109 ST_BIG_TAB_03
MY_TABLE MONTH_PART_202110 ST_BIG_TAB_03

3.3.1 单分区查询对比

包含如下语句，可以从结果看出来，只有前两种方式是成功实现了分区表查询（PARTITION LIST SINGLE）,其他语句都是全表扫描（ PARTITION LIST ALL）。后三种全表扫描花费的预估时间差不多，使用partition访问比使用where更快。

而全表扫描并且有添加where语句，似乎都会表现为filter。而filter中也会包含一些隐含的转换，如限制字符类型等于某个数字输入，是会将表中字符类型的数据进行to_number。那么假设这个字段如month_part包含非数字字符，是否会报错呢?如果将where month_part=202107更改为month_part=to_char(202107)是否就能避免这个错误？

--1 PARTITION LIST SINGLE
EXPLAIN PLAN FOR select * from MY_TABLE partition(month_part_202107);
--2 PARTITION LIST SINGLE
EXPLAIN PLAN FOR select * from MY_TABLE where month_part='202107';
--3 PARTITION LIST ALL
EXPLAIN PLAN FOR select * from MY_TABLE where month_part=202107;
--4 PARTITION LIST ALL
EXPLAIN PLAN FOR select * from MY_TABLE where month_id ='202107';
--5 PARTITION LIST ALL
EXPLAIN PLAN FOR select * from MY_TABLE where month_id =202107;
​
select * from table(dbms_xplan.display);

 

3.3.2 多分区查询对比

如果单单从下面5个语句的结果来看，感觉并没有问题，执行过程与预期相符。where .. in ..与where ..or ..是大体等价的。使用union all的话在这种访问多分区的情况下可能会是一个很好的选择（尤其在开并行的情况下）。

只是这个时间TIME让人有些疑惑，似乎在使用month_id，而不是month_part，其预估的时间time反而会少一点。我并没有找到相关说法，或许只是我对这个字段理解有误。

但是在表中month_part与month_id是相等的，从前面单分区查询开始，其返回的行数Rows就不相等……甚至month_part='202107' 和month_part=202107也不相等，让人相当疑惑。

---1、partition list inlist 不连续分区扫描
EXPLAIN PLAN FOR select * from MY_TABLE where month_part in ('202107','202108');
---2、partition list inlist 不连续分区扫描
EXPLAIN PLAN FOR select * from MY_TABLE where month_part='202107' or month_part='202108';
--3、partition list iterator 连续分区扫描
EXPLAIN PLAN FOR select * from MY_TABLE where month_part<='202107' ;
--4、partition list single 单个分区扫描
EXPLAIN PLAN FOR 
select * from MY_TABLE 
where month_part='202107' 
union all
select * from MY_TABLE 
where month_part='202108' ;
--5、partition list full 全部分区扫描
EXPLAIN PLAN FOR select * from MY_TABLE where month_id in ('202107','202108');
​
select * from table(dbms_xplan.display);

 

3.3.3 更多的分区访问类型

参考:Oracle分区执行计划

range分区可用的操作

分区表，按 n1 ,n2 分区

  • partition range single:访问单个分区

  • partition range iterator:访问多个分区

  • partition range inlist: 分区键中用了in 例如： where n1 in(X1,X2) and n2=X3

  • partition range all: 所有的分区

  • partition range empty: 条件在分区中不存在 （或者说是找不到数据）

  • partition range or: 分区键中用了or 例如 where n1=X1 or n2=X2

  • partition range subquery:

  • partition range join-filter:

  • partition range multi-column:

 

hash分区可用的操作

  • partition hash single:

  • partition hash iterator:

  • partition hash inlist

  • partition hash all

  • partition hash subquery

  • partition hash join-filter

比range少了partition range or和partition range multi-column

 

list分区可用的操作

  • partition list single

  • partition list iterator

  • partition list inlist

  • partition list all

  • partition list empty

  • partition list or

  • partition list subquery

  • partition list join-filter

3.4 开平行

这是一个额外话题，只记录一下，并不想展开。

下面也是分析分区表的结果之一，我跑完3.3全部语句，并且截了图，对截图结果一脸疑惑不知道怎么开始说，因为结果与我预期的并不一样，结果大部分都如下图所示没什么差别，也就TIME上以及使用union all的时候有点差异。

后面发现，是因为我运行执行计划分析的SQL界面，之前我用的时候开了并行的缘故。 所以并不深入研究了。只一个点记录一下，在对表进行多分区查询的时候，使用union all的方式在并行的情况下比用in、or的方式快了很多。

EXPLAIN PLAN FOR select * from my_table where month_part='202107';

 

 

参考

Oracle分区表

Oracle数据库表分区

Oracle 分区表的新增、修改、删除、合并。普通表转分区表方法

Oracle普通表按月转分区表，通过PLSQL包一键生成分区表

Oracle 执行计划（Explain Plan） 说明

ORACLE 在线重定义分区表

分区表交换分区

ORACLE 19.5 测试ALTER TABLE ... MODIFY转换非分区表为分区表

基于oracle的sql优化

Oracle 行迁移详解

ORACLE-关于分区表的执行计划

当你深入了解，你就会发现世界如此广袤，而你对世界的了解则是如此浅薄，请永远保持谦卑的态度。
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/liwxmyself/p/15341839.html