การ Join กับ Query Plan

 การ Join กับ Query Plan
สอง Query ข้างล่างนี้ให้ผลเหมือนกันแต่ตัวไหนเร็วกว่า?
1) SELECT distinct D.deptno, D.dname
FROM dept D, emp E
WHERE E.deptno = D.deptno
ORDER BY D.deptno;

2) SELECT D.deptno, D.dname
FROM dept D
WHERE EXISTS
(
SELECT 1
FROM emp E
WHERE E.deptno = D.deptno))ORDER BY D.deptno;

ปกติเราต้องใช้การ Join ของสองตารางหรือมากกว่า ก็ต่อเมื่อเราต้องการคอลัมน์จากตารางทุกตารางที่เอามา Join กัน ดังนั้นเราไม่ควรจะ Join ตาราง EMP เข้ากับตาราง DEPT ถ้าในผลลัพธ์ที่เราต้องการไม่มีคอลัมน์ของ EMP อยู่เลย ซึ่งถ้าเป็นกรณีอย่างนี้คุณควรจะใช้ WHERE EXISTS หรือไม่ก็ WHERE IN มากกว่า (Cost-Based Optimizer [CBO] เห็นสองตัวนี้เหมือนกัน) เราอาจจะเขียน Query แบบนี้
select deptno, dname
from dept
where exists
( select NULL
from emp
where emp.deptno
= dept.deptno )
order by deptno;

หรือแบบนี้
select deptno, dname
from dept
where deptno in
( select deptno
from emp )
order by deptno;

ทั้งสองกรณี Optmizer อาจจะใช้ Semi Join (ซึ่งเอา deptno ใน dept ทีละ Row ไปเทียบกับ deptno ใน emp และจะหยุดการเปรียบเทียบของ Row นั้นทันทีที่พบว่ามี) หรือไม่ก็ใช้อินเด็กซ์กับ Nested Loops ในการหาใน EMP ว่ามี Row นั้นอยู่หรือเปล่า
{xtypo_info}Semi-Join ระหว่างตาราง 2 ตารางจะให้ผลเป็นแถวจากตารางแรกที่สามารถจับคู่กับแถวในตารางที่สองได้หนึ่งหรือมากกว่าหนึ่งคู่ ความแตกต่างระหว่าง Semi-Join และการ Join แบบธรรมดาคือแถวจากในตารางแรกจะถูกดึงมาครั้งเดียว แม้ว่าในตารางที่สองจะมีอยู่สองแถวที่สามารถจับคู่กับตารางแรกได้, Semi-Join จะถูกเขียนโดยใช้รูปแบบ EXISTS หรือ IN{/xtypo_info}

ตัวอย่างต่อไปนี้แสดงว่าโดยทั่วไปแล้วจะเกิดอะไรขึ้น เมื่อ Optimizer ตรวจพบว่า DEPT มีขนาดเล็ก และ EMP มีขนาดใหญ่ โดยมันจะสแกนแต่ละ Row ของ DEPT และจะเข้าไปหา DEPTNO (โดยใช้อินเด็กซ์) ในตาราง EMP ซึ่งมีขนาดใหญ่ ในทางกลับกันถ้า DEPT มีขนาดใหญ่ Optimizer ก็จะทำ Semi Join ทั้งก้อนโดยไม่ใช้ Index เราจะใช้ก๊อปปี้ของตาราง EMP และ DEPT ในการทดสอบดังนี้
SQL> create table emp as
2 select * from scott.emp;
Table created.

SQL> create table dept as
2 select * from scott.dept;
Table created.

SQL> create index emp_deptno_idx
2 on emp(deptno);
Index created.

จากนั้นเราก็เมคค่าสถิติของตารางขึ้นมาเอง โดยใช้ SET_TABLE_STATS ใน Package DBMS_STATS เพื่อที่จะทำให้ EMP ดูเหมือนตารางที่ใหญ่ มีขนาด 1,000,000 แถว และให้ DEPT ดูเหมือนตารางที่มีขนาดเล็กเพียง 100 แถว นอกจากนั้นเราจะบอก Optimizer ว่ามีอินเด็กซ์บนคอลัมน์ EMP.DEPTNO แต่เนื่องจาก Oracle10g จะทำการคำนวณสถิติของอินเด็กซ์ตั้งแต่ตอนสร้างอินเด็กซ์ เราจึงต้องเอาสถิติเดิมของอินเด็กซ์ออกก่อน และใส่ (เมค) สถิติใหม่เข้าไปให้สอดคล้องกับสถิติของตารางที่เราเมคเอาไว้แต่แรก
SQL> begin
2 dbms_stats.set_table_stats
3 ( user,
4 'EMP',
5 numrows => 1000000,
6 numblks => 100000 );

