B. Ph©n tÝch c¸ch thi hµnh c©u lÖnh SQL

B. Ph©n tÝch c¸ch thi hµnh c©u lÖnh SQL

Ph©n tÝch c¸ch thùc hiÖn mét c©u lÖnh SQL cô thÓ vµ x©y dùng c©y ph©n tÝch.

Gi¶ sö cã c¸c b¶ng Nh©nViªn chøa c¸c th«ng tin vÒ Nh©nViªn nh­ L­¬ng, NghÒ, Tªn, M·Phßng ...v.v vµ b¶ng Phßng chøa M·Phßng vµ TªnPhßng ®Ó liªn kÕt víi b¶ng Nh©nViªn.

Gi¶ sö cã c©u lÖnh SQL sau:

SELECT TªnNh©nViªn, NghÒ, L­¬ng, TªnPhßng

FROM Nh©nViªn, Phßng

WHERE Nh©nViªn.m·Phßng = Phßng.M·Phßng AND NOT EXITSTS ( SELECT * FROM BËcL­¬ng WHERE Nh©nViªn.L­¬ng BETWEEN BËc1 AND BËc2);

Do M·Phßng lµ kho¸ chÝnh nªn c¸c hµng trong b¶ng Phßng sÏ ®­îc truy nhËp b»ng ROWID cßn b¶ng nh©n viªn do kh«ng cã Index trªn TªnNh©nViªn nªn b¶ng nµy sÏ ®­îc truy nhËp b»ng ph­¬ng ph¸p duyÖt toµn bé.

Trong s¬ ®å nµy th× mçi khèi øng víi mét b­íc thùc hiÖn vµ con sè trong khèi chØ thø tù ®­îc thùc hiÖn cña khçi. Mçi khèi sÏ lÊy d÷ liÖu tõ khèi kh¸c (mµu tr¾ng) hoÆc trùc tiÕp tõ d÷ liÖu trªn ®Üa (mµu ®en). Sau khi d÷ liÖu ®­îc xö lý ë mçi khèi th× sÏ tr¶ l¹i mét tËp hîp c¸c hµng (®­îc gäi lµ nguån d÷ liÖu Row Source) cho khèi ë møc trªn vµ nÕu lµ khèi trªn cïng th× sÏ tr¶ l¹i cho ng­êi dïng hoÆc ch­¬ng tr×nh thùc hiÖn c©u lÖnh.

Theo s¬ ®å trªn, Oracle sÏ thùc hiÖn b­íc 3 vµ tr¶ l¹i tõng dßng mét cho b­íc 2. T­¬ng øng víi mçi dßng ë b­íc 3 Oracle thùc hiÖn c¸c c«ng viÖc sau:

- Oracle thùc hiÖn b­íc 5 vµ tr¶ l¹i ROWID cho b­íc 4.

- Oracle thùc hiÖn b­íc 4 tr¶ l¹i gi¸ trÞ hµng cho b­íc 2.

- Oracle thùc hiÖn b­íc 2 b»ng c¸ch lÊy mçi hµng ë b­íc 3 vµ mét hµng ë b­íc 4 ghÐp thµnh mét vµ tr¶ l¹i kÕt qu¶ cho b­íc 1.

- Oracle sÏ thùc hiÖn b­íc 6 vµ sÏ tr¶ l¹i c¸c hµng cho b­íc 1 nÕu cã.

- §Ó thùc hiÖn b­íc 1, Oracle sÏ kiÓm tra ë b­íc 6 cã tr¶ l¹i hµng nµo kh«ng, nÕu cã th× Oracle sÏ tr¶ l¹i hµng tõ b­íc 2 cho ng­êi dïng.

Chó ý r»ng øng víi mçi hµng tr¶ l¹i ë b­íc 3 th× Oracle sÏ ph¶i mét lÇn thùc hiÖn b­íc 5, 4, 2, 6, 1. Víi c¸c b­íc mµ chØ ®ßi hái mét dßng tõ c¸c nh¸nh con trong c©y ph©n tÝch th× rÊt tèt bëi v× Oracle cã thÓ tiÕn hµnh thùc hiÖn b­íc ®ã ngay vµ nh­ vËy c©y lÖnh sÏ ®­îc thu gän l¹i tõng thêi ®iÓm. Nãi chung th× c¸c b­íc chØ yªu cÇu truy nhËp mét hµng tõ b¶ng lµ truy nhËp duy nhÊt trùc tiÕp tõ b¶ng, tõ Index, tõ kÕt nèi Nested-Loop vµ tõ bé läc cßn c¸c b­íc yªu cÇu tÊt c¶ c¸c hµng hµng ®ång thêi lµ s¾p xÕp, kÕt nèi b»ng s¾p xÕp trén, c¸c hµm lµm viÖc víi nhãm hµng vµ phÐp hîp c¸c tËp hîp.

Dùa trªn c©y ph©n tÝch lÖnh cã thÓ ®¸nh gi¸ hoÆc s¾p xÕp l¹i c©u lÖnh ®Ó chän ra c¸ch thøc thùc hiÖn tèi ­u. Ngoµi ra còng cã thÓ chuyÓn c©y ph©n tÝch nµy c¸c chó gi¶i nh­ sau:

STT (ID) Thao t¸c (Operation) Tuú chän (Option) §èi t­îng t¸c ®éng (Object-name)

0 LÖnh SELECT

1 Bé läc

2 Nested-Loop

3 Truy nhËp b¶ng DuyÖt toµn bé Nh©nViªn

4 Truy nhËp b¶ng B»ng ROWID Phßng

5 Truy nhËp b¶ng B»ng kho¸ duy nhÊt PK_MAPHONG

6 Truy nhËp b¶ng B»ng duyÖt toµn bé BËcL­¬ng

C¸c chó gi¶i nµy cã thÓ ®­îc liÖt kª ra bëi Oracle b»ng c¸ch sö dông lÖnh EXPLAIN PLAN ®Ó mçi khi thùc hiÖn c©u lÖnh th× Oracle sÏ ®­a ra c¸c chó gi¶i vÒ c©y ph©n tÝch ®­îc thùc hiÖn.

C. C¸c b­íc tèi ­u ho¸

