Friday, April 30, 2010

ความลึกลับของ Trigger ตอนที่ 1

"กลางดึกคืนหนึ่ง... สมชาติยังคงนั่งเขียนโปรแกรมของเขาอยู่ นี่ก็เลยเวลาเลิกงานไปหกชั่วโมงแล้วสินะ สายตาเขายังคงจ้องเขม็งไปที่จอคอมพิวเตอร์ พยายามที่จะหาเหตุผลว่าทำไมข้อมูลในตารางที่โปรแกรมของ "คนเก่า" เขาเขียนไว้ จึงมีข้อมูลแปลก ๆ เก็บไว้ในคอลัมน์ที่ในโปรแกรมก็ไม่ได้แสดงว่าไปอัพเดท เขาเคยได้ยินว่าที่ตึกแห่งนี้เคยมีโปรแกรมเมอร์คนหนึ่งเสียชีวิตขณะนั่ง เขียนโปรแกรมในคืนวันหนึ่ง พอรุ่งเช้าก็มีคนพบเขานอนฟุบอยู่กับคีย์บอร์ดโดยปราศจากลมหายใจแล้ว หรือว่า....โปรแกรมนี้....มัน....."

เรื่องนี้เป็นเรื่องจริงที่เกิดขึ้น แต่ไม่ได้จริงเพราะโปรแกรมมีผีสิงหรอกครับ แต่เนื่องมาจากสมชาติไม่รู้จักการทำงานของทริกเกอร์ (Trigger แปลว่า ไก เช่นไกปืน หรือแปลว่าเหนี่ยวไกก็ได้) มาก่อน หรืออาจจะรู้แต่ไม่ได้เฉลียวใจ จึงทำให้ต้องนั่งผีหลอกวิญญาณหลอนอยู่อย่างนั้น

ทริกเกอร์ (Trigger) มีลักษณะคล้าย Stored Procedure ประเภทหนึ่งที่จะถูกกระตุ้นให้ทำงานเมื่อมีการกระทำกับตารางในฐานข้อมูล การกระทำเช่น การเพิ่ม (Insert), การแก้ไข (Update), หรือการลบ (Delete) จะมีผลให้ทริกเกอร์ทำงาน ด้วยคุณสมบัติอันน่าทึ่งอันนี้ทำให้ผู้ที่ได้รู้จักทริกเกอร์ใหม่ ๆ จะรู้สึกตื่นเต้น และอยากหาทางใดทางหนึ่งในการใช้มันให้ได้ เช่นการแปลงค่าก่อนนำไปใส่ไว้ในคอลัมน์, ใช้เช็คค่าที่ถูกต้องก่อนนำไปใส่ในคอลัมน์, หรือการสร้างข้อมูล Audit เป็นต้น ในขณะที่ทริกเกอร์มีคุณสมบัติดี ๆ หลายประการ แต่ขณะเดียวกันการใช้ทริกเกอร์ก็มีข้อพึงระวังเพื่อไม่ให้เกิดเรื่องลึกลับ อย่างที่สมชาติเจอ

สาเหตุหลักที่ทำให้ควรหลีกเลี่ยงการใช้ทริกเกอร์
1. ทริกเกอร์ทำให้ยุ่งยากในการดูแลในระยะยาว เนื่องจากตัวมันซ่อนอยู่ และรันโดยตัวมันเอง ซึ่งอาจจะเกิดขึ้นเมื่อใดก็ได้ หากถูกกระตุ้นโดยเหตุการณ์ต่าง ๆ ซึ่งคนส่วนใหญ่จะไม่ได้นึกถึง หรือลืมไปว่ามีทริกเกอร์อยู่ตรงนั้น
2. ทริกเกอร์มักจะถูกนำมาใช้อย่างไม่เหมาะสม โดยเฉพาะสถานการณ์ที่ไม่สามารถ Rollback ได้

