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)

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

Sunday, June 14, 2009

การแบ็คอัพฐานข้อมูลในโหมด No Archivelog โดยใช้ Enterprise Manager

Oracle ได้เตรียมวิธีการแบ็คอัพแบบที่เชื่อถือได้ว่าจะสามารถกู้ข้อมูลคืนได้เมื่อเกิดการสูญเสียของข้อมูลอันเนื่องมาจาก Media Failure (การเสียของอุปกรณ์เช่นฮาร์ดดิสก์ หรือการที่ไฟล์เกิดเสียเป็นต้น) ลองดูสิครับ วิธีนี้ช่วยชีวิตผมมาได้หลายครั้งแล้วครับ

Database ในโหมด No Archivelog
เพื่อเป็นการลดภาระของเครื่อง server ที่ใช้ในการพัฒนาระบบ ซึ่งมักจะเป็นเครื่องที่มีประสิทธิภาพปานกลาง หรือค่อนข้างต่ำ หรือเพื่อให้สามารถรองรับงานของระบบทดสอบอื่น ๆ ได้ไปพร้อม ๆ กัน ระบบฐานข้อมูลที่ใช้บนเครื่องทดสอบอาจจะคอนฟิคให้เป็นระบบ no archivelog mode โดยในโหมดนี้การแบ็คอัพจะต้องทำการปิดระบบฐานข้อมูล (cold or offline backup) และการจะ recover ก็จะทำได้ถึงจุดที่ได้ทำการ backup ไว้เท่านั้น เนื่องจากระบบฐานข้อมูลในโหมดนี้ไม่มี archivelog (ซึ่งช่วยในการบันทึกรายละเอียดการทำงานบนฐานข้อมูลระหว่างแต่ละชุดของแบ็คอัพ)

1. ขั้นตอนแรกเราจะเช็คดูว่า database อยู่ใน no archivelog mode
การดูว่าดาต้าเบสอยู่ใน mode archivelog หรือเปล่า

เปิด Oracle Enterprise Manager(EM) => คลิ๊ก Maintenance tab => คลิ๊ก Recovery Setting => ตรงหัวข้อ Media Recovery
เช็คดูว่า ARCHIVLOG mode ไม่มีเครื่องหมายถูก



ถ้าหากมีเครื่องหมายถูกหน้า ARCHIVELOG Mode แสดงว่าฐานข้อมูลอยู่ใน archivelog mode ให้คลิ๊กที่หน้า ARCHIVELOG Mode เพื่อลบเครื่องหมายถูก แล้วคลิ๊ก Apply จะมีหน้า Confirmation ขึ้นมาเตือนว่าฐานข้อมูลจะ restart ให้คลิ๊ก Yes จะปรากฏหน้า Restart Database: Specify Host and Target Database Credentials



- ตรง Host Credentials ให้ใส่ OS username / password ที่มีสิทธิ์ในไดเรคทอรีที่เกี่ยวข้องเช่น flash_recovery_area
- ตรง Database Credentials ให้ใส่ user ที่ได้สิทธิ์ SYSDBA เช่น SYS เป็นต้น
- คลิ๊ก OK จะปรากฎหน้า Restart Database: Confirmation
- คลิ๊ก YES ระบบฐานข้อมูลจะ shutdown และ เปิดขึ้นมาด้วย no archivelog mode

อาจจะต้องรอสักพักแล้วจึงกด refresh ดูว่า Database start เสร็จแล้วหรือยัง

2. กำหนดขนาดของ Flash Recover Area โดยให้มีขนาดอย่างน้อยที่สุดเท่ากับ
ขนาดของ datafile (.DBF) ทั้งหมดรวมกัน ยกเว้น datafile ที่เป็นของ temporary tablespace
+ ขนาดของไฟล์ incremental backup จำนวน 1 ไฟล์

เปิด em => คลิ๊ก Maintenance tab => คลิ๊ก Recovery Setting => ตรงหัวข้อ Flash Recovery
ในตัวอย่างนี้ เราจะตั้งเป็น 5GB

- พิมพ์ 5 ลงในช่อง Flash Recovery Area Size และคลิ๊ก Apply



