3 Replies Latest reply: Feb 23, 2010 4:37 AM by 755392 RSS

    Drop column in a big table.

    755392
      Hi all,

      I need to drop a column in a table (Oracle Version is 9iR2). This table contains approximately 2 000 000 000 records, each record is about 41 bytes length.

      The column I need to drop represents un varchar(10), i.e. about 25% of a line size.

      Because this operation is going to be very long, I would like to ask for the best way to proceed :
      - should I previously set all values to null before the drop ?
      - should I drop indexes on the table (there are 5 bitmap indexes but none on the column to drop).
      - should I set some specific values to TEMP_TBS or UNDO_TBS before I proceed ?
      - are there any other tips to know to achieve this goal ?

      Thanks to all in advance.

      Gael
        • 1. Re: Drop column in a big table.
          riedelme
          user1368644 wrote:
          Hi all,

          I need to drop a column in a table (Oracle Version is 9iR2). This table contains approximately 2 000 000 000 records, each record is about 41 bytes length.

          The column I need to drop represents un varchar(10), i.e. about 25% of a line size.

          Because this operation is going to be very long, I would like to ask for the best way to proceed :
          - should I previously set all values to null before the drop ?
          I can't see any reason to do that
          - should I drop indexes on the table (there are 5 bitmap indexes but none on the column to drop).
          this should not be necessary if the indexes do not contain the column to be dropped
          - should I set some specific values to TEMP_TBS or UNDO_TBS before I proceed ?
          Not sure.
          - are there any other tips to know to achieve this goal ?
          There are actually a couple of ways do do this. You can drop the column in place, or cheat by copying the old table to a new one withouth the column. The sheer size of your table probably makes the second method impractical, not to mention having to recreate indexes, triggers, etc.
          • 2. Re: Drop column in a big table.
            659537
            Hope this will help you...

            http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:623063677753
            • 3. Re: Drop column in a big table.
              755392
              Thank you for the link.
              It is exactly what i was looking for.

              Best regards.