จัดการกับ index ที่ไม่ได้ใช้งาน

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

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

alter index index_name monitoring usage;

เมื่อ run คำสั่งนี้แล้วระบบจึงจะเริ่มเก็บข้อมูลเกี่ยวกับการใช้งาน index ที่ปรากฏอยู่ในคำสั่ง โดยเราสามารถดูผลได้จากวิว v$object_usage ซึ่งวิวนี้จะแสดงข้อมูลต่าง ๆ ดังนี้

Index_name -ชื่อ index
Table_name -ชื่อ Table ที่ทำ index
Monitoring -Yes/No เพื่อบอกว่ามีการเก็บสถิติ
Used -Yes/No เพื่อบอกว่า index มีการใช้งานหรือไม่
Start_monitoring -วันที่เริ่มเก็บสถิติ
End_monitoring -วันที่สิ้นสุดการเก็บสถิติ

ข้อมูลที่ได้จากวิวนี้จะบอกเราได้ว่า index ตัวใดไม่มีการใช้งานในช่วงเวลาที่เราเก็บสถิติโดยดูที่คอลัมน์ Used ถ้ามีค่าเป็น No ก็แสดงว่าไม่มีการใช้งาน ทีนี้เราก็สามารถลบ index ทิ้งได้เลย และหลังจากที่เราไม่ต้องการให้ระบบเก็บสถิติต่อไปเราก็ทำการยกเลิกได้โดยคำสั่งดังนี้

alter index index_name nomonitoring usage;

ความสำคัญของ ROWID

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

ROWID ในความหมายของออราเคิลก็คือ pseudocolumn(คอลัมน์สมมุติ) เพื่อใช้บอกถึงตำแหน่งทาง physical ของข้อมูลแต่ละ row ใน table ว่าอยู่ที่ data file อะไร บล็อกไหนและอยู่ที่ row ที่เท่าไรใน data block โดยที่ค่าของ ROWID นี้จะเป็นค่า unique สำหรับแต่ละ row ใน table ที่ไม่ซ้ำกันเลย เราสามารถดูค่าของ ROWID ได้ด้วยคำสั่งดังนี้

SQL> select rowid from table_name;

ROWID
------------------
AAANUqAAGAAAVM2AAA
AAANUqAAGAAAVM2AAB
AAANUqAAGAAAVM2AAC
AAANUqAAGAAAVM2AAE

ค่าของ ROWID ที่แสดงให้เราเห็นนั้นถูกเก็บแบบเข้ารหัส BASE64 มีขนาดเท่ากับ 18 character โดยเราสามารถใช้ฟังก์ชัน DBMS_ROWID เพื่อแปลความหมายของ ROWID ได้ดังนี้

เก็บอยู่ที่ Table อะไร
select object_name from dba_objects where data_object_id = dbms_rowid.rowid_object('AAANUqAAGAAAVM2AAA')

เก็บอยู่ที่ Data file อะไร
select file_name from dba_data_files where file_id = dbms_rowid.rowid_relative_fno('AAANUqAAGAAAVM2AAA')

เก็บอยู่ที่ block ที่เท่าไร
select dbms_rowid.rowid_block_number('AAANUqAAGAAAVM2AAA') from dual

หาว่าอยู่ row ที่เท่าไร
select dbms_rowid.rowid_row_number('AAANUqAAGAAAVM2AAA') from dual

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

delete from table_name a where exists (select 1 from table_name b where a.COL1 = b.COL1 and b.ROWID < a.ROWID)

วิธีคำนวณขนาดของ Table Sizes

ในการดูแลจัดการฐานข้อมูลออราเคิล สิ่งสำคัญที่ผู้ดูแลระบบ (DBA) จะต้องคำนึงถึงคือปริมาณของข้อมูลที่โตขึ้นเรื่อย ๆ ทุกวัน เพื่อจะได้ทำการวางแผน Purge ข้อมูลหรือหาซื้อ Disk มาเพิ่ม วิธีการคร่าว ๆ ที่จะหาขนาดของข้อมูลในแต่ละ Table นั้นก็อาจจะหาได้จาก เอาจำนวน Row ทั้งหมดใน Table คูณด้วยขนาดของ Data Type ในแต่ละ Column รวมกัน ซึ่งค่าที่คำนวณได้อาจจะไม่ตรงกับความเป็นจริงเนื่องจากออราเคิลเก็บข้อมูลแบบ Variable Length และที่สำคัญวิธีนี้ค่อนข้างจะกิน resource สูงและจะทำให้ performance ของระบบตกลงเนื่องจากออราเคิลจะเสียเวลาไปกับการ Scan ทั้ง Table เพื่อให้ได้จำนวน Row