3. เมื่อตั้งค่าสถานที่จัดเก็บและโหมดของ database แล้ว ต่อไปคือการตั้งค่าให้กับโปรแกรมการแบ็คอัพ หรือ RMAN ดังนี้
เปิด em => คลิ๊ก Maintenance tab => คลิ๊ก Backup Setting => คลิ๊ก Policy



- ตรง Retention Policy เพื่อกำหนดว่าจะให้คงไฟล์แบ็คอัพไว้ได้นานแค่ไหน เช่น RETENTION POLICY=1 หมายความว่าจะเก็บไฟล์แบคอัพไว้เพียงชุดเดียว ถ้ามีไฟล์แบ็คอัพใหม่ออกมาตัวเก่าก็จะถูกกำหนดให้ obsolete รอเวลาการลบออกเป็นต้น ให้เลือก ’Retain at least the specified number of full backups for each datafile’
- ภายใต้หัวข้อ Backup Policy เช็คถูกที่ Optimize the whole database by skipping unchanged… ถ้าใน Flash Recovery Area มีไฟล์แบ็คอัพที่มีข้อมูลแบ็คอัพชุดเดียวกัน (เช่นมี SCN ชุดเดียวกัน) อยู่แล้ว ก็จะไม่แบ็คอัพซ้ำเพื่อประสิทธิภาพในการแบ็คอัพและพื้นที่จัดเก็บ
- ภายใต้หัวข้อ Backup Policy เช็คถูกที่ Automatically backup the contro lfile… เพื่อกำหนดให้แบ็คอัพ controlfile และ spfile ทุกครั้งที่แบ็คอัพ กรณีไม่ได้ใช้ RMAN catalog ควรใช้ฟีเจอร์นี้
- ภายใต้หัวข้อ Host Credentials ใส่ username และ password ของ OS
- อันนี้ ถ้ามีเชื่อมต่อกับเทปแบ็คอัพก็คลิ๊ก Device แท็ป แล้วใส่ Media Management Vendor Library Parameters ในหัวข้อ Media Management Setting

4. เมื่อจบขั้นตอนที่ 3 เราก็พร้อมที่จะทำการแบ็คอัพแล้ว กลับไปที่แท็ป Maintenance ตรงหัวข้อ High Availability / Backup/Recovery => คลิ๊ก Schedule



- ภายใต้หัวข้อ Host Credentials ใส่ username และ password ของ OS แล้ว คลิ๊ก Schedule customized backup
- คลิ๊ก Schedule Oracle-Suggested Backup



ที่หน้า Schedule Oracle-Suggested Backup: Destination
- เลือก Disk
- คลิ๊ก Next



ที่หน้า Schedule Oracle-Suggested Backup: Setup
เป็นการอธิบายถึงการทำงานของ backup process สังเกต Disk Settings จะบอกตำแหน่งของ Flash Recovery Area
- คลิ๊ก Next



ที่หน้า Schedule Oracle-Suggested Backup: Schedule
ให้กำหนดเวลาที่จะแบ็คอัพในหน้านี้ ถ้าจะให้เริ่มวันนี้เลยก็ปล่อยค่าใน Start Date เป็นค่า default ปล่อยค่า Time Zone เป็น GMT +7:00 และตั้งค่าเวลาที่ต้องการให้เริ่ม backup
- คลิ๊ก Next



หน้าสุดท้ายจะเป็นหน้าสรุปเพื่อให้เราตรวจสอบอีกทีหนึ่ง โดยสคริปต์ที่ได้จะมีลักษณะดังต่อไปนี้

Daily Script:
run {
allocate channel oem_disk_backup device type disk;
recover copy of database with tag 'ORA$OEM_LEVEL_0';
backup incremental level 1 cumulative copies=1 for recover of copy with tag 'ORA$OEM_LEVEL_0' database;
}

บรรทัดที่ 1 เป็นคำสั่งรันคำสั่งต่าง ๆ ที่อยู่ในวงเล็บปีกกา
บรรทัดที่ 2 เป็นการ allocate device channel ที่จะใช้ในการรันคำสั่งอาจจะเป็น disk หรือ tape (sbt)
บรรทัดที่ 3 ให้อัพเดท ( recover) ไฟล์แบ็คอัพ level 0 ด้วยไฟล์แบ็คอัพ incremental level 1ตัวล่าสุด
บรรทัดที่ 4 แบ็คอัพทั้งฐานข้อมูลแบบ incremental level 0 ก่อนในวันแรก และ level 1 ในวันต่อๆ ไป โดยใช้ชื่อแบ็คอัพว่า ORA$OEM_LEVEL_0

