Äú¿ÉÒÔ¾èÖú£¬Ö§³ÖÎÒÃǵĹ«ÒæÊÂÒµ¡£

1Ôª 10Ôª 50Ôª





ÈÏÖ¤Â룺  ÑéÖ¤Âë,¿´²»Çå³þ?Çëµã»÷Ë¢ÐÂÑéÖ¤Âë ±ØÌî



  ÇóÖª ÎÄÕ ÎÄ¿â Lib ÊÓÆµ iPerson ¿Î³Ì ÈÏÖ¤ ×Éѯ ¹¤¾ß ½²×ù Model Center   Code  
»áÔ±   
   
 
     
   
 ¶©ÔÄ
  ¾èÖú
±àд²¢µ÷ÓŲéѯÓï¾äÒÔÓÅ»¯ÐÔÄÜ×î¼Ñʵ¼ù
 
  3677  次浏览      46
 2018-8-21
 
±à¼­ÍƼö:
±¾ÎÄÀ´×ÔÓÚibm£¬ÃèÊöÁËÔÚ DB2 Êý¾Ý¿âÐÔÄÜ·½Ãæ×îС»¯ SQL Óï¾äµÄÓ°ÏìµÄ×î¼Ñʵ¼ù¡£

½éÉÜ

²éѯÐÔÄܲ»ÄÜÖ»¿¼ÂÇijһ´ÎµÄÎÊÌ⣬¶øÓ¦¸Ã¹á´©ÓÚÓ¦ÓóÌÐò¿ª·¢µÄÕû¸öÉúÃüÖÜÆÚ£¬ÔÚÉè¼Æ¡¢¿ª·¢¡¢Éú²ú¸÷¸ö½×¶ÎÖж¼Òª¿¼ÂÇËü¡£

SQL ÊÇÒ»¸ö·Ç³£Áé»îµÄÓïÑÔ£¬Ò²¾ÍÊÇ˵Óкܶà;¾¶Í¬Ñù¿ÉÒÔ»ñµÃÕýÈ·µÄ½á¹û¡£ÕâÖÖÁé»îÐÔÒ²Òâζ×ÅÀûÓà DB2 ÓÅ»¯Æ÷¾ßÓÐÓÅÊÆ£¬Ò»Ð©²éѯ»áÓÅÓÚÆäËûµÄ²éѯ¡£

ÔÚ²éѯÔËÐеĹý³ÌÖУ¬DB2 ÓÅ»¯Æ÷»áΪÿ¸ö SQL Óï¾äÑ¡ÔñÒ»¸ö²éѯ¼Æ»®¡£ÓÅ»¯Æ÷Ä£ÄⲻͬµÄ·ÃÎʼƻ®µÄÔËÐгɱ¾£¬²¢Ñ¡ÔñÆäÖÐÒ»¸ö³É±¾×îµÍµÄ·ÃÎʼƻ®¡£Èç¹ûÒ»¸ö²éѯ°üÀ¨ºÜ¶à¸´ÔÓµÄËÑË÷Ìõ¼þ£¬DB2 ÓÅ»¯Æ÷ÔÚijЩÇé¿öÏ¿ÉÒÔÖØÐ´Î½´Ê£¬²»¹ýÔÚijЩһЩÇé¿öÏÂÈ´²»ÄÜ¡£

¶ÔÓÚһЩ±È½Ï¸´ÔӵIJéѯ£¬Ò»¸ö SQL Óï¾äµÄ×¼±¸»ò±àÒëµÄʱ¼ä¿ÉÄÜ»á±È½Ï³¤£¬ÓÈÆäÊÇ BI Ó¦ÓóÌÐòÖÐʹÓÃµÄ SQL Óï¾ä¡£Äã¿ÉÒÔͨ¹ýµ÷ÕûÉè¼ÆºÍÄãµÄÊý¾Ý¿âÅäÖÃÀ´°ïÖúËõ¶ÌÓï¾ä±à¼­Ê±¼ä¡£Õâ°üÀ¨Ñ¡ÔñÕýÈ·µÄÓÅ»¯¼¶±ð²¢ÕýÈ·ÉèÖÃÆäËû×¢²á±äÁ¿¡£

ÓÅ»¯Æ÷Ò²ÐèÒª¾«È·ÊäÈëÒÔ»ñµÃ¾«È·µÄ²éѯ¼Æ»®¡£ÕâÒâζ×ÅÄãÐèÒªÊÕ¼¯¾«È·µÄͳ¼ÆÐÅÏ¢£¬²¢Ç±ÔÚµÄʹÓø߼¶Í³¼Æ¹¦ÄÜ£¬±ÈÈçͳ¼ÆÊÓͼºÍÁÐ×éͳ¼ÆÐÅÏ¢¡£

ÄãÒ²¿ÉÒÔʹÓà DB2 ¹¤¾ß£¨ÓÈÆäÊÇ DB2 explain ¹¤¾ß£©À´µ÷ÓŲéѯ¡£ DB2 ±àÒëÆ÷¿ÉÒÔץȡ¶¯Ì¬»ò¾²Ì¬Óï¾ä¹ØÓÚ·ÃÎʼƻ®ºÍ»·¾³µÄÐÅÏ¢¡£ÀûÓÃץȡµÄÐÅÏ¢À´Àí½âµ¥¸öÓï¾äµÄÔËÐУ¬ËùÒÔÄã¿ÉÒÔµ÷ÕûËüÃÇÒÔ¼°ÄãµÄÊý¾Ý¿â¹ÜÀíÆ÷ÅäÖÃÀ´Ìá¸ßÐÔÄÜ¡£

±àд SQL Óï¾ä

SQL ÊǺÜÇ¿´óµÄÓïÑÔ£¬ËüÔÊÐíÄãÖ¸¶¨Óï·¨²»Í¬¶øÓïÒåÏàͬµÄ¹ØÏµÐÍÃèÊö¡£²»¹ý£¬Ò»Ð©ÓïÒåÏàͬµÄ±ä»¯±ÈÆðÆäËûµÄ¸üÈÝÒ×ÓÅ»¯¡£ËäÈ» DB2 ÓÅ»¯Æ÷ÓкÜÇ¿µÄ²éÑ¯ÖØÐ´ÄÜÁ¦£¬µ«Ò²²¢²»×ÜÊÇ¿ÉÒÔ°ÑÒ»¸ö SQL Óï¾äÖØÐ´³É×îÓŵÄÐÎʽ¡£Ä³Ð© SQL ½á¹¹Ò²¿ÉÄÜ»áÏÞÖÆÓÅ»¯Æ÷¶Ô·ÃÎʼƻ®¿¼ÂÇ¡£ÏÂÃæµÄÕ½ÚÃèÊöÁËÐèÒª±ÜÃâµÄijЩ SQL ½á¹¹£¬²¢¶ÔÈçºÎÌæ»»»ò±ÜÃâËüÃÇÌá³öÁ˽¨Òé¡£

ÔÚËÑË÷Ìõ¼þÖбÜÃ⸴ÔÓµÄÃèÊö

ÔÚËÑË÷Ìõ¼þÖбÜÃ⸴ÔÓµÄÃèÊö£¬ÕâÑùµÄÃèÊö×èÖ¹ÁËÓÅ»¯Æ÷ʹÓñàĿͳ¼ÆÐÅÏ¢À´ÆÀ¹ÀÒ»¸ö¾«È·µÄÑ¡Ôñ¡£Õâ¸öÃèÊöÒ²¿ÉÄÜ»áÏÞÖÆÑ¡Ôñ¿ÉÒÔÓ¦ÓÃÕâЩν´ÊµÄ·ÃÎʼƻ®¡£ÔÚÓÅ»¯µÄ²éѯÓï¾äµÄÖØÐ´½×¶Î£¬ÓÅ»¯Æ÷¿ÉÒÔÖØÐ´Ò»ÅúÃèÊöÒÔÔÊÐíÓÅ»¯Æ÷ÆÀ¹ÀÒ»¸ö¾«È·µÄÑ¡Ôñ£»²»¹ýËü²»ÄÜ´¦ÀíËùÓпÉÄÜÐÔ¡£

ÔÚÃèÊöÖбÜÃâʹÓÃÁ¬½Óν´Ê

ÔÚÃèÊöÖÐʹÓÃÁ¬½Óν´Ê°ÑÁ¬½Ó·½·¨ÏÞÖÆÎªÁËǶÌ×Ñ­»·¡£ÁíÍ⣬¶Ô»ùÊýµÄÆÀ¹À¿ÉÄܲ»×¼È·¡£ÏÂÃæÊÇһЩÁ¬½ÓÃèÊöµÄÀý×Ó£º

WHERE SALES.PRICE * SALES.DISCOUNT = TRANS.FINAL_PRICE
WHERE UPPER(CUST.LASTNAME) = TRANS.NAME

±ÜÃâ¶Ô±¾µØÎ½´ÊÓйý¶àµÄÁÐÃèÊö

ʹÓÃÏà·´µÄÃèÊöÀ´Ìæ´úÓ¦ÓÃÖеÄÒ»¸öÔÚ±¾µØÎ½´ÊÉÏÓÐÌ«¶àÁеÄÃèÊö¡£¿¼ÂÇÏÂÃæµÄÀý×Ó£º

XPRESSN(C) = 'constant'
INTEGER(TRANS_DATE)/100 = 200802

Äã¿É°ÑËüÃÇÓï¾äÖØÐ´Îª£º

C = INVERSEXPRESSN('constant')
TRANS_DATE BETWEEN 20080201 AND 20080229

Ó¦ÓöàÁеÄÃèÊö½«·Á°­Ë÷Òý¿ªÊ¼ºÍ½áÊø¼üµÄʹÓ㬴Ӷøµ¼Ö²»×¼È·µÄ¿ÉÑ¡ÆÀ¹À£¬²¢ÐèÒªÔÚ²éѯÔËÐÐʱ»¨·Ñ¶îÍâµÄʱ¼äÀ´´¦Àí¡£ÕâЩÃèÊöͬÑù»á×èÖ¹¶Ô²éѯµÄÖØÐ´ÓÅ»¯£¬±ÈÈçÔÚÁÐÏàµÈʱµÄʶ±ð¡¢Ê¹Óó£Á¿À´Ìæ»»¸÷Áв¢ÇÒ¶ÔÖ»ÓÐÒ»Ðзµ»ØµÄÇé¿ö½øÐÐʶ±ð¡£ÔÚÕâÖ®ºóµÄ½øÒ»²½µÄÓÅ»¯Ò²¿ÉÄܻᱻ×èÖ¹£¬Òò´Ë»áʧȥ¸ü¶àµÄÓÅ»¯»ú»á¡£¿¼ÂÇÏÂÃæµÄ²éѯ£º

SELECT LASTNAME, CUST_ID, CUST_CODE FROM CUST
WHERE (CUST_ID * 100) + INT(CUST_CODE) = 123456 ORDER BY 1,2,3

ÄãÒ²¿ÉÒÔÓÃÏÂÃæµÄÓï¾ä£º

SELECT LASTNAME, CUST_ID, CUST_CODE FROM CUST
WHERE CUST_ID = 1234 AND CUST_CODE = '56' ORDER BY 1,2,3

Èç¹ûÔÚ CUST_ID ÉÏÓÐÒ»¸öΨһË÷Òý£¬ÖØÐ´µÄ²éѯ°æ±¾ÈòéѯÓÅ»¯Æ÷×¢Òâµ½×î¶àÒ»ÐмǼ½«±»·µ»Ø¡£Õâ±ÜÃâÁËÒýÈëÒ»¸öûÓбØÒªµÄÅÅÐò²Ù×÷¡£ÕâÒ²Èà CUST_ID ºÍ CUST_CODE Áб» 1234 ºÍ¡® 56 ¡¯Ìæ´ú£¬´Ó¶ø±ÜÃâÁË´ÓÊý¾ÝÒ³»òË÷ÒýÒ³¸´ÖÆÊý¾Ý¡£×îÖÕ£¬Ê¹µÃÔÚ CUST_ID ÉϵÄν´ÊÓ¦ÓóÉÒ»¸öË÷Òý¿ªÊ¼»ò½áÊø¼ü¡£

µ±Ò»¸öÃèÊöÔÚν´ÊÖÐʱ£¬Ëü¿ÉÄܲ»ÊÇ×ÜÕâôÃ÷ÏÔ¡£ÕâÖÖÇé¿ö¾­³£·¢ÉúÔÚÉæ¼°ÊÓͼµÄ²éѯÖУ¬ÓÈÆäµ±ÊÓͼÁÐÊÇͨ¹ýÃèÊö¶¨ÒåµÄʱºò¡£±ÈÈ磬¿¼ÂÇÏÂÃæÊÓͼ¶¨ÒåºÍ²éѯ£º

CREATE VIEW CUST_V AS
(SELECT LASTNAME, (CUST_ID * 100) + INT(CUST_CODE) AS CUST_KEY FROM CUST)
SELECT LASTNAME FROM CUST_V WHERE CUST_KEY = 123456

²éѯÓÅ»¯Æ÷°Ñ²éѯºÍÊÓͼ¶¨ÒåºÏ²¢ÁË£¬²úÉúÏÂÃæµÄ²éѯ£º

ÕâÊÇÇ°ÃæÀý×ÓÖÐÓÐÎÊÌâµÄν´Ê¡£Äã¿ÉÒÔͨ¹ýʹÓà explain ¹¦ÄÜÏÔʾÓÅ»¯ºóµÄ SQL Óï¾äÀ´¹Û²ìÊÓͼºÏ²¢µÄ½á¹û¡£

Èç¹ûÏà·´µÄ¹¦ÄܺÜÄѱí´ï£¬¿¼ÂÇʹÓÃÒ»¸öÉú³ÉÁС£ÀýÈ磬Èç¹ûÄãÏëÕÒµ½Ò»¸öÂú×ã LASTNAME IN ('Woo', 'woo', 'WOO', 'WOo', and so on) ÃèÊö±ê×¼µÄÐÕÊÏ£¬Äã¿ÉÒÔ´´½¨Ò»¸öÉú³ÉÁÐ UCASE(LASTNAME) = 'WOO' £¬ÈçÏ £º

CREATE TABLE CUSTOMER
(LASTNAME VARCHAR(100),
U_LASTNAME VARCHAR(100) GENERATED ALWAYS AS (UCASE(LASTNAME)))
CREATE INDEX CUST_U_LASTNAME ON CUSTOMER (U_LASTNAME)

±ÜÃâÁ¬½ÓÁÐÉϵÄÊý¾ÝÀàÐͲ»Æ¥Åä

ÔÚijЩÇé¿öÏ£¬Êý¾ÝÀàÐͲ»Æ¥Å佫·Á°­Ê¹ÓùþÏ£Á¬½Ó¡£¹þÏ£Á¬½ÓÔÚÁ¬½Óν´ÊÉÏ±ÈÆäËûÁ¬½Ó·½·¨Òª¶àÒ»Ð©Ô¼Êø¡£ÌرðÊÇÁ¬½ÓÁеÄÊý¾ÝÀàÐͱØÐëÍêÈ«Ïàͬ¡£ÀýÈ磬Èç¹ûÒ»¸öÁ¬½ÓÁÐÊÇ FLOAT ÁíÍâÒ»¸öÊÇ REAL£¬¹þÏ£Á¬½Ó½«²»Ö§³Ö¡£ÁíÍ⣬Èç¹ûÁ¬½ÓÁÐÊý¾ÝÀàÐÍÊÇ CHAR¡¢GRAPHIC¡¢DECIMAL »ò DECFLOAT Ôò³¤¶È±ØÐëÒ»Ñù¡£

ÔÚν´ÊÖв»ÒªÊ¹Óà no-op ÃèÊöÀ´¸ü¸ÄÓÅ»¯Æ÷ÆÀ¹À

Ò»¸ö¡° no-op ¡± coalesce() ν´ÊÒÔ¡° COALESCE(X, X) = X ¡±»áÒýÈëÒ»¸öÆÀ¹À´íÎóµ½Ê¹ÓÃËüµÄËùÓвéѯµÄ¼Æ»®ÖС£ÏÖÔÚ DB2 ²éѯ±àÒëÆ÷ûÓÐÄÜÁ¦²»È¥Ñ¡ÔòÄǸöν´Ê²¢È·¶¨ÊÇ·ñËùÓеÄÐоßÌåÂú×ãËü¡£×÷Ϊ½á¹û£¬Õâ¸öν´Ê¼õÉÙÁ˶ÔÀ´×ÔÓÚ²¿·Ö²éѯ¼Æ»®µÄÆÀ¹ÀµÄÐÐÊý¡£Õâ¸ö¸üСµÄÐйÀ¼ÆÍ¨³£ÎªºóÃæµÄ²éѯ¼Æ»®¼õÉÙÁËÐÐÊýºÍÆÀ¹À³É±¾£¬¶øÓÐʱѡÔñ²»Í¬¼Æ»®µÄ½á¹ûÊÇÒòΪ²»Í¬ºòÑ¡¼Æ»®Ö®¼äµÄÏà¹ØÆÀ¹ÀÌõ¼þ¸Ä±äÁË¡£

ΪʲôÕâ¸öʲô¶¼²»×öµÄν´ÊÓÐʱºòÈ´»áÌá¸ß²éѯÐÔÄÜ£¿Ôö¼Óν´Ê¡° no-op ¡± coalesce() ÒýÈëÁËÒ»¸ö´íÎóÑÚ¸ÇÁËһЩ¶«Î÷£¬Òª²»È»¾ÍÊÇ×èÖ¹ÁËÐÔÄÜÓÅ»¯¡£

һЩÐÔÄÜÔöÇ¿¹¤¾ß×öÁËʲôÊÇÒ»¸öÇ¿Á¦²âÊÔ£º¹¤¾ßÒ»ÔÙÒýÈëν´Êµ½Ò»¸ö²éѯµÄ²»Í¬Î»Ö㬲Ù×÷²»Í¬µÄÁУ¬Í¨¹ýÒýÈëÒ»¸ö´íÎóÀ´³¢ÊÔÕÒµ½Ò»ÖÖÇé¿ö£¬Õâ¸ö´íÎó³öÏÖÔÚÄÄÀïÓ°ÏìÁËÒ»¸ö¸üºÃµÄ²éѯ¼Æ»®¡£ÕâÒ²ÊÇÒ»¸öÕæÕýµÄ²éѯÓï¾ä¿ª·¢ÈËÔ±ÔÚÒ»¸ö²éѯÖÐÊÖ¶¯¡£±àд¡° no-op ¡±Î½´Ê¡£Í¨³££¬¿ª·¢ÈËÔ±»áµÃµ½Ò»Ð©¶ÔÊý¾ÝµÄÁ˽âÀ´Ö¸µ¼Õâ¸öν´ÊµÄ·ÅÖá£

ʹÓÃÕâ¸ö·½·¨À´Ìá¸ß²éѯÐÔÄÜÊÇÒ»¸ö¶ÌÆÚµÄ½â¾ö·½°¸£¬Ëü²¢Ã»Óж¨Î»¸ù±¾Ô­Òò¶øÇÒ»áÓÐÒÔÏÂÓ°Ï죺

