Interval partitioning หมดปัญหาเรื่องการลืมสร้าง Partition เตรียมไว้

         วันก่อนนึกครื้มๆ ก็เลยนั่งอ่าน New Feature เล่นๆ ดูว่าตัวไหนน่าสนใจเอามาใช้ให้เป็นประโยชน์ได้บ้าง แล้วก็เหลือบไปเห็น Interval Partitioning (automated partition creation) อืม… น่าสนใจแฮะ เหมาะกับคนขี้ลืมอย่างเรามาก ถ้ามันมี auto แบบนี้สบายเลย ไม่ต้องมานั่งทำทุกเดือน หรือทำเผื่อไว้ล่วงหน้า แจ๋วแฮะ.. วันนี้เลยเอามาฝากเพื่อนๆกัน

          Interval Parttition คือ Freature ใหม่ที่เพิ่มเข้ามาใน oracle11g เป็น feature ที่ช่วยให้ partition สามารถที่จะสร้างให้อัตโนมัติ เมื่อมีข้อมูลใหม่ที่เข้ามาแล้วไม่ได้อยู่ใน range ของที่มีอยู่แล้ว
 

ทำไม Interval Partition จึงน่าใช้

หลีกเลี่ยงการเกิด Error
           ORA-14400: inserted partition key does not map to any partition
    ซึ่ง error นี้จะเกิดขึ้นกรณีที่มีข้อมมูล insert เข้ามาใหม่แต่ไม่มี partition ไหนที่ตรงเงื่อนไขในการเก็บข้อข้อมูล

ถ้าเป็น version ก่อนๆ เราสามารถหลีกเลี่ยง Error  ได้โดยการสร้าง MAX partition ซึ่งจะเป็นตัวเก็บข้อมูลที่ไม่ตรงตามเงื่อนไขของ partition ยกตัวอย่างเช่น

    ถ้าเราสร้าง TABLE  TEST  ให้มีทั้งหมด 3 partition
         – p1 เก็บข้อมูลที่มี salary < 5000
         – p2 เก็บข้อมูลที่มี salary < 10000
         – p3 เก็บข้อมูลที่มี salary < 15000
         – pmax เก็บข้อมูลที่มี salary < maxvalue

โดยใช้คำสั่งด้านล่างนี้:

SQL> create table test
2      (sno number(6),
3      last_name varchar2(30),
4      salary number(6))
5      partition by range(salary)
6     (
7  partition p1 values less than (5000),
8  partition p2 values less than (10000),
9  partition p3 values less than (15000),
10  partition pmax values less than (maxvalue));
 
Table created.

จากตัวอย่าง Partition ด้านบนเมื่อมีข้อมูลที่มี salary > =15000 ข้อมูลจะไปกองอยู่ที่ pmax ที่เดียวเท่านั้น ถ้าสมมุติว่า Table ของเราต้องการแบ่ง partition เป็นเดือน แล้วมีการสร้าง pmax ไว้เพื่อกันกรณีที่ error ถ้าเดือนไหนเราลืม add เพิ่มจะทำให้ไปกองที่ pmax ถ้าลืมสร้างซัก 3 เดือนข้อมูลของ 3 เดือนจะไปกองอยุ่ที่ pmax ที่เดียว และถ้าข้อมูล / เดือน ประมาณ 100ล้าน ดังนั้น pmax จะมีข้อมูลถึง 300ล้าน ซึ่งจะมีผลกับการ query แน่นอน แต่เราก็สามารถที่จะ split partition (คือการแบ่งข้อมูล 3 เดือน ที่อยู่ใน pmax ออกมาเป็น p4,p5,p6 ตามลำดับ) ได้แต่อาจจะต้องหยุด process และเสียเวลานการทำ ซึ่งสำหรับ Interval Partition เราจะสามารถลืมเรื่องนี้ไปได้เลย

คราวนี้เรามาลองดูวิธีการทำ Interval Partition กันดูว่าสามารถ Implement ได้อย่างไร

1. สร้าง Table POS_DATA ขึ้นมา โดยมีการกำหนด key ให้เป็น interval ตัวอย่างนี้เราจะใช้ช่วงแบ่งที่เป็น DATE เพื่อจะได้เห็นภาพชัดเจนขึ้น เพราะการนำไปใช้ส่วนใหญ่จะใช้วันที่เป็นตัวแบ่ง

create table
pos_data (
   start_date        DATE,
   store_id          NUMBER,
   inventory_id      NUMBER(6),
   qty_sold          NUMBER(3),
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( 
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('1-7-2007', 'DD-MM-YYYY')),
   PARTITION pos_data_p3 VALUES LESS THAN (TO_DATE('1-8-2007', 'DD-MM-YYYY'))
);

คำอธิบาย:

1. การทำ Interval จะต้องมีการสร้าง Table & Parttiion เตรียมไว้ก่อน

2. INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) คือตัวกำหนดว่า partiiton ที่จะถูกสร้างอัตโนมัติเป็นอย่างไร อย่างตัวอย่างข้างต้นคือ 1 เดือน

3. ถ้ามีการ Load ข้อมูลเข้า table ที่มีวันที่มากกว่า partition ที่กำหนดไว้ oracle จะทำการสร้าง partition เดือนใหม่ให้โดยอัตโนมัติ ซึ่ง table ด้านบนมีการสร้างไว้ 2 partition คือข้อมูลที่มีค่าน้อยกว่า July 1, 2007 และ August 1, 2007

4.    โดยปกติถ้ามีการ insert ข้อมูลที่วันที่ของเดือน August เข้ามา เช่น August 1, 2007 ระบบจะตี Error   ORA-14400: inserted partition key does not map to any partition
แต่ Interval Partition จะเป็นตัวตัดสินใจเลือกและสร้าง partition ใหม่ขึ้นมาในระบบเพื่อให้สอดคล้องกับข้อมูลที่ถูก insert เข้ามา

ลองมาดูตัวอย่างกันเพื่อความเข้าใจมากขึ้น:

insert into pos_data (start_date, store_id, inventory_id, qty_sold)
values ( '15-AUG-07', 1, 1, 1);

SELECT
   TABLE_NAME,
   PARTITION_NAME,
   PARTITION_POSITION,
   HIGH_VALUE
FROM
   Remote DBA_TAB_PARTITIONS
WHERE
   TABLE_NAME='POS_DATA'
ORDER BY
   PARTITION_NAME;

PARTITION_NAME    HIGH_VALUE 

POS_DATA_P0       TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

POS_DATA_P1       TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P81    TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

จากตัวอย่างด้านบน จะเห็นว่าระบบมีการสร้าง partition ใหม่ชื่อ SYS_P81 ขึ้นให้อัตโนมัติ โดย key ที่แบ่งก็จะเป็นไปตามข้อมูลที่ใส่เข้ามา

โดยเราสามารถที่จะอ้างอิงเรียกใช้ partition ใหม่ได้ตามปกติ
select
   *
from
   pos_data partition (SYS_P81);

และนอกจากนี้เรายังสามารถที่จะกระจายข้อมูลของแต่ละ partition ลงไปต่าง tablespace ได้ด้วย โดยการเพิ่ม syntax STORE IN เพิ่มเข้าไปจากตัวอย่างด้านล่างคือกำหนดให้กระจายไป 3 tablespace A,B,C

INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
STORE IN (tablespaceA, tablespaceB, tablespaceC)

ข้อจำกัดของ Interval Partition :

1. ไม่สามารถใช้กับ Index Organized Tables (IOT) ได้
2.  Key ที่ไว้แบ่ง Partition จะต้องเป็น DATE หรือ NUMBER เท่านั้น
3. ไม่สามารถ create domain indexes ได้
4. ใช้ไม่ได้กับ Sub-Partition Level

รวมคำสั่งสำหรับ Interval Partitioning:

1. การแปลงจาก Parttition table ธรรมดาให้เป็น interval partitioning ทำได้โดยใช้คำสั่งด้านล่าง :

alter table pos_data_range set INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));

2. ถ้าต้องการแปลงจาก Interval partitioning กลับเป็น partition table ธรรมดา ก็ทำได้โดยใช้คำสั่งด้านล่าง

alter table pos_data_range set INTERVAL();

3. บางครั้งเราเปลี่ยนใจ อยากแบ่งทุกๆ 3 เดือนก็สามารทำได้ตามคำสั่งดานล่าง

alter table pos_data set INTERVAL(NUMTOYMINTERVAL(3, 'MONTH'));

โดยหลังจากที่เรา insert ข้อมูลวันที่ 15-NOV-07, partition ใหม่จะถูกสร้างให้อัตโนมัติ โดย range จะถูกขยับไป 3 เดือน

insert into
   pos_data (start_date, store_id, inventory_id, qty_sold)
values
   ('15-NOV-07', 1, 1, 1);

SELECT
   TABLE_NAME, PARTITION_NAME, PARTITION_POSITION, HIGH_VALUE
FROM
   Remote DBA_TAB_PARTITIONS
WHERE
   TABLE_NAME='POS_DATA'
ORDER BY
   PARTITION_NAME;

PARTITION_NAME    HIGH_VALUE 

POS_DATA_P0       TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

POS_DATA_P1       TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P81    TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS_P84    TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

4. การกำหนด  tablespace storage ให้กับ  interval partition

alter table pos_data set STORE IN(tablespace1, tablespace2, tablespace3);

สุดท้ายนี้หวังว่าบทความ New Feature ตัวนี้น่าจะช่วยการจัดการของ DBA  ได้ง่ายมากขึ้น และเป็นประโยชน์กับเพื่อนๆไม่มากก็น้อย ^^