ตัวอย่างนี้เป็นการที่จะให้ rman ทำการแบ็คอัพแบบ whole database ในวันแรก และ incremental ในวันถัดไป โดยตัวที่เป็น whole database แบ็คอัพนั้น จะถูกอัพเดทด้วยไฟล์ incremental แบ็คอัพทุก ๆ วัน ทำให้เราสามารถใช้ file ที่เกิดจาก whole backup ร่วมกับไฟล์แบ็คอัพ incremental ของแต่ละวันในการกู้ข้อมูลกลับมาได้ โดยจะได้เท่าที่ได้แบ็คอัพไว้

ผลของการรันสคริปต์ข้างบนทุกวันจะเป็นดังนี้
> ในวันแรก, คำสั่ง RECOVER จะไม่มีผลอะไร, ส่วนคำสั่ง BACKUP... FOR RECOVER OF COPY... จะสร้างไฟล์แบ็คอัพ level 0 (คล้ายการ copy ไฟล์จาก original datafiles)
> วันที่สอง, คำสั่ง RECOVER ยังคงไม่ทำอะไร เพราะว่ายังไม่มีไฟล์แบ็คอัพ incremental level1 มาให้อัพเดท, ส่วนคำสั่ง BACKUP... จะสร้างไฟล์แบ็คอัพที่เป็น incremental level 1
> วันต่อมา, คำสั่ง RECOVER จะเอาไฟล์ incremental level 1 ที่ได้จากวันก่อนหน้ามาอัพเดทไฟล์แบ็คอัพ level 0 ที่ได้จากวันแรก ทำให้ไฟล์แบ็คอัพ level 0 นี้มีความทันสมัยขึ้นเทียบเท่ากับวันที่สอง, คำสั่ง BACKUP... จะทำการแบ็คอัพ incremental level 1 ของวันนี้

สมมติอีกว่าแผนการแบ็คอัพนี้ให้เริ่มต้นเมื่อวันที่ 1 กุมภาพันธ์ ข้อมูลข้างล่างนี้แสดงให้เห็นว่าเนื้อหาใน flash recovery area เปลี่ยนไปอย่างไรในแต่ละวันเมื่อใช้แผนการแบ็คอัพแบบนี้ สังเกตว่าระบบฐานข้อมูลจะปิดตัวเองก่อนทำการแบ็คอัพทุกครั้ง และเปิดเมื่อได้ทำการแบ็คอัพเสร็จเรียบร้อยแล้ว



หมายเหตุ เนื่องจากเรากำหนด Flash Recover Area Size ไว้เท่ากับ 10G การแบ็คอัพโดยอัตโนมัติที่เราตั้งไว้นี้จะทำการแบ็คอัพไปเรื่อย ๆ จนกว่าจะเต็มพื้นที่ 10G จากนั้นถ้ามันต้องการพื้นที่เพิ่มมันจะเริ่มทำการลบเอาไฟล์เก่าที่ไม่อัพเดทแล้ว (เนื่องมาจากมีไฟล์แบ็คอัพอันใหม่ที่ทันสมัยกว่าเป็นต้น) หรือที่เรียกว่า obsolete ออกไปแล้วจึงทำการแบ็คอัพ

5. การตรวจสอบ Backup Jobs ที่ได้ตั้งไว้
จากหน้า Home => คลิ๊กแท็ป Maintenance ตรงหัวข้อ High Availability / Backup/Recovery => คลิ๊ก Schedule Backup
ตรง Current Database Information คลิ๊กที่ Backup Jobs จะแสดงรายการแบ็คอัพที่ได้ตั้งค่าไว้ในหน้า Job Activity

Sunday, June 7, 2009

(NOT) IN เมื่อใช้กับค่า NULL ทำให้คิวรีแสดงผลไม่ครบ

(ข้อเขียนบางส่วนแปลจากบทความของ Scott Stephens)

