Materialized View: Refresh Fast VS Refresh Complete

Materialized View เป็นเครื่องมือในการทำสำรองข้อมูลขึ้นอีกชุด เพื่อใช้ประโยชน์ในด้านต่าง ๆ ที่เห็นได้ชัดคือการสำรองข้อมูลที่อยู่บน Database ตัวหนึ่งไปไว้บน Database อีกตัวหนึ่ง

เวลาเราสร้าง Materialized View จะมีสามออปชั่นหลัก ๆ ที่เกี่ยวกับการอัพเดทข้อมูลปลายทางให้เหมือนต้นทางคือ

1. เอาเฉพาะส่วนต่าง (Refresh Fast)

2. เอาทั้งหมดมาทับ (Refresh Complete)

3. เอาเฉพาะส่วนต่าง ถ้าไม่ได้ก็เอาทั้งหมดมาทับ (Refresh Force)

บทความนี้จะแสดงให้เห็นว่าเมื่อใดควรจะใช้ Fast Refresh และเมื่อใดควรใช้ Complete Refresh เราเริ่มจากการสร้างตารางทดสอบ TM เพื่อใช้เป็น Master จากนั้นเพิ่มข้อมูลให้กับตารางโดยการ Insert ... Select จนในตารางมีข้อมูลมากพอสมควร (ผมเพิ่มข้อมูลเข้าในตารางให้มีมากเพื่อจะได้เห็นความแตกต่างของเวลาตอน Refresh ได้ชัด หากผู้อ่านนำไปทดสอบอาจจะไม่ต้องใช้ข้อมูลมากขนาดนี้ก็น่าจะพอเห็นความแตกต่างของเวลาที่ใช้ในการ Refresh ครับ)

SQL> create table tm as select * from all_objects;
Table created
SQL> insert into tm select * from tm;
....
....
.....
SQL> select count(*) from tm;
COUNT(*)
----------
867668

เราจะสร้าง Materialized View Log ที่จำเป็นสำหรับการทำ Fast Refresh ก่อน จากนั้นจึงสร้าง Materialized View ชื่อ TM_MV ซึ่งเป็น Mview ที่ใช้วิธีการ Refresh แบบ Fast Refresh เนื่องจากตารางต้นทางไม่มี PK เราจึงต้องสร้าง Materialized View Log และ Materialized View บน ROWID แทน สังเกตเมื่อตอนสร้างเสร็จใหม่ ๆ Materialized View Log ไม่มีข้อมูลเลย (0 Row)

SQL> create materialized view log on tm with rowid;
Materialized view log created
SQL> select count(*) from mlog$_tm;
COUNT(*)
----------
0
SQL> create materialized view tm_mv refresh fast with rowid as select * from tm;
Materialized view created
SQL> select count(*) from tm_mv;
COUNT(*)
----------
867668

เราจะเริ่มทดสอบการทำ Fast Refresh โดยการ Insert ข้อมูลเข้าตาราง TM ซึ่งเป็น Master ของ Materialized View เป็นจำนวน 100 แถว สังเกตว่าในตาราง mlog$_tm ซึ่งเกิดจากการ Create Materialized View Log จะมีข้อมูลเพิ่มขึ้นมาด้วยจำนวนที่เท่า ๆ กัน หรือไม่ก็ใกล้เคียงกัน สังเกตว่าการ Refresh ข้อมูลจำนวน 100 เรคคอร์ด ใช้เวลาน้อยมาก (0.078 วินาที) เมื่อ Refresh เสร็จ จำนวนของข้อมูลใน Materialized View Log กลับไปเป็น 0

SQL> insert into tm select * from tm where rownum < 101;
100 rows inserted
SQL> commit;
Commit complete
SQL> select count(*) from mlog$_tm;
COUNT(*)
----------
100
SQL> exec dbms_mview.refresh('TM_MV');
PL/SQL procedure successfully completed
SQL> select count(*) from tm_mv;
COUNT(*)
----------
867768
SQL> select count(*) from mlog$_tm;
COUNT(*)
----------
0

คราวนี้ลองมาดูว่าถ้ามีการ Refresh ข้อมูลจำนวนมากแล้ว การใช้ Fast Refresh ยังคง "Fast" อยู่หรือไม่ เราจะ Delete ข้อมูลทั้งหมดออกก่อนแล้วจึง Insert ใหม่เข้าไปในตาราง Master จะเห็นว่าเมื่อเรา Refresh ข้อมูลจะใช้เวลาถึง 465 วินาทีทีเดียว ให้สังเกตจำนวนเรคคอร์ดใน Materialized View Log ไปด้วยทุกครั้งที่ทำ DML (Insert, Update, Delete) บนตาราง Master ซึ่งจะเพิ่มขึ้นเมื่อเรา Delete และ Insert

ก่อนจะลบข้อมูลออกจากตาราง TM ผมได้ทำสำรองข้อมูลไว้ก่อน จะได้ไม่ต้องมาเริ่มต้นกระบวนการสร้างข้อมูลใหม่อีกครั้ง โดยวิธีที่ใช้ในการสำรองข้อมูลคือ CTAS (Create Table As Select) แบบนี้ CREATE TABLE TM2 AS SELECT * FROM TM

