Posted by: siamnobita | 05/25/2016

DISTINCT / GROUP BY PERFORMANCE

หลายวันก่อน ผมได้รับคำถามจากรุ่นน้อง 2 คนในเวลาใกล้เคียงกันว่า ระหว่างการใช้ distinct กับ group by แบบไม่มี aggregate functions แบบใด มีประสิทธิภาพดีกว่ากัน และหนึ่งในนั้นบอกกับผมด้วยว่าเขาได้รับคำแนะนำจาก ผู้รู้ ว่าควรใช้ group by มากกว่า

ผมได้ให้คำตอบไปว่า มันไม่ต่างกัน เพราะ ORACLE ใช้เทคนิคแบบเดียวกันในการหาคำตอบ ได้แก่ การทำ hash table หรือการ sort ข้อมูล โดยแบบหลังจะเกิดขึ้นใน version เก่า ๆ (ประมาณก่อน 10g ถ้าจำไม่ผิด) หรือเมื่อต้องมีการ sort จากขั้นตอนอื่น ๆ อยู่แล้ว หากทั้งสองแบบจะมีประสิทธิภาพต่างกัน ก็น่าจะเกิดเมื่อ optimizer เลือกใช้เทคนิคที่ต่างกันเท่านั้น เพราะการใช้ hash ปกติจะเร็วกว่าการ sort และผมแนะนำว่าหากต้องการกำจัดแถวที่ซ้ำออกแล้ว การใช้ distinct จะเป็นการสื่อความต้องการได้ตรงมากกว่า

อย่างไรก็ดี หลังจากตอบคำถามไปแล้ว เพื่อยืนยันความถูกต้อง (เผื่อตอบผิด จะได้รีบแก้ตัว 555) ผมจึงทำการทดสอบง่าย ๆ โดยใช้ตาราง SALES ใน schema SH ซึ่งมีข้อมูลอยู่ประมาณ 9 แสนแถว ผลลัพธ์ที่ได้น่าสนใจมาก

SQL> set timi on
SQL> set autotrace traceonly statistics explain
SQL> select distinct s.prod_id
  2  from sh.sales s ;

72 rows selected.

Elapsed: 00:00:00.01

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |    72 |   288 |   194  (86)|       |       |
|   1 |  HASH UNIQUE                  |                |    72 |   288 |   194  (86)|       |       |
|   2 |   PARTITION RANGE ALL         |                |   918K|  3589K|    29   (0)|     1 |    28 |
|   3 |    BITMAP INDEX FAST FULL SCAN| SALES_PROD_BIX |   918K|  3589K|    29   (0)|     1 |    28 |
----------------------------------------------------------------------------------------------------

SQL> select s.prod_id
  2  from sh.sales s
  3  group by s.prod_id ;

72 rows selected.

Elapsed: 00:00:00.09

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |    72 |   288 |   194  (86)|       |       |
|   1 |  HASH GROUP BY                 |                |    72 |   288 |   194  (86)|       |       |
|   2 |   PARTITION RANGE ALL          |                |   918K|  3589K|    29   (0)|     1 |    28 |
|   3 |    BITMAP CONVERSION TO ROWIDS |                |   918K|  3589K|    29   (0)|       |       |
|   4 |     BITMAP INDEX FAST FULL SCAN| SALES_PROD_BIX |       |       |            |     1 |    28 |
----------------------------------------------------------------------------------------------------

ดูเผิน ๆ ก็ใช้เวลาไม่ต่างกันมากนัก แต่จากการทดสอบซ้ำหลาย ๆ ครั้ง ผลลัพธ์ที่ได้ก็ไม่อาจจะเรียกได้ว่าใช้เวลาเท่ากัน โดยฝ่าย distinct เร็วกว่า เมื่อเข้าไปดูใน plan จึงพบความแตกต่างบางอย่าง ใน plan ที่สองมีบรรทัด BITMAP CONVERSION TO ROWIDS เพิ่มขึ้นมา ซึ่งผมก็ไม่รู้เหมือนกันว่าทำเพื่ออะไร แต่ที่แน่ ๆ มันทำให้งานเพิ่มและ query ช้าลง นอกนั้น HASH GROUP BY กับ HASH UNIQUE แทบจะใช้เวลาไม่ต่างกันเลย

ผมลองทดสอบด้วยการใส่ hint บังคับห้ามใช้ index ผลเป็นดังนี้

SQL> select /*+ no_index(s) */ distinct s.prod_id
  2  from sh.sales s ;

72 rows selected.

Elapsed: 00:00:00.17

-----------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |    72 |   288 |   639  (31)|       |       |
|   1 |  HASH UNIQUE         |       |    72 |   288 |   639  (31)|       |       |
|   2 |   PARTITION RANGE ALL|       |   918K|  3589K|   474   (7)|     1 |    28 |
|   3 |    TABLE ACCESS FULL | SALES |   918K|  3589K|   474   (7)|     1 |    28 |
-----------------------------------------------------------------------------------

SQL> select /*+ no_index(s) */ s.prod_id
  2  from sh.sales s
  3  group by s.prod_id ;

72 rows selected.

Elapsed: 00:00:00.22

-----------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |    72 |   288 |   639  (31)|       |       |
|   1 |  HASH GROUP BY       |       |    72 |   288 |   639  (31)|       |       |
|   2 |   PARTITION RANGE ALL|       |   918K|  3589K|   474   (7)|     1 |    28 |
|   3 |    TABLE ACCESS FULL | SALES |   918K|  3589K|   474   (7)|     1 |    28 |
-----------------------------------------------------------------------------------

SQL> select /*+ no_index(s) */ distinct s.prod_id
  2  from sh.sales s
  3  order by prod_id ;

72 rows selected.

Elapsed: 00:00:00.25

-----------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |    72 |   288 |  3344  (12)|       |       |
|   1 |  SORT UNIQUE         |       |    72 |   288 |  3179   (7)|       |       |
|   2 |   PARTITION RANGE ALL|       |   918K|  3589K|   474   (7)|     1 |    28 |
|   3 |    TABLE ACCESS FULL | SALES |   918K|  3589K|   474   (7)|     1 |    28 |
-----------------------------------------------------------------------------------

SQL> select /*+ no_index(s) */ s.prod_id
  2  from sh.sales s
  3  group by s.prod_id
  4  order by prod_id ;

72 rows selected.

Elapsed: 00:00:00.23

