If we have a relatively big table and we want to modify a column from one type to another, i.e. number to varchar2(50), we may experience very long time. Note if we just change, for example, from varchar2(10) to varchar2(50), it will not be a problem, Oracle can finish in a split of second.
This was what we faced last night during the code release. The column was acutally added in the previous release, but for some reason it has not been used and is containing no data. The problem was that we would run out of the maintenance window if we were just waiting for it to complete (obsevered it would take 4-5 hours from v$session_longops view).
In this situation, the capability of renaming a column name comes to help. We renamed the column with a suffix '_old', and added a new column with the desired data type and name. It completed in seconds.
The syntax looks like:
SQL> alter table myschema.mytab rename column MYCOL to MYCOL_old;
SQL> alter table myschema.mytab add MYCOL varchar2(50);
It is noted in the Oracle online doc that when you rename a column, Oracle updates associated data dictionary tables to ensure that function-based indexes and check constraints remain valid.
So now comes to the question what is the best way to drop those *_old columns? - There are acutully 4 tables with 8 columns were addressed in this way.
Checking the online document again, there is a section in the Admin Guide (9i) that covers some options. I did not see we can do parallel drop of a column. I did notice there is an interesting option: checkpoint, which we may want to consider as our tables are very large. For example:
ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;
The doc describes:
" This option causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space."
Rownum quiz
1 week ago
2 comments:
Indeed interesting. I thought DROP UNUSED COLUMN doesn't use undo at all, at least I can't imagine a reason to do so. Actually there is no way to make UNUSED COLUMN used again, is there?
Jurek,
Thanks for visiting my blog. I tend to agree with you that it makes no sense to use undo for this statement. I don't know why, however, I comfirmed that it do use UNDO.
In an isolated database, I created a "big" table with 1 M rows from all_objects view, then in one session I did:
z9xxx294@DBWRK920> alter table big_table set unused (object_name, object_type, created);
Table altered.
z9xxx294@DBWRK920> alter table big_table drop unused columns;
While waiting for above to complete, in another session, I measured the undo generated by the first session:
12:34:04 z9xxx294@DBWRK920> @undoused
no rows selected
12:36:24 z9xxx294@DBWRK920> /
SID USERNAME Undo segment name START_TIME Undo blocks Undo recs
------ ------------------------------ ------------------------------ -------------------- ----------- ----------
9 Z9xxx94 _SYSSMU8$ 10/14/09 12:36:19 11849 262804
12:36:28 z9xxx294@DBWRK920> /
SID USERNAME Undo segment name START_TIME Undo blocks Undo recs
------ ------------------------------ ------------------------------ -------------------- ----------- ----------
9 Z9xxx94 _SYSSMU8$ 10/14/09 12:36:19 14416 319795
12:36:36 z9xxx294@DBWRK920> @sqlsid
Enter value for sid: 9
old 16: and u.sid=&sid
new 16: and u.sid=9
SQL_TEXT ADDRESS HASH_VALUE SID USER_NAME
---------------------------------------- ---------------- ---------- ------ ------------
alter table big_table drop unused column 000000039D48C388 430430096 9 Z9xxx94
s
12:37:10 z9xxx294@DBWRK920> @undoused
SID USERNAME Undo segment name START_TIME Undo blocks Undo recs
------ ------------------------------ ------------------------------ -------------------- ----------- ----------
9 Z9xxx94 _SYSSMU8$ 10/14/09 12:36:19 36337 806124
12:37:16 z9xxx294@DBWRK920> /
SID USERNAME Undo segment name START_TIME Undo blocks Undo recs
------ ------------------------------ ------------------------------ -------------------- ----------- ----------
9 Z9xxx94 _SYSSMU8$ 10/14/09 12:36:19 80171 1778586
12:38:41 z9xxx294@DBWRK920> /
SID USERNAME Undo segment name START_TIME Undo blocks Undo recs
------ ------------------------------ ------------------------------ -------------------- ----------- ----------
9 Z9xxx94 _SYSSMU8$ 10/14/09 12:36:19 103779 2302394
12:39:25 z9xxx294@DBWRK920> /
no rows selected
Post a Comment