Showing posts with label oracle10g. Show all posts
Showing posts with label oracle10g. Show all posts

Sunday, December 26, 2010

โอ้! ขวางทาง Lock ตอนที่ 2/2


(ต่อจากตอนที่แล้ว โอ้! ขวางทาง Lock ตอนที่ 1/2)

ประเภทของการ Lock และคอลัมน์ ID1/ID2 ในตาราง V$LOCK
เราสามารถหาว่า Session ใดกำลัง Block Session อื่น ๆ อยู่ได้โดยไม่ยากจากการหาข้อมูลในตาราง V$LOCK โดยที่แรกที่เราจะดูคือคอลัมน์ TYPE ซึ่งแสดงประเภทของการ Lock ที่มีอยู่หลายตัว แต่ประเภทการ Lock ส่วนใหญ่นั้นเป็นการ Lock เพื่อการทำงานของ System เอง โดยปกติแล้ว System Lock จะอยู่เพียงครู่เดียว และการจูน Library Cache, Undo Logs หรืออื่น ๆ โดยใช้ข้อมูลใน V$LOCK ก็ไม่ค่อยจะได้ประโยชน์สักเท่าใด (ดูรายการ Lock แบบ System ได้ใน Oracle Database Reference ในบทที่เกี่ยวกับ V$LOCK)
ส่วนการ Lock ประเภทที่เรียกว่า User Lock นั้นมีเพียง 3 แบบ คืือ TX, TM และ UL, โดย
  • UL(User-Defined) คือ Lock ที่เกิดจากการกำหนดของ Package ชื่อ DBMS_LOCK
  • TX Lock เป็นการที่ Transaction หนึ่ง ๆ ทำการ Lock แถวของข้อมูลในตารางที่จะทำการเปลี่ยนแปลง (Row Transaction Lock), TX Lock จะเกิดขึ้นเพียงครั้งเดียวต่อหนึ่ง Transaction ที่จะทำการเปลี่ยนแปลงข้อมูล โดยไม่สำคัญว่าคุณจะเปลี่ยนแปลง Object (เช่นตาราง) จำนวนกี่ตัว คอลัมน์ ID1 และ ID2 จะชี้ไปที่ Rollback Segment และรายการ Transaction ที่เกิดขึ้นใน Transaction Table
  • TM Lock คือ DML Lock ซึ่งจะเกิดขึ้นตามจำนวนของ Object ที่ถูก Lock เพื่อทำการเปลี่ยนแปลง โดยคอลัมน์ ID1 จะแสดง Object ที่กำลังถูกเปลี่ยนแปลง
โหมดของการ Lock
คุณสามารถดูข้อมูลเพิ่มเติมเกี่ยวกับ TM และ TX Lock ได้โดยการดูที่คอลัมน์ LMODE (Lock Mode) และคอลัมน์ REQUEST ทั้งสองคอลัมน์ใช้รหัสตัวเลขชุดเดียวกันในการบอกสถานะของการ Lock โดยยิ่งตัวเลขมากการ Lock ก็จะยิ่งแน่นหนา (Exclusive - หมายถึงปิดกั้นการเข้าถึงของ Session อื่น ๆ มากขึ้น) เช่น 0 หมายถึงไม่ Lock เลย และ 6 หมายถึง Exclusive Lock
ปกติแล้วแต่ละ Session จะต้องขอ Exclusive TX Lock จึงจะทำการเปลี่ยนแปลงข้อมูลได้ (LMODE = 6) และถ้า Session ไม่สามารถขอ Exclusive Lock ได้เนื่องจากมีบางแถวของข้อมูลที่มันต้องการเปลี่ยนแปลงถูก Lock อยู่ก่อนหน้าแล้วโดย Session อื่น คอลัมน์ LMODE ของ Session นั้นก็จะเป็น 0 โดยมีคอลัมน์ REQUEST เป็น 6 เพื่อจะบอกว่ากำลัง (รอ) ร้องขอ Exclusive Lock อยู่ เราสามารถดูลักษณะการทำงานนี้ได้จากแถวของ V$LOCK ที่ได้แสดงไปในบทก่อนหน้าดังนี้

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ------ -- -------- -------- ----- -------- ------- ------
AF9E2C4C AF9E2C60 479 TX 131078 16739 0 6 685 0
ADEBEA20 ADEBEB3C 422 TX 131078 16739 6 0 697 1