-----------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |    72 |   288 |   639  (31)|       |       |
|   1 |  SORT GROUP BY       |       |    72 |   288 |   639  (31)|       |       |
|   2 |   PARTITION RANGE ALL|       |   918K|  3589K|   474   (7)|     1 |    28 |
|   3 |    TABLE ACCESS FULL | SALES |   918K|  3589K|   474   (7)|     1 |    28 |
-----------------------------------------------------------------------------------

คราวนี้ ผลเป็นไปตามคาดหมาย คือแทบไม่ต่างกันเลยเมื่อทดสอบซ้ำหลาย ๆ ครั้ง ดังนั้นคำพูดที่ว่า GROUP BY เร็วกว่า DISTINCT นั้นเห็นได้ชัดว่า ไม่เป็นความจริง เรามีตัวอย่างกรณีที่ DISTINCT เร็วกว่ามาให้ดูแล้ว

อย่างไรก็ดี นี่เป็นการทดสอบบน ORACLE 11gR2 ผมเองก็ไม่แน่ใจว่าในเวอร์ชั่นอื่น ๆ ผลลัพธ์จะแตกต่างจากนี้หรือไม่ และส่วนใหญ่แล้วถ้าไม่ใช่ bitmap index เราก็คงไม่ได้เห็นความแตกต่างแบบนี้ หากเราจะรีบสรุปว่า DISTINCT เร็วกว่า GROUP BY งั้นเรามารอดูกันใน post หน้าครับ

สวัสดีครับ

Posted by: siamnobita | 03/16/2016

Dynamic SQL Pivoting

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

หัวข้อในวันนี้ ไม่ใช่ผลงานของผมเอง แต่เป็นผลงานของคุณ Anton Scheffer โดยผู้อ่านสามารถอ่านรายละเอียด และ download โปรแกรมได้จาก ที่นี่ และดูตัวอย่างเพิ่มเติมได้ ที่นี่ และขอขอบคุณเว็บ asktom ที่พาผมไปเจอผลงานชิ้นนี้ด้วย

ผมเคยเชื่อมาตลอดจนกระทั่งมาเจอ code นี้ว่า คำสั่ง select ไม่สามารถเปลี่ยนแปลงจำนวน column ที่แสดงผลออกมาได้ ต่อให้เราใช้ syntax PIVOT ของเวอร์ชั่น 11g ที่แปลงจาก rows เป็น columns ได้จำนวน columns ที่ได้ก็ต้องถูกกำหนดตายตัวด้วยค่าที่ระบุไว้อยู่ดี

select /* 1 */ *
from table(pivot(‘select deptno, job, sum(sal) sal from emp group by deptno, job’))
order by 1

DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN
———- ———- ———- ———- ———- ———-
10 1300 2450 5000
20 6000 1900 2975
30 950 2850 5600

insert into emp ( empno, ename, deptno, job, sal )
values ( 8000, ‘TOM’, 10, ‘SUPPORT’, 2500)
1 rows inserted.

select /* 2 */ *
from table(pivot(‘select deptno, job, sum(sal) sal from emp group by deptno, job’))
order by 1
DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN SUPPORT
———- ———- ———- ———- ———- ———- ———-
10 1300 2450 5000 2500
20 6000 1900 2975
30 950 2850 5600

rollback
rollback complete.

จะเห็นได้ว่า คำสั่ง select ทั้งสองครั้งแทบไม่ต่างกันเลย ยกเว้นตัวเลขภายใน comment แต่ผลลัพธ์ที่ได้ในครั้งที่สองมี column เพิ่มขึ้นจากข้อมูลที่เรา insert เพิ่มเข้าไปก่อนหน้า (หากใช้ query ที่เหมือนกันเป๊ะเลย อาจจะไม่ได้ผลแบบนี้ เราต้องหลอก optimizer เพื่อให้ parse คำสั่งใหม่ ด้วยการแก้ไข query เล็กน้อยจึงจะเห็น column ที่เพิ่มขึ้นมา)

ในความเห็นของผม นี่เป็นการประยุกต์ใช้สิ่งที่ oracle มีได้อย่างน่าสนใจและดูมหัศจรรย์มาก แต่ในอีกมุมหนึ่งผมก็ยังนึกไม่ออกเหมือนกันว่าจะนำไปใช้ในงานจริงในกรณีใดบ้าง เพราะเมื่อเรา dynamic ผลลัพธ์ โปรแกรมต่าง ๆ ที่ต้องนำผลลัพธ์นี้ไปใช้ ก็ต้อง dynamic ตามไปด้วย ซึ่งผมเองก็ไม่แน่ใจเหมือนกันว่าจะมี report tools ตัวใดบ้างที่มีความสามารถแบบนี้ นอกจากเราจะเขียนโปรแกรมแสดงผลขึ้นเอง ซึ่งในกรณีนั้น เราก็คงเขียน dynamic sql ได้อยู่แล้ว โดยไม่จำเป็นต้องใช้ code ตัวนี้

อย่างไรก็ดี ต้องขอขอบคุณผู้เขียน เพราะได้ทำให้ผมได้เรียนรู้อะไรเพิ่มขึ้นอีกหลายอย่าง และหวังว่าท่านผู้อ่าน blog นี้ก็คงจะได้ความรู้ใหม่ ๆ เพิ่มขึ้นเช่นเดียวกัน

Posted by: siamnobita | 09/24/2015

NO_DATA_FOUND in SQL statements

หัวข้อวันที่มีที่มาจาก Nimish Garg’s blog

ใช้งาน oracle มาก็นานปี แต่ผมเพิ่งรู้จริง ๆ นะว่าคำสั่ง SQL ทั้งหมดมองข้าม exception NO_DATA_FOUND เหมือนไม่มีอะไรเกิดขึ้น

create or replace function test_err_1
return number as
begin
raise no_data_found ;
return 0;
end;
/

exec dbms_output.put_line (test_err_1)

Error starting at line 1 in command:
exec dbms_output.put_line (test_err_1)
Error report:
ORA-01403: no data found
ORA-06512: at “TOM.TEST_ERR_1”, line 4
ORA-06512: at line 1

select test_err_1 from dual ;

TEST_ERR_1
———-
จะเห็นว่าเมื่อทดสอบฟังก์ชั่น test_err_1 ด้วย pl/sql จะเกิด error ขณะที่เมื่อทดสอบด้วย sql จะคืนค่า null ออกมาเฉย นอกจากคำสั่ง select แล้วหากเราทดสอบด้วยคำสั่ง insert หรือ update ก็ได้ผลเช่นเดียวกัน

create table test1 (n number) ;

table TEST1 created.

insert into test1 values (test_err_1) ;

