关于in，exists，join的效率问题的疑问

raidenlee

我用db2batch对下面的几个语句进行测试，结果如下：
select A.* from btp.svdtxn A left join btp.cmmtxnid B on A.txnid = B.txnid where B.txnid is null
Number of rows retrieved is:   159510
Number of rows sent to output is:   0

Elapsed Time is:           45.230     seconds
--------------------------------------------------------------------------------------------------------------
select * from btp.svdtxn where txnid not in ('1741','1742','1513','1522','1523','1735','1703','1758','1750','1150','1810')
Number of rows retrieved is:   159510
Number of rows sent to output is:   0

Elapsed Time is:           43.752     seconds

---------------------------------------------
select * from btp.svdtxn where btp.svdtxn.txnid not in(select txnid from btp.cmmtxnid)
Number of rows retrieved is:   159510
Number of rows sent to output is:   0

Elapsed Time is:           45.820     seconds

---------------------------------------------
select * from btp.svdtxn where not exists(select txnid from btp.cmmtxnid where btp.svdtxn.txnid = btp.cmmtxnid.txnid)
Number of rows retrieved is:   159510
Number of rows sent to output is:   0

Elapsed Time is:           44.536     seconds

---------------------------------------------
svdtxn表的数据大概有680万条左右，cmmtxnid表就是'1741','1742','1513','1522','1523','1735','1703','1758','1750','1150','1810'这些内容。为什么用这几种查询效率上基本都一样呢，不管是exists还是join还是in，是db2内部已经对in或者exists进行join的优化了吗，还能优化的再快点吗?

我笨故我在new

优化器会对这部分进行优化,通常join比较快,至于是使用索引还是生成temp table再join等操作与设置的优化级别也有关系