在这个简短系列的第1部分中，我提供了两个场景的非正式描述，在这些场景中，我们可以从表中进行大规模删除。没有一个具体的例子,很难想象删除数据的性质和可用的访问路径会产生大数据量删除操作对系统的性能影响,所以我要把大部分的时间花在本文讨论的两个测试生成的数据集。这篇文章似乎有点长但相当多的空间会被表格占用。

简单的数据集

随着硬件的能力和规模的不断增长，我们越来越难以就“大表”或“大规模删除”的含义达成一致，对于一个人来说，100万行似乎很大，而对于另一个人来说，1亿行似乎相当普通。
我将使用一个折中方案，用1000万行表示一个投资系统，该系统10年来以每年100万行的速度增长，并且已经达到了1.6GB的段大小。
当然，这个表只是组成整个系统的几个表中的一个，在某个时候我们会对所需要的数据担心，但是，目前，我们只考虑这个表，只考虑表本身和表上的4个索引。
下面是生成数据集的代码:

executedbms_random.seed(0)createtablet1(idnotnull,date_open,date_closed,deal_type,client_ref,small_vc,padding)nologgingaswithgeneratoras(select/*+materializecardinality(1e4)*/rownumidfromdualconnectbyrownum<=1e4)select1e4*(g1.id-1)+g2.idid,trunc(add_months(sysdate,-120)+(1e4*(g1.id-1)+g2.id)*3652/1e7)date_open,trunc(add_months(add_months(sysdate,-120)+(1e4*(g1.id-1)+g2.id)*3652/1e7,12*trunc(dbms_random.value(1,6))))date_closed,cast(dbms_random.string('U',1)asvarchar2(1))deal_type,cast(dbms_random.string('U',4)asvarchar2(4))client_ref,lpad(1e4*(g1.id-1)+g2.id,10)small_vc,rpad('x',100,'x')paddingfromgeneratorg1,generatorg2whereg1.id<=1e3andg2.id<=1e4;executedbms_stats.gather_table_stats(user,'t1',method_opt=>'forallcolumnssize1')altertablet1addconstraintt1_pkprimarykey(id)usingindexnologging;

date_open：从过去的120个月(10年3652天)开始，用于增加值的算法意味着最近的条目在当前日期。
date_closed：是添加到date_open(该表是记录定期投资的简单模型)的1到5年(包括5年)之间的整数。
deal_type：是随机生成的单个大写字符——生成26个不同的值，这些值具有相同的数据量;
client_ref：是随机生成的一个固定长度的字符串，由4个大写字母组成，每个组合提供大约50万个组合和20行。
note：作为补充说明-已经生成的数据集没有使用rownum在任何地方的高容量选择;这将使我能够使用并行执行更快地生成数据(“level”和“rownum”伪列都限制了Oracle使用并行执行的能力)。但是在本例中，因为我希望id列对按到达顺序存储的按顺序生成的值进行建模，所以我是按顺序运行代码的。

规模

我的笔记本电脑上是在Linux 5 VM上运行了database 12.1.0.2，我得到了创建数据、收集统计数据和创建索引所花费的时间如下:
表创建:7:06.40
数据收集:0:10.54
PK主键:0:10.94
创建索引:0:10.79 (date_open)
创建索引:0:12.17 (date_closed)
创建索引:0:13.65 (client_ref)
当然，这就要我们开始提一个很现实问题，即不同的系统可能会有不同的时间消耗结果。
虚拟机分配4 gb的内存(1.6 gb是留出memory_target)和一个四核CPU 2.8 ghz的CPU,但可能最重要的是机器1 tb的固态盘,所以不会失去太多时间在物理I / O。
数据库配置了3个重做日志组，每个重做日志组的大小为200MB(为了日志文件检查点和日志文件切换等待出现一些延迟)，日志是重复的，但是实例没有在archivelog模式下运行。
在stats收集之后，大多数块中的表块计数大约为204,000个块，每个块有49行，PK索引和client_ref索引大约有22,000个叶块，两个日期索引大约有26,500个叶块。