Víi bÊt k× mét c©u lÖnh SQL nµo ®­îc thùc hiÖn bëi Oracle th× bé tèi ­u ho¸ sÏ lµm c¸c c«ng viÖc sau:

• 
  §Þnh trÞ c¸c biÓu thøc vµ ®iÒu kiÖn: Oracle sÏ tiÕn hµnh tÝnh to¸n c¸c biÓu thøc vµ ®iÒu kiÖn thµnh c¸c gi¸ trÞ h»ng mét c¸ch tèi ®a.
  
• 
  ChuyÓn ®æi c©u lÖnh: Víi nh÷ng c©u lÖnh phøc t¹p th× Oracle sÏ tiÕn hµnh chuyÓn thµnh sù kÕt hîp cña c¸c c©u lÖnh ®¬n gi¶n h¬n, vÝ dô nh­ c©u hái cã sö dông Query bé phËn (subquery) th× sÏ ®­îc chuyÓn thµnh kÕt nèi cña hai c©u hái t­¬ng ®­¬ng nÕu cã thÓ.
  
• 
  KÕt hîp View vµo trong c©u lÖnh: View thùc chÊt lµ mét c©u lÖnh SELECT, do vËy nÕu thùc hiÖn mét c©u lÖnh SELECT trªn View th× d÷ liÖu sÏ bÞ duyÖt hai lÇn. NÕu kÕt hîp View vµ c©u lÖnh SELECT th× sÏ chØ ph¶i duyÖt mét lÇn.
  
• 
  Chän c¸ch tèi ­u ho¸: Chän c¸ch tèi ­u dùa trªn luËt Rule-based hoÆc Cost-based.
  
• 
  Chän ph­¬ng thøc truy nhËp: Chän c¸ch truy nhËp nhanh nhÊt tíi hµng cña b¶ng nÕu cã nhiÒu c¸ch truy nhËp.
  
• 
  Chän thø tù cña kÕt nèi: NÕu c©u lÖnh sö dông kÕt nèi nhiÒu h¬n hai b¶ng th× Oracle sÏ tiÕn hµnh t×m ra cÆp kÕt nèi thø nhÊt, råi cÆp tiÕp theo ...v.v.
  
• 
  Chän thao t¸c kÕt nèi: Víi bÊt k× c©u lÖnh nµo th× còng cÇn ph¶i cã mét thao t¸c ®Ó thùc hiÖn phÐp kÕt nèi.
  

1. §Þnh gi¸ biÓu thøc hoÆc ®iÒu kiÖn

1.1. ChuyÓn thµnh c¸c h»ng sè (Consts)

SELECT TªnNh©nViªn, L­¬ng FROM Nh©nViªn WHERE L­¬ng>20,000/10;

SELECT TªnNh©nViªn, L­¬ng FROM Nh©nViªn WHERE L­¬ng>2,000;

SELECT TªnNh©nViªn, L­¬ng FROM Nh©nViªn WHERE L­¬ng*10>20,000;

Râ rµng c¶ ba c©u lÖnh ®Òu tr¶ l¹i cïng mét kÕt qu¶ tuy nhiªn c©u lÖnh thø nhÊt vµ thø ba ph¶i thªm mét phÐp tÝnh ®Ó øng víi mçi phÐp so s¸nh so víi c©u lÖnh thø hai. Oracle sÏ tù ®éng chuyÓn c©u lÖnh thø nhÊt trë thµnh c©u lÖnh thø hai cßn c©u lÖnh thø ba th× kh«ng ®¬n gi¶n ®èi víi Oracle ®Ó thµnh c©u lÖnh thø hai. ChÝnh v× lý do nµy mµ nh÷ng ng­êi ph¸t triÓn phÇn mÒm nªn tù chuyÓn ®iÒu kiÖn so s¸nh thµnh d¹ng mét cét víi mét h»ng sè trong bÊt k× tr­êng hîp nµo cã thÓ h¬n lµ mét ®iÒu kiÖn trong ®ã cã chøa mét biÓu thøc víi mét cét.

1.2. To¸n tö LIKE

ChuyÓn biÓu thøc: TªnNh©nViªn LIKE 'SMITH'

thµnh biÓu thøc t­¬ng ®­¬ng: TªnNh©nViªn = 'SMITH'

C¸c biÓu thøc kiÓu nµy chØ ®­îc chuyÓn khi mµ biÓu thøc so s¸nh víi c¸c biÕn cã kÝch th­íc (size) kh«ng cè ®Þnh. NÕu nh­ TªnNh©nViªn cã kiÓu CHAR(10) th× biÓu thøc trªn kh«ng ®­îc chuyÓn v× TªnNh©nViªn cã kÝch th­íc cè ®Þnh.

1.3. To¸n tö IN

VÝ dô:

thµnh: TªnNh©nViªn='SMITH' OR TªnNh©nViªn='KING' OR TªnNh©nViªn='JONES'

1.4. To¸n tö ANY hoÆc SOME

VÝ dô:

thµnh biÓu thøc: L­¬ng>:L­¬ng1 OR L­¬ng>:L­¬ng2

vµ chuyÓn: x> ANY (SELECT L­¬ng FROM Nh©nViªn WHERE NghÒ='Ph©n tÝch viªn')

thµnh biÓu thøc t­¬ng ®­¬ng lµ:

EXITST (SELECT L­¬ng FROM Nh©nViªn WHERE NghÒ='Ph©n tÝch viªn' and x>L­¬ng)

1.5. To¸n tö ALL

VÝ dô: L­¬ng>ALL(:L­¬ng1, :L­¬ng2)

thµnh L­¬ng> :L­¬ng1 AND L­¬ng > :L­¬ng2

