Sunday, December 27, 2009

Oracle Heterogeneous Service (HS) การเชื่อมต่อ Oracle กับฐานข้อมูลตัวอื่น ๆ เช่น Microsoft SQL Server หรือ AS400 (ตอนที่ 1)

การเชื่อมต่อระหว่างระบบฐานข้อมูลต่างยี่ห้อกันบางทีก็ทำให้ปวดหัวและเสียเวลาได้เหมือนกัน การใช้ Heterogeneous Service อาจจะช่วยคุณได้เหมือนกับอธิชาติดังเรื่องข้างล่างนี้ครับ

ณัฐพลถูกมอบหมายให้เขียนรายงานเพื่อนำข้อมูลกรมธรรม์หลักที่ได้จากระบบประกันชีวิตหลักที่อยู่บนเครื่อง AS400 มาเปรียบเทียบกับเงินสินไหมที่บริษัทต้องจ่ายในแต่ละวัน ของแต่ละกรมธรรม์ซึ่งข้อมูลการเคลมนี้อยู่บนฐานข้อมูลที่เป็น Mircrosoft SQL Server ในขณะที่รายงานที่ออกนี้จะต้องนำมาจากฐานข้อมูล Oracle ที่ได้จากการเตรียมข้อมลจากฐานข้อมูล AS400 และ MSSQL ทั้งสอง เพื่อให้ระบบจัดการความสัมพันธ์ของลูกค้า (Customer Relation Ship Management -- CRM) สามารถนำไปใช้ได้ด้วย ณัฐพลจะทำอย่างไรเพื่อให้สามารถนำข้อมูลจากแหล่งทั้ง 3 เข้ามารวมกันได้อย่างไร้รอยต่อ และให้ทุกอย่างสามารถเป็นไปได้โดยอัตโนมัติ และเชื่อถือได้

ในฐานะโปรแกรมเมอร์ ณัฐพลคิดถึงการใช้โปรแกรมที่เขาเขียน Connect เข้ากับฐานทั้งสองตัว (AS400 และ SQLServer) แล้วนำมา Join กันในโปรแกรมของเขา แต่เมื่อลองทดสอบดูแล้วพบว่าโปรแกรมของเขานั้นช้าเกินกว่าที่จะสามารถใช้ได้จริงในทางปฏิบัติ เนื่องจากข้อมูลในฐานทั้งสองมีอยู่เป็นจำนวนมาก

ณัฐพลจึงขอให้ทีม AS400 Export ข้อมูลออกมาเป็นเท็กซ์ไฟล์ และให้ทีมงานที่ดูแลระบบสินไหม Export ข้อมูลออกมาเป็นเท็กซ์ไฟล์ทุกวันเช่นกัน ส่วนตัวเขาเองในฐานะผู้รวบรวมข้อมูลให้กับระบบ CRM ซึ่งอยู่บนฐานข้อมูลจะทำหน้าที่ Import ข้อมูลเข้าระบบฐานข้อมูล Oracle ทุกวันเช่นกัน ต่อจากนั้นก็แล้วแต่ว่าณัฐพลจะใช้วิธีการใดในการ Join ข้อมูลจากทั้งสองฐานเข้าด้วยกัน

วิธีการดังกล่าวเป็นวิธีการหนึ่งที่พบได้อยู่ทั่วไป แต่ด้วยขั้นตอนการนำข้อมูลเข้า ๆ ออก ๆ จากระบบหนึ่ง ไปยังอีกระบบหนึ่ง อาจทำให้เกิดความไม่สะดวกในการทำงาน และจะต้องอาศัยทักษะในการนำเข้า-ออกข้อมูลจากระบบต่าง ๆ เป็นเท็กซ์ไฟล์
ณัฐพลหากไม่ได้รับความช่วยเหลือจากทีม AS400 หรือ Microsoft SQL Server จะต้องมีทักษะในระบบฐานข้อมูลทั้งสองด้วย แล้วมีวิธีการอื่นไหมที่มีประสิทธิภาพกว่านี้

อธิชาติเป็นโปรแกรมเมอร์ที่มีความรู้ในระบบฐานข้อมูล Oracle เป็นอย่างดี และทราบว่า Oracle มีคุณสมบัติที่สามารถจะติดต่อกับฐานข้อมูลของบริษัทอื่น ๆ โดยผ่าน Heterogeneous Service เขาเริ่มจากการ Install โปรแกรม Client ของ AS400 ลงบนเครื่อง Oracle10gR2 Database Server ซึ่งมี OS เป็น Windows Server2003 และสร้าง ODBC Datasource เพื่อที่จะให้เครื่อง Database Server นี้สามารถมองเห็นทั้ง MSSQL และ AS400 ได้ จากนั้นเขาก็เข้าไปคอนฟิค Oracle Listener, และสร้างคอนเนคชั่นคอนฟิคกูเรชั่นในไฟล์ TNSNames.ora จากนั้นจึงได้เข้าไปสร้าง Database Link ในฐานข้อมูล Oracle เพียงเท่านี้เขาก็สามารถที่จะล็อกอินเข้าในฐานข้อมูล Oracle ในขณะเดียวกันก็สามารถที่จะคิวรีข้อมูลจาก AS400 หรือ MSSQL ได้โดยตรงโดยไม่ต้องล็อกเอาท์ออกมา เพียงเท่านี้อธิชาติก็สามารถที่จะคิวรีข้อมูลจากตารางใน AS400 หรือ MSSQL ได้สด ๆ ราวกับอยู่ในฐานข้อมูลเดียวกัน หลังจากไตร่ตรองแล้วเขาเลือกที่จะใช้วิธีการสร้างตารางที่เป็นก๊อปปี้ของตารางต้นทางแล้ว Import ข้อมูลเข้าไปทุกคืน ด้วยวิธีการนี้เขาสามารถควบคุมประสิทธิภาพของคิวรีในช่วงกลางวันที่มีผู้ใช้งานจำนวนมากได้ดีกว่าการคิวรีจากฐานข้อมูล AS400 หรือ MSSQL แบบ Online โดยหลังจากสร้างตารางเปล่าที่เหมือนต้นฉบับเอาไว้บน Oracle แล้ว อธิชาติจะเขียนคำสั่งเพื่อไปดึงข้อมูลมาโดยมีลักษณะคล้าย ๆ คำสั่งข้างล่างนี้
SQL> insert into ORACLE_TABLES select * from AS400_TABLES@AS400_DB_LINK;
เขาเขียนคำสั่งเหล่านี้เป็นสคริปต์ไฟล์เอาไว้เพื่อให้ข้อมูลอัพเดททุก ๆ คืน เพื่อให้แน่ใจว่าในตอนกลางวันระบบ CRM และรายงานของเขาจะมีข้อมูลที่ทันสมัยเอาไว้ใช้ทุกวัน

เรื่องของ Heterogeneous Service ยังมีต่อ ครั้งหน้าเราจะมาพูดถึงวิธีการทำกันนะครับ ขอขอบคุณที่ติดตามครับ

บทความที่เกี่ยวเนื่องกัน
Oracle Heterogeneous Service (HS) การเชื่อมต่อ Oracle กับฐานข้อมูลตัวอื่น ๆ เช่น Microsoft SQL Server หรือ AS400 (ตอนที่ 2)

Sunday, November 22, 2009

Regular Expression มหัศจรรย์แห่งการจัดการ Text (ตอนที่ 2 จบ)

ข้อเขียนที่แล้วได้แนะนำการใช้งานและประโยชน์ของ Regular Expression สำหรับตอนนี้เราจะมาพูดถึงไวยกรณ์ของ Regular Expression บน Oracle กันนะครับ


source_string คือ Text ที่เราต้องการจัดการ ส่วนมากจะเป็นชื่อคอลัมน์ในตาราง และเป็นคอลัมน์ที่มีประเภทข้อมูลเป็น Char, Varchar2, Nchar, Nvarchar2, Clob, NClob

pattern คือ รูปแบบทีเป็นไวยกรณ์ของ Regular Expression ที่เอาไว้จัดการ Text ใน source_string

match_parameter คือตัวที่กำหนดพฤติกรรมในการจับคู่ตาม pattern ที่กำหนด ตัวอย่างเช่น
'i' หมายถึงการจับคู่ตาม pattern ไม่สนใจเรื่องตัวอักษรใหญ่หรือเล็ก (Case Insensitive)
'c' หมายถึงการจับคู่ตาม pattern ที่จะต้องตรงกันตามอักษรตัวเล็ก-ใหญ่ (Case Sensitive)
'n' ทำให้สัญญลักษณ์จุด (Period)ใน Regular Expression ซึ่งโดยปกติหมายถึงตัวอักษรใด ๆ ก็ได้หนึ่งตัว กลายเป็นหมายถึงตัวขึ้นบรรทัดใหม่ (New Line Character)
'm' ทำให้เครื่องหมาย ^ และ $ เป็นจุดเริ่มต้นและจุดสิ้นสุดบรรทัดใน source_string ซึ่งหมายถึงทำให้มอง source_string เป็นหลาย ๆ บรรทัดได้ ถ้าไม่ได้กำหนดตัวนี้ source_string จะถูกมองเห็นเป็นบรรทัดเดียว และ ^ กับ $ จะหมายถึงเป็นจุดเริ่มต้นกับสิ้นสุดของ Text ทั้งหมดใน source_string

ถ้าเราระบุ match_parameter ที่ขัดแย้งกันเช่น 'ic' จะใช้ค่าตัวหลังสุดคือ 'c' ซึ่งหมายถึง Case Sensitive ตัวอักษรจะต้องเป็นตัวเล็ก-ใหญ่ตรงกัน หรือถ้าเราไม่ระบุ match_parameter เลย ค่าดีฟอลต์จะมีผลดังนี้
- การจับคู่จะเป็น Case Sensitive หรือไม่ขึ้นอยู่กับตัวแปร NLS_SORT
- จุด (Period) จะหมายถึงตัวใด ๆ หนึ่งตัว ไม่ได้แทนตัวขึ้นบรรทัดใหม่
- source_string จะถูกมองเป็นบรรทัดเดียว และเครื่องหมาย ^ และ $ จะหมายถึงจุดเริ่มต้นและสิ้นสุด Text ทั้งหมดใน source_string

ตัวอย่างที่ 1
คิวรีข้างล่างนี้จะแสดงชื่อและนามสกุลของพนักงาน ซึ่งเราต้องการคนที่มีชื่อประมาณว่า "สตีเฟ่น" ซึ่งภาษาอังกฤษอาจจะเขียนเป็น Steven หรือ Stephen ก็ได้(มีชื่อขึ้นต้นด้วย Ste และลงท้ายด้วย en และตรงกลางเป็น v หรือไม่ก็ ph)

SQL select first_name, last_name
from employees
where REGEXP_LIKE (first_name, '^Ste(v|ph)en$');

FIRST_NAME LAST_NAME
---------------------- ------------------------------------------
Steven King
Steven Markle
Stephen Stiles

เครื่องหมาย ^ คื่อให้เริ่มตรวจสอบ(ด้วย pattern)ตั้งแต่อักขระตัวแรกของ source_string โดยที่อักษรตัวแรกจะต้องเป็นตัว S, ตัวที่สองสามเป็น te ตัวถัดมาอาจจะเป็น v หรือ ph ก็ได้ และจบท้ายด้วย en เครื่องหมาย $ แสดงจุดสิ้นสุดของ Text ที่เราต้องการตรวจสอบ เมื่อใช้ en$ หมายถึงว่า Text ที่จะเข้าเงื่อนไขจะต้องลงท้ายด้วย en แต่ถ้าเราใส่แค่ en เฉย ๆ ก็หมาย ความว่าตัวอักษรตัวที่ห้า-หก หรือตัวที่หก-เจ็ด จะเป็น en (ขึ้นอยู่กับว่าจะเป็น Stev หรือ Steph)ส่วนหลังจากนั้นจะเป็นอะไรก็ได้