Quality

当使用这样的模型来质疑它们与现实生产中有多接近时是非常重要的。到目前来看，在我所的的准备工作中，你能发现其中存在哪些问题呢？
首先，表中的Id列太完美了，id列在表中的顺序从小到大排列的非常有序，然而在现实当中，并发性的插入会有一点都抖动，一定范围内连续性的值可能分布在少量的块上，这可能不是很重要，重要的是我是在创建表之后插入数据才创建的索引，这意味着索引在物理上来看是没有什么问题。（每个块中有10%的自由空间），我应该先创建一张空的表，然后在表上建立索引，在这之后再运行几个并发性的脚本使用序列进行单行插入来生成id，但是我上次这样创建的时候，所需要的时间增加了40倍。同样的，这可能也不是很重要，我记得在生产系统中索引的叶块中平均可用空间在任何时候都接近30%。随着块与块之间明显的变化差异，我想时不时的通过基于叶块状态的检查，尤其是date_open这个索引。

场景

尽管任何时间消耗都取决于机器的配置和资源的分配，并且这个模型过于简单化，但是我们任然可以从一些基本的测试当中获取一些有意思的信息。让我们从几个与业务相关的的场景开始：
a、删除所有5年前完成的交易
b、删除client_ref以“A”-“E”开头的所有交易
c、删除所有5年以上的交易
a项可能在删除前已经做了一次最基本要求的归档，也可能已经cpye到另一张表中了。
b项可能告诉我们，client_ref已经(ab)用于在第一个字母中为引用编码一些重要的分类，我们将数据分成两个处理集。
c项可能是按照date_open对数据进行分区的过程的一部分。（虽然我不确定在这种情况下分区是不是一个好方法），在做任何对于数据库来说影响比较大的操作之前，最好看看时刻能够可视化的知道oracle将要做什么？执行的步骤是什么，以及工作负载会出现在哪里？这些场景都是相同的吗？如果不是，他们有什么不同？如果你不知道你的数据以及你删除数据的影响，你可以从数据库中寻求答案-举个例子：

selectrows_in_block,count(*)blocks,rows_in_block*count(*)row_count,sum(count(*))over(orderbyrows_in_block)running_blocks,sum(rows_in_block*count(*))over(orderbyrows_in_block)running_rowsfrom(selectdbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid),count(*)rows_in_blockfromt1----wheredate_open>=add_months(sysdate,-60)--wheredate_open<add_months(sysdate,-60)----wheredate_closed>=add_months(sysdate,-60)--wheredate_closed<add_months(sysdate,-60)----wheresubstr(client_ref,2,1)>='F'--wheresubstr(client_ref,2,1)<'F'--groupbydbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid))groupbyrows_in_blockorderbyrows_in_block;

您将注意到，在这个查询中，我有六个注释谓词(在三个互补对中)。这个查询的基本目的是让我总结一下有多少块可以容纳多少行。但是每对谓词都让我对每种场景的效果有了一些想法-每一对中的一个告诉我关于将要删除的数据量和模式的一些信息。下面是sql*plus
中执行如上查询的输出：

BlocksRowsRowsperblockBlocksRowsRunningtotalRunningtotal------------------------------------------------------------2712712749203,8779,989,973203,8789,990,0005020010,000204,07810,000,000--------sum204,078

下面的输出显示了如果删除了5年以上打开的数据行，留下来的数据将会是什么样子?(也就是说，使用谓词date_open >= add_months(sysdate，-60))

BlocksRowsRowsperblockBlocksRowsRunningtotalRunningtotal-------------------------------------------------------------271271274214226949102,0144,998,686102,0164,998,755--------sum102,016