สังเกตดูว่าคอลัมน์ ID1 และ ID2 ของ Session 2 ซึ่งกำลังถูก Block และรอขอ TX Lock (LMODE=0, REQUEST=6) ชี้ไปที่ Rollback และ Transaction Entries ของ Session 1 นั่นทำให้เราสามารถหา Session ที่กำลัง Block อยู่ได้
บางครั้งคุณอาจจะเห็น TX Lock ใน Mode 4 (LMODE=4), Shared Mode หมายถึง Data Block ที่เก็บแถวของข้อมูลที่เราต้องการเปลี่ยนแปลงไม่มี Interested Transaction List (ITL) เหลือ เราก็จะเห็น Session ร้องขอ TX Lock ใน Mode 4 ระหว่างที่รอให้ ITL ว่าง ดังนั้นถ้าเราเจอ TX-4 Lock เราอาจจะต้องเพิ่ม INITRANS พารามิเตอร์ให้กับ Object ตัวนั้น

ITL (Interested Transaction List)
เมื่อแถวของข้อมูลในตารางถูก Lock โดย Trasaction ใด ๆ ข้อมูลการ Lock นั้นจะถูกเก็บอยู่ในส่วนหัวของ Block ของข้อมูล (Block Header) ที่แถวของข้อมูลนั้นเก็บอยู่ ข้อมูลส่วนนี้เราเรียกว่า Interested Transaction List (ITL) โดยแบ่งเป็น Slot, ใน ITL เป็นที่เก็บข้อมูลของ Transaction Address และ Rowid, ITL สามารถเก็บข้อมูลการ Lock ได้หลาย Transaction โดยเมื่อ Transaction ร้องขอ Lock มันก็จะใส่ rowid ของแถวที่ต้องการไว้ใน ITL และเมื่อ Transaction อื่น ๆ ต้องการ Lock มันก็จะทำเช่นเดียวกัน เมื่อ Transaction ทำการ Commit หรือ Rollback มันก็จะปล่อย Lock พร้อมทั้งข้อมูลใน Slot ของ Block Header ด้วย เพื่อคืนพื้นที่ให้สามารถนำมาใช้ได้ต่อไป การเซ้ทพารามิเตอร์ INITTRANS และ MAXTRANS ตอนสร้างตารางเป็นตัวกำหนดจำนวน Slot ที่สามารถเปิดได้เมื่อจำนวน Transaction ที่ต้องการเข้าเกินกว่าจำนวน Slot ที่มี Slot ใหม่จะถูกเปิดตราบใดที่ยังไม่เกิน MAXTRANS อย่างไรก็ดีถ้าจำนวน Slot เกินจำนวนที่จะขยายได้แล้ว (เนื่องจากพื้นที่ใน Block ไม่พอแล้ว หรือเกิน MAXTRANS) Transaction จะต้องรอซึ่งจะทำให้เกิดการ Lock ในโหมด 4 ขึ้น

TM Lock โดยปกติจะร้องขอที่ Mode 3 (LMODE=3, Shared-Row Exclusive) และ 6 ซึ่งถ้าเป็นคำสั่ง DDL (เช่นพวกคำสั่ง ALTER เป็นต้น) จะร้องขอ TM Exclusive Lock (CREATE TABLE ไม่ต้องใช้ TM Lock เพราะ Object นั้นไม่เคยมีอยู่) ส่วนคำสั่ง DML จะต้องใช้ Shared-Row Exclusive Lock ดังนั้นจากแถวที่เราได้จาก V$LOCK เราสามารถเห็นว่าเป็น DML Lock

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ----- -- ------ ---- ----- -------- ------ ------
ADDF7EC8 ADDF7EE0 422 TM 88519 0 3 0 697 0
ADDF7F74 ADDF7F8C 479 TM 88519 0 3 0 685 0

การระบุ Object ที่ถูก Lock อยู่
เราสามารถจะระบุ Object ที่ถูก Lock ได้จากคอลัมน์ ID1 จากแถวใน V$LOCK ที่เป็น TM Lock โดยนำไป Join กับตาราง DBA_OBJECTS ดังนี้

