پارتیشن بندی انلاین جداول در اوراکل 12cR2 – وحید یوسف زاده

پارتیشن بندی انلاین جداول در اوراکل 12cR2

با ارائه نسخه 12cR2 قابلیت جدیدی در زمینه پارتیشن بندی ارائه شد که به لحاظ پیاده سازی، پیچیدگی بسیار کمتری را نسبت به روشهای رایج پارتیشن بندی نظیر بسته dbms_redefintion دارد و صرفا با اجرای یک دستور ALTER TABLE .. MODIFY، پارتیشن بندی را برای جداول امکان پذیر خواهد کرد.

در ادامه با ارائه یک سناریو، شیوه استفاده از این قابلیت را شرح خواهیم داد.

برای ارائه این سناریو، ابتدا جدولی با نام mytbl را به صورت non-partition ایجاد می کنیم:

SQL> create table usef.mytbl(id number(9), birth_date date);

Table created

اطلاعاتی را در این جدول درج می کنیم:

SQL> insert into mytbl values(1,TO_DATE(’06/05/1352 00:00:00′,’MM/DD/YYYY HH24:MI:SS’));

1 row inserted

SQL> insert into mytbl values(2,TO_DATE(’05/06/1357 00:00:00′,’MM/DD/YYYY HH24:MI:SS’));

1 row inserted

SQL> insert into mytbl values(3,TO_DATE(’04/04/1368 00:00:00′,’MM/DD/YYYY HH24:MI:SS’));

1 row inserted

SQL> insert into mytbl values(4,TO_DATE(’03/05/1369 00:00:00′,’MM/DD/YYYY HH24:MI:SS’));

1 row inserted

SQL> insert into mytbl values(5,TO_DATE(’07/17/1370 00:00:00′,’MM/DD/YYYY HH24:MI:SS’));

1 row inserted

SQL> insert into mytbl values(6,TO_DATE(’04/08/1371 00:00:00′,’MM/DD/YYYY HH24:MI:SS’));

1 row inserted

SQL> insert into mytbl values(7,TO_DATE(’08/16/1375 00:00:00′,’MM/DD/YYYY HH24:MI:SS’));

1 row inserted

SQL> insert into mytbl values(8,TO_DATE(’05/25/1376 00:00:00′,’MM/DD/YYYY HH24:MI:SS’));

1 row inserted

SQL> insert into mytbl values(9,TO_DATE(’02/14/1381 00:00:00′,’MM/DD/YYYY HH24:MI:SS’));

1 row inserted

SQL> commit;

Commit complete

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

SQL> create index ind00 on mytbl(id);

Index created

SQL> create index ind01 on mytbl(birth_date);

Index created

قصد داریم جدول mytbl را بر اساس ستون birth_date و با کمک دستور ALTER TABLE .. MODIFY پارتیشن بندی کنیم. این کار به دو طریق OFFLINE و ONLINE قابل انجام خواهد بود که در حالت ONLINE، اجرای همزمان دستورات DMLای بر روی جدول مورد نظر امکان پذیر می شود.

با اجرای دستور زیر، جدول mytbl را به صورت انلاین، پارتیشن بندی خواهیم کرد:

SQL> ALTER TABLE usef.mytbl MODIFY

    PARTITION BY RANGE (birth_date)

    INTERVAL(NUMTOYMINTERVAL(12, ‘MONTH’))

    (

    partition p1 VALUES LESS THAN(TO_DATE(’02/01/1360 00:00:00′,’MM/DD/YYYY HH24:MI:SS’)),

    partition p2 VALUES LESS THAN(TO_DATE(’02/01/1370 00:00:00′,’MM/DD/YYYY HH24:MI:SS’)),

    partition p3 VALUES LESS THAN(TO_DATE(’02/01/1380 00:00:00′,’MM/DD/YYYY HH24:MI:SS’))

    )ONLINE;

 Table altered

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

SQL> select owner,table_name,partitioning_type,interval from dba_part_tables p where p.table_name=’MYTBL’;

OWNER           TABLE_NAME PARTITIONING_TYPE  INTERVAL