ความยุ่งยากในการดูแลในระยะยาว
สมมติว่าคุณ กำลังทำงานในโครงงานของคนอื่น หรือเพราะว่าคุณต้องทำงานที่คนอื่นทำค้างไว้ ถ้าคุณต้องทำการสำรวจตัวโปรแกรม หรืออาจจะเป็น Stored Procedure ก็อาจจะเป็นวิสัยที่ทำได้ แต่ถ้าคุณพบกับโปรแกรมที่อาศัยทริกเกอร์ที่กระจายอยู่บนตารางต่าง ๆ มากมาย คุณคงต้องเกาหัวแกร็ก ๆ เพราะไม่รู้จะเริ่มตรงไหนดี?

ทริกเกอร์ทำ งานแบบเป็นผลข้างเคียงและเป็นเอกเทศ ไม่ได้อยู่กับโฟลวงานหลักที่คุณสามารถตรวจดูได้ทีละบรรทัด เมื่อคุณทำการอัพเดทเรคคอร์ดระบบอาจจะตอบกลับว่า 1 Row Proccessed แต่อาจจะมีสิ่งต่าง ๆ อีก 500 สิ่งเกิดขึ้นจากทริกเกอร์ก็ได้ นอกจากนี้ทริกเกอร์มักไม่ได้รับการจัดทำเป็นเอกสารอย่างเหมาะสม หรืออาจจะถูกมองข้ามไปเมื่อต้องทำการตรวจสอบตัวโปรแกรม

ลองดูจากตัว อย่างในเว็บไซท์ของ Tom Kyte ดังต่อไปนี้
"เรามีปัญหาเกี่ยวกับการอัพ เดทคอลัมน์ (varchar2) ผลการอัพเดทแสดงว่ามี 1,972 แถวที่ถูกอัพเดท (และเราได้ commit แล้ว) หลังจากนั้นเราดึงข้อมูลมาดูพบว่าคอลัมน์ไม่ได้ถูกอัพเดท แต่เมื่อเราอัพเดทคอลัมน์อื่นกลับทำให้ค่าในคอลัมน์นี้มีค่าเปลี่ยนแปลงไป"

เหตุการณ์ นี้จบเมื่อผู้ถามพบว่ามีประโยคนี้ :new.name := :new.fname || :new.lname อยู่ในทริกเกอร์ ซึ่งหมายถึงการที่ระบุให้กำหนดค่าให้กับคอลัมน์ name ด้วยค่าของ fname || lname (คือคำสั่งให้อัพเดทคอลัมน์ name โดยเอา fname (ชื่อ) ไปรวมกับ lname (นามสกุล) แล้วนำไปใส่ในคอลัมน์ name)

ผู้ออกแบบโปรแกรมไม่ควรจะใช้คอลัมน์ name ซึ่งรวมเอาชื่อกับนามสกุลเข้าไว้ด้วยกัน เพราะเรามีคอลัมน์ชื่อกับคอลัมน์นามสกุลอยู่แล้ว เราสามารถจะใส่ฟังก์ชั่น Concatenate (||) ไว้ในวิว โดยไม่ต้องนำค่าชื่อ + นามสกุลไปเก็บไว้ในคอลัมน์จริง ๆ นอกไปจากนี้ยังมีการเขียนทริกเกอร์อัพเดทคอลัมน์ name ที่ทำให้เกิดความสับสนขึ้นไปอีก เพราะเมื่อผู้ใช้ระบบต้องการอัพเดทคอลัมน์ name จะทำไม่ได้ถ้าไม่ได้หยุด (Disable) การทำงานของทริกเกอร์ไว้ แต่เมื่ออัพเดทคอลัมน์ fname, lname ข้อมูลในคอลัมน์ name กลับเปลียนไป

SQL> create table test_name (fname varchar2(10), lname varchar2(20), name varchar2(30));

Table created

SQL> insert into test_name values ('Somchai','Klaharn','Somchai Klaharn');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from test_name;

FNAME LNAME NAME
---------- -------------------- ------------------------------
Somchai Klaharn Somchai Klaharn