1 rows inserted.

update test1 set n = test_err_1 ;

1 rows updated.

ด้วยความอยากรู้ว่าทำไมจึงเป็นแบบนี้ และเป็นแบบนี้มาตั้งแต่เมื่อไร จึงค้นดู ได้คำตอบจาก Tom Kyte เจ้าเก่าตาม นี้

สรุปคือมันเป็นแบบนี้มานานแล้ว ด้วย oracle มองว่า exception ไม่ใช่ error หรือข้อผิดพลาด เป็นแค่การดักเหตุการณ์บางอย่างขึ้นอยู่กับผู้เขียนโปรแกรมเองที่จะมองว่าเหตุการณ์นั้นถือเป็น error หรือไม่ได้เป็น ซึ่งในกรณีนี้ SQL engine ไม่มองว่า no_data_found เป็น error เป็นแค่การแจ้งเตือนว่า ข้อมูลที่มีหมดแล้ว เลิกงานได้ มันจึงมองข้ามไปเฉย ๆ เท่านั้น ซึ่งหากเป็น exception ตัวอื่น มันก็จะแสดงข้อผิดพลาดออกมาตามปกติ ซึ่งพฤติกรรมนี้ไม่ได้บันทึกไว้ในเอกสารใด ๆ ของ oracle เลย เพราะเค้ามองว่ามันเป็นเรื่องปกติมากกกกก (แอบมึน ปกติตรงไหนเนี่ยะ)

หากเราต้องการให้ SQL มอง NO_DATA_FOUND เป็นข้อผิดพลาด แล้วหยุดการทำงานต้องดัก exception ไว้ แล้ว raise ตัวอื่นมาแทนแบบนี้

create or replace function test_err_2
return number as
begin
raise no_data_found ;
return 0;
exception
when no_data_found then raise program_error ;
end;
/

select test_err_2 from dual ;

ORA-06501: PL/SQL: program error
ORA-06512: at “TOM.TEST_ERR_2”, line 7
ORA-01403: no data found

วันนี้ก็ได้ความรู้ใหม่สำหรับตัวเองอีกแล้ว

Posted by: siamnobita | 09/03/2015

Thank you Tom Kyte

http://www.oracle.com/technetwork/issue-archive/2015/15-sep/last-asktom-2650480.html

เพิ่งได้อ่านข่าว Tom Kyte จะเกษียณตัวเอง ไม่ได้ตอบคำถามที่ web asktom อีกแล้ว และให้ผู้เชี่ยวชาญท่านอื่นมาตอบคำถามแทน

ในฐานะลูกศิษย์ที่ได้ความรู้มากมายจากที่แห่งนี้ อดรู้สึกเสียดายไม่ได้ แต่ก็ต้องแสดงความยินดีที่เค้าจะได้พัก และทำสิ่งที่ตัวเองต้องการทำอย่างเต็มที่

Tom Kyte เป็นอัจฉริยะตัวจริงในวงการ Oracle และเป็น idol อันดับหนึ่งของผม นับจากวันแรกที่ได้รู้จักเขาจากหนังสือ Effective Oracle by Design ตามมาด้วยการเรียนรู้เทคนิคต่าง ๆ จากเว็บไซต์ของเขา แทบจะเรียกได้ว่าความรู้ทั้งหมดที่ใช้ทำมาหากินอยู่ทุกวันนี้ ได้มาจากเขาเกินกว่าครึ่ง

ผมคิดว่าสิ่งที่สำคัญที่สุดที่ได้เรียนรู้จากเขา 2 เรื่อง ได้แก่ เรื่องแรกการเรียนรู้ไม่มีวันสิ้นสุดจากคำกล่าวของเขาที่ว่า I can learn a new thing on Oracle everyday และเรื่องที่สองการฝึกฝนตัวเองที่ดีที่สุดคือการแบ่งปันความรู้ของเราให้กับคนอื่น ผ่านการตอบคำถาม หรือการสอน และทั้งสองเรื่องนี้คือหัวใจสำคัญที่ผมมาถึงจุดนี้ในวันนี้

Thank you very much, Tom. I’m looking forward to reading your new books soon.

Posted by: siamnobita | 02/25/2015

Index and not equal

เพิ่งพบว่า trick ที่เคยใช้ได้ เหมือนจะใช้ไม่ได้แล้วใน 12c

เดิมหากเราต้องการใช้ index สำหรับเงื่อนไข col ไม่เท่ากับ ‘x’ เพราะเราคิดว่าข้อมูลส่วนใหญ่คือข้อมูลที่เท่ากับ ‘x’ เราสามารถใช้ trick โดยเขียนเงื่อนไขใหม่เป็น col น้อยกว่า ‘x’ หรือ col มากกว่า’x’ แต่วันนี้ optimizer เธอเปลี่ยนไป!

SQL> set autotrace traceonly
SQL> select * from bowie where text < 'BOWIE' or text > 'BOWIE';

1000001 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1845943507

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1000K| 10M| 636 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| BOWIE | 1000K| 10M| 636 (2)| 00:00:01 |
—————————————————————————

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

1 – filter(“TEXT”<>’BOWIE’)

Optimizer ฉลาดเกินไปแล้ว ไม่ยอมให้หลอกอีกต่อไป ทำอย่างไรดี เหมือนไม่สามารถปิดได้ด้วย บังคับใช้ /*+ use_concat */ ก็ไม่ได้ ปิด transformation ด้วย /*+ no_query_transformation */ ก็ไม่ได้
ผมพยายามลองอยู่หลายแบบพบว่า ต้องเขียนแบบนี้

SQL> select * from bowie where text < 'bowie' 2 union all 3 select * from bowie where text > 'bowie' ;

Execution Plan
———————————————————-
Plan hash value: 2927488297

————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————————
| 0 | SELECT STATEMENT | | 2 | 22 | 8(50) | 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE | 1 | 11 | 4 (0) | 00:00:01 |
|* 3 | INDEX RANGE SCAN | BOWIE_I | 1 | | 3 (0) | 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| BOWIE | 1 | 11 | 4 (0) | 00:00:01 |
|* 5 | INDEX RANGE SCAN | BOWIE_I | 1 | | 3 (0) | 00:00:01 |
————————————————————————————————

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

3 – access(“TEXT”<‘bowie’) 5 – access(“TEXT”>’bowie’)

ไม่รู้ครั้งต่อไปจะมีอะไรให้ surprised อีก

Posted by: siamnobita | 11/25/2011

Pseudocolumn