โดยปกติดูเหมือนคำว่า 'IN' และ 'EXISTS' ดูเหมือนว่าจะคล้ายกัน แต่อย่างไรก็ตามทั้งคู่มีความแตกต่างกันในการจัดการกับค่า NULL และอาจให้ผลที่ต่างกัน ซึ่งปัญหามาจากความจริงที่ว่าในระบบฐานข้อมูล Oracle คำว่า NULL หมายถึง "ไม่ทราบค่า" ดังนั้นหากมีการเปรียบเทียบค่า หรือมีการกระทำกับค่า NULL ใด ๆ ผลที่ได้ก็ควรจะเป็น NULL คือไม่ทราบค่า ดังเช่นตัวอย่าง

SQL> select 'true' from dual where 1 = null;
SQL> select 'true' from dual where 1 != null;

ทั้งสองคำสั่งจะแสดงผลเป็น 0 row ทั้งนี้เนื่องจากจะบอกว่า 1 เท่ากับ NULL ก็ไม่ใช่ (เป็นเท็จ) ในขณะเดียวกันถ้าจะบอกว่า 1 ไม่เท่ากับ NULL ก็ไม่ใช่อีก เพราะ NULL หมายถึงตัวที่ไม่ทราบค่า จะมีก็แต่ IS NULL จึงจะเปรียบเทียบค่าเท่ากับ NULL ได้ เช่น

SQL> select 'true' from dual where 1 is null;
(เป็นเท็จ คือแสดงผลเป็น 0 rows)
SQL> select 'true' from dual where null is null;
(เป็นจริง)

เมื่อคุณใช้ "IN" คุณกำลังบอก SQL ว่าให้เอาค่า ๆ หนึ่งจากข้างหนึ่งมาเปรียบเทียบกับทุก ๆ ค่าหรือกลุ่มของค่าที่อยู่อีกข้างหนึ่ง ซึ่งถ้ามีค่า NULL อยู่ในกลุ่มแม้เพียงค่าเดียว มันก็จะไม่แสดงข้อมูลใด ๆ ออกมาเลย แม้ว่าค่าทั้งสองข้างจะเป็น NULL เช่น

SQL> select 'true' from dual where null in (null);
SQL> select 'true' from dual where (null,null) in ((null,null));
SQL> select 'true' from dual where (1,null) in ((1,null));

คำสั่งทั้งสามจะไม่แสดงแถวของข้อมูลใด ๆ ซึ่ง "IN" มีความหมายเหมือนกับ "=ANY" กล่าวคือ

SQL> select 'true' from dual where null = ANY(null);
SQL> select 'true' from dual where (null,null) = ANY((null,null));
SQL> select 'true' from dual where (1,null) = ANY((1,null));

คำสั่งทั้งสามจะไม่แสดงแถวของข้อมูลใด ๆ เช่นกัน

แต่เมื่อคุณใช้ "EXISTS", SQL จะนับจำนวนแถวและไม่สนใจค่าใน Subquery แม้ว่าจะมีค่า NULL ใน Subquery นั้น

SQL> select 'true' from dual where exists (select null from dual);
SQL> select 'true' from dual where exists (select 0 from dual where null is null);

โดยตรรกะและ "IN" เหมือนกับ "EXISTS" กล่าวคือ "IN" จะใช้ค่าที่ได้จาก Subquery ในการกรองเอาแถวที่อยู่ในคิวรีตัวนอก (Outer Query) ออก ในขณะที่ "EXISTS" จะเปรียบเทียบค่าแล้วกรองเอาแถวที่อยู่ใน Subquery ออก ดังนั้นในกรณีของค่า NULL จำนวนของแถวที่ได้จากคิวรีก็จะยังคงเท่าเดิม (ตัวอย่างข้างล่างคำสั่ง select mgr from emp จะมี 1 แถวที่ค่า mgr เป็น NULL)

SQL> select ename from emp where empno in (select mgr from emp);
SQL> select ename from emp e where exists (select null from emp where mgr = e.empno);