Tõ x>ALL ( SELECT L­¬ng FROM Nh©nViªn WHERE M·Phßng=10;

thµnh NOT (x<= ANY ( SELECT L­¬ng FROM Nh©nViªn WHERE M·Phßng=10))

vµ sau ®ã sÏ sö dông luËt ¸p dông víi to¸n tö ANY thµnh c©u lÖnh sau:

NOT EXISTS ( SELECT L­¬ng FROM Nh©nViªn WHERE M·Phßng=10 AND x<= L­¬ng)

1.6. To¸n tö BETWEEN

VÝ dô:

thµnh biÓu thøc: L­¬ng>=2,000 AND L­¬ng<=3,000

1.7. To¸n tö NOT

VÝ dô: ChuyÓn biÓu thøc:

NOT M·Phßng = ( SELECT M·Phßng FROM Nh©nViªn WHERE TªnNh©nViªn='TAYLOR')

thµnh biÓu thøc t­¬ng ®­¬ng:

M·Phßng <> ( SELECT M·Phßng FROM Nh©nViªn WHERE TªnNh©nViªn='TAYLOR')

Th«ng th­êng mét ®iÒu kiÖn cã chøa to¸n tö NOT th­êng cã nhiÒu c¸ch viÕt. Oracle chÊp nhËn viÖc chuyÓn ®æi nh÷ng ®iÒu kiÖn nh­ vËy thµnh nh÷ng ®iÒu kiÖn phñ ®Þnh thµnh phÇn, thËm chÝ ngay c¶ viÖc kÕt qu¶ cã chøa nhiÒu NOT h¬n.

VÝ dô c¸c biÓu thøc sau chøa to¸n tö NOT cã nghÜa t­¬ng ®­¬ng:

NOT ( L­¬ng<1,000 OR Th­ëng IS NULL)

NOT L­¬ng<1,000 AND Th­ëng IS NOT NULL

L­¬ng>=1,000 AND Th­ëng IS NOT NULL

1.8. ChuyÓn tiÕp

C¸c ®iÒu kiÖn ®ã cã d¹ng: WHERE Cét1 AND Cét1=Cét2

®­îc chuyÓn thµnh: WHERE Cét1 AND Cét2

Trong ®ã cã thÓ lµ: =,!=, ^=, <, <>, >,<=, >=.

H»ng sè lµ bÊt k× gi¸ trÞ h»ng nµo víi to¸n tö, hµm SQL, kÝ tù, biÕn kÕt hîp hoÆc biÕn.

VÝ dô c©u lÖnh:

SELECT * FROM Nh©nViªn, Phßng

WHERE Nh©nViªn.M·Phßng=20 AND Nh©nViªn.M·Phßng=Phßng.M·Phßng

®­îc chuyÓn thµnh c©u lÖnh cã nghÜa t­¬ng ®­¬ng:

SELECT * FROM Nh©nViªn, Phßng

WHERE Nh©nViªn.M·Phßng=20 AND Phßng.M·Phßng=20;

Chó ý r»ng d¹ng ®iÒu kiÖn sau sÏ kh«ng ®­îc chuyÓn:

WHERE Cét1 Cét3 AND Cét1=Cét2 thµnh ®iÒu kiÖn Cét2 Cét3.

C¸ch thøc nµy còng chØ ®­îc dïng trong tèi ­u dùa vµo luËt Cost-based.

2. ChuyÓn d¹ng c©u lÖnh

+ ChuyÓn nh÷ng c©u lÖnh chøa OR thµnh nh÷ng c©u lÖnh ghÐp nèi víi nhau b»ng UNION ALL

+ ChuyÓn c¸c c©u lÖnh phøc hîp thµnh c©u lÖnh kÕt nèi.

2.1. ChuyÓn nh÷ng c©u lÖnh chøa OR thµnh nh÷ng c©u lÖnh ghÐp nèi

+ NÕu mçi ®iÒu kiÖn t¹o ra mét c¸ch truy nhËp b»ng Index th× c©u lÖnh cã thÓ ®­îc chuyÓn d¹ng. Oracle sÏ chän mét c©y ph©n tÝch sao cho truy nhËp b¶ng nhiÒu lÇn b»ng c¸ch Index kh¸c nhau sau ®ã nèi c¸c kÕt qu¶ l¹i víi nhau.

+ NÕu bÊt k× mét ®iÒu kiÖn nµo ®ßi hái ph¶i duyÖt toµn bé do kh«ng cßn c¸ch truy nhËp nµo kh¸c th× Oracle sÏ kh«ng chuyÓn d¹ng c©u lÖnh nµy (bëi v× lóc ®ã sÏ cÇn h¬n 1 lÇn duyÖt toµn bé b¶ng). Oracle sÏ tiÕn hµnh duyÖt toµn bé b¶ng vµ kiÓm tra mçi hµng cã tho¶ m·n mét trong c¸c ®iÒu kiÖn cña c©u lÖnh hay kh«ng.

Gi¶ sö r»ng ®· cã Index trªn cét NghÒ vµ cét M·Phßng. C©u lÖnh sau:

SELECT * FROM Nh©nViªn WHERE NghÒ='Th­ kÝ' OR M·Phßng=10;

®­îc chuyÓn thµnh c©u lÖnh cã nghÜa t­¬ng ®­¬ng

SELECT * FROM Nh©nViªn WHERE NghÒ='Th­ kÝ'

UNION ALL

SELECT * FROM Nh©nViªn WHERE M·Phßng=10;

NÕu sö dông luËt tèi ­u Rule-based th× c©u lÖnh nµy ®­îc chuyÓn bëi v× mçi c©u lÖnh thµnh phÇn cã thÓ sö dông mét Index. LuËt Rule-based cho r»ng thùc hiÖn hai c©u lÖnh thµnh phÇn truy nhËp b»ng Index sÏ nhanh h¬n lµ mét c©u lÖnh duyÖt toµn bé. Cßn nÕu sö dông luËt Cost-based th× Oracle sÏ ®¸nh ­íc l­îng “gi¸” ph¶i tr¶ øng víi mçi c©u lÖnh vµ sÏ quyÕt ®Þnh chän c©u lÖnh nµo sÏ ®­îc thùc hiÖn .

2.2. ChuyÓn c¸c c©u lÖnh phøc hîp thµnh c©u lÖnh kÕt nèi

Gi¶ sö chän ra c¸c cét trong b¶ng TµiKho¶n mµ cã M·Kh¸chHµng n»m trong b¶ng Kh¸chHµng b»ng c©u lÖnh:

SELECT * FROM TµiKho¶n WHERE M·Kh¸chHµng IN (SELECT M·Kh¸chHµng FROM Kh¸chHµng);

NÕu M·Kh¸chHµng lµ kho¸ chÝnh hoÆc kho¸ duy nhÊt th× c©u lÖnh trªn cã thÓ ®­îc chuyÓn thµnh c©u lÖnh sau ®©y v× nã ®¶m b¶o kÕt qu¶ cña c©u lÖnh míi gièng kÕt qu¶ trong c©u lÖnh ban ®Çu:

SELECT TµiKho¶n.* FROM TµiKho¶n, Kh¸chhµng WHERE TµiKho¶n.M·Kh¸chHµng=Kh¸chHµng.M·Kh¸chHµng.

Tuy nhiªn c©u lÖnh nµy l¹i kh«ng thÓ chuyÓn ®­îc thµnh c©u lÖnh kÕt nèi kiÓu nh­ vËy:

SELECT * FROM Nh©nViªn WHERE L­¬ng> ( SELECT AVG(L­¬ng) FROM Nh©nViªn).

v× c©u lÖnh ®­îc chuyÓn d¹ng:

SELECT * FROM Nh©nViªn WHERE L­¬ng>AVG(L­¬ng);

cho kÕt qu¶ kh¸c h¼n c©u lÖnh ban ®Çu.

Chó ý r»ng c¸c c©u lÖnh phøc hîp cã c©u lÖnh thµnh phÇn sö dông hµm t¸c ®éng trªn nhiÒu hµng th× kh«ng thÓ chuyÓn ®­îc. C¸c hµm ®ã lµ AVG, COUNT, SUM, MAX, MIN.

3. Tèi ­u ho¸ c©u lÖnh truy nhËp trªn VIEW

• 
  KÕt hîp View vµo trong c©u lÖnh sau ®ã tèi ­u c©u lÖnh míi nµy.
  
• 
  KÕt hîp c©u lÖnh vµo trong View sau ®ã tèi ­u c©u lÖnh míi nµy.
  
• 
  Thay v× ph¶i lÊy ra tÊt c¶ c¸c hµng, c¸c cét tõ View, Oracle sÏ chØ lÊy ra tËp hîp c¸c hµng, c¸c cét víi c©u lÖnh yªu cÇu.
  

3.1. KÕt hîp View vµo c©u lÖnh

VÝ dô: T¹o mét View chøa tÊt c¶ c¸c nh©n viªn cã m· Phßng lµ 10 tõ b¶ng Nh©nViªn

CREATE VIEW Phßng10 AS SELECT M·Nh©nViªn, TªnNh©nViªn, NghÒ, L­¬ng, Th­ëng, M·Phßng FROM Nh©nViªn WHERE M·Phßng=10;

§Ó chän ra tÊt c¶ c¸c M·Nh©nViªn>7800 tõ VIEW Phßng10 b»ng c©u lÖnh:

SELECT M·Nh©nViªn FROM Phßng10 WHERE M·Nh©nViªn>7800;

th× c©u lÖnh míi sÏ lµ:

SELECT M·Nh©nViªn FROM Nh©nViªn WHERE M·Phßng=10 AND M·Nh©nViªn>7800;

NÕu cã Index trªn cét M·Phßng vµ M·Nh©nViªn th× c©u lÖnh míi sÏ lµm cho ph­¬ng thøc truy nhËp b»ng Index s½n dïng.

3.2. KÕt hîp c©u lÖnh vµo trong View sau ®ã tèi ­u c©u lÖnh míi nµy

VÝ dô:

vµ c©u lÖnh sau sÏ chän c¸c gi¸ trÞ trung b×nh, MIN, MAX cña l­¬ng cña phßng 10 tõ View NVPhßng;

SELECT * FROM NVPhßng WHERE M·Phßng=10;

C©u lÖnh sÏ ®­îc kÕt hîp vµo VIEW nh­ sau:

SELECT M·Nh©nViªn, AVG(L­¬ng) L­¬ngTB, MIN(L­¬ng) MinL­¬ng, MAX(L­¬ng) MaxL­¬ng FROM Nh©nViªn WHERE M·Phßng=10 GROUP BY M·Phßng;

VÝ dô c©u lÖnh:

SELECT AVG(L­¬ngTB), AVG(MinL­¬ng), AVG(MaxL­¬ng)

FROM NVPhßng;

sÏ cã c©u lÖnh kÕt qu¶ lµ:

SELECT AVG(AVG(L­¬ng)), AVG(AVG(L­¬ng)), AVG(MAX(L­¬ng))

FROM Nh©nViªn

GROUP BY M·Phßng;

3.3. Tèi ­u c¸c c©u lÖnh kh¸c truy nhËp trªn View

VÝ dô t¹o VIEW b»ng c©u lÖnh:

CREATE VIEW NVPhßng AS SELECT M·Nh©nViªn, Avg(L­¬ng) L­¬ngTB, MIN(L­¬ng) MinL­¬ng, MAX(L­¬ng) MaxL­¬ng FROM Nh©nViªn GROUP BY M·Phßng;

vµ c©u lÖnh truy xuÊt trªn View kh«ng thÓ chuyÓn ®­îc lµ:

4. Chän luËt ®Ó tèi ­u ho¸

• 
  C¸ch thøc vµ môc tiªu tèi ­u ho¸ cña Oracle chÞu ¶nh h­ëng cña nh÷ng tham sè sau:
  

• 
  Tham sè khi cµi ®Æt: OPTIMIZER_MODE.
  
• 
  Tr¹ng th¸i cña tõ ®iÓn d÷ liÖu.
  
• 
  Tham sè OPTIMIZER_GOAL cña lÖnh ALTER SESSION, tham sè nµy sÏ x¸c lËp ®Ì lªn sù x¸c lËp cña tham sè khi cµi ®Æt OPTIMIZER_MODE.
  
• 
  C¸c c©u lÖnh kh¸c.
  

• 
  Tham sè OPTIMIZER_MODE vµ OPTIMIZER_GOAL cã thÓ cã c¸c gi¸ trÞ sau ®©y:
  

• 
  CHOOSE: ®©y lµ gi¸ trÞ ngÇm ®Þnh cho tham sè nµy ®Ó x¸c ®Þnh viÖc chän c¸ch thøc tèi ­u rule-based hay cost-based. NÕu nh­ trong tõ ®iÓn d÷ liÖu cã chøa th«ng tin tr¹ng th¸i Ýt nhÊt lµ mét b¶ng th× ph­¬ng thøc cost-based sÏ ®­îc lùa chän vµ cã thÓ nhËn ®­îc c¸ch thøc thi hµnh c©u lÖnh tèt nhÊt. NÕu nh­ trong tõ ®iÓn d÷ liÖu kh«ng cã mét th«ng tin g× vÒ tr¹ng th¸i c¸c b¶ng th× c¸ch thøc tèi ­u rule-based sÏ ®­îc chän.
  
• 
  RULE: gi¸ trÞ sÏ lµm cho Oracle tiÕn hµnh tèi ­u tÊt c¶ c¸c c©u lÖnh SQL dùa vµo ph­¬ng thøc rule-based bÊt chÊp cã th«ng tin tr¹ng th¸i vÒ c¸c b¶ng trong tõ ®iÓn d÷ liÖu.
  
• 
  ALL_ROWS: ph­¬ng thøc tèi ­u cost-based sÏ ®­îc thùc hiÖn cho tÊt c¶ c¸c c©u lÖnh SQL bÊt kÓ th«ng tin tr¹ng th¸i trong tõ ®iÓn d÷ liÖu sao cho nguån tµi nguyªn sö dông lµ tèi thiÓu ®Ó hoµn thµnh c©u lÖnh.
  
• 
  FIRST_ROW: Ph­¬ng thøc tèi ­u cost-based ®­îc sö dông cho tÊt c¶ c¸c c©u lÖnh SQL sao cho thêi gian ®¸p øng lµ nhá nhÊt (nguån tµi nguyªn sö dông ®Ó tr¶ l¹i hµng ®Çu tiªn trong kÕt qu¶ lµ tèi thiÓu). Chó ý r»ng PL/SQL sÏ bá qua tham sè OPTIMIZED_MODE vµ FIRST_ROW khi cµi ®Æt.
  

• 
  NÕu nh­ sö dông ph­¬ng thøc tèi ­u ­íc l­îng gi¸ mµ kh«ng cã th«ng tin tr¹ng th¸i vÒ mét vµi b¶ng th× Oracle sÏ sö dông th«ng tin néi bé nh­ lµ sè khèi d÷ liÖu truy nhËp c¸c b¶ng.
  

5. Chän ph­¬ng thøc truy nhËp tíi hµng

6. Chän thao t¸c kÕt nèi

6.1. Chän c¸ch thùc hiÖn kÕt nèi víi luËt rule-based

Víi mçi vÞ trÝ trong thø tù kÕt nèi, Oracle sÏ chän ra b¶ng cã c¸ch truy nhËp tèt nhÊt vµ cø nh­ vËy cho c¸c vÞ trÝ tiÕp theo.

Víi mçi b¶ng trong thø tù kÕt nèi, Oracle còng tiÕn hµnh chän ra thao t¸c kÕt nèi víi b¶ng (hoÆc nguån d÷ liÖu) ®øng tr­íc. Thao t¸c kÕt nèi b»ng s¾p xÕp trén (Merge-sort) ®­îc sö dông lµm chuÈn (cã thø tù 12) cho c¸c c¸c qui t¾c tiÕp theo.

NÕu cã c¸ch truy nhËp lµ nhá h¬n 12 th× Oracle sÏ sö dông Nested-Loop víi b¶ng (hoÆc nguån d÷ liÖu) ®øng tr­íc lµm b¶ng ngoµi.

NÕu c¸ch truy nhËp lín h¬n 12 th× Oracle sÏ chän kÕt nèi theo ph­¬ng ph¸p trén víi hai b¶ng.

NÕu c¸ch truy nhËp nhá h¬n 12 vµ kh«ng ph¶i lµ kÕt nèi b»ng th× Oracle sÏ chän thao t¸c Nested-Loop víi b¶ng tr­íc ®ã nh­ lµ b¶ng ngoµi.

Oracle sÏ chän sè thao t¸c kÕt nèi Nested-Loop lµ tèi ®a nÕu b¶ng néi ®­îc truy nhËp b»ng Index vµ do vËy viÖc truy nhËp b¶ng néi nhiÒu lÇn sÏ ®¹t hiÖu qu¶ h¬n. Th«ng th­êng th× thø tù c¸c b¶ng trong kÕt nèi sÏ kh«ng gièng nh­ thø tù xuÊt hiÖn trong mÖnh ®Ò FROM. Thø tù xuÊt hiÖn c¸c b¶ng sÏ theo c¸c qui t¾c sau:

+Oracle sÏ chän c¸ch thi hµnh cã thao t¸c Nested-Loop lµ tèi thiÓu nÕu nh­ b¶ng néi ph¶i duyÖt toµn bé.

+NÕu ®iÒu ®ã vÉn cßn ch­a hiÖu qu¶ th× Oracle sÏ tiÕn hµnh chän c¸ch thùc hiÖn víi theo t¸c s¾p xÕp trén lµ tèi thiÓu.

+NÕu vÉn cßn ch­a hiÖu qu¶ th× Oracle sÏ chän c¸ch thùc hiÖn mµ b¶ng ®Çu tiªn trong thø tù kÕt nèi lµ cã c¸ch truy nhËp tèt nhÊt.

Oracle sÏ chän b¶ng ®Çu tiªn mµ ®­îc truy nhËp b»ng Index cét ®¬n vµ sÏ kÕt nèi theo kiÓu s¾p xÕp trén víi c¸c b¶ng kh¸c.

NÕu kh«ng ®­îc th× Oracle sÏ chän b¶ng ®Çu tiªn mµ truy nhËp theo ph­¬ng ph¸p duyÖt cã giíi h¹n vµ sè cét n»m trong phÇn ®Çu Index lµ tèi ®a.

+NÕu vÉn ch­a hiÖu qu¶ th× Oracle sÏ chän b¶ng xuÊt hiÖn sau cïng trong mÖnh ®Ò FROM.

6.2. Chän c¸ch thùc hiÖn kÕt nèi víi luËt tèi ­u Cost-based

• 
  Gi¸ cña mét thao t¸c Nested-Loop lµ gi¸ cña viÖc ®äc mçi hµng ®­îc chän ra tõ b¶ng ngo¹i vµ mçi tr­êng hîp so khíp cña b¶ng néi ®­îc n¹p vµo bé nhí. Oracle sÏ ®Þnh ra gi¸ nµy øng víi th«ng tin tr¹ng th¸i trong tõ ®iÓn d÷ liÖu.
  
• 
  Gi¸ cña mét thao t¸c kÕt nèi b»ng s¾p xÕp trén phÇn lín dùa trªn gi¸ ®Ó ®äc tÊt c¶ c¸c nguån d÷ liÖu vµo bé nhí vµ s¾p xÕp chóng.
  
• 
  Oracle còng ®Þnh gi¸ cña mét kÕt nèi theo s¾p xÕp trén dùa vµo c¸c yÕu tè kh¸c. VÝ dô nh­:
  

- ViÖc ®äc mét ®a khèi lín sÏ lµm gi¶m gi¸ cña thao t¸c kÕt nèi b»ng s¾p xÕp trén trong mét quan hÖ so víi mét kÕt nèi Nested-Loop. NÕu sè l­îng khã ®äc ®ång thêi trong mét lÇn truy xuÊt ®Üa(I/O) lµ lín th× mét truy nhËp qua Index cña b¶ng néi sÏ kÐm hiÖu qu¶ h¬n so víi duyÖt toµn bé. Sè lÇn ®äc ®a khèi ®­îc biÕt qua tham sè DB_FILE_MULTIBLOCK_READ_COUNT.

- Víi mét kÕt nèi ngoµi th× b¶ng trong kÕt nèi ngoµi ph¶i ®i sau nh÷ng b¶ng kh¸c trong thø tù ®iÒu kiÖn cña kÕt nèi. Oracle sÏ s¾p xÕp l¹i nh÷ng thø tù kÕt nèi vi ph¹m ®iÒu kiÖn nµy.

D. KÕt luËn vÒ ph­¬ng ph¸p tèi ­u trong Oracle

1. Tèi ­u b»ng viÖc thay ®æi c¸c thùc thÓ

B»ng viÖc thªm vµo mét thuéc tÝnh tõ b¶ng thø n vµo b¶ng thø nhÊt cã thÓ t¹o ra mét liªn kÕt trùc tiÕp gi÷a b¶ng 1 vµ b¶ng n mµ kh«ng cÇn ph¶i truy xuÊt c¸c b¶ng trung gian. Khi thay ®æi l¹i cÊu tróc c¸c b¶ng th× sÏ ph¸ vì d¹ng chuÈn d÷ liÖu 3NF nh­ng c©u lÖnh truy xuÊt trªn c¸c b¶ng sÏ nhanh h¬n. Do ®ã thao t¸c cÊu tróc l¹i c¸c b¶ng ®ßi hái ng­êi thiÕt kÕ ph¶i n¾m v÷ng c¸c qui t¾c vÒ nghiÖp vô còng nh­ ph¶i l­êng tr­íc ®­îc c¸c vÊn ®Ò n¶y sinh trong c¸c thao t¸c cËp nhËt d÷ liÖu.

VÝ dô:

§Ó biÕt ®­îc kh¸ch hµng thuéc chi nh¸nh nµo cña c«ng ty cã hai c¸ch thùc hiÖn nh­ sau:

C¸ch 2: Thªm cét branch_code vµo b¶ng CUSTOMERS. ChØ cÇn thùc hiÖn kÕt nèi gi÷a hai b¶ng CUSTOMERS vµ BRANCH sau ®ã chiÕu trªn cét BRANCH.name.

2. Tèi ­u c¸c Table space

VÝ dô trong hÖ qu¶n lý ho¸ ®¬n n­íc cã 8 Table space ®­îc t¹o nh­ sau:

3. Tèi ­u vïng nhí hÖ thèng SGA

• 
  §iÒu chØnh kÝch th­íc Share Pool qua th«ng sè shared_pool_size ë trong Parameter File. Trong Share Pool cã ba thµnh phÇn lµ Library cache, Data dictionary cache vµ Session information. KÝch th­íc cña Share Pool lµ thÝch hîp khi nã tho¶ m·n c¸c ®iÒu kiÖn d­íi ®©y.
  

SELECT (sum(reloads)/sum(pins))*100,’%’ FROM v$librarycache;

KÕt qu¶ thùc hiÖn víi Database Center 0.09366945%, nh­ vËy bé nhí dµnh cho Library Cache kh¸ nhiÒu.

- §é lín cña Data dictionary cache thÝch hîp khi tØ lÖ sè lÇn lÊy th«ng tin (GETS) chia cho sè lÇn th«ng tin kh«ng cã (GETMISSES) n»m trong kho¶ng 10% ®Õn 15% vµ cµng gÇn 0 cµng tèt. Gi¸ trÞ nµy ®­îc biÕt qua c©u lÖnh sau:

SELECT (SUM(GETMISSES)/SUM(GETS))*100,'%' FROM V$ROWCACHE;

KÕt qu¶ thùc hiÖn víi Database Center lµ 2.0352019%, nh­ vËy vïng bé nhí dµnh cho Data dictionary cache kh¸ nhiÒu.

• 
  §iÒu chØnh ®é lín cña Database Bufer Cache: Thµnh phÇn nµy ®­îc Oracle chia lµm nhiÒu Block mçi Block cã kÝch th­íc cè ®Þnh tuú thuéc hÖ ®iÒu hµnh vµ x¸c ®Þnh qua tham sè db_block_size trong Parameter File. B»ng c¸ch thay ®æi sè Block (qua tham sè db_block_buffers) sÏ thay ®æi ®­îc kÝch th­íc cña Database Bufer Cache.
  

CREATE TABLE kq (ratio number);

DECLARE

p NUMBER;

c NUMBER;

r NUMBER;

BEGIN

SELECT value INTO d FROM v$sysstat WHERE name='db block gets';

SELECT value INTO c FROM v$sysstat WHERE name='consistent gets';

r := p/(d+c);

r := r*100;

INSERT INTO kq VALUES (r);

EXCEPTION

WHEN ZERO_DIVIDE THEN

INSERT INTO kq VALUES (0);

END;

SELECT ratio,'%' FROM kq;

DROP TABLE kq;

KÕt qu¶ thùc hiÖn Database Center lµ 64.517194 %, ®iÒu nµy chøng tá vïng bé nhí dµnh cho Database Bufer Cache ®ñ tèt vµ nªn dµnh thªm vïng bé nhí tõ Share Pool sang.

• 
  KÝch th­íc cña Redo Log Buffer cã thÓ ®iÒu chØnh ®­îc b»ng tham sè log_buffer trong Parameter File.
  

4. Tèi ­u ho¸ sö dông Index

5. Tèi ­u b»ng thay ®æi c©u lÖnh SQL

SELECT TªnNh©nViªn, L­¬ng FROM Nh©nViªn WHERE L­¬ng*10>20,000;

thµnh: SELECT TªnNh©nViªn, L­¬ng FROM Nh©nViªn WHERE L­¬ng>2,000;

Trong phô lôc B sÏ giíi thiÖu vÒ qu¸ tr×nh tèi ­u b»ng thay ®æi c©u lÖnh trªn view cña phÇn mÒm Tra cøu v¨n b¶n ph¸p qui. Thêi gian thùc hiÖn c©u lÖnh ch­a tèi ­u lµ 41.43 gi©y, sau khi tèi ­u thêi gian thùc hiÖn cßn 2.36 gi©y (b»ng 4.8 %).

Trong phô lôc C sÏ giíi thiÖu vÒ qu¸ tr×nh tèi ­u b»ng thay ®æi c©u lÖnh kÕt nèi còng cña phÇn mÒm Tra cøu v¨n b¶n ph¸p qui. Thêi gian thùc hiÖn c©u lÖnh ch­a tèi ­u lµ 1.16 gi©y, sau khi tèi ­u thêi gian thùc hiÖn xÊp xØ 0 gi©y.

Nh­ vËy thùc tÕ lµ qua hai qu¸ tr×nh tèi ­u th× khi tra cøu th«ng tin vÒ V¨n b¶n ph¸p qui do V¨n phßng chÝnh phñ vµ UBND c¸c tØnh ban hµnh b»ng Web trªn m¹ng th× thêi gian ®¸p øng tõ 41.43 gi©y xuèng xÊp xØ 1.2 gi©y. Thêi gian ®¸p øng n»m ë qu¸ tr×nh Dial up vµ hiÓn thÞ cña tr×nh duyÖt Web.

Mét sè vÝ dô kh¸c vÒ qu¸ tr×nh tèi ­u sÏ ®­îc giíi thiÖu trong phÇn tèi ­u ho¸ HÖ tÝnh ho¸ ®¬n n­íc vµ qu¶n lý kh¸ch hµng cña c«ng ty cÊp n­íc Thµnh phè Hå ChÝ Minh (WSC).

6. Thªm c¸c chØ thÞ thùc hiÖn vµo trong c©u lÖnh

VÝ dô:

SELECT * FROM tab1 WHERE col1=’B’;

Gi¶ sö r»ng cét col1 cña b¶ng tab1 chøa c¸c kÝ tù tõ ‘A’ ®Õn ‘Z’ víi sè l­îng lµ 1000 dßng trong ®ã 75% sè dßng chøa kÝ tù ‘A’ vµ mçi kÝ tù cßn l¹i chiÕm 1% sè dßng cña b¶ng. Víi c©u lÖnh thø nhÊt dïng ph­¬ng ph¸p duyÖt toµn bé sÏ nhanh h¬n sö dông Index cßn trong c©u lÖnh thø hai th× sÏ dïng Index th× l¹i nhanh h¬n duyÖt toµn bé. Nh­ vËy b»ng viÖc sö dông chØ dÉn Oracle cã thÓ thùc hiÖn c©u lÖnh hiÖu qu¶ h¬n (bé tèi ­u sÏ sö dông Index cho c¶ hai c©u lÖnh).

C¸c chØ dÉn cã thÓ ¸p dông cho c¸c c©u lÖnh ®¬n SELECT, UPDATE, INSERT vµ viÕt theo có ph¸p sau:

hoÆc

C¸c chØ dÉn ph¶i ®i ngay sau c¸c tõ kho¸ DELETE, UPDATE, INSERT.

DÊu céng (+) lµ chØ thÞ b¸o cho Oracle biÕt r»ng b¾t ®Çu c¸c chØ dÉn th«ng dÞch vµ trong c¸c chØ dÉn nµy cã thÓ cã chøa c¸c chó thÝch. Cã c¸c kiÓu chØ dÉn thùc hiÖn chän luËt tèi ­u, chän c¸ch truy nhËp, chän thø tù kÕt nèi vµ chän thao t¸c kÕt nèi.

6.1. ChØ dÉn chän luËt tèi ­u

VÝ dô:

FIRST_ROWS: ChØ dÉn chän luËt tèi ­u ­íc l­îng gi¸ víi thêi gian ®¸p øng lµ tèi thiÓu (vµ tèi thiÓu nguån tµi nguyªn ®­îc sö dông ®Ó tr¶ l¹i hµng ®Çu tiªn). ChØ dÉn nµy sÏ Ðp Oracle chän ph­¬ng thøc truy nhËp b»ng Index thay cho duyÖt toµn bé (nÕu cã s½n Index). NÕu kh«ng cã s½n Index th× Oracle sÏ sö dông ph­¬ng thøc truy nhËt b»ng Nested-loop thay cho Merge-sort. ChØ dÉn nµy kh«ng cã t¸c dông khi c©u lÖnh sö dông to¸n tö tËp hîp (UNION, INTERSEC, MINUS, UNION ALL), mÖnh ®Ò GROUP BY, mÖnh ®Ò FOR UPDATE, hµm lµm viÖc víi nhãm hµng (MIN, MAX, SUM, COUNT) vµ to¸n tö DISTINCT.

CHOOSE: ChØ dÉn nµy cho phÐp Oracle chän luËt tèi ­u dùa trªn th«ng tin trong tõ ®iÓn d÷ liÖu, nÕu cã th«ng tin vÒ c¸c b¶ng th× luËt tèi ­u ­íc l­îng ®­îc ¸p dông cßn ng­îc l¹i th× ¸p dông luËt tèi ­u c¬ b¶n.

RULE: ¸p dông luËt tèi ­u c¬ b¶n cho c©u lÖnh.

6.2. ChØ dÉn vÒ chän ph­¬ng thøc truy nhËp

) vµ nªn sö dông bÝ danh (alias) cho tªn b¶ng.

