Efficient Method to Alter Column.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Efficient Method to Alter Column.

  1. #1
    Join Date
    Jul 2013
    Posts
    7

    Efficient Method to Alter Column.

    Hi I am using 11.2.0.3.0 version of oracle.

    Now we are supposed to apply column level TDE(Transparent data encryption) to some of our table in database. 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 8 hrs of downtime window during DB maintenance but looking at the size of the table, i suspect it might cross.)

    i think , below are option available with me, i have some questions as mentioned below, please suggest?



    Copy Column:(Tested on local for 30GB table)
    -----------------------------------------------------------------
    1.Create new column as TDE enabled.
    2.Copy data from existing column to new one through ROWID reference, may be using 'FOR ALL' .
    3. Mark the existing column Unused, and rename the new column.


    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)
    ---------------------------------------------------------------------
    1.Simply execute 'ALTER' script for altering the column.
    E.g. ALTER table tab add (c1 varchar2(3932 CHAR) ENCRYPT);


    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?


    Table redefinition:(not tested)
    -----------------------------------------
    1.Will Create Interim table with column as 'TDE' Enabled.
    2. Apply online redefinition using 'START_REDEF_TABLE'.
    3. We need to copy all dependent objects like 'Index/Constraints/Triggers/Grants' etc.
    4. We need to Sync both the 'Base table' and Interim table.
    5. Finish redefinition


    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?

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by IP2013 View Post
    Hi I am using 11.2.0.3.0 version of oracle.

    Now we are supposed to apply column level TDE(Transparent data encryption) to some of our table in database. 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 8 hrs of downtime window during DB maintenance but looking at the size of the table, i suspect it might cross.)
    Since the extent of the needed maintenance window appear to be the major concern, please note that "On Line Table Redefinition" is Oracle's suggested solution to deal with it. While the process is in progress the original table will remain available therefore it doesn't matter how long the redefinition takes to completed.

    For details... http://www.oracle.com/technetwork/da...bes-130696.pdf ; take a particular look at "Encrypting Columns in Giga and Terabyte Tables" section.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2013
    Posts
    7
    Agreed, Redefinition is the safest and recommended way.
    As i mentioned in my initial post, when i was comparing different possible options at hand. i found redefinition is probably the slowest among all, due to transformation of whole data along with dependents like Indexes/constraints/triggers. So i was thinking to utilize the downtime effectively, means i wanted to go for a mixed approach. For some tables ~30GB, will go for copy column method(which will complete within downtime window), and for ~800GB will go for redefinition.

    Here i have one question, why direct ALTER is running slower than the COPY COLUMN method, please answer?

    Please let me know your thoughts?

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by IP2013 View Post
    Here i have one question, why direct ALTER is running slower than the COPY COLUMN method, please answer?
    I do not know... please trace both methods then tkprof the traces, check what is going on, where time is spent.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width