ตัวอย่างที่ 2
คิวรีข้างล่างนี้แสดงนามสกุลของพนักงาน ที่มีสระเหมือนกันติดกันสองตัว (คอลัมน์ last_name มีตัวอักษรที่เป็นสระ (a, e, i, o หรือ u) ซ้ำกันสองตัวที่อยู่ติดกันด้วย โดยไม่สนใจว่าตัวเล็ก หรือตัวใหญ่

SQL> select last_name
from employees
where REGEXP_LIKE (last_name,'([aeiou])\1','i');

LAST_NAME
-------------------------
Bloom
De Haan
Feeney
Gee
Greenberg
Greene
Khoo
Lee

ตัว Bracket "[]" ครอบตัวอักษรใด ๆ ที่เป็นไปได้ ในกรณีนี้คือตัวใด ๆ ระหว่าง a,e,i,o หรือ u ส่วนเครื่องหมาย Backslash "\" หมายถึงซ้ำตัวที่อยู่ในวงเล็บ "()" ข้างหน้า และตัว i ข้างหลังสุดคือการจับคู่ไม่สนใจว่าจะเป็นตัวใหญ่หรือเล็ก เราจะพบว่า LAST_NAME ที่ปรากฎทุก row จะมีสระซ้ำกันสองตัว

สรุปสัญญลักษณ์ต่าง ๆ ที่ใช้ใน Regular Expression (ส่วนที่เป็น Pattern)
\ (Backslash) เป็นเครื่องหมาย Backslash ตรงตัวหรือทำให้ตัวอักษรซึ่งเป็น Operator (เช่นเครื่องหมายคูณ, หาร, บวก และลบ) กลายเป็นตัวอักษรธรรมดาตัวหนึ่ง เช่น \* จะหมายถึง ตัวดอกจันหนึ่งตัว ไม่ใช่เครื่องหมายคูณเป็นต้น

* แทนการเกิดขึ้น 0 ครั้งขึ้นไป

+ แทนการเกิดขึ้น1 ครั้งเป็นต้นไป

? แทนการเกิดขึ้น 0 หรือ 1 ครั้ง

| ใช้เป็นตัวคั่นเพื่อแสดงตัวเลือก

^ แสดงว่าเป็นจุดเริ่มของข้อความ ถ้าใช้ร่วมกับ match_parameter 'm' จะใช้เป็นจุดเริ่มต้นของบรรทัดใด ๆ ใน source_string

$ แสดงว่าเป็นจุดสิ้นสุดของข้อความ ถ้าใช้ร่วมกับ match_parameter 'm' จะใช้เป็นจุดสิ้นสุดของบรรทัดใด ๆ ใน source_string

. แทนตัวอักษรใด ๆ หนึ่งตัวยกเว้น NULL และแทนตัวขึ้นบรรทัดใหม่ (New Line) ถ้า match_parameter เป็น 'n'

[ ] ใน Bracket นี้จะเป็นลิสต์ของตัวอักษร ซึ่งตัวใดตัวหนึ่งในลิสต์สามารถจับคู่ได้กับใน source_string ถ้าต้องการให้เป็นตรงกันข้ามคือต้องการ source_string ที่ไม่มีในลิสต์จะต้องใส่ ^ เข้าไปเช่นจากคิวรีข้างบนถ้าเปลี่ยน [aeiou] เป็น [^aeiou] จะได้ข้อมูล last_name ที่เป็นตรงกันข้ามคือแสดงทุกเรคคอร์ดใน employees ยกเว้น 8 เรคคอร์ดนี้

( ) เครื่องหมายกลุ่ม, สัญญลักษณ์ทั้งหลายในนี้จะถือเป็นหนึ่งตัว

{m} แทนการเกิดขึ้น m ครั้งเท่านั้น

{m,} แทนการเกิดขึ้น m ครั้งขึ้นไป

{m,n} แทนการเกิดขึ้น m ถึง n ครั้ง

\n แทนการเกิดขึ้นซ้ำครั้งที่ n ของตัว ( ) ที่อยู่ข้างหน้าตัวมัน เช่น ([aeiou])\1 หมายถึงการเกิดขึ้นซ้ำของตัว a, e,
i, o หรือ u ครั้งที่หนึ่ง เช่น aa, ee, ii, oo หรือ uu ตัว n สามารถเป็นได้จาก 1 ถึง 9

[. .] แสดงตัวอักษรหนึ่งตัว ซึ่งอาจจะประกอบด้วยอักขระมากกว่าหนึ่งตัวก็ได้ (เช่น [.ch.] ในภาษาสเปน) ใช้ในกรณีที่เราต้องการแทนตัวอักษรตัวใดๆ ในลำดับ เช่นในภาษาอังกฤษ จาก a ไปจนถึง c เราจะใช้ [a-c] แต่ในบางภาษาตัวอักษรแต่ละตัวอาจจะไม่ได้ประกอบด้วยอักขระเพียงตัวเดียวดังเช่น ch ในภาษาสเปน เราก็เลยต้องใช้ [..] ช่วยเช่นจาก a ถึง ch เราก็สามารถใช้ [a-[.ch.]] แทน

[: :] แสดงคลาสของตัวอักษร (เช่น [:alpha:]) โดยจับคู่กับตัวอักษรใด ๆ ในคลาสของตัวอักษรที่ระบุ
[:alnum:] ตัวอักษรและตัวเลขทุกตัว (alphanumeric)
[:alpha:] ตัวอักษรทุกตัว
[:digit:] ตัวเลขทุกตัว
[:blank:] ตัวช่องว่าง (Space) ทุกตัว
[:space:] ตัวช่องว่างที่ไม่สามารถพิมพ์ได้ (Nonprinting)
[:cntrl:] แสดงตัวอักษร Control ทุกตัว (ตัวอักษรที่พิมพ์ไม่ได้ - Nonprinting)
[:punct:] ตัวอักขระพิเศษทุกตัว
[:lower:] ตัวอักษรที่เป็นตัวเล็กทุกตัว
[:upper:] ตัวอักษรที่เป็นตัวใหญ่ทุกตัว
[:graph:] ตัวอักษรที่อยู่ในคลาส [:punct:], [:upper:], [:lower:] และ [:digit:] ทุกตัว
[:print:] ตัวอักษรทุกตัวที่พิมพ์ได้
[:xdigit:] ตัวอักษรที่เป็นเลขฐานสิบหกทุกตัว
คลาสเหล่านี้จะต้องใช้ภายใน Bracket เท่านั้นเช่น [[:alnum:]] หรือ [[:alpha:][:digit:]] เป็นต้น

[==] จับคู่กับตัวอักษรที่มีตัวอักษรฐาน (Base Character) ตรงกัน เช่น [=a=] จะจับคู่กับตัวอักษรที่มีตัวอักษรฐานเป็น a (เช่น Á และ Ä เป็นต้น) จะต้องใช้ภายใน Bracket เท่านั้น

ลองเล่นดูนะครับ เล่นไป ๆ จะพบว่ามันไม่ยากอย่างที่คิดเลย ขอให้สนุกกับ Regular Expression นะครับ!

บทความที่เกี่ยวเนื่องกัน
Regular Expression มหัศจรรย์แห่งการจัดการ Text (ตอนที่ 1)

Sunday, November 15, 2009

Regular Expression มหัศจรรย์แห่งการจัดการ Text (ตอนที่ 1)

สวัสดีครับ สำหรับตอนนี้ผมตั้งชื่อไว้เสียโก้หรู ไม่ใช่เพราะจะเลียนแบบโฆษณาหรอกครับ แต่ด้วยรู้สึกว่า Regular Expression เป็นเครื่องมือสำหรับนักฐานข้อมูลที่มหัศจรรย์จริง ๆ และควรจะมีมาตั้งนานแล้ว (Regular Expression เดิมมีอยู่บน Unix,Oracle เพิ่งเริ่มจะมีเมื่อ Version 10g นี้เอง) เราเริ่มกันเลยนะครับ

Regular Expression เป็นเรื่องเกี่ยวกับรูปแบบการเรียงตัวของตัวอักษร (Pattern) ซึ่ง Regular Expression ทำให้เราสามารถจับ Pattern ในข้อมูลที่เป็น Text ยาว ๆ ได้ ซึ่งการที่เราสามารถจับ Pattern ใน Text ได้นี้ทำให้เราสามารถค้นหาโดยใช้เงื่อนไข รวมทั้งสามารถจัดการกับข้อมูลได้อย่างมีประสิทธิภาพ

ตัวอย่างการหารูปแบบการเรียงตัวของตัวอักษรที่พบบ่อยคือข้อมูล "ที่อยู่" ที่เป็นข้อความติดต่อกัน เช่น
"11/999 ม.4 ถ.พุทธมณฑลสาย 3 แขวงบางไผ่ เขตบางแค กรุงเทพฯ 10160"

ถ้าข้อมูลในตารางที่อยู่มีข้อมูลที่มีลักษณะเหมือน ๆ กันกับข้อมูลข้างบนนี้ เราอาจจะบอกถึงรูปแบบคร่าว ๆ ได้ว่า
1. เลขที่บ้านจะอยู่ข้างหน้าเสมอ และจะต้องมีตัวเลขอย่างน้อยหนึ่งตัว อาจจะมีเครื่องหมาย "/" ด้วยก็ได้
2. "ม." หมายถึง หมู่ที่
3. "ถ." หมายถึง ถนน
4. "แขวง", "เขต" ความหมายตรงตัว
5. รหัสไปรษณีย์ เป็นเลขห้าหลักอยู่หลังสุด

สมมติว่าเดิมข้อมูลในตารางของเรา (cust_addr) ไม่ได้ตัดแบ่งที่อยู่ออกเป็นคอลัมน์ และเราต้องการตัดข้อมูลที่อยู่ข้างบนนี้ออกเป็นคอลัมน์ ๆ ดังข้างล่างนี้
1. เลขที่ = '11/999'
2. หมู่ = '4'
3. ถนน = 'พุทธมณฑลสาย 3'
4. แขวง/ตำบล = 'บางไผ่'
5. เขต/อำเภอ = 'บางแค'
6. จังหวัด = 'กรุงเทพฯ'
7. รหัสไปรษณีย์ = '10160'

สมมติอีกว่าเรามีข้อมูลในตาราง cust_addr ดังข้างล่างนี้

SQL> select * from cust_addr;

CUST_ID CUST_ADDR
------- ------------------------------------------------------------------
3 501/121 ถนนจันทร์ แขวงทุ่งวัดดอน อำเภอยานนาวา กรุงเทพฯ 10120 โทร (02)285-2834
2 12/45 ถนนเพชรเกษม จังหวัดนครปฐม โทร 0 24311235
1 15/120 ม.3 ถ.พุทธมณฑลสาย 3 แขวงบางไผ่ เขตบางแค กรุงเทพฯ 10160

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

SQL> select cust_addr from cust_addr where regexp_like (cust_addr,'\(?[0-9]{2,3}\)?[[:digit:]]{3,4}\-?[[:digit:]]{3,4}');

CUST_ADDR
-----------------------------------------------------------------
501/121 ถนนจันทร์ แขวงทุ่งวัดดอน อำเภอยานนาวา กรุงเทพฯ 10120 โทร (02)285-2834
12/45 ถนนเพชรเกษม จังหวัดนครปฐม โทร 0 24311235

เราจะได้แค่สองเรคคอร์ดเนื่องจากเรคคอร์ดที่ cust_id = 1 ไม่มีหมายเลขโทรศัพท์

เราสามารถใช้ Regular Expression ในการตัดเอาเฉพาะส่วนที่ต้องการจากข้อความยาว ๆ มาก็ได้ โดยใช้ REGEXP_SUBSTR เช่น

SQL> select cust_addr,regexp_substr(cust_addr,'\(?[0-9]{2,3}\)?[[:digit:]]{3,4}\-?[[:digit:]]{3,4}') as tel_no from cust_addr

CUST_ADDR TEL_NO
------------------------------------------------------------------ ------------
501/121 ถนนจันทร์ แขวงทุ่งวัดดอน อำเภอยานนาวา กรุงเทพฯ 10120 โทร (02)285-2834 (02)285-2834
12/45 ถนนเพชรเกษม จังหวัดนครปฐม โทร 0 24311235 24311235
15/120 ม.3 ถ.พุทธมณฑลสาย 3 แขวงบางไผ่ เขตบางแค กรุงเทพฯ 10160

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

SQL> desc cust_addr2;

Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
CUST_ID NUMBER Y
ADDR_NO VARCHAR2(20) Y
MOO VARCHAR2(5) Y
ROAD VARCHAR2(50) Y
TAMBON VARCHAR2(50) Y
AMPHUR VARCHAR2(50) Y
PROVINCE VARCHAR2(50) Y
POSTCODE VARCHAR2(5) Y

เราจะใช้ REGEXP_SUBSTR ในการตัดข้อความที่อยู่ออกเป็นส่วนๆ เริ่มกันที่บ้านเลขที่่ก่อนนะครับ อย่าเพิ่งกังวลกับตัวอักษรแปลก ๆ นะครับ

SQL> select regexp_substr(cust_addr,'^[[:digit:]]{1,4}/?[[:digit:]]{1,4}') as addr_no from cust_addr;

ADDR_NO
-----------------
501/121
12/45
15/120

คราวนี้เราจะตัดเอาถนน (ROAD) ออกมาอีกคอลัมน์นะครับ

SQL> select regexp_substr(cust_addr,'^[[:digit:]]{1,4}/?[[:digit:]]{1,4}') as addr_no
, regexp_substr(cust_addr,'(ถนน|ถ\.)[[:alpha:]]+[[:blank:]]?[[:digit:]]{0,3}') as road
from cust_addr;

ADDR_NO ROAD
---------- ----------------
501/121 ถนนจันทร์
12/45 ถนนเพชรเกษม
15/120 ถ.พุทธมณฑลสาย 3

ถ้าต้องการตัดทุกคอลัมน์จะได้ดังนี้ครับ

SQL> select
2 cust_id
3 , regexp_substr(cust_addr,'^[[:digit:]]{1,4}/?[[:digit:]]{1,4}') as addr_no
4 , regexp_substr(cust_addr,'(ม\.|หมู่)[0-9]{1,2}') as moo
5 , regexp_substr(cust_addr,'(ถนน|ถ\.)[[:alpha:]]+[[:blank:]]?[[:digit:]]{0,3}') as road
6 , regexp_substr(cust_addr,'(แขวง|ตำบล)[[:alpha:]]+') as tambon
7 , regexp_substr(cust_addr,'(เขต|อำเภอ)[[:alpha:]]+') as amphur
8 , regexp_substr(cust_addr,'(จ\.|จังหวัด)[[:alpha:]]+|กรุงเทพฯ') as province
9 , regexp_substr(cust_addr,'[[:digit:]]{5}$') as postcode
10 , regexp_substr(cust_addr,'\(?[0-9]{1,3}\)?[[:blank:]]?[[:digit:]]{3,4}\-?[[:digit:]]{3,4}') tel_no
11 from cust_addr;

CUST_ID ADDR_NO MOO ROAD TAMBON AMPHUR PROVINCE POSTCODE TEL_NO
------- ------- --- -------------- ---------- ---------- -------- -------- --------
3 501/121 ถนนจันทร์ แขวงทุ่งวัดดอน อำเภอยานนาวา กรุงเทพฯ (02)285-2834
2 12/45 ถนนเพชรเกษม จังหวัดนครปฐม 11235 0 24311235
1 15/120 ม.3 ถ.พุทธมณฑลสาย 3 แขวงบางไผ่ เขตบางแค กรุงเทพฯ 10160

เราใช้คำสั่ง insert into ... select เพื่อ Insert ข้อมูลจากตาราง cust_addr ที่แปลงแล้วเข้าไปในตาราง cust_addr2

SQL> insert into cust_addr2
select
cust_id
, regexp_substr(cust_addr,'^[[:digit:]]{1,4}/?[[:digit:]]{1,4}') as addr_no
, regexp_substr(cust_addr,'(ม\.|หมู่)[0-9]{1,2}') as moo
, regexp_substr(cust_addr,'(ถนน|ถ\.)[[:alpha:]]+[[:blank:]]?[[:digit:]]{0,3}') as road
, regexp_substr(cust_addr,'(แขวง|ตำบล)[[:alpha:]]+') as tambon
, regexp_substr(cust_addr,'(เขต|อำเภอ)[[:alpha:]]+') as amphur
, regexp_substr(cust_addr,'(จ\.|จังหวัด)[[:alpha:]]+|กรุงเทพฯ') as province
, regexp_substr(cust_addr,'[[:digit:]]{5}$') as postcode
, regexp_substr(cust_addr,'\(?[0-9]{1,3}\)?[[:blank:]]?[[:digit:]]{3,4}\-?[[:digit:]]{3,4}') tel_no
from cust_addr;

3 rows inserted

SQL> select * from cust_addr2;

CUST_ID ADDR_NO MOO ROAD TAMBON AMPHUR PROVINCE POSTCODE TEL_NO
------- ------- --- -------------- ---------- ---------- -------- -------- --------
3 501/121 ถนนจันทร์ แขวงทุ่งวัดดอน อำเภอยานนาวา กรุงเทพฯ (02)285-2834
2 12/45 ถนนเพชรเกษม จังหวัดนครปฐม 11235 0 24311235
1 15/120 ม.3 ถ.พุทธมณฑลสาย 3 แขวงบางไผ่ เขตบางแค กรุงเทพฯ 10160

เราจะเห็นว่ายังมีคำที่ไม่ต้องการในแต่ละคอลัมน์อีกเช่น "แขวง" หรือ "ถนน" ฯลฯ เราจะใช้ REGEXP_REPLACE ในการตัดเอาตัวอักษรที่ไม่ต้องการนี้ออก

SQL> update cust_addr2
set moo = regexp_replace(moo,'ม\.|หมู')
, road = regexp_replace(road,'ถนน|ถ\.')
, tambon = regexp_replace(tambon,'ตำบล|แขวง')
, amphur = regexp_replace(amphur,'อำเภอ|เขต')
, province = regexp_replace(province,'จังหวัด');

3 rows updated

SQL> select * from cust_addr2;

CUST_ID ADDR_NO MOO ROAD TAMBON AMPHUR PROVINCE POSTCODE TEL_NO
------- ------- --- -------------- ---------- ---------- -------- -------- --------
3 501/121 จันทร์ ทุ่งวัดดอน ยานนาวา กรุงเทพฯ (02)285-2834
2 12/45 เพชรเกษม นครปฐม 11235 0 24311235
1 15/120 3 พุทธมณฑลสาย 3 บางไผ่ บางแค กรุงเทพฯ 10160

จะเห็นว่า Regular Expression ช่วยเราได้มากในการจัดการกับข้อความที่มีรูปแบบหรือ "Pattern" บางอย่างในเนื้อข้อมูล อย่างเช่นข้อมูล "ที่อยู่"ได้ บทความตอนต่อไปเราจะนำเอาไวยกรณ์ ของ regular expression มาคุยกันต่อ ขอขอบคุณที่ติดตามแล้วพบกันครั้งหน้านะครับ

บทความที่เกี่ยวเนื่องกัน
Regular Expression มหัศจรรย์แห่งการจัดการ Text (ตอนที่ 2 จบ)

Saturday, November 7, 2009

การใช้ Parallel Hint (ตอนที่ 2 จบ)

ข้อเขียนวันนี้ต่อเนื่องและเป็นตอนจบจากตอนที่แล้วคือ การใช้ Parallel Hint (ตอนที่ 1) โดยในตอนนี้จะเป็นวิธีการตรวจสอบการทำงานของ Parallel Query ที่เรากำลังรันอยู่ โดยแปลจากบทความของ gpike เช่นกัน ขอให้สนุกกับบทความและหวังว่าจะได้ประโยชน์กันนะครับ

การตรวจสอบ Parallel Process
ในขณะที่การใช้ Parallel Process เป็นสิ่งที่มีประโยชน์เมื่อต้องจัดการกับข้อมูลขนาดใหญ่ มันก็อาจจะทำให้คุณงงงวยเมื่อไม่รู้ว่ากำลังเกิดอะไรขึ้นภายในระบบฐานข้อมูล ระหว่างการทำ Parallel Query ซึ่ง Oracle ได้เตรียม V$ Views ไว้หลายตัวที่จะช่วยให้เราสามารถตรวจสอบกระบวนการของ Parallel Query ได้ ซึ่งจะช่วยให้เราสามารถเขียนคำสั่งได้ดีขึ้น และสามารถที่จะ Kill Process ที่กินทรัพยากรของระบบจำนวนมากเกินไป

อะไรคือ V$ Views
คิวรีในตัวอย่างต่อจากนี้ไป จะต้องใช้ V_$ View ซึ่งผู้ที่เป็นเจ้าของคือ SYS (คืออยู่ใน Schema "SYS") ปกติแล้ว V_$ View เราจะมองเห็นเป็น (โดยผ่าน Public Synonym) ซึ่งได้เอา Underscore ออก ตัวอย่างเช่นวิว SYS.V_$PX_SESSION เวลาเราใช้วิวตัวนี้เราจะใช้ผ่าน Synonym ชื่อ V$PX_SESSION วิธีการที่ง่ายที่สุด (แต่ไม่ปลอดภัย) คือการ GRANT SELECT ANY TABLE ให้กับ User ทุกคน

การดู Parallel Query Process เบื้องต้น
โดยปกติเมื่อมีการทำ Parallel Query จะเกิด Session ที่คอยประสานการทำงานของ Parallel Servers โดยในขณะทำคิวรี จะมี Parallel Servers 2 ชุดเกิดขึ้นได้แก่ 1) Producers ซึ่งคอยจัดการเรื่อง Full Table Scan และ 2) Consumers ซึ่งเอาข้อมูลทีได้จาก Producers มาทำ Operation ต่างเช่น Hash, Join เป็นต้น Degree ของการทำ Parallel ที่กำหนดในคำสั่ง Hint เป็นตัวกำหนดว่าจะมี Parallel Server Process มากน้อยแค่ไหนในแต่ละ (Parallel) Server Set ไม่ใช่จำนวนทั้งหมดในแต่ละคิวรี ดังตัวอย่างคิวรีข้างล่างนี้

select /*+ PARALLEL(s 2) */ distinct time_of_day from sales s;

ในขณะที่ทำการคิวรี เราจะรันคำสั่งเพื่อดูการทำงานภายในของ Parallel Query ข้างบน

select qcsid,
sid,
nvl(server_group,0) server_group,
server_set,
degree,
req_degree
from SYS.V_$PX_SESSION
order by qcsid,
nvl(server_group,0),
server_set;
คิวรีข้างบนจะให้ผลดังต่อไปนี้ (ผลที่ได้จะปรากฎ ณ ขณะที่ Parallel คิวรีตัวบนยังคงทำงานอยู่)