SQL> select object_name from dba_objects where object_id=88519 ;

OBJECT_NAME
--------------
TSTLOCK

การระบุแถวในตารางที่ถูก Lock
ส่วนใหญ่แล้วการรู้ว่าตารางใดที่ถูก Lock ก็เพียงพอแล้ว แต่หากเราต้องการข้อมูลถึงระดับแถวที่ถูก Lock ก็สามารถทำได้ ดังนี้

SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
2* from v$session where sid=479 ;

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
88519 16 171309 0
ซึ่งให้ข้อมูลกับเราเกี่ยวกับ Object ID, Relative File Number, Block ใน Data File และแถวที่อยู่ใน Block ซึ่ง Session กำลังรอ (Lock) อยู่ ข้อมูล 4 ตัวนี้เมื่อนำมาประกอบกันก็จะเป็น ROWID แบบขยายนั่นเอง (Extended ROWID) เราสามารถจะสร้าง Extended ROWID จากข้อมูลทั้งสี่นี้โดยใช้ Package ชื่อ DBMS_ROWID ซึ่งมีฟังก์ชัน ROWID_CREATE ที่ใช้ในการแสดง ROWID

SQL> select do.object_name,
2 row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
3 dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
4 from v$session s, dba_objects do
5 where sid=543
6 and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;

OBJECT_NAME ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# DBMS_ROWID.ROWID_C
------------ ------------- -------------- --------------- ------------- ------------------
TSTLOCK 88519 16 171309 0 AAAVnHAAQAAAp0tAAA

ซึ่งทำให้เราสามารถนำมาหาแถวของข้อมูลที่เราต้องการในตารางได้

SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;

FOO BAR
--- ---
1 a

บทสรุป
เราได้เห็นวิธีการที่จะระบุ Session ที่ Block Session อื่น และวิธีการค้นหาเพื่อตรวจสอบแถวของข้อมูลที่มี Session รอทำการ Lock อยู่ และดิฉันหวังว่า เราได้เรียนรู้บางสิ่งบางอย่างบ้างเกี่ยวกับกระบวนการที่เกี่ยวข้องกับ V$LOCK

เกี่ยวกับผู้เขียน
Natalka Roshak เป็น Senior Oracle and Sybase Database Administrator, เป็นนักวิเคราะห์ระบบ และ Architect ทำงานอยู่ใน Kingston, Ontario และทำงานเป็นที่ปรึกษาในอเมริการเหนือ สคริปต์และทิปของเธอสามารถหาดูได้จาก Online DBA Toolkit ที่ http://toolkit.rdbms-insight.com/

แปลจาก What's blocking my lock? โดย Natalka Roshak on Sat, 2006-04-01 18:00 www.orafaq.com/blog/natalka_roshak

Thursday, August 5, 2010

Recreate Constraint กับ DBMS_METADATA


สมถวิวได้รับมอบหมายให้ดูแลระบบฐานข้อมูล Oracle มาได้สองเดือน โดยที่ทีมพัฒนาระบบได้ขอร้องให้เธอช่วยเป็น DBA จำเป็นให้กับทีมด้วย ด้วยไฟที่ร้อนแรงเธอแนะนำให้ทีมพัฒนาระบบสร้าง Foreign Key กับทุก ๆ ตารางที่มีการอ้างอิงไปยังตาราง Master เพื่อที่จะได้ไม่มีข้อมูลที่เป็น "กำพร้า" (Orphan) ในตารางลูก เช่นในระบบเก่ามีรายการชำระเงินบางรายการในตารางธุรกรรม ที่ไม่พบสัญญาในตารางที่เป็น Master ทำให้ผู้จัดการข้อมูลต้องเกาหัวแกร็ก ๆ โชคดีว่าข้อมูลการชำระเงินเหล่านี้เกิดขึ้นนานแล้ว และปัจจุบันไม่มีผลแล้ว