ไม่ได้เข้ามาเขียน blog นานจนแทบจะลืมไปแล้ว วันนี้หัวข้อที่เขียนไม่ได้เป็นสาระอะไรมากมาย แต่ขัดอกขัดใจนิดหน่อย เมื่อเว็บที่เราติดตามประจำอ้างถึง sysdate ว่าเป็น pseudocolumn ที่จริงมันก็ไม่ได้สำคัญอะไรว่า sysdate จะเป็น pseudocolumn หรือเปล่า แค่ให้รู้ว่ามันคืนค่าเป็นวันที่ปัจจุบันก็น่าจะพอแล้ว แต่ว่าพูดถึงมันซะหน่อยก็ดี เพราะตอนผมเริ่มเรียนรู้ oracle ใหม่ ๆ ก็งงกับศัพท์ตัวนี้เหมือนกัน (ไม่รู้ว่า database ยี่ห้ออื่นใช้แบบเดียวกันหรือเปล่านะ)

หากอ้างอิงจาก oracle sql reference แล้ว sysdate ถือเป็น function ครับ ไม่ใช่ pseudocolumn โดย oracle ให้คำจำกัดความระบุความแตกต่างของ function กับ pseudocolumn ไว้อย่างชัดเจนดังนี้
A pseudocolumn behaves like a table column, but is not actually stored in the table.
You can select from pseudocolumns, but you cannot insert, update, or delete their
values. A pseudocolumn is also similar to a function without arguments (refer to
Chapter 5, “Functions”). However, functions without arguments typically return the
same value for every row in the result set, whereas pseudocolumns typically return a
different value for each row.

แปลได้ใจความว่า “ฟังก์ชันที่ไม่ระบุ argument ใด ๆ เมื่อเรียกใช้ใน query จะให้ผลลัพธ์เหมือนกันสำหรับทุกแถว ขณะที่ pseudocolumn อาจให้ผลลัพธ์ที่ต่างกันในแต่ละแถว” ตัวอย่างของ pseudocolumn ที่เห็นกันบ่อย ๆ ก็เช่น rownum, rowid, level, nextval เป็นต้น ซึ่งในแต่ละแถวจะคืนค่าที่ต่างกันได้

แต่หากเราใช้ฟังก์ชั่น sysdate ค่าที่ได้ออกมาจะเป็นวันที่และเวลาขณะเริ่มรัน query นั้นแม้ว่า query นั้นจะใช้เวลาหลายวินาที ค่าที่ได้ก็จะไม่แตกต่างกัน ทำให้ oracle จัด sysdate ให้เป็นฟังก์ชั่นครับ

อนึ่งนิยามนี้น่าจะจำกัดเฉพาะ built in sql function ของ oracle เองเท่านั้น หากเราเขียนฟังก์ชั่นขึ้นมาใช้เอง ก็ไม่จำเป็นว่าจะต้องคืนค่าเดียวกันทุกแถวเสมอไป รวมทั้งใน supplied package ด้วยเช่น dbms_random.value เป็นต้น

หวังว่าหัวข้อนี้จะช่วยลดความสับสนให้กับผู้ที่่ยังใช้ศัพท์สองตัวนี้ไม่ถูกต้องได้บ้างนะครับ

Posted by: siamnobita | 06/23/2010

Design Database Performance

หัวข้อในวันนี้จุดประกายความคิดมาจาก up1’s Blog แห่งเว็บ narisa

ผมไม่เคยใช้ MySQL จึงไม่ขอแสดงความเห็นอะไรเกี่ยวกับหลักการ design บน database ตัวนี้ แต่ก็ลองมาคิดดูว่าถ้าเป็น Oracle แล้วผมจะมี tips อะไรในการออกแบบเพื่อประสิทธิภาพบ้าง

ข้อแรก การออกแบบตารางและข้อมูลในตาราง ผมเห็นด้วยกับคุณ up1 ว่าควรทำ Normalization ก่อน แล้วค่อยทำ Denormalization เมื่อจำเป็น อย่างไรก็ดีคำว่า จำเป็น นั้นเราจะรู้ได้อย่างไร ผมเป็น SA มาหลายปี แต่ไม่เคยจำได้สักทีว่าทำ Normalization แต่ละ form เค้าต้องทำอะไรกันบ้าง ถ้าเอาตารางที่ผมออกแบบไปให้ครูบาอาจารย์ทั้งหลายตรวจ มีหวังได้ศูนย์คะแนนสมชื่อโนบิตะแน่ ๆ

อย่างไรก็ดีผมมีหลักการในการออกแบบเพื่อประสิทธิภาพโดยตั้งต้นจาก output ที่ต้องการ เช่น รูปแบบของรายงาน เงื่อนไขในการค้นหา ความถี่ในการใช้ และที่สำคัญความคาดหวังของผู้ใช้ เวลาที่ผมรวบรวม requirement ผมจะถามความคาดหวังของลูกค้ามาด้วย (แต่ไม่เคยรับปากว่าจะทำให้ แหะ ๆ tip อันนี้ สำคัญนะครับ) จากนั้นผมก็จะเริ่มออกแบบว่าจะมีข้อมูลอะไรบ้าง แต่ละข้อมูลควรวางไว้ที่ตารางไหน และแต่ละตารางจะมีความสัมพันธ์กันอย่างไร นี่คือการ normalize ของผม ซึ่งจะเน้นที่ข้อมูลไม่ซ้ำซ้อน และไม่เก็บค่าที่คำนวณได้จาก column อื่น ซึ่งจะทำให้การออกแบบหน้าจอ input ง่ายขึ้น

จากนั้นก็ย้อนมาดู query หลัก ๆ ที่เป็น concern ของผู้ใช้ ย้อนกลับมาว่าเรายอมวางข้อมูลเดียวกันซ้ำสองที่ในเรื่องใดบ้าง หรือเก็บค่าจากการคำนวณเอาไว้ล่วงหน้าหรือไม่ นี่คือการ denormalize ตรงนี้ผมอาจต้องขอค้านคุณ up1 เล็กน้อยที่ว่า การ join เร็วกว่าการ denormalize ไม่น่าจะจริงครับ ขึ้นกับว่าการ denormalize นั้นทำแล้วตอบตรงคำถามหรือไม่ต่างหาก (แต่ตรงนี้ต้องขอเสริมว่า ไม่ต้องกลัวการ join นั้น เป็นหลักการที่ถูกต้องครับ การ join เป็นงานหลักของ RDBMS อยู่แล้ว)