SQL> create or replace trigger upd_testname_trg
2 before update on test_name
3 for each row
4 declare
5 -- local variables here
6 begin
7 :new.name := :new.fname || ' ' || :new.lname;
8 end upd_testname_trg;
9 /

Trigger created

SQL> update test_name set fname = 'Tanakorn',lname = 'Tavornsasnavong';

1 row updated

SQL> select * from test_name;

FNAME LNAME NAME
---------- -------------------- ------------------------------
Tanakorn Tavornsasnavong Tanakorn Tavornsasnavong

SQL> commit;

Commit complete

SQL> update test_name set name = 'Somchai Klaharn';

1 row updated

SQL> commit;

Commit complete

SQL> select * from test_name;

FNAME LNAME NAME
---------- -------------------- ------------------------------
Tanakorn Tavornsasnavong Tanakorn Tavornsasnavong

SQL> alter trigger upd_testname_trg disable;

Trigger altered

SQL> update test_name set name = 'Somchai Klaharn';

1 row updated

SQL> select * from test_name;

FNAME LNAME NAME
---------- -------------------- ------------------------------
Tanakorn Tavornsasnavong Somchai Klaharn


ทริกเกอร์ทำให้การดูแลระบบทำได้ยาก, สับสนง่าย และยังซ่อนตัวเองจากตรวจสอบ ในการตรวจสอบ Code ทริกเกอร์มักจะถูกลืมจากการตรวจสอบด้วย อาจจะเนื่องจากคนส่วนใหญ่เห็นว่าทริกเกอร์เป็นส่วนหนึ่งของวัตถุในฐานข้อมูล (ดังเช่น ตาราง เรามักจะไม่ไปตรวจสอบคำสั่งเช่น CREATE TABLE ตอนตรวจสอบ Code ในโปรแกรม)

การนำไปใช้อย่างไม่เหมาะสม

บ่อยครั้งที่ ผู้สร้างทริกเกอร์อาจจะไม่ได้คิดถึงพฤติกรรมของมันอย่างทั่วถึง ดังเช่น Code ต่อไปนี้

SQL> create trigger send_mail
2 after insert on purchase_order
3 for each row
4 begin
5 utl_mail.send
6 (sender=>'database@x.com',
7 recipients=>'orders@x.com',
8 subject=>'New Order '||:new.po_number,
9 message=>'... ');
10 end;
11 /

Trigger created.


Code ข้างต้นถูกต้องในแง่ของไวยกรณ์ และหลังจากที่ Insert ข้อมูลลงในตารางทริกเกอร์ก็จะทำงานได้อย่างถูกต้อง แต่มีความผิดพลาดในการนำไปใช้ กล่าวคือถ้าเกิดการ Rollback เมล์ที่ถูกส่งไปให้กับผู้รับจะไม่สามารถยกเลิกได้ ในการนี้ SMTP (Simple Mail Transfer Protocol) จะไม่รับรู้ถึงการยกเลิกหรือ Rollback ในฐานข้อมูล การ Rollback อาจจะเกิดจากสาเหตุอื่น ๆ ซึ่งอาจจะไม่ได้เกิดจากความจงใจเช่นการ Disconnect แบบไม่ปกติจากการปิดโปรแกรม SQL*Plus, การเกิดปัญหาเน็ทเวอร์ค เป็นต้น การใช้ทริกเกอร์กับยูทิลิตี้ต่าง ๆ เช่น UTL_FILE (จัดการไฟล์), UTL_MAIL (จัดการเมล์), UTL_HTTP (การใช้บริการของ HTTP) ฯลฯ ล้วนประสบปัญหาเดียวกัน ดังนั้นข้อควรจำประการหนึ่งในการใช้ทริกเกอร์คือ "อย่าใช้ทริกเกอร์กับระบบงานที่ไม่สามารถ Rollback ได้"

(ยังไม่จบ นะครับ ยังมีตอนต่อไป...ขอขอบคุณที่ให้ความสนใจและโปรดติดตามตอนต่อไปนะครับ)