USEF    MYTBL RANGE NUMTOYMINTERVAL(12, ‘MONTH’)

همچنین با انجام پارتیشن بندی، وضعیت ایندکسهای ind00 و ind01 همچنان در حالت USABLE قرار خواهد داشت:

SQL> select index_name,partition_name,status from user_ind_partitions where index_name=’IND01′;

INDEX_NAME  PARTITION_NAME      STATUS

IND01              P1                                USABLE

IND01              P2                                USABLE

IND01              P3                                USABLE

IND01  SYS_P1117                              USABLE

در این روش از پارتیشن بندی، اگر ستونهای ایندکس در partition key موجود باشند و یا بعبارت دیگر، ایندکس به صورت prefixed indexe باشد، نوع پارتیشن بندی آن هم به صورت LOCAL خواهد بود و در غیر این صورت، نوع پارتیشن بندی ایندکس، به GLOBAL تغییر خواهد کرد:

SQL> select index_name,locality from dba_part_indexes where index_name in (‘IND01’);

INDEX_NAME  LOCALITY

IND01              LOCAL

برای تعیین نوع پارتیشن بندی ایندکسها به همراه پارتیشن بندی جداول، می توان از عبارت UPDATE INDEXES به همراه دستور ALTER TABLE استفاده کنیم.

برای مثال، با دستور زیر، ایندکس ind00 به صورت GLOBAL و ایندکس ind01 به صورت LOCAL ایجاد خواهد شد.

SQL> ALTER TABLE usef.mytbl MODIFY

PARTITION BY RANGE (birth_date)

INTERVAL(NUMTOYMINTERVAL(12, ‘MONTH’))

(

partition p1 VALUES LESS THAN(TO_DATE(’02/01/1360 00:00:00′,’MM/DD/YYYY HH24:MI:SS’)),

partition p2 VALUES LESS THAN(TO_DATE(’02/01/1370 00:00:00′,’MM/DD/YYYY HH24:MI:SS’)),

partition p3 VALUES LESS THAN(TO_DATE(’02/01/1380 00:00:00′,’MM/DD/YYYY HH24:MI:SS’))

)ONLINE

UPDATE INDEXES

(

  ind00 GLOBAL

   PARTITION BY RANGE (id)

  (

    PARTITION p1 VALUES LESS THAN (MAXVALUE)

  )  ,

  ind01 LOCAL

);

 Table altered

دستور زیر نشان می دهد که، ایندکسها به فرم مطلوب پارتیشن بندی شده اند:

SQL> select index_name,locality from dba_part_indexes where index_name in (‘IND00′,’IND01’);

INDEX_NAME  LOCALITY

IND00              GLOBAL

IND01              LOCAL

در ابتدای متن، مختصرا اشاره شد که انجام پارتیشن بندی با کمک دستور ALTER TABLE .. MODIFY، به دو صورت ONLINE و OFFLINE امکان پذیر خواهد بود که به صورت پیش فرض، این عمل به صورت OFFLINE انجام می شود به این صورت که به محض اجرای دستور ALTER TABLE .. MODIFY، جدول در حالت EXCLUSIVE قفل خواهد شد.

با مشاهده فایل تریس این دستور، به این نتیجه خواهیم رسید:

PARSING IN CURSOR #140360712535064 len=52 dep=1 uid=0 oct=26 lid=0 tim=2940763017907 hv=1690059723 ad=’75364028′ sqlid=’1k744w1kbshyb’

LOCK TABLE “USEF”.”MYTBL” IN EXCLUSIVE MODE  NOWAIT

در این صورت، اجرای دستورات DMLای با EVENTای به نام enq: TM – contention در حالت انتظار قرار خواهد گرفت.

حال اگر از عبارت ONLINE به همراه دستور ALTER TABLE .. MODIFY استفاده شود، انجام همزمان عملیات DMLای بر روی جدول مورد نظر، امکان پذیر خواهد بود منتها در لحظه استارت و خاتمه یافتن دستور ALTER TABLE .. MODIFY، نباید دستور DMLایی را بر روی این جدول بلاتکلیف رها کرد در غیر این صورت، اجرای دستور ALTER TABLE، باEVENT ای با نام enq: TX – row lock contention در حالت انتظار قرار خواهد گرفت.

