Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73

    Unanswered: set unused column v. drop column

    I have checked the docs and made some test and saw that the set unused works in zero time while drop column takes forever. The basic different is that drop column is reconstructing the table while set unused do not.

    My situation is that I do not care about old data.

    1. What will happen for new data rows, will this column will continue to consume space (assuming we are talking about NUMBER or VARCHAR2)?

    How can I check/test such a thing?


    Thanks in advanced,

    Tal Olier (otal@mercury.co.il).

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >How can I check/test such a thing?
    I'd use SQL*plus.

  3. #3
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73
    Originally posted by anacedent
    >How can I check/test such a thing?
    I'd use SQL*plus.
    check the space issue you dummy

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: set unused column v. drop column

    Originally posted by tal_olier

    1. What will happen for new data rows, will this column will continue to consume space (assuming we are talking about NUMBER or VARCHAR2)?

    How can I check/test such a thing?

    Hello tal_olier,

    When you execute ALTER TABLE <name> SET UNUSED COLUMN <col name> statement, Oracle deletes this column definition from the dictionary. Space allocated to this column is not freed up. It is freed up only when you execute ALTER TABLE <name> DROP UNUSED COLUMNS statement.

    So oracle is not having this column's definition and therefore it returns an error if any attempt made to insert value in this column.

    You can check it by creating temp table and performing these steps.
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  5. #5
    Join Date
    Aug 2002
    Location
    IL
    Posts
    73

    Re: set unused column v. drop column

    Originally posted by Hings
    Hello tal_olier,

    When you execute ALTER TABLE <name> SET UNUSED COLUMN <col name> statement, Oracle deletes this column definition from the dictionary. Space allocated to this column is not freed up. It is freed up only when you execute ALTER TABLE <name> DROP UNUSED COLUMNS statement.

    So oracle is not having this column's definition and therefore it returns an error if any attempt made to insert value in this column.

    You can check it by creating temp table and performing these steps.
    The question was regrading the storage consuming not the actual column that can not be used.

    Anyway got the word from Oracle support that it inserts null to "unused" columns and therefore takes 1 byte.

    will keep you updated.

  6. #6
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: set unused column v. drop column

    Originally posted by tal_olier
    The question was regrading the storage consuming not the actual column that can not be used.

    Anyway got the word from Oracle support that it inserts null to "unused" columns and therefore takes 1 byte.

    will keep you updated.
    I guess I could not understand your question, But anyhow, I also did not know that it occupies 1 Byte.

    thanks for keeping me updated
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    Set to unused THEN drop the column.
    How hard is that??

    Who cares if it takes two days?
    the table is not locked since the column is already set to unused.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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