| Efficient Way to Alter Column For TDE. [message #589335] |
Thu, 04 July 2013 08:03  |
 |
VIP2013
Messages: 91
Registered: June 2013
|
Member |
|
|
Hi I am using 11.2.0.3.0 version of oracle.
Now we are supposed to apply column level TDE to some of our table in database. Now it will be a 'ALTER' on the columns. it involves 4 big tables out of which 3 tables having size ~30GB(one is partitioned table) and another one ~800GB(Not partitioned) Now the concern is, what will be the efficient/safest way to apply TDE on columns, below are the two options with us. (NOTE - We do have downtime window during DB maintenance but looking at the size of the table, i suspect it might take lot.)
1. Directly apply 'ALTER' on the columns. (Note- i was testing on my local, it took 3hrs for a 30GB table to ALTER the column to TDE)
2. Use Table Redefinition for Altering the column. (Creating interim table with column as TDE and then Redefining whole table).
Please suggest, if having some real time experience?
[Updated on: Thu, 04 July 2013 08:08] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Efficient Way to Alter Column For TDE. [message #589428 is a reply to message #589361] |
Fri, 05 July 2013 09:07  |
|
VIP2013
Messages: 91
Registered: June 2013
|
Member |
|
|
Hi Michel,
Thanks for the prompt reply.I am having three tables ~30GB and one ~800GB. Considering , i am having ~8hrs of downtime and required space available. given below are comparison of all the three methods available.I have some questions regarding the methods, i have mentioned that below. Can you please answer?
Online Redefinition:(Not tested)
-----------------------
Complexity:
1. It will involve in many steps and full table transition in terms of 'all Indexes/all Constraints/all grants/all Triggers' and Data.
2. All procedure are predefined one. And in case of any error in these procedures during transaction hour, it might cause issue.
3.Need additional storage equal to that of the base table.
Question:
1.Whether this method will be slowest of all three, if yes, then why?
2. Our database is highly concurrent during transaction hours, so whether online redefinition will slow down the DMLS on the base table?
Copy Column:(Tested on local for 30GB table)
-------------
complexity:
1.Less complex compare to Online Redefinition as because only columnlevel changes will be done, other dependent objects like 'Index/Constraints/Triggers/grants' will not be touched.
2.It will take extra space used for one EXTRA column. + Some UNDO.
3.it took 2 hr 20 min for 30GBtable having 80000000 record.
Direct 'Alter':(Tested on local for 30GB table)
----------------
Complexity:
1.Least complex as one line script required
for ALTER.
2.it took 3 hr for 30GBtable having 80000000 record.
3.As I tested it generates ~10 GB of undo for a 30GB table.
Question;
1.Why its taking more time than 'Copy Column' method, how its different from 'Alter' logically?
2.Whether it will take any extra space?
[Updated on: Fri, 05 July 2013 14:32] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
