举一反三-分区裁剪作用的“新”发现

作者介绍

赵勇 云和恩墨北区技术工程师

专注于SQL审核和优化相关工作。曾经服务的客户涉及金融保险、电信运营商、政府、生产制造等行业。

分区裁剪的定义

分区表的实质是采用化整为零的思想,将一个大对象划分为若干个小的对象。当数据的访问,被限定在几个较小的对象,甚至只发生在1个小对象上时,这时所访问的数据量,只是原来的几分之一,几十分之一、几百分之一,甚至更少。其性能较未分区时,会得到极大的提升。当发生这种情况时,我们称之为分区裁剪(或分区消除)。

实验论证

分别创建分区表和普通表

假设该表是用于存储学生信息的,其上共有四列:

第一列stu_year,表示学生的入学年份;

第二列stu_no,表示学号(5位序号),每年的新生都从00001开始;

第三列stu_name,表示学生的姓名;

第四列stu_memo,表示存储备注信息。

该表按入学年份,即stu_year列进行了分区,分区的类型为列表分区。

再创建一个同样列信息的普通表:

向两个表中插入模拟数据

总共5000行数据,每个学年的学生人数为1000人,且学号不重复。但整体来看,每个学号,都是5个重复值。

执行上述操作后,分区表stu_part 和普通表stu_normal中拥有了完全相同的数据。

分别在分区表和普通表查询信息

分别在分区表stu_part 和普通表stu_normal查询入学年份为2015年,学号为00001的学生姓名:

分区表查询用SQL:

普通表查询用SQL

这里采用了加gather_plan_statistics 提示的方法来执行,并在执行后,在同一个会话窗口中,立即通过dbms_xplan.display_cursor()的方法来获取相应的执行计划及每一步的逻辑读消耗。

查看执行后的计划和统计信息

在分区表上执行后,得到的执行计划和运行统计信息如下

如上图所示,我们可以看到只访问了第4个分区,整个执行下来,消耗的逻辑读是380个块次。

在普通表上执行后,得到的执行计划和运行统计信息如下

如上图所示,对于普通表的访问,虽然同样是全表扫描,但由于这里的全表扫描,是要对5000行数据的全扫,所以,逻辑读是1728个。而对于分区表的全表扫描,只是对其中一个分区的1000行数据进行全表扫描。

因此,发生了分区裁剪的分区表上的资源开销是更小的。

从WHERE子句中去除分区键的条件

分区表查询用SQL

在分区表上执行后,得到的执行计划和运行统计信息如下:

从上图可见,由于去除了分区键列条件,造成对分区表的扫描是要访问全部5个分区。可以看到此时的逻辑读消耗是1896个块次。

普通表查询用SQL

在普通表上执行后,得到的执行计划和运行统计信息如下:

从上图可知,仍然是全表扫描。与未去除分区键列条件时的情况是一样的。逻辑读的开销仍然是1728个块次。

我们可以看到,由于分区表要访问所有的分区,其逻辑读的开销是高于同样数据量的普通表的。

在stu_no学号列上创建索引

在分区表的STU_NO列上创建本地分区索引

在分区表上执行后,得到的执行计划和运行统计信息如下:

如上图所示,只访问了单个分区(即第4个分区)。而且,不仅只访问了分区表上的第4个分区,而且,也只访问了5个本地分区索引中的第4个。即在分区表和分区索引上,都发生了分区裁剪。由于上述原因,逻辑读的开销大幅下降到只有4个块次了。

在普通表的STU_NO列上创建索引:

在普通表上执行后,得到的执行计划和运行统计信息如下:

如上图所示,这里采取了对普通表上的STU_NO列上的索引的范围扫描。其逻辑读的开销是8个块次。

相比较而言,我们可以看到通过普通索引对表访问所需的逻辑读开销,比通过分区索引对表访问的要大。

无分区键列条件时的情况

在分区表上执行后,得到的执行计划和运行统计信息如下:

如上图所示,由于没有了分区键列条件的限制,需要对全部分区进行扫描。即先对5个本地分区的索引进行扫描,根据得到的满足学号条件的记录的ROWID,再回表去获取STU_NAME列的数据并返回。所以,对分区表也是全部5个分区都访问到了。其逻辑读的全部开销是16个块次。

在普通表上执行后,得到的执行计划和运行统计信息如下:

如上图所示,其结果与未去除分区键列条件时的情况是一样的。逻辑读的开销是8个块次。相较于前面分区表的开销,普通表的开销更小。

通过上面的测试,我们可以得到一个结论:当未发生分区裁剪时(即所有分区均要访问时),其资源开销要高于同样行数的普通表和普通索引。

那么,如果在分区表的STU_NO列上,创建一个非分区索引,还会有分区裁剪的作用吗?

首先,我们在STU_NO列上创建非分区索引。由于此前已经在该列上创建过本地分区索引,所以,需要先把该索引删除后,才能创建同一列上的非分区索引。

然后执行带分区键列条件的SQL,得到如下的执行计划和运行统计信息:

如上图中红框中内容所示,我们可知,该SQL的执行顺序是先通过索引IND_STU_PART_NO_GLOBAL找到5条学号为00001的记录(对应执行计划中ID为2的步骤)。由于最终查询结果是学生姓名,且还要满足SUT_YEAR=2015这个条件,而这两列并未在索引中出现,所以,需要通过索引中存储的对应记录ROWID回表,从表中获取相应5行记录的相关信息,并使用STU_YEAR列,过滤出等于2015的记录(对应执行计划中ID为1的步骤)。

