DELETE与SELECT计算回表COST的区别

前几日,一位同事发来微信,发送过来一个问题共同探讨,SELECT时采用了全表扫描,忽略了索引,为何在DELETE时选择了此索引?具体SQL如下图所示:

1028-2

其中,A_SCHEMA表记录数量为:176941,B表记录数量为:5224320,通过SELECT的查询计划来看,A,B表进行HASH_JOIN过滤时,A表作为驱动表,未采用索引,采用了全表扫描的执行计划,我们看一下把SELECT改成DELETE,如下图SQL:

1028-3

从上图中的执行计划中看到,DELETE时,A表走了A_SCHEMA_ID索引,问题1,为什么SELECT时不走索引呢?

1028-4

可以看到,该索引的CLUSTERING_FACTOR非常大,基本接近于表的记录数,而表的BLOCK数仅为755,说明在SELECT用到此索引进行回表时,IO COST影响了此路径最终的成本:

SELECT采用此索引时的成本,表连接方式为Nested_loop:

1028-7

最终选择了成本更低的HASH_JOIN,对应此表的访问方式为全表扫描:

1028-6

但是随之而来的问题2:DELETE为何选择了走此索引?

我们看一下成本计算结果:

1028-8

1028-9

选择走索引的NL成本更低,最终选择了Nest_loop的表连接方式。但是说到这里还是没有解释问题,为什么DELETE时却走了索引?为何DELETE时,成本却比SELECT要少?之前向朋友猜测了一种可能性并且向大神得到了求证,现在我们来实验模拟一下这个当初的猜测:

创建一个测试表,并且在选择率极好的OBJECT_ID列上创建单列索引,查询单列值,用到了T_IDX01索引

1028-10

手工将索引的CLUSTERING_FACTOR设置成一个超大值,再次执行查询,因回表开销巨大,选择了全表扫描:

1028-11

我们看一下DELETE,用上了这个索引T_IDX01但是,没有显示回表步骤与相应Cost。

1028-12

通过以上实验,我们可以得出结论,当DELETE语句并且用到索引时,是不计算回表开销的,因为DELETE势必要回表,但是在执行计划中却不显示,只能说Oracle有意不去计算回表成本,即使回表开销巨大。这也也解释了朋友遇到的问题,在DELETE是选择了Nested_loop并且用上了索引,是Oracle CBO因为没有计算索引的回表代价从而降低了成本之后选择的最佳路径。