QCSID SID SERVER_GROUP SERVER_SET DEGREE REQ_DEGREE
------ ------- ---------- ---------- ---------- --------
390 390 0
390 368 1 1 2 2
390 353 1 1 2 2
390 332 1 2 2 2
390 330 1 2 2 2

ผลที่ได้แสดงให้เห็นว่าจากการทำ Parallel Query โดยมี Degree =2 ทำให้เกิด Process ขึ้นมา 5 Process แถวบนสุดเป็นตัวประสานงานของ Process อื่น ๆ เรียกว่า Query Coordinator (QC) แถว 2 และ 3 แสดง เซ็ทของ Parallel Process (server_set = 1) ซึ่งมีหน้าที่ทำการ Full Table Scan ตาราง SALES ในขณะที่แถวที่ 4 และ 5 (server_set = 2) จะแสดงการทำงานของ Parallel Process ซึ่งทำงานส่วนที่เหลือ (Distinct) ของข้อมูลบนตาราง SALES

สมมติว่าเราเพิ่ม Degree เป็น 4 แต่ในระบบฐานข้อมูลอนุญาตให้ใช้แค่ Degree = 3 เราจะได้ผลจากการคิวรี V_$PX_SESSION ดังนี้
QCSID SID SERVER_GROUP SERVER_SET DEGREE REQ_DEGREE
------ ------- ---------- ---------- ---------- --------
390 390 0
390 351 1 1 3 4
390 342 1 1 3 4
390 330 1 1 3 4
390 332 1 2 3 4
390 353 1 2 3 4
390 339 1 2 3 4

แม้ว่าคำสั่งจะขอ Degree = 4 (REQ_DEGREE) จะเห็นว่ามีแค่ 3 (DEGREE) ซึ่งอธิบายว่าทำไมเราจึงมีแค่ 3 Process ในแต่ละ Server Set

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

select a.qcsid,
a.sid,
a.server_group,
a.server_set,
substr(b.name,1,20) operation,
a.value
from V$PX_SESSTAT a,
V$STATNAME b
where a.statistic# = b.statistic#
and upper(b.name) = 'PHYSICAL READS'
order by a.qcsid,
a.server_group,
a.server_set;
คิวรีข้างบนนี้จะให้ผลดังข้างล่างก็ต่อเมื่อเรากำลังรันคิวรีที่เป็น Parallel อยู่ โดยผลที่ได้นี้เกิดจากคิวรีที่มี Degree = 2 และมีการ Join แบบซับซ้อน และมีการ Sort ร่วมกับการทำ Full Table Scan

QCSID SID SERVER_GROUP SERVER_SET OPERATION VALUE
------ ------- ---------- ---------- ---------- --------
332 330 1 1 physical reads 4632
332 351 1 1 physical reads 4697
332 333 1 2 physical reads 4554
332 339 1 2 physical reads 4605
332 332 physical reads 168005

คิวรีนี้แสดงให้เห็นว่าตอนที่กำลังรันคิวรี Parallel Server Set ทั้งคู่ (Producer และ Consumer) จะทำงานโดยแตกออกเป็น 2 Process ตาม DEGREE ที่กำหนด SID 332 เป็น Session ที่ทำหน้าที่เป็นตัวเริ่มต้นและคอยประสานงานของ Session ย่อยอื่น ๆ (Query Coordination Session) ค่าของ Physical Reads (การอ่านค่าจากฮาร์ดดิสก์) จะเพิ่มขึ้นเรื่อย ๆ จนกว่า Session นี้จะ Disconnect ไป

การตรวจสอบการทำงานภายในที่ใช้เวลานาน (Long Operations) โดยใช้ V$SESSION_LONGOPS
V$SESSION_LONGOPS เป็นที่เก็บสถานะของการทำงานซึ่งจะระบบกะว่าใช้เวลาไม่ต่ำกว่า 6 วินาที ตัวอย่างของ Long Operation ได้แก่ Full Table Scan, Hash Join และ Sort Merge ที่อาจจะสร้าง Log การทำงานของตัวมันในระหว่างทำงานไว้ในตารางนี้ (และหายไปเมื่อทำงานเสร็จ) ข้อดีของตารางนี้คือมีคอลัมน์ที่แสดงเวลาที่งานจะเสร็จโดยประมาณเป็นวินาที สำหรับ Parallel Query ที่จะเก็บ Log ในตารางนี้ จะสร้างเรคคอร์ดตามจำนวนของ Parallel Server ที่กำหนดไว้

select sl.sid,
sl.serial#,
substr(sl.opname,1,30),
sl.sofar,
to_char(sl.start_time,'DD-MON-YYYY:HH24:MI:SS') start_time,
sl.elapsed_seconds elapsed,
sl.time_remaining remaining
from V$SESSION_LONGOPS sl,
V$SESSION s
where s.sid = sl.sid
and s.serial# = sl.serial#
and s.username = '&username'
order by sl.start_time desc,
sl.time_remaining asc;

ระหว่างการทำงานของ Parallel คิวรีทีใช้เวลานาน เราอาจจะได้ผลของคิวรีดังนี้

SID SERIAL# OPERATION START_TIME ELAPSED REMAINING
----- ------------- ------------------ -------------------- --------------- -------------------
332 18956 Table Scan 03-MAY-2007:14:26:11 42 18
333 18957 Table Scan 03-MAY-2007:14:26:13 40 20

โปรดสังเกตว่า V$SESSION_LONGOPS ไม่ได้เอาไว้ใช้ในการตรวจสอบ Parallel Query โดยตรง แต่สามารถใช้ได้กับการทำงานใด ๆ ที่ใช้เวลานานเช่นการทำ Full Table Scan

แปลจาก Monitoring Oracle Parallel Queries: PARALLEL Hint Part2 Published in Complex SQL, Hints, SQL, Tuning by gpike วันพฤหัสบดีที่ 3 พฤษภาคม 2007

บทความที่เกี่ยวเนื่องกัน
1. การใช้ Parallel Hint (ตอนที่ 1)

Thursday, November 5, 2009

การกำหนดให้ Developer ไม่สามารถแก้ไขโครงสร้างตาราง

ขอบคุณคุณ Somritt Thiendej สำหรับคำถาม
"โปรแกรมเมอร์ใช้ toad ต่อ oracle db ทำอย่างไรจะไม่ให้สามารถแก้ไข ฟิล เพิ่มฟิล ในตารางได้?"

ปกติ User บนฐานข้อมูลจะมีสิทธิ์สร้างตาราง และเปลี่ยนแปลงตารางที่เขาสร้างได้ ถ้าเราต้องการให้ User หรือ Developer มีสิทธิ์เฉพาะการจัดการข้อมูลในตาราง แต่ไม่มีสิทธิ์ในการแก้ไขโครงสร้าง เราจะต้องให้ User หรือ Developer ล็อกอินด้วย User อื่น แล้วกำหนดสิทธิ์เฉพาะการจัดการข้อมูลในตารางให้เขาดังตัวอย่างข้างล่างนี้ครับ

สมมติว่าตารางที่ต้องให้ Developer ใช้อยู่ใน Schema "account" สังเกตว่าถ้าเราล็อกอินด้วย User "account" เราจะสามารถสร้างตาราง,เปลี่ยนแปลงโครงสร้างตารางที่สร้าง และเปลี่ยนแปลงข้อมูลในตารางได้

===========================================================
Listing1: User "account" สามารถสร้างและจัดการโครงสร้างตารางที่ตนเองสร้างขึ้นได้เสมอ เราไม่สามารถห้ามได้

SQL> conn system
Connected.

SQL> create user account2 identified by account;

User created.

SQL> grant connect,resource to account2;

Grant succeeded.

SQL> create table table1 (id number,name varchar2(20));

Table created.

SQL> alter table table1 add address varchar2(50);

Table altered.

SQL> desc table1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(20)
ADDRESS VARCHAR2(50)

SQL> drop table table1;

Table dropped.

SQL> create table table2(id number,name varchar2(30));

Table created.

SQL> insert into table2 values (1,'Somchai');

1 row created.

SQL> insert into table2 values (2,'Somkid');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from table2;

ID NAME
---------- ------------------------------
1 Somchai
2 Somkid

ต่อมาเราสร้าง User ชื่อ accapp ที่จะให้ Developer ใช้ในการ Access ฐานข้อมูล จากนั้นเรา Grant สิทธิ์ให้ Connect ได้ และสามารถใช้ทรัพยากรของระบบได้ (เช่นสามารถสร้างตารางของตัวเองได้) Resource เป็นสิทธิ์ที่ให้พื้นที่กับ User แบบไม่จำกัด จากนั้นเราก็ ล็อกอินด้วย User ที่มีสิทธิ์ DBA หรือล็อกอินด้วย User "account" แล้ว Grant สิทธิ์ทุกอย่างบนตาราง Table2 ให้กับ accapp เมื่อเราล็อกอินเป็น accapp แล้วคิวรีดูจาก USER_TAB_PRIVS ซึ่งแสดงสิทธิ์ของ User ที่ล็อกอินอยู่ว่ามีสิทธิ์อะไรบนตารางไหนบ้าง เนื่องจากเราได้ Grant All ไว้เราจะเห็นว่า accapp ได้สิทธิ์ทุกสิทธิ์บนตาราง Table2 ของ "account" เราสามารถจะลบสิทธิ์บางตัวที่ไม่ต้องการออก โดยการใช้คำสั่ง Revoke หรือจะใช้วิธีการ Grant เฉพาะสิทธิ์ที่ต้องการให้กับ Developer ก็ได้ เช่นถ้าต้องการให้ Developer จัดการกับข้อมูลในตารางได้เท่านั้นก็ใช้คำสั่ง "grant insert,update,delete on table2 to accapp" แทนที่จะใช้คำสั่ง grant all เป็นต้น ข้อสำคัญอย่าไป grant สิทธิ์ DBA ให้กับ User ก็แล้วกันนะครับ :)

===========================================================
Listing2: User "accapp" จะสามารถจัดการตารางของ User (หรือ Schema) อื่น ได้เท่าที่ได้รับสิทธิ์

SQL> conn system
Connected.
SQL> create user accapp identified by accapp;

User created.

SQL> grant connect,resource to accapp;

Grant succeeded.

SQL> grant all on account.table2 to accapp;

Grant succeeded.

SQL> conn accapp
Connected.
SQL> column owner format a15
SQL> column table_name format a15
SQL> column privilege format a30
SQL> select owner ,table_name ,privilege from user_tab_privs;

OWNER TABLE_NAME PRIVILEGE
--------------- --------------- ------------------------------
ACCOUNT TABLE2 FLASHBACK
ACCOUNT TABLE2 DEBUG
ACCOUNT TABLE2 QUERY REWRITE
ACCOUNT TABLE2 ON COMMIT REFRESH
ACCOUNT TABLE2 REFERENCES
ACCOUNT TABLE2 UPDATE
ACCOUNT TABLE2 SELECT
ACCOUNT TABLE2 INSERT
ACCOUNT TABLE2 INDEX
ACCOUNT TABLE2 DELETE
ACCOUNT TABLE2 ALTER

11 rows selected.

SQL> select * from account.table2;

ID NAME
---------- ------------------------------
1 Somchai
2 Somkid

-- คำสั่งข้างล่างแสดงให้เห็นว่าตอนนี้ accapp สามารถเปลี่ยนโครงสร้างของ table2 ได้
SQL> alter table account.table2 add test number;

Table altered.

SQL> conn account
Connected.
SQL> revoke alter on table2 from accapp;

Revoke succeeded.

SQL> conn accapp
Connected.
--หลังจากที่เรา Revoke สิทธิ์ในการแก้ไขตารางออกไปแล้ว accapp ไม่สามารถแก้ไขตารางได้
SQL> alter table account.table2 drop column test;
alter table account.table2 drop column test
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> alter table account.table2 add testcol varchar2(30);
alter table account.table2 add testcol varchar2(30)
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> drop table account.table2;
drop table account.table2
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn accapp
Connected.

SQL> desc account.table2;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(30)
TEST NUMBER

SQL> show user
USER is "ACCAPP"
SQL> conn system
Connected.
SQL> alter table account.table2 drop column TEST;

Table altered.

SQL> conn accapp
Connected.
SQL> select * from account.table2;

ID NAME
---------- ------------------------------
1 Somchai
2 Somkid

SQL> insert into account.table2 values (3,'Somkiat');

1 row created.

SQL> select * from account.table2 ;

ID NAME
---------- ------------------------------
1 Somchai
2 Somkid
3 Somkiat

SQL> update account.table2 set name = 'XXXX' where id = 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from account.table2;

ID NAME
---------- ------------------------------
1 Somchai
2 XXXX
3 Somkiat

SQL> delete account.table2 where id = 3;

1 row deleted.

SQL> commit;

Commit complete.

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

SQL> conn account
connected.

SQL> set heading off
SQL> select 'grant all on ' || table_name || ' to accapp;' from user_tables;

Tuesday, October 20, 2009

การใช้ Parallel Hint (ตอนที่ 1)

Parallel Hint เป็น Hint ตัวหนึ่งที่มีประโยชน์ในการจัดการกับข้อมูลขนาดใหญ่ โดยหลักการคือการแยกงานออกทำพร้อม ๆ กัน แล้วจึงนำผลที่ได้มารวมกันอีกทีหนึ่ง อย่างไรก็ตามการใช้ Parallel Hint นี้จะต้องใช้ด้วยความระมัดระวังเพราะมันอาจจะมีผลกระทบคือไปเบียดบังเอาทรัพยากรของระบบอย่างมากได้ โดยส่วนตัวผมคิดว่า Parallel Hint เหมาะกับงานที่เป็น Batch Process ซึ่งทำนอกเวลางานมีผู้ใช้งานจำนวนน้อยและเราสามารถควบคุมสถานการณ์ได้ ข้อเขียนในวันนี้ผมได้นำเอาบทความเรื่อง Parallel Execution and the /*+ PARALLEL */ hint มาแปลและเรียบเรียงใหม่ โดยใช้ตัวอย่างที่ผู้อ่านสามารถทดลองดูได้จริง ขอให้สนุกกับข้อเขียนนี้ และหวังว่าจะมีประโยชน์ในการนำไปใช้งานนะครับ


Parallel Execution and the /*+ PARALLEL */ hint
คำสั่งชี้นำ(hint) /*+ PARALLEL(ชื่อตาราง ดีกรี) */ คงจะเป็นคำสั่งที่ผมชอบที่สุดอันหนึ่ง เนื่องจากผมเอามันออกมาใช้เกือบจะทุกกรณีไป อย่างไรก็ตาม PARALLEL hint แม้ว่าจะทรงพลัง แต่ก็อาจจะมีผลเสียต่อประสิทธิภาพของระบบได้หากใช้โดยไม่ระมัดระวัง ปกติ hint อื่น ๆ จะให้ผลอย่างมากก็คือทำให้ประสิทธิภาพของคิวรีดีขึ้นหรืออย่างเลวก็ช้าลง แต่ PARALLEL hint อาจจะทำให้ทั้งระบบฐานข้อมูลเกิดปัญหาเลยก็ได้ถ้าใช้โดยไม่ระวัง

ระหว่างการทำ Parallel Execution การปฏิบัติการภายในของระบบฐานข้อมูลจะถูกตัดแบ่งออกเป็นส่วน ๆ เพื่อแจกใหักับระบบซึ่งรองรับ Multi-thread (การทำงานพร้อม ๆ กันหลาย ๆ งานในขณะเดียวกันของคอมพิวเตอร์) การทำ Parallel Execution สามารถใช้ได้ทั้งกับการคิวรีหรือคำสั่งจัดการข้อมูล (DML) เช่น INSERT, UPDATE หรือคำสั่งที่เกี่ยวกับโครงสร้าง (DDL) เช่น CREATE INDEX ฯลฯ เบื้องลึกเบื้องหลังต่าง ๆ สามารถหาอ่านได้จากเอกสารของ Oracle ในที่นี้เราจะพูดถึงแต่ว่า hint ตัวนี้สามารถทำอะไรให้เราได้บ้าง

สมมติว่าฐานข้อมูลของเรา DBA ได้ตั้งค่าใหัสามารถทำ Parallel Query ได้ เราสามารถที่จะเรียกใช้คุณสมบัติ Parallel ได้โดยการใช้คีย์เวิรด์ "DEGREE" ในคำสั่ง CREATE TABLE หรือ ALTER TABLE เพื่อที่จะทำให้คิวรีที่ทำ Full Table Scan (การอ่านข้อมูลทั้งตารางโดยไม่ใช้อินเด็กซ์) กับตารางนี้ใช้คุณสมบัติ Parallel โดยอัตโนมัติ คีย์เวิรด์ DEGREE จะบอกถึงจำนวนของ Process ที่จะให้ระบบฐานข้อมูลแตกงานออกเพื่อทำพร้อม ๆ กัน (ขึ้นอยู่กับจำนวน CPU ที่มีด้วย) แต่โดยมากแล้วไม่ค่อยมีใครใช้คุณสมบัตินี้กับตารางโดยตรงเนื่องจากอาจจะส่งผลกระทบกับการทำงานโดยรวมกับระบบฐานข้อมูล เช่นโดยปกติถ้าตารางนั้นมีอินเด็กซ์ ซึ่งคิวรีที่ใช้สามารถที่จะใช้อินเด็กซ์ได้ซึ่งกินทรัพยากรน้อยการการใช้ Parallel Query แต่ตัว Optimizer (ตัวที่ทำการคิดในระบบฐานข้อมูลเพื่อให้คิวรีได้อย่างมีประสิทธิภาพที่สุด) อาจจะไปเลือกใช้ Full Table Scan แทน ถ้าตารางนั้นได้กำหนดให้สามารถทำ Parallel ไว้

