Phô lôc C giíi thiÖu qu¸ tr×nh tèi ­u c©u lÖnh kÕt nèi trong phÇn mÒm tra cøu v¨n b¶n ph¸p qui

Phô lôc C giíi thiÖu qu¸ tr×nh tèi ­u c©u lÖnh kÕt nèi trong phÇn mÒm tra cøu v¨n b¶n ph¸p qui.

Lý do: viÖc kÕt nèi nhiÒu b¶ng lµ sù kÕt nèi tõng cÆp hai b¶ng mét cho ®Õn khi trë thµnh mét b¶ng duy nhÊt. C¸c v¨n b¶n cña mét ®Þa ph­¬ng ®Òu cã mét m· ®Þa ph­¬ng duy nhÊt trong khi ®ã ORACLE lu«n t¹o cÆp kÕt nèi gi÷a b¶ng VB vµ b¶ng DMDP (Danh môc ®Þa ph­¬ng) ®Ó lÊy ra tªn ®Þa ph­¬ng råi míi chän theo m· VB. KÕt qu¶ lµ ph¶i tiÕn hµnh s¾p xÕp vµ duyÖt toµn bé nguån VB cña ®Þa ph­¬ng ®· chØ ra.

C¸ch gi¶i quyÕt: LÊy tªn ®Þa ph­¬ng øng víi m· ®Þa ph­¬ng trong tham sè vµo cña procedure. Nh­ vËy thêi gian chuyÓn tõ thùc hiÖn phÐp kÕt nèi (1.16 gi©y) xuèng cßn thùc hiÖn phÐp chän (xÊp xØ 0 gi©y).

• 
  C©u lÖnh ban ®Çu:
  

V_VBCC.SO, V_VBCC.KY_HIEU, V_VBCC.NGAY_PH, V_VBCC.TRICH_YEU

FROM V_CCTH V_CCTH, VB V_VBCC,DMDP DMDP1, DMLVB DMLVB1, DMCD DMCD1

WHERE V_VBCC.MA_VB = V_CCTH.MA_CC

and V_VBCC.MA_DP = V_CCTH.MA_DP_CC

and DMDP1.MA_DP = V_VBCC.MA_DP

and DMLVB1.MA_LOAI_VB = V_VBCC.MA_LOAI_VB

and DMCD1.MA_CHU_DE = V_VBCC.MA_CHU_DE

and (V_CCTH.MA_DP_VB = 'VPCP')

and (V_CCTH.MA_VB = 4404)

ORDER BY 7;

Thêi gian thùc hiÖn

call count cpu elapsed disk query current rows

total 5 1.14 1.16 375 8590 2 2

------- ---------------------------------------------------

0 SELECT STATEMENT GOAL: CHOOSE

2 SORT (ORDER BY)

2 NESTED LOOPS

2 NESTED LOOPS

5 HASH JOIN

2 VIEW OF 'V_CCTH'

2 SORT (UNIQUE)

2 UNION-ALL

3 INDEX (RANGE SCAN) OF 'CCTW_PK' (UNIQUE)

0 TABLE ACCESS (BY ROWID) OF 'SNAP$_S_CCDP_BTC'

1 INDEX (RANGE SCAN) OF 'I_S_CCBTC' (NON-UNIQUE)

4105 NESTED LOOPS

4105 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'VB'

4105 INDEX (UNIQUE SCAN) OF 'DMDP_PK' (UNIQUE)

2 INDEX (UNIQUE SCAN) OF 'DMLVB_PK' (UNIQUE)

2 INDEX (UNIQUE SCAN) OF 'DMCD_PK' (UNIQUE)

• 
  C©u lÖnh ban ®Çu ®­îc chuyÓn ®æi thµnh hai c©u lÖnh con nh­ sau
  

V_VBTH.MA_LOAI_VB,DMLVB3.TEN_LOAI_VB,V_VBTH.MA_CHU_DE,DMCD3.MA_LOAI_CD,

DMCD3.TEN_CHU_DE,V_VBTH.SO,V_VBTH.KY_HIEU,V_VBTH.NGAY_PH,V_VBTH.NGUOI_KY,

V_VBTH.TRICH_YEU,V_VBTH.TOAN_VAN,V_VBTH.MA_VB

FROM V_VBTH V_VBTH,DMDP DMDP,DMCD DMCD3,DMLVB DMLVB3

WHERE V_VBTH.MA_VB = :b1 AND V_VBTH.MA_DP = :b2 AND DMDP.MA_DP = V_VBTH.MA_DP

AND DMCD3.MA_CHU_DE = V_VBTH.MA_CHU_DE AND DMLVB3.MA_LOAI_VB = V_VBTH.MA_LOAI_VB;

Thêi gian thùc hiÖn

call count cpu elapsed disk query current rows

total 3 0.00 0.00 0 11 2 19

------- ---------------------------------------------------

0 SELECT STATEMENT GOAL: CHOOSE

1 NESTED LOOPS

1 NESTED LOOPS

1 HASH JOIN

1 TABLE ACCESS (BY ROWID) OF 'DMDP'

2 INDEX (RANGE SCAN) OF 'DMDP_PK' (UNIQUE)

1 VIEW OF 'V_VBTH'

1 SORT (UNIQUE)

1 UNION-ALL

1 TABLE ACCESS GOAL: ANALYZED (BY ROWID) OF 'VB'

1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'VB_PK'

(UNIQUE)

'SNAP$_S_VBDP_BTC'

1 TABLE ACCESS (BY ROWID) OF 'DMCD'

1 INDEX (UNIQUE SCAN) OF 'DMCD_PK' (UNIQUE)

1 TABLE ACCESS (BY ROWID) OF 'DMLVB'