ข้อที่สอง การเลือก primary key ผมจะมองหา natural key ก่อนตัดสินใจใช้ surrogate key ผมมักจะดึง primary key ของ parent มาเป็นส่วนหนึ่งของ primary key ของตารางที่เป็น child เสมอ มองในแง่การ normalize นี่เป็นจุดที่ขัดแย้ง แต่ผมพบว่ามันมักเกิดประโยชน์ในภายหลัง เมื่อเราต้องการดูข้อมูลในตารางที่เป็น หลาน เหลน โหลน ของตารางแรก เราจะลดการ join 3-4 ตารางได้ ถึงจะไม่กลัวการ join แต่เลี่ยงได้ก็ไม่เสียหายนี่ครับ

ปัญหาเดียวของการกำหนด primary key แบบนี้ คือเราไปเลือก primary key ตัวที่มีสิทธิ์เปลี่ยนแปลงได้ กติกาของ primary key มีอยู่ว่าต้อง unique และมีค่าเสมอไม่เป็น null แค่สองข้อเท่านั้น แต่ primary key ที่ดีควรมีกติกาเพิ่มอีกหนึ่งข้อคือ ห้าม update ด้วย ผมมี case ที่จำฝังใจมาตลอด คือ member no ของบริษัทประกันภัยแห่งหนึ่ง ซึ่งเขาใช้ตัวเลขหลักสุดท้ายในการบอกว่า 0 คือผู้เอาประกันหลัก 1 คือคู่สมรส และ 2,3,.. คือบุตร member no นี้ไม่มีวันซ้ำกัน (ณ ขณะใด ๆ) และต้องมีค่าเสมอ แล้วผมก็ใช้มันเป็น primary key สิ่งทีผมมองข้ามคือ วันดีคืนดีผู้เอาประกันหลักหย่าแล้วแต่งงานใหม่ คนเก่าก็ลบจากระบบไม่ได้เพราะมีตารางอื่น link มาเต็มไปหมด คนใหม่ก็จำเป็นต้องใช้รหัสของคนเก่า สิ่งที่ต้องทำคือต้องเขียนโปรแกรมตาม update member no ของคนเก่า ในตารางลูกหลานเหลนโหลนทั้งหมด แล้วค่อย insert คนใหม่เข้ามา นิทานเรื่องนี้สอนให้รู้ว่า primary key ที่มีความหมายในตัวเองแบบนี้ เสี่ยงกับการเปลี่ยนแปลงภายหลัง ถึงกระนั้นก็ตามผมก็ยังเลือกใช้ primary key ที่มีความหมายอยู่ดี แต่ใช้ความระมัดระวังมากขึ้น

ข้อที่สาม การเลือกชนิดของข้อมูล คุณ up1 พูดถึงกรณีของ MySQL ที่เกี่ยวกับเรื่องนี้ ของ Oracle ก็มีเหมือนกัน หลักการง่าย ๆ ของผมก็คือ เก็บวันที่ไว้ใน Date เก็บตัวเลขไว้ใน Number เรื่องนี้มีผลกับ performance โดยตรงเลย เพราะการเลือกใช้ชนิดของข้อมูลให้ตรงกับข้อมูลที่จะเก็บนั้น เป็นทางหนึ่งที่จะบอกให้ optimizer รู้ว่าการกระจายตัวของข้อมูลที่แท้จริงเป็นอย่างไร จึงจะเลือก execution plan ที่เหมาะสมให้ สมัยเป็น SA ใหม่ ๆ มีคนบอกผมให้เก็บตัวเลขที่ไม่ต้องมีการคำนวณเช่น id ไว้ใน varchar2 ซึ่งก็โอเคตราบเท่าที่เราค้นหาข้อมูลด้วยเงื่อนไข id = ??? เท่านั้น หากเราค้นหาด้วย id >= ??? หรือ between … and … ชนิดของข้อมูลที่เราใช้มีผลทำให้ plan ผิดได้ทันที

เคยมีคนบอกผมว่าไม่อยากใช้ date ใน oracle เพราะเคยเจอปัญหากับ database ยี่ห้ออื่น หรือประมาณว่าถ้าย้ายไป database อื่นจะเกิดปัญหา อะไรทำนองนี้ นี่เป็นอีกประเด็นที่สำคัญ หลักการออกแบบที่ดีต้องขึ้นกับ database ที่เราใช้งานจริงเท่านั้น การมองเผื่อไปว่าอนาคตต้องไปใช้กับ database อื่น ๆ อีก เป็นตัวถ่วงประสิทธิภาพอย่างแรงครับ เพราะเราไม่สามารถใช้งาน database ของเราอย่างเต็มที่ จะโดนห้ามไปหมดทุกอย่างเลย date ก็ห้ามใช้ ค่า null ก็ห้ามใช้ ฟังก์ชั่นหลาย ๆ ตัวก็ห้ามใช้ด้วย ผมว่าคนที่พูดแบบนี้ไม่เคยศึกษาความสามารถของ DBMS อย่างจริงจังครับว่ามันทำอะไรได้บ้าง เลยคิดว่ามันก็แค่ตาราง กับ select command เท่านั้น

อีกประเด็นที่คู่กับการกำหนดชนิดของข้อมูล คือการกำหนดขนาดของข้อมูลครับ การกำหนดขนาดสูงสุดของข้อมูลที่เราประมาณไว้เป็นการบอกให้ฐานข้อมูลช่วยกรองข้อมูลที่ผิดเพี้ยนออกให้ตั้งแต่ก่อนนำเข้า เราอาจเผื่อเพิ่มไว้สักหลักสองหลักอันนี้ไม่มีปัญหา หรือเผื่อน้อยเกินไป เพิ่มทีหลังก็ยังไหว แต่หลาย ๆ ครั้ง ผมเคยเห็นประกาศเป็น number หรือเป็น varchar2(4000) โดยอ้างว่า oracle ใช้พื้นที่เก็บข้อมูลตามจริงนั้น ซึ่งเป็นข้ออ้างที่ไม่เข้าท่าเลยครับ คนที่ออกแบบตารางแบบนี้เป็น SA ที่ไม่รับผิดชอบงานที่ตัวเองต้องทำ ถึงจะไม่ได้ใช้พื้นที่เก็บข้อมูลมากขึ้น แต่ Programmer ที่จะเขียนหน้าจอบันทึกข้อมูลต้องเผื่อไว้แค่ไหนครับ หน้ารายงานด้วย ให้เขาเดาเอาเองหรือครับ นี่เป็นหน้าที่ของ SA ต่างหากที่จะต้องเดา ไม่ใช่ Programmer เดาเสร็จแล้วก็บอกเขาด้วย definition ของตารางนี่แหละครับ ไม่ต้องอาศัย program spec. อะไรให้มันยุ่งยากอีก