เรียบเรียงจาก "The Trouble with Triggers"
โดย Tom Kyte, Oracle Magazine Volume XXII / Number 5 September/October2008, ASK TOM

เซ็ท Parameter ผิด คิดจนหัวบวม

ถ้าเราเซ็ทพารามิเตอร์บางตัวผิด (Oracle ไม่สามารถยอมรับค่านั้นได้) อาจจะทำให้เราไม่สามารถ Startup Database ได้ ดังเช่นตัวอย่างของการตั้งค่าพารามิเตอร์ SGA_MAX_SIZE ซึ่งจะต้อง Restart ระบบฯ หากค่าที่กำหนดเป็นค่าที่ไม่สามารถยอมรับได้ (Invalid) จะทำให้ระบบฯ Startup ไม่ขึ้น ดังเรื่องของสมสรวงที่จะกล่าวถึงต่อไปนี้

สมสรวง ต้องการเพิ่มหน่วยความจำให้กับระบบฐานข้อมูลออราเคิล โดยเขาต้องการให้ฐานข้อมูลใช้หน่วยความจำ 6GB โดยการเซ็ทพารามิเตอร์ SGA_TARGET ซึ่งสามารถทำได้โดยไม่ต้อง Restart ระบบฯ โดย Server มีหน่วยความจำทั้งหมดอยู่ 8GB บนเครื่อง โดยเขาต้องการเซ็ท SGA_MAX_SIZE ให้มากที่สุดเท่าที่จะทำได้ เขาจึงตัดสินใจเซ็ท SGA_MAX_SIZE ให้เท่ากับหน่วยความจำทั้งหมดที่มี

พารามิเตอร์ SGA_MAX_SIZE ใช้ในการกำหนดลิมิตสูงสุดของหน่วยความจำที่สามารถกำหนดให้ SGA_TARGET ได้ เป็นพารามิเตอร์ที่ต้อง Restart ระบบฐานข้อมูลจึงจะมีผล ในขณะที่พารามิเตอร์ SGA_TARGET สามารถกำหนดได้โดยไม่ต้อง Restart ระบบฯ

SQL> alter system set sga_max_size=8g scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-27102: out of memory
OSD-00022: additional error information
O/S-Error: (OS 8) Not enough storage is available to process this command.

เนื่อง จาก SGA_MAX_SIZE เป็นพารามิเตอร์ที่ต้องการการ Restart ระบบฐานข้อมูล แต่เมื่อเขา Startup ฐานข้อมูลปรากฎว่าเขาพบ Error ORA-27102: out of memory และไม่สามารถที่จะเปิดฐานข้อมูลได้เลย

สาเหตุที่เป็นเช่นนี้ เนื่องจาก เมื่อสมสรวงใช้คำสั่ง alter system set sga_max_size=8g scope=spfile นั้น ออราเคิลจะบันทึกค่าของพารามิเตอร์นี้ไว้ในไฟล์ %ORACLE_HOME%\dbs\SPFILEsid.ORA และเมื่อสมสรวงใช้คำสั่ง STARTUP เพื่อเปิดระบบฐานข้อมูล ออราเคิลจะไปอ่านพารามิเตอร์ในไฟล์นี้ในการเปิดระบบ เนื่องจากค่าที่ระบุ SGA_MAX_SIZE มีขนาดเกินกว่าที่หน่วยความจำที่มี (หน่วยความจำที่มีอยู่ 8G นั้นส่วนหนึ่งถูกใช้ไปโดย O/S) จึงไม่สามารถเปิดระบบฐานข้อมูลได้

ผมว่าออราเคิลน่าจะมีฟังก์ชันในการตรวจสอบพารามิเตอร์ใน SPFile ก่อนที่จะยอมปิดระบบฐานข้อมูล และแจ้งเตือนให้ผู้บริหารระบบทราบถึงผล (Error) อันอาจจะเกิดขึ้นหากมีการ Startup ด้วย SPFile ตัวปัจจุบัน

