仓酷云

 找回密码
 立即注册
搜索
热搜: 活动 交友 discuz
查看: 1088|回复: 8
打印 上一主题 下一主题

[学习教程] MSSQL教程之CBO与RBO下的IN/EXISTS

[复制链接]
柔情似水 该用户已被删除
跳转到指定楼层
楼主
发表于 2015-1-16 22:40:30 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
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下优化器会为你选择,怎样写就只是习气成绩了。因此我们的保存数据方法就是:在删除的动作开始之前,把表数据备份起来,然后留一个空表,在空表上执行“删除”操作。
蒙在股里 该用户已被删除
沙发
发表于 2015-1-19 21:09:14 | 只看该作者
很多书籍啊,不过个人认为看书太慢,还不如自己学。多做实际的东西,就会遇到很多问题,网上搜下解决问题。不断重复这个过程,在配合sql的F1功能。
admin 该用户已被删除
板凳
发表于 2015-1-25 20:17:45 | 只看该作者
我是新手,正在学习数据库和操作系统,深感理论的泛广,唯有一步一步来,但是又感觉时间不够,收集了很多资料却总是没能认真的看完,希望有一个讨论板块,大家共同解决,共同分享,共同努力
再见西城 该用户已被删除
地板
发表于 2015-2-3 20:50:10 | 只看该作者
光写几个SQL实在叫无知。
深爱那片海 该用户已被删除
5#
发表于 2015-2-9 05:08:38 | 只看该作者
语句级快照和事务级快照终于为SQLServer的并发性能带来了突破。个人感觉语句级快照大家应该应用。事务级快照,如果是高并发系统还要慎用。如果一个用户总是被提示修改不成功要求重试时,会杀人的!
透明 该用户已被删除
6#
发表于 2015-2-27 00:19:00 | 只看该作者
但是随着数据量的增大,这种成本差距会逐渐减小,趋于相等。(500万数量级只相差10%左右)
飘飘悠悠 该用户已被删除
7#
发表于 2015-3-8 18:49:39 | 只看该作者
而SQLServer如果能像Oracle一样可以为登陆分配如:5%的cpu,10%的内存。就可以解决这个漏洞。
只想知道 该用户已被删除
8#
发表于 2015-3-16 11:40:05 | 只看该作者
另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程);
莫相离 该用户已被删除
9#
发表于 2015-3-22 22:48:00 | 只看该作者
从底层原理到表层引用,书籍多的很。个人认为没有什么那本书好?这样的说法。主要看和个人的学习方法是否适合。
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|Archiver|手机版|仓酷云 鄂ICP备14007578号-2

GMT+8, 2024-12-23 16:15

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表