ÐÔÄÜÌáÉýµÄDZÁ¦±»ÑÚ¸ÇÁË¡£

²»Äܱ£Ö¤Õâ¸ö workaround »áÌṩÓÀ¾ÃµÄÐÔÄÜÌá¸ß£¬¾ÍÏñ DB2 ²éѯ±àÒëÆ÷¿ÉÄÜ×îÖÕ¸üºÃµÄ´¦ÀíÕâ¸öν´Ê»òÕ߯äËûËæ»úÒòËØ¿ÉÄÜÓ°ÏìÁËËü¡£

»òÐí»áÓÐÆäËûÓï¾äÊܵ½ÏàͬµÄÔ­ÒòÓ°Ïì¶øÇÒ¶ÔϵͳÐÔÄܵÄÓ°Ïìͨ³£»áÓÉÄãµÄϵͳ³ÐÊÜ¡£

±ÜÃâ²»ÏàµÈµÄÁ¬½Óν´Ê

Á¬½Óν´ÊʹÓñȽϲÙ×÷£¬³ýÁËÏàµÈÆäËû¶¼Ó¦¸Ã±ÜÃâ¡£ÒòΪ²»ÏàµÈÁ¬½Ó·½·¨¾Í»áÏÞÖÆÎªÇ¶Ì×Ñ­»·¡£¶øÇÒ£¬ÓÅ»¯Æ÷»òÐí²»ÄÜΪÁ¬½Óν´Ê¼ÆËãÒ»¸ö¾«È·µÄ¿ÉÑ¡ÆÀ¹À¡£È»¶ø²»µÈÁ¬½Óν´Ê²»ÄÜÓÀÔ¶¹æ±Ü¡£µ±ËüÃÇÊDZØÐëµÄʱºò£¬È·±£Ò»¸öν´ÊË÷Òý´æÔÚÓÚÈκÎÒ»¸ö±íÖУ¬ÒòΪÁ¬½Óν´Ê½«Ó¦ÓÃǶÌ×ÄÚ²¿Á¬½Ó¡£

²»µÈÁ¬½Óν´ÊµÄÒ»¸ö¼òµ¥Àý×ÓÊÇ£¬ÎªÁ˾«È··´Ó³Î¬¶ÈÊý¾ÝÔÚ²»Í¬µÄʱ¼äµãµÄ״̬£¬Ò»¸öÐÇÐÍģʽÖеÄά¶ÈÊý¾Ý±ØÐë°æ±¾»¯¡£Õâ³£³£×÷Ϊһ¸ö¡®ÂýÂý¸Ä±äµÄά¶È¡¯À´±»²Î¿¼¡£Ò»ÀàÂýÂý¸Ä±äµÄά¶È°üÀ¨Ã¿¸öά¶ÈÐеÄÓÐЧµÄ¿ªÊ¼ºÍ½áÊøÈÕÆÚ¡£ÎªÁËÁ¬½Óά¶ÈµÄÖ÷¼ü£¬Ò»¸öÔÚÊÂʵ±íºÍά¶È±íÖ®¼äµÄÁ¬½ÓÐèÒª¼ì²éºÍÊÂʵ±íÏà¹ØµÄÊý¾Ý£¬°üÀ¨Î¬¶ÈµÄ¿ªÊ¼ºÍ½áÊøÈÕÆÚ¡£Õâ³£³£×÷Ϊһ¸ö¡®µÚ 6 ÀàÂýÂý¸Ä±äµÄά¶È¡¯±»²Î¿¼¡£·¶Î§Á¬½Ó»Øµ½ÊÂʵ±íͨ¹ýһЩʵ¼ÊÊÂÎñÈÕÆÚÒÔ½øÒ»²½ÏÞ¶¨Î¬¶È°æ±¾£¬³É±¾»áºÜ¸ß¡£ÀýÈ磺

SELECT ... FROM
PRODUCT P, SALES F
WHERE
P.PROD_KEY = F.PROD_KEY AND F.SALE_DATE BETWEEN P.START_DATE AND P.END_DATE

ÔÚÕâ¸öÇé¿öÏ£¬ÐèҪȷ±£ÓÐÒ»¸öË÷ÒýÔÚ£¨F.PROD_KEY, F.SALE_DATE£©ÁÐÉÏ

¿ÉÒÔ¿¼ÂÇ´´½¨Ò»¸öͳ¼ÆÊÓͼÀ´°ïÖúÓÅ»¯Æ÷¼ÆËãÒ»¸ö¸üºÃµÄ¿ÉÑ¡ÆÀ¹À¡£ÀýÈç

CREATE STATISTICAL VIEW V_PROD_FACT AS
SELECT P.*
FROM PRODUCT P, SALES F
WHERE
P.PROD_KEY=F.PROD_KEY and
F.SALE_DATE BETWEEN P.START_DATE AND P.END_DATE
ALTER VIEW V_PROD_FACT ENABLE QUERY OPTIMIZATION
RUNSTATS ON TABLE DB2USER.V_PROD_FACT WITH DISTRIBUTION

Ö¸¶¨ÐÇÐÍģʽÁ¬½Ó£¬±ÈÈçË÷Òý ANDing ÐÇÐÍÁ¬½Ó£¬²¢ÇÒÈç¹ûÔÚ²éѯ¿éÖÐÓÐÈκβ»µÈÁ¬½Óν´Ê£¬¼¯ÖÐÁ¬½Ó²»±»¿¼ÂÇ¡££¨²Î¼û¡°Èç¹ûÄãʹÓÃÐÇÐÍģʽÁ¬½Ó£¬È·±£ÄãµÄ²éѯºÍ±ØÐëÆ¥ÅäÔÚ 12 Ò³Öеıê×¼¡±£©

±ÜÃâ³öÏÖ¶à¸ö DISTINCT ¹Ø¼ü×Ö

±ÜÃâʹÓÃÔÚͬһ¸ö subselect ÖÐÔËÐжà¸ö DISTINCT ¼¯ºÏµÄ²éѯ£¬ËüµÄÔËÐгɱ¾·Ç³£¸ß¡£¿¼ÂÇÏÂÃæµÄÀý×Ó£º

SELECT SUM(DISTINCT REBATE), AVG (DISTINCT DISCOUNT) FROM DAILY_ SALES GROUP BY PROD_KEY;

ΪÁËÅÐ¶Ï DISTINCT REBATE ÖµºÍ DISTINCT COUNT Öµ£¬À´×Ô PROD_KEY ±íµÄÊäÈëÁ÷ÐèÒª½øÐÐÁ½´ÎÅÅÐò¡£Õâ¸ö²éѯÓï¾äµÄ²éѯ¼Æ»®¾ÍÏñÕâÑù :

ÓÅ»¯Æ÷ÖØÐ´ÁË×î³õµÄ²éѯÓï¾ä£¬·Ö³ÉÁ½¸öµ¥¶ÀµÄ¼¯ºÏ£¬Ã¿¸öÖ¸¶¨ DISTINCT ¹Ø¼ü×Ö£¬È»ºó°Ñ¶à¸ö¼¯ºÏÓà UNION ¹Ø¼ü×ÖÁ¬½ÓÆðÀ´¡£ÄÚ²¿ÖØÐ´µÄÓï¾äÊÇ£º

SELECT Q8.MAXC0, (Q8.MAXC1 / Q8.MAXC2)
FROM
(SELECT MAX(Q7.C0) AS MAXC0, MAX(Q7.C1) AS MAXC1, MAX(Q7.C2) AS MAXC2
FROM
(SELECT SUM(DISTINCT Q2.REBATE) as C0 ,
cast(NULL as integer) AS C1, 0 AS C2, Q2.PROD_KEY
FROM
(SELECT Q1.PROD_KEY, Q1.REBATE
FROM DB2USER.DAILY_SALES AS Q1) AS Q2
GROUP BY Q2.PROD_KEY
UNION ALL
SELECT cast (NULL as integer) AS C0, SUM(DISTINCT Q5.DISCOUNT) AS C1,
COUNT(DISTINCT Q5.DISCOUNT) AS C2, Q5.PROD_KEY
FROM
(SELECT Q4.PROD_KEY, Q4.DISCOUNT
FROM DB2USER.DAILY_SALES AS Q4) AS Q5
GROUP BY Q5.PROD_KEY) AS Q7
GROUP BY Q7.PROD_KEY) AS Q8

Èç¹ûÄã²»ÄܱÜÃâ¶à¸ö DISTINCT ¼¯ºÏµÄ»°£¬¾Í¿¼ÂÇʹÓôø ENHANCED_MUTIPLE_DISTINCT Ñ¡ÏîµÄ DB2_EXTENDED_OPTIMIZATION ×¢²á±í±äÁ¿¡£ Õâ¸öÑ¡Ïʹµ½¶à¸ö DISTICT ¼¯ºÏµÄÊäÈëÁ÷±»¶Áȡһ´ÎÈ»ºó±» UNION µÄÿһ¸ö·ÖÖ§ÖØ¸´Ê¹Óá£Õâ¸öÑ¡Ïî¿ÉÒÔÔÚÊý¾Ý¿â·ÖÇøµÄ´¦ÀíÆ÷±ÈÂʽϵ͵Äʱ£¨ÀýÈ磬±ÈÂÊСÓÚ»òµÈÓÚ 1£©Ìá¸ßÕâÀà²éѯµÄÐÔÄÜ¡£Õâ¸öÉèÖÃÓ¦¸Ã¶ÔûÓжԳƵĶദÀíÆ÷µÄ DPF£¨Database Partitioning Feature£©»·¾³£¨SMPs£©ºÜÓÐÓá£Õâ¸öÓÅ»¯À©Õ¹²»ÄÜÔÚËùÓл·¾³ÖÐÌá¸ß²éѯÐÔÄÜ¡£Ó¦¸Ã½øÐвâÊÔÀ´Åжϵ¥¸ö²éѯÐÔÄܵÄÌá¸ß¡£

±ÜÃâ¶àÓàµÄν´Ê

ijЩ²éѯµÄÓïÒåÐèÒªÍâÁ¬½Ó£¨²»¹Ü×ó¡¢ÓÒ»òÈ«Á¬½Ó£©¡£È»¶ø£¬Èç¹û²éѯÓïÒå²»ÐèÒªÒ»¸öÍâÁ¬½Ó²¢±»ÓÃÓÚ´¦Àí²»Ò»ÖÂÊý¾Ý£¬ÄÇô×îºÃÊÇ´¦Àí²»Ò»ÖÂÊý¾ÝµÄ¸ù±¾Ô­Òò¡£ÀýÈ磬ÔÚÒ»¸öÊý¾Ý¼¯ÊÐÖÐÓÐÒ»¸öÐÇÐÍģʽ£¬ÊÂʵ±í¿ÉÄܰüº¬ÊÂÎñÊý¾Ý£¬²»¹ýÒòΪÊý¾Ý²»Ò»ÖµÄÎÊÌ⣬Ôì³ÉÓ븸ά¶ÈÐеÄһЩά¶È²»Æ¥Åä¡£ÕâÊÇ¿ÉÄÜ·¢ÉúµÄ£¬ÒòΪ³éÈ¡¡¢×ª»»ºÍ×°ÔØ£¨ETL£©¹ý³Ì³öÓÚijЩԭÒò²»ÓëÉÌÒµ¼ü²»¼æÈÝ¡£ÔÚÕâÖÖÇé¿öÏ£¬ÊÂʵ±íµÄÐÐÓëά¶È×óÁ¬½Ó¿ÉÒÔÈ·±£ËüÃǼ´Ê¹ÔÚûÓи¸±íµÄÇé¿öϵõ½·µ»Ø¡£ÀýÈ磺

SELECT ... FROM DAILY_SALES F
LEFT OUTER JOIN CUSTOMER C ON F.CUST_KEY = C.CUST_KEY
LEFT OUTER JOIN STORE S ON F.STORE_KEY = S.STORE_KEY
WHERE
C.CUST_NAME = 'SMITH'

×óÍâÁ¬½Ó»á×èֹһЩÓÅ»¯£¬Ò²°üÀ¨Ê¹ÓÃÖ¸¶¨µÄÐÇÐÍģʽÁ¬½Ó·ÃÎÊ·½·¨¡£È»¶ø£¬ÔÚijЩÇé¿öÏÂ×óÍâÁ¬½Ó¿ÉÒÔ×Ô¶¯±»²éѯÓÅ»¯Æ÷ÖØÐ´³ÉÒ»¸öÄÚ²¿Á¬½Ó¡£ÔÚÕâ¸öÀý×ÓÖУ¬ÔÚ CUSTOMER ºÍ DAILY_SALES Ö®¼äµÄ×óÍâÁ¬½Ó¿ÉÒÔ±»×ª»»³ÉÒ»¸öÄÚ²¿Á¬½Ó£¬ÒòΪ C.CUST_NAME= ¡¯ SMITH ¡¯Î½´Ê½«ÅųýÕâÒ»ÁÐÖÐËùÓÐ NULL ÖµµÄÐУ¬Ê¹µÃÒ»¸ö×óÍâÁ¬½ÓÓïÒå±äµÃûÓбØÒª¡£ËùÒÔÓÉÓÚÍâÁ¬½ÓµÄ³öÏÖµ¼ÖÂÄÇЩÓÅ»¯Ëðʧ¿ÉÄܲ¢²»»á¶ÔËùÓвéѯ²úÉú¸ºÃæÓ°Ï졣Ȼ¶ø£¬×¢Òâµ½ÕâЩÏÞÖÆ²¢±ÜÃâÍâÁ¬½Ó·Ç³£ÖØÒª£¬³ý·ÇËüÃÇÊǾø¶Ô±ØÐëµÄ¡£

°Ñ FETCH FIRST N ROWS ONLY ×Ó¾äºÍ OPTIMIZE FOR N ROWS ×Ó¾ä·ÅÔÚÒ»ÆðʹÓÃ

OPTIMIZE FOR N ROWS ×Ó¾ä±íÃ÷¶ÔÓÚÓÅ»¯Æ÷Ó¦ÓóÌÐò´òËãÖ»»ñÈ¡ N ÐУ¬²»¹ý²éѯ½«·µ»ØÍêÕûµÄ½á¹û¼¯¡£ FETCH FIRST N ROWS ONLY ×Ó¾äÏÔʾ²éѯֻÐè·µ»Ø N ÐС£

DB2 Êý¾Ý·þÎñÆ÷²»»áÔÚΪÍⲿ×Ó²éѯָ¶¨ÁË FETCH FIRST N ROWS ONLY ¶ø×Ô¶¯¼ÙÉè OPTIMIZE FOR N ROWS ¡£³¢ÊÔÓë FETCH FIRST N ROWS ONLY Ò»ÆðÖ¸¶¨ OPTIMIZE FOR N ROWS À´¹ÄÀø²éѯ¼Æ»®Ö±½Ó´ÓÒýÓÃµÄ±í·µ»ØÐУ¬¶ø²»Ö´ÐÐÒ»¸ö»º³å²Ù×÷£¬±ÈÈç²åÈëÒ»¸öÁÙʱ±í¡¢ÅÅÐò»ò²åÈëÒ»¸ö¹þÏ£Á¬½ÓµÄ¹þÏ£±í¡£

Ó¦ÓóÌÐòÖ¸¶¨ OPTIMIZE FOR N ROWS À´¹ÄÀø²éѯ¼Æ»®±ÜÃ⻺³å²Ù×÷£¬Ò²²»»ñÈ¡Õû¸ö½á¹û¼¯£¬ËüÃÇ¿ÉÄܵ¼ÖÂÐÔÄܵÍÏ¡£ÕâÊÇÒòΪ¿ìËÙ·µ»ØÇ° N ÐеIJéѯ¼Æ»®¿ÉÄܲ»ÊǶÔÓÚ»ñÈ¡Õû¸ö½á¹û¼¯µÄ²éѯ¼Æ»®¡£

Èç¹ûÄãÕýÔÚʹÓÃÐÇÐÍģʽµÄÁ¬½Ó£¬ÇëÈ·±£ÄãµÄ²éѯÂú×ãÐèÒªµÄ±ê×¼

ÓÅ»¯Æ÷ΪÐÇÐÍģʽ¿¼ÂÇÁ½¸öÌØÊâµÄÁ¬½Ó·½·¨£¬½Ð×öÒ»¸öÐÇÐÍÁ¬½Ó»ò¼¯ÖÐÁ¬½Ó£¬ËüÃÇ¿ÉÒÔÃ÷ÏÔµÄÌá¸ßÐÔÄÜ¡£Èç¹û²éѯ±ØÐëÂú×ãÒÔϱê×¼¡£

¶Ôÿ¸ö²éѯ¿é

×îÉÙ 3 ¸ö²»Í¬µÄ±í±»Á¬½Ó

ËùÓÐÁ¬½Óν´Ê±ØÐëÏàµÈ

ûÓÐ×Ó²éѯ´æÔÚ

ÔÚ±íÓë±íÖ®¼ä»òÕß²éѯ¿éÖ®¼ä²»´æÔÚÏà¹ØÐÔ»òÒÀÀµÐÔ

ÁíÍ⣬¶ÔÓÚË÷Òý ANDing£¬±ØÐëûÓв»È·¶¨µÄº¯Êý£¬ÒòΪΪ·½±ã semi-joins ÊÂʵ±íµÄν´Ê±ØÐëͨ¹ýË÷ÒýÀ´Ó¦ÓÃ

ÊÂʵ±í

ÊDzéѯ¿éÖÐ×î´óµÄ±í

ÉÙÓÚ 10000 ÐÐ

±»ÊÓΪֻÓÐÒ»¸ö±í

±ØÐëÁ¬½Óµ½ÖÁÉÙÁ½¸öά¶È±í»ò½Ð×ö snowflakes µÄ×é¡£

ά¶È±í

²»ÊÇÊÂʵ±í

¿ÉÒÔµ¥¾ÝÁ¬½Óµ½ÊµÊ©±í»òÔÚ snowflakes ÖÐ

Ò»¸öά¶È±í»òÕßÒ»¸ö snowflake ¡£

±ØÐë¹ýÂËʵʩ±í£¨»ùÓÚÓÅ»¯Æ÷µÄÆÀ¹À½á¹ûÀ´¹ýÂË£©

±ØÐëÓÐÒ»¸öÁ¬½Óν´Êµ½ÊµÊ©±í£¬ËüʹÓÃÁËʵʩ±íË÷ÒýÖÐÖ÷ÒªµÄÁС£Õâ¸ö¹æ·¶±ØÐëÂú×㣬²»¹ÜÊÇ¿¼ÂÇÐÇÐÍÁ¬½Ó»ò¼¯ÖÐÁ¬½Ó£¬¼´Ê¹¼¯ÖÐÁ¬½Ó½«ÖÁÐèҪʹÓÃÒ»¸öµ¥¶ÀµÄÊÂʵ±íË÷Òý¡£