แล้วถ้าประกาศ varchar2(4000) รู้ไหมครับว่า เกิดผลอะไรกับการสร้าง index ตอนสร้าง index oracle จะเช็คขนาดของ index entry ที่เป็นไปได้จากค่าสูงสุดที่เราประกาศไว้นี่แหละ แล้วถ้าเกินขนาดของ block (ลบ overhead อีกเล็กน้อย) ก็จะไม่อนุญาตให้สร้าง index นั้น ผลก็คือ composite index ที่ประกอบด้วย varchar2(4000) 2 column สร้างไม่ได้บน default block size (8k)

เขียนเป็นคุ้งเป็นแคว เพิ่งออกแบบแค่ตารางเอง ยังไม่ได้ออกแบบ index เลย เอาไว้ต่อคราวหน้านะครับ (ถ้าไม่ลืมเสียก่อน)

Posted by: siamnobita | 06/17/2010

Finding continuous number

หัวข้อในวันนี้ได้รับการอนุเคราะห์จากคำถามของคุณ Artelrooy ที่เว็บบอร์ด pantip

ทางผมต้องการ นับ ข้อมูล เฉพาะ down โดยแต่ละรอบจะมีข้อมูลเข้ามาทุก 5 นาที ครับ เช่น รอ 12.00  ได้ down ก็จะนับ 1  แต่ต่อมา รอบ 12.05 ส่ง down มาอีกจะไม่นับ  จนกว่า ข้อมูล จะเป็น up  แล้ว ส่ง down มาให้อีกทีจะนับ เป็น 2 ครับ เงื่อนไขคือ ถ้ามี down ส่งมาติดต่อ กัน จะนับ เพียง 1 ครั้ง ครับ  ไม่ทราบว่าต้องเขียน query อย่างไรครับ

เห็นว่าคำถามนี้น่าสนใจดี สามารถใช้เป็นตัวอย่างให้เห็นประโยชน์ที่บางคนคิดไม่ถึงของ analytic functions ได้

สำหรับผู้อ่านที่คุ้นเคยกับ analytic functions ของ Oracle ดีอยู่แล้วอ่านคำถามครั้งแรกก็น่าจะนึกถึง lead, lag functions ซึ่งสามารถใช้ในการเปรียบเทียบข้อมูลในแถวปัจจุบันกับข้อมูลในแถวก่อนหน้าได้ อย่างไรก็ดีสำหรับ database ยี่ห้ออื่น เท่าที่ผมรู้ยังไม่มี function 2 ตัวนี้ ดังนั้น วันนี้ผมจะแสดงตัวอย่างของการนำ function row_number มาประยุกต์ใช้ในกรณีนี้ครับ

SQL> select tt1, status
  2  from down_time
  3  /
TT1      STAT
-------- ----
00:00:00 UP
...
09:05:00 UP
09:10:00 DOWN
09:15:00 UP
...
19:40:00 UP
19:45:00 DOWN
...
20:30:00 DOWN
20:35:00 UP
...
21:55:00 UP
22:00:00 DOWN
...
22:15:00 DOWN
22:20:00 UP
...
23:55:00 UP
288 rows selected.

SQL> select count(distinct (rn1-rn2)) cnt_down
  2  from (
  3  select status
  4    , row_number () over (order by tt1) rn1
  5    , row_number () over (partition by status order by tt1) rn2
  6  from down_time
  7  )
  8  where status = 'DOWN'
  9  /
  CNT_DOWN
----------
         3

tips นี้ผมได้ต้นแบบมาจาก otn forum โดยคุณ Rod West และ Vadim Tropashkoโดย query ต้นฉบับนั้นใช้ตอบคำถามว่าเราจะยุบแถวหลาย ๆ แถว ให้เหลือเป็นแต่ละช่วงที่ต่อเนื่องกันได้อย่างไร เช่น 1,2,3,5,6,8,9,10 ก็เหลือเพียง 3 แถว คือ 1-3, 5-6 และ 8-10 เป็นต้น

สำหรับคำถามที่ต้องการยุบข้อมูลมากกว่าหนึ่งแถวให้เหลือเพียงหนึ่งแถวนั้น ส่วนใหญ่เราต้องตอบด้วย group by และ aggregate functions ปัญหาคือเราจะ group by ด้วยอะไร ผมจะแสดงวิธีแก้ปัญหาของเขาโดยใช้ rownum

SQL> select rownum, n1, n1-rownum diff
  2  from t1
  3  order by n1 ;
    ROWNUM         N1       DIFF
---------- ---------- ----------
         1          1          0
         2          2          0
         3          3          0
         4          5          1
         5          6          1
         6          8          2
         7          9          2
         8         10          2
8 rows selected.

ดูจากตัวอย่างข้างบนจะเห็นได้ว่าทุกค่าที่อยู่ติดกัน จะมีผลต่างระหว่างค่ากับ rownum ที่เท่ากัน ผลต่างนี้คือสิ่งที่เราจะใช้ในการ group by

SQL> select min(n1) n_from, max(n1) n_to
  2  from ( select rownum rn, n1 from t1 order by n1 )
  3  group by n1-rn
  4  order by n_from
  5  /
    N_FROM       N_TO
---------- ----------
         1          3
         5          6
         8         10

กลับมาที่คำถามของเราจะเห็นว่ายากขึ้นอีกนิด แต่ก็เป็นคำถามที่ไม่ต่างจากคำถามนี้ในแง่ที่ว่าต้องการยุบ status ที่มีค่า ‘DOWN’ ที่อยู่ติดกันให้เหลือเพียงหนึ่ง สิ่งที่ผมต้องทำเพิ่มก็คือสร้าง running number ขึ้นมาสองตัว ตัวหนึ่งนับเฉพาะค่าที่ เป็น ‘DOWN’ (ซึ่งคือ row_number ที่มี partition by clause ) อีกตัวนับทั้งหมด (ซึ่งคือ row_number ที่มีแต่ order by clause)  จากนั้นเมื่อกรองข้อมูลมาดูเฉพาะที่เราสนใจคือ ‘DOWN’ เราก็จะได้ตัวเลขสองชุด ชุดที่หนึ่งเรียงกันแบบต่อเนื่อง ส่วนชุดที่สองเป็นตัวเลขที่มี gap ซึ่งเกิดจากข้อมูลที่ไม่ใช่ down เพียงเท่านี้ เราก็จะได้คำถามในลักษณะเดียวกันแล้ว แต่ไม่ได้ต้องการค่าสูงสุด ต่ำสุดในแต่ละช่วง แต่ต้องการนับว่ามีกี่ช่วง ซึ่งสามารถตอบได้ด้วย function count (distinct ..)

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