同时,我们可以看到ID为2的步骤,发生了3个块次的逻辑读。通过前面的数据构建,我们可知,会有5条学号为00001的记录。所以,通过索引,我们可以找到5条索引键值为00001的索引记录,并得到这5条记录的ROWID。在ID为1的步骤,通过这5个ROWID,直接访问对应的5个数据块。因为这5条记录,是属于不同的入学年份,所以,一定是在不同的5个分区中,因此,一定是5个数据块。

此外,通过下面的操作,我们也可以验证这5条记录,确实属于不同的数据块:

加上上一步中消耗的3个块次逻辑读,至此,总共消耗了8(3+5)个块次的逻辑读。

细心或者是有经验的同学,一定发现了这次执行的查询中,由于在分区键列条件中传入的值的数据类型(数值型)与分区键列的数据类型(字符型)不一致,从而发生了隐式转换,并造成了分区键并未发挥出分区裁剪的作用。这一点,可以从执行计划中谓词信息中确认。如下图中红框所示:

但考虑到这里访问的是非分区索引,且索引列上也没有分区键列,这个分区键列应该发挥不出什么作用。

但我们实际验证后,发现情况不是我们想的那样了。如下图所示:

从上图中,我们可以发现,在ID为2的那一步,与之前没有区别,仍是访问同样的索引,消耗的逻辑读仍是3。但ID为1的那一步,显示至此步止,共发生了4个块次的逻辑读。减去其上一步的3个块次的逻辑读,实际这一步,只发生了一个块次的逻辑读。相比于此前的写法,整整少了4个。其原因应该是由于我们这次的写法,没有在分区键列上发生隐式转换,使得分区键列发挥了作用(这一点,从Pstart和Pstop列中的数值4,也可以证明,在这次执行过程中,只访问了第4个分区。)。

但是我的执行计划的第一步是访问一个非分区的索引,获得了5条学号为00001的记录的ROWID,由于还要用STU_YEAR列上的条件进一步过滤,以及要获取STU_NAME列上的值,所以,需要通过ROWID回表,去获取这两列上的值,并进行过滤,但回表这一步只访问了一个数据块。

Oracle是通过什么样的方法,知道这五行记录中只有一行是需要回表的。那么它又是如何做到的呢?

经过思考,其实,由于不同的分区有不同的对象号(OBJECT_ID),ROWID又是由对象号、文件号、块号和块内行号所组成的。如下图所示:

所以,在进行这一步操作时,根据条件<stu_year=’2015’>,数据库就知道只有相应分区内的数据才可能满足这个条件。

通过数据字典,还可以知道这个分区的对象ID,从获取到的5个ROWID中,可以解析出相应的对象ID。所以,属于另外4个分区的ROWID是没有必要回表访问的。这也就造成索引虽然返回了5个ROWID,但只有其中1个ROWID才是可能满足条件的,才是需要回表的。因此,也就发生了我们前边看到的,在这一步中,只发生了1个块次的逻辑读。

结论

在分区表上,通过访问非分区索引回表时,分区键列条件依旧有分区裁剪的作用,可以明显减少数据访问的开销。

对于分区表,我们在编写SQL时,要尽可能地把分区键列上的条件带上,并正确书写,即避免在分区键列上进行函数或算术运算,避免出现数据类型不一致等可能造成分区键列失效的情况。这对于发挥分区表上的分区裁剪作用,提升SQL性能是大有裨益的。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-09-15

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏偏前端工程师的驿站

编译期类型检查 in ClojureScript

前言  话说"动态类型一时爽,代码重构火葬场",虽然有很多不同的意见(请参考),但我们看到势头强劲的TypeScript和Flow.js,也能感知到静态类型在某...

1767
来自专栏java一日一条

Stackoverflow上人气最旺的10个Java问题

我一直认为Java是引用传递;然而,我看了一堆博客(例如这篇)声称不是这样的。我认为我没有理解它们之间的区别。

512
来自专栏大壮

iOS runtime(理论篇)

1495
来自专栏灯塔大数据

塔秘 | 从Zero到Hero,一文掌握Python关键代码

前 言 本文整体梳理了 Python 的基本语法与使用方法,并重点介绍了对机器学习十分重要且常见的语法,如基本的条件、循环语句,基本的列表和字典等数据结构,此...

3348
来自专栏前端架构

JavaScript-数据结构和算法(程序=数据结构+算法)

数据结构是对在计算机内存中(有时在磁盘中)的数据的一种安排。包括数组、链表、栈、二叉树、哈希表等。

401
来自专栏Java呓语

原型模式(克隆生成对象)

暂时抛弃掉之前的上下文(机器人 Samu与主人 Alice),创建型模式总不能很好对应机器人的上下文。

806
来自专栏C/C++基础

C++引用计数(reference counting)技术简介(3)

要想将引用计数施加到现有的实值对象Widget上,按照前面讨论的,都需要修改Winget类的源代码。但是,有时程序库的内容不是我们呢可以修改的,又该如何做呢?

561
来自专栏linux驱动个人学习

Linux进程ID号--Linux进程的管理与调度(三)【转】

Linux 内核使用 task_struct 数据结构来关联所有与进程有关的数据和结构,Linux 内核所有涉及到进程和程序的所有算法都是围绕该数据结构建立的,...

581
来自专栏Java学习网

通常Java开发人员如何进行数据排序?

在实际工作中和平时学习中,以及分析开源Java项目的大量源代码后,我发现Java开发人员通常使用两种方法。一是使用Collections或 Arrays的 so...

21510
来自专栏文武兼修ing——机器学习与IC设计

队列及其实现队列队列的实现

队列 队列即FIFO,一言以蔽之就是先进先出。比如入队列的顺序是1,2,3,4,那么出队列的顺序也是1,2,3,4 队列的实现 软件——GO语言实现 除了使用链...

3037

扫描关注云+社区