การแก้ไข
ใน %ORACLE_BASE%\admin\orcl10g\pfile\ มีไฟล์พารามิเตอร์ชื่อ init.ora.xxxxxxxxxxxx อยู่ ซึ่งเป็นเท็กซ์ไฟล์ธรรมดา (ต่างจาก SPFile ซึ่งเป็น Binary) เราสามารถเลือกที่จะ Startup ระบบฯ ด้วยไฟล์พารามิเตอร์ตัวนี้แทน SPFile ได้ เราเรียกไฟล์นี้ว่า Pfile (Parameter File) เป็นไฟล์ลูกพี่ลูกน้องกับ Server Parameter File (SPFile) ซึ่งค่าของพารามิเตอร์ที่กำหนดไว้ภายในอาจจะเหมือนหรือต่างกันก็ได้

และ เนื่องจากการที่เราพยายาม Startup ด้วย SPFile ครั้งล่าสุดทำให้ระบบจำค่าพารามิเตอร์ที่ผิดเอาไว้ เราจึงจะต้องเคลียร์ค่าเหล่านี้โดยปิด Service => ลบหรือเปลี่ยนชื่อของ SPFile (ตัวเก่า) => แล้วเปิด Service ใหม่ => แล้วจึง Startup ระบบฯ ด้วย PFile

SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\tanakorn>oradim -shutdown -shuttype srvc -sid orcl10g

C:\Documents and Settings\tanakorn>ren C:\oracle\product\10.2.0\db_1\dbs\spfileorcl10g.ora spfileorcl10g_ori.ora

C:\Documents and Settings\tanakorn>oradim -startup -sid orcl10g

C:\Documents and Settings\tanakorn>exit

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='C:\oracle\product\10.2.0\admin\orcl10g\pfile\init.ora.312255320268'
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 167775108 bytes
Database Buffers 436207616 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.

หลังจากที่เราเปิดระบบฯ ได้แล้ว เราควรจะสร้าง SPFile จาก Pfile ที่เราใช้ในการเปิดระบบฯ เอาไว้ เนื่องจาก SPFile ตัวเก่าใช้ไม่ได้แล้ว เมื่อสร้างเสร็จแล้วให้ Restart ระบบฯ อีกทีเพื่อให้ระบบฯ กลับมาใช้ SPFile อย่างเดิม

การเปิดฐานข้อมูลด้วย SPFile มีประโยชน์ที่เห็นได้ชัดคือทำให้เราสามารถที่จะเปลี่ยนค่าในพารามิเตอร์ได้ โดยสะดวก โดยไม่ต้องไปแก้ไขในเท็กซ์ไฟล์อย่าง PFile

SQL> create spfile from pfile='C:\oracle\product\10.2.0\admin\orcl10g\pfile\init.ora.312255320268';

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 167775108 bytes
Database Buffers 436207616 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>

เป็นการเตรียมการที่ดีที่จะสร้าง PFile จาก SPFile ตัวปัจจุบันทุกครั้้ง ก่อนที่จะแก้ไขพารามิเตอร์ที่จะต้องทำการ Restart ระบบฯ เพื่อที่หากเกิดปัญหาจะสามารถนำเอา PFile ตัวนั้นมาใช้ Start ระบบฯได้เลย จากตัวอย่างเราใช้ไฟล์เก่า ซึ่งพารามิเตอร์บางตัวอาจจะไม่เหมือนกับที่ปรากฏใน SPFile ซึ่งอัพเดทกว่าเสมอ

SQL> conn / as sysdba
Connected.
SQL> create pfile from spfile;

File created.

Pfile ที่สร้างขึ้นโดยดีฟอลต์จะไปอยู่ที่ %ORACLE_HOME%\database\ (หรือในกรณีนี้คือ C:\oracle\product\10.2.0\db_1\database\)

Monday, April 5, 2010

อินเด็กซ์ที่ค่าไม่ซ้ำ (Unique Index)

