Home » RDBMS Server » Performance Tuning » Efficient Way to Alter Column For TDE.
Efficient Way to Alter Column For TDE. [message #589335] Thu, 04 July 2013 08:03 Go to next message
VIP2013
Messages: 84
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 #589340 is a reply to message #589335] Thu, 04 July 2013 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have no downtime then only the second way is possible.
Maybe you can use a third one (if you have space): add a TDE column, copy the values from the old column to the new one, set unused the old one, rename the new one.
If the old column can be updated during the process then you have to add another column, a flag one, set it to 0 at the start point then at 1 when you copy the data, when there is no more row with flag 0 you can lock the table to do the last 2 steps (lock of short time just the time you set unused the column and rename the new one).

Regards
Michel
Re: Efficient Way to Alter Column For TDE. [message #589360 is a reply to message #589340] Thu, 04 July 2013 11:38 Go to previous messageGo to next message
VIP2013
Messages: 84
Registered: June 2013
Member
Considering i have (downtime window + space) available with me, which will be the fastest of all.
i mean to say Direct Alter VS Redefinition VS copying columns?
Again i think redefinition will be slowest of all as it will involve whole table transition, am i correct?

[Updated on: Thu, 04 July 2013 11:40]

Report message to a moderator

Re: Efficient Way to Alter Column For TDE. [message #589361 is a reply to message #589360] Thu, 04 July 2013 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have the downtime you want then the first one is the easiest one.
If you have just a short downtime then the third one is the one.
This is my opinion, others may differ.

Regards
Michel
Re: Efficient Way to Alter Column For TDE. [message #589428 is a reply to message #589361] Fri, 05 July 2013 09:07 Go to previous messageGo to next message
VIP2013
Messages: 84
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

Re: Efficient Way to Alter Column For TDE. [message #589531 is a reply to message #589428] Mon, 08 July 2013 05:51 Go to previous message
VIP2013
Messages: 84
Registered: June 2013
Member
Can anybody help me with the answer.
Previous Topic: Oracle Performance Test Tools
Next Topic: Simple query is taking long time
Goto Forum:
  


Current Time: Sun Sep 21 09:20:20 CDT 2014

Total time taken to generate the page: 0.07640 seconds