วิธีการง่าย ๆ ที่ดีกว่าที่กล่าวมาซึ่งออราเคิลได้จัดเตรียมไว้ให้แล้วสามารถทำได้โดยใช้ View ที่ชื่อว่า DBA_SEGMENT โดยสั่ง run คำสั่งดังนี้

select byte from dba_segment where segment_name = 'table_name';

คำสั่งนี้จะได้ผลลัพธ์ขนาดของ Table เป็นจำนวน byte โดยระบุเงื่อนไข where เป็นชื่อของ Table ได้ตามต้องการ และในทำนองเดียวกันเราก็สามารถหาขนาดของ database segment อื่น ๆ ได้เช่นกัน เช่น Index หรือ Materialized View เป็นต้น

การลดขนาด Table เพื่อคืนเนื้อที่ให้กับ Disk

เมื่อมีการใช้งาน Table ที่มีการทำ insert, update, delete บ่อย ๆ ก็จะเกิดบล็อคของข้อมูลที่เป็นพื้นที่ว่างเกิดขึ้น พื้นที่ว่างเหล่านี้เป็นที่ว่างที่ไม่ได้ถูกใช้งานทำให้เกิดการสิ้นเปลืองเนื้อที่ใน Tablespace โดยไม่จำเป็น นอกจากนี้ยังมีผลต่อประสิทธิภาพโดยรวมของระบบอีกด้วยเนื่องจากการที่มีจำนวนบล็อคที่ว่างกระจายอยู่ใน Segment มากมายทำให้การ query ข้อมูลทำได้ช้าเพราะต้อง scan ข้อมูลจากหลาย ๆ บล็อคเพื่อให้ได้ข้อมูลตามต้องการ

ใน Oracle 10g มีคำสั่งที่ใช้ในการลดพื้นที่ว่างเหล่านี้และจัดเรียงบล็อคข้อมูลใหม่เพื่อเพิ่มประสิทธิภาพโดยรวมของระบบ โดยมีรูปแบบคำสั่งดังนี้

alter table table_name shrink space

เนื่องจากการ shrink จะทำให้เกิดการเปลี่ยนแปลงตำแหน่งของ rowid ใน table ดังนั้นก่อนที่จะใช้คำสั่งนี้ได้ จะต้องทำการ enable row movement เสียก่อนด้วยคำสั่งดังนี้

alter table table_name enable row movement

ในทำนองเดียวกันถ้าเราต้องการลดพื้นที่ว่างสำหรับ database segment อื่น ๆ เช่น index ก็สามารถทำได้เช่นกัน ดังนี้

alter index index_name shrink space

สร้าง Table พักด้วย Global Temporary Table

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

Global Temporary Table คือคำตอบสุดท้ายที่ออราเคิลตั้งแต่ 8i เป็นต้นมาได้ให้ฟีเจอร์นี้มาเพื่อใช้สำหรับเป็นที่พักข้อมูลชั่วคราว โดยที่ผู้ใช้งานในแต่ละ session จะเห็นเฉพาะข้อมูลของตัวเอง และที่สะดวกไปกว่านั้นก็คือ เมื่อผู้ใช้ออกจากระบบหรือจบ session ออราเคิลก็จะทำการลบข้อมูลพักใน session นั้น ๆ ให้โดยอัตโนมัติ โดยมีรูปแบบคำสั่งที่ใช้ในการสร้าง table ดังนี้

create global temporary table ...table_name... (column1 data_type, column2 data_type,...)

โดยคำสั่งนี้มี option ให้เลือก 2 option คือ

• on commit delete rows ใช้ option นี้เมื่อ commit จบ transaction แล้วข้อมูลใน table จะถูกลบทิ้งทันที โดย option นี้จะถูกระบุเป็นค่า default ถ้าไม่มีการระบุ
• on commit preserve rows ทางเลือกนี้จะยังคงเก็บข้อมูลไว้ใน table แม้ว่าจะจบ transaction ไปแล้วก็ตาม แต่ข้อมูลจะถูกลบทิ้งเมื่อจบ session