สมถวิวในนามของ DBA จำเป็นได้บรรจงสร้าง Constraint ต่าง ๆ ไม่ว่า Primary Key, Foreign Key ลงในตารางที่ทีมพัฒนากำลังเขียนโปรแกรมกันอยู่ เพื่อควบคุมการอ้างอิงกันของข้อมูลให้ถูกต้องที่สุด และป้องกันการปวดหัวในอนาคต วันหนึ่งสมโคดซึ่งเป็น Developer ในทีมได้บ่นกับสมถวิวว่า ทำไมต้องมี Constraint เยอะแยะ ทดสอบโปรแกรมลำบากมาก จะทำข้อมูลทดสอบก็ยาก จะลบก็ยาก ต้องไปตามลบกันเป็นทอด ๆ และแนะนำสมถวิวว่า "เอาออกเหอะ ตัวเอง เด๋วทีมดีเวลล็อป ควบคุมจากโปแก็มเอง" สมโคดกะจะให้โปรแกรมเป็นตัวเช็ค โดยคิดว่าการให้ User เลือกจากดร็อปดาวน์ลิสต์น่าจะทำให้ข้อมูลที่เข้ามาจะต้องมีในตารางมาสเตอร์เท่านั้น

สมถวิวเองก็เคยเจอปัญหานี้เหมือนกันคือไอ้เจ้า Foreign Key นี้มันช่างทำให้การทำงานเก้งก้างจัง จะลบก็ยาก จะเพิ่มก็ยาก แต่จะให้ลบ หรือไม่สร้างเลยละก็ เมินเสียเถอะ! เธอไม่อยากต้องมาปวดหัวกับข้อมูลประหลาด ๆ ในอนาคต เอ...แต่จะแก้ปัญหาอย่างไรดี เธอรู้ว่าการ Disable Constraint ก็ไม่สามารถจะแก้ปัญหานี้ได้ในบางกรณี แต่จะทำยังไงดีเธอเตรียมกระดาษกับปากกาเอาไว้แล้ว เธอจะจดรายละเอียด Constraint ให้ละเอียดเลย พร้อมทั้งคอลัมน์ต่าง ๆ ที่อยู่ใน Constraint ฐานข้อมูลตัวนี้มีแค่ 200 ตารางเอง แล้วก็มีตารางที่ต้องอ้างอิงตารางอื่นประมาณแค่ 150 ตาราง เธอรู้สึกปวดมวนในท้องขึ้นมาทันที แล้วถ้าเธอสร้างผิดแล้วไม่เหมือนเดิมล่ะ...เอถ้าเราสามารถจะลบ Constraint ออก และสร้างใหม่ได้แบบง่าย ๆ ก็ดีสินะ

สมถวิวคิดถูกแล้วที่จะควบคุมมาตรฐานหรือ Intergrity ของฐานข้อมูลซึ่งเป็นหน้าที่หนึ่งของ DBA แต่ปัญหาของการนี้คือเราจะต้องเก็บเอา Foreign Key Constraint เก่าเอาไว้ก่อนลบ แล้วเอามาสร้างใหม่เมื่อต้องการกลับมาสร้างกลับเพื่อควบคุม Constraint อีกครั้ง โชคดีที่ Oracle ได้เตรียม Utility สำหรับการสร้าง DDL (Data Discription Language - คำสั่งที่ใช้ในการสร้างวัตถุต่าง ๆ ที่เกี่ยวกับข้อมูล เช่นตาราง, อินเด็กซ์เป็นต้น) เอาไว้ให้แล้ว ซึ่งน่าจะช่วยสมถวิวในการ Drop และสร้าง Constraint ใหม่ได้มาก ลองดูคำสั่งข้างล่างนี้

SQL> select
2 to_char(DBMS_METADATA.get_ddl('REF_CONSTRAINT',CONSTRAINT_NAME))
3 from USER_CONSTRAINTS where CONSTRAINT_TYPE = 'R';

TO_CHAR(DBMS_METADATA.GET_DDL('REF_CONSTRAINT',CONSTRAINT_NAME))
-------------------------------------------------------------------------

ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE


