จัดการกับ 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