下面的两种情况，那种写法执行效率高。

1、delete from subject_data where subject_id in (130,129,127);

2、delete from subject_data where subject_id = 130;
delete from subject_data where subject_id = 129;
delete from subject_data where subject_id = 127;

在subject_id列有索引的情况下显然是第一种情况执行效率高。其执行计划如下：

Execution Plan
----------------------------------------------------------
0 DELETE STATEMENT Optimizer=CHOOSE
1 0 DELETE OF 'SUBJECT_DATA'
2 1 CONCATENATION
3 2 INDEX (RANGE SCAN) OF 'SD_SUBJECT_ID' (NON-UNIQUE)
4 2 INDEX (RANGE SCAN) OF 'SD_SUBJECT_ID' (NON-UNIQUE)
5 2 INDEX (RANGE SCAN) OF 'SD_SUBJECT_ID' (NON-UNIQUE)

这两种情况虽然执行相同次数的INDEX RANGE SCAN，但1只需解析一次2要解析3次。

另外，在subject_id列没有索引的情况下也仍然是第一种情况执行效率高，理由同第一种情况。

[@more@]

ITPub博客

那种写法执行效率高

最新文章