ลองดูตัวอย่างของคิวรีที่ช้า และการใช้ Parallel เพื่อทำให้มันเร็วขึ้น
เริ่มจากการสร้างตารางจากตาราง all_objects โดยเพิ่มจำนวนแถวให้มากกว่า 4 ล้านแถว และสร้างตาราง Look Up ชื่อ obj_type โดยการ select distinct OBJECT_TYPE จากตาราง ALL_OBJECTS อีกที

SQL> create table all_obj as select * from all_objects;

Table created.

SQL> insert into all_obj select * from all_obj;

70908 rows created.

SQL> insert into all_obj select * from all_obj;

141816 rows created.

SQL> insert into all_obj select * from all_obj;

283632 rows created.

SQL> insert into all_obj select * from all_obj;

567264 rows created.

SQL> insert into all_obj select * from all_obj;

1134528 rows created.

SQL> insert into all_obj select * from all_obj;

2269056 rows created.

SQL> insert into all_obj select * from all_obj;

4538112 rows created.

SQL> commit;

Commit complete.

SQL> create table obj_type as select distinct object_name from all_objects;

Table created.

SQL> set autotrace traceonly explain
SQL> select ot.object_type,count(*)
2 from all_obj ao,
3 obj_type ot
4 where ao.object_type = ot.object_type
5 group by ot.object_type
6 order by ot.object_type;

ผลที่ได้จะดูคล้าย ๆ ข้างล่าง


นี่เป็นผลที่เราคาดไว้แล้ว Optimizer จะทำการสแกนทุก ๆ แถวในตาราง ALL_OBJ และ Join กับตาราง OBJ_TYPE ซึ่งก็ไม่มีอะไรผิดปกติ คิวรีจะใช้เพียง Thread เดียวในการทำงาน แต่ถ้าเครื่องของเรามี CPU มากกว่าหนึ่ง และระบบสามารถทำงานด้วย Thread จำนวนมากกว่าหนึ่งได้ เราก็จะสามารถใช้ Parallel Hint ดังตัวอย่างต่อไปนี้

SQL> select /*+ parallel(ao 2) */ ot.object_type,count(*)
2 from all_obj ao,
3 obj_type ot
4 where ao.object_type = ot.object_type
5 group by ot.object_type
6 order by ot.object_type;


เราจะเห็นว่ามีบรรทัดเพิ่มขึ้นมาอีกหลายบรรทัดใน Explain Plan ซึ่งพอจะสรุปได้ว่ามีการแยก Query ออกเป็นส่วนย่อย ๆ แล้วทำงานพร้อม ๆ กัน เสร็จแล้วจึงเอาผลมารวมกันอีกที จากการใส่ Hint นี้เข้าไปเราพบว่าคิวรีของเราใช้เวลาทั้งหมด 00:04:05 จากเดิมที่ไม่ได้ใช้ Hint คิวรีเดียวกันนี้ใช้เวลา 00:07:21 ซึ่งเร็วกว่าเดิมถึง 40%!

อย่างไรก็ตามของดีไม่ได้มาเปล่า ๆ ด้วยการใช้ Parallel Hint นี้เราได้สั่งให้มีการใช้พลังของ Hardware มากกว่าปกติ จากตัวอย่างเราใช้ Degree = 2 ซึ่งเราอาจจะใช้มากกว่านี้เป็น 4, 6, 8 หรือ 16 ก็ได้ขึ้นอยู่กับ Hardware ที่เรามี แต่จากประสบการณ์ที่เคยทำมาบางทีการกำหนดให้ Degree สูง ๆ ก็ไม่ใช่ว่าจะให้ผลที่เร็วที่สุดเสมอไป ขึ้นกับปัจจัยหลาย ๆ ประการบน Hardware ของคุณด้วย ตัวอย่างเช่น CPU อาจจะทำงานได้ดีกับ Parallel Hint แต่อาจจะพบคอขวดที่ I/O ก็ได้เป็นต้น ลองทดสอบกับ Degree ต่าง ๆ ที่เหมาะสมกับระบบของคุณ นอกจากนี้การกำหนด Degree สูง ๆ จะทำให้เกิด Process จำนวนมากในการทำงานคิวรี ซึ่งจะไปเบียดบังเอาพลังของ Hardware จากคนอื่นๆ ที่ใช้เครื่องแม่ข่ายเดียวกันนั้นอยู่ด้วย จึงควรใช้ Parallel Query ด้วยความระมัดระวัง และเท่าที่จำเป็นเมื่อการทำ Full Table Scan เป็นสิ่งที่หลีกเลี่ยงไม่ได้ บางตำราให้ลองเอา 2 * จำนวน CPU ที่มีในการกำหนดจำนวน Degree ไปลองกันดูเอง แล้วพบกันตอนต่อไปนะครับ

เรียบเรียงจาก
Parallel Execution and the /*+ PARALLEL */ hint ตีพิมพ์ใน Complex SQL, SQL Tuning by gpike วันอังคารที่ 27 มีนาคม 2007

บทความที่เกี่ยวเนื่องกัน
1. การใช้ Parallel Hint (ตอนที่ 2 จบ)

Sunday, September 20, 2009

การตั้งค่าใหักับ PGA Memory

1. ตั้งค่า WORKAREA_SIZE_POLICY = AUTO
2. ตั้งค่า PGA_AGGREGATE_TARGET = จำนวน Memory ที่เหลือทั้งหมด

หลังจากที่เราทำการ Config Memory ส่วนสำคัญ ๆ เช่น SGA ซึ่งประกอบด้วย Buffer Cache, Log Buffer, Shared+Large+Java Poolไปแล้ว Memory ส่วนที่เหลือเราจะกำหนดให้กับ PGA Memory ซึ่ง Oracle ใช้ในการ Sort (เช่นการใช้ Memory ที่เกิดจากการใช้คำสั่ง ORDER BY, คำสั่งที่เป็น Analytical, DISTINCT, GROUP BY หรือการทำ Hash Join ของ Oracle ฯลฯ) ซึ่ง Operation ต่าง ๆ เหล่านี้จะใช้ Memory ส่วนที่เรียกว่า PGA ซึ่งเมื่อ Memory ส่วนนี้ไม่เพียงพอที่จะทำงาน (เช่นการ Order By) Oracle จะย้าย (Swap) ข้อมูลไปทำใน Temporary Tablespace (Temporary Tablespace จึงทำหน้าที่คล้าย ๆ Virtual Memory ในกรณีนี้) แล้วย้ายกลับเข้ามาใน PGA

Automatic Memory Management
สิ่งที่ยุ่งยากมากอันหนึ่งของ DBA คือการกำหนดพื้นที่ในการทำงานให้กับ User แต่ละคนที่ล็อกอินเข้ามาใช้งานฐานข้อมูล เราสามารถกำหนดแบบ Manual ได้โดยการทำผ่านพารามิเตอร์ชื่อ SORT_ARE_SIZE และ HASH_AREA_SIZE แต่สิ่งที่ยากก็คือไม่รู้ว่าจะเซ็ทให้ใหญ่หรือเล็กแค่ไหน และที่ซับซ้อนกว่านั้นคือความต้องการใช้พื้นที่ของ User จะเปลี่ยนไปตามเรื่อย ๆ เช่นตอนเช้า 8:00 มีเพียง 2 User ต่อมาตอน 14:00 อาจจะมี 100 User เป็นต้น

การกำหนดแบบ Automatic Memory Management จะทำให้งานเหล่านี้ง่ายขึ้น โดยการเซ็ทพารามิเตอร์
1. WORKAREA_SIZE_POLICY = AUTO
2. PGA_AGGREGATE_TARGET = Memory ที่เหมาะสม

โดย Memory ที่เหมาะสม คือ (Physical) Memory ส่วนที่เหลือที่จะให้ Oracle จัดการ Assign ใหั User แต่ละคนเองโดยอัตโนมัติ ซึ่งการใช้ Automatic Memory Management จะทำให้งานการกำหนดพื้นที่ในการทำงานให้กับ User แต่ละคนทำได้ง่ายกว่าการกำหนดเองผ่าน SORT_AREA_SIZE และ HASH_AREA_SIZE โดยเฉพาะในกรณีที่ความต้องการเหล่านี้เปลี่ยนแปลงไปเรื่อย ๆ

ในอดีตหลังจากกำหนด Memory ส่วนหลัก ๆ (Buffer Cache, Log Buffer และ Pools ต่าง ๆ)ไปแล้ว DBA จะกำหนด Memory ส่วนที่เหลือให้กับ PGA โดยกำหนดผ่าน SORT_AREA_SIZE สมมติว่ามีการ Order By พร้อม ๆ กัน 10 Order By, Oracle อาจจะใช้ Memory ประมาณ 10 * SORT_AREA_SIZE แต่ถ้ามี 100 Order By พร้อม ๆ กันก็อาจจะใช้ Memoryได้ถึง 100 * SORT_AREA_SIZE และเมื่อรวมถึงการที่มีงานอื่น ๆ จะต้องใช้พื้นที่ของ PGA อีก ทำให้การควบคุมขนาดของ PGA ให้เหมาะสมตลอดเวลา ทำได้ยุ่งยากมาก

สิ่งที่เราต้องการคือให้หน่วยความจำส่วนนี้สามารถใช้งานได้อย่างยืดหยุ่น กล่าวคือถ้ามีผู้ใช้พร้อมกันจำนวนมาก แต่ละผู้ใช้งานก็จะได้ Memory ขนาดเล็ก ในขณะที่เมื่อจำนวนผู้ใช้งานลดลง แต่ะละคนก็ควรจะได้พื้นที่ใน PGA จำนวนมากขึ้นตามสัดส่วน ซึ่งการตั้งค่า WORKAREA_SIZE_POLICY = AUTO และกำหนด Memory ส่วนที่เหลือใหักับ PGA_AGGREGATE_TARGET ทั้งหมด จะเป็นการมอบหมายใหั Oracle จัดการหน่วยความจำ PGA ให้ตามแนวคิดนี้โดยอัตโนมัติ ซึ่งขนาดของ PGA_AGGREGATE_TARGET ที่เหมาะสมนี้เราจำต้องเป็นผู้กำหนดให้กับ Oracle ซึ่งเราอาจจะหาค่าที่เหมาะสมนี้ได้จาก Oracle Enterprise Manager (PGA Advisory) หรือ Statspack ซึ่งจะแนะนำว่าขนาดของ PGA_AGGREGATE_TARGET ที่เหมาะสม (ที่จะทำให้การใช้ Temporary Tablespace น้อยที่สุด) ณ ขณะนั้นคืออะไร

แล้วมีกรณีใดบ้างที่เราจะไม่ต้องการใช้ WORKAREA_SIZE_POLICY และ PGA_AGGREGATE_TARGET เพื่อให้ Oracle จัดการหน่วยความจำส่วนของ User ให้?

โดยปกติการจัดการที่ Oracle ทำให้โดยอัดโนมัติจะกระจายหน่วยความจำให้กับแต่ละ User เท่า ๆ กัน ซึ่งจะจำกัดขนาดของหน่วยความจำเป็นสัดส่วนของ PGA_AGGREGATE_TARGET แต่ในบางกรณีเช่นการ Batch Process ตอนกลางคืน ที่ไม่มีผู้ใช้คนอื่นอีกมีแต่ Batch Process ที่ต้องการใช้พื้นที่บน PGA ในกรณีนี้เราสามารถใช้ SORT_AREA_SIZE ผ่านคำสั่ง alter session ในการกำหนดขนาดของ PGA และให้ Memory ทั้งหมดกับ Batch Process ได้, คำสั่ง alter session นี้จะ Override การจัดการหน่วยความจำ PGA อัตโนมัติของ Oracle สำหรับเฉพาะ Session และเมื่อ Session นั้นปิดไป Session ใหม่ที่เปิดมาก็จะใช้การจัดการหน่วยความจำ PGA แบบอัตโนมัติตามเดิม

กล่าวโดยสรุปเกี่ยวกับการกำหนดค่าใหักับหน่วยความจำ PGA
ใช้วิธีการ Automatic Memory Management โดย
1. ตั้งค่า WORKAREA_SIZE_POLICY = AUTO
2. ตั้งค่า PGA_AGGREGATE_TARGET = จำนวน Memory ที่เหลือทั้งหมด
หากต้องการ PGA Memory ขนาดใหญ่พิเศษเป็นครั้งคราว
3. ใช้คำสั่ง alter session set PGA_AGGREGATE_TARGET = จำนวน Memory ที่เหลือทั้งหมด

Sunday, August 30, 2009

การตรวจสอบ Performance โดยใช้ Enterprise Manager

ใน Oracle ก่อนเวอร์ชั่น 9 การตรวจสอบ Performance แต่ละตัวเป็นเรื่องค่อนข้างยุ่งยาก และมักจะต้องทำใน text mode แต่ในปัจจุบันนี้ Oracle ได้พัฒนา Tools ที่ใช้ในการบริหารฐานข้อมูล เพื่อให้การบริหารฐานข้อมูลทำได้ง่ายขึ้นและใช้เวลาในการเรียนรู้น้อยลง Oracle Enterprise Manager ตัวที่นำมาให้ดูเป็นตัวอย่างนี้เป็นตัวที่มากับ Oracle10g Database ซึ่งสามารถใช้งานได้เลยถ้ามีการกำหนดให้ลงไว้เมื่อตอนสร้างฐานข้อมูล ผู้ใช้สามารถเข้า Enterprise Manager โดยพิมพ์ URL บน Web Browser เช่น http://hostname:5500/em หรือ http://hostname:1158/em แล้วแต่ว่า Port ถูกตั้งค่าเป็นอะไร ผมจะไม่เขียนถึงรายละเอียดตรงนี้นะครับ เมื่อเข้าสู่หน้าจอหลักของ Oracle Enterprise Mananger แล้ว คลิ๊กแท็ป Performance เลยครับ

Sessions: Waiting and Working เป็น Chart ที่สำคัญที่สุดอันหนึ่ง แสดงแผนภูมิของสภาวะการทำงานของระบบฐานข้อมูล ณ ช่วงเวลาหนึ่งจนถึงปัจจุบัน โดยกราฟจะแสดงจำนวน Session ที่กำลังใช้ CPU และกี่ Session ที่กำลังใช้งาน I/O, Redo Writes หรือกำลังทำงานผ่านระบบเน็ทเวอร์ค หรือการรอคอยประเภทอื่น ๆ

ในสภาวะที่เหมาะสม ทุก ๆ Sessions ควรจะทำงานบน CPU (เป็นพื้นที่สี่เขียวในแผนภูมิ) และไม่มีการรอคอย (Wait) ซึ่งเป็นพื้นที่สีอื่นที่นอกเหนือจาำกสีเขียวของ CPU) ถ้าปริมาณของสีอื่น ๆ มีมากกว่าสีเขียวในอัตราส่วนสูง แสดงว่ามีการรอคอยในระบบจำนวนมาก (สังเกตสีของชนิดของการรอคอยเพื่อจะได้รู้ว่าเป็นการรอคอยประเภทใด) เราสามารถคลิ๊กบน Legend เพื่อดูรายละเอียดของการรอคอยแต่ละประเภท อย่างไรก็ตามบางครั้งการรอคอยก็เป็นสิ่งที่หลีกเลี่ยงได้ยาก เช่นตอนที่เราโหลดข้อมูลจำนวนมากจาก Disk เข้าสู่ Database Buffer เป็นต้น



Hosts: Average Run Queue Length แสดงถึงการที่ Process มากกว่าหนึ่งตัว กำลังพยายามแย่งใช้ CPU กันอยู่ โดย Run Queue Length แสดงถึง Process ที่พร้อมจะใช้ CPU แต่ไม่สามารถจะใช้ได้ เพราะมี Process อื่นใช้อยู่ ตัวอยางเช่น เมื่อมี User ที่ได้ Lock ต้องการใช้ CPU แต่ไม่สามารถใช้งานได้ ในขณะที่มี User คนอื่นต้องการจะ Lock ตารางนั้นบ้าง User คนหลังจะต้องคอยจนกว่า คนแรกจะได้ใช้ CPU และปล่อย Lock อย่างไรก็ตามในบางครั้งเราอาจจะเห็นว่ามีการใช้งาน CPU ถึง 100% แต่ Response Time อาจจะดีก็ได้ เนื่องจากไม่มี Process ใด ๆ รอคอย CPU อยู่เลย

Hosts: Paging Rate แสดงอัตราที่ CPU เขียน Page of Memory ไว้บน Disk เนื่องจาก RAM ไม่พอซึ่งก่อให้เกิดผลเสียกับประสิทธิภาพของระบบอย่างมาก และถ้ามี Process ใด ๆ ที่รันอยู่ต้องการข้อมูลของ Page ที่ถูกเขียนไปไว้บน Disk ก็จะต้องรอให้ I/O ทำงานให้ ซึ่งเป็นการทำงานที่ช้ามากเมื่อเทียบกับการทำงานบน RAM ล้วน ๆ ดังนั้นระบบควรจะมี RAM อย่างเพียงพอในการทำงาน ซึ่งจะทำให้ระบบไม่ต้องทำการ Paging เลย