ตัวอย่างเช่น

create global temporary table gt_temp (col1 varchar2(10),col2 varchar2(20)) on commit delete rows;

ต่อไปนี้เราก็จะได้ใช้งาน table พักโดยสามารถใช้คำสั่ง insert,update,delete เพื่อจัดการกับข้อมูล และสามารถทำ index ได้เหมือนกับ table ปกติโดยที่ไม่ต้องมาคอยจัดการลบข้อมูลทิ้งอีกต่อไป

ตัวแปร Array ใน PL/SQL

ใน PL/SQL เราสามารถสร้างตัวแปร Array เพื่อใช้เก็บข้อมูลหลาย ๆ ค่าไว้ในตัวแปรชื่อเดียวกันโดยมี index หรือหมายเลขเป็นตัวกำกับชื่อตัวแปรนั้น ๆ การกำหนดตัวแปรลักษณะนี้ทำให้เราสามารถอ้างอิงค่าในตัวแปร Array ได้หลายค่าพร้อม ๆ กันโดยการอ้างชื่อตัวแปรพร้อมกับหมายเลขกำกับ เช่น Array(1), Array(2), Array(3) เป็นต้น ทำให้เราสามารถพัฒนาโปรแกรมได้ยืดหยุ่นมากขึ้น

ตัวอย่างเช่น

01 DECLARE
02 Type phone_no_array is varray(4) of varchar2(20);
03 phone_no phone_no_array;
04 BEGIN
05 phone_no := phone_no_array('024561203','023895542','024357890','026987852');
06 DBMS_OUTPUT.PUT_LINE(phone_no(1));
07 DBMS_OUTPUT.PUT_LINE(phone_no(2));
08 DBMS_OUTPUT.PUT_LINE(phone_no(3));
09 DBMS_OUTPUT.PUT_LINE(phone_no(4)):
10* END;
SQL>/
024561203
023895542
024357890
026987852

จากตัวอย่างข้างบนในบันทัดที่ 2 เป็นการกำหนดชนิดตัวแปร Array ชื่อ phone_no_array ขนาด 4 สมาชิกโดยเก็บข้อมูลเป็น varchar2 และในบันทัดที่ 3 กำหนดให้ตัวแปร phone_no มี data type เป็น phone_no_array จากนั้นในบันทัดที่ 5 จะเป็นการกำหนดค่าเริ่มต้นให้กับตัวแปร phone_no ทั้งหมด 4 ค่า และในบันทัดที่ 6-10 เป็นการแสดงผลลัพธ์ของค่าในตัวแปร phone_no ออกมาทั้ง 4 ค่า โดยอ้างอิงชื่อตัวแปรและหมายเลขกำกับตัวแปร เช่น phone_no(1), phone_no(2) เป็นต้น

ลองมาดูกันอีกซักหนึ่งตัวอย่าง

01 DECLARE

02 Type num_array is varray(100) of number;

03 num num_array := num_array();

04 BEGIN

05 num.extend(100);

06 FOR I in 1..100 LOOP

07 num(I) := I;

08 END LOOP;

09* END

ตัวอย่างนี้เป็นการกำหนดค่าเริ่มต้นให้ตัวแปร Array ที่ชื่อว่า num ซึ่งเป็น Array ว่าง ๆ ขนาด 100 สมาชิกโดยในบันทัดที่ 3 ไม่ได้มีการกำหนดค่าของสมาชิกให้กับ Array เหมือนในตัวอย่างแรก ดังนั้นหากเราต้องการใช้งานตัวแปร Array เราจะต้องทำการ extend ก่อนจึงจะใช้งานได้ ในที่นี้บันทัดที่ 5 เป็นการ extend เพื่อใช้งาน array จำนวน 100 ค่า จากนั้นบันทัดที่ 6-8 จะเป็นการวนลูปเพื่อนำเอาค่า 1 ถึง 100 ใส่ลงไปใน Array ตามลำดับ ด้วยวิธีการนี้ทำให้เราสามารถกำหนดค่าให้กับ Array แบบ dynamic ได้โดยไม่ต้องมากำหนดค่าคงที่ให้กับ Array แต่ละตัวเหมือนกับตัวอย่างแรก