VÝ dô:

SELECT /*+ FULL(a) Kh«ng sö dông Index cho b¶ng Nh©nViªn cã bÝ danh lµ a */ M·Nh©nViªn, TªnNh©nViªn

FROM Nh©nViªn a

WHERE M·Nh©nViªn=7566;

ROWID: Sö dông duyÖt b¶ng b»ng ROWID cho mét b¶ng ®Æc biÖt b»ng chØ dÉn ROWID().

CLUSTER: Chän ph­¬ng thøc duyÖt b»ng Cluster cho mét b¶ng b»ng chØ dÉn CLUSTER()

VÝ dô:

SELECT --+ CLUSTER Nh©nViªn

TªnNh©nViªn, M·Phßng

WHERE M·Phßng=10 AND Nh©nViªn.M·Phßng=Phßng.M·Phßng;

HASH: Chän ph­¬ng thøc truy nhËp b¨m (hash scan) cho mét b¶ng b»ng chØ dÉn HASH().

INDEX: Chän c¸ch truy nhËp b»ng Index cho mét b¶ng theo có ph¸p sau

NÕu danh s¸ch Index cã nhiÒu gi¸ trÞ th× Oracle sÏ ­íc l­îng gi¸ cña ph­¬ng thøc duyÖt øng víi mçi Index vµ chän ra ph­¬ng thøc nµo cã gi¸ nhá nhÊt. NÕu kh«ng Index ®­îc chØ ra trong danh s¸ch th× Oracle sÏ tiÕn hµnh ­íc l­îng gi¸ th«ng qua c¸c Index cã s½n trong CSDL ®ang truy nhËp.

