با ارائه قابلیت Interval partitioning در اوراکل نسخه 11g، نیاز به اضافه کردن دستی پارتیشن، در هنگام درج اطلاعات خارج از محدوده از بین رفته است. در این متن به صورت خلاصه نکاتی را در مورد Interval Partitioning مرور خواهیم کرد.

نکته شماره 1:همانطور که می دانید برای پارتیشن بندی جدول به روش Range-Interval، باید حداقل یک Range Partition برای جدول تعریف کرد:

SQL> create table tbl (

     id number,

     name varchar2(10),

     date_        DATE

)

PARTITION BY RANGE (date_)

 INTERVAL(NUMTOYMINTERVAL(1, ‘YEAR’))

(

   PARTITION p1 VALUES LESS THAN (TO_DATE(‘4-6-2001’, ‘DD-MM-YYYY’))

);

ممکن است با اضافه شدن پارتیشنهای جدید به این جدول، تصمیم به حذف پارتیشن p1(که تنها پارتیشن از نوع Range جدول tbl است) را داشته باشیم:

SQL> insert into tbl values(1,’test’,TO_DATE(‘4-6-2002‘, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into tbl values(1,’test’,TO_DATE(‘4-6-2003‘, ‘DD-MM-YYYY’));

1 row inserted

SQL> select PARTITION_NAME,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME   INTERVAL

—————- ——–

P1               NO

SYS_P701 YES

SYS_P702         YES

SQL> alter table TBL drop partition p1;

ORA-14758: Last partition in the range section cannot be dropped

همانطور که می بینید، در اوراکل 11g، حذف پارتیشن اول از جدول TBL امکان پذیر نمی باشد و برای حذف آن باید حداقل یک پارتیشن دیگر از نوع RANGE در جدول موجود باشد(علاوه بر پارتیشن p1) که با اجرای دستور زیر، همه پارتیشنهای  INTERVAL به NON-INTERVAL تغییر خواهد کرد:

SQL> alter table tbl set interval( numtoyminterval(1,’YEAR’));

Table altered

SQL> select PARTITION_NAME,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME   INTERVAL

—————- ——–

P1               NO

SYS_P701         NO

SYS_P702         NO

در این شرایط، قابلیت حذف پارتیشن P1 به وجود خواهد آمد:

SQL> alter table TBL drop partition p1;

Table altered

در اوراکل 12cR2، مسئله کمی تغییر کرده است و برای حذف اولین پارتیشن جدول، نیاز به اجرای دستور اضافه ای نخواهد بود و این مسئله توسط اوراکل مدیریت می شود:

SQL> select PARTITION_NAME,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       INTERVAL

——————– ——–

P1                   NO

SYS_P17415           YES

SYS_P17416           YES

SQL> alter table TBL drop partition p1;

Table altered

SQL> select PARTITION_NAME,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       INTERVAL

——————– ——–

SYS_P17415           NO

SYS_P17416           YES

 

نکته شماره 2: جدولی را به صورت زیر و با استراتژی range interval پارتیشن بندی کرده ایم:

SQL> create table tbl (

     id number,

     name varchar2(10),

     date_        DATE

)

PARTITION BY RANGE (date_)

INTERVAL(NUMTOYMINTERVAL(1, ‘YEAR‘))

(

   PARTITION p1 VALUES LESS THAN (TO_DATE(‘4-6-2001’, ‘DD-MM-YYYY’)),

   PARTITION p2 VALUES LESS THAN (TO_DATE(‘5-5-2003’, ‘DD-MM-YYYY’)),

   PARTITION p3 VALUES LESS THAN (TO_DATE(‘6-4-2005’, ‘DD-MM-YYYY’))

); 

Table created.

با درج رکوردی که تاریخ آن خارج از محدوده تعریف شده است(بزرگتر از تاریخ 6-4-2005)، خواهیم دید که عدد در نظر گرفته شده برای روز و ماه پارتیشن جدید(توسط دیتابیس)، برابر با عدد تنظیم شده برای Range partition آخر جدول tbl می باشد.

SQL> insert into tbl values(7,’test’,TO_DATE(‘9-8-2041’, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into tbl values(7,’test’,TO_DATE(’10-11-2091′, ‘DD-MM-YYYY’));

1 row inserted

SQL> select PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       HIGH_VALUE

——————– ———–

P1                    2001-06-04

P2                    2003-05-05

P3                    2005-04-06

SYS_P501              2042-04-06

SYS_P521              2092-04-06

همانطور که می بینید، هر پارتیشنی که برای سال جدید ایجاد می شود، در تاریخ ****-04-06 ایجاد خواهد شد(04=month,06=day) تکرار می شود که این اعداد بر اساس اعداد در نظرگرفته شده برای اخرین Range partition تعریف شده جدول انتخاب شده است.

در صورتی که این بخواهیم این اعداد را برای INTERVAL PARTITIONهای جدید تغییر دهیم، می توانیم دستورات زیر را  اجرا کنیم.

–غیرفعال کردن interval partitioning:

SQL>  alter table tbl set interval();

Table altered

–تعریف پارتیشن جدید بر اساس تاریخ دلخواه:

SQL> alter table tbl add partition p100 values less than (TO_DATE(‘3030-01-01‘,’YYYY-MM-DD’));

Table altered

–فعال کردن interval partitioning:

SQL> alter table tbl set interval(NUMTOYMINTERVAL(1,’YEAR‘));

Table altered

با درج رکورد جدید، خواهیم دید که پارتیشن در روز و ماه تعریف شده ایجاد خواهد شد:

SQL> insert into tbl values(855,’test’,TO_DATE(’12-12-4040′, ‘DD-MM-YYYY’));

1 row inserted

SQL> select PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       HIGH_VALUE

——————– ———–

P1                   2001-06-04

P2                   2003-05-05

P3                   2005-04-06

SYS_P501             2042-04-06

SYS_P521             2092-04-06

P100                 3030-01-01

SYS_P541             4041-01-01

7 rows selected

 

نکته شماره 3: همانطور که می دانید در زمان اضافه کردن یک RANGE PARTITION می توان نامی را برای آن پارتیشن در نظر گرفت:

SQL>   alter table tbl add partition p2040 values less than (TO_DATE(‘4-6-2040’, ‘DD-MM-YYYY’));

Table altered

البته در صورت عدم نامگذاری پارتیشن، این کار بر عهده دیتابیس خواهد بود:

SQL>   alter table tbl add partition  values less than (TO_DATE(‘4-6-2040’, ‘DD-MM-YYYY’));

Table altered

SQL> select PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       HIGH_VALUE

——————–                   ——————–

P1                                           2001-06-04

SYS_P581                             2040-06-04

اما با فعال بودن Interval Partitioning، نمی توان با روش فوق پارتیشنی را به جدول اضافه کرد:

SQL> alter table tbl add partition p4020 values less than (4);

ORA-14760: ADD PARTITION is not permitted on Interval partitioned objects

البته از اوراکل 12cR2 می توان نام پارتیشنهای که به صورت خودکار ایجاد شده اند را برای interval partitionها تغییر داد:

SQL> select PARTITION_NAME,HIGH_VALUE,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       HIGH_VALUE INTERVAL

——————– ———- ——–

P1                   1          NO

SYS_P601             3          YES

SQL> alter table tbl rename partition for (2) to p3;

Table altered

SQL> select PARTITION_NAME,HIGH_VALUE,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME       HIGH_VALUE INTERVAL

——————– ———- ——–

P1                   1          NO

P3                   3          YES

 

نکته شماره 4: اضافه کردن دستی پارتیشن به یک جدول Range-Interval

 به روش متداول امکان اضافه کردن پارتیشن به جدولی که به صورت Range-Interval پارتیشن بندی شده است، وجود ندارد(بصورت دستی و بدون انجام عملیات DMLای منظور است) و برای انجام آن نیاز به فراهم شدن مقدماتی دارد که در ادامه با دو روش زیر نحوه انجام آن را ملاحضه خواهید کرد:

روش اول:

SQL> select PARTITION_NAME,HIGH_VALUE,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME  HIGH_VALUE            INTERVAL

————— ———–          ——–

P1               2001-06-04         NO

SQL> LOCK TABLE tbl PARTITION FOR(TO_DATE(’11-12-2021′, ‘DD-MM-YYYY’)) IN SHARE MODE;

Table(s) locked

SQL> commit;

Commit complete

SQL> select PARTITION_NAME,HIGH_VALUE,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME  HIGH_VALUE  INTERVAL

————— ———– ——–

P1              2001-06-04   NO

SYS_P664 2022-06-04   YES

یکی از کاربردهای اضافه کردن پارتیشن به روش فوق، به انجام عملیات exchange برمیگردد:

SQL> create table tbl_non_partition as select * from tbl where 1=2;

Table created

SQL> insert into tbl_non_partition values(855,’test’,TO_DATE(’09-09-2021′, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into tbl_non_partition values(855,’test’,TO_DATE(’07-07-2021′, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into tbl_non_partition values(855,’test’,TO_DATE(’08-08-2021′, ‘DD-MM-YYYY’));

1 row inserted

SQL> insert into tbl_non_partition values(855,’test’,TO_DATE(’10-10-2021′, ‘DD-MM-YYYY’));

1 row inserted

SQL> commit;

Commit complete

SQL> ALTER TABLE tbl

    EXCHANGE PARTITION FOR (TO_DATE(’11-12-2021′, ‘DD-MM-YYYY’))

      WITH TABLE tbl_non_partition;

Table altered

SQL> select count(*) from tbl_non_partition;

  COUNT(*)

———-

         0

SQL> select count(*) from tbl PARTITION FOR (TO_DATE(’11-12-2021′, ‘DD-MM-YYYY’));

  COUNT(*)

———-

         4

روش دوم: غیر فعال کردن موقت interval partitioning و اضافه کردن دستی پارتیشن:

 SQL> ALTER TABLE tbl SET INTERVAL ();

Table altered

SQL> alter table tbl add partition p4020 values less than (TO_DATE(’11-12-2021′, ‘DD-MM-YYYY’)) ;

Table altered

SQL> select PARTITION_NAME,HIGH_VALUE,INTERVAL from user_tab_partitions where table_name=’TBL’;

PARTITION_NAME  HIGH_VALUE  INTERVAL

————— ———-  ——–

P1              2001-06-04  NO

P4020           2021-12-11  NO

 

نکته شماره 5: تبدیل Range Partititoning به Interval Partititoning:

ساختار جدول tbl1 را در نظر بگیرید:

create table tbl1 (

     id number,

     name varchar2(10),

     date_        DATE

)

PARTITION BY RANGE (date_)

(

   PARTITION p1 VALUES LESS THAN (TO_DATE(‘4-1-2001’, ‘DD-MM-YYYY’))

); 

با دستور زیر قصد داریم اطلاعات مربوط به سال 2030 را در جدول tbl1 درج کنیم اما با توجه به آنکه پارتیشن p1 اطلاعاتی که تاریخ آنها مربوط به قبل از تاریخ 4-1-2001 است، را پشتیبانی می کند، دستورزیر با خطا مواجه خواهد شد:

SQL> insert into tbl1 values(6,’test’,TO_DATE(‘4-1-2030’, ‘DD-MM-YYYY’));

ORA-14400: inserted partition key does not map to any partition

با تبدیل نوع پارتیشن بندی جدول از range به interval، خواهیم دید که اوراکل در زمان درج اطلاعات خارج از محدوده، پارتیشن جدیدی را ایجاد خواهد کرد. دستور زیر، Range Partititoning را به Interval Partititoning تبدیل خواهد شد:

SQL> ALTER TABLE tbl1 SET INTERVAL(NUMTOYMINTERVAL(2,’YEAR’));

Table altered.

SQL> select partition_name, interval from   user_tab_partitions where  table_name = ‘TBL1’;

PARTITION_ INT

———- —

P1         NO

SQL> insert into tbl1 values(6,’test’,TO_DATE(‘4-1-2030’, ‘DD-MM-YYYY’));

1 row created.

SQL> select partition_name, interval from   user_tab_partitions where  table_name = ‘TBL1’;

PARTITION_ INT

———- —

P1         NO

SYS_P421   YES

نکته شماره 6: تبدیل interval  به range

همانطور که مشاهده شد، با تبدیل نوع پارتیشن بندی از range به interval، پارتیشن SYS_P421 به صورت خودکار ایجاد خواهد شد. در صورتی که قصد تغییر نوع پارتیشن بندی جدولی را از interval به range داشته باشیم، می توانیم از روش زیر استفاده کنیم:

SQL> ALTER TABLE tbl1 SET INTERVAL ();

Table altered.

SQL> select partition_name, interval from user_tab_partitions where  table_name = ‘TBL1’;

PARTITION_ INT

———- —

P1         NO

SYS_P421   NO

SQL>  insert into tbl1 values(7,’test’,TO_DATE(‘4-1-2040’, ‘DD-MM-YYYY’));

ORA-14400: inserted partition key does not map to any partition

 

نکته شماره 7: زمانی که از interval partitioning استفاده می کنیم، می توانیم پارتیشنها را به صورت round robin بین tablespaceهای مختلف توزیع کنیم. این کار با کمک عبارت store in قابل انجام است:

SQL> alter table tbl1 set STORE IN(tbs1,tbs2,tbs3,tbs4);

Table altered.