SQL is fun!

Posted by: siamnobita | 05/24/2010

Guess Work

หัวข้อในวันนี้มีที่มาจากกระทู้หนึ่งใน narisa.com 

คำถามในกระทู้เริ่มต้นมาจากผลลัพธ์แปลก ๆ จาก query ธรรมดา ๆ ประโยคหนึ่ง ผู้ตั้งคำถามก็แสดงทั้ง query และผลลัพธ์ให้ดูในการตั้งคำถามครั้งแรก อย่างไรก็ดีไม่มีสิ่งใดที่พอจะบอกได้เลยว่าข้อผิดพลาดนั้นจะเกิดจากที่ใด นอกจากคำบอกเล่าที่ว่า “table นี้มี index และอื่นๆ อีกมากมาย”

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

ผลลัพธ์ยังไม่เปลี่ยนแปลงคือไม่รู้ว่า ข้อผิดพลาดนั้นเกิดจากที่ใด ปฏิบัติการเดาเริ่มขึ้น ตั้งข้อสงสัยไปที่ implicit conversion เนื่องจากเห็นการใช้ data type ที่ไม่ตรงกันระหว่างคอลัมน์กับค่าที่นำมาเปรียบเทียบ รวมถึงตั้งข้อสงสัยในตัวผู้ตั้งคำถามด้วยว่าเกิดความสับสนเรื่อง schema หรือไม่ (ทั้ง ๆ ที่ไม่น่าเกี่ยวกับความเพี้ยนที่เกิดขึ้นเลย)

ผ่านไปห้าวัน ผู้ตั้งคำถามกลับมาพร้อมบอกว่าแก้ปัญหาได้แล้ว ด้วยการ drop index แล้วสร้างใหม่ และสรุปว่า “โดยสาเหตุเกิดมาจาก Index บางตัวทำงานผิดพลาด (โดยไม่ทราบสาเหตุ แต่เป็นไปได้ว่าเกิดจากการที่ไฟดับ ทำให้ server ไม่ได้ shutdown ตามขั้นตอน)”

ปัญหาของผู้ตั้งคำถามได้รับการแก้ไขเรียบร้อยแล้ว แต่ปัญหาของผมยังคาใจอยู่ จริงหรือที่ index ทำงานผิดพลาดเพราะไฟดับ ฟังก์ชั่น recovery ของ oracle มีช่องโหว่ถึงขนาดนี้เชียวหรือ ด้วยความรักใน oracle ผมอยากยืนยันว่าเป็นไปไม่ได้ แต่ด้วยความซื่อสัตย์ต่ออาชีพ ผมยังยืนยันแบบนั้นไม่ได้ เพราะไม่สามารถพิสูจน์สมมติฐานอะไรได้เลย ปัญหามันจบไปแล้วด้วยการ drop index แล้วสร้างใหม่ และผมก็ไม่สามารถเรียกร้องให้เขานำปัญหากลับมาเพื่อให้ผมหาสาเหตุต่อได้ด้วย😦

ตามหลักมุทิตา เมื่อเขาแก้ปัญหาได้ผมก็ขอแสดงความยินดีด้วย อย่างไรก็ดี เนื่องจากสาเหตุที่วิเคราะห์มายังมีข้อน่าสงสัย ปัญหาดังกล่าวอาจเกิดขึ้นได้อีก เมื่อไฟดับ? (แซวเล่นเฉย ๆ) หนทางในการแก้ปัญหาอย่างถาวรยังต้องหาสาเหตุที่แท้จริงต่อไป ผมจึงขอบันทึกสิ่งที่ผมสงสัยเอาไว้ ณ ที่นี้

  1. execution plan ก่อนและหลังการแก้ไข มีการเปลี่ยนแปลงอย่างไร การลบและสร้าง index นอกจากตัว index ที่ใหม่แล้ว oracle จะยกเลิก plan เดิม และคำนวณ plan ออกมาใหม่ด้วย
  2. มีอะไรที่ทำงานอยู่เบื้องหลัง query นี้บ้าง ตัวอย่างเช่น implicit conversion ที่ผมสงสัยในครั้งแรก (อย่าเพิ่งเห็นว่าธรรมดาเกินไป ถ้ามันเกี่ยวกับ character set ด้วยเล่า ทีมทดสอบของ oracle ไม่ได้ตรวจสอบครบทุกกรณีแน่ ๆ อาจมี bug อยู่ที่ใดที่หนึ่ง)
  3. query ที่เรามองเห็นมันอาจไม่ใช่ query ที่รันจริง ๆ oracle มี feature อื่น ๆ ที่สามารถ rewrite query ได้อยู่มากมาย อาจต้องรัน sql trace หรือ set event trace อื่น ๆ เพื่อค้นให้พบว่าผลลัพธ์ที่ได้มานั้นออกมาจาก query อะไรกันแน่
  4. ผมยังไม่ตัดข้อสงสัยของเจ้าของกระทู้ที่ว่า โครงสร้างของ index ผิดพลาดเป็นสาเหตุของปัญหา แม้ว่าผมยังไม่สามารถเชื่อมโยงกับทฤษฎีการทำงานของ database ได้ ดังนั้นการ dump โครงสร้างของ index ใน block ที่เกี่ยวข้อง ก็อาจช่วยให้เห็นสาเหตุของปัญหาได้เช่นเดียวกัน

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

ขอธรรมะจงอยู่ในใจคนไทยทุกคน

Posted by: siamnobita | 03/09/2010

SQL Tuning Again

เข้าไปอ่าน blog ของเพื่อนบ้านมา เกี่ยวกับเรื่อง Oracle Performance Tuning อ่านแล้วก็ขัดอกขัดใจอย่างมากมาย โดยเฉพาะในส่วนที่อธิบายเกี่ยวกับ sql tuning ( พออ่านไปถึงหนังสืออ้างอิงก็ร้องอ๋อเลย อีตาดอน เบอร์สั้นอีกแล้ว)

สรุปจากเนื้อหาที่ว่ามาใน blog นั้น เขามีขั้นตอนในการ tune sql อยู่ 4 ขั้นตอนย่อย

  • Remove unnecessary large-table full-table scans
  • Cache small-table full-table scans
  • Verify optimal index usage
  • Materialize your aggregations and summaries for static tables