Ò»¸ö²éѯ¿é±íÏÖÒ»¸ö×óÍⲿÁ´½Ó»òÓÒÍⲿÁ¬½Ó£¬¿ÉÒÔֻɿ¼°Á½¸ö±í£¬ËùÒÔ²»·ûºÏÒ»¸öÐÇÐÍģʽµÄÁ¬½Ó

²»ÐèҪΪÓÅ»¯Æ÷ÄÜ·¢ÏÖÒ»¸öÐÇÐÍģʽÁ¬½Ó¶øÏÔʾµÄÉùÃ÷²Î¿¼ÍêÕûÐÔ¡£

±ÜÃâ¶àÓàµÄν´Ê

±ÜÃâ¶àÓàµÄν´Ê£¬ÓÈÆäÊǵ±ËûÃÇ·¢ÉúÔÚ²»Í¬µÄ±íµÄʱºò¡£ÔÚijЩÇé¿öÏ£¬ÓÅ»¯Æ÷²¢²»ÄÜÅж϶àÓàµÄν´Ê¡£Õâ¿ÉÄܵ¼Ö»ùÊý±»µÍ¹À¡£

ÀýÈ磬ÔÚ SAP BI Ó¦ÓóÌÐòÖÐÑ©»¨Ä£Ê½ºÍʵʩ±íÒÔ¼°Î¬¶È±í±»×÷Ϊһ¸ö²éѯÓÅ»¯Êý¾Ý½á¹¹±»Óõ½¡£ÔÚһЩÇé¿öÏ»áÓжàÓàµÄʱ¼äÌØÕ÷ÁУ¨¶ÔÓÚÔµġ° SID_0CALMONTH ¡±»ò¶ÔÓÚÄêµÄ "SID_0FISCPER"£©¶¨ÒåÔÚÊÂʵ±íºÍά¶È±íÖС£

SAP BI OLAP ´¦ÀíÆ÷ÔÚά¶ÈºÍÊÂʵ±íµÄʱ¼äÌØÕ÷ÁÐÉÏ»á²úÉú¶àÓàµÄν´Ê¡£

ÕâЩ¶àÓàµÄν´Ê¿ÉÄܲúÉúºÜ³¤µÄÔËÐÐʱ¼ä¡£

ÏÂÃæÌṩÁËÒ»¸öÀý×Ó£¬ÔÚÒ»¸ö SAP BI ²éѯÖÐÓÐÁ½¸ö¶àÓàµÄν´Ê±»¶¨ÒåÔÚ WHERE Ìõ¼þÀï¡£ÏàͬµÄλν´Ê¶¨ÒåÔÚʱ¼äά¶È£¨DT£©ºÍÊÂʵ£¨F£©±í¡£

AND ( "DT"."SID_0CALMONTH" = 199605
AND "F". "SID_0CALMONTH" = 199605
OR "DT"."SID_0CALMONTH" = 199705
AND "F". "SID_0CALMONTH" = 199705 )
AND NOT ( "DT"."SID_0CALMONTH" = 199803
AND "F". "SID_0CALMONTH" = 199803 )

DB2 ÓÅ»¯Æ÷ûÓÐ×¢Òâµ½ÏàͬµÄν´Ê£¬²¢¶ÔËüÃÇ·Ö±ð´¦Àí¡£Õâµ¼Öµ͹ÀÁËν´Ê¡¢Éú³É²»ÊÇ×îÓŵķÃÎʼƻ®ÒÔ¼°¸ü³¤µÄ²éѯÔËÐÐʱ¼ä¡£

´¦ÓÚÕâ¸öÔ­Òò¶àÓàµÄν´Ê´Ó DB2 Êý¾Ý¿â¾ßÌåÆ½Ì¨Èí¼þ²ãÃæÈ¥µôÁË¡£

ÒÔÉÏν´Êת»»³ÉÏÂÃæÕâÒ»¸ö¡£Ö»ÓÐÊÂʵ±í¡° SID_0CALMONTH ¡±ÁÐÉϵÄν´Ê±»±£Áô£º

AND ( "F". "SID_0CALMONTH" = 199605
OR "F". "SID_0CALMONTH" = 199705 )
AND NOT ( "F". "SID_0CALMONTH" = 199803 )

Ó¦Óà SAP 957070 ºÍ 1144883 ±¸Íü¼À´È¥µô¶àÓàµÄν´Ê¡£

Éè¼Æ²¢ÅäÖÃÄãµÄÊý¾Ý¿â

ÓкܶàÊý¾Ý¿âÉè¼ÆºÍÅäÖÃÑ¡Ïî¿ÉÒÔÓ°Ïì²éѯÐÔÄÜ¡£¶ÔÊý¾Ý¿âÉè¼ÆµÄ¸ü¶à½¨Òé²Î¿¼¡° Planning your Physical Database Design ¡±×î¼Ñʵ¼ùÎÄÕ¡£

ʹÓÃÔ¼ÊøÀ´Ìá¸ß²éѯÓÅ»¯

¿¼ÂǶ¨ÒåµÄΨһÐÔ£¬¼ì²é²¢²Î¿¼Ò»ÖÂÐÔÔ¼Êø¡£ÕâÐ©Ô¼ÊøÌṩÁËÓïÒåÐÅÏ¢£¬ÔÊÐí DB2 ÓÅ»¯Æ÷ÖØÐ´²éѯÀ´ÆÀ¹ÀÁ¬½Ó£¬Í¨¹ýÁ¬½ÓÀ´½µµÍ¾ÛºÏºÍ FETCH FIRST N ROWS£¬È¥µô²»±ØÒªµÄ DISTINCT Ñ¡Ïî±»ºÍһЩÆäËüµÄÓÅ»¯¡£µ±Ó¦ÓóÌÐò¿ÉÒÔ±£Ö¤Ëü×Ô¼ºµÄ¹ØÏµÊ±£¬ÐÅÏ¢Ô¼ÊøÒ²¿ÉÒÔ±»ÓÃÀ´¼ì²é²¢²Î¿¼Ò»ÖÂÐÔÔ¼Êø¡£ÏàͬµÄÓÅ»¯Ò²ÊÇ¿ÉÒԵġ£µ±¸üУ¨²åÈë»òɾ³ý£©ÐеÄʱºò£¬À´×ÔÊý¾Ý¿â¹ÜÀíÆ÷µÄÇ¿ÖÆÔ¼Êø¿ÉÄܵ¼ÖºܸߵÄϵͳ¿ªÏú£¬ÓÈÆäÔÚ¸üкܶàÓÐÒ»ÖÂÐÔÔ¼ÊøµÄÐеÄʱºò¡£Èç¹ûÒ»¸öÓ¦ÓóÌÐòÔÚ¸üÐÂÒ»ÐÐ֮ǰÒѾ­ÑéÖ¤µÄÐÅÏ¢£¬ÕâÑùʹÓÃÐÅÏ¢Ô¼Êø±ÈÆðÕý³£µÄÔ¼Êø¸üÓÐЧ

ÀýÈ磬¿¼ÂÇ 2 ¸ö±í DAILY_SALES ºÍ CUSTOMER ¡£ÔÚ CUSTOMER ±íÖеÄÿһÐж¼ÓÐÒ»¸öΨһµÄ¿Í»§¼üÖµ£¨CUST_KEY£©¡£ DAILY_SALES °üº¬Ò»¸ö CUST_KEY Áв¢ÇÒÿһÐж¼ÒýÓÃÒ»¸ö CUSTOMER ±íÖеĿͻ§¼ü¡£¿ÉÒÔ´´½¨Ò»¸ö²Î¿¼Ò»ÖÂÐÔÔ¼ÊøÀ´·ÀÖ¹ÔÚ CUSTOMER ºÍ DAILY_SALES Ö®¼ä·¢Éú 1:N µÄ¹ØÏµ¡£Èç¹ûÓ¦ÓóÌÐòÒªÇ¿ÖÆÔ¼ÊøÕâ¸ö¹ØÏµ£¬¿ÉÒÔ´´½¨Ò»¸öÐÅÏ¢»¯µÄÔ¼Êø¡£ÄÇôÏÂÃæµÄ²éѯ±ÜÃâÁËÔÚ CUSTOMER ºÍ DAILY_SALES Ö®¼ä½øÐÐÁ¬½Ó£¬ÒòΪûÓÐ´Ó CUSTOMER »ñÈ¡ÈκÎÁУ¬¶øÇÒÀ´×ÔÓÚ DAILY_SALES µÄÿһÐж¼¿ÉÒÔÔÚ CUSTOMER ÀïÃæÕÒµ½Óë֮ƥÅäµÄÐУ¬ËùÒÔ²éѯÓÅ»¯Æ÷½«×Ô¶¯É¾³ýÁ¬½Ó

SELECT AMT_SOLD, SALE PRICE, PROD_DESC
FROM DAILY_SALES, PRODUCT, CUSTOMER
WHERE
DAILY_SALES.PROD_KEY = PRODUCT.PRODKEY AND
DAILY_SALES.CUST_KEY = CUSTOMER.CUST_KEY

Ó¦ÓóÌÐò±ØÐëÖ´ÐÐÐÅÏ¢Ô¼Êø£¬·ñÔò²éѯ¿ÉÄÜ·µ»Ø²»ÕýÈ·µÄ½á¹û¡£ÔÚÉÏÃæµÄÀý×ÓÖУ¬Èç¹ûÐдæÔÚÓÚ DAILY_SALES ÖУ¬ÔÚ CUSTOMER ±íÖÐÈ´ÕÒ²»µ½ÏàÓ¦µÄ¿Í»§¼ü£¬ÄÇôÉÏÃæµÄ²éѯ·µ»ØµÄÐпÉÄܲ»ÕýÈ·¡£

ÔÚ¸´ÔÓ²éѯÖÐʹÓà REOPT °ó¶¨Ñ¡ÏîºÍÊäÈë±äÁ¿

ÔÚÒ»¸öÔÚÏßÊÂÎñ´¦Àí£¨OLTP£©»·¾³µÄÖÐÊäÈë±äÁ¿ÓнϺõÄÓï¾ä×¼±¸Ê±¼äÊǹؼü£¬ÔÚÕâÑùµÄ»·¾³ÖÐÓï¾äÍùÍù±È½Ï¼òµ¥¶øÇÒ²éѯ¼Æ»®Ñ¡ÔñÒ²ºÜ¼òµ¥¡£Ê¹Óò»Í¬µÄÊäÈë±äÁ¿¶à´ÎÔËÐÐÏàͬµÄÓï¾ä¿ÉÒÔ¸´ÓÃÔÚ¶¯Ì¬Óï¾ä¸ßËÙ»º´æÖбàÒëÁ˵ķÃÎÊÆ¬¶Î£¬±ÜÃâÁËÓÉÓÚËæÊ±¸ü¸ÄÊäÈëÖµ¶øÔì³É°º¹óµÄ SQL Óï¾ä±àÒ뿪Ïú¡£

È»¶ø£¬ÊäÈë±äÁ¿¶Ô¸´ÔӵIJéѯ¸ºÔØÒ²»áÔì³ÉÎÊÌ⣬ËüÃǵIJéѯ¼Æ»®Ñ¡Ôñ·Ç³£¸´ÔÓ£¬Òò´ËÓÅ»¯Æ÷ÐèÒª¸ü¶àµÄÐÅÏ¢À´×ö³öºÃµÄ¾ö¶¨¡£¶øÇÒ£¬Óï¾ä±àÒëʱ¼äͨ³£ÊÇ×ÜÔËÐÐʱ¼äÖеÄÒ»¸öºÜС×é³É²¿·Ö¡£ÒòΪ BI ²éѯͨ³£²»»áÖØ¸´£¬ËùÒÔ²¢Ã»ÓдӶ¯Ì¬Óï¾ä¸ßËÙ»º´æÉϵõ½ºÃ´¦¡£

Èç¹ûÔÚÒ»¸ö¸´ÔÓ²éѯ¹¤×÷¸ºÔØÖÐÐèҪʹÓÃÊäÈë±äÁ¿£¬Ç뿼ÂÇʹÓà REOPT(ALWAYS) BIND Ñ¡Ïî¡£µ±ÊäÈë±äÁ¿ÖµÊÇÒÑÖªµÄ£¬REOPT BIND Ñ¡Ïî´Ó PREPARE µ½ OPEN »òÖ´Ðйý³ÌÖÐÍÆ³ÙÁËÓï¾ä±àÒë¡£±äÁ¿Öµ±»´«µÝµ½ SQL ±àÒëÆ÷ÖУ¬ÕâÑùÓÅ»¯Æ÷¿ÉÒÔʹÓÃÕâЩ±ãÀûÀ´¼ÆËãÒ»¸ö¸ü¾«È·µÄÑ¡ÔñÆÀ¹À¡£ REOPT(ALWAYS) ±íʾËùÓÐÖ´ÐÐÓï¾ä¶¼Ó¦¸Ã±»Ô¤±àÒë¡£ REOPT(ALWAYS) Ò²¿ÉÒÔ±»ÓÃÓÚÉæ¼°ÌØÊâ¼Ä´æÆ÷µÄ¸´ÔÓ²éѯ£¬±ÈÈç "WHERE TRANS_DATE = CURRENT DATE - 30 DAYS" ¡£Èç¹ûÊäÈë±äÁ¿¶Ô OLTP ¹¤×÷¸ºÔØÔì³É½Ï²îµÄ·ÃÎʼƻ®Ñ¡Ôñ£¬²¢ÇÒ REOPT(ALWAYS) Ñ¡ÏîÒòΪÓï¾ä±àÒëÔì³É¹ý¶àµÄ¿ªÏú£¬ÄÇô¿¼ÂǶÔÌôÑ¡¹ýµÄ²éѯʹÓà REOPT(ONCE) ¡£ REOPT(ONCE) ÍÆ³ÙÓï¾äµÄ±àÒëÖ±µ½Ê׸öÊý¾Ý±äÁ¿±»°ó¶¨¡£Ê¹ÓÃÕâ¸öÊ׸öÊäÈë±äÁ¿Öµ±àÒë²¢ÓÅ»¯ SQL Óï¾ä¡£ºóÐøÊ¹Óò»Í¬µÄÖµÀ´ÔËÐеÄÓï¾ä½«ÖØÓûùÓÚµÚÒ»¸öÊäÈë±àÒëµÄ²éѯƬ¶Î¡£ÕâÊÇÒ»¸öºÃ·½·¨ , Èç¹ûÊ׸öÊäÈë±äÁ¿´ú±íÁ˺óÐøµÄÊäÈëÖµ£¬²¢ÇÒÔÚÊäÈëֵδ֪µÄÇé¿öÏÂ±ÈÆðÓÅ»¯Æ÷ʹÓò»Í¬µÄÖµ½øÐÐÆÀ¹À£¬ËüÌṩ¸öÁËÒ»¸ö¸üºÃµÄ²éѯ·ÃÎʼƻ® .

Óкܶ෽·¨À´Ö¸¶¨ REOPT£º

¶Ô C/C++ Ó¦ÓóÌÐòÖеÄǶÈëʽ SQL£¬Ê¹Óà REOPT BIND Ñ¡Ïî¡£Õâ¸ö BIND Ñ¡ÏîÓ°Ï쾲̬ºÍ¶¯Ì¬ SQL µÄÔÙÓÅ»¯ÐÐΪ¡£

¶Ô CLP °ü£¬Óà REOPT °ó¶¨²ÎÊýÖØÐÂ°ó¶¨ CLP °ü¡£ÀýÈ磬ʹÓà CS ¸ôÀë¼¶±ðºÍ REOPT ALWAYS À´ÖØÐÂ°ó¶¨ CLP °ü£¬ÏêϸÃüÁ

rebind nullid.SQLC2G13 reopt always;

¶ÔʹÓô«Í³ JDBC Çý¶¯µÄ CLI Ó¦ÓóÌÐò»ò JDBC Ó¦ÓóÌÐò£¬ÔÚ db2cli.ini ÖÐÉèÖà REOPT ¹Ø¼ü×Ö¡£Ñ¡ÏîµÄÖµÊÇ£º

2 - NONE

3 - ONCE

4 - ALWAYS

¶ÔÓÚʹÓà JCC ͨÓÃÇý¶¯µÄ JDBC Ó¦ÓóÌÐò£¬Ê¹ÓÃÏÂÃæµÄ·½·¨Ö®Ò»£º

ʹÓà SQLATTR_REOPT Á¬½Ó»òÓï¾äÊôÐÔ¡£

ʹÓà SQL_ATTR_CURRENT_PACKAGE_SET Á¬½Ó»òÓï¾äÊôÐÔÀ´Öƶ¨ NULLID¡¢NULLIDR1 »ò NULLIDRA °ü¼¯ºÏ¡£ NULLIDR1 ºÍ NULLIDRA ÊDZ£ÁôµÄ°ü¼¯ºÏÃû³Æ¡£Ò»µ©Ê¹ÓþͷֱðÒþº¬ÁË REOPT ONCE ºÍ REOPT ALWAYS ¡£ÕâЩ°ü¼¯ºÏÐèÒªÓÚÄǸöÏÂÃæÃüÁîÏÔʾµÄ´´½¨£º

db2 bind db2clipk.bnd collection NULLIDR1;
db2 bind db2clipk.bnd collection NULLIDRA;

¶Ô SQL PL ´æ´¢¹ý³ÌʹÓÃÏÂÃæµÄ·½·¨Ö®Ò»£º

ʹÓà SET_ROUTINE_OPTS ´æ´¢¹ý³ÌÀ´ÎªÔÚµ±Ç°»á»°Öд´½¨ SQL PL ´æ´¢¹ý³ÌÉèÖðó¶¨Ñ¡ÏÀýÈçµ÷Óà sysproc.set_routine_opts( ¡® reopt always ¡¯ )

ʹÓà DB2_SQLROUTINE_PREPOPTS ×¢²á±í±äÁ¿ÔÚʵÀý¼¶±ðÉèÖà SQL PL ´æ´¢¹ý³ÌÑ¡Ïî¡£ÖµÉèÖÃΪʹÓà SET_ROUTINE_OPTS ´æ´¢¹ý³Ì½«¸²¸Ç DB2_SQLROUTINE_PREPOPTS Ö¸¶¨µÄÖµ

Äã¿ÉÒ²ÄÜʹÓÃÓÅ»¯ÅäÖÃÀ´Îª¾²Ì¬Óï¾äºÍ¶¯Ì¬Óï¾äÉèÖà REOPT£¬ÈçÏÂÃæÀý×ÓÏÔʾµÄ£º

<STMTPROFILE ID="REOPT example ">
<STMTKEY>
<![CDATA[select acct_no from customer where name = ? ]]>
</STMTKEY>
<OPTGUIDELINES>
<REOPT VALUE='ALWAYS'/>
</OPTGUIDELINES>
</STMTPROFILE>

ΪÄãµÄ¹¤×÷¸ºÔØÑ¡Ôñ×î¼ÑµÄÓÅ»¯¼¶±ð