คำสั่งข้างบนนี้สามารถรันได้จาก Schema ที่เก็บตารางต่าง ๆ ได้ ไม่ต้องมีสิทธิ์เป็น DBA, สังเกตตรง DBMS_METADATA ซึ่งเป็น Package ชื่อก็บอกอยู่แล้วว่าเกี่ยวกับ Metadata หรือเป็นอะไรที่เกี่ยวกับข้อมูลของ Data อีกที ตัวฟังก์ชันที่เรานำมาให้ใช้คือ "GET_DDL" ซึ่งจะแสดง DDL หรือคำสั่งที่ใช้ในการสร้างวัตถุต่าง ๆที่เกี่ยวกับข้อมูลดังได้กล่าวมาแล้วตัวอย่างคำสั่งที่สามารถสร้างขึ้นมาได้จากฟังก์ชันนี้เช่น คำสั่งในการสร้างตาราง, คำสั่งในการสร้างอินเด็กซ์ หรือจากตัวอย่างนี้คือคำสั่งในการสร้าง Constraint เป็นต้น สังเกตว่าเราใช้คำสั่งนี้ร่วมกับ Data Dictionary View เช่นในกรณีนี้คือ USER_CONSTRAINTS ถ้าเราใช้คำสั่ง
{xtypo_code}SQL> select CONSTRAINT_NAME from USER_CONSTRAINTS where CONSTRAINT_TYPE = 'R';

เราจะได้ชื่อ Constraint ทั้งหมดที่เป็น Foreign Key เราเพียงแต่ใส่ฟังก์ชัน GET_DLL (ซึ่งเป็นส่วนหนึ่งของ Package ชื่อ DBMS_METADATA) เข้าไปเราก็จะได้ลิสต์ของคำสั่งในการสร้าง Constraint ที่เป็น Foreign Key ทั้งหมดใน Schema ปัจจุบัน

SQL> select to_char(DBMS_METADATA.get_ddl('REF_CONSTRAINT',CONSTRAINT_NAME))
2 from user_constraints where constraint_type = 'R';

***ตรงฟังก์ชัน to_char เราสามารถเอาออกได้ ที่ใส่ไว้เนื่องจากข้อมูลที่ได้มีประเภทข้อมูลเป็น CLOB บาง Interface อาจจะไม่สามารถแสดงผลได้

ตรงที่เป็น 'REF_CONSTRAINT' เป็นค่าคงที่ที่เราต้องรู้ว่าจะใส่คำว่าอะไรในการคิวรีเช่นถ้าต้องการ Index ก็อาจใช้คำสั่งร่วมกับ Dictionary View ชื่อ USER_INDEXES เช่น
SQL> select to_char(DBMS_METADATA.get_ddl('INDEX',INDEX_NAME))
2 from USER_INDEXES;

TO_CHAR(DBMS_METADATA.GET_DDL(
-------------------------------------------------------------------------

CREATE INDEX "SCOTT"."IND_MVDEPT_DNAME" ON "SCOTT"."MV_DEPT" ("DNAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"


CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"


CREATE UNIQUE INDEX "SCOTT"."PK_DEPT1" ON "SCOTT"."MV_DEPT" ("DEPTNO")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"
...
...
ดูค่าคงที่สำหรับ Object Type ได้ที่ http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBIEDIA

เมื่อเราได้คำสั่งมาแล้วเราก็สามารถที่จะบันทึกเก็บไว้ หลังจากที่เรา Drop เอา Constraint (หรือ Index) ออกแล้วเราก็สามารถนำคำสั่งเหล่านี้กลับมารันเพื่อให้วัตถุที่เป็น Constraint หรือ Index ต่าง ๆ กลับมาดังเดิม เช่นในกรณีของสมถวิว เธออาจจะรันคำสั่งนี้ขึ้นมาเก็บไว้ก่อน แล้วจึง Drop Constraint เมื่อทำงานต่าง ๆ ที่มี Constraint เป็นอุปสรรคเสร็จแล้ว ก็สามารถสร้าง Constraint ขึ้นมาใหม่โดยใช้คำสั่งที่เก็บไว้ข้างต้น สมถวิวก็จะสามารถคงความเป็น DBA ที่ดีไว้ โดยที่ยังสามารถให้ความสะดวกกับเพื่อนร่วมงานของเธอได้
เวอร์ชั่น: Oracle9i,10g

Sunday, June 27, 2010

การ Join กับ Query Plan

 การ Join กับ Query Plan
สอง Query ข้างล่างนี้ให้ผลเหมือนกันแต่ตัวไหนเร็วกว่า?
1) SELECT distinct D.deptno, D.dname
FROM dept D, emp E
WHERE E.deptno = D.deptno
ORDER BY D.deptno;

2) SELECT D.deptno, D.dname
FROM dept D
WHERE EXISTS
(
SELECT 1
FROM emp E
WHERE E.deptno = D.deptno))ORDER BY D.deptno;