แต่ปัญหาเกิดขึ้นเมื่อมีเปลี่ยนเป็น "NOT IN" และ "NOT EXISTS" ซึ่งจะให้เซ็ทของแถวที่ต่างกัน ("NOT IN" จะให้ผลเป็น 0แถวในขณะที่ "NOT EXISTS" จะให้ผลเป็นจำนวนแถวตามเงื่อนไขแม้ว่าใน Subquery จะมีค่า NULL อยู่)

SQL> select ename from emp where empno not in (select mgr from emp);
SQL> select ename from emp e where not exists (select 0 from emp where mgr = e.empno);

NOT IN เป็นการเปรียบเทียบทีละค่าโดยใช้เครื่องหมายเท่ากับ "=" และจะให้ผลเป็น 0 แถวถ้าผลการเปรียบเทียบเป็น False หรือเป็น NULL(Unknown) เช่น
SQL> select 'true' from dual where 1 not in (null,2);
แสดงผลเป็น 0 แถว (Unknown) เนื่องจากเงื่อนไข 1 != null เป็นเงื่อนไขที่ไม่ทราบผล
SQL> select 'true' from dual where 1 != null and 1 != 2;
แสดงผลเป็น 0 แถว (Unknown) เนื่องจากเงื่อนไข 1 != null เป็นเงื่อนไขที่ไม่ทราบผล
SQL> select 'true' from dual where (1,2) not in ((2,3),(2,null));
แสดงผลเป็น 'true' เนื่องจากเงื่อนไข (1,2) != (2,3) และ (1,2) != (2,null) เป็นจริง สาเหตุที่ (1,2) != (2,null) เป็นจริง (แม้ว่าจะมีค่า NULL ในค่าในวงเล็บตัวหลัง) เนื่องจาก
ในกรณี "NOT IN" ถ้าทั้งสองตัวนั้นต่างก็เป็นสมาชิกของ Compound Key (เป็น Key ที่มีสมาชืกมากกว่า 1 เช่น (1,null), (2,null) เป็นต้น) กล่าวคือ ถ้า Compound Key 2 ชุดมีค่า Null อยู่ทั้งคู่ และค่าที่ไม่เป็น Null มีค่าต่างกัน จะถือว่าทั้งคู่มีค่าต่างกันได้เช่น (1,null) != (2,null) อย่างไรก็ตาม (1,null) = (1,null) จะให้ผลเป็น Unknown เนื่องจากเราไม่รู้ว่า NULL คือค่าใดแน่
SQL> select 'true' from dual where (1,null) not in ((1,2),(2,3));
แสดงผลเป็น 0 แถว (Unknown) เนื่องจากเงื่อนไข (1,null) != (1,2) เป็นเงื่อนไขที่ไม่ทราบผล (แม้ว่าเราจะรู้ว่าเงื่อนไข (1,null) != (2,3) เป็นจริง) แต่ Unknown AND True ก็ให้ผลเป็น Unknown อยู่ดี)
SQL> select 'true' from dual where (1,null) not in ((2,3),(2,null));
แสดงผลเป็น 'true' เนื่องจากเงื่อนไข (1,null) != (2,3) และ (1,null) != (2,null) เป็นจริง
SQL> select 'true' from dual where (1,null) not in ((1,2),(2,null));
แสดงผลเป็น 0 แถว (Unknown) เนื่องจากเงื่อนไข (1,null) != (1,2) เป็นเงื่อนไขที่ไม่ทราบผล
SQL> select 'true' from dual where 1 not in (2,3);
แสดงผลเป็น 'true'
SQL> select 'true' from dual where 1 != 2 and 1 != 3;
แสดงผลเป็น 'true'

อย่างไรก็ตามเรายังคงสามารถใช้ "NOT IN" หากเราจะตัดค่า NULL ออกจาก Subquery ในตัวอย่างข้างล่าง empno ถูกกำหนดให้ไม่เป็น NULL

SQL> select ename from emp where empno not in (select mgr from emp where mgr is not null);
SQL> select ename from emp where empno not in (select nvl(mgr,0) from emp);

หากเราเข้าใจความแตกต่างระหว่าง "IN", "EXISTS", "NOT IN" และ "NOT EXISTS" เราก็จะสามารถหลีกเลี่ยงปัญหาเมื่อปรากฏมี NULL ในข้อมูลหรือใน Subquery ได้