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

No comments:

Post a Comment