ÉèÖÃÓÅ»¯¼¶±ð¿ÉÒÔ»ñµÃÏÔʽָ¶¨ÓÅ»¯¼¼ÊõµÄºÃ´¦£¬ÓÈÆä³öÓÚÏÂÃæµÄÔ­Òò£º

ΪÁ˹ÜÀí·Ç³£Ð¡µÄÊý¾Ý¿â»òÕ߷dz£¼òµ¥µÄ²éѯÓï¾ä

ΪÁËÔÚÄãµÄÊý¾Ý¿â·þÎñÆ÷±àÒëʱ½øÐÐÄÚ´æÏÞÖÆ

ΪÁ˼õÉÙ²éѯ±àÒëʱ¼ä£¬±ÈÈç PREPARE

´ó¶àÊýÓï¾ä¿ÉÒÔͨ¹ýʹÓÃµÚ 5 ¼¶ÓÅ»¯µÃµ½³ä·ÖµÄÓÅ»¯ºÍºÏÀíµÄ×ÊÔ´£¬ÕâÒ²ÊÇĬÈϵIJéѯÓÅ»¯¼¶±ð¡£ÔÚÒ»¸ö¸ø¶¨µÄÓÅ»¯¼¶±ð£¬²éѯ±àÒëʱ¼äºÍ×ÊÔ´ÏûºÄÊÇÖ÷ÒªÊܲéѯ¸´ÔӶȵÄÓ°Ï죬ÓÈÆäÊÇÁ¬½ÓÒÔ¼°×Ó²éѯµÄÊýÄ¿¡£²»¹ý£¬±àÒëʱ¼äºÍ×ÊÔ´µÄʹÓÃͬÑùÊܵ½Ö´ÐÐÓÅ»¯µÄÓ°Ïì¡£

²éѯÓÅ»¯¼¶±ð 1£¬2£¬3£¬5 ºÍ 7 ÊÊÓÃÓÚÒ»°ãÓÃ;¡£Ö»ÓÐÄãÐèÒª½øÒ»²½¼õÉÙ²éѯÓÅ»¯Ê±¼ä¶øÇÒÔÚÄãÖªµÀ SQL Óï¾ä·Ç³£¼òµ¥µÄÇé¿öϲſ¼ÂǼ¶±ð 0 ¡£

Tip£ºÒª·ÖÎöÒ»¸öÔËÐкܳ¤Ê±¼äµÄ²éѯ£¬¶Ô²éѯÔËÐÐ db2batch À´ÕÒ³ö»¨Á˶àÉÙʱ¼äÔÚ±àÒëÉÏÔÚÔËÐÐÉÏ»¨·ÑÁ˶àÉÙʱ¼ä¡£Èç¹û±àÒëÐèÒª¸ü¶àµÄʱ¼ä£¬½µµÍÓÅ»¯¼¶±ð¡£Èç¹ûÖ´ÐÐÐèÒª¸ü¶àµÄʱ¼äÄÇô¾Í¿¼ÂǸü¸ßµÄÓÅ»¯¼¶±ð

µ±ÄãÑ¡ÔñÁËÒ»¸öÓÅ»¯¼¶±ð£¬¿¼ÂÇÏÂÃæµÄÒ»°ã×¼Ôò£º

´ÓʹÓÃĬÈϲéѯÓÅ»¯¼¶±ð¿ªÊ¼£¬¼¶±ð 5

ҪʹÓÃĬÈϼ¶±ðÖ®ÍâµÄ¼¶±ð£¬Ê×Ïȳ¢ÊÔ¼¶±ð 1£¬2 »ò 3 ¡£¼¶±ð 0£¬1 ºÍ 2 ʹÓÃ̰À·Á¬½Óö¾ÙÔËËã·¨Ôò¡£

Èç¹ûÄãÓкܶà±íÒÔ¼°ÔÚͬһÁÐÉÏÓдóÁ¿µÄÁ¬½Óν´Ê£¬ÔÚ¹ØÐıàÒëʱ¼äµÄÇé¿öÏÂʹÓÃÓÅ»¯¼¶±ð 1 »ò 2 ¡£

¶ÔÖ»Óв»µ½Ò»ÃëµÄÔËÐÐʱ¼äµÄ²éѯʹÓÃÒ»¸öµÍµÄÓÅ»¯¼¶±ð£¨0 »ò 1£©¡£±ÈÈç²éѯÍùÍùÓÐÏÂÃæµÄÌØµã£º

Ö»·ÃÎÊÒ»¸ö»òºÜÉٵıí

Ö»»ñȡһÐлòÕß¼¸ÐÐ

ʹÓÃÍêȫΨһµÄË÷Òý

ÔÚÏßÊÂÎñ´¦Àí£¨OLTP£©ÊÂÎñÊÇÕâÖÖÀàÐÍ·ÃÎʵĺܺÃÀý×Ó

¶Ô³¤Ê±¼äÔËÐУ¨³¬¹ý 30 Ã룩µÄÓï¾äʹÓøßһЩµÄÓÅ»¯¼¶±ð£¨3£¬5 »ò 7£©¡£

ÓÅ»¯¼¶±ð 3 ¼°ÆäÒÔÉÏʹÓö¯Ì¬±à³ÌÁ¬½Óö¾ÙËã·¨¡£Õâ¸öËã·¨¿¼ÂǸü¶àµÄ¿ÉÑ¡¼Æ»®£¬²¢ÇÒ¿ÉÄÜÕÐÖÂ±È 0£¬1£¬ºÍ 2 ¸ü¶àµÄ±àÒëʱ¼ä£¬ÓÈÆäÔÚ±íµÄÊýÄ¿Ôö¼Óºó¡£

Ö»ÓÐÔÚÄã¶ÔÒ»¸ö²éѯÓÐÌØ±ðµÄÓÅ»¯ÐèÇóʱ²ÅʹÓÃÓÅ»¯¼¶±ð 9 ¡£

¸´ÔÓ²éѯÐèÒª²»Í¬ÊýÁ¿µÄÓÅ»¯À´Ñ¡Ôñ×î¼Ñ·ÃÎʼƻ®¡£¶ÔÓÐÏÂÃæÌØÕ÷µÄ²éѯ£¬Ç뿼ÂÇʹÓøü¸ßµÄÓÅ»¯¼¶±ð£º

·ÃÎÊÒ»¸ö´ó±í

ν´ÊÊýÄ¿ºÜ¶à

´óÁ¿µÄ×Ó²éѯ

ºÜ¶àÁ¬½Ó

ºÜ¶à¼¯ºÏ²Ù×÷£¬±ÈÈç UNION ºÍ INTERSECT

ºÜ¶àÆ¥ÅäµÄÐÐ

ÓÐ GROUP BY ºÎ HAVING ²Ù×÷

ǶÌ×±íÃèÊö

´óÁ¿µÄÊÓͼ

¾ö²ßÖ§³Ö²éѯ»òÔµױ¨¸æ²éѯ¶ÔÓÚÊý¾Ý¿âÊÇÒ»¸öºÜ³£¼ûµÄ¸´ÔÓ²éѯµÄºÜºÃÀý×Ó£¬¶ÔÓÚÕâÀà²éѯÓÅ»¯¼¶±ðÖÁÉÙÓ¦¸ÃʹÓÃĬÈÏÖµ¡£

ʹÓøü¸ßµÄ²éѯÓÅ»¯¼¶±ðµÄ SQL Óï¾äÊÇÓɲéѯÉú³ÉÆ÷²úÉúµÄ¡£ºÜ¶à²éѯÉú³ÉÆ÷´´½¨Ð§ÂʵÍϵIJéѯ¡£Ð´µÃºÜ×¾ÁӵIJéѯ£¬°üÀ¨ÄÇЩÓвéѯÉú³ÉÆ÷²úÉúµÄ²éѯ£¬ÐèÒª¶îÍâµÄÓÅ»¯ÒÔÑ¡ÔñÒ»¸öºÃµÄ·ÃÎʼƻ®¡£Ê¹ÓòéѯÓÅ»¯¼¶±ð 2 ºÍ¸ü¸ßµÄ¼¶±ð¿ÉÒÔÌá¸ßÄÇЩ SQL ²éѯ¡£

¶ÔÓÚ SAP Ó¦ÓóÌÐò£¬×ÜÊÇʹÓÃÓÅ»¯¼¶±ð 5 ¡£Õâ¸öÓÅ»¯¼¶±ðÆôÓÃÁ˺ܶàΪ SAP ÓÅ»¯¹ýµÄ DB2 ¹¦ÄÜ£¬±ÈÈçÉèÖà DB2_REDUCED_OPTIMIZATION ×¢²á±í±äÁ¿¡£

ʹÓòÎÊý±ê¼ÇÀ´¼õÉÙ¶¯Ì¬Óï¾äµÄ±à¼­Ê±¼ä

DB2 Êý¾Ý·þÎñÆ÷¿ÉÒÔͨ¹ýÔÚ¶¯Ì¬Óï¾ä¸ßËÙ»º´æÖб£´æ·ÃÎÊÆ¬¶ÎºÍÓï¾äÎı¾À´±ÜÃâÖØ¸´Ô¤±àÒëÒ»¸öÇ°ÃæÔËÐйýµÄ¶¯Ì¬ SQL Óï¾ä¡£¶ÔÕâ¸öÓï¾äµÄÒ»¸öºóÐø PREPARE ÇëÇ󽫳¢ÊÔÔÚ¶¯Ì¬Óï¾ä¸ßËÙ»º´æÖвéÕÒ·ÃÎÊÆ¬¶ÎÀ´±ÜÃâ±àÒ롣Ȼ¶ø£¬Ö»ÒªÎ½´ÊÔÚ×ÖÃæÉÏÓÐÒ»µã²»Í¬£¬Õâ¸öÓï¾ä¸ßËÙ»º´æÖÐµÄÆ¬¶Î¾Í²»Ò»Ö¡£ÀýÈ磬ÏÂÃæÁ½¸öÓï¾ä¾ÍÔÚ¶¯Ì¬Óï¾ä¸ßËÙ»º´æÖб»¿´×÷²»Í¬µÄÓï¾ä¡£

SELECT AGE FROM EMPLOYEE WHERE EMP_ID = 26790
SELECT AGE FROM EMPLOYEE WHERE EMP_ID = 77543

Èç¹ûËüÃÇÔËÐеÃ̫Ƶ·±£¬Ïà¹Ø SQL Óï¾äµÄ±àÒëÉõÖÁ»áÔì³É¶îÍâµÄϵͳ CPU ¸ºµ£¡£ÔÚ¡° Monitoring and Tuning the System ¡±×î¼Ñʵ¼ùÎÄÕÂÖÐÃèÊöÁË ÈçºÎ¼ì²âÕâÐÔÄÜÎÊÌâ¡£Èç¹ûÄãµÄϵͳÓöµ½ÕâÀàÐÔÄÜÎÊÌ⣬Ӧ¸Ã¿¼ÂǰÑÓ¦ÓóÌÐò¸Ä³ÉʹÓòÎÊý±ê¼ÇÀ´°Ñν´ÊµÄÖµ´«µÝ¸ø DB2 ±àÒëÆ÷£¬¶ø²»ÒªÏÔʽµÄÔÚ SQL Óï¾äÖаüº¬Ëü¡£²»¹ý£¬¶ÔÓÚ¸´ÔӵIJéѯÈç¹ûʹÓòÎÊý±ê¼ÇÄÇôµÃµ½µÄ·ÃÎʼƻ®¿ÉÄܲ»ÊÇ×îÓŵġ£¸ü¶àÐÅÏ¢Çë²Î¼û¡°ÔÚ¸´ÔÓ²éѯÖÐʹÓà REOPT °ó¶¨Ñ¡ÏîºÍÊäÈë±äÁ¿¡±¡£

ÉèÖà DB2_REDUCED_OPTIMIZATION ×¢²á±í±äÁ¿

Èç¹û¶ÔÄãµÄÓ¦ÓóÌÐòÉèÖõÄÓÅ»¯¼¶±ð²»Äܳä·ÖµÄ¼õÉÙ±àÒëʱ¼ä£¬ÄÇô¾Í³¢ÊÔÉèÖà DB2_REDUCED_OPTIMIZATION ×¢²á±äÁ¿¡£Õâ¸ö×¢²á±äÁ¿ÔÚÓÅ»¯Æ÷²éÕÒ¿Õ¼äÉϱÈÉèÖÃÓÅ»¯¼¶±ðÌṩÁ˸ü¶à¿ØÖÆ¡£Õâ¸ö×¢²á±äÁ¿ÈÃÄã¿ÉÒÔÇëÇóÔÚÖ¸¶¨µÄÓÅ»¯¼¶±ðÖмõÉÙÓÅ»¯¹¦ÄÜ»òÕßÑϸñʹÓÃÓÅ»¯¹¦ÄÜ¡£Èç¹ûÄã¼õÉÙÁËʹÓÃÓÅ»¯¼¼ÊõµÄÊýÄ¿£¬ÄãͬÑù¼õÉÙÁËʱ¼äºÍÓÅ»¯¹ý³ÌÖÐʹÓõÄ×ÊÔ´¡£

×¢Ò⣺ËäÈ»ÓÅ»¯Ê±¼äºÍ×ÊԴʹÓÿÉÄÜ»á¼õÉÙ£¬ÕâÒ²Ôö¼ÓÁ˲úÉúµÄ²éѯ¼Æ»®²»ÊÇ×îÓŵķçÏÕ¡£

Ê×ÏÈ£¬³¢ÊÔÉèÖÃ×¢²á±í±äÁ¿Îª YES ¡£Èç¹ûÓÅ»¯¼¶±ðÊÇ 5£¨Ä¬ÈÏÖµ£©»ò¸üµÍ£¬ÓÅ»¯Æ÷½«²»»áʹÓÃijЩÐèÒª»¨·Ñ´óÁ¿×¼±¸Ê±¼äºÍ×ÊÔ´µÄÓÅ»¯¼¼Êõ£¬µ«ÊÇͨ³£Ò²²»»á²úÉú¸üºÃµÄ²éѯ¼Æ»®¡£Èç¹ûÓÅ»¯¼¶±ðÊÇ 5£¬ÓÅ»¯Æ÷»á¼õÉÙ»òÈ¡ÏûһЩ¶îÍâµÄ¼¼Êõ£¬Õâ¿ÉÄܽøÒ»²½¼õÉÙÓÅ»¯Ê±¼äºÍʹÓõÄ×ÊÔ´£¬²»¹ýͬÑù½øÒ»²½Ôö¼ÓÁ˵õ½µÄ²éѯ¼Æ»®²»ÊÇ×îÓŵķçÏÕ¡£¶ÔÓÚµÍÓÚ 5 µÄÓÅ»¯¼¶±ð£¬ËüÃǵÄһЩ¼¼Êõ¿ÉÄÜÔÚÈκÎÇé¿ö϶¼ÎÞЧ¡£

Èç¹ûÉèÖà YES ûÄܳä·ÖËõ¶Ì±àÒëʱ¼ä£¬¿ÉÒÔ³¢ÊÔÉèÖÃÕâ¸ö×¢²á±äÁ¿ÎªÒ»¸öÊý×Ö¡£Ð§¹ûÊÇºÍ YES Ò»Ñù£¬¶ÔÓÚÔÚ¼¶±ð 5 ÉϵĶ¯Ì¬×¼±¸²éѯÓÅ»¯ÓкóÐøµÄ¸½¼ÓÐÐΪ¡£Èç¹ûÔÚÈκβéѯ¿éÖÐÁ¬½ÓµÄ×ÜÊýÄ¿³¬¹ýÁËÕâ¸öÉèÖã¬ÄÇôÓÅ»¯Æ÷¾ÍÇл»µ½Ò»¸ö̰À·Á¬½Óö¾ÙËã·¨¶ø²»ÊÇÈ¡Ïû¶îÍâµÄÓÅ»¯¼¼Êõ¡£ÕâÑùµÄЧ¹ûÊDzéѯ½«ÔÚÒ»¸öÀàËÆÓÅ»¯¼¶±ð 2 µÄ¼¶±ðÉϱ»ÓÅ»¯¡£

¶Ô SAP Ó¦ÓóÌÐòÉèÖà DB2_WORKLOAD ×¢²á±äÁ¿

¶ÔÓÚ SAP Ó¦ÓóÌÐò£¬×ÜÊÇ»áΪ SAP Éè DB2_WORKLOAD ×¢²á±äÁ¿¡£Õâ»á´¥·¢Ò»ÅúÆäËûµÄ¶Ô SAP Ó¦ÓóÌÐòÓÐÌØÊâºÃ´¦µÄ DB2 ×¢²á±í±äÁ¿ÉèÖá£ÏÂÃæÊÇÔÚÕâЩ±ãÀûÖÐºÍ DB2 ÓÅ»¯Æ÷Ïà¹ØµÄ×¢²á±äÁ¿ÉèÖÃ

DB2_MINIMIZE_LIST_PREFETCH=YES

DB2_INLIST_TO_NLJN=YES

DB2_ANTIJOIN=EXTEND

DB2_REDUCED_OPTIMIZATION=<specific heuristics for SAP workloads>

DB2_VIEW_REOPT_VALUES=YES

DB2_REDUCED_OPTIMIZATION µÄÉèÖÃÊÇרÃÅÕë¶Ô SAP ¹¤×÷¸ºÔصġ£²»ÒªÔÚÆäËûÓ¦ÓóÌÐòÏÂʹÓóý·ÇÊÇ DB2 ¼¼ÊõÖ§³ÖÍÆ¼öµÄ¡£

DB2_MINIMIZE_LIST_PREFETCH=YES ÈçûÓÐ×ã¹»¿ÉÓÃÐÅÏ¢À´ÅжÏÊÇ·ñÕâ¸ö·ÃÎÊ·½·¨¶Ô²éѯÓкô¦£¬½«»á×èÖ¹ÓÅ»¯Æ÷¿¼ÂÇÁгöԤȡ±í·ÃÎÊ·½·¨¡£Èç¹ûν´Ê°üº¬²ÎÊý±ê¼Ç£¬Õâ¿ÉÄܶÔʵÀý¾ÍÊÇÕâÖÖÇé¿ö¡£ÔÚ SAP Ó¦ÓóÌÐòÖУ¬²ÎÊý±ê¼ÇÔÚ´ó¶àÊýÓ¦ÓóÌÐòÖж¼ÓÐʹÓ㬳ýÁË SAP NetWeaver BIreport ²éѯ¡£

DB2_INLIST_TO_NLJN=YES ʹÓÅ»¯Æ÷Ö§³Ö°üº¬Áбíν´Ê IN µÄ²éѯʹÓÃǶÌ×Ñ­»·Á¬½Ó¡£ IN ÁÐ±í±»×ª»»½øÒ»¸ö±í²¢ÏñÍⲿ±íµÄ NLJN ºÍͨ¹ýË÷Òý·ÃÎʵÄÄÚ²¿±íʹÓá£ÔÚ SAP Ó¦ÓóÌÐòÖо­³£ÓÐ IN ÁбíµÄ²éѯ£¬ÀýÈ統ʹÓà SAP µÄ F4 °ïÖú¹¦ÄÜÀ´´ÓÒ»¸ö¿ÉÄܵÄÖµµÄÁбíÖÐÑ¡ÔñÒ»¸ö·¶Î§»òÒ»Åú²»Í¬µÄÖµ¡£