ปกติเราต้องใช้การ Join ของสองตารางหรือมากกว่า ก็ต่อเมื่อเราต้องการคอลัมน์จากตารางทุกตารางที่เอามา Join กัน ดังนั้นเราไม่ควรจะ Join ตาราง EMP เข้ากับตาราง DEPT ถ้าในผลลัพธ์ที่เราต้องการไม่มีคอลัมน์ของ EMP อยู่เลย ซึ่งถ้าเป็นกรณีอย่างนี้คุณควรจะใช้ WHERE EXISTS หรือไม่ก็ WHERE IN มากกว่า (Cost-Based Optimizer [CBO] เห็นสองตัวนี้เหมือนกัน) เราอาจจะเขียน Query แบบนี้
select deptno, dname
from dept
where exists
( select NULL
from emp
where emp.deptno
= dept.deptno )
order by deptno;

หรือแบบนี้
select deptno, dname
from dept
where deptno in
( select deptno
from emp )
order by deptno;

ทั้งสองกรณี Optmizer อาจจะใช้ Semi Join (ซึ่งเอา deptno ใน dept ทีละ Row ไปเทียบกับ deptno ใน emp และจะหยุดการเปรียบเทียบของ Row นั้นทันทีที่พบว่ามี) หรือไม่ก็ใช้อินเด็กซ์กับ Nested Loops ในการหาใน EMP ว่ามี Row นั้นอยู่หรือเปล่า
{xtypo_info}Semi-Join ระหว่างตาราง 2 ตารางจะให้ผลเป็นแถวจากตารางแรกที่สามารถจับคู่กับแถวในตารางที่สองได้หนึ่งหรือมากกว่าหนึ่งคู่ ความแตกต่างระหว่าง Semi-Join และการ Join แบบธรรมดาคือแถวจากในตารางแรกจะถูกดึงมาครั้งเดียว แม้ว่าในตารางที่สองจะมีอยู่สองแถวที่สามารถจับคู่กับตารางแรกได้, Semi-Join จะถูกเขียนโดยใช้รูปแบบ EXISTS หรือ IN{/xtypo_info}

ตัวอย่างต่อไปนี้แสดงว่าโดยทั่วไปแล้วจะเกิดอะไรขึ้น เมื่อ Optimizer ตรวจพบว่า DEPT มีขนาดเล็ก และ EMP มีขนาดใหญ่ โดยมันจะสแกนแต่ละ Row ของ DEPT และจะเข้าไปหา DEPTNO (โดยใช้อินเด็กซ์) ในตาราง EMP ซึ่งมีขนาดใหญ่ ในทางกลับกันถ้า DEPT มีขนาดใหญ่ Optimizer ก็จะทำ Semi Join ทั้งก้อนโดยไม่ใช้ Index เราจะใช้ก๊อปปี้ของตาราง EMP และ DEPT ในการทดสอบดังนี้
SQL> create table emp as
2 select * from scott.emp;
Table created.

SQL> create table dept as
2 select * from scott.dept;
Table created.

SQL> create index emp_deptno_idx
2 on emp(deptno);
Index created.

จากนั้นเราก็เมคค่าสถิติของตารางขึ้นมาเอง โดยใช้ SET_TABLE_STATS ใน Package DBMS_STATS เพื่อที่จะทำให้ EMP ดูเหมือนตารางที่ใหญ่ มีขนาด 1,000,000 แถว และให้ DEPT ดูเหมือนตารางที่มีขนาดเล็กเพียง 100 แถว นอกจากนั้นเราจะบอก Optimizer ว่ามีอินเด็กซ์บนคอลัมน์ EMP.DEPTNO แต่เนื่องจาก Oracle10g จะทำการคำนวณสถิติของอินเด็กซ์ตั้งแต่ตอนสร้างอินเด็กซ์ เราจึงต้องเอาสถิติเดิมของอินเด็กซ์ออกก่อน และใส่ (เมค) สถิติใหม่เข้าไปให้สอดคล้องกับสถิติของตารางที่เราเมคเอาไว้แต่แรก
SQL> begin
2 dbms_stats.set_table_stats
3 ( user,
4 'EMP',
5 numrows => 1000000,
6 numblks => 100000 );