VÝ dô:

SELECT /*+ INDEX(Nh©nViªn giíitÝnh_index) sö dông Index trªn cét giíi tÝnh cña b¶ng Nh©nViªn khi mµ cã Ýt n÷ : GiíiTÝnh=0 */

TªnNh©nViªn, L­¬ng WHERE GiíiTÝnh=0;

INDEX_ASC: ChØ dÉn chän ph­¬ng thøc truy nhËp qua Index vµ nÕu Index ®ã lµ duyÖt cã giíi h¹n (Index Range Scan) th× sÏ theo chiÒu t¨ng dÇn cña gi¸ trÞ ®­îc Index.

INDEX_DESC: ChØ dÉn chän ph­¬ng thøc truy nhËp qua Index vµ nÕu Index ®ã lµ duyÖt cã giíi h¹n (Index Range Scan) th× sÏ theo chiÒu gi¶m dÇn cña gi¸ trÞ ®­îc Index.

AND_EQUAL: Chän ph­¬ng thøc truy nhËp b»ng duyÖt trªn nhiÒu Index ®¬n. Tèi thiÓu ph¶i cã hai Index ®¬n vµ tèi ®a lµ n¨m Index ®¬n.

USE_CONCAT: ChØ dÉn nµy buéc ®iÒu kiÖn OR trong mÖnh ®Ò WHERE chuyÓn thµnh query sö dông to¸n tö tËp hîp UNION ALL. Th«ng th­êng viÖc chuyÓn nµy chØ ®­îc thùc hiÖn khi gi¸ cña viÖc chuyÓn lµ nhá h¬n víi gi¸ cña c©u lÖnh ban ®Çu.