¼´Ê¹ÔÚÖ¸¶¨ÁË REOPT(ONCE) µÄÇé¿öÏ£¬DB2_MINIMIZE_LIST_PREFETCH=YES ºÍ DB2_INLIST_TO_NLJOIN=YES ½«ÒÀÈ»±£³Ö»î¶¯¡£

DB2_ANTIJOIN=EXTEND ÈÃÓÅ»¯Æ÷ȥѰÕÒ°Ñ NOT IN ºÍ NOT EXISTS ×Ó²éѯת»»³É anti-joins µÄ»ú»á¡£

DB2_VIEW_REOPT_VALUES=YES ÈÃËùÓÐ SAP Óû§È¥´æ´¢Ò»¸öµ±Óï¾ä±»½âÊ͵ÄʱºòÔÚ EXPLAIN_PREDICATE ±íÖÐÖØÐÂÓÅ»¯¹ýµÄ SQL Óï¾äµÄ»º´æµÄÖµ¡£ËùÓÐ SAP Óû§Ê¹ÓÃÏàͬµÄ¾ßÓÐËùÐèȨÏÞµÄÊý¾Ý¿âÁ¬½ÓÓû§¡£

ÊÕ¼¯ÕýÈ·µÄ±àÄ¿ÐÅÏ¢£¬°üÀ¨¸ß¼¶Í³¼Æ¹¦ÄÜ

¾«È·µÄÊý¾Ý¿âͳ¼ÆÐÅÏ¢ÊDzéѯÓÅ»¯µÄ¹Ø¼ü¡£ÔÚËùÓжԲéѯÐÔÄÜÀ´Ëµ·Ç³£¹Ø¼üµÄ±íÉÏÓйæÂɵÄÔËÐÐ RUNSTATS ¡£Èç¹ûÒ»¸öÓ¦ÓóÌÐòÖ±½Ó²éѯÕâЩ±í²¢ÇÒÓдóÁ¿µÄ¶¯Ì¬±àÄ¿¸üбÈÈç DDL Óï¾ä£¬Äã¿ÉÄÜҲϣÍûËѼ¯ÏµÍ³±àÄ¿±íµÄÐÅÏ¢¡£¿ÉÒÔÆôÓÃ×Ô¶¯ËѼ¯Í³¼ÆÐÅÏ¢¹¦ÄÜÀ´ÔÊÐí DB2 Êý¾Ý·þÎñÆ÷×Ô¶¯ÔËÐÐ RUNSTATS ¡£¿ÉÒÔÆôÓÃʵʱÊÕ¼¯Í³¼ÆÐÅÏ¢£¬Í¨¹ýÁ¢¿ÌÊÕ¼¯ÕâЩÐÅÏ¢À´Èà DB2 Êý¾Ý·þÎñÆ÷ÔÚÓÅ»¯²éѯ֮ǰÄÜÌṩ¸ü¼°Ê±µÄͳ¼ÆÐÅÏ¢¡£

Èç¹ûÊÖ¶¯ÔËÐÐ RUNSTATS À´ÊÕ¼¯Í³¼ÆÐÅÏ¢£¬ÄãÓ¦¸ÃÖÁÉÙʹÓÃÏÂÃæµÄÑ¡Ïî¡£

RUNSTATS ON TABLE DB2USER.DAILY_SALES WITH DISTRIBUTION AND SAMPLED DETAILED INDEXES ALL

·Ö·¢Í³¼ÆÐÅÏ¢¿ÉÒÔÈÃÓÅ»¯Æ÷ÖªµÀÊÇ·ñÓÐÊý¾ÝÇãб¡£µ±Ê¹ÓÃÌØ¶¨Ë÷Òý·ÃÎʱíʱ£¬ÏêϸµÄË÷Òýͳ¼ÆÐÅÏ¢Ìṩ¸ü¶àµÄ I/O ÐèÇóϸ½ÚÀ´Ô¤È¡Êý¾ÝÒ³¡£È»¶ø¶Ô´ó±íÊÕ¼¯ÏêϸµÄË÷Òýͳ¼ÆÐÅÏ¢»áÏûºÄºÜ¶àµÄ CPU ºÍÄÚ´æ¡£ SAMPLED Ñ¡ÏîÌṩÁËÏêϸË÷Òýͳ¼ÆÐÅÏ¢ºÍÏàͬµÄ¾«È·ÐÔ£¬È´Ö»ÐèҪһС²¿·Ö CPU ºÍÄÚ´æ¡£µ±²¢Ã»ÓжÔÒ»¸ö±íÌṩһ¸öͳ¼ÆÅäÖÃÎļþʱ£¬ÕâЩĬÈÏֵͬÑù»á±»×Ô¶¯ÊÕ¼¯Í³¼ÆÐÅϢʹÓá£

ΪÁËÌá¸ß²éѯÐÔÄÜ£¬¿¼ÂÇÊÕ¼¯¸ü¶à¸ü¸ß¼¶µÄͳ¼ÆÐÅÏ¢£¬±ÈÈçÁÐ×éµÄͳ¼ÆÐÅÏ¢£¬LIKE ͳ¼ÆÐÅÏ¢»ò´´½¨Í³¼ÆÐÅÏ¢ÊÓͼ¡£

ÁÐ×éͳ¼ÆÐÅÏ¢

Èç¹ûÄãµÄ²éѯ²»Ö¹Ò»¸öÁ¬½Óν´ÊÀ´Á¬½ÓÁ½¸ö±í£¬ÔÚÑ¡ÔñÒ»¸öÖ´Ðмƻ®À´ÔËÐвéѯ֮ǰ DB2 ÓÅ»¯Æ÷½«¼ÆËãÈçºÎÑ¡Ôñÿ¸öν´Ê¡£

ÀýÈ磬¿¼ÂÇÒÔ¹©³§ÉÌ£¬ËûÓÃÓкܶàÑÕÉ«µÄÔ­ÁÏÉú²ú²úÆ·£¬µ¯ÐÔºÍÆ·ÖÊ¡£²úÆ·×îºóÒ³ÊǺÍÔ­ÁÏÒ»ÑùµÄÑÕÉ«¡£Õâ¸ö³§ÉÌÖ´ÐÐÁËÒÔϲéѯ£º

SELECT PRODUCT.NAME, RAWMATERIAL.QUALITY
FROM PRODUCT, RAWMATERIAL
WHERE PRODUCT.COLOR = RAWMATERIAL.COLOR
AND PRODUCT.ELASTICITY = RAWMATERIAL.ELASTICITY

Õâ¸ö²éѯ·µ»ØÁËËùÓвúÆ·µÄÃû×ÖºÍÔ­²ÄÁÏÆ·ÖÊ¡£ÔÚÕâÀïÓÐÁ½¸öÁ¬½Óν´Ê£º

PRODUCT.COLOR = RAWMATERIAL.COLOR
PRODUCT.ELASTICITY = RAWMATERIAL.ELASTICITY

ÓÅ»¯Æ÷»á¼Ù¶¨ÕâÁ½¸öν´ÊÊǶÀÁ¢µÄ£¬Ò²¾ÍÊÇ˵ËùÓе¯ÐÔ¶ÔÑÕÉ«µÄ±ä»¯Ã»ÓйØÏµ¡£È»ºóͨ¹ý½¨Á¢Ã¿¸ö±í¹ØÓÚµ¯ÐԵȼ¶ÊýºÍ²»Í¬µÄÑÕÉ«ÊýÄ¿µÄ±àÄ¿ÐÅÏ¢ÆÀ¹Àν´ÊµÄ×ܵĿÉÑ¡×éºÏ£¬²¢»ùÓÚÕâ¸öÆÀ¹À¡£ÀýÈ磬±ÈÆðºÏ²¢Á¬½ÓËü¿ÉÄܸüÇãÏòÓÚÑ¡ÔñÒ»¸öǶÌ×Ñ­»·Á¬½Ó£¬·´Ö®ÒàÈ»¡£

È»¶ø£¬ÕâÁ½¸öν´Ê¿ÉÄܲ¢²»¶ÀÁ¢¡£ÀýÈç±È½Ï¸ßµÄµ¯ÐÔ²ÄÁÏ¿ÉÄÜÖ»Óм¸ÖÖÑÕÉ«£¬¶øÇÒµ¯ÐÔ²îµÄ²ÄÁÏÒ²¿ÉÄܲ»Í¬ÓÚµ¯ÐԺõIJÄÁÏÖ»ÓÐÊ£ÏÂµÄÆäËûÑÕÉ«¡£È»ºó×éºÏÕâЩν´ÊµÄÑ¡ÔñÏû³ýһЩÐУ¬ËùÒÔ²éѯ½«·µ»Ø¸ü¶àµÄÐС£Ã»ÓÐÕâЩÐÅÏ¢£¬ÓÅ»¯Æ÷Ò²Ðí²»»áÑ¡Ôñ×î¼ÑµÄ¼Æ»®¡£

ΪÁËÔÚ PRODUCT.COLOR ºÍ PRODUCT.ELASTICITY ÉÏÊÕ¼¯ÁÐ×éͳ¼ÆÐÅÏ¢ , ÔËÐÐÏÂÃæ RUNSTATS ÃüÁ

RUNSTATS ON TABLE product ON COLUMNS ((color, elasticity ))

ÓÅ»¯Æ÷ʹÓÃÕâЩͳ¼ÆÐÅÏ¢À´¼ì²âÏà¹ØÐÔ£¬²¢¶¯Ì¬µ÷ÕûÏà¹ØµÄν´ÊÑ¡Ïî×éºÏ£¬Òò´ËµÃµ½¶ÔÁ¬½ÓµÄ³ß¶ÈºÍ¿ªÏú¸ü¾«È·µÄÆÀ¹À¡£

µ±Ò»¸ö²éѯÐèÒªÊý¾ÝÒÔÒ»¸ö¾ßÌåµÄ·½Ê½£¨Ê¹Óà GROUP BY »ò DISTINCT ¹Ø¼ü×Ö£©±à×éʱÁÐ×éͳ¼ÆÐÅÏ¢Ò²»á·Ç³£ÓÐÓã¬ÒòΪÓÅ»¯Æ÷ÐèÒª¼ÆËãÃ÷È··Ö×éµÄÊýÄ¿¡£

¿¼ÂÇÏÂÃæ²éѯ£º

SELECT DEPTNO, YEARS, AVG(SALARY)
FROM EMPLOYEE
GROUP BY DEPTNO, MGR, YEAR_HIRED

ûÓÐÈκÎË÷Òý»òÁÐ×éͳ¼ÆÐÅÏ¢£¬ÓÅ»¯Æ÷ÆÀ¹À·Ö×éµÄÊýÄ¿£¨Ò²°üÀ¨ÔÚÕâÖÖÇé¿öÏ·µ»ØµÄÐÐÊýÄ¿£©ÊÇ DEPTNO¡¢MGR ºÍ YEAR_HIRED µÄ²»Í¬ÖµµÄ½á¹û¡£Õâ¸öÆÀ¹À¼ÙÉè·Ö×é¼üÁÐÊǶÀÁ¢µÄ¡£È»¶ø£¬Èç¹ûÿ¸ö¹ÜÀíÔ±¹ÜÀíÒ»¸öÈ·¶¨µÄ²¿ÃÅ£¬Õâ¸ö¼ÙÉè¾Í¿ÉÄÜÊÇ´íÎóµÄ¡£Í¬Ñù£¬²»´ó¿ÉÄÜÿ¸ö²¿ÃÅÿÄê¶¼¹ÍÓÃÐÂÔ±¹¤¡£Òò´Ë£¬²»Í¬ DEPTNO¡¢MGR ºÍ YEAR_HIRED ÖµµÃ½á¹û¿ÉÄÜÊǶԾßÌå×éµÄÊýÄ¿¹ý¸ßÆÀ¹À¡£

ÔÚ DEPTNO¡¢MGR ºÍ YEAR_HIRED ÉÏÊÕ¼¯µÄÁÐ×éͳ¼ÆÐÅÏ¢½«ÎªÉÏÃæµÄ²éѯÌṩ¸øÓÅ»¯Æ÷ºÍÓÐÈ·¶¨ÊýÄ¿µÄ·Ö×飺

RUNSTATS ON TABLE EMPLOYEE ON COLUMNS ((DEPTNO, MGR, YEAR_HIRED))

ΪÁË JOIN ν´Ê¹ØÏµ£¬ÓÅ»¯Æ÷Ò²¹ÜÀí¼òµ¥µÈ¼Ûν´Ê£¬±ÈÈç

DEPTNO = ¡± Sales ¡± AND MGR = ¡° John ¡±

ÔÚÉÏÃæµÄ EMPLOYEE ±í£¬ÔÚ DEPTNO ν´ÊÉϺܿÉÄÜÓëÔÚ YEAR ÉϵÄν´ÊºÁÎÞ¹ØÏµ¡£È»¶øÔÚ DEPTNO ºÍ MGR ÉϵÄν´ÊÈ´¿Ï¶¨Ïà¹Ø£¬ÒòΪÿ¸öµ¥¶ÀµÄ²¿ÃÅ¿ÉÄܾ­³£ÔÚÒ»¶Îʱ¼äÊܵ½Í¬Ò»¸ö¾­ÀíµÄ¹ÜÀí¡£ÓÅ»¯Æ÷ʹÓÃÁеÄͳ¼ÆÐÅÏ¢À´Åжϲ»Í¬Ö®µÄ×éºÏÊýÄ¿²¢¶ÔÕâÁ½ÁеĹØÏµµ÷ÕûÑ¡Ôñ»ò¼ÆÊýÆÀ¹À¡£

×ÓÔªËØÍ³¼Æ

Èç¹ûÄãÔÚģʽ½áβ֮ÍâµÄÈκÎλÖöÔÖ¸¶¨µÄ LIKE ν´ÊʹÓà % ͨÅä·û£¬Äã¶¼Ó¦¸ÃËѼ¯¹ØÓÚ×ÓÔªËØ½á¹¹µÄ»ù±¾ÐÅÏ¢¡£

ÒÔ¼°ÏòͨÅä·û LIKE ν´Ê£¨ÀýÈ磬SELECT .... FROM DOCUMENTS WHERE KEYWORDS LIKE '%simulation%'£©£¬ÕâÁÐÒÔ¼°²éѯ±ØÐëÂú×ãijЩ±ê×¼À´´Ó×ÓÔªËØÍ³¼ÆÐÅÏ¢ÖлñÀû¡£

±íÁÐÓ¦¸Ã°üÀ¨Óɿոñ·Ö¿ªµÄ×ÓÓò»òÕß×ÓÔªËØ¡£ÀýÈ磬һ¸öËÄÐÐµÄ DOCUMENTS ±í³öÓÚÎı¾¼ìË÷µÄÄ¿µÄÓÐÒ»¸ö KEWORDS ÁкÍһϵÁÐÏà¹ØµÄ¹Ø¼ü×Ö¡£ KEYWORDS µÄÖµÊÇ£º

'database simulation analytical business intelligence'
'simulation model fruit fly reproduction temperature'
'forestry spruce soil erosion rainfall'
'forest temperature soil precipitation fire'

ÔÚÕâ¸öÀý×ÓÖУ¬Ã¿ÁеÄÖµÓÐ 5 ¸ö×ÓÔªËØ×é³É£¬Ã¿¸ö¶¼ÊÇÒ»¸ö´Ê£¨¹Ø¼ü´Ê£©£¬Í¨¹ý¿Õ¸ñºÍÆäËû¹Ø¼ü´Ê·Ö¸ô¿ª¡£

²éѯӦ¸ÃÔÚ WHERE ×Ó¾äÖвο¼ÕâЩÁС£

ÓÅ»¯Æ÷»áÆÀ¹Àÿ¸öν´ÊÆ¥Åä¶àÉÙÐС£¶ÔÓÚÕâЩͨÅä·û LIKE ν´Ê£¬ÓÅ»¯Æ÷¼ÙÉè COLUMN Æ¥ÅäÁËһϵÁб˴ËÁ¬½ÓµÄÔªËØ£¬²¢ÇÒËü»ùÓÚ×Ö·û´®µÄ³¤¶ÈÀ´¹ÀËãÿ¸öÔªËØµÄ³¤¶È£¬³ýÁË % ¿ªÍ·»ò½áβµÄ×Ö·û¡£Èç¹ûÄãÊÕ¼¯×ÓÔªËØµÄͳ¼ÆÐÅÏ¢£¬ÓÅ»¯Æ÷½«ÓйØÓÚÿ¸ö×ÓÔªËØºÍ·Ö¸ô·ûµÄ³¤¶ÈÐÅÏ¢¡£Ëü¿ÉÒÔʹÓÃÕâЩ¶îÍâµÄÐÅÏ¢À´¸ü¾«È·µÄÆÀ¹À¿ÉÄÜÓжàÉÙÐÐÆ¥ÅäÕâ¸öν´Ê¡£

ΪÁËÁ¬½Ó×ÓÔªËØÍ³¼ÆÐÅÏ¢£¬ÔËÐÐÓÐ LIKE STATISTICS ×Ó¾äµÄ RUNSTATS ¡£

ͳ¼ÆÊÓͼ

DB2 »ùÓڳɱ¾µÄÓÅ»¯Æ÷°ÑÒ»¸ö»ùÓÚ·ÃÎʼƻ®´¦ÀíÆ÷´¦ÀíµÄÐÐÊý ¨C »ò»ùÊýµÄÆÀ¹À×÷ΪÕâ¸ö²Ù×÷µÄ¾«È·³É±¾¡£Õâ¸ö»ùÊýÆÀ¹ÀÊÇÓÅ»¯Æ÷³É±¾Î¨Ò»×îÖØÒªµÄÊäÈ룬¶øÇÒËüµÄ¾«È·¶ÈºÜ´ó³Ì¶ÈÉÏÈ¡¾öÓÚ RUNSTATS ʵÓù¤¾ß´ÓÊý¾Ý¿âÊÕ¼¯µÄͳ¼ÆÐÅÏ¢¡£¶ÔÓÚ¼ÆËãÒ»¸ö¾«È·µÄ»ùÊýÆÀ¹ÀÀ´ËµÉÏÃæÃèÊöµÄͳ¼ÆÐÅÏ¢ÊÇ×îÖØÒªµÄ£¬È»¶øÓÐһЩ»·¾³È´ÐèÒª·Ç³£³ÉÊìµÄͳ¼ÆÐÅÏ¢¡£ÓÈÆäÊÇÏëÒªÌåÏÖÔ½¸´ÔӵĹØÏµ¾Í¸üÐèÒª³ÉÊìµÄͳ¼ÆÐÅÏ¢£¬±ÈÈçÉæ¼°±È½ÏµÄÃèÊö£¨ÀýÈ磬price > MSRP + Dealer_markup£©£¬¹ØÏµ¿çÁ˶à¸ö±í£¨ÀýÈ磬product.name = 'Alloy wheels' and product.key = sales.product_key£©£¬»òÕ߯äËûν´Ê³ýÁËÉæ¼°¶ÀÁ¢ÊôÐÔÒÔ¼°¼òµ¥±È½Ï²Ù×÷µÄν´Ê¡£Í³¼ÆÊÓͼ¿ÉÒÔ·ÀÖ¹ÕâЩ¸´ÔÓ¹ØÏµÀàÐÍ£¬ÒòΪͳ¼ÆÐÅÏ¢ÊÇÔÚÕâ¸öÊÓͼ·µ»ØÖµµÄ»ù´¡ÉÏÊÕ¼¯µÄ£¬¶ø²»ÊÇ´ÓÕâ¸öÊÓͼÏà¹ØµÄ»ù´¡±íÉÏ¡£

