ไม่ได้เข้ามาเขียน 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 เป็นต้น

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

หัวข้อในวันนี้จุดประกายความคิดมาจาก 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 เลย เอาไว้ต่อคราวหน้านะครับ (ถ้าไม่ลืมเสียก่อน)

หัวข้อในวันนี้ได้รับการอนุเคราะห์จากคำถามของคุณ 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!

หัวข้อในวันนี้มีที่มาจากกระทู้หนึ่งใน 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 นี้และเราจะได้นำคดีนี้กลับมาพิจารณาต่อไป

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

เข้าไปอ่าน 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 ก็ตาม

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

ต่อจากตัวอย่างที่แล้ว ยังสนุกไม่เลิก รู้สึกว่า logic ของตัวอย่างที่แล้วมันเข้าใจยากเกินไปหน่อย เลยพยายามหา logic อื่น ผลลัพธ์คือ query นี้ครับ

select d1, d2
  , trunc(((d2-d1)+1)/7)*2
  + case
      when to_char(d1,’d') = ’1′ and to_char(d2,’d') = ’7′ then 0
      when to_char(d1,’d') = ’1′ then 1
      when to_char(d2,’d') = ’7′ then 1
      when to_char(d2,’d') < to_char(d1,’d') then 2
      else 0
    end cnt
from weekend_count

สำหรับ query นี้มาจากการสังเกตตาราง

		1	2	3	4	5	6	7
		อา	จ	อ	พ	พฤ	ศ	ส
1	อา	1	1	1	1	1	1	0
2	จ	2	0	0	0	0	0	1
3	อ	2	2	0	0	0	0	1
4	พ	2	2	2	0	0	0	1
5	พฤ	2	2	2	2	0	0	1
6	ศ	2	2	2	2	2	0	1
7	ส	2	2	2	2	2	2	1

แนวนอนเป็นวันที่เริ่มต้นว่าตรงกับวันอะไร ส่วนแนวตั้งเป็นวันที่สุดท้าย
ส่วนค่าแต่ละค่ามาจากการนับ เช่น จากวันอาทิตย์ ถึง วันศุกร์ มีวันหยุด 1 วัน
จากวันอังคาร ถึงวันจันทร์ จะมีวันหยุด 2 วัน

ซึ่งผมสรุปได้ เป็นกรณีดังนี้
1.วันแรกเป็นวันอาทิตย์ และวันสุดท้ายเป็นวันเสาร์ ครบสัปดาห์พอดี ไม่ต้องบวกเพิ่ม
2.วันแรกเป็นวันอาทิตย์ หรือวันสุดท้ายเป็นวันเสาร์ มีวันหยุด 1 วัน
3.วันสุดท้ายเป็นวันในสัปดาห์ที่อยู่ก่อนวันแรก แสดงว่าผ่านเสาร์อาทิตย์มาหนึ่งรอบ บวกเพิ่มอีก 2
4.นอกจากกรณีข้างต้น แสดงว่าไม่มีวันหยุดเลย

ซึ่งสามารถเขียนเป็น sql โดยใช้ case expression ได้ดังตัวอย่าง

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

SQL is fun!!!!

ช่วงนี้ไม่ค่อยมีเวลามาเขียนหัวข้อใหม่ ๆ เท่าไหร่ ต้องขออภัยคนอ่านเป็นอย่างยิ่งครับ

หัวข้อวันนี้บังเอิญไปเจอคำถามในเว็บ narisa ที่น่าสนใจข้อหนึ่ง ต้องการนับวันหยุดที่อยู่ภายในช่วงวันที่ที่กำหนด โดยเจ้าของกระทู้สนใจนับเฉพาะเสาร์-อาทิตย์เท่านั้น

ในกรณีทั่ว ๆ ไปแล้ว หากโจทย์ต้องการนับวันหยุด ผมจะสร้างตารางเก็บวันหยุดทั้งหมดขึ้นมา แล้วก็ใช้ count ธรรมดา ก็เพียงพอ แถมรองรับกรณีวันหยุดตามประเพณีต่าง ๆ ได้ด้วย แต่อาจต้องการการ maintenance ตารางในแต่ละปีบ้าง

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

create table weekend_count
( d1  date
, d2  date
) ;
select d1, d2
  , greatest((next_day(d1,7)-d1)-5,0)
  + ((next_day(d2,7)-7)-next_day(d1,7))/7*2
  + least ((d2+1) – (next_day(d2,7)-7),2)
from weekend_count
/

หลักการคือ หาวันเสาร์แรก และ เสาร์สุดท้ายในช่วงมาโดยใช้ฟังก์ชั่น next_day(d1,7) และ next_day(d2,7)-7

นับ weekend ช่วงที่หนึ่งคือ ตั้งแต่วันแรกจนถึงวันเสาร์แรก จะมีเสาร์อาทิตย์เท่ากับจำนวนวันที่เกิน 5 วัน
นับ weekend ช่วงที่สองคือ ตั้งแต่วันเสาร์แรกจนถึงวันเสาร์สุดท้าย จะมีเสาร์อาทิตย์เท่ากับจำนวนวันหารเจ็ดคูณสอง
นับ weekend ช่วงที่สามคือ ตั้งแต่วันเสาร์สุดท้ายจนถึงวันสุดท้าย จะมีเสาร์อาทิตย์เท่ากับจำนวนวัน แต่ไม่เกินสอง

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

ก่อนจบหัวข้อ ขอกลับมาที่เฉลยที่อยู่ในเว็บนั้นอีกนิด จริง ๆ แล้วเจ้าของกระทู้ต้องการคำนวณช่วงวันที่ที่ user กรอกเข้ามาผ่าน forms ดังนั้นหากคำนวณด้วย pl/sql ล้วน ๆ น่าจะเหมาะสมกว่า การใช้ sql ที่ผมแสดงให้ดู แต่อาจสามารถปรับเอา logic ไปประยุกต์ใช้ได้ ซึ่งจะตัดเรื่องการวนลูปออกไป เหลือแต่การคำนวณล้วน ๆ ซึ่งเร็วกว่า

การใช้ sql แบบนี้นั้น จะเหมาะในกรณีที่เราเก็บช่วงวันที่อยู่ในตาราง และมีจำนวนหลาย ๆ ช่วง ต้องการ select และคำนวณไปพร้อม ๆ กันเลย ซึ่งการใช้ built-in function ก็จะเร็วกว่า การเขียน user-defined function ตามที่เคยแสดงให้ดูในครั้งก่อนแล้ว

สุขสันต์ปีใหม่นะครับ

ขอพักแนว technical ว่าด้วยเรื่อง performance tuning มาคุย(บ่น) เรื่องงานเอกสารเพื่อประกอบกระบวนการ Software Development Life Cycle ที่มักเรียกกันสั้น ๆ ว่า Program Spec. นี้สักหน่อย

เรื่องของเรื่องมีอยู่ว่า ที่บริษัททำการทบทวนเอกสารที่จำเป็นต่อการพัฒนาระบบงานครั้งใหญ่ มีการประชุมซึ่งผมดันต้องมาเป็นหนึ่งในสมาชิกที่เข้าประชุมด้วย ผมเอง ขอสารภาพว่า เป็นคนที่ขี้เกียจทำงานเอกสารอย่างมากมาย จึงเสนอให้ยกเลิกเจ้า Program Spec. ตัวนี้เสียที เพราะไม่เคยสร้างประโยชน์ที่แท้จริงอะไรเลย (ยกเว้นอาจเพิ่ม man-days เพื่อนำไปเรียกเก็บเงินจากลูกค้าได้) ผลก็คือผมเป็นเสียงเดียวที่เห็นด้วยกับข้อเสนอนี้ (ฮา) ดังนั้น โดยหลักประชาธิปไตยที่ไม่ฟังเหตุผลของเสียงข้างน้อย ผมก็ต้องก้มหน้าก้มตาทำเอกสารไร้ค่าชิ้นนี้ต่อไป

เหตุผลของเสียงข้างมาก คือ เอกสารชิ้นนี้มีความจำเป็น เพื่อใช้ในการติดต่อสื่อสารกันระหว่าง System Analyst (SA) หรือคนออกแบบโปรแกรม กับ Programmer (PG) หรือคนเขียนโปรแกรม เมื่อมีปัญหาในตัวโปรแกรมที่เขียนขึ้น ทั้งสองฝ่ายจะได้ไม่ต้องเถียงกัน ยึดตัว program spec นี้เป็นหลัก และอีกเหตุผลสำคัญก็คือ บริษัทไหน ๆ เขาก็เขียนกันทั้งนั้น โดยเฉพาะบริษัทฝรั่งใหญ่ ๆ โต ๆ ทั้งหลาย เรียกว่า ต้องมีไม่งั้นไม่ professional ว่างั้นเหอะ

เหตุผลของเสียงข้างน้อย (ผมเอง) คือ เรื่อง pro ไม่ pro ขอพักไว้ก่อน แต่เท่าที่ทำงานมา ผมไม่เคยเห็น program spec ที่ไหนที่สามารถบรรลุวัตถุประสงค์ที่วางไว้ คือ ยุติข้อขัดแย้งระหว่าง SA กับ PG ได้ (ซึ่งเสียงใหญ่ในที่ประชุมบอกว่า program spec ที่ดี ๆ ใช้งานได้จริงก็มี แต่ผมไม่เคยเห็นเอง ก็อาจจะจริงครับ แต่ผมก็ทำงานในแผนกพี่นั่นแหละ อย่าลืมสิ ฮา)

จากประสบการณ์ของผม โปรเจ็คต์ที่จบได้สวย ๆ ไม่ว่าจะมี program spec หรือไม่ ก็ไม่มีข้อขัดแย้งระหว่าง SA กับ PG ขณะเดียวกัน โปรเจ็คต์ที่จบได้ห่วยหรือไม่มีทางจบแน่นอน ก็มักจะเกิดข้อขัดแย้งระหว่าง SA กับ PG ขึ้นแล้วสุดท้ายก็มาลงที่ Program Spec ว่า SA ไม่ยอมทำ หรือทำไม่ดี เขียนไม่ละเอียด (โอ้ พระเจ้า จะเอาละเอียดขนาดนั้น ตูเขียนโปรแกรมให้เองเลยดีกว่ามั้ง)

ประเด็นของผมก็คือ ข้อขัดแย้งระหว่าง SA กับ PG มีความสัมพันธ์โดยตรงกับความสำเร็จล้มเหลวของโปรเจ็คต์ เห็นได้ชัดเจน (แต่ประมาณ ไก่เกิดก่อนไข่ หรือไข่เกิดก่อนไก่ บอกไม่ได้ว่าอะไรเกิดก่อน) แต่ program spec ไม่ได้เกี่ยวอะไรด้วย เธอเป็นเพียงแพะที่น่าสงสารเท่านั้น

หาก SA กับ PG สามารถสื่อสารกันด้วยความเข้าใจ และมีพื้นฐานอยู่บนความเชื่อใจและวางใจซึ่งกันและกัน ข้อขัดแย้งย่อมไม่เกิดขึ้น ผมมี keyword อยู่สองคำ คือความเข้าใจ และความเชื่อใจ หากคนสองคนคุยกันภาษาเดียวกัน (สื่อสารสองทาง) แต่ยังไม่เข้าใจกัน แล้วคิดว่า คนหนึ่งไม่พูดไม่จา เขียนหนังสือส่งให้อีกคนอ่าน (สื่อสารทางเดียว) คนอ่านเค้าจะอ่านเข้าใจหรือครับ program spec ไม่ช่วยเรื่องความเข้าใจแน่นอน จริงอยู่ที่ว่าบางครั้งคำพูดอาจไม่เพียงพอสำหรับการสื่อสาร ต้องอาศัยภาษามือ ภาพประกอบช่วย แต่ไม่ได้หมายความว่านั่นต้องเป็นprogram spec เขียนขึ้นกระดานก็ได้ ชี้ ๆ เอาบน prototype ก็ได้

สำหรับความเชื่อใจยิ่งแล้วใหญ่ เพราะ program spec ลึก ๆ แล้วมีที่มาจากความไม่เชื่อใจกัน กลัวว่าพอมีปัญหา อีกฝ่ายจะโยนความผิดมาให้ตัวเอง หากเป็นการจ้างงานระหว่างสองบริษัท ผมเห็นด้วยเอกสารเป็นลายลักษณ์อักษร เป็นสิ่งจำเป็น แต่หากสิ่งนี้มีความจำเป็นระหว่างคนสองคนในที่ทำงานเดียวกัน ผมคิดว่ามีปัญหาความเป็นทีมเวอร์คในที่ทำงานนั้นแล้วครับ

มีอีกเรื่องที่ผมอยากเอามาวิเคราะห์ คือความละเอียดของ Program Spec หลาย ๆ ครั้งเมื่อปัญหาเกิดขึ้น ความละเอียดของ Program Spec ถูก request ถึงขั้นต้องเอา code บางส่วนมาใส่ไว้ให้เลย PG จะได้ copy ไปแปะ โปรแกรมจะได้ทำงานได้ตามที่ SA ต้องการ ให้เขียนสดแค่บางส่วนซึ่งทดสอบไม่ได้ แล้วหวังจะให้ code นั้นไม่มี bug ผมว่าคนที่พูดแบบนี้ ไม่เคยเขียนโปรแกรมครับ code ที่ปลอด bug ต้องผ่านการทดสอบบนข้อมูลเสมือนจริงจนครบทุกกรณีที่เป็นไปได้ สุดยอด SA ขนาดไหน ก็ไม่ไหวหรอกครับ สุดท้าย PG ก็ต้องนำ code นั้นไปปรับปรุงแก้ไขให้ถูกต้องอีกครั้งอยู่ดี แล้วอะไรจะเกิดขึ้นครับ มีทั้ง code ที่ใช้งานจริง กับ code ที่อยู่ใน spec แบบนี้ เขาเรียก denormalize นะครับ เกิด update anomalies แน่นอน แถมไม่มีประโยชน์ในแง่ query performance ด้วย ทำไปทำไมครับ

เพิ่งนึกได้ เค้าบอกผมอีกด้วยว่า Program Spec มีประโยชน์อีกในแง่เวลาต้องกลับมาแก้ไขโปรแกรม โดยคนอื่นที่ไม่ได้เป็นคนเขียน จะได้เข้าใจการทำงานของโปรแกรม เอ่อ แน่ใจหรือครับ เอ่อ แล้วพอแก้ไขโปรแกรมเสร็จ พี่แก้ไขใน spec ด้วยหรือเปล่าครับ แล้วคนที่มาแก้ไขเป็นคนที่สาม เขาจะรู้หรือครับว่าพี่แก้ไขอะไรไป นี่ก็ denormalize เหมือนกันครับ คำอธิบายการทำงานของโปรแกรม ควรจะอยู่ในตัวโปรแกรมเอง ไม่ว่าจะโดยการ comment หรือ refactoring ก็ว่ากันไป แก้เสร็จปุ๊บ ก็ใส่ comment ไปเลย ว่าใครแก้ แก้เมื่อไหร่ แก้ทำไม โปรแกรมที่ไม่มี comment ต่อให้มี program spec ก็ไม่มีประโยชน์หรอกครับ ยังไงก็ต้องนั่งไล่ logic ของคนเขียนคนแรกอยู่ดี ว่าเขาคิดอะไรอยู่ตอนเขียน

สุดท้ายก่อนจบ blog นี้ ผมเสนอให้ใช้เป็น job assignment แทน program spec ครับ เอาไว้กรณี PG หรือ SA ขี้ลืม จะได้ติดตามงานกันได้ รายละเอียดงานก็ใส่เท่าที่จำเป็น ทำเสร็จก็ปิด job ถ้าต้องมีการแก้ไขโปรแกรมตัวเดิมอีกครั้ง ก็ออก job ใหม่ ไม่ต้องไป refer ตัวเก่า ปิดแล้วก็ปิดเลย ไม่ต้องตามไป update ให้เสียเวลา แต่ผมคงต้องรอจนกว่าจะมีบริษัทเป็นของตัวเองมั้ง กว่าจะได้ใช้ concept นี้ ในการทำงานจริง เฮ้อออออออ ยาว ๆ สักที

ครั้งที่แล้ว ผมพูดถึงการ tune function และยกตัวอย่างให้เห็น ซึ่งหากกล่าวโดยสรุปคือ แนะนำให้พยายามใช้ built-in functions ใน sql statement แทน stored functions ทุกครั้ง หากเป็นไปได้

อย่างไรก็ดี การใช้ stored functions ก็มีข้อดีอยู่ ซึ่งผมลืมที่จะพูดถึงไป นั่นคือ หาก business requirement เปลี่ยน แทนที่เราจะต้องตามแก้ในทุก sql statement เหมือนการใช้ built-in เราก็เพียงแก้ source code ของ function แค่นั้นก็จะมีผลไปทุก statement ที่เรียกใช้

หากเรามีการใช้ built-in function ในการคำนวณที่เหมือน ๆ กันในหลาย ๆ statement และการคำนวณนั้นอาจมีการเปลี่ยนแปลง ในกรณีนี้ผมอยากแนะนำให้เราเขียน view ขึ้นมา และเก็บ definition ของ column ซึ่งได้จากการคำนวณนั้นไว้ใน view ครับซึ่งหากมีการเปลี่ยนแปลง เราก็แก้ไขที่เดียวเช่นเดียวกัน

อีกประเด็นหนึ่ง คือเราคงไม่สามารถใช้ built-in function แทน user defined function นี้ได้เสมอไป เช่น requirement นั้นยุ่งยาก หรือพิเศษพิสดาร ไม่เหมือนคนทั่วไป ถ้าเป็นเช่นนั้น สิ่งที่เราจะทำได้ดีที่สุดในการ tune คือการพยายามลดจำนวนครั้งของการ call ลงให้มากที่สุดแทนครับ ตัวอย่างเช่น sql statement ในครั้งที่แล้ว

select get_attime2(starttime), count(*) cnt
from tt1
group by get_attime2(starttime)
/

ถ้าเราเรียกใช้ฟังก์ชั่นที่ group by clause ฟังก์ชั่นนี้ก็จะถูกเรียกใช้หนึ่งครั้งต่อหนึ่งแถวของข้อมูล วิธีหนึ่งที่จะช่วยลดได้ก็คือ เขียนแบบนี้ครับ

select time_str, sum(cnt) total
from (
  select get_attime2(starttime) time_str, count(*) cnt
  from tt1
  group by starttime
)
group by time_str
/

โดยการเขียนแบบนี้ get_attime2 จะถูกเรียกเท่าจำนวนค่าที่ไม่ซ้ำทั้งหมดของ starttime หรือประมาณ 86,400 (24x60x60) เท่านั้น แทนที่จะถูกเรียก 500,000 ครั้งเท่าจำนวนแถวทั้งหมดของข้อมูล เวลาที่ใช้ตามที่ผมทดสอบดู ลงลงจาก 3.45 เหลือเพียง 0.9 วินาทีเท่านั้น

ในเรื่องของการลดจำนวนครั้งที่ call นี้ สิ่งสำคัญที่เราต้องทำความเข้าใจคือ ฟังก์ชั่นถูกเรียกใช้เมื่อไรบ้าง เมื่อเขียน statement ในลักษณะต่าง ๆ กัน ตัวอย่างเช่น

create or replace package pkg_test
is
  g_cnt    number ;
  function f_cnt
  ( p_num  number
  ) return number ;
end ;
/
create or replace package body pkg_test
is
  function f_cnt
  ( p_num   number
  ) return number is
  begin
    g_cnt := g_cnt + 1 ;
    return p_num ;
  end ;
end ;
/

 
function pkg_test.f_cnt จะ return ค่า input ที่ให้ไป แต่จะนับจำนวนครั้งเมื่อถูกเรียกใช้แต่ละครั้ง และเก็บไว้ในตัวแปร pkg_test.g_cnt เราลองมาทดสอบกันดู ด้วย statement ดังนี้

drop table tt1 purge
/
create table tt1
as
select rownum id, mod(rownum, 100)+1 grp
from all_objects
where rownum <= 50000
/
exec pkg_test.g_cnt := 0
select pkg_test.f_cnt(id)
from tt1
where grp = 1
/
exec dbms_output.put_line ( pkg_test.g_cnt )

เขียนแบบนี้ function ถูกเรียก 500 ครั้ง หรือเท่ากับจำนวนแถวที่ return ออกมา

exec pkg_test.g_cnt := 0
select id
from tt1
where grp = pkg_test.f_cnt(1)
/
exec dbms_output.put_line ( pkg_test.g_cnt )

เขียนแบบนี้ function ถูกเรียก 50,000 ครั้ง หรือเท่ากับจำนวนแถวที่อยู่ในตารางทั้งหมด หมายถึง oracle จะเรียกใช้ฟังก์ชั่นทุกครั้ง เพื่อทดสอบว่า row ใดเป็นไปตาม condition ที่เราระบุ อย่างไรก็ดีหากเราสร้าง index ให้กับ column grp การเรียกใช้ function จะเปลี่ยนไป โดยหาค่าผลลัพธ์ของฟังก์ชั่นออกมาก่อน แล้วนำค่านั้นไปใช้ในการ access index การเรียกใช้จะเหลือเพียง 2 ครั้งเท่านั้น (แต่ทำไมไม่เป็น 1 ครั้ง เท่านั้นผมก็ไม่ทราบเหมือนกัน เอาเป็นว่า 1 ครั้ง กับ 2 ครั้ง ผลต่างมันไม่มากนักก็ถือว่าโอเคครับ)

create index tt1_idx
on tt1(grp)
/
exec pkg_test.g_cnt := 0
select id
from tt1
where grp = pkg_test.f_cnt(1)
/
exec dbms_output.put_line ( pkg_test.g_cnt )

เอาไว้คราวหน้า เราจะมาทดสอบแบบนี้กับ deterministic function ดูครับ

ตามที่สัญญาไว้ในครั้งที่แล้ว ว่าจะยกตัวอย่างที่เคย tuning จริงมา post วันนี้ขอเริ่มด้วยกรณีการเรียกใช้ stored function ที่เขียนขึ้นเอง โดยไม่จำเป็น

ตัวอย่างนี้ เพิ่งได้มาสด ๆ ร้อน ๆ จากน้องในที่ทำงานเดียวกัน แต่อยู่คนละ project ซึ่ง project ของเขาเป็นโรงงานแห่งหนึ่ง ที่จริงเขาก็ยังไม่ได้ขอให้ tune query ให้ แค่ขอคำปรึกษาเรื่องการใช้ analytic function เท่านั้น แต่พอผมเห็น statement ของเขาแล้วมันอดไม่ได้ ก็เลยแนะนำเรื่องการ tune แถมให้ด้วยเลย

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

CREATE OR REPLACE FUNCTION GET_ATTIME1
( start_time IN NUMBER
) RETURN varchar2 IS
  rs_time VARCHAR2(6) ;
BEGIN
  if start_time = 80000 then
    rs_time:= '08:00';
  end if;
  if start_time >= 80001 and start_time <= 90000 then
    rs_time:= '09:00';
  end if;
  ...
  if (start_time >= 230001 and start_time <= 240000)
    or start_time = 0 then
    rs_time:= 't0:00';
  end if ;
  if start_time >= 1 and start_time <= 10000 then
    rs_time:= 't01:00';
  end if ;
  ...
  if start_time >= 70001 and start_time <  80000 then
    rs_time:= 't08:00';
  end if ;
  RETURN rs_time;
end ;
/

จากนั้นก็นำไปเรียกใช้ในลักษณะดังนี้

select get_attime1(starttime), count(*) cnt
from tt1
group by get_attime1(starttime) ;

จะเห็นว่าหนึ่งแถวใน tt1 ก็จะมีการเรียกใช้ function หนึ่งครั้ง ดู ๆ ไป ก็ไม่ได้ซับซ้อนอะไรนัก แต่จะมีผลต่อ performance หรือไม่ ขึ้นกับจำนวนแถวทั้งหมดใน tt1 ผมได้ลองทดสอบโดยสร้างข้อมูลขึ้นมาแบบสุ่ม จำนวน 500,000 แถว เพื่อจะทดลองผลของการ tuning (ดู code ทั้งหมด ได้ ที่นี่ ครับ )

ขั้นแรก พิจารณาในตัว code ที่น้องเขาเขียน จะเห็นว่า ใช้ if ย่อย ๆ หลาย if แทนที่จะใช้ elsif จุดนี้จะช่วยตัดทอนการทำงานที่ไม่จำเป็นออกได้ เนื่องจากถ้าใช้ if เราต้องทำการเปรียบเทียบทุกกรณีที่ระบุ แต่ถ้าเอา elsif เข้ามาช่วย เมื่อเจอกรณีที่ให้ค่าจริงปุ๊บ ก็หยุดปั๊บ ไม่ทำงานส่วนที่เหลือทันที (ดูที่ code get_attime2 ) ผลลัพธ์ที่ทดลองบนเครื่องของผม เวลาลดลงจาก 3.75 วินาที เหลือประมาณ 3.3 วินาที

อย่างไรก็ดี วิธีการที่ผมใช้ในการ tune จริง ๆ คือการเปลี่ยนมาใช้ buit-in function ล้วน ๆ ครับ จาก code ข้างบน ผมเขียนโดยใช้ sql function ได้ดังนี้

select (case
          when ( starttime < 80000 or starttime >= 230001)
            then 't'
          else null
        end)
       ||to_char(mod(ceil( starttime/10000 ),24),'fm09')||':00'
  , count(*) cnt
from tt1
group by (case
          when ( starttime < 80000 or starttime >= 230001)
            then 't'
          else null
        end)
       ||to_char(mod(ceil( starttime/10000 ),24),'fm09')||':00'  ;

ดูยุ่งยากกว่าเยอะนะครับ ใครเห็น code นี้คงมึนไปเหมือนกันว่าคนเขียนอยากทำอะไร แต่ผลลัพธ์ของ query นี้ได้เหมือนเดิมเป๊ะ ในเวลาเพียงประมาณ 0.75 วินาทีครับ

เวลาที่ลดลงไปเป็นผลมาจากการที่เราลดการ switching ระหว่าง sql engine กับ pl/sql engine ครับ ถ้าเทียบเวลาที่ใช้ในการคำนวณด้วยฟังก์ชั่นที่ผมเขียนกับการใช้ if แล้ว การคำนวณผมช้ากว่าด้วยซ้ำ ดูได้จาก function get_attime3 ซึ่งผมเขียนโดยเลียนแบบ built-in function ที่ผมใช้ ผลที่ได้ใช้เวลาถึง 5.5 วินาทีด้วยซ้ำ ดังนั้นหากฟังก์ชั่นนี้ถูกเรียกใช้ใน pl/sql เป็นหลักแทนที่จะใช้ใน sql statement การใช้ get_attime2 ก็จะให้ผลลัพธ์ที่ดีกว่า

หนังสือหลาย ๆ เล่มพูดถึงการ tuning function ด้วยการกำหนดให้ function นั้น เป็น deterministic function ลองดูว่าผลลัพธ์เป็นอย่างไร ผมใช้ code เดียวกับ get_attime2 มาสร้างเป็น deterministic function get_attime4 ผลลัพธ์ที่ได้ผมมองไม่เห็นความแตกต่างเลยครับ เรื่อง deterministic function นี้ผมว่าผมเอามาเขียนเป็นอีกหนึ่งหัวข้อได้เลย เพราะฉะนั้นอดใจรอหน่อยแล้วกันนะครับ

ถ้าคุณคิดว่า tune แค่นี้ก็โอเคแล้ว ยังครับ ยังมีอีกหนึ่ง trick เอาผลลัพธ์ของฟังก์ชั่นมาเก็บไว้ในตารางก่อน แล้ว join เอา เนื่องจาก input ของฟังก์ชั่นนี้มันมีจำนวนจำกัด แค่เวลาใน 24 ชั่วโมงเท่านั้น ตารางที่ใช้ในการเก็บผลลัพธ์ของเราก็ไม่ได้ใหญ่โตอะไร (ตาราง tt2) ผลลัพธ์จากการ join ที่หลายคนกลัวกันนักหนา เหลือเพียง 0.5 วินาทีครับ อย่างไรก็ดี trick นี้ เปลี่ยนจาก cpu load มาเป็น I/O load ดังนั้นก็อยู่ที่ server ของคุณว่า bottleneck อยู่ที่ใด ต้องทำการเปรียบเทียบเองครับ แต่ก็ถือเป็นอีกหนึ่งทางเลือกได้

น่าสนใจไหมครับ ถ้าเห็นว่าน่าสนใจ comment ติชมกันหน่อยนะครับ แล้วคราวหน้าผมจะนำตัวอย่างอื่น ๆ มาให้ดูอีกครับ

ขอบคุณครับ