Instance Throughput เนื่องจาก Sessions Chart บอกเพียงว่ามีการใช้งาน CPU และการรอคอยเท่าใด แต่ไม่ได้บอกว่าการใช้งานเหล่านั้นสมเหตุสมผลเพียงใด แผนภูมินี้จะแสดงน้ำหนักของการใช้ข้อมูลบน Sessions Chart เช่นถ้าจำนวน Session ใน Sessions Chart พุ่งขึ้นสูง แต่ตัวเลขใน Throughput นี้ก็สูงขึ้นด้วย ก็อาจจะยอมรับได้ว่ามีคนเข้ามาใช้ระบบเป็นจำนวนมาก จึงทำให้ระบบทำงานมาก ซึ่งก็ถือว่าระบบไม่ได้ช้าผิดปกติแต่อย่างใด แต่อาจจะต้องพิจารณาในแง่อื่น เช่นเมื่อมี User เข้ามาใช้ระบบมากขึ้น ก็อาจจะต้องทำการปรับปรุงขนาดของฮาร์ดแวร์เป็นต้น อย่างไรก็ตามถ้า Throughput ลดต่ำลง แต่ Sessions Chart แสดงการแย่งใช้งานสูง อย่างนี้แสดงว่าอาจจะต้องทำการจูนระบบแล้ว



ในส่วนของ Active Session คลิ๊กที่ หัวข้อบน Legend ที่ต้องการดูรายละเอียดจะเข้าสู่หน้า Active Session Waiting ให้คลิ๊กที่ "User I/O"

Active Sessions Waiting: User I/O แผนภูมินี้แสดงการรอการทำงานของดิสก์ (User I/O) โดยแสดงการรอใช้งานดิสก์ของ Session ต่าง ๆ ณ ช่วงเวลาหนึ่ง ๆ เราสามารถเลือกช่วงเวลา (ห้านาที) ที่ต้องการดูรายละเอียดได้โดยคลิ๊กที่แถบด้านล่างใต้แกน X ของแผนภูมิ เมื่อคลิ๊กแล้วจะปรากฎรายละเอียดที่ด้านล่างของแผนภูมิ

Legend แสดงการรอคอยประเภทต่าง แสดงไว้เป็นสีเพื่อให้สามารถแยกความแตกต่างบนแผนภูมิได้

บริเวณด้านล่างที่แสดงรายละเอียด (Detail for Selected 5 Minute Interval) ประกอบด้วย 5 แท็ปคือ
- Overview - แสดงภาพรวมของรายละเอียด
- Top SQL – เพื่อดู SQL statement ที่ทำให้เกิดการรอคอยยาวนานที่สุด
- Top Session – เพื่อดู session ที่รอคอยยาวนานที่สุด
- Top Files – เพื่อดูไฟล์ที่มีการรอคอยใช้งานนานที่สุด
- Top Objects – แสดง object (ตาราง) ที่มีการรอใช้งานนานที่สุด

ข้อมูลที่แสดงบนแท็ปต่าง ๆ มีดังนี้
1) แท็ป Overview แสดง Top Waiting SQL (แท็ป Top SQL) และ Top Waiting Sessions (แท็ป Top Sessions)
- Top Waiting SQL แสดงคำสั่ง SQL ที่มีการรอคอยนานที่สุด คลิ๊กที่รายการที่มีค่า % มากที่สุด (ยกเว้น Other) เพื่อดูคำสั่ง และ execution plan
- Top Waiting Session แสดง session ที่มีการรอคอยสูงสุด คลิ๊กที่รายการที่มีค่า % มากที่สุด (ยกเว้น Other) เพื่อดูรายละเอียดของ session เช่น โปรแกรมที่ใช้, เวลาล็อกอิน, ประเภทของการเชื่อมต่อเป็นต้น



2) แท็ป Top SQL แสดง Top Waiting SQL และสามารถแสดงรายละเอียดของ SQL ได้ด้วยโดยการคลิ๊กที่ Legend ของคำสั่งที่มีเวลารอคอยสูงสุด (แต่ละบรรทัดของ Legend หมายถึงแต่ละคำสั่ง SQL คำสั่งที่มีการรอคอยสูงสุดจะปรากฎเป็นพื้นที่ขนาดใหญ่ที่สุดบน Pie Chart) เมื่อเราคลิ๊กที่ บรรทัดใด ๆ บน Legend เราจะสามารถดูคำสั่ง SQL และ Execution Plan ของมันได้
บริเวณทางขวามือของแท็ปนี้จะแสดงรายละเอียดของคำสั่งที่แสดงในแผนภูมิทางด้านซ้ายเพื่อให้รู้ว่าคำสั่งที่ช้านั้น ๆ ใช้เวลาไปกับอะไรเป็นส่วนใหญ่ เราสามารถคลิ๊กที่รายการในคอลัมน์ Wait Event เพื่อดูจำนวนของการรอคอยที่ระดับช่วงระยะเวลาต่าง ๆ



3) แท็ป Top Sessions ถ้ากล่าวโดยให้เห็นภาพง่าย ๆ Sessions ก็คือการเชื่อมต่อเข้าสู่ฐานข้อมูลของ Users นั่นเอง ในแท็ปนี้เราสามารถเห็นว่ามี Sessions ใดที่มีการรอคอยนานที่สุด รวมทั้งจำนวน Sessions อยู่ทางด้านซ้าย และประเภทของการรอคอยอยู่ทางด้านขวา รวมทั้งโปรแกรมและ Username ที่ใช้ในการเชื่อมต่อกับฐานข้อมูลของ Session นั้น ๆ ด้วย



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



5) แท็ป Top Objects แสดงวัตถุเช่น (ตาราง) ในฐานข้อมูลที่มีการรอคอยสูง เราสามารถใช้ข้อมูลนี้ร่วมกับแท็ป Top Files ในการจะย้ายตารางที่มีการรอคอยสูง ๆ ออกจากไฟล์ได้
บริเวณทางด้านซ้ายจะแสดงวัตถุต่าง ๆ (ส่วนใหญ่จะเป็นตาราง) ที่มีการรอคอยสูงเปรียบเทียบกันในแผนภูมิ Pie Chart ส่วนทางด้านขวาจะแสดงเวลารอคอยโดยเฉลี่ยของแต่ละวัตถุ



Enterprise Manager ยังมี Features อื่น ๆ อีกมากมายที่ช่วยในการบริหารฐานข้อมูล ด้วยความที่เป็น Graphic จึงทำให้ใช้งานได้ง่าย และการเห็นภาพหรือกราฟต่าง ๆ จะทำให้เข้าใจได้ง่าย หวังว่า Features ที่เกี่ยวกับ Performance ที่นำมาลงนี้น่าจะเป็นตัวตั้งต้นที่จะทำให้ผู้อ่านสามารถขยายผลในการศึกษาการใช้งานต่อไปโดยไม่ยาก

บทความที่เกี่ยวเนื่องกัน
1.การตรวจสอบการทำงานของ Session
2.การใช้ TKPROF เพื่อแสดงสถิติการรันของคำสั่ง SQL
3.Performance monitoring & tuning

Saturday, August 15, 2009

การกู้ข้อมูลกรณี Redo Log File ที่ไม่ได้ Active อยู่เสีย

Redo Log File มีอยู่ 3 สถานะคือ
1) Current คือตัวที่ระบบฐานข้อมูลกำลังเขียนอยู่
2) Active คือตัวที่ระบบฯ ยังต้องใช้ในการทำ Instance Recovery อยู่ และ
3) Inactive คือสถานะที่ไม่ได้ใช้งานใด ๆ แล้ว

กรณีที่ Redo Log File เสีย หากตัว (หรือกลุ่ม) ที่เสียมีสถานะเป็น Inactive จะจัดการง่ายกว่ากรณีที่เป็น Current หรือ Active โดยมีขั้นตอนในการกู้ Redo Log File ดังนี้

1) เช็คดูว่า Redo Log File ตัวที่เสียนั้นมีสถานะเป็น Inactive
2) ถ้าใช่ เคลียร์ข้อมูลใน Redo Log File นั้น
3) Open database
4) แบ็คอัพฐานข้อมูลทั้งหมด (Whole Database Backup)

ในบทความนี้เราเน้นที่การแก้ปัญหา Redo Log File เสียเมื่อโหมดในการเก็บล็อกของระบบฐานข้อมูลเป็นแบบ No Archive (ไม่มีการ Archive Redo Log มาเก็บไว้)
เมื่อ Redo Log File เสีย เราจะพบ Error Message คล้าย ๆ กับตัวอย่างข้างล่างนี้

------------------------------------------------------------
D:\> set oracle_sid=orcl2
D:\> sqlplus / as sysdba
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247876 bytes
Variable Size 83887484 bytes
Database Buffers 75497472 bytes
Redo Buffers 7139328 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\REDO03.LOG'
------------------------------------------------------------

จากตัวอย่างเรารู้ว่า Redo Log File ตัวที่มีปัญหาคือ Onlinelog 3 ซึ่งมีพาธอยู่ที่ D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL2\REDO03.LOG

เมื่อทราบว่าปัญหาเกิดจาก Redo Log File เราจะเริ่มแก้ปัญหาด้วยวิธีการที่กล่าวมา
1) เราจะเช็คดูว่า Database อยู่ในโหมด No Archive และสถานะของ Onlinelog 3 เป็น Inactive

------------------------------------------------------------
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Current log sequence 9

SQL> select group#,thread#,status from v$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 CURRENT
3 1 INACTIVE
2 1 INACTIVE
------------------------------------------------------------

2) จากผลการตรวจสอบพบว่าระบบฐานข้อมูลอยู่ในโหมด No Archive และไฟล์ที่เสีย (3) มีสถานะเป็น Inactive เราจะเคลียร์ล็อกที่อยู่ใน Redo Log File 3

------------------------------------------------------------
SQL> alter database clear logfile group 3;
Database altered.
------------------------------------------------------------

3) จากนั้นจึงเปิดระบบฐานข้อมูล

------------------------------------------------------------
SQL> alter database open;
Database altered.
------------------------------------------------------------

4) ระบบจะกลับมาเปิดใช้งานได้อีกครั้ง ข้อควรจำก็คือจะต้องทำการ Shutdown Immediate, Startup Mount และทำการแบ็คอัพทั้งฐานข้อมูล (Whole Database Backup) เก็บไว้ทันที

บทความที่เกี่ยวเนื่องกัน
1. การกู้ข้อมูลกรณี Control File เสีย

Sunday, August 2, 2009

NLS_LANG คือตัวแปร Environment บนฝั่ง Client ไม่ใช่บน Database Server

อย่างที่ผมได้ให้หัวเรื่องไว้เกี่ยวกับ NLS_LANG คือตัวแปรตัวนี้เป็นตัวแปรบนฝั่ง Client หรืออย่างน้อยก็เป็นตัวแปรของ Client Application (ซึ่งจริง ๆ แล้วอาจจะ Install ไว้บน Database Server ก็ได้) ที่จะติดต่อกับฐานข้อมูล เพื่อให้เห็นภาพลองดูวิธีการตั้งค่าตัวแปรตัวนี้บน Windows ดูกันหน่อยนะครับ

C:\>set NLS_LANG="ARABIC_UNITED ARAB EMIRATES.AR8MSAWIN"
C:\>echo %NLS_LANG%
"ARABIC_UNITED ARAB EMIRATES.AR8MSAWIN"

เรา set NLS_LANG บน client เพื่อบอกว่าขณะนี้เราจะ connect ด้วย environment แบบไหน โดยตอน select ถ้า Character Set ในตัวอย่างคือ (AR8MSAWIN) มีขนาดเล็กกว่า Database Character Set เราจะเห็นเป็น question mark หมายถึงด้วย environment ของเรา ไม่รู้จักตัวอักษรที่ database ส่งมาให้ เช่นถ้า Database Character Set เป็น UTF8 แต่เรา set NLS_LANG ที่เครื่องเป็น US7ASCII ตัวอักษรที่ส่งมาจาก Database ทีไม่อยู่ใน range ที่ US7ASCII รู้จักจะกลายเป็น ???