SQL> select count(*) from tm;
COUNT(*)
----------
867768
SQL> select count(*) from tm_mv;
COUNT(*)
----------
867768
SQL> select count(*) from mlog$_tm;
COUNT(*)
----------
0
SQL> set timing on
SQL> delete from tm;
867768 rows deleted
Executed in 139.375 seconds
SQL> commit;
Commit complete
Executed in 0.078 seconds
SQL> select count(*) from mlog$_tm;
COUNT(*)
----------
867768
Executed in 15.657 seconds
SQL> insert into tm select * from tm2;
867668 rows inserted
Executed in 143.578 seconds
SQL> commit;
Commit complete
Executed in 0 seconds
SQL> select count(*) from mlog$_tm;
COUNT(*)
----------
1735436
Executed in 16.422 seconds
SQL> select count(*) from tm;
COUNT(*)
----------
867668
Executed in 14.657 seconds
SQL> select count(*) from tm_mv;
COUNT(*)
----------
867768
Executed in 1.765 seconds
SQL> exec dbms_mview.refresh('TM_MV');
PL/SQL procedure successfully completed
Executed in 465.156 seconds
SQL> set timing off

คราวนี้มาลองใช้ Materialized View แบบ Complete Refresh ดูบ้าง เรา Drop Materialized View TM_MV และ Materialized View Log บนตาราง TM ออก แล้วจึงสร้าง Materialized View TM_MV ใหม่ให้เป็นแบบ Complete Refresh

SQL> drop materialized view tm_mv;
Materialized view dropped
SQL> drop materialized view log on tm;
Materialized view log dropped
SQL> select count(*) from tm;
COUNT(*)
----------
867668
SQL> create materialized view tm_mv refresh complete with rowid as select * from tm;
Materialized view created
SQL> select count(*) from tm_mv;
COUNT(*)
----------
867668

จากนั้นเราจะลบข้อมูลในตาราง TM ซึ่งเป็นตาราง Master แล้วจึง Insert เข้าไปใหม่ แล้วจึง Refresh Materialized View TM_MV จะเห็นว่า Complete Refresh ใช้เวลาเพียง 68 วินาที

SQL> set timing on
SQL> delete from tm;
867668 rows deleted
Executed in 69.875 seconds
SQL> insert into tm select * from tm2;
867668 rows inserted
Executed in 40.468 seconds
SQL> commit;
Commit complete
Executed in 0.015 seconds
SQL> exec dbms_mview.refresh('TM_MV');
PL/SQL procedure successfully completed
Executed in 67.593 seconds
SQL> set timing off

คราวนี้ลองมาดูผลการ Refresh ถ้าตาราง Master ใช้วิธีการ Truncate แล้วจึงค่อย Insert จะเห็นว่าเวลาที่ใช้ในการ Refresh Complete ไม่ต่างกัน แต่หากถ้าเราใช้ Fast Refresh (ซึ่งต้องใช้ร่วมกับ Materialized View Log) เราจะพบ Error ORA-12034: materialized view log on ... younger than last refresh

SQL> truncate table tm;
Table truncated
SQL> insert into tm select * from tm2;
867668 rows inserted
SQL> commit;
Commit complete
SQL> set timing on
SQL> exec dbms_mview.refresh('TM_MV');
PL/SQL procedure successfully completed
Executed in 63.218 seconds
SQL> set timing off
SQL> drop materialized view tm_mv;
Materialized view dropped
SQL> create materialized view log on tm with rowid;
Materialized view log created
SQL> create materialized view tm_mv refresh fast with rowid as select * from tm;
Materialized view created
SQL> select count(*) from tm;
COUNT(*)
----------
867668
SQL> select count(*) from tm_mv;
COUNT(*)
----------
867668
SQL> select count(*) from mlog$_tm;
COUNT(*)
----------
0
SQL> exec dbms_mview.refresh('TM_MV');
PL/SQL procedure successfully completed
SQL> set timing on
SQL> truncate table tm;
Table truncated
Executed in 2.14 seconds
SQL> insert into tm select * from tm2;
867668 rows inserted
Executed in 106.031 seconds
SQL> commit;
Commit complete
Executed in 0 seconds
SQL> select count(*) from mlog$_tm;
COUNT(*)
----------
867668
Executed in 15.593 seconds
SQL> exec dbms_mview.refresh('TM_MV');
begin dbms_mview.refresh('TM_MV'); end;
ORA-12034: materialized view log on "SCOTT"."TM" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858
ORA-06512: at line 1

กล่าวโดยสรุป

1. ใช้ Complete Refresh เมื่อมีการเปลี่ยนแปลงข้อมูลบนตาราง Master เป็นจำนวนมาก (ตาราง Master ที่มีลักษณะเป็น Batch Process) และใช้ Complete Refresh เมื่อตารางมีการเปลี่ยนแปลงแบบล้างข้อมูลออกทั้งหมดแล้วแทนที่ด้วยข้อมูลใหม่ รวมทั้งกรณี Truncate ด้วย
2. ใช้ Fast Refresh เมื่อตาราง Master มีการเปลี่ยนแปลงข้อมูลเพียงเล็กน้อย และอาจจะมีการเปลี่ยนแปลงบ่อย ๆ (ตาราง Master มีลักษณะเป็น Transaction Process)