6.3. ChØ dÉn vÒ thø tù kÕt nèi trong c©u lÖnh sö dông kÕt nèi

ORDERED: ChØ dÉn thø tù kÕt nèi tu©n theo thø tù xuÊt hiÖn tªn c¸c b¶ng trong mÖnh ®Ò FROM.

VÝ dô:

SELECT /*+ ORDERED */ tab1.col1, tab2.col2, tab3.col3 FROM tab1, tab2, tab3 WHERE tab1.col1=tab2.col1 AND tab2.col1=tab3.col1

C¸c vÝ dô ®Çy ®ñ h¬n sÏ ®­îc giíi thiÖu trong phô lôc D.

• 
  ChØ dÉn cho qu¸ tr×nh chän thao t¸c kÕt nèi
  

USE_NL: ChØ dÉn chän thao t¸c kÕt nèi Nested-Loop víi b¶ng néi (inner table) ®­îc chØ ra

USE_HASH: ChØ dÉn chän thao t¸c kÕt nèi Hash-Join.

USE_MERGE: ChØ dÉn chän thao t¸c kÕt nèi Merge-Sort.

C¸c chØ dÉn kÕt nèi rÊt quan träng ®Ó ®iÒu khiÓn qu¸ tr×nh kÕt nèi theo ®óng mong muèn cña ng­êi lËp tr×nh. Phô lôc D sÏ ®­a ra mét vÝ dô cã sö dông chØ dÉn kÕt nèi cña phÇn mÒm Tra cøu v¨n b¶n ph¸p qui ®· gi¶i quyÕt ®­îc yªu cÇu ®Æt ra vÒ tèc ®é.

Каталог: UDS -> Ebook -> CD1
CD1 -> H×nh 14. Phn tÝch modulus cña rabin víi mét ch­¬ng tr×nh con gi¶i m· cho tr­íc
CD1 -> Diễn hoạt nhân vật với Progressive Morphing i-khái niệm Progressive Morphing
CD1 -> 1. §Æc ®iÓm: Tªn file: asp
CD1 -> Ch­¬ng 9 T×m ra c¸c ®Þa chØ th­ ®iÖn tö Trong ch­¬ng nµy
CD1 -> Microsoft Frontpage là một phần mềm thiết kế web chuyên nghiệp của hãng phần mềm Microsoft
CD1 -> TÀi liệu tham khảo về google adsense
CD1 -> MỘt số kinh nghiệm với windows xp (phần I) Chạy chương trình không cần cài lại
CD1 -> Adobe photoshop cs 0
CD1 -> Tài liệu tham khảo của trường đại học wakayama Nhật

tải về 0.52 Mb.

Chia sẻ với bạn bè của bạn: