Ch¬ng IV Giíi thiÖu vµ tèi u hãa c©u lÖnh SQL trong hÖ tÝnh ho¸ ®¬n vµ qu¶n lý kh¸ch hµng c«ng ty cÊp níc Thµnh phè Hå chÝ minh
Néi dung chÝnh yÕu cña ch¬ng nµy lµ ®a ra nh÷ng c¸ch thøc cô thÓ tèi u ho¸ c©u lÖnh SQL trong HÖ tÝnh ho¸ ®¬n vµ qu¶n lý kh¸ch hµng c«ng ty cÊp níc Thµnh phè Hå ChÝ Minh. Trong phÇn A sÏ giíi thiÖu vÒ qui m« cña bµi to¸n nãi trªn ®Ó nªu lªn ®îc ý nghÜa cña tèi u ho¸, do qui m« cña bµi to¸n lín (víi 500,000 kh¸ch hµng, 1 triÖu ®ång hå, 3 triÖu chØ sè ®ång hå, 5 triÖu ho¸ ®¬n....) dÉn ®Õn mçi c¶i tiÕn ®Òu rÊt cã ý nghÜa. Trong phÇn B sÏ giíi thiÖu ph¬ng ph¸p t×m nh÷ng c©u lÖnh cha tèi u vµ trong phÇn C lµ c¸c vÝ dô thùc nghiÖm ®· ®îc sö dông trong HÖ tÝnh ho¸ ®¬n vµ qu¶n lý kh¸ch hµng ®Ó n©ng cao tèc ®é xö lý cho phÇn mÒm nµy.
A. Giíi thiÖu vÒ hÖ ch¬ng tr×nh TÝnh ho¸ ®¬n vµ qu¶n lý kh¸ch hµng 1. §Æt vÊn ®Ò
C«ng ty cÊp níc TP Hå ChÝ Minh (WSC - Water Supply Company) lµ mét doanh nghiÖp quèc doanh lín thùc hiÖn c«ng viÖc ®¶m b¶o cung cÊp níc sinh ho¹t cho hµng tr¨m ngµn hé c tró trªn ®Þa bµn thµnh phè. §Ó ®¶m b¶o quyÒn lîi nh©n d©n còng nh cña WSC, c«ng ty ®· kÝ kÕt hîp ®ång cung cÊp níc cho kh¸ch hµng vµ hµng th¸ng kh¸ch hµng sÏ nhËn ®îc ho¸ ®¬n yªu cÇu thanh to¸n sè tiÒn t¬ng øng víi khèi lîng níc ®· sö dông. Víi sè lîng kh¸ch hµng nhiÒu nh vËy, viÖc qu¶n lý doanh thu ®¶m b¶o cho chÝnh x¸c, kÞp thêi lµ yÕu tè rÊt quan träng ¶nh hëng ®Õn ho¹t ®éng kinh doanh cña WSC.
WSC ®· cã nhiÒu n¨m øng dông m¸y tÝnh trong s¶n xuÊt vµ qu¶n lý kinh doanh. Tríc n¨m 1975, WSC ®· sö dông hÖ m¸y tÝnh IBM ®Ó qu¶n lý vµ tÝnh ho¸ ®¬n thanh to¸n tiÒn níc. Tõ n¨m 1990 hÖ nµy ®îc thay b»ng c¸c ch¬ng tr×nh viÕt b»ng FoxBase trªn PC, sau nµy chuyÓn sang FoxPro. HiÖn t¹i hÖ thèng cò viÕt trªn PC ho¹t ®éng cã rÊt nhiÒu h¹n chÕ nh d÷ liÖu bÞ ph©n nhá, qui tr×nh thao t¸c phøc t¹p, thùc hiÖn b»ng tay nhiÒu do kh«ng cã kÕt nèi thèng nhÊt. C¸c b¸o c¸o ph¶i tæng hîp tõ c¸c phÇn nhá, khi in ®îc ho¸ ®¬n mµ ph¸t hiÖn sai th× ph¶i söa ë møc lËp tr×nh do vËy c¸c b¸o c¸o vÒ t×nh h×nh kinh doanh kh«ng ®¸p øng kÞp thêi, kÐm chÝnh x¸c.
§øng tríc yªu cÇu hiÖn ®¹i ho¸ m¹ng líi cÊp níc, tõ n¨m 1997 WSC ®îc trang bÞ mét hÖ thèng m¹ng m¸y tÝnh hiÖn ®¹i, ®ßi hái mét hÖ thèng phÇn mÒm míi, øng dông c«ng nghÖ hiÖn ®¹i, cã kh¶ n¨ng kÕt nèi diÖn réng, qu¶n lý lîng kh¸ch hµng lín vµ ®¸p øng yªu cÇu nghiÖp vô míi. Nh©n viªn còng ®· cã t¸c phong c«ng nghiÖp vµ c¸c thao t¸c hÖ thèng ®· trë thµnh mét thãi quen nghiÖp vô. WSC cÇn x©y dùng “HÖ tÝnh ho¸ ®¬n vµ qu¶n lý kh¸ch hµng” ®Ó phôc vô cho c«ng viÖc kinh doanh cña m×nh ®¹t hiÖu qu¶ h¬n. Môc tiªu ®Æt ra ®èi víi hÖ thèng lµ ph¶i ®¸p øng ®îc 142 yªu cÇu do c¸c chuyªn gia t vÊn níc ngoµi bao trïm lªn c¸c lÜnh vùc:
+ Kh¸ch hµng
+ Yªu cÇu vµ khiÕu n¹i cña kh¸ch hµng
+ §ång hå vËt t, thiÕt bÞ vµ vÞ trÝ l¾p ®Æt ®ång hå
+ BiÓu gi¸ tiÒn níc vµ tiÒn phô thu
+ ChØ sè ®ång hå vµ Xö lý ho¸ ®¬n tiÒn níc
+ Thu tiÒn
+ Thëng ph¹t kh¸ch hµng
+ PhiÕu c«ng t¸c/thi c«ng vµ nh©n sù
+ §¸p øng yªu cÇu nghiÖp vô hiÖn t¹i
Nh vËy hÖ thèng míi võa ph¶i thùc hiÖn theo nh÷ng yªu cÇu hiÖn ®¹i nhng l¹i kh«ng ®îc ph¸ vì nh÷ng qui tr×nh nghiÖp vô ®· tån t¹i tõ l©u.
ViÖc x©y dùng mét phÇn mÒm qu¶n lý kh¸ch hµng vµ tÝnh ho¸ ®¬n níc cho c«ng ty cÇn thiÕt ph¶i cã mét m¹ng m¸y tÝnh côc bé vµ quan träng h¬n lµ ph¶i cã HÖ qu¶n trÞ CSDL trªn m¹ng. Tuy cã nhiÒu HÖ qu¶n trÞ CSDL hç trî cho ng«n ng÷ SQL nh Access, Foxpro, , DB2, Sybase ... nhng Oracle lµ c«ng cô ph¸t triÓn thÝch hîp h¬n c¶ v× ®©y lµ mét bµi to¸n ph©n t¸n thùc sù víi CSDL lín, nhiÒu ngêi dïng sÏ khai th¸c triÖt ®Ó c¸c u ®iÓm cña Oracle nh ®· nªu ë ch¬ng II.
2. Ph©n tÝch bµi to¸n
C¸c kh¸ch hµng cña WSC ®îc c¸c nh©n viªn theo dâi vµ qu¶n lý viÖc thanh to¸n dÞch vô th«ng qua c¸c ho¸ ®¬n. Trªn ho¸ ®¬n ghi râ ngµy viÕt ho¸ ®¬n, tªn kh¸ch hµng, sè lîng níc ®· sö dông, ®¬n gi¸ tÝnh trªn mét ®¬n vÞ tÝnh, sè tiÒn kh¸ch hµng ph¶i tr¶ vµ tªn nh©n viªn ®· viÕt ho¸ ®¬n kiªm thu tiÒn. Ho¸ ®¬n nµy ®îc sao lµm nhiÒu b¶n trong ®ã mét b¶n kh¸ch hµng gi÷, mét b¶n thñ quÜ gi÷, mét b¶n kÕ to¸n gi÷. KÕ to¸n ph¶i b¸o c¸o cho l·nh ®¹o vÒ doanh thu trong th¸ng, sè tiÒn thu vÒ quÜ, sè tiÒn kh¸ch hµng cßn nî, danh s¸ch kh¸ch hµng cha thanh to¸n ho¸ ®¬n ®Ó cã biÖn ph¸p xö lý. Ngoµi ra ban l·nh ®¹o còng cÇn ®îc biÕt møc ®é sö dông níc ë mçi khu vµ sù chªnh lÖnh vÒ møc ®é gi÷a c¸c khu víi nhau ®Ó cã sù ®iÒu chØnh hîp lý trong viÖc cung cÊp níc.
-
C¸c s¬ ®å chøc n¨ng FHD (Function Hearchy Diagram):
HÖ qu¶n lý ho¸ ®¬n níc ®îc thiÕt kÕ híng chøc n¨ng gåm bèn ph©n hÖ thµnh phÇn trong ®ã cã h¬n 120 module ch¬ng tr×nh, 70 module lµm b¸o c¸o, 30 Database triggers, 83 thñ tôc vµ hµm.
Díi ®©y lµ c¸c s¬ ®å chøc n¨ng cho tõng ph©n hÖ
-
S¬ ®å quan hÖ thùc thÓ ERD (Entity Relationship Diagram):
HÖ qu¶n lý ho¸ ®¬n níc bao gåm 105 thùc thÓ trong ®ã c¸c thùc thÓ quan träng lµ :
+ Kh¸ch hµng (Customers) : HÖ thèng lu c¸c th«ng tin kh¸ch hµng nh tªn, ®Þa chØ, sè CMT... C¸c th«ng tin liªn quan ®Õn tÝnh tiÒn níc vµ b¸o c¸o th× ®îc chuÈn ho¸. Mçi kh¸ch hµng cã mét m· sè duy nhÊt gäi lµ m· danh bé (cust_no) dùa trªn ®Þa danh quËn,phêng vµ ®êng phè. Ngoµi ra øng víi mçi kh¸ch hµng chÝnh cßn cã thÓ c¸c kh¸ch hµng phô ®îc ph©n biÖt bëi sub_cust_id (nÕu lµ kh¸ch hµng chÝnh th× thuéc tÝnh nµy nhËn gi¸ trÞ null). Víi mçi lo¹i kh¸ch hµng thuéc mét trong c¸c lo¹i: T gia, TËp thÓ, C¬ quan, Ngêi níc ngoµi. Kh¸ch hµng cã thÓ sö dông níc cho nhiÒu môc ®Ých kh¸c nhau nh dïng cho sinh ho¹t, s¶n xuÊt, dÞch vô vµ mçi mét ®Ých cã mét gi¸ biÓu riªng. Mét kh¸ch hµng thuéc mét ®ît tÝnh ho¸ ®¬n/thu tiÒn/®äc sè nhÊt ®Þnh. §ång hå l¾p ®Æt ®îc lu ®Çy ®ñ c¸c th«ng sè kü thuËt vµ vÞ trÝ vËt lý.
HiÖn nay tæng sè kh¸ch hµng cña c«ng ty lµ 500,000.
+ §ång hå (meter): ®©y lµ mét trong c¸c vËt t cña c«ng ty cÇn ®îc qu¶n lý víi sè lîng 1,000,000 chiÕc.
+ Mçi ®ång hå ®îc cµi ®Æt cho kh¸ch hµng ®Òu ph¶i lu gi÷ c¸c th«ng tin liªn quan nh m· kh¸ch hµng, kinh ®é, vÜ ®é, nh·n hiÖu, níc s¶n xuÊt, kiÓu mÆt sè.
+ Ho¸ ®¬n (Bills): 5,000,000.
+ ChuyÓn nî khã ®ßi: 1,000,000
+ ChØ sè ®ång hå (Metereading) lµ mét trong nh÷ng phÇn nhËp liÖu chÝnh, phøc t¹p, ®îc thùc hiÖn hµng th¸ng tríc khi tÝnh ho¸ ®¬n tæng céng lµ 3,000,000 chØ sè. Mçi ®ît cÇn ph¶i nhËp kho¶ng 20,000 chØ sè ®ång hå víi c¸c tr¹ng th¸i kh¸c nhau. Tr¹ng th¸i ®äc sè: Theo lý thuyÕt th× chØ sè ®ång hå sÏ tuÇn tù t¨ng vµ ChØ sè míi nhá h¬n ChØ sè cò chØ khi vît qua sè tèi ®a. Nhng thùc tÕ viÖc nhËp chØ sè lµ mét vÊn ®Ò kh¸ phøc t¹p vµ cã nhiÒu t×nh huèng nh: Míi l¾p, Thay ®ång hå, §ång hå bÞ kÑt kim, Kh«ng ®äc ®îc sè v× ngËp níc, v¾ng nhµ...
+ Sè liÖu thanh to¸n: 5,000,000
+§iÒu chØnh hîp ®ång: 1,000,000
+PhiÕu c«ng t¸c: 500,000
+Yªu cÇu vµ khiÕu n¹i: 100,000
3. Gi¶i quyÕt bµi to¸n
Trªn c¬ së tæ chøc m¹ng nh trªn, HÖ qu¶n lý ho¸ ®¬n níc, ®îc thiÕt kÕ theo m« h×nh c¬ së d÷ liÖu ph©n t¸n trªn m«i trêng Oracle.
HÖ gåm mét c¬ së d÷ liÖu ë trung t©m vµ 4 c¬ së d÷ liÖu ë 4 chi nh¸nh. T¹i trung t©m cã thÓ truy nhËp d÷ liÖu cña c¶ 4 chi nh¸nh. Mçi chi nh¸nh chØ cã thÓ truy nhËp vµ cËp nhËt d÷ liÖu cña chi nh¸nh ®ã.
T¹i trung t©m 2 m¸y chñ chÝnh lµ Billing vµ Account ®îc nèi víi nhau vµ ch¹y theo chÕ ®é dù phßng (Main-Standby). Khi m¸y thø nhÊt cã sù cè, th× m¸y thø hai sÏ ®¶m nhËn nhiÖm vô cña m¸y chñ thø nhÊt. §¶m b¶o hÖ thèng thêng xuyªn ho¹t ®éng.
Trêng hîp m¸y chñ ë Chi nh¸nh cã sù cè th× cã thÓ kh«i phôc ®Çy ®ñ d÷ liÖu tõ trung t©m, nh vËy hÖ thèng ®¶m b¶o kh«ng bÞ mÊt d÷ liÖu
Trong giai ®o¹n nµy, dùa vµo c¸c s¬ ®å thùc thÓ vµ s¬ ®å chøc n¨ng cña hÖ thèng tiÕn hµnh Generate ra thµnh c¸c c©u lÖnh SQL DDL (Data Define Language) ®Ó t¹o ra c¸c b¶ng, c¸c Form vµ c¸c Report.
B. C¸ch thøc t×m ra nh÷ng lÖnh cÇn tèi u trong phÇn mÒm
§Ó t×m ra ®îc c¸c lÖnh SQL cha tèi u trong phÇn mÒm cÇn t¹o ra b¶n sao c¸c lÖnh ®· ®îc thùc hiÖn mét c¸ch thùc sù (c©u lÖnh SQL ®îc thùc hiÖn cã thÓ kh«ng gièng nh c©u lÖnh SQL viÕt trong ch¬ng tr×nh) sau ®ã x©y dùng l¹i c©y ph©n tÝch. Qu¸ tr×nh t¹o b¶n sao c©u lÖnh trong phÇn mÒm chØ ®îc thùc hiÖn khi c¸c tham sè trong Parameter File cã gi¸ trÞ:
timed_statistics = true # TÝnh to¸n thêi gian thùc hiÖn c©u lÖnh
sql_trace = true # T¹o c¸c file cã tªn më réng lµ trc
# N¬i chøa c¸c file *.trc do c¸c tiÕn tr×nh cña hÖ thèng
background_dump_dest=%RDBMS80%\trace
# N¬i chøa c¸c file *.trc do c¸c tiÕn tr×nh cña ngêi dïng
user_dump_dest=%RDBMS80%\trace
Sau khi thùc hiÖn c¸c thay ®æi trong Init File th× CSDL ph¶i ®îc kÕt nèi l¹i (Shutdown sau ®ã Startup) ®Ó c¸c tham sè nµy cã t¸c dông. Khi thùc hiÖn mét ch¬ng tr×nh th× h×nh ¶nh cña c¸c tiÕn tr×nh sinh ra sÏ ®îc ghi l¹i ra ®Üa. B»ng tr×nh tiÖn Ých TKPROF.EXE cã thÓ gi¶i nghÜa c¸c tiÕn tr×nh nµy còng nh dùng l¹i c©y ph©n tÝch lÖnh tõ ®ã t×m ra c¸c lÖnh cha hîp lý mµ cÇn thiÕt ph¶i tèi u (cã thÓ nèi nhiÒu file *.trc l¹i cho TKPFOF thi hµnh mét lÇn).
Có ph¸p:
TKPROF filename1 filename2 [ SORT = [ ( ] option [ , option, option,..) ] ] [ PRINT = integer ]
[ INSERT = filename3 ] [ SYS = Yes | No ] [ TABLE = schema.table ]
[ EXPLAIN = user/password ]
Trong ®ã c¸c tham sè
- filename1: tªn file ¶nh cña tiÕn tr×nh ( *.trc )
- filename2: tªn file kÕt qu¶ cña TKPROF.
-EXPLAIN: KÕt nèi vµo CSDL cña Oracle víi danh nghÜa user/password råi x©y dùng c©y ph©n tÝch lÖnh b»ng EXPLAIN PLAN (xem phô lôc A).
- TABLE: Tªn lîc ®å vµ tªn b¶ng trong lîc ®å trung gian ®Ó t¹o ra file kÕt qu¶.
- INSERT: Tªn file SQL script t¹o b¶ng chøa néi dung file kÕt qu¶.
- SYS: BËt/t¾t danh s¸ch c©u lÖnh cña qu¶n trÞ hÖ thèng (User SYS)
- SORT: S¾p xÕp thø tù kÕt qu¶ theo tr×nh tù chØ ra nh thêi gian, sè hµng, sè khèi ®äc tõ ®Üa...
- RECORD: T¹o ra SQL script ghi l¹i c¸c c©u lÖnh ®· ®îc thùc hiÖn ®Ó cã thÓ t¸i hiÖn l¹i c¸c qu¸ tr×nh cña ngêi dïng.
Trong file kÕt qu¶ cña TKPROF gåm cã c¸c th«ng tin vÒ c¸c qu¸ tr×nh ph©n tÝch lÖnh (parsed), thùc hiÖn (Executed), lÊy kÕt qu¶ (Fetched) cña c©u lÖnh, sè hiÖu tiÕn tr×nh ngêi dïng, sè lÇn c¸c gãi hµm th viÖn ph¶i n¹p vµo (Library Cache Misses), c©y ph©n tÝch lÖnh. Trong c¸c cét cña c©y ph©n tÝch lÖnh cã th«ng tin sè lÇn thùc hiÖn c©u lÖnh (count), thêi gian sö dông CPU (CPU), thêi gian trong c¸c giai ®o¹n thùc hiÖn c©u lÖnh (Elapsed), sè lîng khèi d÷ liÖu ®äc tõ ®Üa (Disk), sè bé ®Öm cÇn nhËn (Query), sè bé ®Öm hiÖn cã (Current), sè hµng ®îc truy xuÊt (Rows). Ngoµi ra Oracle cßn cung cÊp lÖnh EXPLAIN PLAN ®Ó ®a ra c©y ph©n tÝch lÖnh, ANALYZE ®Ó ph©n tÝch d÷ liÖu tõ m«i trêng SQL/PLUS (xem phÇn phô lôc A).
C. Thùc hiÖn tèi u phÇn mÒm Qu¶n lý ho¸ ®¬n níc 1. Tèi u b»ng sö dông Index
Khi hÖ qu¶n lý ho¸ ®¬n ®· x©y dùng xong vµ ®îc ®a vµo sö dông th× qu¸ tr×nh tÝnh ho¸ ®¬n cña hÖ vÉn chËm. §Ó cã thÓ t¨ng tèc ®é thùc hiÖn cña ch¬ng tr×nh, c¸c c©u lÖnh thùc hiÖn trong qu¸ tr×nh tÝnh ho¸ ®¬n ®· ®îc ph©n tÝch vµ kÕt qu¶ cho thÊy tr¹ng th¸i mçi ho¸ ®¬n ®îc tÝnh xong sÏ ®îc cËp nhËt vµo b¶ng ho¸ ®¬n (Bills) theo m· kh¸ch hµng (cust_id), tuy nhiªn b¶ng Bills l¹i thiÕu Index trªn cét nµy v× vËy dÉn ®Õn viÖc truy nhËp b¶ng ph¶i dïng ph¬ng thøc duyÖt toµn bé (xem c©y ph©n tÝch lÖnh liÖt kª ë díi).
§Ó kiÓm tra xem mét cét ®· ®îc INDEX hay cha b»ng c¸ch xem View cña hÖ thèng chøa th«ng tin vÒ c¸c cét ®· Index cã chøa cét ®ã hay kh«ng nh c©u lÖnh sau:
SELECT index_name,column_name FROM user_ind_columns
WHERE table_name='BILLS';
KÕt qu¶ thu ®îc nh sau:
INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
BILL_CRE_SCO_FK_I CR_SCORE_STT
BILL_EMP_FK_I EMP_ID
Theo kÕt qu¶ trªn th× Index cét cust_id cña b¶ng Bills cha tån t¹i v× vËy cÇn t¹o Index cho cét CUST_ID cña b¶ng BILLS nh sau:
CREATE INDEX bill_cust_id ON bills(cust_id);
Sau khi t¹o c¸c Index th× ph¬ng thøc truy nhËp b¶ng BILLS chuyÓn tõ duyÖt toµn bé (FULL) trë thµnh duyÖt qua Index cã giíi h¹n (RANGE SCAN trong c©y ph©n tÝch lÖnh), thêi gian thùc hiÖn cña c¸c c©u lÖnh t¬ng øng ®îc lÖt kª trong b¶ng so s¸nh:
-
C©u lÖnh tÝnh ho¸ ®¬n dùa trªn b¶ng ho¸ ®¬n c¬ së (Based_Bill):
begin :X0 := SP_WBL_CALCULATE_BASED_BILL(:CUST_ID_, :SUB_CUST_ID_,
:BRANCH_CODE_, :PERIOD_, :YEAR_, :NAME_, :CUSTOMER_NO_, :CONTRACT_NO_,
:ACRINYM_, :BC_NAME_, :CURRENCY_ID_, :PERCENTAGE_, :ADDRESS_, :SIGN_,
:BILL_STATUS_, :OLD_STT_, :OLD_PERIOD_, :OLD_YEAR_, :BILL_NO_);
end;
B¶ng so s¸nh thêi gian thùc hiÖn procedure tríc vµ sau khi t¹o Index
Index
|
Call count
|
cpu
|
elapsed
|
disk
|
query
|
current
|
rows
|
Kh«ng
|
2
|
0.37
|
24.85
|
1308
|
1340
|
4
|
1
|
Cã
|
2
|
0.28
|
0.28
|
0
|
37
|
2
|
1
| -
C©u lÖnh t×m lîng níc ®iÒu chØnh, lîng níc tiªu thô vµ m· tr¹ng th¸i tõ b¶ng ho¸ ®¬n (Bill):
SELECT NVL(WATER_ADJUST,0),NVL(LNTT,0),STATUS_CODE FROM BILLS
WHERE CUST_ID = :b1 AND STT = :b2;
B¶ng so s¸nh thêi gian thùc hiÖn c©u lÖnh tríc vµ sau khi t¹o Index
Index
|
Call count
|
cpu
|
elapsed
|
disk
|
query
|
current
|
rows
|
Kh«ng
|
3
|
0.10
|
24.03
|
1308
|
1310
|
2
|
2
|
Cã
|
3
|
0.01
|
0.01
|
0
|
6
|
0
|
2
|
C©y ph©n tÝch lÖnh khi cha cã Index
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
9827 TABLE ACCESS (FULL) OF 'BILLS'
C©y ph©n tÝch lÖnh khi ®· cã Index
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
0 TABLE ACCESS (BY ROWID) OF 'BILLS'
0 INDEX (RANGE SCAN) OF 'BILL_CUST_ID' (NON-UNIQUE)
-
C©u lÖnh chuyÓn m· tr¹ng th¸i cho c¸c ho¸ ®¬n ®· ®îc tÝnh:
UPDATE BILLS SET PAID_STATUS=4
WHERE CUST_ID = :b1 AND STT IN
(SELECT STT FROM BASED_BILLS WHERE CUST_ID = :b1
AND NVL(SUB_CUST_ID,0) = :b3 AND STT != :b4 AND PERIOD = :b5
AND BILLING_YEAR = :b6 );
B¶ng so s¸nh thêi gian thùc hiÖn c©u lÖnh tríc vµ sau khi t¹o Index
Index
|
Call count
|
cpu
|
elapsed
|
disk
|
query
|
current
|
rows
|
Kh«ng
|
2
|
0.05
|
29.00
|
1308
|
1316
|
5
|
3
|
Cã
|
2
|
0.04
|
0.32
|
0
|
41
|
4
|
4
|
C©y ph©n tÝch lÖnh khi cha cã Index
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
3 MERGE JOIN
4 SORT (JOIN)
9828 TABLE ACCESS (FULL) OF 'BILLS'
3 SORT (JOIN)
3 VIEW
3 SORT (UNIQUE)
4 TABLE ACCESS (BY ROWID) OF 'BASED_BILLS'
5 INDEX (RANGE SCAN) OF 'B_B_CUST_FK_I' (NON-UNIQUE)
C©y ph©n tÝch lÖnh khi ®· cã Index
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
0 NESTED LOOPS
0 VIEW
0 SORT (UNIQUE)
0 TABLE ACCESS (BY ROWID) OF 'BASED_BILLS'
0 INDEX (RANGE SCAN) OF 'B_B_CUST_FK_I' (NON-UNIQUE)
0 TABLE ACCESS (BY ROWID) OF 'BILLS'
0 INDEX (RANGE SCAN) OF 'BILL_CUST_ID' (NON-UNIQUE)
-
C©u lÖnh x¸c lËp gi¸ trÞ tiÒn thu ®iÒu chØnh vµ phô thu ®iÒu chØnh:
UPDATE BILLS SET MONEY_ADJUST=:b1,SURCHARGE_ADJUST=:b2
WHERE CUST_ID = :b3 AND STT = :b4;
B¶ng so s¸nh thêi gian thùc hiÖn c©u lÖnh tríc vµ sau khi t¹o Index
Index
|
Call count
|
cpu
|
elapsed
|
disk
|
query
|
current
|
rows
|
Kh«ng
|
2
|
0.02
|
28.28
|
1308
|
1312
|
3
|
1
|
Cã
|
2
|
0.03
|
0.03
|
0
|
8
|
1
|
1
|
C©y ph©n tÝch lÖnh khi cha cã Index
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
0 TABLE ACCESS (FULL) OF 'BILLS'
C©y ph©n tÝch lÖnh khi ®· cã Index
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
0 TABLE ACCESS (BY ROWID) OF 'BILLS'
0 INDEX (RANGE SCAN) OF 'BILL_CUST_ID' (NON-UNIQUE)
Chó thÝch: Sau khi thö nghiÖm tÝnh 3858 ho¸ ®¬n b¾t ®Çu tõ 03:39:25 ®Õn 04:47:48 th× xong. Tèc ®é tÝnh trung b×nh lµ 58 ho¸ ®¬n/ phót. Khi cha t¹o Index th× víi ®iÒu kiÖn nh vËy thêi gian ®Ó tÝnh lµ 173 phót (gÊp 2.5 lÇn).
2. Tèi u b»ng thªm chØ thÞ thùc hiÖn vµ thay ®æi c©u lÖnh SQL
C©u lÖnh chøa sub query:
-
VÝ dô ®Ó lÊy ra c¸c th«ng tin vÒ kh¸ch hµng thuéc chi nh¸nh Sµi Gßn víi ®iÒu kiÖn ®îc tÝnh ho¸ ®¬n lµ thuéc ®ît tÝnh ho¸ ®¬n ®ang tÝnh (BC_CODE = 'A5'), thuéc vÒ chi nh¸nh Sµi Gßn (BRANCH_ID = 3), ®ang l¾p ®¹t ®ång hå vµ ®ang ®îc cung cÊp níc (ACTIVE = 1, STATUS=1) cã thÓ dïng c©u lÖnh sau:
SELECT ID, NAME, CUSTOMER_NO, CONTRACT_NO, CURRENCY_ID, SORT_NAME, BRANCH_CODE, ADDRESS
FROM SNAP$_CUSTOMERS$SG
WHERE ID IN (SELECT CUST_ID FROM SNAP$_ABC$SG WHERE STATUS=1 AND BC_CODE='A5'
AND BRANCH_ID=3 AND ACTIVE=1);
Víi mçi hµng thuéc b¶ng CUTOMERS ®Òu ph¶i duyÖt lÇn lît øng víi b¶ng SNAP$_ABC$SG. Tæng sè thêi gian thùc hiÖn lµ:
call count cpu elapsed disk query current rows
total 269 5.28 9.46 1444 52546 3 4002
C©y ph©n tÝch lÖnh:
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
13965 FILTER
13965 TABLE ACCESS (FULL) OF 'SNAP$_CUSTOMERS$SG'
4002 FILTER
9111 TABLE ACCESS (BY ROWID) OF 'SNAP$_ABC$SG'
19073 INDEX (RANGE SCAN) OF 'ASS_BCY_CUST_FK_I_SG' (NON-UNIQUE)
-
C©u lÖnh t¬ng ®¬ng sö dông phÐp kÕt nèi:
SELECT ID, NAME, CUSTOMER_NO, CONTRACT_NO, CURRENCY_ID, SORT_NAME,
BRANCH_CODE, ADDRESS
FROM SNAP$_CUSTOMERS$SG, SNAP$_ABC$SG WHERE STATUS=1 AND ID=CUST_ID
and BC_CODE='A5' AND BRANCH_ID=3 AND ACTIVE=1;
Thêi gian thùc hiÖn:
call count cpu elapsed disk query current rows
total 269 1.99 2.44 309 20632 2 4002
C©y ph©n tÝch lÖnh:
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
4002 NESTED LOOPS
9113 TABLE ACCESS (FULL) OF 'SNAP$_ABC$SG'
4003 TABLE ACCESS (BY ROWID) OF 'SNAP$_CUSTOMERS$SG'
8006 INDEX (RANGE SCAN) OF 'CUST_PK_I_SG' (NON-UNIQUE)
Chia sẻ với bạn bè của bạn: |