Wednesday, June 17, 2009

การใช้ Constraints เพื่อเพิ่มประสิทธิภาพในการคิวรี (ตอนที่ 2)

 การใช้ Constraints เพื่อเพิ่มประสิทธิภาพในการคิวรี (ตอนที่ 2)
(แปลจาก “On Constraints, Metadata, and Truth” โดย Tom Kyte, Oracle Magazine V XXIII, Issue3)

Constraints, Primary Keys and Foreign Keys
คราวนี้ลองมาดู Primary และ Foreign Keys ว่ามีผลต่อ Optimizer อย่างไรบ้าง ตัวอย่างต่อไปนี้ เราจะคัดลอกตารางจาก SCOTT.EMP และตาราง SCOTT.DEPT เราจะสมมติ


ว่าตารางทั้งสองนี้มีขนาด ใหญ่ โดยเราจะใช้ DBMS_STATS.SET_TABLE_STATS เพื่อให้ Optimizer คิดว่า "ใหญ่" และเราสร้างวิว EMP_DEPT ดังใน Listing 7

==========================================================
Listing 7: สร้างตาราง EMP และ DEPT ขนาด "ใหญ่" และวิว EMP_DEPT
SQL> create table emp
2 as
3 select *
4 from scott.emp;

Table created.


SQL> create table dept

2 as
3 select *
4 from scott.dept;

Table created.


SQL> create or replace view emp_dept

2 as
3 select emp.ename, dept.dname
4 from emp, dept
5 where emp.deptno = dept.deptno;

View created.


SQL> begin

2 dbms_stats.set_table_stats
3 ('TANAKORN','EMP',numrows=>1000000, numblks=>100000);
4 dbms_stats.set_table_stats
5 ('TANAKORN','DEPT',numrows=>100000, numblks=>10000);
6 end;
7 /

PL/SQL procedure successfully completed.

==========================================================
คราว นี้ลองสมมติด้วยว่าวิว EMP_DEPT จะถูกใช้ในการคิวรีตาราง EMP และ DEPT และใช้ในการคิวรีผลของการ Join ระหว่างสองตาราง เมื่อเราใช้วิวในการดึงข้อมูลจากตาราง

EMP เราสังเกตว่า ระบบฐานข้อมูลจะ Access ทั้งตาราง EMP และ DEPT ใน Execution Plan ดังแสดงใน Listing 8

==========================================================
Listing 8: คิวรีบนวิว EMP_DEPT จะ Access ตารางทั้ง EMP และ DEPT

SQL> select ename from emp_dept;

Execution Plan

----------------------------------------------------------
Plan hash value: 615168685

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

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 31M| | 23301 (1)| 00:04:40 |
|* 1 | HASH JOIN | | 1000K| 31M| 2448K| 23301 (1)| 00:04:40 |
| 2 | TABLE ACCESS FULL| DEPT | 100K| 1269K| | 1944 (1)| 00:00:24 |
| 3 | TABLE ACCESS FULL| EMP | 1000K| 19M| | 19444 (1)| 00:03:54 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

==========================================================
เนื่องจากเรารู้ว่าถ้าเราต้องการ ENAME การ Access ตาราง DEPT นั้นไม่จำเป็นเลย เพราะว่า DEPTNO เป็น Primary Key ของ DEPT และ DEPTNO ในตาราง EMP ก็เป็น Foreign Key ที่อ้างอิงตาราง DEPT หมายความว่าถ้าเรา Join ตาราง EMP และ DEPT โดยใช้ DEPTNO และทุก ๆ Row ของตาราง EMP ที่ DEPTNO ไม่เป็น Null จะจับคู่กับ 1 เรคคอร์ดในตาราง DEPT เสมอ เรารู้ว่า DEPTNO ใน EMP จะจับคู่ได้ 1 เรคคอร์ดเสมอเพราะ Foeign Key ที่กำหนดไว้บนตาราง EMP และ Primary Key ที่กำหนดไว้ใน DEPT

SQL> alter table dept add constraint dept_pk primary key(deptno);
Table altered.

SQL> alter table emp add constraint emp_fk_dept foreign key(deptno)
2 references dept(deptno);
Table altered.

เราจะได้ Query Plan ดังนี้
SQL> select ename from emp_dept;

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 126 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMP"."DEPTNO" IS NOT NULL)

เราจะเห็นว่า Optimizer ได้ตัดเอาตาราง DEPT ออกจากการคิด จะเห็นว่า Hash Join ไม่มีแล้ว และมี Predicate (คำสั่งที่กำหนดถูกกำหนดขึ้นเองโดยระบบ) เพิ่มขึ้นมาว่า DEPTNO IS NOT NULL ซึ่งอธิบายได้ว่า Optimizer รู้ว่าการมี FK และ PK จะทำให้คำสั่ง SELECT ENAME FROM EMP WHERE DEPTNO IS NOT NULL มีผลเท่ากับ คิวรีที่ใช้ในวิว การ Optimize ในลักษณะนี้ (การตัดเอาตารางที่ไม่จำเป็นออกไป) จะทำให้การคิวรีได้ผลเร็วขึ้นมาก โดยเฉพาะถ้าวิวของเรา Join ตารางจำนวนมาก ๆ เอาไว้ แล้ว User จะ select ข้อมูลจากวิวตัวนี้ และไม่ได้ต้องการข้อมูลจากตารางอื่น ๆ ที่อยู่ในวิวตัวนั้นด้วย

ในตัวอย่างยังได้แสดงให้เห็นว่าการ "SELECT * FROM ..." ไม่ควรจะนำมาใช้ในชีวิตจริง เนื่องจากเราจะไม่ได้ประโยชน์จาก Optimization และ Optimizer จะ Access ตาราง DEPT ตลอด เนื่องจากมันคิดว่าเราต้องการข้อมูลจากมัน (* หมายถึงเอาข้อมูลทุกอย่าง) ดังนั้น "ระบุคอลัมน์ที่ต้องการใช้จริงเสมอในคิวรีของคุณ"

(ยังไม่จบ นะครับ ยังมีตอนต่อไป...ขอขอบคุณที่ให้ความสนใจและโปรดติดตามตอนต่อไปนะครับ)

อ่านเพิ่มเติม:
การใช้ Constraints เพื่อเพิ่มประสิทธิภาพในการคิวรี (ตอนที่ 1)
การใช้ Constraints เพื่อเพิ่มประสิทธิภาพในการคิวรี (ตอนที่ 3-ตอนจบ)

1 comment:

Anonymous said...

ขอบคุณมากๆๆค่ะ มีประโยชน์มากจิงๆ

Post a Comment