با ارائه نسخه 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
وحید یوسف زاده
ارائه خدمات مشاوره ، پشتیبانی و نصب و راه اندازی پایگاه داده اوراکل در سراسر کشور...................... تلفن: 09128110897 ایمیل:vahidusefzadeh@gmail.com