با مشاهده فایل تریس این دستور، خواهیم دید که در ابتدا جدول در حالت  ROW EXCLUSIVE قفل خواهد شد:

PARSING IN CURSOR #140552670379032 len=56 dep=1 uid=0 oct=26 lid=0 tim=2934105997185 hv=3487644036 ad=’7232a7a8′ sqlid=’32rmgdz7y2fc4′

LOCK TABLE “USEF”.”MYTBL” IN ROW EXCLUSIVE MODE  NOWAIT

در حین انجام عملیات پارتیشن بندی به کمک دستور ALTER TABLE .. MODIFY جداول متعددی ایجاد خواهند شد که یکی از این جداول SYS_JOURNAL_nnnnn می باشد این جدول، تغییراتی که در طول اجرای دستور ALTER TABLE .. MODIFY بر روی جدول mytbl اتفاق می افتد، را در خود ثبت می کند:

PARSING IN CURSOR #140552669808440 len=125 dep=1 uid=0 oct=1 lid=0 tim=2934106191858 hv=1147115125 ad=’7fd4f80ab7c0′ sqlid=’7cdc76125z5mp’

create table “USEF”.”SYS_JOURNAL_85904″ (rid rowid, opcode char(1), primary key(rid)) organization index tablespace “MYTBS01”

توجه! عدد 85904 که در نام جدول SYS_JOURNAL_85904 مشاهده می کنید، شماره OBJECT_ID جدول mytbl می باشد.

فرضا اگر دستور زیر را در حین انجام عملیات پارتیشن بندی اجرا کنیم:

SQL> insert into mytbl values(100,TO_DATE(’12/01/1397 00:00:00′,’MM/DD/YYYY HH24:MI:SS’));

SQL> commit;

SQL> select rowid from mytbl where id=100;

AAAVHwAAFAAAPVbAAA

rowid رکورد مربوطه، در جدول SYS_JOURNAL_85904 ثبت خواهد شد:

SQL> select * from SYS_JOURNAL_85904;

RID                                          OPCODE

AAAVHwAAFAAAPVbAAA      I

در مثال زیر خواهیم دید که امکان همزمانی دستورات DMLای و دستور ALTER TABLE .. MODIFY وجود دارد.

مثال: فرض کنید کاربری دستور زیر را وارد کرده است(بدون انجام commit و یا rollback):

–session 1:

 SQL> insert into mytbl values(10,TO_DATE(’07/17/1390 00:00:00′,’MM/DD/YYYY HH24:MI:SS’));

 1 row inserted

در همین حال، کاربر دیگری قصد دارد تا دستور ALTER TABLE .. MODIFY را بر روی جدول mytbl اجرا کند:

–session 2

SQL>ALTER TABLE usef.mytbl MODIFY

PARTITION BY RANGE (birth_date)

INTERVAL(NUMTOYMINTERVAL(12, ‘MONTH’))

)online

Executing…

در این صورت، دستور ALTER TABLE .. MODIFY به حالت کما خواهد رفت و تا زمانی که تکلیف دستور insert مشخص نشود، در این state باقی خواهد ماند! پس با انجام commit توسط کاربر اول، دستور ALTER TABLE هم اجرا خواهد شد:

–session 1:

SQL> commit;

 Commit complete

–session 2:

Table altered

همچنین اگر جدول mytbl نسبتا حجیم باشد، امکان انجام عملیات DML در حین اجرای دستور ALTER TABLE امکان پذیر خواهد بود:

session 1:

SQL>ALTER TABLE usef.mytbl MODIFY

PARTITION BY RANGE (birth_date)

INTERVAL(NUMTOYMINTERVAL(12, ‘MONTH’))

Executing…

–session 2:

SQL> delete mytbl where id=6;

1 rows deleted

SQL> delete mytbl where id=7;

1 rows deleted

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *