【杂】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-关于分区表的执行计划

posted @ 2021-09-27 10:30  l.w.x  阅读(2203)  评论(0编辑  收藏  举报