MSSQL教程之CBO与RBO下的IN/EXISTS

Federated将不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。非常适合分布式应用早晨抽暇看了看asktom的RSS，发明两篇应当说很进门的关于IN/EXISTS的文章：http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:953229842074，http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:442029737684文章很长，也没有细心看完，不外有些器材仍是很成心思的，下手实行了一下。人人任意看看咯，Tom现在回覆成绩的情况我已没举措测试了，在10.1.0.4下做了些测试，CBO和RBO（利用hints）下仍是很年夜区分的。SQL>select*fromscott.emp;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO-----------------------------------------------------------------------------------7369SMITHCLERK790217-12月-80800207499ALLENSALESMAN769820-2月-811600300307521WARDSALESMAN769822-2月-811250500307566JONESMANAGER783902-4月-812975207654MARTINSALESMAN769828-9月-8112501400307698BLAKEMANAGER783901-5月-812850307782CLARKMANAGER783909-6月-812450107788SCOTTANALYST756619-4月-873000207839KINGPRESIDENT17-11月-815000107844TURNERSALESMAN769808-9月-8115000307876ADAMSCLERK778823-5月-871100207900JAMESCLERK769803-12月-81950307902FORDANALYST756603-12月-813000207934MILLERCLERK778223-1月-82130010已选择14行。实行企图----------------------------------------------------------0SELECTSTATEMENTOptimizer=ALL_ROWS(Cost=3Card=14Bytes=1218)10TABLEACCESS(FULL)OFEMP(TABLE)(Cost=3Card=14Bytes=1218)SQL>createtabletmp_empasselect*fromscott.empwhereenamelikeS%;表已创立。SQL>select*fromtmp_emp;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO-----------------------------------------------------------------------------------7369SMITHCLERK790217-12月-80800207788SCOTTANALYST756619-4月-87300020实行企图----------------------------------------------------------0SELECTSTATEMENTOptimizer=ALL_ROWS(Cost=3Card=2Bytes=174)10TABLEACCESS(FULL)OFTMP_EMP(TABLE)(Cost=3Card=2Bytes=174)先测试一下IN：SQL>select*fromtmp_empwhereenamein(selectenamefromscott.emp);EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO-----------------------------------------------------------------------------------7369SMITHCLERK790217-12月-80800207788SCOTTANALYST756619-4月-87300020实行企图----------------------------------------------------------0SELECTSTATEMENTOptimizer=ALL_ROWS(Cost=7Card=2Bytes=188)10HASHJOIN(SEMI)(Cost=7Card=2Bytes=188)21TABLEACCESS(FULL)OFTMP_EMP(TABLE)(Cost=3Card=2Bytes=174)31TABLEACCESS(FULL)OFEMP(TABLE)(Cost=3Card=14Bytes=98)表没有做太过析，利用了HASHJOIN(SEMI)。Tom在回覆成绩的时分提到：Select*fromT1wherexin(selectyfromT2)istypicallyprocessedas:select*fromt1,(selectdistinctyfromt2)t2wheret1.x=t2.y;Thesubqueryisevaluated,distincted,indexed(orhashedorsorted)andthenjoinedtotheoriginaltable--typically.明显第二个查询是很奇异的。也恰是这个促使我翻开了数据库测试，岂非一个IN还必要先DISTINCT一下？没见过IN发生排序操纵啊。SQL>ed已写进fileafiedt.buf1select*fromtmp_emp,(selectdistinctenamefromscott.emp)t2*wheretmp_emp.ename=t.enameSQL>/EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNOENAME---------------------------------------------------------------------------------------------7369SMITHCLERK790217-12月-8080020SMITH7788SCOTTANALYST756619-4月-87300020SCOTT实行企图----------------------------------------------------------0SELECTSTATEMENTOptimizer=ALL_ROWS(Cost=8Card=2Bytes=188)10VIEW(Cost=8Card=2Bytes=188)21SORT(UNIQUE)(Cost=8Card=2Bytes=202)32HASHJOIN(Cost=7Card=2Bytes=202)43TABLEACCESS(FULL)OFTMP_EMP(TABLE)(Cost=3Card=2Bytes=188)53TABLEACCESS(FULL)OFEMP(TABLE)(Cost=3Card=14Bytes=98)明显纷歧样了。那末改用RULE形式：SQL>ed已写进fileafiedt.buf1select/*+rule*/*fromtmp_emp,(selectdistinctenamefromscott.emp)t2*wheretmp_emp.ename=t.enameSQL>/EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNOENAME---------------------------------------------------------------------------------------------7788SCOTTANALYST756619-4月-87300020SCOTT7369SMITHCLERK790217-12月-8080020SMITH实行企图----------------------------------------------------------0SELECTSTATEMENTOptimizer=HINT:RULE10MERGEJOIN21VIEW32SORT(UNIQUE)43TABLEACCESS(FULL)OFEMP(TABLE)51SORT(JOIN)65TABLEACCESS(FULL)OFTMP_EMP(TABLE)再尝尝加上索引：SQL>altertabletmp_empaddconstraintspk_tmpempprimarykey(ename);表已变动。剖析一下：SQL>analyzetabletmp_empcomputestatisticsfortableforallindexedcolumns;表已剖析。SQL>analyzetablescott.empcomputestatisticsfortableforallcolumns;表已剖析。SQL>select*2fromtmp_empe,(selectdistinctenamefromscott.emp)t3wheree.ename=t.ename;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNOENAME---------------------------------------------------------------------------------------------7369SMITHCLERK790217-12月-8080020SMITH7788SCOTTANALYST756619-4月-87300020SCOTT实行企图----------------------------------------------------------0SELECTSTATEMENTOptimizer=ALL_ROWS(Cost=6Card=2Bytes=188)10VIEW(Cost=6Card=2Bytes=188)21SORT(UNIQUE)(Cost=6Card=2Bytes=88)32NESTEDLOOPS(Cost=5Card=2Bytes=88)43TABLEACCESS(FULL)OFEMP(TABLE)(Cost=3Card=14Bytes=70)53TABLEACCESS(BYINDEXROWID)OFTMP_EMP(TABLE)(Cost=1Card=1Bytes=39)65INDEX(UNIQUESCAN)OFPK_TMPEMP(INDEX(UNIQUE))(Cost=0Card=1)SQL>ed已写进fileafiedt.buf1select/*+rule*/*2fromtmp_empe,(selectdistinctenamefromscott.emp)t3*wheree.ename=t.enameSQL>/EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNOENAME---------------------------------------------------------------------------------------------7788SCOTTANALYST756619-4月-87300020SCOTT7369SMITHCLERK790217-12月-8080020SMITH实行企图----------------------------------------------------------0SELECTSTATEMENTOptimizer=HINT:RULE10NESTEDLOOPS21VIEW32SORT(UNIQUE)43TABLEACCESS(FULL)OFEMP(TABLE)51TABLEACCESS(BYINDEXROWID)OFTMP_EMP(TABLE)65INDEX(UNIQUESCAN)OFPK_TMPEMP(INDEX(UNIQUE))SQL>select*fromtmp_empwhereenamein(selectenamefromscott.emp);EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO-----------------------------------------------------------------------------------7788SCOTTANALYST756619-4月-873000207369SMITHCLERK790217-12月-8080020实行企图----------------------------------------------------------0SELECTSTATEMENTOptimizer=ALL_ROWS(Cost=5Card=1Bytes=44)10NESTEDLOOPS(Cost=5Card=1Bytes=44)21SORT(UNIQUE)(Cost=3Card=14Bytes=70)32TABLEACCESS(FULL)OFEMP(TABLE)(Cost=3Card=14Bytes=70)41TABLEACCESS(BYINDEXROWID)OFTMP_EMP(TABLE)(Cost=1Card=1Bytes=39)54INDEX(UNIQUESCAN)OFPK_TMPEMP(INDEX(UNIQUE))(Cost=0Card=1)SQL>select/*+rule*/*fromtmp_empwhereenamein(selectenamefromscott.emp);EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO-----------------------------------------------------------------------------------7788SCOTTANALYST756619-4月-873000207369SMITHCLERK790217-12月-8080020实行企图----------------------------------------------------------0SELECTSTATEMENTOptimizer=HINT:RULE10NESTEDLOOPS21VIEWOFVW_NSO_1(VIEW)32SORT(UNIQUE)43TABLEACCESS(FULL)OFEMP(TABLE)51TABLEACCESS(BYINDEXROWID)OFTMP_EMP(TABLE)65INDEX(UNIQUESCAN)OFPK_TMPEMP(INDEX(UNIQUE))此次的确靠近了良多，整体上看和Tom说的情形差未几。不外RBO这类形式对后边的表对应列选择性低时应当很好，而其他情形生怕不见得是优化的。上面看看EXISTS：SQL>select*fromtmp_emptwhereexists(selectnullfromscott.empewheret.ename=e.ename);EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO-----------------------------------------------------------------------------------7788SCOTTANALYST756619-4月-873000207369SMITHCLERK790217-12月-8080020实行企图----------------------------------------------------------0SELECTSTATEMENTOptimizer=ALL_ROWS(Cost=5Card=1Bytes=44)10NESTEDLOOPS(Cost=5Card=1Bytes=44)21SORT(UNIQUE)(Cost=3Card=14Bytes=70)32TABLEACCESS(FULL)OFEMP(TABLE)(Cost=3Card=14Bytes=70)41TABLEACCESS(BYINDEXROWID)OFTMP_EMP(TABLE)(Cost=1Card=1Bytes=39)54INDEX(UNIQUESCAN)OFPK_TMPEMP(INDEX(UNIQUE))(Cost=0Card=1)看着和用IN一样哦。SQL>select/*+rule*/*fromtmp_emptwhereexists(selectnullfromscott.empewheret.ename=e.ename);EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO-----------------------------------------------------------------------------------7369SMITHCLERK790217-12月-80800207788SCOTTANALYST756619-4月-87300020实行企图----------------------------------------------------------0SELECTSTATEMENTOptimizer=HINT:RULE10FILTER21TABLEACCESS(FULL)OFTMP_EMP(TABLE)31TABLEACCESS(FULL)OFEMP(TABLE)用RBO就分歧咯！SQL>selectt.*fromtmp_empt,scott.empewheret.ename=e.ename;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO-----------------------------------------------------------------------------------7369SMITHCLERK790217-12月-80800207788SCOTTANALYST756619-4月-87300020实行企图----------------------------------------------------------0SELECTSTATEMENTOptimizer=ALL_ROWS(Cost=5Card=2Bytes=88)10NESTEDLOOPS(Cost=5Card=2Bytes=88)21TABLEACCESS(FULL)OFEMP(TABLE)(Cost=3Card=14Bytes=70)31TABLEACCESS(BYINDEXROWID)OFTMP_EMP(TABLE)(Cost=1Card=1Bytes=39)43INDEX(UNIQUESCAN)OFPK_TMPEMP(INDEX(UNIQUE))(Cost=0Card=1)如许看来在CBO下，利用内联系关系、IN、EXISTS极可能失掉统一个实行企图（更多的情形就意外试了），优化器会发明三者的语义是不异的；而在较早的RBO下EXISTS接纳FILTER而IN相称于对子查询先DISTINCT后联系关系，内联系关系则是间接联系关系就好了。Tom在他最新的复兴中如许说：UsetheRBOandseewhatyousee.waybackwhenIwrotethis,thatwasthe"morepopular"ofthetwoperhapstodayin2005,whatIsaidyearsagousingtheRBOdoesnotapplytotheCBO.thecboissmartenoughtorecognizethesetwothingsareeffectivelythesame.IN相称于对子查询先DISTINCT后联系关系这一条真的没有想通，RBO为何做如许的事呢？其实是没有普适性，我团体的了解就是计划的时分以为IN前面是跟一个值列表的情形占多数，固然先把值算出来，然后NL就能够了，假如前面是一个内外的值那也就一样处置；并且利用IN的时分大概年夜多是子查询对外层查询的选择性高，即外层的表较年夜，而子查询的前往值较少。看看分歧情形的分歧实行企图，Oracle在CBO上的确仍是花了点心机的，赫赫。结论：在RBO下，利用IN仍是EXISTS必要视情形而定，只需记着利用IN存在排序和DISTINCT这一步骤应当就不难判别；CBO下优化器会为你选择，怎样写就只是习气成绩了。因此我们的保存数据方法就是：在删除的动作开始之前，把表数据备份起来，然后留一个空表，在空表上执行“删除”操作。

很多书籍啊，不过个人认为看书太慢，还不如自己学。多做实际的东西，就会遇到很多问题，网上搜下解决问题。不断重复这个过程，在配合sql的F1功能。

我是新手，正在学习数据库和操作系统，深感理论的泛广，唯有一步一步来，但是又感觉时间不够，收集了很多资料却总是没能认真的看完，希望有一个讨论板块，大家共同解决，共同分享，共同努力

光写几个SQL实在叫无知。

语句级快照和事务级快照终于为SQLServer的并发性能带来了突破。个人感觉语句级快照大家应该应用。事务级快照，如果是高并发系统还要慎用。如果一个用户总是被提示修改不成功要求重试时，会杀人的！

但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

而SQLServer如果能像Oracle一样可以为登陆分配如：5%的cpu，10%的内存。就可以解决这个漏洞。

另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程);

从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。