ในทางกลับกันตอน insert ถ้า database มี Character Set ที่เล็กกว่าของเครื่อง Client ที่ insert เช่น database เป็น US7ASCII แต่เครื่อง client set NLS_LANG=american_america.TH8TISASCII ข้อมูลภาษาไทยที่เรา insert เข้าไปจะกลายเป็น ??? แต่ถ้า database เป็น TH8TISASCII เหมือนกับเครื่อง Client (หรือเป็น Character Set ที่เป็น superset ของ TH8TISASCII จะสามารถ insert ได้ ดังตัวอย่างข้างล่าง เราConnect เข้ากับ orcl3 ซึ่งมี Character Set เป็น US7ASCII (พารามิเตอร์ชื่อ NLS_CHARACTER_SET) โดยใช้ sqlplus บน DOS Command Window

C:\>set nls_lang=american_america.th8tisascii
C:\>sqlplus oe@orcl3
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 29 15:04:40 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET US7ASCII

1 rows selected.

SQL> desc test_char;
Name Null? Type
----------------------------------------- -------- ----------------------------
CNAME NCHAR(10)
VNAME NVARCHAR2(10)

SQL> insert into test_char(cname) values ('ธนากร');
1 row created.

SQL> select * from test_char;
CNAME VNAME
---------- ----------
?????

จากตัวอย่างเรา Insert เข้าไปในตารางในฐานข้อมูลที่มี Character Set ที่เป็น US7ASCII ในขณะที่เครื่อง Client ที่ใช้ในการ Insert มี Character Set (ที่ีตั้งค่าโดย NLS_LANG) ที่มีขนาดใหญ่กว่า US7ASCII (TH8TISASCII มีขนาด 8 บิท และมีจำนวนตัวอักษรมากกว่า US7ASCII ซึ่งมีขนาด 7 บิท) ค่าที่ได้จากการ Insert ตัวอักษรที่ไม่อยู่ในชุดตัวอักษร US7ASCII เลยจึงกลายเป็น '?' ทุกตัว

คราวนี้เราลองมาทดสอบกับฐานข้อมูลที่มี Character Set ที่เป็น TH8TISASCII บ้าง (NLS_LANG ยังคงเป็น TH8TISASCII)

SQL> select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET TH8TISASCII

1 rows selected.
SQL> create table test_char (cname nchar(10), vname nvarchar2(10));
Table created.

SQL> insert into test_char (cname) values ('ธนากร');
1 row created.

SQL> select * from test_char;
CNAME VNAME
---------- ----------
ธนากร
1 row selected.

ดังนั้นเพื่อให้แน่ใจว่าจะไม่เกิดการสูญเสียข้อมูลมีข้อควรระลึกถึงเกี่ยวกับ NLS_LANG ดังนี้
1. ตั้งค่า NLS_LANG ให้เป็นตัวเดียวกับ Database Character Set เสมอ
2. ถ้าทำอย่างกรณีข้อ 1 ไม่ได้ และต้องทำ DML กับฐานข้อมูล(เช่น Insert, Update, Delete) ให้ตั้งค่า NLS_LANG ให้เป็น Subset ของ Database Character Set
3. ถ้าทำอย่างกรณีข้อ 1 ไม่ได้ และต้องทำการ Select ข้อมูลอย่างเดียว ให้ตั้งค่า NLS_LANG ให้เป็น Superset ของ Database Character Set

หมายเหตุ
1. Database Character Set สามารถดูได้จากตัวแปร 'NLS_CHARACTERSET'
2. คำสั่ง Set NLS_LANG จะมีผลต่อ Session นั้น ๆ เท่านั้น ถ้าเราปิด Command Window แล้วเปิดใหม่จะต้อง Set ค่าตัวนี้ใหม่ ถ้าต้องการให้มีผลถาวรอาจจะเข้าไปตั้งค่า Environment Variables (คลิ๊กขวาที่ My Computer เลือก Properties => คลิ๊กเลือก Advanced Tab แล้วคลิ๊ก Environment Variables)
3. โดยปกติหากเราไม่ได้ตั้งค่า NLS_LANG ค่าดีฟอลต์จะเก็บอยู่ที่ Registry ของเครื่องใน HKEY_LOCAL_MACHINE => SOFTWARE => ORACLE => KEY_OraDb10g_home1 ให้ดูที่ Registry ทางขวามือชื่อ NLS_LANG ซึ่งส่วนที่อยู่หลังจุดจะเป็น Character Set เช่น AMERICAN_AMERICA.TH8TISASCII ก็หมายความว่าเครื่อง Client นี้ (ถ้าไม่ได้ตั้งค่า NLS_LANG ดังวิธีการอื่น ๆ ที่กล่าวมา) มี Character Set เป็น TH8TISASCII

บทความที่เกี่ยวเนื่องกัน
1. การใช้ NCHAR และการกำหนด Character Sets

Friday, July 24, 2009

SQL วันเดียว

สำหรับตอนนี้ มาจากการที่มักจะมีคำถามอยู่บ่อย ๆ ว่า SQL บน Oracle ต่างจาก SQL บน SQLServer อย่างไร หรือต่างจาก SQL บน Relational Database Management System (RDBMS) ตัวอื่น ๆ อย่างไร ครั้นพอจะจัด Full Course Training ก็ดูเหมือนว่าอาจจะไม่จำเป็น เพราะคนที่ทำงานโดยใช้ SQL มา ก็น่าจะมีทักษะอยู่แล้ว แต่อยากรู้เฉพาะจุดที่แตกต่างกัน มากกว่า อย่างไรก็ตามผมคิดของผมเอาเองว่าการรู้ว่า Oracle SQL มี Features สำคัญ ๆ อย่างไร น่าจะเป็นประโยชน์กับผู้ใช้งานบ้างไม่มากก็น้อย อาจจะจำรายละเอียดไม่ได้ แต่อย่างน้อยต่อไปเมื่อต้องการใช้ก็สามารถที่จะไปค้นดูจากใน Reference ได้ต่อไป โจทย์จึงอยู่ที่ว่าทำอย่างไรจึงจะสามารถสรุปเอาเฉพาะส่วนหลัก ๆ (แต่ครอบคลุม) เนื้อหาของ SQL บน Oracle มาให้ผู้ที่ต้องการศึกษาได้ โดยใช้เวลาในการศึกษาไม่มาก จึงได้บทสรุปย่อ ๆ ขนาดเล็กมาบทหนึ่ง

ตารางที่ใช้เป็นตารางที่อยู่ใน Schema(User) HR ซึ่งเป็นดีฟอลต์ของ Oracle ที่มีมาตั้งแต่ตอนสร้างฐานข้อมูล ซึ่ง Schema(User) HR ใน Oracle10g จะล๊อคอยู่โดยดีฟอลต์ คุณจะต้องปลดล๊อคก่อนโดย ล็อกอินเข้าฐานข้อมูลด้วย User ที่มีสิทธิ์เป็น DBA แล้วใช้คำสั่ง

SQL> alter user hr account unlock;
User altered.

********************************************************************************
Chapter1: Basic SQL Statements
********************************************************************************
เราสามารถแบ่งประเภทของ SQL Statements ได้เป็น 5 ประเภทคือ
1. Data Retrieval
- SELECT
2. Data Manupulation Language (DML)
- INSERT
- UPDATE
- DELETE
- MERGE
3. Data Definition Language (DDL)
- CREATE
- ALTER
- DROP
- RENAME
- TRUNCATE
4. Transaction Control
- COMMIT
- ROLLBACK
- SAVEPOINT
5. Data Control Language (DCL)
- GRANT
- REVOKE

1. การใช้คำสั่ง SELECT โดยทั่วไป
select * from departments;
select department_id, department_name, manager_id, location_id from departments;
Note:
- คำสั่ง SQL จะใช้ตัวใหญ่หรือตัวเล็กก็ได้ (Not Case Sensitive)
2. การใช้ตัวคำนวณทางคณิตศาสตร์ เช่น + - * /
select last_name, salary, salary + 300 from employees;
Note:
- ถ้าค่าที่ใช้ในการคำนวณเป็น null ผลการคำนวณที่ได้จะเป็น null เสมอ
select last_name, job_id, salary, commission_pct, 12*salary*commission_pct from employees;
3. ชื่อย่อของ Column (Alias)
select last_name as name, commission_pct comm from employees;
select last_name "Name", salary*12 "Annual Salary" from employees;
4. การนำข้อมูลในต่างคอลัมน์มาแสดงผลต่อกัน (Concatenate)
select last_name || job_id as "Employees" from employees;
5. การพิมพ์ค่าตายตัว (Constant) ลงร่วมกับค่าที่อยู่ในคอลัมน์ (Literal Character String)
select last_name || ' : 1 Month salary = ' || salary Monthly from employees;
6. การกำจัดแถวซ้ำ
select department_id from employees;
select DISTINCT department_id from employees;
7. การดูโครงสร้างตาราง
DESC employees;
select * from tab;
select * from user_tables;
select * from user_tab_columns;


********************************************************************************
Chapter2: Restricting and sorting data
********************************************************************************
1. คิวรีเลือกเฉพาะแถวที่ต้องการตามเงื่อนไข โดยใช้เครื่องหมายเท่ากับ
select employee_id, last_name, job_id, department_id from employees where department_id = 90;
select last_name, job_id, department_id from employees where last_name = 'Whalen';
select last_name, department_id, hire_date from employees where hire_date = '03-jan-90';
2. คิวรีเลือกเฉพาะแถวที่ต้องการตามเงื่อนไข โดยใช้เครื่องหมายเปรียบเทียบอื่น ๆ (Comparison condition)
= > >= < <= <> != ^=
-- Between
select last_name, salary from employees where salary BETWEEN 2500 and 3500;
-- IN
select employee_id, last_name, salary, manager_id from employees where manager_id IN (100,101,201);
-- LIKE
select first_name from employees where first_name like 'S%';
select last_name, hire_date from employees where hire_date like '%95';
select last_name from employees where last_name like '_o%';
select employee_id, last_name, job_id from employees where job_id like '%SA\_%' escape '\'
-- NULL
select last_name, manager_id from employees where manager_id is null;
Logical condition
-- AND
select employee_id, last_name, job_id, salary from employees where salary >= 10000 and job_id like '%MAN%';
-- OR
select employee_id, last_name, job_id, salary from employees where salary >= 10000 or job_id like '%MAN%';
-- NOT
select last_name, job_id from employees where job_id not in ('IT_PROG','ST_CLERK','SA_REP');
3. การเรียงลำดับแถว (Order by)
select last_name, job_id, department_id,hire_date from employees order by hire_date desc;
select employee_id, last_name, salary*12 annsal from employees order by annsal;
select last_name, department_id, salary from employees order by department_id, salary desc;

********************************************************************************
Chapter3: ฟังก์ชั่นที่ใช้กับข้อมูลแต่ละแถว (Single Row Functions)
********************************************************************************
1. ฟังก์ชันที่ใช้กับตัวอักษร Character functions
- Case manipulation functions: LOWER, UPPER, INITCAP
select 'The job id for ' || Initcap(first_name) || ' ' || UPPER (last_name) || ' is ' || LOWER(job_id) as "EMPLOYEE DETAILS" from employees;
select employee_id, last_name, department_id from employees where last_name = 'higgins';
select employee_id, last_name, department_id from employees where LOWER(last_name) = 'higgins';
select employee_id, last_name, department_id from employees where INITCAP(last_name) = 'Higgins';
- Character-manipulation functions: CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM
select employee_id, concat(first_name, last_name) name, job_id, LENGTH(last_name), INSTR(last_name,'n',1,2) "Contains 'a'?" from employees where SUBSTR(job_id,4) = 'REP';
select employee_id, concat(first_name, last_name) name, job_id, LENGTH(last_name), INSTR(last_name,'a') "Contains 'a'?" from employees where SUBSTR(last_name,-1,1) = 'n';

2. ฟังก์ชันที่ใช้กับตัวเลข (Number functions)
--ROUND
select ROUND(45.923,2), ROUND(45.923,0), ROUND(45.923,-2) from dual;
--TRUNC
select TRUNC(45.923,2), TRUNC(45.923), TRUNC(45.923,-1) from dual;
--MOD
select last_name, salary, MOD(salary,5000) from employees where job_id = 'SA_REP';

3. ฟังก์ชั่นที่ใช้กับวันที่ (Date functions)
select last_name, hire_date from employees where last_name like 'G%';
select SYSDATE from dual;
-- Arithmetic with Dates
select sysdate +1, sysdate -1, sysdate - date '2009-05-20', sysdate + 1/24 from dual;
select last_name, (sysdate - hire_date) / 7 as weeks from employees where department_id = 90;
-- Date Functions

select MONTHS_BETWEEN(date '1995-09-01',date '1994-01-11') from dual;
select ADD_MONTHS (date '1994-01-11',6) from dual; -- Add 6 months
select NEXT_DAY (sysdate,'FRIDAY') from dual; -- Next Friday
select LAST_DAY (sysdate) from dual;-- Last day of month

select employee_id, hire_date, MONTHS_BETWEEN(sysdate, hire_date) hire_month, ADD_MONTHS(hire_date,6) review, NEXT_DAY(sysdate,'FRIDAY'), LAST_DAY(hire_date) from employees
where months_between (SYSDATE, hire_date) > 175;

select
ROUND(sysdate,'MONTH') ,
ROUND(sysdate,'YEAR') ,
TRUNC(sysdate,'MONTH') ,
TRUNC(sysdate,'YEAR')
from dual;
select employee_id, hire_date, ROUND(hire_date,'MONTH'), TRUNC(hire_date, 'MONTH') from employees where hire_date like '%97';

4. ฟังก์ชั่นที่ใช้เพื่อแปลงชนิดของข้อมูล (Data type conversion functions)
-- การแปลงแบบปริยาย (Implicit conversion)
VARCHAR2 or CHAR => NUMBER
VARCHAR2 or CHAR => DATE
NUMBER => VARCHAR2
DATE => VARCHAR2
-- การแปลงแบบจงใจ (Explicit conversion)
Convert to Character: TO_CHAR
can convert number,date to character
Convert to Number: TO_NUMBER
can convert character to number
Convert to Date: TO_DATE
can convert character to date

select employee_id, TO_CHAR(hire_date,'MM/YY') "Month Hire" from employees where last_name = 'Higgins';
-- ส่วนประกอบของรูปแบบการแสดงผลวันที่ (Elements of the Date format)
YYYY = Full year in numbers
YEAR = Year spelled out
MM = 2-digit month
MONTH = Full name month
MON = 3-letter month
DAY = Full name day
DY = 3-letter day of week
DD = Numeric day of month
Q = Quarter of year
WW = Week of year
W = Week of month
DDD,DD,D = Day of year, month, week
J = Julian day
select TO_CHAR(sysdate,'DAY') from dual;

-- ส่วนประกอบของรูปแบบการแสดงผลเวลา (Time elements)
HH24:MI:SS AM เช่น 15:45:32 PM
DD "of" MONTH เช่น 12 of OCTOBER
AM or PM = Meridian indicator
A.M. or P.M.
HH or HH12 or HH24 = Hour of day
MI = Minute
SS = Second
SSSSS = Seconds past midnight (0-86399)
TH = แสดงลำดับ
SP = แสดงคำอ่าน
SPTH or THSP = แสดงคำอ่านของลำดับ
เช่น ddSPTH แปลงเป็นคำอ่านของวันที่แบบมีลำดับเช่น Fourteenth
select to_char(sysdate,'DDspTH') from dual;
select last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') as hiredate from employees;
select last_name, TO_CHAR(hire_date, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') hiredate from employees;
เปลี่ยนภาษาในการแสดงผลวันที่
alter session set NLS_DATE_LANGUAGE=THAI;
แก้เป็นปีพุทธศักราช
alter session set NLS_CALENDAR='THAI BUDDHA';
- ใช้ TO_CHAR กับ number
TO_CHAR
9 = Numeric position
0 = Leading Zero
$ = Floating dollar sign
L = Floating local currency
MI = Minus signs ทางขวา
PR = วงเล็บครอบเลขติดลบ

select salary, TO_CHAR(salary, '$0099,9999.00') x_salary from employees where last_name = 'Ernst';
- Nesting functions
select last_name, manager_id, NVL(TO_CHAR(manager_id), 'No Manager') from employees where manager_id is null;
select TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date,6), 'FRIDAY'),'fmDay, Month DDth, YYYY') "Next 6 Month Review" from employees order by hire_date;

5. ฟังก์ชั่นอื่น ๆ
NVL (expr1,expr2) ถ้า expr1 เป็น null จึง expr2 ไม่งั้นเป็น expr1
select last_name, salary, commission_pct, (salary*12) + (salary*12*commission_pct) an_sal from employees;
select last_name, salary, NVL(commission_pct,0), (salary*12) + (salary*12*NVL(commission_pct,0)) an_sal from employees;
-- NVL2 (expr1,expr2,expr3) ถ้า expr1 เป็น null จึง expr3 ไม่งั้นเป็น expr2
select last_name, salary, commission_pct, NVL2(commission_pct,'SAL+COMM','SAL') income from employees where department_id in (50,80);
-- NULLIF (expr1,expr2) ถ้า expr1 = expr2 จึงให้มีค่าเป็น null ไม่งั้นเป็น expr1
select first_name, length(first_name) "expr1", last_name, length(last_name) "expr2", NULLIF(length(first_name), length(last_name)) result from employees;
-- COALESCE แสดงค่าที่เป็น Not Null ตัวแรกของสมาชิกในกลุ่มของ Expression
select last_name, commission_pct,salary,10, COALESCE(commission_pct, salary, 10) comm from employees order by commission_pct;

6. การใช้เงื่อนไขในคิวรี (Condition expression)
-- การใช้ CASE
CASE expr
WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
เช่น
select last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
from employees;

-- การใช้ DECODE
DECODE (col | expression, search1, result1[, search2, result2,...,][,default])
select last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary) revise_salary
from employees;

select last_name, salary,
DECODE(trunc(salary/2000,0),
0,0.00,
1,0.09,
2,0.20,
3,0.30,
4,0.40,
5,0.42,
6,0.44,
0.45) tax_rate
from employees
where department_id = 80;

********************************************************************************
Chapter4: การ Join
********************************************************************************
-- ใช้คำสั่งแบบ Oracle
select table1.column, table2.column from table1, table2 where table1.column1=table2.column2;
select table1.column, table2.column from table1 join table2 on (table1.column1 = table2.column2);
1. Cartesian products
select last_name from employees;
107 rows
select department_name from departments;
207 rows
select last_name, department_name from employees, departments
2889 rows

2. Equijoins
select employees.employee_id, employees.last_name, employee.department_id, departments.department_id
, departments.location_id
from employees , departments
where employees.department_id = departments.department_id;
-- Table alias
select e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
from employees e, departments d
where e.department_id = d.department_id;
- Join more than 2 tables
select e.last_name, d.department_name, l.city
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id;

3. Non-Equijoins
select * from employees;
select * from jobs;
select e.last_name, e.salary, j.job_title,min_salary || ' - ' || max_salary
from employees e, jobs j
where e.salary between j.min_salary and j.max_salary;

4. Outer Joins
- เครื่องหมายบวก '(+)' อยู่ข้างที่ข้อมูลขาดหายไป (เป็น NULL)
select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id;
ุุหรือใช้ SQL1999
select e.last_name, e.department_id, d.department_name
from employees e
left outer join departments d
on (d.department_id = e.department_id);
select * from employees;

5. Self Joins
- การที่ตาราง Join กับตัวมันเอง ใช้ในกรณีที่มีความสัมพันธ์เป็นลำดับชั้นในตาราง เช่น หัวหน้า-ลูกน้องเป็นต้น
select worker.last_name || ' works for ' || manager.last_name
from employees worker, employees manager
where worker.manager_id = manager.employee_id;


- ใช้คำสั่ง SQL มาตรฐาน (SQL1999)
1. Cross Joins (Cartesian Product)
select last_name, department_name
from employees CROSS JOIN departments;

2. Natural Joins
select department_id, department_name, location_id, city
from departments NATURAL JOIN locations;

3. Using
select e.employee_id, e.last_name, d.location_id
from employees e JOIN departments d USING (department_id)
where location_id = 1400;

4. Inner Join with 'ON'
select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
from employees e JOIN departments d
ON (e.department_id = d.department_id);

5. Three-Way Joins with 'ON'
select employee_id, city, department_name
from employees e
JOIN departments d on d.department_id = e.department_id
JOIN locations l on d.location_id = l.location_id;

6. Left(Right) Outer Join
select e.last_name, e.department_id, d.department_name
from employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

select e.last_name, e.department_id, d.department_name
from employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id)

7. Full Outer Join
-- การ Join ที่แสดงค่าว่างของทั้งข้างซ้ายและขวาขึ้นมา เหมือนการเอา Left กับ Right Join มารวมกัน
select e.last_name, e.department_id, d.department_name
from employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);

8. Additional Condition
select e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
from employees e JOIN departments d
ON (e.department_id = d.department_id) AND e.manager_id = 149;

********************************************************************************
Chapter5: ฟังก์ชั่นที่ใช้กับข้อมูลเป็นกลุ่มของแถว (Group Functions)
********************************************************************************
1. การใช้ฟังก์ชัน
AVG([DISTINCT | ALL] n) ไม่เอา Null (ถ้าข้อมูลเป็น NULL ไม่เอามาคำนวณ)
COUNT({* | [DISTINCT | ALL] expr}) ถ้าเป็น * เอา Null (ถ้าไม่ใช้ * ไม่เอา NULL)
MAX([DISTINCT | ALL] expr) ไม่เอา Null
MIN([DISTINCT | ALL] expr) ไม่เอา Null
STDDEV([DISTINCT | ALL] x) ไม่เอา Null
SUM([DISTINCT | ALL] n) ไม่เอา Null
VARIANCE([DISTINCT | ALL] x) ไม่เอา Null

-- ตัวอย่าง ===========================================
select AVG(salary), MAX(salary), MIN(salary), SUM(salary)
from employees
where job_id like '%REP%';

select MIN(hire_date), MAX(hire_date) from employees;

select COUNT(*) from employees where department_id = 50;
-- 45
select COUNT(commission_pct) from employees where department_id = 80;
-- 34
select COUNT(distinct department_id) from employees;
-- 11
select AVG (commission_pct) from employees;
-- คำนวณเฉพาะ Rows ที่มี commission_pct ไม่เป็น NULL เท่านั้น คือเอา Rows ที่ commisiton_pct ไม่เป็น Null แล้วหาค่าเฉลี่ยจาก Rows เหล่านี้
select AVG(nvl(commission_pct,0)) from employees;