สามข้อแรก แตกมาเป็นสามข้อ แต่ความจริงหนีไม่พ้นเรื่องเดียวกันคือการเลือก full table scan หรือ index scan ส่วนข้อสุดท้ายเป็นการ save queries output เอาไว้ ประมาณว่าถ้า query มันช้า ก็ save มันเก็บไว้ซะเลยคราวหลังจะได้ไม่ต้องรันอีก ง่ายดีจริง ๆ (อาจเป็นการมองแบบอคติมากไปหน่อย แต่ผมหมั่นไส้คุณดอนมานานแล้ว แหะ แหะ)

อันที่จริงแล้ว การ tune sql แบบที่ tune execution plan กันเลยนั้น มีข้อปลีกย่อยอีกเยอะแยะ เขียนกันเป็นตำราได้ทั้งเล่มเลยด้วยซ้ำ การสรุปมาแค่ 4 ข้อนี้นั้น มันน้อย over เกินไป นอกจาก access path แล้วคุณต้องดู join order, join method, query transformation เป็นอย่างน้อย นี่ขัดใจข้อแรก

ขัดใจข้อต่อมา การเปรียบเทียบระหว่าง table scan กับ index scan โดยใช้แค่ขนาดของ table เป็นตัวตัดสินใจ เอ่อ ไม่อยากเชื่อจริง ๆ ว่าพี่แกทำมาหากินผ่านมาได้อย่างไร หรือแกกั๊กความรู้ที่แท้จริงเก็บไว้ใช้เอง ที่เขียนสอนพวกเรา แค่เสี้ยวน้อย ๆ แค่พอสร้างศรัทธาให้เราเชื่อว่าพี่แกต้องแน่จริง ถึงกล้าเขียนเป็นหนังสือออกมาขาย

ผมคงยังไม่มีเวลาพอเถียงแกในเรื่องอื่น ๆ ดังนั้นวันนี้จะจับประเด็นร้อน เรื่อง index นี่ก่อน เพราะเคยสัมภาษณ์คนเข้าทำงานมา น้อยคนที่จะตอบเรื่อง index นี่ได้ถูกใจผม

1. หลักการเลือกว่าควรใช้ index หรือ full scan นั้น อยู่ที่ selectivity ครับ ไม่ใช่ขนาดของข้อมูล โดย selectivity น้อย ๆ ควรใช้ index มาก ๆ ควรใช้ full scan และระหว่างน้อย ๆ กับมาก ๆ นั้น ก็จะมี grey area ที่เราจำเป็นต้องนำปัจจัยอื่นมาใช้ในการตัดสินใจเพิ่มเติม นอกเหนือจากตัว selectivity เอง ช่วง grey area นี้ก็อาจประมาณ 5 – 20 % แต่ก็ยืดหยุ่นได้อีก อาจต้องลองรันให้รู้ดำรู้แดงถึงจะตัดสินใจได้ก็มี

2. การ cache ข้อมูลไว้ใน memory ไม่ค่อยเกิดประโยชน์กับ full table scan ไม่ว่า table นั้นจะเล็กหรือใหญ่ เนื่องจาก block ที่อ่านโดย full scan นั้นจะถูกวางไว้ด้าน LRU least recently used ทันที พร้อมจะถูกถีบกลับลง Hard disk ในเวลาไม่นาน ยกเว้นคุณกำหนดคุณสมบัติของ table นั้นให้ทำการ cache เวลา full scan ด้วย ซึ่งไม่ใช่ค่า default ซึ่งเรื่องของ cache ก็เป็นอีกปัจจัยหนึ่งในการพิจารณาช่วง grey area กล่าวคือดูว่าข้อมูลที่เราสนใจนั้นมัน hot ขนาดไหน ถ้าใคร ๆ ก็สนใจข้อมูลชุดนี้มันก็มีแนวโน้มจะอยู่ใน cach เราก็จะ prefer index scan แม้ว่า selectivity จะค่อนข้างสูงก็ตาม

3.ปัจจัยพิเศษสุดท้ายที่ผมนึกออก คือเรื่องของ clustering factor ครับ อธิบายความหมายได้ว่าคือการจัดเก็บข้อมูลในตาราง เรียงลำดับตามการเรียงตัวของค่าใน indexed คอลัมน์ มากน้อยขนาดไหน หรือแนวโน้มว่าสำหรับค่าใด ๆ ใน index ที่อยู่ติด ๆ กัน ข้อมูลจริงในตารางมันอยู่ติดกันใน block เดียวกันด้วยหรือเปล่า ถ้ามันอยู่ติดกันมันก็ทำให้เราอ่าน table block น้อยลงต่อค่าใน index 1 ค่า ซึ่งทำให้เรา prefer index scan เช่นเดียวกัน แต่ถ้ามันกระจายตัวมาก เราอ่านข้อมูลแค่ 10,000 rows จาก table 1,000,000 rows ซึ่งเก็บอยู่ใน block 10,000 blocks เราอาจต้องเข้าอ่านข้อมูลทั้ง 10,000 blocks (100%) เลยก็ได้ ถ้าแต่ละ row ที่ต้องการมันดันอยู่คนละ block หมดเลยกรณีแบบนี้ full scan ไปเลยก็เร็วกว่าเห็น ๆ

ก็ขอยกตัวอย่างเพียงคร่าว ๆ เผื่อใครที่สนใจจะได้ไปศึกษาข้อมูลเพิ่มเติมได้ (แต่อย่าไปศึกษาจากตำราพี่ดอนก็แล้วกัน อิอิ) เพราะความจริงแล้วหลักการเลือกมันก็ไม่ได้มีเพียง 3 ข้อนี้เหมือนกัน 3 ข้อนี้แค่เป็นประเด็นที่เจอบ่อย ๆ ยังมีกรณีที่ table มัน almost empty หรือ column ที่ต้องการอยู่บน index ทั้งหมด ซึ่งทำให้การใช้ index full scan หรือ index fast full scan ดีกว่า table full scan และ/หรือกรณีอื่น ๆ อีกมากมาย จาระไนไม่หมด

ถ้าจะให้แนะนำหนังสือ ผมแนะนำหนังสือ sql tuning ของ dan tow ดีกว่าครับ เนื้อหาน่าจะถูกต้องกว่าแม้ว่าบางส่วนไม่ได้ update ตาม features ใหม่ของ oracle ก็ตาม

สำหรับวันนี้ สวัสดีครับ

Older Posts »

หมวดหมู่