پارتیشن بندی آنلاین یک جدول با dbms_redefinition

اگر شما بخواهید در سایت عملیاتی یک جدول که مدام زیر بار هست رو تغییر ساختار بدید، ۲ راه دارید یا downtime بدید یا از پکیج dbms_redefinition استفاده کنید. خوب پس با من هراه باشید تا با 100% availability تغییرات رو انجام بدیم.

تغییرات ساختار هر چیزی میتواند باشد، در اینجا قصد داریم یک heap table را به partitioned table بدون downtime همراه با dml کاربران تغییر ساختار بدیم.

• چون این کار را برای تست انجام میدهیم اول یک یوزر معمولی می سازیم و دسترسی های مورد نیاز رو به آن میدهیم:

create user masoud account unlock identified by masoud quota unlimited on users;
grant create table, create session to masoud;
grant create sequence to masoud;

• حال با آن کاربر متصل شده و یک جدول معمولی ایجاد میکنیم و آن را با ۵۰،۰۰۰ رکورد پر میکنیم:

create sequence masoud_seq;

create table mytab (
id  number(20),
cdate date
);

begin
  for i in 1..1000 loop
    for j in 1..50 loop
      insert into mytab values (masoud_seq.nextval, sysdate + i);
    end loop;
  end loop;
  commit;
end;
/

تا الان ما یک جدول داریم به نام mytab که پارتیشن شده نیست و از الان به بعد با دستورات dbms_redefinition میخواهمیم آن را در حالی که کاربران حال query زدن و اجرای DML هستند به یک جدول پارتیشن شده تیدیل کنیم.

• اول بررسی میکنیم ببینیم جدول جاری میتواند از تکنیک dbms_redefinition استفاده کند یا خیر :

 select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

begin
DBMS_REDEFINITION.CAN_REDEF_TABLE(
  UNAME => 'MASOUD',
  TNAME => 'MYTAB',
  OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID
);
end;
/

PL/SQL procedure successfully completed.

همانگونه که میبینید خروجی با مشکلی رو به رو نشد و error هم نداشتیم پس جدول رو میتوانیم به صورت online تغییر ساختار بدیم. یک نکته در مورد CONS_USE_ROWID هست اون هم اینکه جدول یا باید دارای primary key باشد یا اگر نیست از تکنیک فوق استفاده کنید. درصورتی که این option رو انتخاب نکنید توی روال با مشکل رو به رو میشوید.

• در این مرحله یک جدول با ساختار partition شده میسازیم که میخواهیم جدول mytab به شکل آن باشد:

create table interim (
id  number(20),
cdate date
)
partition by range(cdate) interval (numtodsinterval(1,'day'))
(PARTITION "P1"  VALUES LESS THAN (TO_DATE(' 2017-05-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));

جدول جاری من در حال حاضر ۵۰،۰۰۰ رکورد داد. پارتیشن های من در ساختار جدید هرکدام ۵۰ رکورد دارد، اگر query ها ای که به سمت پایگاه داده می آید table full scan باشد و اگر ما دنبال یک رکورد خاص با جستجو روی cdate باشیم احتمال partition pouring  بسیار زیاد است و سرعت اجرای query به مراتب بیشتر خواهد بود.

• خوب حال شروع به انجام redefinition میکنیم:

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE
 (uname=>'MASOUD',
 orig_table=>'MYTAB',
 int_table=>'interim',
 options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);
end;
/

PL/SQL procedure successfully completed.

اگر این مرحله بسیار زمان بر بود بهتره که SYNC_INTERIM_TABLE گهگاه اجرا کنیم تا در مرحله  FINISH_REDEF_TABLE زمان lock کمتری داشته باشیم.

• در این مرحله وابستگی ها و object های مرتبط را به جدول interim میدهیم:

vari p_errors number;

BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
 (uname=>'MASOUD',
 orig_table=>'MYTAB',
 int_table=>'INTERIM',
 num_errors=>:p_errors);
END;
/

PL/SQL procedure successfully completed.

SQL> print p_errors;
P_ERRORS
----------
0

در صورتی که روال بالا به مشکل رو به رو شده باشد p_errors مقدار خواهد داشد، که الان مشکلی را نشان نمیدهد.

• تا الان جدول mytab بصورت قلبی پابرجاست و حدول interim پارتیشن شده است و اطلاعات mytab در درون آن کپی شده است.

SQL> select table_name from user_part_tables;

TABLE_NAME
------------------------------
INTERIM

• درصورت مشکل در هرکدام از مراحل فوق  با دستور زیر فرایند redefinition را متوفق نمایید:

begin
  DBMS_REDEFINITION.ABORT_REDEF_TABLE
  (
    UNAME=>'MASOUD',
    ORIG_TABLE=>'MYTAB',
    INT_TABLE=>'interim');
end;
/

PL/SQL procedure successfully completed.

• و این هم آخرین مرحله از redefinition:

begin
dbms_redefinition.finish_redef_table
 (uname=>'MASOUD',
 orig_table=>'MYTAB',
 int_table=>'INTERIM');
end;
/

PL/SQL procedure successfully completed.

در این مرحله اسم جداول با یکدیگر جابجا شده و DML ها به صورت واقعی جدول با ساختار جدید را تحت تاثیر قرار میدهد.

• در نهایت بررسی میکنیم ببینیم جدول پارتیشن شده است یا خیر:

select table_name,partitioning_type from user_part_tables;

TABLE_NAME                     PARTITION
------------------------------ ---------
MYTAB                          RANGE

select count(1) as cnt from user_tab_partitions;

       CNT
----------
      1001

select index_name,table_name from user_indexes;

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
MYTAB_ID_PX                    MYTAB
TMP$$_MYTAB_ID_PX0             INTERIM

دوستان عزیز امیدوارم مطلب کاربردی بوده باشد.

شاد و پیروز باشید.