µ±Ò»¸ö²éѯ±»±àÒëʱ£¬ÓÅ»¯Æ÷°ÑÕâ¸ö²éѯºÍ¿ÉÓõÄͳ¼ÆÊÓͼ½øÐÐÆ¥Åä¡£µ±ÓÅ»¯Æ÷¼ÆËã»ùÊý¹ÀËãÖмä½á¹û¼¯Ê±£¬ËüʹÓÃÀ´×ÔÊÓͼµÄͳ¼ÆÐÅÏ¢À´¼ÆËãÒ»¸ö¸üºÃµÄÆÀ¹À¡£

²éѯ²»±ØÒòΪÓÅ»¯Æ÷ʹÓÃÊÓͼ¾ÍÖ±½Ó²Î¿¼Í³¼ÆÊÓͼ¡£ÓÅ»¯Æ÷¿ÉÒÔʹÓúÍÎﻯ²éѯ±í£¨MQTs£©ÏàͬµÄÆ¥Åä»úÖÆÀ´Æ¥Å䵽ͳ¼ÆÊÓͼµÄ²éѯ¡£³öÓÚÕâ¸ö¿¼ÂÇ£¬³ýÁËËûÃDz»±»ÓÀ¾Ã´¢´æ£¬Í³¼ÆÊÓͼºÍ MQTs ·Ç³£ÏàËÆ£¬Òò´ËËüÃÇÒ²²»ÏûºÄ´ÅÅ̿ռäÒ²²»ÐèҪά»¤¡£

Ò»¸öͳ¼ÆÊÓͼÔÚµÚÒ»´Î´´½¨Ò»¸öÊÓͼ²¢Ê¹Óà ALTER VIEW Óï¾äÔÊÐíÓÅ»¯¡£È»ºóÔÚÕâ¸öͳ¼ÆÊÓͼÉÏÔËÐÐ RUNSTATS£¬ÓÃÕâ¸öÊÓͼµÄͳ¼ÆÐÅÏ¢ÌîÈëϵͳ±àÄ¿±íÖС£ÀýÈçΪÁË´´½¨Ò»¸öͳ¼ÆÊÓͼÀ´±íÏÖÔÚʱ¼äÔÚÒ»¸öÐÇÐÍģʽÖÐά¶È±íºÍʵʩ±íÖ®¼äµÄÁ¬½Ó£¬Ö´ÐÐÏÂÃæÓï¾ä£º

CREATE VIEW SV_TIME_FACT AS (
SELECT T.* FROM TIME T, SALES S
WHERE T.TIME_KEY = S.TIME_KEY)
ALTER VIEW SV_TIME_FACT ENABLE QUERY OPTIMIZATION
RUNSTATS ON TABLE DB2DBA.SV_TIME_FACT WITH DISTRIBUTION

ͳ¼ÆÊÓͼ¿ÉÒÔ±»ÓÃÓÚ¶Ô²éѯÌá¸ß»ùÊýÆÀ¹À£¬ÒÔ¼°·ÃÎʼƻ®ºÍ²éѯÐÔÄÜ£¬ÀýÈ磺

SELECT SUM(S.PRICE)
FROM SALES S, TIME T, PRODUCT P
WHERE
T.TIME_KEY = S.TIME_KEY AND T.YEAR_MON = 200712 AND
P.PROD_KEY = S.PROD_KEY AND P.PROD_DESC = ¡® Power drill ¡¯

ûÓÐͳ¼ÆÊÓͼ£¬ÓÅ»¯Æ÷¼ÙÉèËùÓÐÊÂʵ±í TIME_KEY µÄÖµ¶ÔÓ¦Ò»¸öÌØ¶¨µÄʱ¼äά¶È YEAR_MON ÖµÔÚÊÂʵ±íÖÐÊÇͳһµÄ¡£È»¶ø¿ÉÄÜÏúÊÛÔÚ 12 ÔÂÌØ±ðÇ¿¾¢£¬²úÉú±ÈÆäËûÔ·ÝÇ¿¾¢¶àºÜ¶àµÄ½»Òס£

ÔںܶàÇé¿öÏ£¬Í³¼ÆÊÓͼ¿ÉÒÔÌá¸ß²éѯÐÔÄÜ£¬²¢ÇÒÓÐһЩ¼òµ¥µÄ×î¼Ñʵ¼ù¿ÉÒÔ°ïÖúÅжÏÐèÒª´´½¨ÄÄЩͳ¼ÆÊÓͼ¡£Çë²Î¿¼¡°¸ü¶àÔĶÁ¡±Õ½ڡ£

×Ô¶¯ÊÕ¼¯Í³¼ÆÐÅÏ¢µ±Ç°²»ÄÜÓÃÓÚͳ¼ÆÊÓͼ¡£ÎÞÂÛʲôʱºòͳ¼ÆÊÓͼµÄ»ù´¡±íÊý¾ÝÓÐÁËÏÔÖøµÄ¸Ä¶¯¶¼ÒªÊÕ¼¯Í³¼ÆÊÓͼµÄͳ¼ÆÐÅÏ¢¡£

×îС»¯ RUNSTAS µÄÓ°Ïì

½øÒ»²½¸ÄÉÆ RUNSTATS µÄʱ»ú£º

ͨ¹ý COLUMNS ×Ó¾äÀ´ÏÞÖÆÒªÊÕ¼¯Í³¼ÆÐÅÏ¢µÄÁС£ÔÚ²éѯ¹¤×÷¸ºÔØÖо­³£»áÓÐһЩÁÐÓÀÔ¶¶¼²»»á±»Î½´ÊÉæ¼°µ½£¬Òò´ËËüÃDz»ÐèÒª±»Í³¼Æ¡£

Èç¹ûÇãÏòÓÚÒ»ÖµÄÊý¾Ý¾ÍÏÞÖÆËѼ¯·Ö²¼Í³¼ÆÐÅÏ¢µÄÁÐÊý¡£ÊÕ¼¯·Ö²¼Í³¼ÆÐÅÏ¢ÐèÒª±ÈÊÕ¼¯»ù´¡ÁÐͳ¼Æ»¨·Ñ¸ü¶àµÄ CPU ºÍÄÚ´æ¡£²»¹ý£¬ÒªÅжÏÒ»ÁеÄÖµÊÇ·ñÏàͬÐèÒªÏÖÓеÄͳ¼ÆÐÅÏ¢»òÕß¶ÔÊý¾Ý½øÐвéѯ¡£ÒòΪ±í»á·¢Éú¸Ä±ä£¬Õâ¸ö·½·¨Í¬Ñù»ùÓÚÊý¾Ý½«±£³ÖÒ»ÖµļÙÉè¡£

ͨ¹ýÖÆ¶¨ TABLESAMPLE SYSTEM »ò BERNOULLI ×Ó¾äʹÓÃÒ³Ãæ»òÐм¶±ðµÄÈ¡ÑùÀ´ÏÔʾ´¦ÀíµÄÐÐÊýºÍÒ³ÃæµÄÊýÄ¿¡£´Ó 10% ¼¶±ðµÄÑù±¾¿ªÊ¼ÀýÈç TABLESAMPLE SYSTEM(10) ¡£¼ì²éͳ¼ÆÐÅÏ¢µÄ¾«È·ÐÔÒÔ¼°ÏµÍ³ÐÔÄÜÊÇ·ñÒòΪ¸ü¸Ä·ÃÎʼƻ®¶øÓÐËùϽµ¡£Èç¹ûÊǵϰ£¬¾Í³¢ÊÔÓà 10% Ðм¶±ðµÄÑù±¾À´Ìæ»»£¬±ÈÈç TABLESAMPLE BERNOULLI(10) ¡£Èç¹ûͳ¼ÆÐÅÏ¢¾«¶È²»¹»£¬ÔòÔö¼ÓÈ¡ÑùÊýÁ¿¡£µ±Ê¹Óà RUNSTATS Ò³Ãæ»òÐм¶±ðµÄÈ¡Ñù£¬¶Ô½øÐÐÁ¬½ÓµÄ±íʹÓÃÏàͬÑù±¾ÂÊ¡£ÕâºÍÈ·±£Á¬½ÓÁÐͳ¼ÆÐÅÏ¢ÓÐÏàͬ¼¶±ðµÄ¾«¶È¡£

¶ÔÒ»¸öÅäÖÃÁËÊý¾Ý¿â·ÖÇø¹¦ÄÜ£¨DPF£©µÄÊý¾Ý·þÎñÆ÷£¬RUNSTATS ´Óµ¥¸öÊý¾Ý¿â·ÖÇøËѼ¯Í³¼ÆÐÅÏ¢¡£Èç¹û RUNSTATS ÔËÐÐÔÚ±íËùÔÚµÄÕâ¸öÊý¾Ý¿â·ÖÇøÉÏ£¬Í³¼ÆÐÅÏ¢½«ÔÚÕâÀïÊÕ¼¯¡£Èç¹û²»ÊÇ£¬Í³¼ÆÐÅÏ¢½«´ÓµÚÕâ¸ö±íËùÔÚ·ÖÇø×éµÄÒ»¸öÊý¾Ý¿â·ÖÇøÉÏÊÕ¼¯¡£ÎªÁ˱£³ÖÒ»ÖµÄͳ¼ÆÐÅÏ¢£¬¾ÍҪȷ±£½øÐÐÁ¬½ÓµÄ±íµÄͳ¼ÆÐÅÏ¢´ÓÏàͬµÄÊý¾Ý¿â·ÖÇøËѼ¯¡£

±ÜÃâÊÖ¶¯¸üбàĿͳ¼ÆÐÅÏ¢

DB2 Êý¾Ý·þÎñÆ÷Ö§³Öͨ¹ý¶Ô SYSSTAT ģʽϵÄÊÓͼ·¢Æð UPDATE Óï¾äÊÖ¶¯À´¸üбàĿͳ¼ÆÐÅÏ¢¡£Õâ¸ö¹¦ÄܶÔÔÚ²âÊÔϵͳÖÐÄ£ÄâÒ»¸öÉú²úÊý¾Ý¿âÀ´¼ì²é²éѯ¼Æ»®£¬»òÐí·Ç³£ÓÐÓá£ÎªÁËÔÚÆäËûϵͳÉÏÖØ·Å£¬db2look ¹¤¾ß¶Ôץȡ DDL ºÍ UPDATE SYSSTAT Óï¾ä·½ÃæºÜÓаïÖú¡£

È»¶ø£¬Òª±ÜÃâÊÖ¶¯¸üÐÂͳ¼ÆÐÅÏ¢ - Òâζ×ÅΪÁËÇ¿ÖÆÖ´ÐÐÒ»¸öÌØ¶¨µÄ²éѯ¼Æ»®Í¨¹ýÌṩ²»ÕýÈ·µÄͳ¼ÆÐÅÏ¢Ó°Ïì²éѯÓÅ»¯Æ÷¡£Õâ¸öʵ¼ù¿ÉÄÜÔì³ÉijЩ²éѯµÄÐÔÄÜÌáÉý£¬Ò²¿ÉÄܵ¼ÖÂÆäËûµÄÐÔÄÜϽµ¡£ÔÚ×îÖÕʹÓÃÕâ¸ö°ì·¨Ö®Ç°£¬Ç뿼ÂÇÆäËü֮ǰ¿¼ÂǹýµÄµ÷ÓÅ·½·¨¡£Èç¹û²»µÃ²»Ê¹ÓÃÕâ¸ö·½·¨£¬ÔÚÕâÖÖÇé¿öÏÂÒ»¶¨Òª¼Ç¼ԭʼµÄͳ¼ÆÐÅÏ¢£¬Èç¹û¸üÐÂͳ¼ÆÐÅÏ¢Ôì³ÉÁËÐÔÄÜϽµ¾ÍÐèÒª»Ö¸´ËüÃÇ¡£

¶Ô SAP Ó¦ÓóÌÐò£¬Ê¹ÓÃ×Ô¶¯Í³¼ÆÐÅÏ¢ÊÕ¼¯

ÔÚ SAP °²×°¹ý³ÌÖУ¬×Ô¶¯ºÍʵʩͳ¼ÆÐÅÏ¢ÊÇĬÈÏ´ò¿ªµÄ¡£Õâ¾ÍÊÇ˵Äã²»ÐèÒªÊÖ¶¯·¢Æð RUNSTATS ¡£

ºÜ¶à SAP ʹÓÃÇé¿öÊÇÔÚµÚÒ»²½ÒÔ¼°Í¨¹ýÒ»¸ö¸´Ô SAP Îı¾²éѯÀ´·ÃÎÊÐ޸ıíÄÚÈÝ¡£²»Òª¸Ä±äÏÂÃæÌáµ½µÄĬÈÏÉèÖà :

Automatic maintenance (AUTO_MAINT) = ON
Automatic table maintenance (AUTO_TBL_MAINT) = ON
Automatic runstats (AUTO_RUNSTATS) = ON
Automatic statement statistics (AUTO_STMT_STATS) = ON
Automatic statistics profiling (AUTO_STATS_PROF) = OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF

ʹÓÃÓв»¾ùÔÈÊý¾ÝµÄ SAP BI ±íµÄͳ¼ÆÊÓͼ

ÔÚ SAP BI snowflake ģʽµÄ±íÖв»¾ùÔȵķֲ¼Êý¾Ý¿ÉÄܵ¼Ö²¢²»ÓÅ»¯µÄ²éѯ·ÃÎʼƻ®²¢Ôö¼Ó²éѯʱ¼ä¡£´ó¶àÊýÕâÑùµÄÎÊÌâÊÇͨ¹ýÉèÖà SAP ¹¤×÷¸ºÔرäÁ¿ =SAP À´½â¾öµÄ¡£µ«ÊÇÔÚһЩÀýÍâµÄÇé¿öÏ£¬µ±ºÜ¶à±í»¥ÏàÁ¬½Óʱ£¬Í³¼ÆÊÓͼ¿ÉÒÔ±»´´½¨À´Îª DB2 ÓÅ»¯Æ÷Ìṩ¸ü¶à¹ØÓÚÖµ·Ö²¼µÄÐÅÏ¢¡£

ÏÂÃæµÄÀý×ÓÖУ¬ÊÂʵ±íºÍ¿Í»§¡¢ÇëÇó¡¢Ê±¼äÒÔ¼°²úƷά¶È±í½øÐÐÁ¬½Ó¡£

´ó¶àÊý½»Ò×ÊǸú Customer #10200 Íê³ÉµÄ¡£Òò´Ë£¬ÊÂʵ±íµÄ¾ø´ó¶àÊýÐÐÔÚ Dim C ÁеÄÖµÊÇ 2 ¡£ÕâÔì³ÉÁËÔÚÊÂʵ±íµÄ Dim C ÁÐÉϵIJ»¾ùÔȵÄÊý¾Ý·Ö²¼¡£

DB2 ÓÅ»¯Æ÷¿ÉÄÜ»áÔÚ¶ÔÔÚ¿Í»§Î¬¶ÈÓÐÔ¼ÊøµÄ²éѯʱºò¹ÀËã³ö´íÎóµÄ»ùÊý

¿ÉÒÔÔÚά¶ÈºÍÊÂʵ±íÉ϶¨Òåͳ¼ÆÊÓͼÉú³É¶îÍâµÄͳ¼ÆÐÅÏ¢£¬DB2 ÓÅ»¯Æ÷ÀûÓÃÕâЩÐÅÏ¢À´ÔöÇ¿»ùÊýÆÀ¹À²¢¼õÉÙ²éѯÔËÐÐʱ¼ä¡£

ÔÚÉÏÃæµÄÀý×ÓÖУ¬ÏÂÃæÔÚ¿Í»§ºÍÊÂʵ±íά¶ÈÉϵÄͳ¼ÆÊÓͼ¿ÉÄܽâ¾ö´íÎó»ùÊýͳ¼ÆÎÊÌâ²¢¼õÉÙ²éѯÏìӦʱ¼ä£º

CREATE VIEW stat_view as
( SELECT cust .c, cust.customer#, cust.region, f.dimC
FROM customer_ ά¶È cust,
fact_table fact
WHERE cust.c = fact.dimC )

Èç¹ûÕâ¸öͳ¼ÆÊÓͼµÄÒ»¸ö»ù±¾±í·¢ÉúÁ˸͝£¬¾ÍÐèÒªÔÚͳ¼ÆÊÓͼÉϸüÐÂÊý¾Ý¿âͳ¼ÆÐÅÏ¢¡£Í³¼ÆÊÓͼ²»Ö§³Ö DB2 ×Ô¶¯Í³¼ÆÐÅÏ¢ËѼ¯¡£

ʹÓà SAP BI ¾Û¼¯

SAP BI ʵʩÁËËüÃÇ×Ô¼ºµÄÎﻯ²éѯ±í£¬½Ð×ö SAP BI ¾Û¼¯¡£Ê¹Óà SAP ¾Û¼¯ÔÚ SAP BI Ó¦ÓóÌÐòÖÐÌæ´úÒ»°ãµÄ DB2 Îﻯ²éѯ±í¡£

Èç¹ûÆäËûµ÷ÓÅÑ¡Ïî²»ÄܲúÉú¿ÉÒÔ½ÓÊܵÄЧ¹ûÄÇôʹÓÃÓÅ»¯ÅäÖÃÎļþ

Èç¹ûÄãÒѾ­×ñÑ­Á˱¾ÎÄÍÆ¼öµÄ×î¼Ñʵ¼ù£¬¶øÄãÏàÐÅÈÔȻûÓдﵽ×îÓŵÄÐÔÄܵϰ£¬Äã¾Í¿ÉÒÔÏò DB2 ÓÅ»¯Æ÷Ìṩһ¸öÃ÷È·µÄÓÅ»¯Ö¸ÄÏ¡£

