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!

Unused column concept

Discussion in 'SQL PL/SQL' started by jagadekara, May 21, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    Some where i saw
    I want to know about this concept. Could you please aware me about that concept.
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Jagadekara,

    It's like a "logical drop" of the a column and the actual drop may be very heavy.

    You can get more details on this site:



    Regards,
    Rajen.
     
  3. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks for reply rajen,

    I didn't get you...
    I am not able to see any site in your post.
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
  5. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    See this

    docs.oracle.com/cd/B28359_01/server.111/b28310/tables006.htm#ADMIN11665
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Again you provide url which has http:// two times.... Ok no problem. i got it now...

    So if we logically drop that column by this way, can we get back again?

    and which is better drop or unused column?
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Q: So if we logically drop that column by this way, can we get back again?

    A: No,It is simple to return a column it is impossible.

    Note:
    However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed.
     
    jagadekara likes this.
  8. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Ok, If we don't want two columns from a table which have data.

    So at this time which is better? drop or unused column?
     
  9. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Jagadekara,

    In practice, it depends on the size of your table: it it's small then you can use any method (though UNUSED in instantaneous ... takes seconds I mean)
    However, with huge tables (size of several million records), UNUSED would be the choice: it will take a few seconds while DROP will take minutes (assuming there are no locks).

    Regards,
    Rajen.
     
    jagadekara likes this.
  10. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    It's not really a case of which is better. Setting a column to unused is always a precursor to dropping the column. The thought is that you set it to unused if the column should stop being available to queries immediately, but the table is large, is currently being accessed, and you cannot have the table locked for the amount of time it would take to DROP the column. Later, presumably during off hours, the column will be dropped from the table. Until the column is dropped, it will continue to use space in the database even though the data it contains is unavailable.
     
  11. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks All for your valuable info...