那種寫法執行效率高

下面的兩種情況，那種寫法執行效率高。

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@]