อินเด็กซ์ที่ค่าไม่ซ้ำ (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 จะใช้อินเด็กซ์ทีมีในการคิวรี แต่ถ้าเกินมันก็จะไปค้นข้อมูลจากตารางโดยตรง