กู้ข้อมูล Oracle ด้วย Flashback

หลายคนคงจะเคยพลาดหรือพลั้งเผลอลบข้อมูลใน database ไปโดยไม่ตั้งใจ ถ้าบังเอิญ rollback ได้ทันก็ถือว่าโชคดีไป แต่โชคไม่ได้เข้าข้างเราเสมอไป บางครั้งข้อมูลสำคัญอาจจะถูก commit ไปแล้ว หรืออาจจะเผลอลบไปด้วยคำสั่ง Drop Table ซึ่งถือเป็นคำสั่งอันตรายอย่างยิ่งสำหรับมือใหม่หัดใช้เนื่องจากข้อมูลทั้ง table จะหายวับไปภายในพริบตา ทีนี้ก็ต้องมาตั้งสติกันว่าจะทำอย่างไรกันต่อไป ถ้ามี Back up ไว้ก็พอรอดตัวไปได้แต่ก็ต้องมาดูกันว่า Back up ไว้ล่าสุดเมื่อไร จะได้ข้อมูลกับคืนมาสมบูรณ์จนถึงจุดก่อนที่จะถูกลบหรือไม่ ปัญหาเหล่านี้เคยเจอกับตัวเองมาแล้วก็พอจะเข้าใจคนหัวอกเดียวกันว่าเป็นอย่างไร

พอออราเคิลออกเวอร์ชัน 10g ก็คงจะเข้าใจความทุกข์ยากของคนชอบพลั้งเผลอ จึงได้เพิ่มฟีเจอร์ "flashback" เข้ามาเป็นตัวช่วยในการกู้คืนข้อมูล โดยสามารถเรียกดูข้อมูลที่ถูกลบไปแล้วตามช่วงเวลาได้ (Flashback Query) และยังสามารถกู้ Table ที่ถูก Drop กลับคืนมาได้อีกด้วย (Flashback Drop)

• Flashback Query เป็นฟีเจอร์ที่ใช้สำหรับเรียกดูข้อมูลเดิมก่อนหน้าที่จะถูกลบหรือแก้ไขโดยสามารถระบุเวลาย้อนหลังได้ ตัวอย่างเช่น

select * from employees as of timestamp to_timestamp('14/09/2009 13:00:00','dd/mm/rrrr' hh24:mi:ss');

เป็นการย้อนเวลากลับไปดูข้อมูลในวันที่ 14/09/2009 เวลา 13.00 เพื่อดูว่าข้อมูล ณ เวลานั้นเป็นอย่างไร ถ้าเป็นข้อมูลที่ต้องการเราก็สามารถดึงข้อมูลเหล่านี้เพื่อ insert กลับคืนมาได้ ถึงแม้ว่าเราจะย้อนเวลาไปดูข้อมูลเก่าได้แต่ก็ไม่ได้หมายความว่าเราจะย้อนเวลาไปได้นานเท่าที่เราต้องการเนื่องจาก Flashback Query จะไปดึงข้อมูลเก่าที่ยังค้างอยู่ใน Undo Segment โดยมีพารามิเตอร์ undo_retention เป็นตัวกำหนดว่าจะให้เก็บข้อมูลเก่าไว้ใน Undo Segment นานเท่าไร ดังนั้นเราจึงไม่สามารถย้อนเวลาไปดึงข้อมูลเก่าได้เกินกว่าที่กำหนดไว้ในพารามิเตอร์นี้

• Flashback Drop เป็นฟีเจอร์ที่ใช้สำหรับกู้ table ที่ถูกลบด้วยคำสั่ง Drop ให้กลับคืนมาได้อย่างง่ายดาย ตัวอย่างเช่น

Flashback table employees to before drop;

โดยหลักการแล้วเมื่อ Table ถูกลบด้วยคำสั่ง Drop ข้อมูลเหล่านั้นจะยังไม่ถูกลบไปจริง ๆ เพียงแต่ว่ามันจะถูกย้ายไปเก็บไว้ที่ recyclebin แทน โดยเราสามารถเรียกดูข้อมูลใน recyclebin ได้ด้วยคำสั่ง show recyclebin ใน sql*plus หรือจะเรียกดูผ่านวิว user_recyclebin ก็ได้ นั่นคือเหตุผลที่ว่าทำไมเราจึงสามารถกู้ข้อมูลกลับมาได้