2. การใช้ฟังก์ชั่น กับ GROUP BY
select department_id, AVG(salary) from employees GROUP BY department_id ORDER BY AVG(salary);
- Group มากกว่า 1 คอลัมน์
select department_id dept_id, job_id, sum(salary)
from employees
GROUP BY department_id, job_id;
- เงือนไขในการ Group (HAVING)
select department_id, AVG(salary)
from employees
GROUP BY department_id
HAVING AVG(salary) > 8000
- More
select job_id, sum(salary) payroll
from employees
where job_id not like '%REP%'
group by job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);
- Nesting Group
select MAX(AVG(salary)) from employees group by department_id;

********************************************************************************
Chapter6: การใช้ Subquery
********************************************************************************
-- Single-row (ส่วนที่เป็น Subquery ให้ผลเพียงเรคคอร์ดเดียว)

select last_name, job_id from employees
where job_id =
(select job_id from employees where employee_id = 141);

select last_name, job_id, salary from employees
where job_id =
(select job_id from employees where employee_id = 141)
and salary >
(select salary from employees where employee_id = 143);

select last_name, job_id, salary from employees
where salary =
(select min(salary) from employees);

select department_id, min(salary)
from employees
group by department_id
having min(salary) >
(select min(salary) from employees where department_id = 50);

-- Multi-row (ส่วนที่เป็น Subquery ให้ผลหลายเรคคอร์ด)
select last_name, salary, department_id
from employees
where salary IN
(select min(salary) from employees group by department_id);

-- การใช้ ANY
select employee_id, last_name, job_id, salary
from employees
where salary < ANY
(select salary from employees where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';
-- ข้อมูลในตาราง EMPLOYEES เอาเรคคอร์ดใด ๆ ที่ไม่ได้เป็น Programmer (IT_PROG) และมีค่า SALARY น้อยกว่า SALARY ใด ๆ ในตาราง EMPLOYEES ที่เป็น Programmer (IT_PROG) หรืออาจจะกล่าวอีกอย่างได้ว่า "เอาพนักงานในแผนกอื่นที่มีเงินเดือนน้อยกว่าโปรแกรมเมอร์ที่มีเงินเดือนมากที่สุด"

select employee_id, last_name, job_id, salary
from employees
where salary < ALL
(select salary from employees where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';
-- ข้อมูลในตาราง EMPLOYEES เอาเรคคอร์ดใด ๆ ที่ไม่ได้เป็น Programmer (IT_PROG) และมีค่า SALARY น้อยกว่า SALARY ทุก SALARY ในตาราง EMPLOYEES ที่เป็น Programmer (IT_PROG) หรืออาจจะกล่าวอีกอย่างได้ว่า "เอาพนักงานในแผนกอื่นที่มีเงินเดือนน้อยกว่าโปรแกรมเมอร์ที่มีเงินเดือนน้อยที่สุด"

********************************************************************************
Chapter7: การเปลี่ยนแปลงข้อมูล (Manipulation Data)
********************************************************************************
1. การแทรกแถวข้อมูล Insert
INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (700, 'Public Relations', 100, 1700);

INSERT INTO departments (department_id, department_name)
VALUES (700, 'Public Relations');

INSERT INTO departments
VALUES (700, 'Public Relations',null, null);

- การแทรกแถวด้วยข้อมูลพิเศษ (เช่นวันที่ปัจจุบัน (SYSDATE))
INSERT INTO employees (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id)
VALUES (999, 'Louis', 'Popp', 'LPOPP1', '515.124.4567', SYSDATE, 'AC_ACCOUNT', 6900, NULL, 205, 100);

- การแทรกแถวด้วยข้อมูลที่เป็นวันที่
INSERT INTO employees
VALUES (1999,'Den','Raphealy','xDraphel','515.127.4561', TO_DATE('1999 12', 'YYYY MM'), 'AC_ACCOUNT', 11000, null, 100,30);

2. การแทรกแถวข้อมูลโดยเอาแถวข้อมูลจากอีกตารางหนึ่ง
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
from employees
where job_id like '%REP%';

3. การเปลี่ยนแปลงข้อมูล
UPDATE employees SET department_id = 70
WHERE employee_id = 113;

UPDATE employees
SET job_id = (select job_id from employees where employee_id = 205),
salary = (select salary from employees where employee_id = 205)
WHERE employee_id = 114;

UPDATE employees e
SET salary = (select max_salary from jobs j where j.job_id = e.job_id)
WHERE job_id like '%REP';

4. การลบแถวข้อมูล
DELETE from departments
WHERE department_name = 'Finance';

DELETE employees
WHERE department_id = (select department_id from departments where department_name like '%Public%');
-- เมื่อรันคำสั่งแล้ว เมื่อคิวรีด้วย Subquery ที่อยู่ใน Insert into จะต้องปรากฎเรคคอร์ดนั้นด้วย ถ้าไม่ จะขึ้น Error
insert into
(select employee_id, last_name, email, hire_date, job_id, salary,department_id from employees where department_id = 50 WITH CHECK OPTION)
values (999998, 'Smith','JSMITH',to_date('07-JUN-99','dd-mon-rr'),'ST_CLERK',5000,40);

5. การใช้ค่าดีฟอลต์ที่กำหนดไว้ในโครงสร้างตาราง
update jobs set max_salary = DEFAULT where job_id = 'IT_PROG';

6. การใช้ MERGE (เปรียบเทียบค่าในคอลัมน์ของตารางหนึ่งกับของอีกตารางหนึ่ง ถ้า Match (เหมือนกัน) ก็ทำอย่างหนึ่ง หรือถ้าไม่ Match ก็ทำอีกอย่าง)
MERGE INTO employees e
using copy_emp c
on (e.employee_id = c.employee_id)
WHEN MATCHED THEN
UPDATE SET
e.salary = e.salary + c.extra_bonus
WHEN NOT MATCHED THEN
INSERT VALUES (c.employee_id,c.first_name,c.last_name,c.email,c.phone_number,c.hire_date,c.job_id,c.salary,c.commission_pct,c.manager_id,c.department_id);

7. การควบคุมธุรกรรม (Transaction Control)
Controlling Transactions
- Session A ==========================================
select * from employees where employee_id = 100 for update;
- Session B ==========================================
update employees set salary = salary + 2 where employee_id =100;
-- Transaction hang
- Session A ==========================================
update employees set salary = salary + 1 where employee_id =100;
commit;
- Session B ==========================================
-- Transaction successful wait for DCL

- SAVEPOINT ช่วยให้สามารถ Rollback ถึงเพียงบางจุด ไม่ต้อง Rollback ทั้งหมด
select * from regions;
insert into regions values (5,'Africa');
select * from regions;
SAVEPOINT before_lunch;
insert into regions values (6,'South East Asia');
select * from regions;
ROLLBACK TO before_lunch;
select * from regions;
ROLLBACK;
select * from regions;

********************************************************************************
Chapter8: การสร้างและจัดการตาราง
********************************************************************************
CREATE TABLE EMPLOYEE
(empid NUMBER(10)
,lastname VARCHAR2(25)
,firstname VARCHAR2(25)
,salary NUMBER(10,4) DEFAULT 10)
TABLESPACE users;

select * from user_tables;
select * from user_catalog;
select * from cat;

-- ชนิดของข้อมูลที่มีใน Oracle
VARCHAR2(size) => Variable-length character; size is a maximum size (upto 4000 bytes)
CHAR(size) => Fixed-length character; size is a maximum size
NUMBER(p,s) => p is precision จำนวนหลักทั้งหมดรวมตัวเลขหลังจุดทศนิยมแต่ไม่รวมจุด and s is scale จำนวนหลักของตัวเลขหลังจุดทศนืืยม
DATE => Date and time values
CLOB => Character data upto 4GB
BLOB => Binary data upto 4 GB
BFILE => Binary data ที่เก็บในไฟล์ที่อยู่นอก Database ขนาดใหญ่สุด 4GB
ROWID => เลขฐานสิบหกที่ใช้ชี้ที่อยู่ของแถว ในฐานข้อมูล
TIMESTAMP => วันที่ รวมกับเศษส่วนของวินาที
INTERVAL YEAR TO MONTH => เก็บช่วงเวลาเป็นปีและเดือน
INTERVAL DAY TO SECOND => เก็บช่วงเวลาเป็นวันและชั่วโมง,นาที และวินาที
เช่น
create table test_time (time_begin TIMESTAMP(6));
select * from test_time
insert into test_time select hire_date from employees;
commit;
select time_begin from test_time;
-- สร้างตารางที่ใส่จำนวนปีได้ถึง 3 หลัก
drop table test_time2;
create table test_time2 (time_duration INTERVAL YEAR (3) TO MONTH);
-- Insert ข้อมูลเข้าไป 120 เดือน ตรง MONTH(2) ถ้าใส่ 3 หรือตัด (2) ออก ก็จะได้ค่าเท่ากัน ตราบใดที่ค่าที่ใส่เข้าไปยังอยู่ใน YEAR(3) หรือ 999 ปีก็ใช้ได้
insert into test_time2 (time_duration) values (INTERVAL '120' MONTH(3));
select * from test_time2;
select sysdate + time_duration from test_time2;
insert into test_time2 (time_duration) values (INTERVAL '120' YEAR(3));
select sysdate + time_duration from test_time2;
insert into test_time2 (time_duration) values (INTERVAL '10-2' YEAR(2) TO MONTH);
select sysdate + time_duration from test_time2;

drop table test_time3;
create table test_time3 (time_duration INTERVAL DAY (1) TO SECOND);
insert into test_time3 (time_duration) values (INTERVAL '3' DAY (3));
insert into test_time3 (time_duration) values (INTERVAL '3' HOUR (3));
select * from test_time3;
select sysdate + time_duration from test_time3;

--Create table by using a subquery
CREATE TABLE dept80
as
select * from employees where department_id = 80;

-- Alter table
ALTER TABLE dept80 ADD new_col varchar2(20);
ALTER TABLE dept80 MODIFY new_col char(5);
ALTER TABLE dept80 set unused column new_col;
select * from user_unused_col_tabs;
ALTER TABLE dept80 drop unused column;
-- Drop table
DROP TABLE dept80;
-- Flashback เอาตารางที่ Drop ไปแล้วกลับมา
select * from dept80;
FLASHBACK TABLE dept80 TO BEFORE DROP;
-- Rename table
RENAME test_time to test_time1;
-- Truncate table
TRUNCATE TABLE test_time1;

********************************************************************************
Chapter9: การใช้ Constraints
********************************************************************************
drop table employee2;
-- การสร้าง Constraints พร้อม ๆ กับตาราง
create table employee2
(
empid number(10) UNIQUE
,lastname number(25) NOT NULL
,firstname number(25) NOT NULL
,salary number(10,4) CHECK (salary > 100)
,department_id number(4)
)
tablespace users;

select * from user_constraints where table_name = 'EMPLOYEE2'
select * from user_cons_columns where table_name = 'EMPLOYEE2';

-- เพิ่ม Constraints ทีหลัง
ALTER TABLE employee2 ADD CONSTRAINT employee_pk PRIMARY KEY (employee_id);
ALTER TABLE employee2 ADD CONSTRAINT employee_uq UNIQUE (first_name,last_name);
ALTER TABLE employee2 ADD CONSTRAINT employee_ck CHECK (salary > 0);

ALTER TABLE employee2 ADD CONSTRAINT employee_fk FOREIGN KEY (department_id)
REFERENCES departments (department_id)
ON DELETE CASCADE NOVALIDATE;
-- NOVALIDATE คือถ้ามีข้อมูลเก่าที่ละเมิดกฎอยู่ก็ไม่เป็นไร แต่ข้อมูลใหม่จากนี้ไปจะถูกบังคับด้วยกฎของ FK
-- สามารถใช้ ON DELETE SET NULL ก็ได้

ALTER TABLE employee2 DISABLE CONSTRAINT employee_fk;
ALTER TABLE employee2 ENABLE CONSTRAINT employee_fk;

ALTER TABLE employee2 DROP CONSTRAINT CASCADE CONSTRAINTS;
ALTER TABLE departments DROP CONSTRAINT dept_id_pk CASCADE;

select * from USER_CONSTRAINTS;
select * from USRE_CONS_COLUMNS;

********************************************************************************
Chapter10: การสร้างวิว (Creating Views)
********************************************************************************
CREATE OR REPLACE VIEW empvu80
AS select employee_id, last_name, salary
from employees
where department_id = 80;

CREATE OR REPLACE VIEW salvu50
AS select employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY
from employees
where department_id = 50;

CREATE OR REPLACE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary), MAX(e.salary), AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;

DROP VIEW dept_sum_vu;

-- Inline Views คือแทนตารางด้วยคิวรีในคำสั่ง
select a.last_name, a.salary, a.department_id, b.maxsal
from employees a, (SELECT department_id, max(salary) maxsal
FROM employees
GROUP BY department_id) b
where a.department_id = b.department_id
and a.salary < b.maxsal;

-- Top-N Analysis
-- การสร้างลำดับที่ และคิวรีเอาเฉพาะลำดับที่ ๆ ต้องการ
select ROWNUM as RANK, last_name, salary
from (select last_name, salary from employees order by salary desc)
where rownum <= 3;

select a.rank, a.last_name, a.salary from
(
select ROWNUM as RANK, last_name, salary
from (select last_name, salary from employees order by salary desc)
where rownum <=15
) a
where a.rank >=10;

********************************************************************************
Chapter11: การสร้างเลขอัตโนมัติ (Sequences)
********************************************************************************
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 320
MAXVALUE 9999
NOCACHE
NOCYCLE;

-- การตรวจดู sequences
select sequence_name, min_value, max_value, increment_by, last_number
from user_sequences;

-- การใช้งาน sequences
insert into departments (department_id, department_name, location_id)
values (DEPT_DEPTID_SEQ.NEXTVAL,'Support', 2500);

select dept_deptid_seq.currval from dual;

-- ปัญหาการเกิดการข้ามหมายเลขใน sequences
เมื่อมีการใช้ Cache จะทำให้ระบบทำงานได้เร็วขึ้น แต่ก็อาจจะทำให้เกิดการข้ามหมายเลขได้ ถ้ามีการ Rollback หรือระบบ Down, เลขที่อยู่ใน Cache ก็จะหายไปหมด

-- การแก้ไข sequences จะแก้ได้ถ้าไม่ทำให้เกิดขัดแย้งกับเลขที่ได้รันไปแล้ว เช่นข้างล่างนี้ OK
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
CACHE 20
NOCYCLE;

-- แต่ข้างล่างนี้จะ Error ถ้ามีการแก้ให้ไปเริ่มต้นที่ค่าใหม่ (START WITH) เมื่อแก้ไม่ได้ก็ต้อง Drop และสร้าง Sequence ตัวใหม่
alter sequence dept_deptid_seq
increment by 20
maxvalue 999999
cache 20
nocycle
START WITH 20;

-- การลบ sequence
DROP SEQUENCE dept_deptid_seq;

********************************************************************************
Chapter12: การใช้ Index เพื่อเพิ่มประสิทธิภาพของคิวรี
********************************************************************************
-- การสร้าง Index
create index emp_last_name_idx on employees (last_name);
select * from employees where last_name = 'ABF'

drop index emp_last_name_idx;
create unique index emp_last_name_idx on employees (first_name,last_name);

select * from employees where first_name = 'ABF'

-- ควรจะใช้อินเด็กซ์ก็ต่อเมื่อ
1. มีความหลากหลายของค่าในคอลัมน์
2. มี Null จำนวนมาก
3. ถูกใช้ใน Where clause บ่อย
4. ตารางมีขนาดใหญ่ และคิวรีส่วนใหญ่จะดึงข้อมูลมาประมาณ 2-4 % ของ Rows ทั้งหมด
-- ไม่ควรใช้อินเด็กซ์เมื่อ
1. ตารางเล็ก
2. ข้อมูลไม่ได้ถูกใช้ใน Where Clause
3. คิวรีส่วนใหญ่ดึงข้อมูลมาเกิน 2 ถึง 4 เปอร์เซ็นต์ของจำนวน Rows ทั้งหมด
4. ข้อมูลถูก Update บ่อยมาก
5. คอลัมน์ที่ถูก Index เวลาถูกใช้ใน Where Clause จะเป็นส่วนหนึ่งของ Expression

SELECT * FROM EMPLOYEES WHERE TRUNC(HIRE_DATE) = DATE '2009-01-01';
create index idx_employees on employees (trunc(hire_date))

-- อินเด็กซ์ที่ใช้ร่วมกับฟังก์ชั่น (Function-Based Indexes)
create index upper_dept_name_idx on departments (UPPER(department_name));
-- บน SQLPlus ใช้คำสั่งนี้เพื่อให้ผลที่ได้เป็น Explain Plan แทนที่จะเป็นแถวข้อมูล
set autotrace traceonly explain
select * from departments where upper(department_name) = 'SALES';

-- การลบ Index
drop index upper_dpt_name_idx;

-- การใช้ Synonym เพื่อทำปลอกครอบชื่อตาราง เราสามารถอ้างอิงถึงตารางโดยชื่อตารางโดยตรง หรือโดยใช้ Synonym ก็ได้
create synonym d_sum for dept_sum_vu;
drop synonym d_sum;

********************************************************************************
Chapter13: การใช้ SET OPERATIONS
********************************************************************************
-- UNION เอามาทั้งสองชุด ที่ซ้ำกันไม่เอา
select employee_id, job_id
from employees
UNION
select employee_id, job_id
from job_history;

-- UNION ALL เอามาทั้งสองชุด มีเท่าไรเอามาให้หมด
select employee_id, job_id, department_id
from employees
UNION ALL
select employee_id, job_id, department_id
from job_history
order by employee_id;

-- INTERSECT เอาตัวที่มีอยู่ในทั้งสองชุด
select employee_id, job_id
from employees
INTERSECT
select employee_id, job_id
from job_history;

-- MINUS เอามาเฉพาะตัวที่อยู่ในชุดแรก และไม่อยู่ในชุดที่สอง
select employee_id, job_id
from employees
MINUS
select employee_id, job_id
from job_history;

select current_timestamp from dual
select sysdate from dual;
select dbtimezone from dual;
select extract (year from sysdate) from dual;
select extract (month from sysdate) from dual;
select extract (day from sysdate) from dual;
select hire_date, hire_date + to_yminterval ('01-02') as hire_date_ymininterval
from employees where department_id = 20;

********************************************************************************
Chapter14: เพิ่มเติมกับการใช้ GROUP BY
********************************************************************************
select department_id, job_id, SUM(salary), COUNT(employee_id)
from employees GROUP BY department_id, job_id;

-- ใช้ HAVING เพื่อ Filter เอาผลที่ต้องการ
select department_id, AVG(salary)
from employees
GROUP BY department_id
HAVING AVG(salary) > 9500;

select department_id, job_id, SUM(salary)
from employees
group by department_id, job_id

-- ใช้ ROLLUP เพื่อให้ทำการ Sum ผลลัพธ์ในแต่ละ Group โดยอัตโนมัติ
select department_id, job_id, SUM(salary)
from employees
where department_id < 60
GROUP BY ROLLUP (department_id, job_id);

-- ใช้ CUBE เพื่อให้แสดงทุก ๆ ความเป็นไปได้ของผลลัพธ์ในมิติต่าง ๆ
select department_id, job_id, SUM(salary)
from employees
where department_id < 60
GROUP BY CUBE (department_id, job_id);

-- ฟังก์ชัน Grouping ใช้เพื่อให้ผลการ Group ดูง่ายขึ้น
-- 0 หมายถึงรายละเอียด
-- 1 หมายถึงสรุป
select department_id DEPTID, job_id JOB,
SUM(salary),
GROUPING (department_id) GRP_DEPT,
GROUPING (job_id) GRP_JOB
from employees
where department_id < 50
GROUP BY ROLLUP(department_id, job_id);

********************************************************************************
Chapter15: Advanced Subqueries
********************************************************************************
-- Subqueries
select last_name, job_id, salary
from employees
where salary < (select avg(salary)
from employees);
-- Pairwise Comparison subquery
-- ใน Where Clause มีคอลัมน์มากกว่าหนึ่ง
select employee_id, manager_id, department_id
from employees
where (manager_id, department_id) in
(select manager_id, department_id from employees where employee_id in (178,174))
and employee_id not in (178,174);

-- Nonpairwise Comparison subquery
-- คิวรีนี้ให้ผลเหมือนกับคิวรี Pairwise ข้างบน สังเกตว่าคอลัมน์ทั้งสอง (manager_id,department_id) ถูกแยกออกเป็นสองเงื่อนไข และใช้ Subquery คนละตัวกัน
select employee_id, manager_id, department_id
from employees
where manager_id in
(select manager_id from employees where employee_id in (178,174))
and department_id in
(select department_id from employees where employee_id in (178,174))
and employee_id not in (178,174);

-- Subquery in the FROM Clause
-- คิวรีข้างล่างแทนที่จะใช้สองตาราง Join กัน กลับใช้ 1 ตาราง กับอีก 1 คิวรี (ซึ่งใช้แทนตาราง) มา Join กันแทน
select a.last_name, a.salary, a.department_id, b.salavg
from employees a, (select department_id, AVG(salary) salavg from employees group by department_id) b
where a.department_id = b.department_id
and a.salary > b.salavg;

-- Scalar Subquery Expressions
-- เป็น Subquery ที่จะให้ค่าที่ Select ขึ้นมาเพียงหนึ่งค่า (หนึ่งแถวและหนึ่งคอลัมน์ ค่าเดียว จึงเรียกว่า Scalar)
-- เราใช้ Scalar Subquery แทนการ Join โดยเฉพาะ Outer Join
select last_name,d.department_name from employees e, departments d where e.department_id = d.department_id (+)
-- จากตัวอย่างเรา Select จากตาราง EMPLOYEES เท่านั้น ไม่ต้องทำ Outer Join กับ DEPARTMENTS เราจึงแน่ใจว่าคิวรีข้างล่างจะแสดงผลทุกแถวของ EMPLOYEES เสมอ
select last_name,(select department_name from departments d where d.department_id = e.department_id) dept_name from employees e
-- In CASE Expressions
-- เราสามารถใช้ Scalar Subquery ใน Case Expression ได้เช่นกัน
select employee_id, last_name
, (case
when department_id =
(select department_id from departments where location_id = 1800)
then 'Canada' else 'USA' end) location
from employees;

-- ORDER BY Expressions
-- เราสามารถใช้ Scalar Subquery ใน ORDER BY Clause ก็ได้
-- ตัวอย่างข้างล่างเป็นการ ORDER BY department_name แม้ว่าข้อมูลที่ Select ขึ้นมาจะไม่มีคอลัมน์นี้ (และในตาราง EMPLOYEES ก็ไม่มีคอลัมน์นี้)
select employee_id, last_name, department_id
from employees e
ORDER BY (select department_name from departments d where e.department_id = d.department_id);
-- ลอง Select ดูจากตาราง DEPARTMENTS โดย Order by DEPARTMENT_NAME
select department_name,department_id from departments order by department_name;

-- Correlated Subqueries
-- คือ Subquery ที่จะ Return ค่าที่ผันแปรตามค่าในเรคคอร์ดของคิวรีหลัก
select last_name, salary, department_id
from employees outer
where salary > (select avg(salary)
from employees
where department_id = outer.department_id);

-- Another example
select employee_id, last_name, e.job_id
from employees e
where 2 <= (select count(*)
from job_history
where employee_id = e.employee_id);

-- EXISTS Operators
-- เราใช้ EXISTS แทน IN และ NOT EXISTS แทน NOT IN
select * from departments for update
select distinct department_id from employees
select * from departments

select distinct department_id from departments where department_id not in (select nvl(department_id,0) from employees )
select department_id from departments d where not exists (select 'I love you' from employees e where e.department_id = d.department_id)

select * from employees e where e.department_id not in (select department_id from departments d)
select * from departments where department_id like '1%'
select * from departments d
where not exists
(select 1 from employees e where e.department_id = d.department_id)

select employee_id, last_name, job_id, department_id
from employees outer
where exists (select 0 from employees where manager_id = outer.employee_id);

select employee_id, last_name, job_id, department_id
from employees outer
where exists (select 0 from employees where manager_id = outer.employee_id);

select department_id, department_name
from departments d
where department_id not exists (select 0 from employees where department_id = d.department_id);

-- Correlated Update
-- การ Update ค่าในตารางหลัก โดยคิวรีเอาค่าจากตารางอื่นที่มีความสัมพันธ์กับค่าในบางคอลัมน์ของตารางหลัก
alter table employees add (department_name varchar2(14));

update employees e
set department_name = (select department_name
from departments d
where e.department_id = d.department_id);

-- Correlated Delete
-- การ Delete ค่าในตารางหลัก โดยคิวรีเอาค่าจากตารางอื่นที่มีความสัมพันธ์กับค่าในบางคอลัมน์ของตารางหลัก
delete from employee e
where employee_id = (select employee_id
from emp_history
where employee_id = e.employee_id);

delete from job_history jh
where employee_id = (select employee_id
from employees e
where jh.employee_id = e.employee_id
and start_date = (select min(start_date)
from job_history jh
where jh.employee_id = e.employee_id)
and 5 > (select count(*)
from job_history jh
where jh.employee_id = e.employee_id
group by employee_id
having count(*) > =4));

-- WITH
-- เปรียบ WITH คล้าย ๆ กับการใช้ View เพียงแต่เราไม่ต้องสร้าง View เอาไว้ก่อน การใช้ WITH ช่วยให้การเขียนคิวรีที่ต้องอ้างถึง Subquery ตัวเดิมซ้ำ ๆ กัน สามารถกำหนด Subquery ไว้ที่เดียว ทำให้คิวรีสั้นขึ้น ดูง่ายขึ้น และแก้ไขได้ง่ายขึ้น
with
dept_costs as (
select d.department_name, sum(e.salary) as dept_total
from employees e, departments d
where e.department_id = d.department_id
group by d.department_name),
avg_cost as (
select sum(dept_total) / count(*) as dept_avg
from dept_costs)
select *
from dept_costs
where dept_total > (select dept_avg from avg_cost)
order by department_name

********************************************************************************
Chapter16: Extension of DML and DDL
********************************************************************************
create table mgr_history as select employee_id,manager_id,salary from employees where 1=0;
create table sal_history as select employee_id,hire_date,salary from employees where 1=0;

-- Unconditional INSERT ALL
-- การ Insert ข้อมูลเข้าทีเดียวสองตาราง (หรือมากกว่า)
INSERT ALL
INTO sal_history (employee_id,hire_date,salary) values (empid, hiredate,sal)
INTO mgr_history (employee_id,manager_id,salary) values (empid, mgr, sal)
select employee_id empid, hire_date hiredate,
salary sal, manager_id mgr
from employees
where employee_id > 200;

rollback;

-- Conditional INSERT ALL
-- การ Insert ข้อมูลเข้าทีเดียวสองตาราง แบบมีเงื่อนไขว่า ถ้า.. ให้ Insert เข้าตาราง...
INSERT ALL
WHEN sal > 10000 THEN
INTO sal_history values (empid,hiredate,sal)
WHEN mgr > 200 THEN
INTO mgr_history values (empid,mgr,sal)
select employee_id empid, hire_date hiredate,
salary sal, manager_id mgr
from employees
where employee_id > 200;
select * from sal_history;
select * from mgr_history;

-- Conditional FIRST INSERT ถ้า Condition แรกเป็นจริงก็ข้ามไปแถวต่อไป
INSERT FIRST
WHEN sal > 25000 THEN
INTO special_sal VALUES (deptid, sal)
WHEN hiredate like ('%') then
INTO hiredate_history_00 VALUES (deptid, hiredate)
WHEN hiredate like '%99%') then
INTO hiredate_history_99 VALUES (deptid,hiredate)
ELSE
INTO hiredate_history values (dept_id, hiredate)
SELECT department_id deptid, sum(salary) sal,
max(hire_date) hiredate
FROM employees
GROUP BY department_id;

