شرایط جدول mtbl را در نظر بگیرید:

SQL>  select count(*) from mtbl;

16777216

SQL>  select to_char(creation_time,’YYYY’,’nls_calendar=persian’),count(*) from mtbl group by to_char(creation_time,’YYYY’,’nls_calendar=persian’) order by 1 desc;

TO_CHAR(CREATION_TIME,’YYYY’,’   COUNT(*)

—————————— ———-

1399                               262144

1397                              3932160

1396                              4194304

1395                              4194304

1394                              4194304

Executed in 4.563 seconds

حجم جدول mtbl:

SQL> select bytes/1024/1024 SIZE_MB from user_segments p where p.segment_name=’MTBL’;

   SIZE_MB

———-

      4286

قصد داریم رکوردهایی از این جدول که creation_time آنها مربوط به سال 1399 بوده را در جدول حفظ کرده و مابقی اطلاعات را حذف کنیم.

این کار به روشهای مختلفی قابل انجام است که ساده ترین آنها، اجرای دستور delete به شکل زیر می باشد:

SQL> alter system flush buffer_cache;

System altered

SQL> delete mtbl where to_char(creation_time,’YYYY’,’nls_calendar=persian’)!=’1399′;

16515072 rows deleted

Executed in 1422.139 seconds

SQL> select count(*) from MTBL;

  COUNT(*)

———-

    262144

SQL> select bytes/1024/1024 SIZE_MB from user_segments p where p.segment_name=’MTBL’;

   SIZE_MB

———-

      4286

همانطور که می بینید، حذف اطلاعات در مدت زمان 1422 ثانیه انجام شده است و تغییری در فضای مصرفی جدول ایجاد نشده است.

می دانیم که برای سرعت بخشیدن به این عملیات می توان از روشهای دیگری هم استفاده کرد نظیر CTAS، drop partition، truncate partition، استفاده از بسته dbms_parallel_execute و …

که البته فاکتورهایی چون حجم جدول، درصد اطلاعاتی که قرار است از جدول حذف شوند، میزان downtime برای این کار و حتی نسخه دیتابیس در انتخاب روش حذف موثر خواهند بود.

در این متن قصد داریم به قابلیت جدیدی که در اوراکل 12cR2 ارائه شده است و می تواند سرعت عملیات حذف را در شرایط فوق افزایش دهد، پرداخته و مطالبی را ارائه کنیم.

این دستور، alter table move including rows می باشد که می تواند در زمان حذف درصد بالایی از اطلاعات یک جدول بسیار موثر واقع شود و سرعت عملیات حذف را افزایش دهد.

این دستور مشابه CTAS عمل می کند با این تفاوت که downtime در سطح جدول خواهد بود و مداخله DBA هم به حداقل می رسد به این صورت که در هنگام اجرای دستور، جدول در حالت ROW SHARE قفل خواهد شد و امکان انجام عملیات DMLای بر روی جدول برای sessionهای دیگر هم وجود دارد و تنها در زمان switch بین دو جدول(جدول temp و mtbl) نیاز به یک قفل از نوع exclusive خواهیم داشت. البته در مجموع CTAS سرعت بهتری نسبت به این روش دارد(صرف نظر از downtime).

در این روش اوراکل صرفا رکوردهای که در قسمت including rows مشخص شده اند را به فضای جدیدی انتقال داده و در نهایت جدول قبلی را حذف می کند(با تغییر نام temp segment به mtbl). البته در صورت استفاده از عبارت online، ایندکسها هم در وضعیت معتبر باقی خواهند ماند. در ادامه متن، حذف اطلاعات جدول mtbl را با استفاده از این روش تکرار می کنیم.

SQL> alter system flush buffer_cache;

System altered

SQL> alter table mtbl move online including rows where to_char(creation_time,’YYYY’,’nls_calendar=persian’)=’1399′;

Table altered

Executed in 52.881 seconds

SQL> select bytes/1024/1024 SIZE_MB from user_segments p where p.segment_name=’MTBL’;

   SIZE_MB

———-

        72

SQL> select count(*) from MTBL;

  COUNT(*)

———-

    262144

همانطور که می بینید، این عملیات در مدت زمان 52 ثانیه انجام شده است که نسبت به روش قبلی که در زمان 1422 ثانیه انجام شد، بسیار قابل توجه می باشد.

در کنار حذف اطلاعات قدیمی جدول MTBL با دستور alter table move including rows می توان ساختار جدول را از حالت non-partition به partition تغییر داد:

SQL> alter table MTBL modify

  2    partition by range ( creation_time )

  3    interval ( numtoyminterval(1,’MONTH‘))

  4     (

  5       PARTITION p1 VALUES LESS THAN (TO_DATE(‘1-1-1399’, ‘DD-MM-YYYY’,’nls_calendar=persian’))

  6     )online

  7   including rows where to_char(creation_time,’YYYY’,’nls_calendar=persian’) =’1399′;

 Table altered

Executed in 59.256 seconds

SQL> select table_name,partition_name from user_tab_partitions;

TABLE_ PARTITION_NAME

—— —————

MTBL   P1

MTBL   SYS_P866