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-ตอนจบ)

6 comments:

yongyot said...

รอบทต่อไปอยู่นะครับ

Tanakorn Tavornsasnavong said...

ขอบคุณที่ให้ความสนใจนะครับ

ตอนที่ 2: http://tanakornt.blogspot.com/2009/06/constraints-2.html
ตอนที่ 3:
http://tanakornt.blogspot.com/2009/06/constraints-materialized-views.html

Anonymous said...

ได้ความรู้มากเลยครับ ขอบคุณที่แบ่งปันครับ

Anonymous said...

ขอบคุณมากๆค่ะ จะตามพื่อเป็นความรู้ต่อไปนะคะ

Mr.San said...

ขอบคุณสำหรับบทความดีๆ ครับ

Unknown said...

ผมอยากจะรู้ database oracle ใน view สามารถจะสร้าง PRIMARY KEY ได้หรือเปล่าครับ

Post a Comment