อินเด็กซ์ที่ค่าไม่ซ้ำ (Unique Index) ในที่นี้หมายถึง B-Tree Index ที่เป็นแบบ Unique ซึ่งถือเป็นสุดยอดของอินเด็กซ์เนื่องจากความเร็วและการนำไปใช้งานที่ง่าย เราจะสร้างตารางใหม่ชื่อ MY_OBJECTS จากตาราง ALL_OBJECTS (ALL_OBJECTS เป็นตาราง Meta Data ของ Oracle มีอยู่ในทุก Schema) โดยมีฟิลด์ OBJECT_ID เป็นเลขรันนิ่ง เราจะใช้ฟิลด์นี้ในการสร้างอินเด็กซ์แบบไม่ซ้ำ

SQL> conn scott/tiger
Connected.

SQL> create table tmp_objects as select owner,object_name from all_objects;

Table created.

SQL> insert into tmp_objects select * from tmp_objects;

107518 rows created.

SQL> insert into tmp_objects select * from tmp_objects;

215036 rows created.

SQL> insert into tmp_objects select * from tmp_objects;

430072 rows created.

SQL> insert into tmp_objects select * from tmp_objects;

860144 rows created.

SQL> insert into tmp_objects select * from tmp_objects;

1720288 rows created.

SQL> commit;

Commit complete.

SQL> create table my_objects as select rownum as object_id,owner,object_name from tmp_objects;

Table created.

SQL> select count(*) from my_objects;

COUNT(*)
----------
3440576

SQL> drop table tmp_objects;

Table dropped.

หลังจากนั้นเราสร้างอินเด็กซ์แบบค่าไม่ซ้ำ (Unique Index) บนคอลัมน์ OBJECT_ID เมื่อเราคิวรีโดยใช้ OBJECT_ID=15000 แล้วจะพบว่า Optimizer สามารถใช้วิธีการค้นข้อมูลแบบอินเด็กซ์ค่าเดียว (Index Unique Scan) ซึ่งมีความเร็วสูงที่สุด แต่ถ้าเราใช้ OBJECT_ID > 15000 แล้วทำให้ Optimizer ไม่มีทางเลี่ยงที่จะต้องค้นหาแบบอินเด็กซ์ช่วงแทน (Index Range Scan) สังเกตดูค่าในคอลัมน์ Cost (%CPU) ของการคิวรีทั้งสองแบบ จะเห็นว่าการค้นหาแบบอินเด็กซ์ช่วงใช้เวลาสูงกว่าแบบอินเด็กซ์ค่าเดียว

SQL> create unique index myobjects_idx1 on my_objects(object_id);

Index created.

SQL> select * from my_objects where object_id = 15000;

Execution Plan
----------------------------------------------------------
Plan hash value: 3640416373

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MY_OBJECTS | 1 | 47 | 3 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | MYOBJECTS_IDX1 | 1 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=15000)

SQL> select * from my_objects where object_id < 15000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2829829274

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50492 | 2317K| 433 (1)| 00:00:06 |
| 1 | TABLE ACCESS BY INDEX ROWID| MY_OBJECTS | 50492 | 2317K| 433 (1)| 00:00:06 |
|* 2 | INDEX RANGE SCAN | MYOBJECTS_IDX1 | 50492 | | 122 (1)| 00:00:02 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"<15000)

Note
-----
- dynamic sampling used for this statement