ÓÅ»¯Ö¸Äϰüº¬ÔÚÒ»¸ö½Ð×öÓÅ»¯ÅäÖÃÎļþµÄ XML ÎĵµÖС£ÅäÖÃÎļþ¶¨Òå SQL Óï¾äºÍËûÃÇÏà¹ØµÄÓÅ»¯Ö¸ÄÏ¡£

Èç¹ûÄã¹ã·ºµÄʹÓÃÁËÓÅ»¯ÅäÖÃÎļþ£¬ËüÃÇÐèÒªÄ㸶³öºÜ¶àµÄŬÁ¦À´Î¬»¤¡£¸üÖØÒªµÄÊÇ£¬ÄãÖ»ÄÜʹÓÃÓÅ»¯ÅäÖÃÎļþÀ´¶ÔÏÖÓÐ SQL Óï¾äÌá¸ßÐÔÄÜ¡£ÏÂÃæµÄ×î¼Ñʵ¼ù¶ÔËùÓеIJéѯ³ÖÐøÎȶ¨µÄÌá¸ß²éѯÐÔÄÜ£¬°üÀ¨Î´À´µÄijЩ²éѯ¡£

ʹÓýâÊ͹¤¾ßÀ´µ÷ÓÅ SQL Óï¾ä

½âÊ͹¤¾ßÊÇÓÃÀ´ÏÔʾ±»²éѯÓÅ»¯Æ÷ÓÃÀ´ÔËÐÐÒ»¸ö SQL Óï¾äµÄ²éѯ·ÃÎʼƻ®¡£Ëü°üº¬ÁËÓÃÓÚÔËÐÐ SQL Óï¾ä¹ØÓÚÏà¹Ø²Ù×÷·Ç³£¹ã·ºµÄÐÅÏ¢£¬±ÈÈç¼Æ»®²Ù×÷È¥¡¢ËüÃÇµÄ arguments¡¢Ö´ÐÐ˳ÐòºÍ³É±¾¡£ÒòΪ²éѯ·ÃÎʼƻ®ÊDzéѯÐÔÄÜÖÐ×îÖØÒªµÄÒòËØÖ®Ò»£¬ÎªÁËÄÜÕï¶Ï²éѯÐÔÄÜÎÊÌ⣬Äܹ»Àí½â½âÊ͹¤¾ßµÄÊä³ö·Ç³£ÖØÒª¡£

½âÊÍÐÅϢͨ³£ÓÃÓÚ£º

Àí½âΪʲôӦÓóÌÐòÐÔÄÜ·¢ÉúÁ˱仯

ÆÀ¹ÀÐÔÄܵ÷ÓŵÄЧ¹û

·ÖÎöÐÔÄܱ仯

ΪÁ˰ïÖúÄãÀí½â²éѯÐÔÄܱ仯µÄÔ­Òò£¬ÐèÒªµ÷ÓÅǰºóµÄ½âÊÍÐÅÏ¢£¬Äã¿ÉÒÔͨ¹ýÖ´ÐÐÏÂÃæ²½ÖèµÃµ½ËüÃÇ£º

ÔÚÄã×öÈκθü¸Ä֮ǰ£¬×¥È¡²éѯµÄ½âÊÍÐÅÏ¢²¢±£´æ½âÊÍ±í¡£ÁíÍ⣬Ҳ¿ÉÒÔ±£´æ db2exfmt ½âÊ͹¤¾ßµÄÊä³ö¡£È»¶ø£¬ÎªÁ˸ü³ÉÊìµÄ·ÖÎö£¬°Ñ½âÊÍÐÅÏ¢±£´æÔÚ½âÊͱíÖпÉÒÔ¸ü·½±ãÓà SQL ²éѯ£¬ÓÖÌṩÁ˰ÑÊý¾Ý±£´æÔÚ¹ØÏµÐÍ DBMS ÖÐÔÚά»¤ÉÏÃ÷ÏÔµÄÓÅÊÆ¡£´ËÍâ db2exfmt ¹¤¾ß¿ÉÒÔÔÚÈκÎʱ¼äÔËÐС£

Èç¹ûÄã²»Ïë¡¢»ò²»ÄÜ·ÃÎÊ Visual Explain À´²é¿´ÕâЩÐÅÏ¢£¬¾Í±£´æ»ò´òÓ¡µ±Ç°±àĿͳ¼ÆÐÅÏ¢¡£ÄãÒ²¿ÉÒÔʹÓà db2look Éú²úÁ¦¹¤¾ßÀ´°ïÖúÖ´ÐÐÕâ¸öÈÎÎñ¡£ÁíÍ⣬Èç¹ûÊÇÄãÓà DB2 9.5£¬ÔÚÓï¾ä±»½âÊ͵ÄͬʱËѼ¯½âÊÍ¿ìÕÕ¡£ Db2exfmt ¹¤¾ß½«×Ô¶¯¸ñʽ»¯°üº¬ÔÚ¿ìÕÕÖеÄÐÅÏ¢¡£ÕâÔÚʹÓÃ×Ô¶¯»òÊÇͳ¼ÆÐÅÏ¢ÊÕ¼¯µÄʱºòÓÈÆäÖØÒª£¬ÒòΪ²éѯÓÅ»¯Æ÷ʹÓõÄͳ¼ÆÐÅÏ¢¿ÉÄÜ»¹Ã»ÓдæÈëϵͳ±àÄ¿±íÖУ¬»òÕßËûÃÇ¿ÉÄÜÔÚÓï¾ä´Óϵͳ±àÄ¿±íÖлñÈ¡ÐÅÏ¢µ½±»½âÊ͵Ĺý³ÌÖб»¸ü¸ÄÁË¡£

±£´æ»ò´òÓ¡Êý¾Ý¶¨ÒåÓïÑÔ£¨DDL£©Óï¾ä£¬°üÀ¨ÕâÄÇЩ CREATE TABLE¡¢CREATE VIEW¡¢CREATE INDEX¡¢CREATE TABLESPACE ¡£ Db2look ͬÑù¿ÉÒÔÖ´ÐÐÕâ¸öÈÎÎñ¡£

ͨ¹ýÕâ¸ö·½·¨ÊÕ¼¯µÄÐÅÏ¢£¬Îª½«À´µÄ·ÖÎöÌṩÁËÒ»¸ö²Î¿¼µã¡£¶Ô¶¯Ì¬ SQL Óï¾äÀ´Ëµ£¬µ±ÄãµÚÒ»´ÎÔËÐÐÄãµÄÓ¦ÓóÌÐòʱ£¬Äã¿ÉÒÔËѼ¯Õâ¸öÐÅÏ¢¡£¶ÔÓÚ¾²Ì¬Óï¾ä£¬Ò²¿ÉÒÔÔڰ󶨵ÄʱºòËѼ¯Õâ¸öÐÅÏ¢¡£ÔÚÒ»¸öÖ÷Ҫϵͳ¸ü¸Ä֮ǰËѼ¯Õâ¸öÐÅÏ¢·Ç³£ÖØÒª£¬±ÈÈç°²×°Ò»¸öеķþÎñ¼¶±ð»ò DB2 °æ±¾»òÕßÒ»¸öºÜ´óµÄÅäָ͝£¬±ÈÈçÔö¼Ó»òɾ³ýÊý¾Ý¿â·ÖÇøºÍ·Ö²¼Êý¾Ý¡£ÕâÊÇÒòΪÕâÀàϵͳ¸ü¸Ä¿ÉÄÜÔì³É·ÃÎʼƻ®µÄ²»Àû¸ü¸Ä¡£ËäÈ»·ÃÎʼƻ®Í˲½Ó¦¸ÃºÜÉÙ·¢Éú£¬µ«ÊÇÓÐÕâЩ¿ÉÓÃÐÅÏ¢½«ÔÊÐí¸ü¿ìµÄÄã½â¾öÐÔÄÜÍ˲½µÄÎÊÌâ¡£Òª·ÖÎöÒ»¸öÐÔÄܱ仯£¬°Ñ֮ǰµÄÐÅÏ¢ºÍÏÖÔÚÄ㿪ʼ·ÖÎöµÄʱºòÊÕ¼¯µ½µÄ¹ØÓÚ²éѯºÍ»·¾³µÄÐÅÏ¢½øÐбȽϡ£

Ò»¸ö¼òµ¥µÄÀý×Ó£¬ÄãµÄ·ÖÎö¿ÉÄÜÏÔʾË÷Òý²»ÔÙ×÷Ϊ·ÃÎʼƻ®µÄÒ»²¿·Ö±»Óõ½¡£Ê¹Óà Visual Explain »ò db2exfmt ÏÔʾµÄ±àĿͳ¼ÆÐÅÏ¢£¬Äã¿ÉÄÜ×¢Òâµ½ index ¼¶±ðÊýÔ¶Ô¶¸ßÓÚ²éѯµÚÒ»´Î°ó¶¨µ½Êý¾Ý¿âµÄʱºòµÄÖµ¡£È»ºóÄã¿ÉÒÔÑ¡ÔñÖ´ÐÐÏÂÃæµÄij¸ö²Ù×÷£º

ÖØ×éË÷Òý

ΪÄãµÄ±íºÍË÷ÒýËѼ¯ÐµÄͳ¼ÆÐÅÏ¢

ÔÚÖØÐ°󶨵ÄʱºòËѼ¯½âÊÍÐÅÏ¢¡£

ÔÚÄãÖ´ÐÐÆäÖÐij¸ö²Ù×÷Ö®ºó£¬ÔÙ¼ì²éһϲéѯ¼Æ»®¡£Èç¹ûË÷ÒýÔÙÒ»´Î±»Ê¹ÓÃÁË£¬Õâ¸ö²éѯµÄÐÔÄÜ¿ÉÄܲ»ÔÙÊǸöÎÊÌâ¡£ÖØ¸´ÕâЩ²½ÖèÖ±µ½ÎÊÌâ±»½â¾ö¡£

ÆÀ¹ÀÐÔÄܵ÷ÕûЧ¹û

Äã¿ÉÒÔ½øÐÐһϵÁеIJÙ×÷À´°ïÖúÌá¸ß²éѯÐÔÄÜ£¬±ÈÈçУ¶ÔÅäÖòÎÊý¡¢Ìí¼ÓÈÝÆ÷¡¢ºÍËѼ¯Ë¢Ð±àĿͳ¼ÆÐÅÏ¢¡£

ÔÚÄãÕâЩ·½Ãæ½øÐÐÁ˸ü¸Ä£¬Èç¹ûÔÚ±»·ÃÎʼƻ®Ñ¡Ôñµ½µÄ·½ÃæÓиü¸ÄµÄ»°£¬Äã¿ÉÒÔʹÓýâÊ͹¤¾ßÀ´ÅжÏÓ°Ïì¡£ÀýÈ磬Èç¹ûÄã»ùÓÚË÷ÒýÖ¸ÄÏÌí¼ÓÒ»¸öË÷Òý»òÎﻯ²éѯ±í£¨MQT£©£¬½âÊÍÊý¾Ý¿ÉÒÔ°ïÖúÄãÅжÏÊÇ·ñË÷Òý»òÎﻯ²éѯ±í×îÖÕÈçÄãËùÆÚÍûµÄ±»Óõ½ÁË¡£

ËäÈ»½âÊÍÊä³öÌṩÁËÈÃÄãÅжÏÑ¡ÖеķÃÎʼƻ®µÄÐÅÏ¢ºÍ³É±¾£¬¶ÔÒ»¸ö²éѯÀ´Ëµ¾«È·²âÁ¿ÐÔÄÜÌá¸ßµÄΨһ·½·¨ÊÇʹÓûù×¼µÄÊǼ¼Êõ¡£

ÔÚ SAP Ó¦ÓóÌÐòÖÐÌá¸ß SQL ²éѯÐÔÄÜ

SAP ÌṩÁËÏÂÃæ½Ó¿ÚºÍÊý¾Ý¿âͨѶ£º

Ó¦ÓóÌÐòдÈë ABAP

SAP Open SQL

×ÔÈ» SQL ͨ¹ý ADBC ½Ó¿Ú£¨ABAP Êý¾Ý¿âÁ¬½Ó£©

×ÔÈ» SQL£¬ÒÔ EXEC SQL Óï¾ä¿ªÊ¼²¢ÒÔ ENDEXEC Óï¾ä½áÊø

JDBC ÓÃÓÚ SAP Java Ó¦ÓóÌÐò

SAP ABAP Ó¦ÓóÌÐòÖ÷ҪʹÓà SAP Open SQL À´·ÃÎÊÊý¾Ý¿â¡£ SAP Open SQL ÓÉÒ»×éÖ´ÐÐÊý¾Ý¿â²Ù×÷µÄ ABAP Óï¾ä×é³É¡£±»¿ª·¢À´Îª SAP Ö§³ÖµÄËùÓйØÏµÊý¾Ý¿âƽ̨Ìṩһ¸öͨÓýӿڡ£ SAP Open SQL Óï¾äÔÚ SAP Êý¾Ý¿â½Ó¿ÚÖб»×ª»»Îª×ÔÈ» SQL ¡£

SAP NetWeaver BI ʹÓà SAP BI ·¢²¼µÄ ADBC ¡£¶ÔÓÚ×ÔÈ» SQL ½Ó¿Ú ADBC ÊÇÒ»¸ö»ùÓÚÀàµÄ API£¬ËüÖ§³ÖÖ´Ðж¯Ì¬Éú³É SQL Óï¾ä¡£ÔÚ ABAP »·¾³ÖÐʹÓà ADBC ºÍÔÚ Java »·¾³ÖÐʹÓà JDBC ÀàËÆ¡£

Óû§ËûÃÇ×Ô¼ºÐ´µÄ report ºÍÊÂÎñ¿ÉÒÔʹÓÃÈκÎÒ»¸ö¿ÉÓýӿÚÀ´·ÃÎÊÊý¾Ý¿â¡£

ÔÚ SAP Open SQL ÖÐʹÓà DB2 ²ÎÊý

SAP Ö§³ÖÒ»Åú DB2 ²ÎÊý£¬ËüÃÇ¿ÉÒÔÌí¼Óµ½ Open SQL Óï¾äÖÐÒÔÓ°Ïì´Ó Open SQL Éú³ÉµÄ×ÔÈ» SQL Óï¾äÈçºÎÔÚÊý¾Ý¿â²ã±àÒëºÍÔËÐС£¿Í»§¿ÉÒÔÔÚËûÃÇ×Ô¼ºÐ´µÄ report ºÍÊÂÎñÖÐʹÓÃÕâЩ DB2 ²ÎÊýÀ´Ìá¸ß SQL ²éѯÐÔÄÜ¡£

×¢Ò⣬¶ÔÇ°ÃæÃèÊöµÄ DB2 SAP Ö¸¶¨²ÎÊýÉèÖã¬ËûÓ¦¸ÃÖ»ÐèÒªÔÚÌØÊâÇé¿öÏÂʹÓÃÕâЩ²ÎÊý¡£Ö»Óз¢ÉúÊý¾Ý¿âÐÔÄÜÎÊÌâÐèÒª¿ìËÙ½â¾ö·½°¸ºÍÌæ´ú½â¾ö·½°¸Ê±²ÅʹÓÃËüÃÇ¡£

¶Ô Open SQL £¬SAP Ö§³ÖÏÂÁÐ DB2 ²ÎÊý

OPT_FOR_ROWS N£ºÈç¹û N > 0£¬Ò»¸ö OPTIMIZE FOR N ROWS ×Ó¾ä¾Í±»Ìí¼Óµ½ SQL Óï¾äÖС£¶Ô ABAP Óï¾äËü°üº¬ UP TO N ROWS ×Ӿ䣬OPTIMIZE FOR N ROWS ÊÇ×Ô¶¯Éú³ÉµÄ¡£ OPT_FOR_ROWS 0 ±»ÓÃÓÚ·ÀÖ¹×Ô¶¯Éú³É OPTIMIZE FOR N ROWS ¡£

USE_OPTLEVEL X£ºÕâ¸ö²ÎÊý¿ÉÒÔ±»ÓÃÀ´Ö¸¶¨Óï¾ä´¦ÀíµÄ DB2 ²éѯÓÅ»¯¼¶±ð¡£Ä¬ÈÏÇé¿öÏ£¬SAP ʹÓÃÓÅ»¯¼¶±ð 5£¬ÕâΪ´ó¶àÊýÇé¿öÌṩÁ˺ÏÀíµÄÓï¾ä±àÒëºÍÖ´ÐÐʱ¼ä¡£ÔÚijЩÇé¿öÏ£¬Ò»¸ö½ÏµÍ»ò½Ï¸ßµÄÓÅ»¯¼¶±ð¿ÉÄÜÉú³ÉÒ»¸ö¶ÔÌØ¶¨Óï¾ä²¢¾ßÓиü¸ßÐÔÄܵķÃÎʼƻ®¡£ÔÚÕâЩÇé¿öÏ£¬²ÎÊý¿ÉÒÔ±»ÓÃ×÷¶ÌÆÚ½â¾ö·½°¸»òÌæ´ú·½°¸¡£

&SUBSTITUTE LITERALS&£º¶ÔÓÚ SAP Äں˰汾 4.6£¬Õâ¸ö²ÎÊýÐèÒª°Ñ SAP ÅäÖòÎÊý dbs/db6/dbsl_substitute_literals ÉèÖÃΪ 1 ¡£Õâ¸ö²ÎÊýʹ ABAP Óï¾äÎı¾ÖеÄÎÄ×Ö±»×÷Ϊ SQL Óï¾äµÄÎÄ×ÖʵÏÖ£¨¶ø²»ÊÇ×÷Ϊ²ÎÊý±ê¼Ç£©¡£Èç¹ûÕâЩÎÄ×Ö±»Ê¹Óã¬ÓÅ»¯Æ÷¿ÉÒÔʹÓÃÉæ¼°ÕâЩÁеıíÖеķֲ¼ÐÅÏ¢¡£×¢Ò⣬´æÔÚÕâÖÖ¿ÉÄÜ£¬Ã»ÓжÔÕâ¸ö±íËѼ¯·Ö²¼Í³¼ÆÐÅÏ¢¡£

&SUBSTITUTE VALUES&£º¶ÔÓÚ SAP Äں˰汾 4.6£¬Õâ¸ö²ÎÊýÐèÒª°Ñ SAP ÅäÖòÎÊý dbs/db6/dbsl_substitute_literals ÉèÖÃΪ 1 ¡£Õâ¸ö²ÎÊýʹÔÚ ABAP Óï¾äÖеÄËùÓÐÊäÈëÖµ¶¼±»×÷Ϊ SQL Óï¾äÎı¾µÄÎÄ×ÖʵÏÖ¡£¾ÍÏñ &SUBSTITUTE LITERALS&£¬DB2 ÓÅ»¯Æ÷¿ÉÒÔʹÓùØÓÚÉæ¼°±íÖеķֲ¼ÐÅÏ¢¡£È»¶ø£¬ÓÉÓÚÖµµÄÌæ»»ÒòΪÓï¾äÎı¾¸Ä±äÁË£¬Õâ¸öÓï¾äÿ´ÎÔËÐеÄʱºò¶¼ÐèÒªÖØÐÂ×¼±¸¡£