7 dbms_stats.delete_index_stats
8 ( user,
9 'EMP_DEPTNO_IDX' );

10 dbms_stats.set_index_stats
11 ( user,
12 'EMP_DEPTNO_IDX',
13 numrows => 1000000,
14 numdist => 10000,
15 numlblks =>10000 );

16 dbms_stats.set_column_stats
17 ( user,
18 'EMP',
19 'DEPTNO',
20 DISTCNT => 10000 );

21 dbms_stats.set_table_stats
22 ( user,
23 'DEPT',
24 numrows=> 100,
25 numblks => 100 );
26 end;
27 /

ตอนนี้เราก็พร้อมที่จะจับตาดูการตัดสินใจของ Optimizer เราใช้ AUTOTRACE เพื่อที่จะดู Query Plan ที่สร้างขึ้นจากคิวรีทั้งสองตัว เราแสดงคิวรีทั้งสองเพื่อที่จะแสดงให้เห็นว่าเมื่อ Developer ใช้ CBO (Cost-Based Optimizer) เขาไม่จำเป็นต้องตัดสินใจว่าจะเขียนอย่างไรให้ดีที่สุด (คือเพียงในระดับที่ยอมรับได้ก็เพียงพอ ส่วนที่เหลือให้ Optimizer ช่วยจัดการ) -- Optimizer จะเข้าใจรูปแบบของคิวรีที่เขียนขึ้นและเลือกวิธีการนำข้อมูลออกมาที่ดีที่สุด ซึ่งแตกต่างโดยสิ้นเชิงกับวิธีแบบ Rule-Based Optimizer (RBO) ซึ่งเป็น Optimizer แบบเก่าที่เราจะต้องเป็นผู้กำหนดแปลนในการรันคำสั่งเอาเอง ซึ่งจะมองเห็น WHERE IN กับ WHERE EXISTS ว่าแตกต่างกันโดยสิ้นเชิง

เมื่อเราใช้ CBO กับตาราง EMP ซึ่งมีขนาดใหญ่ และ DEPT ซึ่งมีขนาดเล็ก, Oracle อาจจะใช้ Query Plan ดังแสดงข้างล่างนี้ คืออ่านทุก ๆ แถวของ DEPT และทำสแกนอินเด็กซ์เฉพาะช่วง (Index Range Scan) บนตาราง EMP เพื่อหา DEPTNO ที่ตรงกับใน DEPT และเนื่องจากการสแกนจากในอินเด็กซ์เพื่อเปรีบบเทียบนี้มีแค่ 100 ครั้ง (เนื่องจากเราทำให้ Optimizer เห็นว่ามีข้อมูลใน DEPT แค่ 100 แถว เมื่อนำ 100 แถวเข้าไปหาในอินเด็กซ์จึงต้องทำทั้งหมด 100 ครั้ง) และเนื่องจาก (เราทำให้ Optimizer เห็นว่า) ตาราง EMP มีขนาดใหญ่ Optimizer จึงใช้ Query Plan นี้ทั้งกับกรณี WHERE IN และ WHERE EXISTS
SQL> set autotrace traceonly explain
SQL> select deptno, dname
2 from dept where deptno in
3 ( select deptno
4 from emp )
5 order by deptno;

Execution Plan
-----------------------------------------------------------
Plan hash value: 3383088615

-----------------------------------------------------------
 
| Id | Operation       | Name          | Rows  | Bytes |
-----------------------------------------------------------
| 0  |SELECT STATEMENT |               | 100   | 3500  |
| 1  |SORT ORDER BY    |               | 100   | 3500  |
| 2  |NESTED LOOPS SEMI|               | 100   | 3500  |
| 3  |TABLE ACCESS FULL| DEPT          | 100   | 2200  |
|* 4 |INDEX RANGE SCAN | EMP_DEPTNO_IDX| 1000K | 12M   |
-----------------------------------------------------------

Predicate Information (identified by operation id):
 
-----------------------------------------------------------

4 - access("DEPTNO"="DEPTNO")
 

SQL> select deptno, dname
 