7 dbms_stats.delete_index_stats
8 ( user,
9 'EMP_DEPTNO_IDX' );

10 dbms_stats.set_index_stats
11 ( user,
12 'EMP_DEPTNO_IDX',
13 numrows => 1000000,
14 numdist => 10000,
15 numlblks =>10000 );

16 dbms_stats.set_column_stats
17 ( user,
18 'EMP',
19 'DEPTNO',
20 DISTCNT => 10000 );

21 dbms_stats.set_table_stats
22 ( user,
23 'DEPT',
24 numrows=> 100,
25 numblks => 100 );
26 end;
27 /

ตอนนี้เราก็พร้อมที่จะจับตาดูการตัดสินใจของ Optimizer เราใช้ AUTOTRACE เพื่อที่จะดู Query Plan ที่สร้างขึ้นจากคิวรีทั้งสองตัว เราแสดงคิวรีทั้งสองเพื่อที่จะแสดงให้เห็นว่าเมื่อ Developer ใช้ CBO (Cost-Based Optimizer) เขาไม่จำเป็นต้องตัดสินใจว่าจะเขียนอย่างไรให้ดีที่สุด (คือเพียงในระดับที่ยอมรับได้ก็เพียงพอ ส่วนที่เหลือให้ Optimizer ช่วยจัดการ) -- Optimizer จะเข้าใจรูปแบบของคิวรีที่เขียนขึ้นและเลือกวิธีการนำข้อมูลออกมาที่ดีที่สุด ซึ่งแตกต่างโดยสิ้นเชิงกับวิธีแบบ Rule-Based Optimizer (RBO) ซึ่งเป็น Optimizer แบบเก่าที่เราจะต้องเป็นผู้กำหนดแปลนในการรันคำสั่งเอาเอง ซึ่งจะมองเห็น WHERE IN กับ WHERE EXISTS ว่าแตกต่างกันโดยสิ้นเชิง

เมื่อเราใช้ CBO กับตาราง EMP ซึ่งมีขนาดใหญ่ และ DEPT ซึ่งมีขนาดเล็ก, Oracle อาจจะใช้ Query Plan ดังแสดงข้างล่างนี้ คืออ่านทุก ๆ แถวของ DEPT และทำสแกนอินเด็กซ์เฉพาะช่วง (Index Range Scan) บนตาราง EMP เพื่อหา DEPTNO ที่ตรงกับใน DEPT และเนื่องจากการสแกนจากในอินเด็กซ์เพื่อเปรีบบเทียบนี้มีแค่ 100 ครั้ง (เนื่องจากเราทำให้ Optimizer เห็นว่ามีข้อมูลใน DEPT แค่ 100 แถว เมื่อนำ 100 แถวเข้าไปหาในอินเด็กซ์จึงต้องทำทั้งหมด 100 ครั้ง) และเนื่องจาก (เราทำให้ Optimizer เห็นว่า) ตาราง EMP มีขนาดใหญ่ Optimizer จึงใช้ Query Plan นี้ทั้งกับกรณี WHERE IN และ WHERE EXISTS
SQL> set autotrace traceonly explain
SQL> select deptno, dname
2 from dept where deptno in
3 ( select deptno
4 from emp )
5 order by deptno;

Execution Plan
-----------------------------------------------------------
Plan hash value: 3383088615

----------------------------------------------------------- 
| Id | Operation       | Name          | Rows  | Bytes |
-----------------------------------------------------------
| 0  |SELECT STATEMENT |               | 100   | 3500  |
| 1  |SORT ORDER BY    |               | 100   | 3500  |
| 2  |NESTED LOOPS SEMI|               | 100   | 3500  |
| 3  |TABLE ACCESS FULL| DEPT          | 100   | 2200  |
|* 4 |INDEX RANGE SCAN | EMP_DEPTNO_IDX| 1000K | 12M   |
-----------------------------------------------------------

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