CONVERT_FAE_TO_CTE£º¶ÔÓÚÄں˰汾ÔçÓÚ 7.0 µÄ SAP£¬Õâ¸ö²ÎÊýÓÃÀ´¸ø¿ØÖÆÓÐ FOR ALL ENTIRES ½á¹¹µÄ ABAP Óï¾äÉú³É SQL ¡£

ʹÓÃÕýÈ·µÄ SAP ÅäÖòÎÊýÀ´¿ØÖÆÎª FOR ALL ENTIRES Open SQL ²éѯÉú³É SQL

FOR ALL ENTRIES (FAE) Óï¾äÊÇÒ»¸öͨÓÃµÄ SAP ABAP Óï¾ä½á¹¹¡£Õâ¸ö²ÎÊýÔÊÐí ABAP ³ÌÐòÔ±ÓÃÒ»¸ö ABAP ÄÚ²¿±íÁ¬½ÓÒ»¸ö»ò¼¸¸öÊý¾Ý¿â±í¡£ FAE Óï¾ä×ÜÊÇ·µ»ØÒ»¸öΨһµÄ½á¹û¼¯¡£ SAP Êý¾Ý¿â½Ó¿Ú½«É¾³ýÈκδÓÊý¾Ý¿â·µ»ØµÄÖØ¸´Êý¾Ý¡£

ÀýÈ磺

SELECT ¡­ FROM <dbtable> FOR ALL ENTRIES IN <itab>
WHERE <col> = <itab>~<col>

SAP ÌṩÁËÏÂÁÐÅäÖòÎÊýÀ´¿ØÖưѶàÉÙÓï¾ä±»×ª»¯Îª SQL£º

rsdb/prefer_in_itab_opt£ºÈç¹ûÕâ¸ö²ÎÊý±»ÉèÖÃΪ 1 ¶øÇÒÈç¹û FAE Óï¾äÖ»ÔÚ WHERE ×Ó¾äÖÐÉæ¼°Ò»¸öÄÚ²¿±íÁУ¬ÄÇôһ¸öÓÐ IN ÁбíµÄ×ÔÈ» SQL Óï¾ä¾Í±»Éú³É³öÀ´£º

SELECT ¡­ FROM <dbtable> WHERE
<col> IN (<itab>[1]~<col>,
<itab>[2]~<col>, ¡­ , <itab>[N]~<col> )

<itab>[2]~<col>, ¡­ , <itab>[N]~<col> )

rsdb/prefer_join£ºÕâ¸ö²ÎÊýÊÇÔÚ SAP Äں˰汾 7.0 »òÒÔºó¿ÉÓá£Èç¹ûÕâ¸ö²ÎÊý±»ÉèÖÃΪ 1 ²¢ÇÒÈç¹û FAE Óï¾ä²»°üº¬Èκζà±íÖ®¼äµÄÁ¬½Ó£¬ÄÇô FAE Óï¾ä¾Í±»ÊµÏÖΪһ¸öÁ¬½Ó£º

SELECT ¡­
FROM <dbtable> t1,
( SELECT * FROM ( VALUES(<itab>[2]~<col>, <itab>[2]~<col>, ¡­ ,
<itab>[N]~<col> ) )
AS t2_tmp ( <coL> ) GROUP BY <col> )
AS T2
WHERE t1.<col> = t2.<col>

rsdb/prefer_union_all£ºÈç¹ûÕâ¸ö²ÎÊý±»ÉèÖÃΪ 1£¬ÄÇô½«ÎªÄÚ²¿±íÖеÄÿһ¸öÖµ¶¼»áÉú³ÉÒ»¸ö SQL Óï¾ä²¢ÇÒͨ¹ý UNION ALL Á´½ÓÆðÀ´£º

SELECT ¡­ FROM <dbtable> WHERE <col> = <itab> [1]~ <col> UNION ALL
SELECT ¡­ FROM <dbtable> WHERE <col> = <itab> [2]~ <col> UNION ALL
¡­
SELECT ¡­ FROM <dbtable> WHERE <col> = <itab> [N]~ <col>

Èç¹ûÕâ¸ö²ÎÊý±»ÉèΪ 0£¬ÄÇô½«Éú³ÉÓÉ OR Á´½ÓµÄÌõ¼þ£º

SELECT ¡­ FROM <dbtable> WHERE
<col> = <itab>[1]~<col> OR
<col> = <itab>[2]~<col> OR
¡­
<col> = <itab>[N]~<col> OR

Õâ¸ö²ÎÊýÔÚ rsdb/prefer_join ²ÎÊý±»ÉèÖÃΪ 1 µÄÇé¿öϲ»±»Ê¹Óá£

Èç¹ûÉÏÃæµÄת»»Ò»¸ö¶¼Ã»Óз¢Éú£¬FAE Óï¾ä½«±»×ª»»Îª´ø OR µÄ×ÔÈ» SQL Óï¾ä¡£

ÏÂÃæ¸½¼ÓµÄ SAP ÅäÖòÎÊýͨ¹ýÒ»¸ö×ÔÈ» SQL Óï¾ä¿ØÖÆÄÚ²¿ ABAP ±íÖÐÊäÈëµÄÊýÄ¿¡£

rsdb/max_blocking_factor

rsdb/max_in_blocking_factor

rsdb/prefer_fix_blocking

rsdb/min_blocking_factor

rsdb/min_in_blocking_factor

Èç¹ûÄÚ²¿±í°üº¬¸ü¶àÊäÈ룬һЩ×ÔÈ» SQL Óï¾ä»á±»Éú³É³öÀ´¶øÇÒ½á¹ûËѼ¯ÔÚ SAP Êý¾Ý¿â½Ó¿ÚÖС£¸ü¶àϸ½Ú£¬²Î¼û SAP note 48230 ¡£

SAP ÓÃÓÚ¿ØÖÆ FAE Óï¾äµÄÖ´ÐеÄÅäÖòÎÊýÔÚÕû¸ö SAP ϵͳµÄºÜ´ó·¶Î§ÄڶԹؼüÊý¾Ý¿âÃüÁîºÍÐÔÄÜÓ°ÏìÉ϶¼Óзdz£´óµÄЧ¹û¡£Òò´Ë£¬Ö»ÓÐÔÚ½øÐÐÁËÏêϸÎÊÌâ·ÖÎöÖ®ºó»òÊÇ SAP ¼¼ÊõÖ§³ÖÍÆ¼öµÄÇé¿öϸ²¸ÇÕâЩÅäÖòÎÊýµÄĬÈÏÉèÖá£

¶Ô SAP ÄÚºË 7.0 Ö®ºóµÄ°æ±¾£¬rsdb/prefer_join=1 Ñ¡ÏîÊDz»¿ÉÓõġ£¿ÉÓà IN ÁÐ±í£¬UNION ALL Óï¾ä»òÔÚ WHERE ×Ó¾äÖÐÓÐ OR Á´½ÓÌõ¼þµÄÓï¾äÀ´Ìæ»»¡£ SAP µÄ²ÎÊý CONVERT_FAE_TO_CTE ¿ÉÒÔ±»Ìí¼Óµ½ ABAP FAE Óï¾äÖÐÀ´×ª»»³ÉÒ»¸ö DB2 ͨÓñíµÄ±í´ïʽ£¨CTE£©¡£ÕâÌá¸ßÁËÐÔÄÜ£¬ÒòΪ DB2 ÓÅ»¯Æ÷¿ÉÒÔ²úÉú¸üºÃµÄ·ÃÎʼƻ® ¨C ÒòΪÄÚ²¿±íÖеÄÖØ¸´ÖµÒѾ­ÔÚ CTE Öб»É¾³ýÁË¡£

¶Ô SAP BI ÓÐÎÊÌâµÄ²éѯ½øÐÐ SQL ÐÔÄܵ÷ÓÅ

ÔÚ YDB6BWEXT ±¨¸æÖУ¬Á½¸öÓû§¿ÉÓÃÓÚÌṩһ¸ö¸ü¿ìµÄÆäËûÑ¡ÔñÒÔÌá¸ß³¤Ê±¼äÔËÐÐ SAP BI ÓÐÎÊÌâµÄ²éѯµÄÐÔÄÜ¡£

¶Ô reporting ²éѯ£¬SAP NetWeaver BI ʹÓà ADBC ºÍ×ÔÈ» SQL Óï¾ä¡£Õâ¸öÓï¾äÔÚ SAP NetWeaver BI Read Interface ÖÐÉú³É²¢ÔËÐУ¬ÔÚ SQL Óï¾äÖ´ÐÐǰºó»áÓÐÁ½¸ö³ö¿Ú¡£ÀýÈ磬һ¸ö SAP BI ²éѯµÄ DB2 ÓÅ»¯¼¶±ð»ò SQL ²éѯ¼¶±ð¿ÉÒÔÔÚ³ö¿ÚÖ¸¶¨¡£

¶Ô¿ÉÓóö¿Ú¶øÑÔ£¬¿Í»§ÐèÒªÓ¦Óà YDB6BWEXT ±¨¸æµÄ queryoptbefore Óë queryoptafter Á½¸ö±íºÍÔÚ SAP NetWeaver BI 7.0 ÒÔ¼°¸ü¸ß°æ±¾É쵀 SAP note 1143736 ÒÔ¼°¶ÔÔçÆÚ°æ±¾Ó¦Óà SAP note 872397 ¡£

Òª»ñÈ¡ÈçºÎʵʩÔÚ YDB6BWEXT Öеijö¿ÚÖ¸ÄÏÇëÁªÏµ SAP ¿ª·¢Ö§³Ö¡£

1.±ÜÃâ²éѯÌõ¼þÖеĸ´ÔÓ±í´ïʽ

2.±ÜÃâ±í´ïʽÖÐÁ¬½Óν´Ê

3.±ÜÃâ±¾µØÎ½´ÊÖжÔÁÐÓ¦Óñí´ïʽ

4.±ÜÃâ½ÓÁÐÊý¾ÝÀàÐͲ»Æ¥Åä

5.±ÜÃâ²»ÏàµÈµÄÁ¬½Óν´Ê

6.±ÜÃâ DISTINCT ¹Ø¼ü×ֵľۼ¯

7.±ÜÃâ²»±ØÒªµÄÍâÁ¬½Ó

8.°Ñ FETCH FIRST N ROWS ONLY ×Ó¾äºÍ OPTIMIZE FOR N ROWS ×Ó¾äÒ»ÆðʹÓÃ

9.Èç¹ûÄãÔÚʹÓÃÐÇÐÍģʽÁ¬½Ó£¬È·±£ÄãµÄ²åÐðÂú×ã±ê×¼ÒªÇó

10.±ÜÃâ¶àÓàµÄ²éÑ¯Ô¼Êø

11.ʹÓÃÔ¼ÊøÒÔÌá¸ß²éѯÓÅ»¯

12.ÔÚ¸´ÔÓ²éѯÖÐ REOPT °ó¶¨Ñ¡ÏîÓëÊä³ö±äÁ¿Í¬Ê±Ê¹ÓÃ

13.ΪÄãµÄ¹¤×÷¸ºÔØÑ¡Ôñ×î¼ÑµÄÓÅ»¯¼¶±ð

14.ÉèÖà DB2_REDUCED_OPTIMIZATION ×¢²á±äÁ¿

15.Ϊ SAP Ó¦ÓóÌÐò£¬ÉèÖà DB2_WORKLOAD ×¢²á±äÁ¿

16.ÊÕ¼¯ÕýÈ·µÄ±àĿͳ¼ÆÐÅÏ¢£¬°üÀ¨¸ß¼¶Í³¼Æ¹¦ÄÜ

17.×îС»¯ RUNSTATS µÄÓ°Ïì

18.±ÜÃâÊÖ¶¯¸üбàĿͳ¼ÆÐÅÏ¢

19.¶Ô SAP Ó¦ÓóÌÐò£¬Ê¹ÓÃ×Ô¶¯Í³¼ÆÐÅÏ¢ÊÕ¼¯

20.¶ÔÖµ·Ö²¼²»¾ùÔ鵀 SAP BI ±íʹÓÃͳ¼ÆÊÓͼ

21.ʹÓà SAP BI ¾Û¼¯

22.ÔÚ SAP Open SQL ÖÐʹÓà DB2 ²ÎÊý

23.¶Ô FOR ALL ENTRIES Open SQL ²éѯʹÓÃÕýÈ·µÄ SAP ÅäÖòÎÊýÀ´¿ØÖÆ SQL Éú³É

24.Èç¹ûÆäËûµ÷ÓÅÑ¡ÏîûÓеõ½¿ÉÒÔ½ÓÊܵĽá¹û£¬¾ÍʹÓÃÓÅ»¯ÅäÖá£

×ܽá

±¾ÎÄÖеÄ×î¼Ñʵ¼ùÊÇΪÁ˰ïÖúÄã×îС»¯¶Ôϵͳ×ÊÔ´µÄʹÓÃÒÔ¼°ÔËÐÐ SQL Óï¾äµÄʱ¼ä¡£±ÜÃâDZÔÚµÄÐÔÄÜÎÊÌ⣬ÔÚÄã±àдÄãµÄ SQL Óï¾äºÍÅäÖÃÄãµÄÉèÖÃʱʹÓÃÕâЩ×î¼Ñʵ¼ù¡£

Òª±àд¿ÉÒÔ±» DB2 ÓÅ»¯Æ÷ÇáÒ×ÓÅ»¯µÄ SQL Óï¾ä£¬¾ÍÒª±ÜÃâÒ»ÏÂÇé¿ö£º

ËÑË÷Ìõ¼þÖеĸ´ÔÓ±í´ïʽ

ÔÚ±¾µØÎ½´ÊÁÐÉÏÓ¦Óñí´ïʽ

Á¬½ÓÁеÄÊý¾ÝÀàÐͲ»Æ¥Åä

²»µÈÁ¬½Óν´Ê

¶à¸ö¾Û¼¯Ê¹Óà DISTINCT ¹Ø¼ü×Ö

²»±ØÒªµÄÍâÁ¬½Ó

¶àÓàµÄ²éѯν´Ê

Èç¹ûÄãÔÚʹÓÃÐÇÐÍģʽÁ¬½Ó£¬ÎªÁË DB2 Êý¾Ý·þÎñÆ÷¿ÉÒÔʹÓÃרÃŵÄÐÇÐÍÁ¬½Ó·½·¨ÒÔÌá¸ß²éѯÐÔÄÜ£¬ÒªÈ·±£ÄãµÄ²éѯÂú×ã±ê×¼¡£

Èç¹ûÄãÖ¸¶¨ FETCH FIRST n ROWS ONLY ×Ӿ䣬ÄãÒ²Ó¦¸ÃÖ¸¶¨ OPTIMIZE FOR n ×Ó¾äÀ´±ÜÃ⻺³å²Ù×÷

ÔÚÄãÉè¼Æ²¢ÅäÖÃÄãµÄ DB2 Êý¾Ý¿âʱ£¬Ç뿼ÂÇÏÂÁÐ×î¼Ñʵ¼ù£º

ʹÓÃÔ¼Êø

¶ÔÔÚν´ÊÖÐÓÐÊäÈë±äÁ¿µÄ SQL Óï¾äʹÓà REOPT °ó¶¨Ñ¡Ïî

¶ÔÄãµÄ¹¤×÷¸ºÔØÑ¡Ôñ×îÓż¶±ð

ÉèÖà DB2_REDUCED_OPTIMIZATION ×¢²á±äÁ¿

È·±£×¼È·µÄ±àĿͳ¼ÆÐÅÏ¢

¿¼ÂÇÊÕ¼¯¸ß¼¶Í³¼ÆÐÅÏ¢£¬±ÈÈçÁзÖ×éͳ¼ÆÐÅÏ¢¡¢×ÓÔªËØÍ³¼ÆÐÅÏ¢ÒÔ¼°Í³¼ÆÊÓͼ

×îºó£¬¿ÉÒÔʹÓà DB2 ½âÊ͹¦ÄÜÀ´»Ø¹ËDZÔڵIJéѯ·ÃÎʼƻ®²¢ÅжÏÈçºÎµ÷Õû²éѯÒÔ»ñµÃ×î¼ÑÐÔÄÜ¡£

Èç¹ûÄ㿪·¢ SAP Ó¦ÓóÌÐò£¬ÄãÓ¦¸ÃÓ¦ÓÃÕâЩ×î¼Ñʵ¼ù£¬±ÈÈçÈ·±£±àĿͳ¼ÆÐÅÏ¢µÄÕýÈ·¡£ÁíÍâ¡££¬ÄãÓ¦¸ÃÓ¦Óà SAP ÌØ¶¨µÄ×î¼Ñʵ¼ù£º

Ϊ SAP ÉèÖà DB2_WORKLOAD ×¢²á±äÁ¿

¶ÔÓв»Æ½ºâ·Ö²¼ÖµµÄ SAP BI ±íʹÓÃͳ¼ÆÐÅÏ¢ÊÓͼ

ʹÓà SAP BI ¾Û¼¯À´Ìæ´ú MQTs

ÄãÒ²¿ÉÒÔͨ¹ý SAP Open SQL ÖÐ DB2 ²ÎÊýÕýÈ·µÄÅäÖÃÌá¸ßÔÚ SAP Ó¦ÓóÌÐòÖÐµÄ SQL ²éѯÐÔÄÜ£¬²¢Ê¹ÓÃÕýÈ·µÄ SAP ÅäÖòÎÊýÀ´¿ØÖÆ FOR ALL ENTRIES ¹¹ÔìµÄ Open SQL ²éѯµÄ SQL ·ç¸ñ¡£

   
3677 ´Îä¯ÀÀ       46
Ïà¹ØÎÄÕÂ

»ùÓÚEAµÄÊý¾Ý¿â½¨Ä£
Êý¾ÝÁ÷½¨Ä££¨EAÖ¸ÄÏ£©
¡°Êý¾Ýºþ¡±£º¸ÅÄî¡¢ÌØÕ÷¡¢¼Ü¹¹Óë°¸Àý
ÔÚÏßÉ̳ÇÊý¾Ý¿âϵͳÉè¼Æ ˼·+Ч¹û
 
Ïà¹ØÎĵµ

GreenplumÊý¾Ý¿â»ù´¡Åàѵ
MySQL5.1ÐÔÄÜÓÅ»¯·½°¸
ijµçÉÌÊý¾ÝÖÐ̨¼Ü¹¹Êµ¼ù
MySQL¸ßÀ©Õ¹¼Ü¹¹Éè¼Æ
Ïà¹Ø¿Î³Ì

Êý¾ÝÖÎÀí¡¢Êý¾Ý¼Ü¹¹¼°Êý¾Ý±ê×¼
MongoDBʵս¿Î³Ì
²¢·¢¡¢´óÈÝÁ¿¡¢¸ßÐÔÄÜÊý¾Ý¿âÉè¼ÆÓëÓÅ»¯
PostgreSQLÊý¾Ý¿âʵսÅàѵ