2 from dept where exists
3 ( select null
4 from emp
5 where emp.deptno =
6 dept.deptno )
7 order by deptno;

Execution Plan
 
------------------------------------------------------------
Plan hash value: 3383088615

------------------------------------------------------------
 
| Id | Operation        | Name          | Rows  | Bytes |
------------------------------------------------------------
| 0  | SELECT STATEMENT |               | 100   | 3500  |
| 1  | SORT ORDER BY    |               | 100   | 3500  |
| 2  | NESTED LOOPS SEMI|               | 100   | 3500  |
| 3  | TABLE ACCESS FULL| DEPT          | 100   | 2200  |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO_IDX| 1000K | 12M   |
------------------------------------------------------------

Predicate Information (identified by operation id):
 
------------------------------------------------------------

4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")



ต่อมาหากจำนวนของข้อมูลของ DEPT เพิ่มขึ้น และ Optimizer เห็นว่าจำนวนครั้งที่มันจะต้องเข้าไปตรวจข้อมูลจากอินเด็กซ์ของตาราง EMP มากขึ้นมาก ๆ แปลนที่ Optimizer จะใช้ในการดึงข้อมูลก็จะเปลี่ยนไป จากเดิมที่ใช้ Nested Loops ร่วมกับ Index Range Scan ก็จะเปลี่ยนไปเป็นการทำงานกับข้อมูลทั้งก้อน (แทนการใช้อินเด็กซ์) เพื่อที่จะให้เห็นภาพเราต้องบอก Optimizer ว่าตอนนี้ DEPT มีขนาดใหญ่ขึ้นมาก
{xtypo_code}SQL> begin
2 dbms_stats.set_table_stats
3 ( user,
4 'DEPT',
5 numrows=> 100000,
6 numblks => 10000 );
7 end;
8 /

จากนั้นก็รันคิวรีใหม่อีกทีหนึ่ง และดู Query Plan ที่เกิดขึ้นดังนี้
SQL> set autotrace traceonly explain
SQL> select deptno, dname
2 from dept where deptno in
3 ( select deptno
4 from emp )
5 order by deptno;

Execution Plan
-----------------------------------------------------------------
Plan hash value: 3127359958
-----------------------------------------------------------------
| Id | Operation           | Name           | Rows | Bytes |
-----------------------------------------------------------------
| 0  | SELECT STATEMENT    |                | 100K | 3417K |
| 1  | SORT ORDER BY       |                | 100K | 3417K |
|* 2 | HASH JOIN SEMI      |                | 100K | 3417K |
| 3  | TABLE ACCESS FULL   | DEPT           | 100K | 2148K |
| 4  | INDEX FAST FULL SCAN| EMP_DEPTNO_IDX | 1000K| 12M   |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------------
2 - access("DEPTNO"="DEPTNO")
SQL> select deptno, dname
2 from dept where exists
3 ( select null
4 from emp
5 where emp.deptno =
6 dept.deptno )
7 order by deptno;

Execution Plan
 
---------------------------------------------------------------
Plan hash value: 3127359958

---------------------------------------------------------------
 
| Id | Operation           | Name          | Rows | Bytes |
---------------------------------------------------------------
| 0  | SELECT STATEMENT    |               | 100K | 3417K |
| 1  | SORT ORDER BY       |               | 100K | 3417K |
|* 2 | HASH JOIN SEMI      |               | 100K | 3417K |
| 3  | TABLE ACCESS FULL   | DEPT          | 100K | 2148K |
| 4  | INDEX FAST FULL SCAN| EMP_DEPTNO_IDX| 1000K| 12M   |
---------------------------------------------------------------

Predicate Information (identified by operation id):
 
---------------------------------------------------------------

2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")


เราจะเห็นว่าขณะนี้ในคิวรีทั้งสองกรณี Optimizer เลือกใช้ (Index) Full Scan และ Hash Semi Join แทนการเข้าไปค้นใน Index ดังทีแรก เนื่องมาจากว่าไม่งั้นมันก็ต้องเข้าไปค้นในอินเด็กซ์เป็นจำนวน 100,000 ครั้ง
เรียบเรียงจาก:
http://www.oracle.com/technology/oramag/oracle/06-may/o36asktom.html