DB2 SQLÐÔÄÜÓÅ»¯Ïà¹ØµÄ10´óÒªËØ
ÏÂÃæÌá³öÁË10ÌõºÍDB2 SQLÐÔÄÜÏà¹ØµÄ10ÌõÒòËØ£º
1¡¢ÌṩÊʵ±µÄͳ¼ÆÐÅÏ¢
¶ÔDB2ÓÅ»¯¹ÜÀíÆ÷£¨otimizer£©¶øÑÔ£¬ÈçºÎ¸ü¼ÓÓÐЧµÄÖ´ÐÐSQLÓï¾äÊÇÓÉ´æÔÚÓÚDB2 catalogÖеÄͳ¼ÆÐÅÏ¢¾ö¶¨µÄ£¬ÓÅ»¯Æ÷ÀûÓÃÕâЩÐÅÏ¢¾ö¶¨×îÓÅ»¯µÄ·¾¶¡£
Òò´Ë£¬ÎªÁ˱£³ÖϵͳÄܹ»×ö³ö×î¼ÑÑ¡Ôñ£¬ÐèÒª¾³£µÄÔËÐÐRunstatsÃüÁÀ´±£³Öϵͳͳ¼ÆÐÅÏ¢µÄ¼°Ê±ÓÐЧ¡£ÔÚ¹¤×÷¸ººÉºÜ´óµÄÉú²ú»·¾³ÖУ¬¾³£ÔËÐÐRunstatsÊǺܱØÒªµÄ£¬ÎªÁ˼õÉÙ½øÐÐͳ¼Æ¶Ôϵͳ´øÀ´µÄÓ°Ï죬¿ÉÒÔʹÓÃSampling¼¼Êõ¡£
2¡¢ÔÚSQLÓï¾äÖо¡Á¿Ê¹ÓÃStage 1µÄν´Ê
ÊôÓÚStage 1µÄSQLν´ÊÓÉData Manager´¦Àí£¬¶øÊôÓÚStage 2µÄSQLÓÉRelational Data ServerÖ´ÐС£ÔÚ´¦ÀíЧÂÊÉÏ£¬Data Manager¸üÓÐÓÅÊÆ£¬Òò´Ë£¬Èç¹û¿ÉÄܵĻ°¾¡Á¿°ÑSQLÓï¾äд³ÉÄܹ»ÔÚStage 1Ö´Ðеġ£Èç¹ûÄܹ»½áºÏIndexÀ´È·¶¨SQLÓï¾äÖеÄν´Ê£¬ÄÇô½«»á¸ü¼ÓÌá¸ßSQLÖ´ÐеÄЧÂÊ¡£Òò´Ë£¬ÔÚдSQL²éѯÓï¾äµÄʱºò£¬Òª½áºÏIndexµÄ¶¨ÒåÒÔ¼°Î½´ÊÃèдµÄÑ¡Ôñ£¬¾¡¿ÉÄܵÄÈÃSQLÔÚStage 1ÔËÐС£
3¡¢ÔÚSQLÖнöÑ¡ÔñÐèÒªµÄÁÐ
ÔÚSQLÓï¾äÖУ¬½öÖ¸¶¨³ÌÐòÐèÒªµÄÁлáÓÐÀûÓÚÌá¸ßSQLÔËÐеÄÐÔÄÜ¡£Èç¹û²ÉÓÃSelect *ÕâÖÖģʽ£¬½«»á¸ø¿Í»§¶ËµÄÓ¦ÓóÌÐò´øÀ´¶îÍâµÄ´¦Àí¹¤×÷£¬²¢ÐèÒª¸ü¶àµÄÄÚ´æµÈ×ÊÔ´¡£¶Ô·þÎñÆ÷¶Ë¶øÑÔ£¬¶àÓàµÄÁÐÔÚÅÅÐò¹ý³ÌÖлáÔö´óSort¹¤×÷Îļþ£¬²¢Òò´Ëµ¼ÖÂÅÅÐòËٶȵÄϽµ£¬²¢ÐèÒª¸ü¶àµÄ´æ´¢×ÊÔ´¡£ÔÚÍøÂç´«Êä¹ý³ÌÖУ¬Ò²½«´«ÊäһЩ²»±ØÒªµÄÊý¾Ý£¬½µµÍÍøÂçЧÂÊ¡£
4¡¢½öÑ¡ÔñÐèÒªµÄÐÐ
²éѯµÄÐÐÔ½ÉÙ£¬²éѯµÄËٶȺÍÏìÓ¦ËٶȾÍÔ½¿ì¡£²éѯ»ñµÃµÄÿһÐÐÊý¾Ý£¬¶¼¾ÀúÁË´Ó´æ´¢É豸-¡µ»º³å³Ø-¡µÅÅÐòºÍת»»-¡µÍøÂç´«Êä-¡µÓ¦ÓóÌÐòµÈһϵÁйý³Ì¡£ÔÚÊý¾ÝµÄ²éѯ¹ý³ÌÖУ¬Êý¾Ý¿â¹ÜÀíÆ÷½«»á×÷ºÜ¶àÊý¾ÝµÄ¹ýÂ˹¤×÷£¬Èç¹ûÓÐÐí¶à²¢²»ÐèÒªµÄÐдÓÊý¾Ý¿âÖмìË÷³öÀ´£¬½«»áÀ˷Ѻܶ಻±ØÒªµÄ´¦Àíʱ¼ä£¬Òò´Ë£¬ÔÚSQLÓï¾äµÄ׫д¹ý³ÌÖÐÓ¦¸ÃÏêϸµÄÖ¸¶¨²éѯÌõ¼þ£¬½ö²éѯÐèÒªµÄÐС£
5¡¢Èç¹ûÊý¾Ý¿âÖеÄÊý¾ÝºÜ³¤Ê±¼ä²»±ä£¬ÔÚSQL¾¡Á¿Ê¹Óó£Á¿»òÕß×Ö·û¡£
ÔÚSQLÖÐʹÓÃÖ÷»ú±äÁ¿£¬ÊÇΪÁËʹϵͳÊÊÓ¦²»¶Ï±ä»¯µÄÍⲿ»·¾³¡£ÔÚSQL³ÌÐòÖÐʹÓÃÖ÷»ú±äÁ¿Äܹ»Ê¹Ó¦ÓóÌÐò²»ÐèÒªÖØа󶨾ͿÉÒÔÖ±½ÓÖ´ÐÐеÄSQL¶¨Òå¡£µ«ÊÇÕâÖÖÁé»îÐÔÊÇÒÔ½µµÍDB2ÓÅ»¯¹ÜÀíÆ÷µÄÐÔÄÜΪ´ú¼ÛµÄ¡£ÔÚ°üº¬Ö÷»ú±äÁ¿µÄSQLÓï¾äÖУ¬DB2ÓÅ»¯¹ÜÀíÆ÷ÒÔĬÈϵĹÀ¼ÆÖµÀ´½øÐÐSQL·¾¶µÄÑ¡Ôñ£¬¶ø²»ÊǸù¾ÝCatalogÖеÄͳ¼ÆÐÅÏ¢£¬ÕâÖÖÑ¡ÔñºÜ¶àʱºò¶¼²»ÊÇ×îÓÅ»¯µÄ¡£Òò´Ë£¬Ê¹Óó£Á¿»òÕß×Ö·û´úÌæÖ÷»ú±äÁ¿ÔÚ³¤Ê±¼ä¹Ì¶¨²»±äµÄ»·¾³Öн«»á±ÈʹÓÃÖ÷»ú±äÁ¿¸üÓÐÓÅÊÆ¡£
6¡¢Ê¹Êý×Ö¡¢ÈÕÆÚµÈÊý¾ÝÀàÐÍÏàÆ
Ïà¹ØÎĵµ£º
ӦһλÍøÓѵÄÐèÇ󣬲¢ÐÞÕýÁËMVPÀîºé¸ù".NETƽ̨ÏÂWEBÓ¦ÓóÌÐòµÄ²¿Êð(°²×°Êý¾Ý¿âºÍ×Ô¶¯ÅäÖÃ)"ÖеÄosqlÓ÷¨´íÎó£¬ÒѲâÊÔͨ¹ý¡£
Ò»).´´½¨²¿ÊðÏîÄ¿
1. ÔÚ“Îļþ”²Ëµ¥ÉÏÖ¸Ïò“Ìí¼ÓÏîÄ¿”£¬È»ºóÑ¡Ôñ“н¨ÏîÄ¿”¡£
2. ÔÚ“Ìí¼ÓÐÂÏîÄ¿”¶Ô»°¿òÖУ¬Ñ¡Ôñ“ÏîÄ¿ÀàÐÍ”´°¸ñÖ ......
¡ô1.DBCC CacheStats £ºÏÔʾ´æÔÚÓÚµ±Ç° buffer Cache ÖеĶÔÏóµÄÐÅÏ¢£¬ÀýÈç £ºhitrates ±àÒëµÄ¶ÔÏóºÍÖ´Ðмƻ®
DBCC CACHESTATS
¡¡¡¡´ÓÕâ¸öÃüÁî¿ÉÒԵõ½Ò»Ð©¹Ø¼üµÄͳ¼ÆÐÅÏ¢£º
¡¡¡¡Hit Ratio£ºÏÔʾÌض¨¶ÔÏó¿ÉÒÔÔÚSql ServerµÄ»º´æÖб»ÃüÖеİٷֱȣ¬Õâ¸öÊýÖµÔ½´ó£¬Ô½ºÃ
¡¡¡¡Object Count£ºÏÔ ......
Êý¾Ý¿â£º
1¡¢ ´´½¨Êý¾Ý¿âµÄÓï·¨£º
if exists (select * from sysdatabases where name='DT_name') /*ÅжÏÊÇ·ñ´æÔÚ£¬ÊÇÔòɾ³ý*/
drop database DT_name
create database database_name
on primary /* Ö¸¶¨Ö÷ÒªÎļþ×飬ÆäÖеÚÒ»¸öÖ÷ÒªÎļþ * ......
½«ÁÐת»»ÎªÐУ¬Ï൱ÓÚ½«±í½á¹¹Ðýת90¶È
T_Student ±í
Stud_ID
Sex
Name
1
ÄÐ
Tom
2
Å®
Anne
3
ÄÐ
Jack
Ö´ÐÐ: Exec proColumnToRow ’T_Student’,’Name’,’ New_ID’
ת»»ºóµÄ±í
New_ID
Tom
Anne
Jack
Stud_ID
1
2
3
Sex
ÄÐ
Å®
ÄÐ
& ......
--
> ²âÊÔÊý¾Ý£º[tb]
if
object_id
(
'
[tb]
'
)
is
not
null
drop
table
[
tb
]
go
create
table
[
tb
]
(
[
id
]
int
,
[
lx
]
int
)
insert
[
tb
]
select
29
,
2
union
all
select
30
,
3
union
all
sel ......