Saturday, June 20, 2009

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

(แปลจาก “On Constraints, Metadata, and Truth” โดย Tom Kyte, Oracle Magazine V XXIII, Issue3)

Constraints และ Materialized Views

ตัวอย่างถัดไปของการเพิ่มประสิทธิภาพของคิวรีโดยใช้ Constraints คือ Query Rewrite (การที่ Optimizer ทำการเปลี่ยน Query ของเราเพื่อให้การรันมีประสิทธิภาพดีขึ้น) จะเกี่ยวเนื่องกับ Primary Key, Foreign Key, Not Null และ Materialized View (MV) ผมมักจะพูดถึง MV ว่าเป็น "Index ของ Data Warehouse" ประโยชน์ของ MV โดยทั่วไปคือเป็นการเตรียมข้อมูลสรุปที่ได้จากการรันคำสั่งยาว ๆ และมีความซับซ้อนกับตารางรายละเอียดที่มีข้อมูลจำนวนมาก และเก็บข้อมูลสรุปเหล่านี้ไว้บนดิสก์ และเมื่อ End User รันคำสั่งเฉพาะกาล (Adhoc) กับตารางรายละเอียดเหล่านั้น ก็จะได้ข้อมูลจากข้อมูลสรุปจาก MV ไปโดยที่ไม่รู้ว่ามี MV อยู่ ซึ่งคล้าย ๆ กับการที่ Index ถูกใช้กับระบบที่เป็น OLTP (On-Line Transaction Processing) เพื่อเพิ่มความเร็วให้กับคิวรี

เราจะใช้ตาราง EMP และ DEPT อีกครั้ง โดยเราจะลบ Constraint ออก

SQL> alter table emp drop constraint emp_fk_dept;
Table altered.

SQL> alter table dept drop constraint dept_pk;
Table altered.

ตอนนี้เรามีแค่ตาราง EMP และ DEPT (ที่ไม่มี Constraint) และ Optimizer เชื่อว่าทั้งสองตารางมีขนาดใหญ่ (จากการที่เราใช้ DBMS_STATS ในการบอกมัน) เราจะสร้าง MV ซึ่ง Join ตาราง EMP และ DEPT เข้าด้วยกัน และลองคิวรีเพื่อนับจำนวน Employees จำแนกตาม Department ดังแสดงใน Listing 10
==========================================================
Listing 10: Materialized View ที่ Join ตาราง EMP และ DEPT และนับจำนวน

SQL> create materialized view mv enable query rewrite
2 as
3 select dept.deptno, dept.dname, count(*) from emp, dept
4 where emp.deptno = dept.deptno
5 group by dept.deptno, dept.dname;
Materialized view created.

SQL> begin
2 dbms_stats.set_table_stats
3 ('SCOTT','MV',numrows=>100000, numblks=>10000);
4 end;
5 /
PL/SQL procedure successfully completed.
==========================================================
คราวนี้ถ้าเราคิวรีโดยใช้คำสั่งดังใน Listing 11 เราจะเห็นว่า Optimizer หลีกเลี่ยงที่จะใช้ตารางรายละเอียด (EMP, DEPT) และ Access ตาราง MV แทน คือแทนที่จะ Join ทั้งสองตารางแล้วนับจำนวนแถว Optimizer (ซึ่งรู้ว่ามี Materialized View อยู่ และให้ผลในการคิวรีเร็วกว่า) กลับใช้ Materialized View แทน
==========================================================
Listing 11: คิวรีที่ใช้ Materialized View

SQL> select dept.dname, count(*) from emp, dept
2 where emp.deptno = dept.deptno and dept.dname = 'SALES'
3 group by dept.dname;

SQL> select dept.dname, count(*) from emp, dept
2 where emp.deptno = dept.deptno and dept.dname = 'SALES'
3 group by dept.dname;

Execution Plan
----------------------------------------------------------
Plan hash value: 1703036361

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 2 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 1 | 22 | 2 (0)| 00:00:01 |
|* 2 | MAT_VIEW REWRITE ACCESS FULL| MV | 1 | 22 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MV"."DNAME"='SALES')
==========================================================
อย่างไรก็ตาม ถ้าเราถามคำถามที่แตกต่างออกไปเล็กน้อยดังแสดงใน Listing 12 เราจะเห็นว่า Optimizer ไม่ Rewrite คิวรีของเราให้ไปใช้ MV
==========================================================
Listing 12: คิวรีที่ไม่ได้ใช้ Materialized View

SQL> select count(*) from scott.emp;
COUNT(*)
----------
14

SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
SQL_ID abj9tmfcs15bm, child number 0
-------------------------------------
select count(*) from scott.emp

Plan hash value: 2083865914
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
| 2 | TABLE ACCESS FULL| EMP | 16 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
14 rows selected.
==========================================================
ดังที่เห็น Optimizer เลือกที่จะทำ Full Table Scan ตาราง EMP, นับจำนวนแถวแล้วแสดงผล เรารู้ว่า Optimizer สามารถที่จะเลือกใช้ MV ซึ่งมีขนาดเล็กกว่ามาก โดยการ Summary ข้อมูลที่ได้ถูก Count ไว้แต่แรกแล้ว แทนที่จะนับจำนวนเรคคอร์ดใน EMP ทีละอัน คิวรีสามารถที่จะ Sum ข้อมูลที่ได้ Count ไว้ (จำแนกตาม DEPTNO) ใน MV โดยใช้เวลาน้อยกว่ามาก เนื่องจากเรารู้ว่า
- คอลัมน์ DEPTNO ในตาราง DEPT เป็น Primary key
- คอลัมน์ DEPTNO ในตาราง EMP เป็น Foreign Key
- คอลัมน์ DEPTNO ในตาราง EMP ไม่เป็น NULL
เรารู้ข้อมูลข้างต้น แต่ระบบฐานข้อมูลไม่รู้ คราวนี้ถ้าเราบอกให้ระบบฐานข้อมูลทราบโดยการใส่ Constraint

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)
references dept(deptno);
Table altered.

SQL> alter table emp modify deptno not null;
Table altered.

คราวนี้ ต่อไปถ้าเราคิวรีด้วยคำสั่งเดิม เราจะเห็นว่า Optimizer ทำการ Rewrite คิวรีให้ไปใช้ MV ซึ่งมาขนาดเล็กกว่า แทนที่จะใช้ตาราง EMP ซึ่งมีขนาดใหญ่ ดังแสดงใน Listing 13
==========================================================
Listing 13: หลังจากที่ได้ใส่ Constraints คิวรีจะใช้ Materialized View

SQL> select count(*) from emp;

Execution Plan
----------------------------------------------------------
Plan hash value: 1747602359

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | MAT_VIEW REWRITE ACCESS FULL| MV | 3 | 39 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
14 rows selected
==========================================================

จบบทความเรื่อง "การใช้ Constraints เพื่อเพิ่มประสิทธิภาพในการคิวรี" โดยบริบูรณ์ครับ

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

2 comments:

Unknown said...

ขอบคุณครับ สำหรับความรู้ ผมได้เรียนรู้เพิ่มเยอะเลยครับ

Anonymous said...

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

Post a Comment