ครั้งที่แล้ว ผมพูดถึงการ 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 (24×60x60) เท่านั้น แทนที่จะถูกเรียก 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 ดูครับ