1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to drop a column fast?

Discussion in 'SQL PL/SQL' started by 13478, May 10, 2016.

  1. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hello,

    To make dropping a column fast in a huge table, what I do is: make the column unused first, then drop it.


    SQL> alter table HUGE set unused column PEOPLE_NAME;

    Table altered.

    Elapsed: 00:00:01.60
    SQL> alter table HUGE drop unused columns;

    Table altered.

    Elapsed: 02:08:25.18

    umm...still cost some time, do we have another way to make it fast?

    Thank you very much.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    This from doc :

    Code (SQL):
    ALTER TABLE HUGE DROP unused COLUMNS checkpoint 10000;

    Other solution is using of CTAS or dbms_redefinition .

    Table HUGE is partitioned, has restrictions, indexes?
     
    Last edited: May 10, 2016
  3. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hello,
    Yeah,
    it is partitioned.

    it is range partitioned by year, (by column FISC_YEAR).
    Sub-partitioned by list by month (by column FISC_MNTH)

    To be frank, I think using checkpoint may not speed up DDL (it is only to prevent to blow up undo tablespace)
    Am I right?
    (and, there is no constraints and index related to this column)

    Thank you so much













     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    1) If the table isn't used actively, then it is possible to use the simple scenario(for example):
    a) to create the table AA similar to the table BB
    b) to use dbms_parallel_execution for parallel translation of data from the table BB in the table AA
    c) rename table BB -> BB_old,rename table AA to BB
    d) to validate dependent objects

    2) if the table is used actively, then it is possible to use dbms_redefinition and parallel dml for redefinition of the table
     
  5. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Got your idea, let me weigh the options to find a practical solution.

    Thank you and have a good day.