คราวนี้ลองสร้างอินเด็กซ์ที่ค่าซ้ำกันได้บ้าง (Non Unique Index) บนคอลัมน์ OBJECT_ID เช่นกัน เมื่อเราคิวรีไม่ว่าจะโดยใช้ OBJECT_ID=15000 หรือ OBJECT_ID>15000 จะพบว่า Optimizer จะไม่สามารถใช้วิธีการค้นข้อมูลแบบอินเด็กซ์ค่าเดียว (Index Unique Scan) ได้เลย เนื่องจากอินเด็กซ์ที่เราสร้างไม่ได้เป็นอินเด็กซ์ที่ค่าไม่ซ้ำ (Unique Index) นั่นเอง ลองสังเกตดูอีกว่าเมื่อเราใช้เงื่อนไข OBJECT_ID>1500000 Optimizer จะใช้วิธีการ TABLE ACCESS FULL แทน เนื่องจากมันรู้ว่ามีข้อมูลจำนวนมากที่มี OBJECT_ID > 1500000 การเข้าไปหาข้อมูลตรง ๆ จากตารางน่าจะเร็วกว่าการไปค้นข้อมูลในอินเด็กซ์ก่อนแล้วค่อยไปหาในตาราง ลองเปรียบเทียบกับการหาหนังสือในห้องสมุด ถ้าเรารู้เลขเรียกหนังสือเราก็จะไปที่ตู้ดัชนีแล้วดูว่าหนังสืออยู่ที่ไหน แต่ถ้าเราต้องการหาหนังสือจำนวนครึ่งหนึ่งของหนังสือทั้งห้องสมุด เราคงไม่ไปใช้ตู้ดัชนี การเดินไปที่หิ้งแล้วไล่ไปทีละเล่มเลยดูเหมือนจะเร็วกว่า

SQL> drop index myobjects_idx1;

Index dropped.

SQL> create index myobjects_idx1 on my_objects(object_id);

Index created.

SQL> select * from my_objects where object_id = 15000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2829829274

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 47 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MY_OBJECTS | 1 | 47 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MYOBJECTS_IDX1 | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=15000)

Note
-----
- dynamic sampling used for this statement

SQL> select * from my_objects where object_id < 15000;

Execution Plan
----------------------------------------------------------
Plan hash value: 2829829274

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50492 | 2317K| 441 (1)| 00:00:06 |
| 1 | TABLE ACCESS BY INDEX ROWID| MY_OBJECTS | 50492 | 2317K| 441 (1)| 00:00:06 |
|* 2 | INDEX RANGE SCAN | MYOBJECTS_IDX1 | 50492 | | 130 (1)| 00:00:02 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"<15000)

Note
-----
- dynamic sampling used for this statement

สรุปก็คือ ปกติการค้นข้อมูลแบบอินเด็กซ์ค่าเดียว (Index Unique Scan) จะเร็วกว่าการค้นหาแบบอินเด็กซ์ช่วง (Index Range Scan) ดังนั้นในทุก ๆ กรณีถ้าค่าในคอลัมน์นั้นไม่ซ้ำกันเลยให้ใช้อินเด็กซ์ที่ไม่มีค่าซ้ำ (Unique Index) เพื่อให้ Optimizer สามารถค้นข้อมูลแบบอินเด็กซ์ค่าเดียวไ้ด้เสมอ และในกรณีใด ๆ ถ้าเราสามารถจะใช้เครื่องหมายเท่ากับใน Where Clause ได้ย่อมจะให้ผลดีกว่าการใช้เครื่องหมายมากกว่าหรือน้อยกว่าหรือ LIKE หรืออื่น ๆ เนื่องจากสาเหตุเดียวกัน นอกจากนี้การ Optimizer อาจจะเลือกที่จะทำการตรงไปดูค้นข้อมูลในตารางโดยตรงเลยก็ได้ หากมันเห็นว่าการทำแบบนั้นเร็วกว่า โดยปกติถ้าข้อมูลที่จะดึงออกมามีปริมาณไม่เกิน 10 เปอร์เซ็นต์ของข้อมูลทั้งหมดแล้ว Optimizer จึงจะไปใช้อินเด็กซ์ (อาจจะมากหรือน้อยกว่าขอให้ผู้อ่านลองไปทดสอบเป็นการบ้านดูนะครับ) เช่น ถ้าข้อมูลมี 3 ล้านเรคคอร์ด ถ้าคิวรีโดยใช้ Where Clause แล้วผลที่ได้ไม่เกิน 3 แสนเรคคอร์ด Optimizer จะใช้อินเด็กซ์ทีมีในการคิวรี แต่ถ้าเกินมันก็จะไปค้นข้อมูลจากตารางโดยตรง