| Drop column from Huge Huge tables [message #179503] |
Tue, 27 June 2006 14:32  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hello
In our production database we need to drop a column from 10 tables , those tables are really huge each having 40 million rows,
I used this statement in Test environment where number of rows are almost same since it was a week old copy from production
it is taking more than 1 hr 15 minutes to finish dropping those columns
alter table table1 column column_name;
alter table table2 column column_name;
alter table table3 column column_name;
alter table table4 column column_name;
alter table table5 column column_name;
alter table table6 column column_name;
alter table table7 column column_name;
alter table table8 column column_name;
alter table table8 column column_name;
alter table table10 column column_name;
Any alternative way can i do this task
somebody was referring the usage of execute immediate no idea how i can use here,
And this statements above are in a script so it will run one by one
i.e after finishing the first table it will go to second table
actually which is not required because there is no reference on this column being dropped.
Can i reduced the time it takes to finish it.
Version 9i Rel 2.
Thanks
[Updated on: Tue, 27 June 2006 14:33] Report message to a moderator |
|
|
|
| Re: Drop column from Huge Huge tables [message #179512 is a reply to message #179503] |
Tue, 27 June 2006 15:40  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Why would you want to drop a column? Is it not needed any more? Could you, instead of dropping it, update its value to NULL, for example?
Back to your initial question: I don't know what good would EXECUTE IMMEDIATE do here. It (actually, 10 of them) should be part of a PL/SQL block. Does Oracle submit this statement and goes to another one immediately? I really don't know Oracle that good ...
I can think of 2 another options: running 10 separate SQL*Plus sessions (or something similar) and issuing 10 different ALTER TABLE DROP COLUMN statements.
Another possibility (this one I like the most) could be writing 10 different SQL scripts, each having one ALTER TABLE DROP COLUMN statement, and submitting them as jobs.
[Updated on: Tue, 27 June 2006 15:40] Report message to a moderator |
|
|
|
|
|
| Re: Drop column from Huge Huge tables [message #179583 is a reply to message #179503] |
Wed, 28 June 2006 02:15 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If I were you, I'd use the SET UNUSED approach:
SQL> desc temp
Name Null? Type
----------------------------------------- -------- -------------
COL_1 VARCHAR2(10)
COL_2 VARCHAR2(30)
SQL> alter table temp set unused column col_2;
Table altered.
SQL> desc temp;
Name Null? Type
----------------------------------------- -------- -------------
COL_1 VARCHAR2(10)
This does not drop the columns, but effectively removes the ability to access them.
You can then use the DROP UNUSED COLUMNS command to free up the data storage during periods of low usage.
SQL> select * from user_unused_col_tabs;
TABLE_NAME COUNT
------------------------------ ----------
TEMP 1
SQL> alter table temp drop unused columns;
Table altered.
SQL> select * from user_unused_col_tabs;
no rows selected
It is much quicker to set the column as Unused than to drop it.
|
|
|
|
|
|
|
|
| Re: Drop column from Huge Huge tables [message #179765 is a reply to message #179596] |
Wed, 28 June 2006 11:26 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks Every body there for valuable suggestions,
We don't need this column any more,
Yes i can set it as unused but atlast we have to drop it,
it's 24/7 system , we are getting some 4 hrs outage window in production we have many changes to be done, and this is one among them taking 1 hr and (+).
we can't keep this column because the application code is already changed we need to drop at that day itslef, i know setting it usused will work for me but some other day i have to do it,
well will do like this
set unused this columns all on that day (not sure how much time it will take for making it unused)
and daily will drop from 1 table each.. Is this approach OK.
Once again Thanks every body.
|
|
|
|
|
|
| Re: Drop column from Huge Huge tables [message #179783 is a reply to message #179767] |
Wed, 28 June 2006 13:35 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks Mahesh for information
One last thing
If i make column unused and make the table available for all users and drop the column at later when less activity is there,
Will dropping the unused column release the space held earlier by this column.
and will the table be again locked while dropping unused column.
Thanks Again !
|
|
|
|
| Re: Drop column from Huge Huge tables [message #179789 is a reply to message #179783] |
Wed, 28 June 2006 14:42 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>> Will dropping the unused column release the space held earlier by this column.
yes. Just setting the column to be UNUSED will not do the same because, it is just a dictionary update ( That is why oracle strongly recomends the users NOT TO UPDATE the dictionary by any other means)
>>and will the table be again locked while dropping unused column
I don't know. I will set up a test case and check it.
I guess, the table will not be locked. Because oracle behaves as if it has 'forgotten' everything and anything about the unused column.
But, all the physical work will be done as usual. Oracle needs to scrub all entries it has made.
You can see, there is not much of a difference.
42 alter table emp drop unused columns
43
44
45 call count cpu elapsed disk query current rows
46 ------- ------ -------- ---------- ---------- ---------- ---------- ----------
47 Parse 1 0.00 0.00 0 0 0 0
48 Execute 1 32.48 56.42 0 2756 926814 0
49 Fetch 0 0.00 0.00 0 0 0 0
50 ------- ------ -------- ---------- ---------- ---------- ---------- ----------
51 total 2 32.48 56.42 0 2756 926814 0
52
53 Misses in library cache during parse: 1
54 Optimizer goal: CHOOSE
55 Parsing user id: 74
56
57 Elapsed times include waiting on following events:
58 Event waited on Times Max. Wait Total Waited
59 ---------------------------------------- Waited ---------- ------------
60 log buffer space 94 0.45 19.01
61 log file switch completion 2 0.22 0.31
62 log file sync 1 0.28 0.28
63 SQL*Net message to client 1 0.00 0.00
64 SQL*Net message from client 1 0.03 0.03
65 ********************************************************************************
41 alter table emp drop column mgr
42
43
44 call count cpu elapsed disk query current rows
45 ------- ------ -------- ---------- ---------- ---------- ---------- ----------
46 Parse 1 0.00 0.00 0 0 0 0
47 Execute 1 33.30 66.52 0 2936 928803 0
48 Fetch 0 0.00 0.00 0 0 0 0
49 ------- ------ -------- ---------- ---------- ---------- ---------- ----------
50 total 2 33.30 66.52 0 2936 928803 0
51
52 Misses in library cache during parse: 1
53 Optimizer goal: CHOOSE
54 Parsing user id: 74
55
56 Elapsed times include waiting on following events:
57 Event waited on Times Max. Wait Total Waited
58 ---------------------------------------- Waited ---------- ------------
59 log buffer space 132 0.71 25.86
60 log file switch completion 2 0.62 0.81
61 SQL*Net message to client 1 0.06 0.06
62 SQL*Net message from client 1 0.14 0.14
63 ********************************************************************************
[Updated on: Wed, 28 June 2006 14:43] Report message to a moderator |
|
|
|
| Re: Drop column from Huge Huge tables [message #179790 is a reply to message #179783] |
Wed, 28 June 2006 14:49 |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
- Link to documentation
| Quote: |
Specify DROP UNUSED COLUMNS to remove from the table all columns currently marked as unused. Use this statement when you want to reclaim the extra disk space from unused columns in the table.
|
- AskTom thread, indicating that even when dropping unused columns, the table will be locked.
| Quote: |
b) can I drop unused columns when users are accessing the tables (online
customers).
........
b) no, but you could use DBMS_REDEFINITION in 9i to accomplish that.
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
