Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

¸ßЧSQL²éѯ֮Ë÷Òý£¨VI£©

ÎÒÃÇÏÈ¿´ NestedLoop ºÍ MergeJoin µÄËã·¨£¨ÒÔÏÂΪÒýÓ㬼û RicCC µÄ¡¶ ͨÍùÐÔÄÜÓÅ»¯µÄÌìÌà - µØÓü JOIN ·½·¨ËµÃ÷ ¡· ):
==================================
NestedLoop:
   foreach rowA in tableA where tableA.col2=?
    {
    search rowsB from tableB where tableB.col1=rowA.col1 and tableB.col2=? ;
    if(rowsB.Count<=0)
        discard rowA ;
    else
        output rowA and rowsB ;
    }
MergeJoin:
Á½¸ö±í¶¼°´ÕÕ¹ØÁª×Ö¶ÎÅÅÐòºÃÖ®ºó£¬ merge join ²Ù×÷´Óÿ¸ö±íȡһÌõ¼Ç¼¿ªÊ¼Æ¥Å䣬Èç¹û·ûºÏ¹ØÁªÌõ¼þ£¬Ôò·ÅÈë½á¹û¼¯ÖУ»·ñÔò£¬½«¹ØÁª×Ö¶ÎÖµ½ÏСµÄ¼Ç¼Åׯú£¬´ÓÕâÌõ¼Ç¼¶ÔÓ¦µÄ±íÖÐÈ¡ÏÂÒ»Ìõ¼Ç¼¼ÌÐø½øÐÐÆ¥Å䣬ֱµ½Õû¸öÑ­»·½áÊø¡£
==================================
ÎÒÃÇͨ¹ý×î¼òµ¥µÄÇé¿öÀ´¼ÆËã NestedLoop ºÍ MergeJoin µÄÏûºÄ£º
Á½Õűí A ¡¢ B £¬·Ö±ðÓÐ m ¡¢ n ÐÐÊý¾Ý£¨ m < n £©£¬Õ¼Óûù´¡±íÎïÀí´æ´¢¿Õ¼ä·Ö±ðΪ a ¡¢ b Ò³£¬¾Û¼¯Ë÷ÒýÊ÷·ÇÒ¶½Úµã¶¼ÊÇÁ½²ã£¨Ò»²ã¸ù½Úµã£¬Ò»²ãÖм伶½Úµã£©£¬ A ¡¢ B µÄ¾Û¼¯Ë÷Òý½¨ÔÚ A.col1 ¡¢ B.col1 ÉÏ¡£Ò»Ìõ²éѯÓï¾ä£º
select A.col1, B.col2 from A inner join B where A.col1 = B.col1 ¡£
Ö´ÐÐ NestedLoop ²Ù×÷ £º
A ×÷Ϊ outer input £¬ B ×÷Ϊ inner input ʱ£º A ´øÀ´µÄ IO Ϊ a £»Ã¿´Îͨ¹ý clustered index seek Ö´ÐÐÄÚ²¿Ñ­»·£¬»¨·Ñ 3( Ò»¸ö¸ù½Úµã¡¢Ò»¸öÖм伯½áµã¡¢Ò»¸öÒ¶½Úµã¡£µ±È»Ò²¿ÉÄÜÖ±½Ó´Ó¸ù½Úµã¾ÍÄõ½ÒªµÄÊý¾Ý£¬ÎÒÃÇÖ»¿¼ÂÇ×µÄÇé¿ö£©£¬ÕâÑùÖ´ÐÐÕû¸öǶÌ×Ñ­»·¹ý³ÌÏûºÄ IO Ϊ a + 3*m ¡£Èç¹û B ×÷Ϊ inner input £¬ A ×÷Ϊ outer input ·ÖÎöÀàËÆ¡£
Ö´ÐÐ MergeJoin :
MergeJoin Òª°Ñ A ¡¢ B Á½Õűí×ö¸ö Scan £¬È»ºó½øÐÐ Merge ²Ù×÷¡£ËùÒÔ A ¡¢ B ·Ö±ð´øÀ´ IO Ϊ a + b ¾ÍÊÇ×ܵÄÂß¼­ IO ¿ªÏú¡£
´ÓÉÏÊö·ÖÎöÀ´¿´£¬Èô a + 3*m << a + b £¬¼´ 3*m << b £¬ÄÇô NestedLoop ÐÔÄÜÊǼ«¼ÑµÄ¡£µ±È»£¬ÎÒÃÇ±È½Ï A ±íµÄÐÐºÍ B ±íËùÕ¼Êý¾ÝÒ³´óС¿´ÉÏÈ¥Óеã¿äÕÅ£¬µ«ÊÇÁ¿»¯·ÖÎöȷʵÈç´Ë¡£ÔÚÕâÀÎÒÃÇûÓмÆËã NestedLoop ºÍ MergeJoin ±¾ÉíµÄ cpu ¼ÆË㿪Ïú£¬ÌرðÊǺóÕߣ¬Õⲿ·Ö²¢²»ÄÜÍêÈ«ºöÂÔ£¬µ«ÊÇÒ²À´µÃÓÐÏÞ¡£
OK £¬ÏÖÔÚÎÒÃÇÊÔͼִÐÐʵ¼ÊµÄÓï¾äÑéÖ¤ÎÒÃǵĹ۵㣬¿´¿´ÄÜ·¢ÏÖʲô¡£


Ïà¹ØÎĵµ£º

PL/SQLÓïÑÔ¼ò½é

Ò»¡¢    PL/SQLÓïÑÔ¼ò½é
(±¾½²ÒåÖ®ËùÓгÌÐò¾ùµ÷ʽͨ¹ý)
Ê×ÏÈÎÒÃÇ¿´Ò»¸ö¼òµ¥Ö®Àý×Ó,ÏÂÃæÕâ¸öÀý×ÓÊÇͳ¼Æ´Ó1ÖÁ100µÄ×ܺÍ.
declare
i number:=0;          /*ÉùÃ÷±äÁ¿¾®¸ø³õÖµ*/
t number:=1;
error_message exception;  /*ÉùÃ÷Ò»¸ö³ö´í´¦Àí*/
begin ......

¹ØÓÚsql¸ü¸Ä¼ÆËã»úÃûºÍ·þÎñÆ÷ÃûÒ»Ö»ò"´íÎó 18483

½ñÌìÔÚÅäÖÃÊý¾Ý¿â·¢²¼ºÍ·Ö·¢Ê±×ÜÊDZ¨³öÏÖ 18483 ´íÎó
Ìáʾ˵£º´íÎó 18483:δÄÜÁ¬½Óµ½·þÎñÆ÷ "XXX"£¬ÒòΪ 'distributor_admin'δÔڸ÷þÎñÆ÷É϶¨ÒåΪԶ³ÌµÇ½¡£
Îҵķ¢²¼ºÍ·Ö·¢ÊÇͬһ¸ö·þÎñÆ÷£¬"XXX" ΪÎҵĻúÆ÷Ãû£¬·Ö·¢Êý¾Ý¿âÊÇĬÈϵÄÃû³Æ£¬¶øÎÒÔÚÁíÍâһ̨»úÆ÷ÉÏ×öʱ¾ÍÕý³£¡£
1¡¢ÉèÖù²Ïí¸´ÖÆÄ¿Â¼:
      ......

SQL 2005 ´æ´¢¹ý³Ì µ÷ÊÔ

SQL 2005 µÄ´æ´¢¹ý³ÌºÍ´¥·¢Æ÷µ÷ÊԴ󷨣¨Ô­´´£©
www.chengchen.net ³Ì³¿
       ×òÌìÍíÉÏÎÒÕÒ±éÁË»¥ÁªÍøÒ²Ã»Óз¢ÏÖ¹ØÓÚSQL2005´æ´¢¹ý³ÌºÍ´¥·¢Æ÷µÄµ÷ÊÔ·½·¨£¬Ñо¿µ½Á賿2µã¶àÖÓ£¬ÖÕÓÚÕÒµ½·½·¨ÁË£¬²»¸É¶ÀÏí£¬ÄóöÀ´·ÖÏí¡£Èç¹ûÒª×ªÔØ£¬Çë±£Áô°æÈ¨£¬Ð»Ð»£¡
     &nbs ......

SQL SERVER ÖÐÈçºÎʹÓÃÐÐËø

¸øÄã¸ö×îÏêϸµÄ°É ¿ÉÄÜÓÐÄãÒªµÄÄÚÈÝ
ËøµÄ¸ÅÊö
Ò». ΪʲôҪÒýÈëËø
¶à¸öÓû§Í¬Ê±¶ÔÊý¾Ý¿âµÄ²¢·¢²Ù×÷ʱ»á´øÀ´ÒÔÏÂÊý¾Ý²»Ò»ÖµÄÎÊÌâ:
¶ªÊ§¸üÐÂ
A,BÁ½¸öÓû§¶ÁͬһÊý¾Ý²¢½øÐÐÐÞ¸Ä,ÆäÖÐÒ»¸öÓû§µÄÐ޸Ľá¹ûÆÆ»µÁËÁíÒ»¸öÐ޸ĵĽá¹û,±ÈÈ綩Ʊϵͳ
Ôà¶Á
AÓû§ÐÞ¸ÄÁËÊý¾Ý,ËæºóBÓû§ÓÖ¶Á³ö¸ÃÊý¾Ý,µ«AÓû§ÒòΪijЩԭÒòÈ¡Ï ......

DB2 SQLÐÔÄÜÓÅ»¯Ïà¹ØµÄ10´óÒªËØ

ÏÂÃæÌá³öÁË10ÌõºÍDB2 SQLÐÔÄÜÏà¹ØµÄ10ÌõÒòËØ£º
1¡¢ÌṩÊʵ±µÄͳ¼ÆÐÅÏ¢
¶ÔDB2ÓÅ»¯¹ÜÀíÆ÷£¨otimizer£©¶øÑÔ£¬ÈçºÎ¸ü¼ÓÓÐЧµÄÖ´ÐÐSQLÓï¾äÊÇÓÉ´æÔÚÓÚDB2 catalogÖеÄͳ¼ÆÐÅÏ¢¾ö¶¨µÄ£¬ÓÅ»¯Æ÷ÀûÓÃÕâЩÐÅÏ¢¾ö¶¨×îÓÅ»¯µÄ·¾¶¡£
Òò´Ë£¬ÎªÁ˱£³ÖϵͳÄܹ»×ö³ö×î¼ÑÑ¡Ôñ£¬ÐèÒª¾­³£µÄÔËÐÐRunstatsÃüÁÀ´±£³Öϵͳͳ¼ÆÐÅÏ¢µÄ¼°Ê±ÓÐЧ¡£Ô ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