نکاتی در مورد حذف ستون از جدول – پایگاه داده اوراکل

زمانی که دستور drop column را اجرا می کنیم، اوراکل باید تمامی بلاکهای جدول را به حافظه منتقل کرده و اطلاعات مربوط به ستون را از همه رکوردهای حاضر در بلاک حذف کند.

در کنار عملیات I/O و پردازش رکورد، جدول هم به صورت exclusive قفل خواهد شد و مضاف بر آن، حدف ستون منجر به ایجاد redo و undo هم می شود. در نتیجه حذف ستون از یک جدول حجیم می تواند کار بسیار پرهزینه و زمانبری باشد.مثال زیر را ببینید:

–تعداد بلاکهای جدول:

SQL> select blocks,bytes from dba_segments where segment_name=’MYTBL’;

BLOCKS BYTES

———- ———-

34816 285212672

–تعداد بلاکهای جدول در buffer cache قبل از انجام عملیات drop column:

SQL> select count(distinct l.BLOCK#) from v$bh l where objd=(select l.object_id from dba_objects l where l.object_name=’MYTBL’);

COUNT(DISTINCTL.BLOCK#)

———-

–حذف فیزیکی ستون c1 از جدول MYTBL:

SQL> alter table MYTBL drop column c1 ;

Table altered

Executed in 45.906 seconds

–تعداد بلاکهای جدول در buffer cache بعد از انجام عملیات drop column:

SQL> select count(distinct l.BLOCK#) from v$bh l where objd=(select l.object_id from dba_objects l where l.object_name=’MYTBL’);

COUNT(DISTINCTL.BLOCK#)

———————–

34184

به این شیوه از حذف ستون، حذف فیزیکی گفته می شود. با بازنگری تعداد بلاکها و حجم جدول MYTBL خواهیم دید که حذف ستون منجر به کم شدن فضای تخصیص داده شده به جدول نمی شود:

SQL> select blocks,bytes from dba_segments where segment_name=’MYTBL’;

BLOCKS BYTES

———- ———-

34816 285212672

و برای برگرداندن فضای خالی، باید جدول بازسازی شود:

SQL> alter table MYTBL move parallel 10;

Table altered

SQL> select blocks,bytes from dba_segments where segment_name=’MYTBL’;

BLOCKS BYTES

———- ———-

25032 205062144

همانطور که ملاحظه شد، حذف ستون c1 از جدول MYTBL حدودا 45 ثانیه زمان برده است که این زمان برای جداول حجیم می تواند بسیار طولانی تر شود.

حجم redo و undo تولید شده را هم با کمک پرس و جوی زیر می بینیم:

SQL> select name, round(value / 1024 / 1024) “SIZE_MB”

from v$mystat, v$statname

where v$mystat.statistic# = v$statname.statistic#

and (v$statname.name = ‘redo size’ or

v$statname.name = ‘undo change vector size’);

NAME SIZE_MB

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

redo size 2170

undo change vector size 756

توجه: با پرس و جوی زیر می توان حجم Active undo را زیر نظر داشت کرد:

select tablespace_name, status, sum(blocks) * 8192/1024/1024 MB from dba_undo_extents group by tablespace_name, status;

پیشرفت اجرای دستور drop column را می توان با دستور زیر نظارت کرد:

SQL> select sofar,totalwork, round(sofar/totalwork*100) “% Complete” from v$session_longops p where target=’USEF.MYTBL’;

SOFAR TOTALWORK % Complete

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

13144 33947 39

حذف منطقی ستون

اوراکل از نسخه 8i برای این مسئله راهکار دیگری ارائه داده است که ان هم حذف منطقی ستون، قبل از حذف فیزیکی آن می باشد. با کمک این قابلیت می توان، ستون را به unused علامت زده و عملیات حذف فیزیکی را در زمانی خارج از پیک کاری دیتابیس انجام داد.

با این کار ستون حذف شده از دید کاربر مخفی خواهد ماند و تمامی ایندکسها و محدودیتهای ایجاد شده بر روی این ستون هم حذف خواهند شد و حتی می توان ستون جدیدی با این نام برای جدول ایجاد کرد:

SQL> select count(distinct l.BLOCK#) from v$bh l where objd=(select l.object_id from dba_objects l where l.object_name=‘MYTBL’);

COUNT(*)

———-

SQL> create index dfgdf on MYTBL (c1);

Index created

SQL> select count(*) from user_indexes where table_name=’MYTBL’;

COUNT(*)

———-

SQL> alter table usef.MYTBL Set Unused(c1);

Table altered

Executed in 0.083 seconds

SQL> select count(*) from user_indexes where table_name=’MYTBL’;

COUNT(*)

———-

SQL> desc MYTBL

Name Type Nullable Default Comments

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

C3 VARCHAR2(7) Y

SQL> select count(distinct l.BLOCK#) from v$bh l where objd=(select l.object_id from dba_objects l where l.object_name=’MYTBL’);

COUNT(DISTINCT L.BLOCK#)

———————–

239

توجه! دستور alter table set unused قابلیت برگشت پذیری ندارد!!!

لیست جداولی که ستونی از آنها صرفا به صورت منطقی حذف شده را می توان از طریق ویوی DBA_UNUSED_COL_TABS و DBA_PARTIAL_DROP_TABS مشاهده کرد:

SQL> SELECT * FROM DBA_UNUSED_COL_TABS;

OWNER TABLE_NA COUNT

—– ——– ———-

USEF MYTBL 1

در نهایت برای حذف فیزیکی این ستون می توان(در زمانی خارج از پیک کاری سیستم) دستور زیر را اجرا کرد:

SQL> alter table MYTBL drop unused columns;

Table altered

Executed in 55.936 seconds

SQL> SELECT * FROM DBA_UNUSED_COL_TABS;

no rows selected

همانطور که می بینید، حذف فیزیکی این ستون، حدودا به 50 ثانیه زمان نیاز دارد.

مشاهده جزییات بیشتری از وضعیت بلاک بعد از حذف منطقی ستون

در ادامه با مشاهده محتویات بلاکهای جدول خواهیم دید که اطلاعات ستونها کماکان در بلاکهای جدول موجود هستند:

SQL> alter table MYTBL Set Unused(c3);

Table altered

Executed in 0.02 seconds

SQL> alter table MYTBL Set Unused(c2);

Table altered

Executed in 0.02 seconds

SQL> SELECT * FROM DBA_UNUSED_COL_TABS;

OWNER TABLE_NA COUNT

—– ——– ———-

USEF MYTBL 2

SQL> select column_name,

data_type,

column_id,

hidden_column,

segment_column_id seg_cid,

internal_column_id internal_cid

from dba_tab_cols

where owner = ‘USEF’

and table_name = ‘MYTBL’;

SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,c3 from MYTBL where rownum<=1;

FILE# BLOCK# C3

———- ———- ——-

2 130331 ONLINE

SQL> alter system dump datafile 2 block 130331;

System altered

SQL> SELECT p.tracefile FROM v$session s JOIN v$process p ON s.paddr = p.addr WHERE s.sid =(SELECT sid FROM v$mystat WHERE ROWNUM = 1);

TRACEFILE

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

/oracle19c/base/diag/rdbms/oracledb/oracledb/trace/oracledb_ora_23630.trc

با مشاهده محتویات فایل تریس، اطلاعات ستونهای در حالت Unused هم قابل مشاهده هستند:

block_row_dump:

tab 0, row 0, @0x1f68

tl: 24 fb: –H-FL– lb: 0x0 cc: 3

col 0: [ 6] 4f 4e 4c 49 4e 45

col 1: [ 6] 4f 4e 4c 49 4e 45

col 2: [ 6] 4f 4e 4c 49 4e 45

tab 0, row 1, @0x1f50

tl: 24 fb: –H-FL– lb: 0x0 cc: 3

col 0: [ 6] 4f 4e 4c 49 4e 45

col 1: [ 6] 4f 4e 4c 49 4e 45

col 2: [ 6] 4f 4e 4c 49 4e 45

tab 0, row 2, @0x1f38

tl: 24 fb: –H-FL– lb: 0x0 cc: 3

col 0: [ 6] 4f 4e 4c 49 4e 45

col 1: [ 6] 4f 4e 4c 49 4e 45

col 2: [ 6] 4f 4e 4c 49 4e 45

ستونهای UNUSED را حذف می کنیم:

SQL> alter table MYTBL drop unused columns;

Table altered

اطلاعات بلاک را بازبینی می کنیم:

SQL> alter system dump datafile 2 block 130331;

System altered

محتویات بلاک:

block_row_dump:

tab 0, row 0, @0x1f68

tl: 10 fb: –H-FL– lb: 0x2 cc: 1

col 0: [ 6] 4f 4e 4c 49 4e 45

tab 0, row 1, @0x1f50

tl: 10 fb: –H-FL– lb: 0x2 cc: 1

col 0: [ 6] 4f 4e 4c 49 4e 45

tab 0, row 2, @0x1f38

tl: 10 fb: –H-FL– lb: 0x2 cc: 1

col 0: [ 6] 4f 4e 4c 49 4e 45

نکته: استفاده از ویژگی alter table set unused با محدودیتهای روبرو می باشد برای مثال، نمی توان ستونی که جزیی از partition key هست را حذف کرد.

استفاده از عبارت checkpoint

در زمان حذف فیزیکی ستونی که در حالت UNUSED قرار دارد، ممکن است sessionای که دستور را اجرا کرده به دلایلی چون کرش کردن دیتابیس، kill شود در این صورت نیاز است تا عملیات drop column مجددا اجرا شود.

در کنار این خطر احتمالی، حذف فیزیکی یک ستون می تواند منجر به افزایش ACTIVE undo و متعاقب آن، سرریز شدن فضای undo tablespace شود.

به این جهت اوراکل عبارت checkpoint n را به دستور alter table .. drop unused columns اضافه کرد که با پردازش هر n رکورد توسط دستور drop column، عملیات انجام شده، commit خواهد شد.

با هر بار انجام commit توسط دستور، ACTIVE undo به UNEXPIRE undo تغییر خواهد کرد در نتیجه حجم کلی ACTIVE undo از حد مشخصی بیشتر نخواهد شد.

بنابرین عدد تعیین شده برای عبارت checkpoint، تعداد رکوردها را مشخص خواهد کرد که این کار می تواند مدت زمان انجام عملیات حذف را بسیار افزایش دهد:

SQL> alter table usef.MYTBL Set Unused(c1);

Table altered

Executed in 0.02 seconds

SQL> alter table MYTBL drop unused columns Checkpoint 250;

Table altered

Executed in 65.147 seconds

همانطور که می بینید، زمان حذف از 45 ثانیه به 65 ثانیه رسیده است.

توجه: در صورت استفاده از عبارت checkpoint، پیشرفت عملیات drop column در ویوی v$session_longops نمایش داده نخواهد شد.

DROP COLUMNS CONTINUE

اگر دستور drop column Checkpoint به هر دلیلی با خطا متوقف شود، اجرای دستورات DDLای و DMLای با خطا مواجه خواهد شد(به جز TRUNCATE TABLE) و عملا جدول در وضعیت unusable قرار خواهد گرفت:

SQL> alter table MYTBL drop unused columns Checkpoint 250;

ORA-01013: user requested cancel of current operation

SQL> delete MYTBL where rownum<=1;

ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE

SQL> select count(*) from MYTBL;

ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE

در این صورت، تنها راهکار، اجرای دستور ALTER TABLE DROP COLUMNS CONTINUE خواهد بود:

SQL> ALTER TABLE MYTBL DROP COLUMNS CONTINUE;

Table altered

SQL> delete MYTBL where rownum<=1;

1 row deleted

#در حاشیه

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

نامرئی کردن یک ستون از جدول

با invisible کردن یک ستون هم می توان آن ستون را از دید کاربران و برنامه مخفی نگه داشت با این تفاوت که این تغییر قابل برگشت خواهد بود:

SQL> ALTER TABLE MYTBL MODIFY c1 INVISIBLE;

Table altered

SQL> desc MYTBL;

Name Type Nullable Default Comments

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

C2 VARCHAR2(7) Y

C3 VARCHAR2(7) Y

C1 VARCHAR2(7) Y

SQL> select * from MYTBL where rownum<=1;

C2 C3

——- ——-

ONLINE ONLINE

SQL> insert into MYTBL values(‘test’,’test’);

1 row inserted

برای برگرداندن این تغییر، کافیست دستور زیر را اجرا کنیم:

SQL> ALTER TABLE MYTBL MODIFY c1 VISIBLE;

Table altered

SQL> select * from MYTBL where rownum<=1;

C2 C3 C1

——- ——- ——-

ONLINE ONLINE ONLINE

وحید یوسف زاده

ارائه خدمات مشاوره ، پشتیبانی و نصب و راه اندازی پایگاه داده اوراکل در سراسر کشور...................... تلفن: 09128110897 ایمیل:vahidusefzadeh@gmail.com

حذف منطقی ستون

مشاهده جزییات بیشتری از وضعیت بلاک بعد از حذف منطقی ستون

استفاده از عبارت checkpoint

DROP COLUMNS CONTINUE

نامرئی کردن یک ستون از جدول

وحید یوسف زاده

دیدگاهتان را بنویسید لغو پاسخ