(แปลจาก “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)
Showing posts with label not null. Show all posts
Showing posts with label not null. Show all posts
Saturday, June 20, 2009
Sunday, May 31, 2009
การใช้ Constraints เพื่อเพิ่มประสิทธิภาพในการคิวรี (ตอนที่ 1)
(แปลจาก “On Constraints, Metadata, and Truth” โดย Tom Kyte, Oracle Magazine V XXIII, Issue3)
เป็นเรื่องน่าสนใจว่า Constraints สำคัญต่อความเร็วในการคิวรีเพียงใด คนส่วนใหญ่จะคิดว่า Constraints เป็นเรื่องที่เกี่ยวกับ Data Integrity ซึ่งก็ถูก แต่ Constraints ก็มีผลต่อ Optimizer ในการคิด Execution Plan เช่นกัน ซึ่งองค์ประกอบของตัวแปรที่ Optimizer ใช้ในการคำนวณได้แก่
• คำสั่ง SQL
• ค่าสถิติของตารางหรือ Database Objects อื่นๆ ที่มี ทั้งหมดที่เกี่ยวข้อง
• ข้อมูลทางสถิติของระบบ เช่น ความเร็ว CPU, ความเร็วในการอ่าน 1 Data Block เป็นต้น
• ตัวแปรตั้งต้นของฐานข้อมูล (Initialization Parameters)
• และ Constaints!
Optimizer จะใช้ข้อมูลเหล่านี้ทั้งหมดในการคำนวณ Execution Plan ที่ดีที่สุด ผมพบว่าบางครั้ง เรามีแนวโน้มที่จะไม่ใช้ Constraints ใน Data Warehouse หรือในระบบที่ใช้ในการออก Report โดยอ้างว่า “ข้อมูลดีอยู่แล้ว เราได้ทำการ Cleansing ข้อมูลแล้ว เราไม่ต้องใช้ Constraints เพื่อควบคุมความสอดคล้องกันของข้อมูล (Data Integrity) แล้ว”
ความจริงก็คือเขาอาจจะไม่ต้องการ Constraints ในการควบคุมความสอดคล้องกันของข้อมูล (แต่เขาอาจจะแปลกใจถ้าลองได้ลองเปิด (Enable) Constraints ขึ้นมาจริง ๆ) อย่างไรก็ตามเขายังคงต้องการ Constraints เพื่อที่จะได้ Execution Plan ที่ดีที่สุด ในระบบขนาดใหญ่อย่าง Data Warehouse ถ้าคิวรีมี Execution Plan ที่ไม่ดีอาจจะต้องใช้เวลาเป็นชั่วโมงหรือเป็นวัน แทนที่จะเป็นวินาทีหรือเป็นนาที ดังนั้น Data Warehouse จึงจำเป็นจะต้องมี Constraints เพื่อประโยชน์ในแง่ความเร็วในการคิวรี
ลองดูตัวอย่าง (ทดลองกับ Oracle 11g Release 11.1.0.6) ตัวอย่างแรกเป็นคุณสมบัติของ Optimizer ที่เรียกว่า Partition View Elimination ซึ่งมีมาตั้งแต่เวอร์ชั่น 7.3 กล่าวคือถ้าเราให้ข้อมูลกับ Optimizer มากพอ (อาจจะโดยการกำหนด Constraints) มันก็จะสามารถตัดเอาบางตารางที่ไม่จำเป็นต้องใช้ในการคิวรีได้
ใน Listing1 เราสร้างตารางขึ้นมาสองตาราง ซึ่งเก็บข้อมูลที่เป็นคนละชุดกัน ไม่มีข้อมูลใดที่อยู่ในทั้งสองตาราง (Mutually Exclusive) โดยใช้คอลัมน์ OBJECT_TYPE และเราก็ได้กำหนด View ซึ่ง UNION ALL ตารางทั้งสองเข้าด้วยกัน
==========================================================
Listing1: สร้างตารางสองตารางที่มีข้อมูลไม่ซ้ำกัน และสร้างวิว
SQL> create table t1
2 as
3 select * from all_objects
4 where object_type in ('TABLE','VIEW');
Table created.
SQL> alter table t1 modify object_type not null;
Table altered.
SQL> alter table t1 add constraint t1_check_otype
2 check (object_type in ('TABLE','VIEW'));
Table altered.
SQL> create table t2
2 as
3 select * from all_objects
4 where object_type in ('SYNONYM','PROCEDURE');
Table created.
SQL> alter table t2 modify object_type not null;
Table altered.
SQL> alter table t2 add constraint t2_check_otype
2 check (object_type in ('SYNONYM','PROCEDURE'));
Table altered.
SQL> create or replace view v
2 as
3 select * from t1
4 union all
5 select * from t2;
View created.
==========================================================
คราวนี้ใน Listing2 เราคิวรีวิวตัวที่เราสร้างนี้ (V) และใช้ OBJECT_TYPE ในส่วน Where ของคำสั่ง และดูว่า Optimizer ตอบสนองยังไง
==========================================================
Listing2: การตอบสนองของ Optimizer
SQL> set autotrace traceonly explain
SQL> select * from v where object_type='TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 3982894595
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 5372 | 128 (1)| 00:00:02 |
| 1 | VIEW | V | 34 | 5372 | 128 (1)| 00:00:02 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL | T1 | 136 | 21488 | 10 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| T2 | 5 | 790 | 119 (0)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OBJECT_TYPE"='TABLE')
4 - filter(NULL IS NOT NULL)
5 - filter("OBJECT_TYPE"='TABLE')
==========================================================
ครั้งแรกดูเหมือนว่าใน Execution Plan ไม่ได้เอาตาราง T2 ออก (เพราะใน T2 ไม่มี OBJECT_TYPE เป็น ‘TABLE’) แต่เมื่อดูลึก ๆ จะพบว่ามีการ Filter ที่ข้อ 4 ด้วยดังนี้
“NULL IS NOT NULL”
ซึ่งน่าสนใจตรงที่ว่า เราไม่ได้เป็นคนกำหนด Filter ตัวนี้ และเนื่องจาก NULL IS NOT NULL เป็นเท็จเสมอ Execution Plan ตัวนี้จึงไม่เคยถูกใช้เลย (ถ้าเราใช้ TKPROF เราจะพบว่าไม่มี I/O เกิดขึ้นบนตาราง T2 เลย
คราวนี้มาดูตัวอย่างที่สอง เราจะมาดูที่ NOT NULL Constraint และดูว่าทำไมมันจึงสำคัญเอามาก ๆ เราจะสร้างตาราง และอินเด็กซ์คอลัมน์ที่สามารถเป็น NULL ได้
SQL> create table t
2 as
3 select * from all_objects;
Table created.
SQL> create index t_idx on t(object_type);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,’T’);
PL/SQL procedure successfully completed.
คราวนี้เราจะนับจำนวนแถวในตารางนี้ ซึ่งโดยปกติ Optimizer จะมีวิธีการเดียวในการแสดงผลดังใน Listing3
==========================================================
Listing3: วิธีการเดียวที่ใช้ในการนับจำนวนแถว
SQL> set autotrace traceonly explain
SQL> select count(*) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 228 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 53798 | 228 (1)| 00:00:03 |
-------------------------------------------------------------------
==========================================================
เนื่องจากว่า OBJECT_TYPE สามารถเป็น NULLได้ และอินเด็กซ์จะไม่เก็บข้อมูลใด ๆ ถ้าทุก ๆ คอลัมน์ที่ประกอบเป็นอินเด็กซ์ล้วนเป็น NULL ดังนั้นเรา (Optimizer) จึงไม่สามารถนับจำนวนแถวในตารางโดยใช้อินเด็กซ์ได้ – เราจึงต้องใช้วิธีการ Full Table Scan ดังนั้นถ้าเราบอก Optimizer ว่า OBJECT_TYPE IS NOT NULL เราจะเห็นว่า Execution Plan เปลี่ยนไป (ในทางที่ดีกว่า) ดังแสดงใน Listing4
==========================================================
Listing4: วิธีที่ดีกว่าที่จะนับจำนวนแถว
SQL> alter table t modify object_type NOT NULL;
Table altered.
SQL> set autotrace traceonly explain;
SQL> select count(*) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1058879072
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| T_IDX | 53798 | 43 (0)| 00:00:01 |
==========================================================
แต่ว่าถ้ายังไง ๆ เราก็ต้องให้ OBJECT_TYPE เป็น NULL ล่ะ มีวิธีการใดพอจะช่วยได้ไหม? -- สิ่งที่เราสามารถทำได้ก็คือเพิ่มคอลัมน์ใด ๆ ที่เป็น NOT NULL เข้าไปในอินเด็กซ์ ซึ่งจะทำให้อินเด็กซ์สามารถถูกใช้ได้ตอนทำ Full Table Scan ได้ แม้ว่าคิวรีจะมี WHERE Clause เป็น OBJECT_TYPE IS NULL ซึ่งอาจจะทำให้หลาย ๆ คนแปลกใจด้วยความเชื่อที่ว่า “IS NULL” ไม่สามารถใช้อินเด็กซ์ ซึ่งผมเชื่อว่าเนื่องมาจากคนส่วนใหญ่คิดว่าค่าที่เป็น Null จะไม่ถูกเก็บในอินเด็กซ์
ความจริงก็คือค่า Null อาจจะถูกอินเด็กซ์ได้ เพียงแต่ถ้าทุก ๆ คอลัมน์ที่ประกอบกันเป็นอินเด็กซ์มีค่าเป็น Null จึงจะไม่ถูกเก็บในอินเด็กซ์ ดังนั้นถ้ามีอย่างน้อยหนึ่งคอลัมน์ในอินเด็กซ์ที่ NOT NULL ทุกๆ แถวในตารางจะถูกเก็บในอินเด็กซ์ ลองดู Listing5
==========================================================
Listing5: Execution Plan ที่ใช้ Full Table Scan เมื่อ OBJECT_TYPE สามารถเป็น NULL
SQL> alter table t modify object_type NULL;
Table altered.
SQL> set autotrace traceonly explain
SQL> select * from t where object_type is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 102 | 228 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 1 | 102 | 228 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE" IS NULL)
==========================================================
แสดงให้เห็นว่าถ้า OBJECT_TYPE สามารถเป็น NULL ได้ Optimizer จะไม่สามารถใช้อินเด็กซ์ในการหา OBJECT_TYPE IS NULL ได้ ถ้าเราเพิ่มคอลัมน์ที่เป็น NOT NULL เข้าไปในอินเด็กซ์ Execution Plan จะเปลี่ยนไป ซึ่งในที่นี่ผมเพิ่มค่า 0 เข้าไปในอินเด็กซ์ (จริง ๆ แล้วค่าใด ๆ ก็ได้ ขอแค่ NOT NULL ก็พอ ซึ่ง 0 เป็นค่าที่เล็กมาก ผมจึงนำมาใช้)
SQL> drop index t_idx;
Index dropped.
SQL> create index t_idx
2 on t (object_type,0);
Index created.
ใน Listing6 แสดง Execution Plan ที่สามารถใช้อินเด็กซ์
==========================================================
Listing6: Execution Plan ที่ใช้อินเด็กซ์เมื่ออินเด็กซ์มีคอลัมน์ที่เป็น NOT NULL
SQL> select * from t where object_type is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 470836197
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 102 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 102 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE" IS NULL) ==========================================================
(ยังไม่จบนะครับ ยังมีตอนต่อไป...ขอขอบคุณที่ให้ความสนใจและโปรดติดตามตอนต่อไปนะครับ)
อ่านเพิ่มเติม:
การใช้ Constraints เพื่อเพิ่มประสิทธิภาพในการคิวรี (ตอนที่ 2)
การใช้ Constraints เพื่อเพิ่มประสิทธิภาพในการคิวรี (ตอนที่ 3-ตอนจบ)
เป็นเรื่องน่าสนใจว่า Constraints สำคัญต่อความเร็วในการคิวรีเพียงใด คนส่วนใหญ่จะคิดว่า Constraints เป็นเรื่องที่เกี่ยวกับ Data Integrity ซึ่งก็ถูก แต่ Constraints ก็มีผลต่อ Optimizer ในการคิด Execution Plan เช่นกัน ซึ่งองค์ประกอบของตัวแปรที่ Optimizer ใช้ในการคำนวณได้แก่
• คำสั่ง SQL
• ค่าสถิติของตารางหรือ Database Objects อื่นๆ ที่มี ทั้งหมดที่เกี่ยวข้อง
• ข้อมูลทางสถิติของระบบ เช่น ความเร็ว CPU, ความเร็วในการอ่าน 1 Data Block เป็นต้น
• ตัวแปรตั้งต้นของฐานข้อมูล (Initialization Parameters)
• และ Constaints!
Optimizer จะใช้ข้อมูลเหล่านี้ทั้งหมดในการคำนวณ Execution Plan ที่ดีที่สุด ผมพบว่าบางครั้ง เรามีแนวโน้มที่จะไม่ใช้ Constraints ใน Data Warehouse หรือในระบบที่ใช้ในการออก Report โดยอ้างว่า “ข้อมูลดีอยู่แล้ว เราได้ทำการ Cleansing ข้อมูลแล้ว เราไม่ต้องใช้ Constraints เพื่อควบคุมความสอดคล้องกันของข้อมูล (Data Integrity) แล้ว”
ความจริงก็คือเขาอาจจะไม่ต้องการ Constraints ในการควบคุมความสอดคล้องกันของข้อมูล (แต่เขาอาจจะแปลกใจถ้าลองได้ลองเปิด (Enable) Constraints ขึ้นมาจริง ๆ) อย่างไรก็ตามเขายังคงต้องการ Constraints เพื่อที่จะได้ Execution Plan ที่ดีที่สุด ในระบบขนาดใหญ่อย่าง Data Warehouse ถ้าคิวรีมี Execution Plan ที่ไม่ดีอาจจะต้องใช้เวลาเป็นชั่วโมงหรือเป็นวัน แทนที่จะเป็นวินาทีหรือเป็นนาที ดังนั้น Data Warehouse จึงจำเป็นจะต้องมี Constraints เพื่อประโยชน์ในแง่ความเร็วในการคิวรี
ลองดูตัวอย่าง (ทดลองกับ Oracle 11g Release 11.1.0.6) ตัวอย่างแรกเป็นคุณสมบัติของ Optimizer ที่เรียกว่า Partition View Elimination ซึ่งมีมาตั้งแต่เวอร์ชั่น 7.3 กล่าวคือถ้าเราให้ข้อมูลกับ Optimizer มากพอ (อาจจะโดยการกำหนด Constraints) มันก็จะสามารถตัดเอาบางตารางที่ไม่จำเป็นต้องใช้ในการคิวรีได้
ใน Listing1 เราสร้างตารางขึ้นมาสองตาราง ซึ่งเก็บข้อมูลที่เป็นคนละชุดกัน ไม่มีข้อมูลใดที่อยู่ในทั้งสองตาราง (Mutually Exclusive) โดยใช้คอลัมน์ OBJECT_TYPE และเราก็ได้กำหนด View ซึ่ง UNION ALL ตารางทั้งสองเข้าด้วยกัน
==========================================================
Listing1: สร้างตารางสองตารางที่มีข้อมูลไม่ซ้ำกัน และสร้างวิว
SQL> create table t1
2 as
3 select * from all_objects
4 where object_type in ('TABLE','VIEW');
Table created.
SQL> alter table t1 modify object_type not null;
Table altered.
SQL> alter table t1 add constraint t1_check_otype
2 check (object_type in ('TABLE','VIEW'));
Table altered.
SQL> create table t2
2 as
3 select * from all_objects
4 where object_type in ('SYNONYM','PROCEDURE');
Table created.
SQL> alter table t2 modify object_type not null;
Table altered.
SQL> alter table t2 add constraint t2_check_otype
2 check (object_type in ('SYNONYM','PROCEDURE'));
Table altered.
SQL> create or replace view v
2 as
3 select * from t1
4 union all
5 select * from t2;
View created.
==========================================================
คราวนี้ใน Listing2 เราคิวรีวิวตัวที่เราสร้างนี้ (V) และใช้ OBJECT_TYPE ในส่วน Where ของคำสั่ง และดูว่า Optimizer ตอบสนองยังไง
==========================================================
Listing2: การตอบสนองของ Optimizer
SQL> set autotrace traceonly explain
SQL> select * from v where object_type='TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 3982894595
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 5372 | 128 (1)| 00:00:02 |
| 1 | VIEW | V | 34 | 5372 | 128 (1)| 00:00:02 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL | T1 | 136 | 21488 | 10 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| T2 | 5 | 790 | 119 (0)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("OBJECT_TYPE"='TABLE')
4 - filter(NULL IS NOT NULL)
5 - filter("OBJECT_TYPE"='TABLE')
==========================================================
ครั้งแรกดูเหมือนว่าใน Execution Plan ไม่ได้เอาตาราง T2 ออก (เพราะใน T2 ไม่มี OBJECT_TYPE เป็น ‘TABLE’) แต่เมื่อดูลึก ๆ จะพบว่ามีการ Filter ที่ข้อ 4 ด้วยดังนี้
“NULL IS NOT NULL”
ซึ่งน่าสนใจตรงที่ว่า เราไม่ได้เป็นคนกำหนด Filter ตัวนี้ และเนื่องจาก NULL IS NOT NULL เป็นเท็จเสมอ Execution Plan ตัวนี้จึงไม่เคยถูกใช้เลย (ถ้าเราใช้ TKPROF เราจะพบว่าไม่มี I/O เกิดขึ้นบนตาราง T2 เลย
คราวนี้มาดูตัวอย่างที่สอง เราจะมาดูที่ NOT NULL Constraint และดูว่าทำไมมันจึงสำคัญเอามาก ๆ เราจะสร้างตาราง และอินเด็กซ์คอลัมน์ที่สามารถเป็น NULL ได้
SQL> create table t
2 as
3 select * from all_objects;
Table created.
SQL> create index t_idx on t(object_type);
Index created.
SQL> exec dbms_stats.gather_table_stats(user,’T’);
PL/SQL procedure successfully completed.
คราวนี้เราจะนับจำนวนแถวในตารางนี้ ซึ่งโดยปกติ Optimizer จะมีวิธีการเดียวในการแสดงผลดังใน Listing3
==========================================================
Listing3: วิธีการเดียวที่ใช้ในการนับจำนวนแถว
SQL> set autotrace traceonly explain
SQL> select count(*) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 228 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 53798 | 228 (1)| 00:00:03 |
-------------------------------------------------------------------
==========================================================
เนื่องจากว่า OBJECT_TYPE สามารถเป็น NULLได้ และอินเด็กซ์จะไม่เก็บข้อมูลใด ๆ ถ้าทุก ๆ คอลัมน์ที่ประกอบเป็นอินเด็กซ์ล้วนเป็น NULL ดังนั้นเรา (Optimizer) จึงไม่สามารถนับจำนวนแถวในตารางโดยใช้อินเด็กซ์ได้ – เราจึงต้องใช้วิธีการ Full Table Scan ดังนั้นถ้าเราบอก Optimizer ว่า OBJECT_TYPE IS NOT NULL เราจะเห็นว่า Execution Plan เปลี่ยนไป (ในทางที่ดีกว่า) ดังแสดงใน Listing4
==========================================================
Listing4: วิธีที่ดีกว่าที่จะนับจำนวนแถว
SQL> alter table t modify object_type NOT NULL;
Table altered.
SQL> set autotrace traceonly explain;
SQL> select count(*) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1058879072
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| T_IDX | 53798 | 43 (0)| 00:00:01 |
==========================================================
แต่ว่าถ้ายังไง ๆ เราก็ต้องให้ OBJECT_TYPE เป็น NULL ล่ะ มีวิธีการใดพอจะช่วยได้ไหม? -- สิ่งที่เราสามารถทำได้ก็คือเพิ่มคอลัมน์ใด ๆ ที่เป็น NOT NULL เข้าไปในอินเด็กซ์ ซึ่งจะทำให้อินเด็กซ์สามารถถูกใช้ได้ตอนทำ Full Table Scan ได้ แม้ว่าคิวรีจะมี WHERE Clause เป็น OBJECT_TYPE IS NULL ซึ่งอาจจะทำให้หลาย ๆ คนแปลกใจด้วยความเชื่อที่ว่า “IS NULL” ไม่สามารถใช้อินเด็กซ์ ซึ่งผมเชื่อว่าเนื่องมาจากคนส่วนใหญ่คิดว่าค่าที่เป็น Null จะไม่ถูกเก็บในอินเด็กซ์
ความจริงก็คือค่า Null อาจจะถูกอินเด็กซ์ได้ เพียงแต่ถ้าทุก ๆ คอลัมน์ที่ประกอบกันเป็นอินเด็กซ์มีค่าเป็น Null จึงจะไม่ถูกเก็บในอินเด็กซ์ ดังนั้นถ้ามีอย่างน้อยหนึ่งคอลัมน์ในอินเด็กซ์ที่ NOT NULL ทุกๆ แถวในตารางจะถูกเก็บในอินเด็กซ์ ลองดู Listing5
==========================================================
Listing5: Execution Plan ที่ใช้ Full Table Scan เมื่อ OBJECT_TYPE สามารถเป็น NULL
SQL> alter table t modify object_type NULL;
Table altered.
SQL> set autotrace traceonly explain
SQL> select * from t where object_type is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 102 | 228 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 1 | 102 | 228 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE" IS NULL)
==========================================================
แสดงให้เห็นว่าถ้า OBJECT_TYPE สามารถเป็น NULL ได้ Optimizer จะไม่สามารถใช้อินเด็กซ์ในการหา OBJECT_TYPE IS NULL ได้ ถ้าเราเพิ่มคอลัมน์ที่เป็น NOT NULL เข้าไปในอินเด็กซ์ Execution Plan จะเปลี่ยนไป ซึ่งในที่นี่ผมเพิ่มค่า 0 เข้าไปในอินเด็กซ์ (จริง ๆ แล้วค่าใด ๆ ก็ได้ ขอแค่ NOT NULL ก็พอ ซึ่ง 0 เป็นค่าที่เล็กมาก ผมจึงนำมาใช้)
SQL> drop index t_idx;
Index dropped.
SQL> create index t_idx
2 on t (object_type,0);
Index created.
ใน Listing6 แสดง Execution Plan ที่สามารถใช้อินเด็กซ์
==========================================================
Listing6: Execution Plan ที่ใช้อินเด็กซ์เมื่ออินเด็กซ์มีคอลัมน์ที่เป็น NOT NULL
SQL> select * from t where object_type is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 470836197
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 102 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 102 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE" IS NULL) ==========================================================
(ยังไม่จบนะครับ ยังมีตอนต่อไป...ขอขอบคุณที่ให้ความสนใจและโปรดติดตามตอนต่อไปนะครับ)
อ่านเพิ่มเติม:
การใช้ Constraints เพื่อเพิ่มประสิทธิภาพในการคิวรี (ตอนที่ 2)
การใช้ Constraints เพื่อเพิ่มประสิทธิภาพในการคิวรี (ตอนที่ 3-ตอนจบ)
Subscribe to:
Comments (Atom)