1 INDEX (UNIQUE SCAN) OF 'DMLVB_PK' (UNIQUE)

********************************************************************************

SELECT TEN_DP FROM DMDP DMDP WHERE DMDP.MA_DP = :b1;

call count cpu elapsed disk query current rows

total 3 0.00 0.00 0 2 0 2

Rows Execution Plan

------- ---------------------------------------------------

0 SELECT STATEMENT GOAL: CHOOSE

1 TABLE ACCESS (BY ROWID) OF 'DMDP'

1 INDEX (UNIQUE SCAN) OF 'DMDP_PK' (UNIQUE)

Phô lôc D Tèi ­u ho¸ b»ng sö dông Index kÕt hîp víi Hint Text Trong phÇn mÒm Tra cøu V¨n b¶n Ph¸p qui

Sau mét thêi gian thö nghiÖm, ph©n tÝch kÕt qu¶ ®¹t ®­îc vµ ch­a ®¹t ®­îc cña c¸c lÇn tèi ­u c©u lÖnh SQL trong phÇn mÒm Tra cøu V¨n b¶n ph¸p qui chóng t«i ®· rót ra ®­îc ph­¬ng thøc tèi ­u tèt nhÊt cho phÇn mÒm nµy lµ sö dông Index phøc kÕt hîp víi Hint Text.

• 
  Sö dông Index phøc:
  

• 
  Sö dông Hint Text:
  

Do ®ã gi¶i ph¸p ®Ó kh¾c phôc lµ thªm chØ thÞ thùc hiÖn kÕt nèi ORDERD ®Ó c¸c b¶ng kÕt nèi theo thø tù xuÊt hiÖn sau mÖnh ®Ò FROM.

Nh­ vËy sau khi kÕt hîp c¶ hai ph­¬ng ph¸p trªn th× sè lÇn duyÖt ®Ó truy xuÊt tíi v¨n b¶n sÏ lµ Ýt nhÊt (qua index trùc tiÕp) vµ sè l­îng c¸c hµng tham gia vµo kÕt nèi lµ nhá nhÊt (mét hµng) do ®ã thêi gian t¨ng lªn rÊt nhiÒu lÇn v× thao t¸c kÕt nèi tõ n v¨n b¶n (toµn bé v¨n b¶n cña mét ®Þa ph­¬ng) cßn l¹i chØ kÕt nèi mét v¨n b¶n ®· t×m ra.

D­íi ®©y lµ mét vÝ dô trong hµng chôc c©u lÖnh nh­ vËy.

• 
  C©u lÖnh ch­a ®­îc tèi ­u
  

V_VBCC.SO,V_VBCC.KY_HIEU,V_VBCC.NGAY_PH,V_VBCC.NGUOI_KY,V_VBCC.TRICH_YEU,

V_VBCC.TOAN_VAN,V_CCTH.MA_DP_VB,V_CCTH.MA_VB

FROM

:b2 AND V_CCTH.MA_DP_VB = :b3 AND V_CCTH.MA_VB = :b4 AND V_VBCC.MA_VB =

V_CCTH.MA_CC AND V_VBCC.MA_DP = V_CCTH.MA_DP_CC

Thêi gian thùc hiÖn

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.02 0.02 0 0 0 0

Execute 2 0.06 11.09 0 0 2 6

Fetch 1 1.85 10.51 520 8245 1954 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4 1.93 21.62 520 8245 1956 7

C©y ph©n tÝch lÖnh

Rows Execution Plan

------- ---------------------------------------------------

0 SELECT STATEMENT GOAL: CHOOSE

1 MERGE JOIN

15 TABLE ACCESS (BY ROWID) OF 'SNAP$_S_VBDP_BTC'

16 INDEX (RANGE SCAN) OF 'IH_S_BTC_DPVB' (NON-UNIQUE)

1 SORT (JOIN)

1 VIEW OF 'V_CCTH'

1 SORT (UNIQUE)

1 UNION-ALL

1 INDEX (UNIQUE SCAN) OF 'CCTW_PK' (UNIQUE)

0 TABLE ACCESS (BY ROWID) OF 'SNAP$_S_CCDP_BTC'

1 INDEX (RANGE SCAN) OF 'I_S_CCBTC' (NON-UNIQUE)

• 
  C©u lÖnh sau khi tèi ­u
  

V_VBCC.MA_CHU_DE,V_VBCC.SO,V_VBCC.KY_HIEU,V_VBCC.NGAY_PH,V_VBCC.NGUOI_KY,

V_VBCC.TRICH_YEU,V_VBCC.TOAN_VAN,V_CCTH.MA_DP_VB,V_CCTH.MA_VB

FROM

:b2 AND V_CCTH.MA_DP_VB = :b3 AND V_CCTH.MA_VB = :b4 AND V_VBCC.MA_VB =

V_CCTH.MA_CC AND V_VBCC.MA_DP = V_CCTH.MA_DP_CC

Thêi gian thùc hiÖn

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.11 0.17 1 0 1 0

Execute 1 0.03 0.21 0 0 0 3

Fetch 1 0.00 0.02 1 6 0 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 3 0.14 0.40 2 6 1 4

C©y ph©n tÝch lÖnh

Rows Execution Plan

------- ---------------------------------------------------

0 SELECT STATEMENT GOAL: CHOOSE

1 HASH JOIN

1 INDEX (UNIQUE SCAN) OF 'CCTW_PK' (UNIQUE)

1 SORT (UNIQUE)

1 UNION-ALL

1 TABLE ACCESS (BY ROWID) OF 'VB'

1 INDEX (UNIQUE SCAN) OF 'VB_PK' (UNIQUE)

0 TABLE ACCESS (BY ROWID) OF 'SNAP$_S_VBDP_BTC'