这看起来相当不错--粗略的来说我们已经将表一半的块清空了，另一半没有动。如果我们现在尝试‘收缩空间’，那么我们只需要将表的下半部分复制到表的上半部分。我们会生成大量的undo数据和redo日志。但是任何索引的任何聚簇因子可能没有一点改变。另一种选择是，如果我们决定让空白空间保持原样，那么任何新数据都会非常有效地开始填充空白空间（几乎就想是重新分配区一样），同样的我们也会看到任何聚簇的因子也没有什么改变。将此结果与删除所有5年前关闭的行所带来的结果进行比较，（也就是说，如果我们使用谓词date_closed>= add_months(sysdate，-60)，会看到什么?）这个结果集 会大很多。

BlocksRowsRowsperblockBlocksRowsRunningtotalRunningtotal--------------------------------------------------------------15555222442749311333914038842811,1244211,51256803,4001,1014,91261,2567,5362,35712,44871,85612,9924,21325,44082,50820,0646,72145,50492,87525,8759,59671,379102,96129,61012,557100,989112,62128,83115,178129,820122,22226,66417,400156,484131,81223,55619,212180,040141,55021,70020,762201,740151,54323,14522,305224,885161,61125,77623,916250,661171,97633,59225,892284,253182,16839,02428,060323,277192,41645,90430,476369,181202,31746,34032,793415,521212,31048,51035,103464,031222,08045,76037,183509,791231,83342,15939,016551,950241,69640,70440,712592,654251,76944,22542,481636,879261,79946,77444,280683,653272,13857,72646,418741,379282,25163,02848,669804,407292,44870,99251,117875,399302,33970,17053,456945,569312,28670,86655,7421,016,435321,86459,64857,6061,076,083331,70456,23259,3101,132,315341,56653,24460,8761,185,559351,55654,46062,4321,240,019361,85066,60064,2821,306,619372,13178,84766,4131,385,466382,58398,15468,9961,483,620392,966115,67471,9621,599,294402,891115,64074,8531,714,934412,441100,08177,2941,815,015421,93281,14479,2261,896,159431,30055,90080,5261,952,0594468330,05281,2091,982,1114529113,09581,5001,995,206461074,92281,6072,000,12847321,50481,6392,001,63248314481,6422,001,77649122,4125,998,188204,0547,999,964--------sum204,054

在这种情况下，大约有60%的blocks依然每个块持有原来的49行，但是表中的其他块几乎没有被删除，而是被完全清空。（如果您将第一个输出中的总块数与第一个报告中的总块数进行比较，您会注意到现在肯定有几个块(24个块)是完全空的）现在有多少块可用来插入?这里有一个快速的计算，我们的大部分块有49行，占了90%（default pctree = 10），因此，一个块将下降到75%的标记(即当ASSM将其标记为有空闲空间时)，当它少于41行时(49 * 75 /90)，在204,000个块中，大约75,000个符合这个标准(检查“运行的块总数”列)

索引空间

上一节展示了一些简单的SQL，让您了解了表中将如何显示空间(或数据将如何保留)-我们可以对索引做类似的事情吗？答案必然是肯定的。但是，回答“在删除匹配谓词X的数据之后，索引会是什么样子”这个问题的代码运行起来要比运行表的代码开销更大。首先，这里有一段简单的代码来检查索引的当前内容:

selectrows_per_leaf,count(*)leaf_blocksfrom(select/*+index_ffs(t1(client_ref))*/sys_op_lbid(94255,'L',t1.rowid)leaf_block,count(*)rows_per_leaffromt1whereclient_refisnotnullgroupbysys_op_lbid(94255,'L',t1.rowid))groupbyrows_per_leaforderbyrows_per_leaf;

