Oracle分区表实践

你的名字 2021-06-10 20:39 457阅读 0赞

#### 分区裁剪的定义 ####

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

实验论证

分别创建分区表和普通表

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

第一列stu\_year，表示学生的入学年份；

第二列stu\_no，表示学号（5位序号），每年的新生都从00001开始；

第三列stu\_name，表示学生的姓名；

第四列stu\_memo，表示存储备注信息。

该表按入学年份，即stu\_year列进行了分区，分区的类型为列表分区。

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 1]  
执行上述操作后，分区表stu\_part 和普通表stu\_normal中拥有了完全相同的数据。  
分别在分区表和普通表查询信息  
分别在分区表stu\_part 和普通表stu\_normal查询入学年份为2015年，学号为00001的学生姓名：  
分区表查询用SQL：

![在这里插入图片描述][20200910162442742.png_pic_center]  
普通表查询用SQL：  
![在这里插入图片描述][20200910162446908.png_pic_center]  
这里采用了加gather\_plan\_statistics提示的方法来执行，并在执行后，在同一个会话窗口中，立即通过dbms\_xplan.display\_cursor()的方法来获取相应的执行计划及每一步的逻辑读消耗。

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

在分区表上执行后，得到的执行计划和运行统计信息如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 2]  
如上图所示，我们可以看到只访问了第4个分区，整个执行下来，消耗的逻辑读是380个块次。

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

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

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

分区表查询用SQL：

![在这里插入图片描述][2020091016250354.png_pic_center]  
在分区表上执行后，得到的执行计划和运行统计信息如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 4]  
从上图可见，由于去除了分区键列条件，造成对分区表的扫描是要访问全部5个分区。可以看到此时的逻辑读消耗是1896个块次。

普通表查询用SQL：  
![在这里插入图片描述][20200910162511828.png_pic_center]

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

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

在stu\_no学号列上创建索引

在分区表的STU\_NO列上创建本地分区索引：

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

在普通表的STU\_NO列上创建索引：

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

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

无分区键列条件时的情况

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

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

在普通表上执行后，得到的执行计划和运行统计信息如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 9]  
如上图所示，其结果与未去除分区键列条件时的情况是一样的。逻辑读的开销是8个块次。相较于前面分区表的开销，普通表的开销更小。

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

那么，如果在分区表的STU\_NO列上，创建一个非分区索引，还会有分区裁剪的作用吗？

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

![在这里插入图片描述][2020091016254514.png_pic_center]

然后执行带分区键列条件的SQL，得到如下的执行计划和运行统计信息：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 10]  
如上图中红框中内容所示，我们可知，该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条记录，确实属于不同的数据块：

细心或者是有经验的同学，一定发现了这次执行的查询中，由于在分区键列条件中传入的值的数据类型（数值型）与分区键列的数据类型（字符型）不一致，从而发生了隐式转换，并造成了分区键并未发挥出分区裁剪的作用。这一点，可以从执行计划中谓词信息中确认。如下图中红框所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 12]  
但考虑到这里访问的是非分区索引，且索引列上也没有分区键列，这个分区键列应该发挥不出什么作用。

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 13]  
从上图中，我们可以发现，在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性能是大有裨益的。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center]: /images/20210518/2869d126fc2841aa9ee22e540d128cf1.png
[20200910162430486.png_pic_center]: /images/20210518/a92281dcfb8141d186c6861b24e6e3f4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20210518/277f22ed74a94128819257894eda2586.png
[20200910162442742.png_pic_center]: /images/20210518/046aba10d8e2420cb450f83949ffb5de.png
[20200910162446908.png_pic_center]: /images/20210518/a1cc3c2a67654993a727484b55cdbb5b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20210518/23badce7393e4730b551a736dba064a6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20210518/a263bbc8f0af4aadaaa57bd75f0e1796.png
[2020091016250354.png_pic_center]: /images/20210518/d76a2d12228547a1a83554c3695bd60b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20210518/965c2b642cab4df7b66949c5405a95e7.png
[20200910162511828.png_pic_center]: /images/20210518/fc21037f13de4b4686522dfe87efc171.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20210518/94b8851a94c1485eaaa8ae0df1b9247d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20210518/4f0f4f50a17d45ab9de169fc69e04432.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20210518/39e87e066cf448a8850875da0c9a92dc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 8]: /images/20210518/430455ff60e148daa878d7fb406c67f5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 9]: /images/20210518/6065940eb1f04107a8c157da6b358119.png
[2020091016254514.png_pic_center]: /images/20210518/d9af9554a8774d43a499168a33efa87c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 10]: /images/20210518/3da564bebe5e49c58b731b0ef8dc21ff.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 11]: /images/20210518/d10a597df09343eca0dae5f82291b02c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 12]: /images/20210518/c7405fa2ccb84f4fbd69728702412b0f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzk2ODYxMw_size_16_color_FFFFFF_t_70_pic_center 13]: /images/20210518/0dc575cc45f048c88ff609aad607c903.png