4 - access("DEPTNO"="DEPTNO") 

SQL> select deptno, dname 
2 from dept where exists
3 ( select null
4 from emp
5 where emp.deptno =
6 dept.deptno )
7 order by deptno;

Execution Plan 
------------------------------------------------------------
Plan hash value: 3383088615

------------------------------------------------------------ 
| Id | Operation        | Name          | Rows  | Bytes |
------------------------------------------------------------
| 0  | SELECT STATEMENT |               | 100   | 3500  |
| 1  | SORT ORDER BY    |               | 100   | 3500  |
| 2  | NESTED LOOPS SEMI|               | 100   | 3500  |
| 3  | TABLE ACCESS FULL| DEPT          | 100   | 2200  |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO_IDX| 1000K | 12M   |
------------------------------------------------------------

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

4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")


ต่อมาหากจำนวนของข้อมูลของ DEPT เพิ่มขึ้น และ Optimizer เห็นว่าจำนวนครั้งที่มันจะต้องเข้าไปตรวจข้อมูลจากอินเด็กซ์ของตาราง EMP มากขึ้นมาก ๆ แปลนที่ Optimizer จะใช้ในการดึงข้อมูลก็จะเปลี่ยนไป จากเดิมที่ใช้ Nested Loops ร่วมกับ Index Range Scan ก็จะเปลี่ยนไปเป็นการทำงานกับข้อมูลทั้งก้อน (แทนการใช้อินเด็กซ์) เพื่อที่จะให้เห็นภาพเราต้องบอก Optimizer ว่าตอนนี้ DEPT มีขนาดใหญ่ขึ้นมาก
{xtypo_code}SQL> begin
2 dbms_stats.set_table_stats
3 ( user,
4 'DEPT',
5 numrows=> 100000,
6 numblks => 10000 );
7 end;
8 /

จากนั้นก็รันคิวรีใหม่อีกทีหนึ่ง และดู Query Plan ที่เกิดขึ้นดังนี้
SQL> set autotrace traceonly explain
SQL> select deptno, dname
2 from dept where deptno in
3 ( select deptno
4 from emp )
5 order by deptno;

Execution Plan
-----------------------------------------------------------------
Plan hash value: 3127359958
-----------------------------------------------------------------
| Id | Operation           | Name           | Rows | Bytes |
-----------------------------------------------------------------
| 0  | SELECT STATEMENT    |                | 100K | 3417K |
| 1  | SORT ORDER BY       |                | 100K | 3417K |
|* 2 | HASH JOIN SEMI      |                | 100K | 3417K |
| 3  | TABLE ACCESS FULL   | DEPT           | 100K | 2148K |
| 4  | INDEX FAST FULL SCAN| EMP_DEPTNO_IDX | 1000K| 12M   |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------------
2 - access("DEPTNO"="DEPTNO")
SQL> select deptno, dname
2 from dept where exists
3 ( select null
4 from emp
5 where emp.deptno =
6 dept.deptno )
7 order by deptno;

Execution Plan 
---------------------------------------------------------------
Plan hash value: 3127359958

--------------------------------------------------------------- 
| Id | Operation           | Name          | Rows | Bytes |
---------------------------------------------------------------
| 0  | SELECT STATEMENT    |               | 100K | 3417K |
| 1  | SORT ORDER BY       |               | 100K | 3417K |
|* 2 | HASH JOIN SEMI      |               | 100K | 3417K |
| 3  | TABLE ACCESS FULL   | DEPT          | 100K | 2148K |
| 4  | INDEX FAST FULL SCAN| EMP_DEPTNO_IDX| 1000K| 12M   |
---------------------------------------------------------------

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

2 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

เราจะเห็นว่าขณะนี้ในคิวรีทั้งสองกรณี Optimizer เลือกใช้ (Index) Full Scan และ Hash Semi Join แทนการเข้าไปค้นใน Index ดังทีแรก เนื่องมาจากว่าไม่งั้นมันก็ต้องเข้าไปค้นในอินเด็กซ์เป็นจำนวน 100,000 ครั้ง
เรียบเรียงจาก:
http://www.oracle.com/technology/oramag/oracle/06-may/o36asktom.html