对于‘SYS_OP_LBID()的调用是将一个表rowid作为它的输入之一，并返回一些类似于块的第一行的rowid的内容，而该块的地址是索引叶块的地址，索引块持有表rowid所提供的索引条目。另外两个参数是索引object_id(如果索是分区的，则是分区或者是子分区)和一个表示函数的特定用法的标志。在这个例子中是“L”。hint在目标索引上使用快速索引扫描是必要的，任何其他路径都可能返回错误的出结果-‘client_ref’不为空是必要的。以确保查询可以有效的使用index_ffs路径。
对于我的初始化数据集，索引在每个块中都有448个索引条目，除了一个（大概是最后一个，192行）。即使这是简单的查询也要为了每个索引的要求而精心设计-因为索引快速扫描需要得到正确的结果，这就是我们不得不做一些不同寻常的删除操作，看看我们大量删除会怎么影响索引。下面是一个例子，展示我们如何找出试图删除5年多前打开的行对client_ref索引产生什么影响。

selectrows_per_leaf,count(*)blocks,rows_per_leaf*count(*)row_count,sum(count(*))over(orderbyrows_per_leaf)running_blocks,sum(rows_per_leaf*count(*))over(orderbyrows_per_leaf)running_rowsfrom(select/*+leading(v1t1)use_hash(t1)*/leaf_block,count(*)rows_per_leaffrom(select/*+no_mergeindex_ffs(t1(client_ref))*/sys_op_lbid(94255,'L',t1.rowid)leaf_block,t1.rowidridfromt1whereclient_refisnotnull)v1,t1wheret1.rowid=v1.ridanddate_open<add_months(sysdate,-60)groupbyleaf_block)groupbyrows_per_leaforderbyrows_per_leaf;

正如您所看到的，我们从一个内联视（暗示不可合并）图开始将索引块id附加每个表的rowid上，然后将这组行id连接回表-通过rowid连接并强制进行散列连接。我已经暗示了散列连接，因为它(可能)是最有效的策略，但是尽管我引入了一个leading()提示，但我没有包含关于交换(或不)连接输入的提示-我将让优化器决定这两个数据集中哪个更小，由此来更适合的构建哈希表。
在这种特殊的情况下优化器能够使用一个仅索引的访问路径来查找date_open比五年前跟早行的所有rowid。尽管如此(部分原因是我的pga_aggregate_target相对较小，散列连接溢出到(固态)磁盘)，查询耗时3分15秒，而上一个查询在缓存整个索引时恰好运行了1.5秒。以下是输出的摘录:

BlocksRowsRows_per_leafBlocksRowsRunningtotalRunningtotal-------------------------------------------------------------1812362345818623725830187237471,204188118881,392...21034672,6602,312474,88221140184,6112,713559,493...221808178,5688,9891,921,410222851188,9229,8402,110,332223832185,53610,6722,295,868...24221652,27221,3204,756,57524317342,03921,4934,798,61424415638,06421,6494,836,678...265126522,3215,003,718266126622,3225,003,984

我们要修改22322个叶块——这是索引中的每一个叶块;我们从一个叶块中删除的行数从1到266不等。我一次从83行输出中选择了几行，但是您可能仍然可以看到该模式似乎遵循正态分布，以222(50%)为中心。
如果这样删除我们应该很清楚，我们将花费大量的精力来更新这个索引；即使这样，“每个叶块删除多少行”这个简单的数字也不能告诉我们要做的工作的全部内容。我们不知道我们是否会(例如)在同一时间删除所有266个索引条目从最后一块上面显示删除完成，我们将非常随机地在索引周围跳跃式来回，并发现自己不断地重新访问该块，以便一次删除一个索引条目。因此在下一期中，我们将研究需要考虑工作负载的哪些方面，以及不同的删除策略会对工作负载产生怎样的影响。

原作者:Jonathan Lewis

原文地址：https://www.red-gate.com/simple-talk/sql/oracle/massive-deletes-part-2/

| 译者简介

汤健·沃趣科技数据库技术专家沃趣科技数据库工程师，多年Oracle数据库从业经验，深入理解Oracle数据库结构体系，现主要参与公司一体机产品安装、测试、优化，并负责电信行业数据库以及系统运维。