-- Pivot INSERT กลับคอลัมน์เป็นแถว
-- เราสามารถใช้ INSERT ALL ในการทำ Pivot ก็ได้
-- จากตัวอย่างข้างล่างเดิมปริมาณการขายเก็บของแต่ละวันในสัปดาห์ ถูกเก็บไว้แยกเป็นคอลัมน์วัน (จันทร์, อังคาร, ...) เราต้องการนำข้อมูลเหล่านี้มาเก็บในอีกตารางที่อยู่ในรูปของการเก็บแบบแต่ละวันของสัปดาห์ในรูปของแต่ละเรคคอร์ด
INSERT ALL
INTO sales_info values (employee_id,week_id,sales_MON)
INTO sales_info values (employee_id,week_id,sales_TUE)
INTO sales_info values (employee_id,week_id,sales_WED)
INTO sales_info values (employee_id,week_id,sales_THU)
INTO sales_info values (employee_id,week_id,sales_FRI)
select employee_id, week_id,sales_MON,sales_TUE,sales_WED,sales_THU,sales_FRI
from sales_source_data;

********************************************************************************
Chapter17: External Tables
********************************************************************************
เราสามารถ Select ข้อมูลจากเท็กซ์ไฟล์ข้างนอกระบบฐานข้อมูลได้

1. สมมติเรามีเท็กซ์ไฟล์ที่มีข้อมูลดังนี้
7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20

2. การ Select จากไฟล์ จะต้องเก็บไฟล์ไว้ในไดเรคทอรีที่ Oracle รู้จัก โดยในตัวอย่างนี้เราจะเก็บไฟล์ไว้ในไดเรคทอรีบนดาต้าเบสที่ชื่อว่า 'DATA_PUMP_DIR' ซึ่งมีมากับดาต้าเบสโดยดีฟอลต์ การจะเช็คดูว่ามี directory แล้วหรือยังและไดเรคทอรีดังกล่าวแม็ปกับไดเรคทอรีบน OS ใด เราสามารถใช้คำสั่ง (จะต้อง Login user ที่มีสิทธิ์ DBA)
select * from dba_directories where directory_name = 'DATA_PUMP_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------ --------------------- ----------------------------------
SYS DATA_PUMP_DIR C:\oraclexe\app\oracle\admin\XE\dpdump\
เราอาจจะสร้าง directory ใหม่เพื่อให้แม็ปกับ directory ที่เราต้องการก็ได้โดยการสร้างจะต้องมีสิทธิ์ที่เป็น DBA เช่นกัน

3. Save ไฟล์ในข้อ 1 ด้วยชื่อ "emp.dat" ไว้ในไดเรคทอรีที่แม็ปไว้กับไดเรคทอรี DATA_PUMP_DIR ใน Oracle
4. ผู้จะสร้าง External Table จะต้องมีสิทธิ์ Read,write ในไดเรคทอรีที่เก็บไฟล์ (จะต้องให้ user ที่มีสิทธิ์ DBA grant ให้)
grant read,write on directory data_pump_dir to scott;
Grant succeeded

5. จากนั้นล็อกอินด้วย user ทีต้องการสร้างตารางแล้วสร้างตารางดังข้างล่าง
create table emp_ext
(empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2)
)
organization external
(type oracle_loader
default directory data_pump_dir
access parameters
(
records delimited by newline
fields terminated by ','
)
location ('emp.dat')
)
reject limit 1000;
โดยคอลัมน์ที่กำหนดจะต้องสอดคล้องกับข้อมูลในไฟล์
- ตรง 'location' เป็นตัวกำหนดว่าชื่อไฟล์ที่จะให้ไปอ่านชื่ออะไร
- โดย Oracle จะไปอ่านไฟล์ดังกล่าวตามที่กำหนดไว้ใน default directory
- 'fields terminated by' เป็นตัวกำหนดว่าข้อมูลแต่ละคอลัมน์ถูกแยกจากกันด้วยสัญญลักษณ์อะไร
- 'reject limit' เป็นตัวกำหนดว่าถ้ามีข้อผิดพลาดในการอ่านไม่เกินจำนวนเท่าไรจึงจะแสดง error และไม่ต้องอ่านต่อ

6. หลังจากสร้างตารางเสร็จแล้ว เราสามารถ select ข้อมูลได้เหมือนตารางปกติ
select * from emp_ext;

********************************************************************************
Chapter18: Hierarchical Retrieval
********************************************************************************
-- คิวรีข้างล่างแสดงข้อมูลของพนักงาน โดยเรคคอร์ดถัดไปจะเป็นเรคคอร์ดของหัวหน้าของเรคคอร์ดที่อยู่ก่อนหน้า
select employee_id, last_name, job_id, manager_id
from employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id ;
-- เราสามารถใช้ PRIOR เพื่อแสดงผลในคอลัมน์ได้
select level,last_name || ' reports to ' ||
PRIOR last_name "Walk Top Down", level
from employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id ;

-- Using Level to format Hierarchical Reports
-- เราใช้ LEVEL เพื่อแสดงลำดับที่ในผลของคิวรีได้ ตัวอย่างข้างล่างในใช้ LEVEL เพื่อจัดรูปแบบของ Report ในลักษณะของแผนภูมิแบบลำดับชั้น (Hierarchical)
select LPAD(last_name, length(last_name) + (level*2) -2 , '_') as org_chart, level
from employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id
-- เราสามารถเพิ่มเงื่อนไขใน CONNECT BY ได้
select employee_id, last_name, job_id, manager_id
from employees
START WITH last_name = 'Kochhar'
CONNECT BY PRIOR employee_id = manager_id and last_name != 'Higgins'

-- Use WHERE to eliminate a node
-- เราใช้ WHERE เพื่อตัดเฉพาะบางโหนด (คน) ออกไป คิวรีข้างล่างเฉพาะเรคคอร์ดของ 'Higgins' ถูกตัดออกไป
select department_id, employee_id,last_name, job_id,salary, manager_id
from employees
where last_name != 'Higgins'
start with last_name = 'Kochhar'
connect by prior employee_id = manager_id;

-- Use CONNECT BY to eliminate a branch
-- เราเพิ่มเงื่อนไขใน CONNECT BY เพื่อตัดเฉพาะบางกิ่ง (โหนดและโหนดลูก,หลาน) ออกไป คิวรีข้างล่างเรคคอร์ดของ 'Higgins' และเรคคอร์ดที่เป็นลูกน้องของ 'Higgins' จะถูกตัดออกไป
select department_id, employee_id, last_name, job_id, salary, manager_id
from employees
start with last_name = 'Kochhar'
CONNECT BY PRIOR employee_id = manager_id
AND last_name != 'Higgins';