7

There is a table in Oracle of huge size ~200GB and deleting a column in it via ALTER TABLE table DROP COLUMN column will take a lot of time. What are the ways to speed this up, or at least, if it is impossible to speed it up, how to do it correctly? Is it really only DROP COLUMN?

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

I read about SET UNUSED, but it seems like it's the same thing? Rather, it will not physically clear the table and you still need to use DROP UNUSED COLUMNS. I also found something about SHRINK SPACE, but I didn't understand how it works and how to use it. Should it be a combination together with SET UNUSED? Although at least it became clear that this could not be done in parallel. The only thing that can be achieved is not to lock the table during deletion

P.S. My question is more about the relevance of the data. I've read a lot of forums and found that all the answers and questions are very old. I would like to receive a fresh, accurate and comprehensive answer on how best to perform the removal

Improve this question

3 Answers 3

Reset to default
10

Dropping a column from a large table is rarely worth it. It has to recreate 100% of the data blocks, creating massive redo and single block read, and it's done serially, not taking advantage of parallel processing.

Instead, simply set the column unused ( alter table mytable set unused (mycol) ) and forget about it. It essentially disappears and is as good as gone, except that it is still using space (it still exists in the data blocks). But who cares? Most of the time that's just fine.

If you need the space savings right away, after setting the column unused simply physically reorg the table with an ALTER TABLE MOVE which can be done with parallelism:

alter table mytable move parallel (degree 16) update indexes

That will remove the unused column from the data blocks without having to do gymnastics to recreate the table. At this point the column is both logically removed and physically removed, but for some reason Oracle keeps its memory of it around in user_tab_cols. I honestly can't imagine for what purpose.

The only downside to having an unused column sitting out there is you have a hidden column (visible in user_tab_cols but not in user_tab_columns) that can cause an issue with partition exchanges, which require that the standalone table you are swapping with a partition have precisely the same structure in the dictionary - it'd need to have the same hidden/unused column in the same physical position, which can be a bit tricky to recreate. Fortunately Oracle has provided the create table... for exchange with table... syntax to automatically reproduce the precise structure needed, including those pesky unused columns. So, not really a problem. I'd just set it unused and call it a day.

If you do end up needing to get it out of there entirely, you'll need to perform the actual column drop (which after the reorg/move may be faster by bypassing redo, though it will still scan the whole table) or recreate the table. If it comes to that, at some convenient later time, maybe coinciding with other maintenance (compression, archiving, etc..), use the CTAS/table replace method already recommended by others to completely remove the column from the blocks. You can also use the PL/SQL package dbms_redefinition which does this for you, though I've never found a need to use it so don't know how it performs or what limitations it has.

But whatever method you use, the point is that it doesn't need to be done now, or perhaps ever.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

alter table move doesn't drop the column, so it still appears in user_tab_cols, but it does at least free up the space - fiddle.
@AlexPoole, thank you Alex, I just verified with a block dump that that's true. What in the world is the point of retaining the hidden column in user_tab_cols if it's permanently gone both logically and physically? Weird. I'll update my answer.
It does seem odd but it always seems to have been like this - Tom Kyte showed the space being freed in 2003, for instance; and you know how Oracle don't change things if they don't have to... It does seem silly when it's useless and just gets in the way. I don't know if drop unused is then quick - haven't tested that.
@PaulW what if a column need to be modified its datatype or its nullability? is their another way then online redefinitio ? I can post it as a question if you would like
@Baalback, some changes you simply do an alter table modify .... . Some changes can't be done this way or would take too long, in which case rebuilding the table (create a new table with CTAS and swap the names) is the typical procedure.
4

Either:

  1. As you did ALTER TABLE table_name DROP COLUMN column_name

  2. or

    CREATE TABLE table_name_new AS SELECT col1, col2, col3, col4 -- Excluding the column you want to drop FROM table_name

    Then: add indexes, constraints, etc. to the new table; drop the old table; and rename the new table to have the old table's name.

Improve this answer

2 Comments

It's going to be a very long time anyway, isn't it? We have a lot of indexes. Their creation will take almost the same amount of time. Testing is also necessary
@ulxanxv, no, recreating the table and all its indexes is much faster than dropping a column, especially when using parallelism. If I needed the space, MT0's answer is what I'd do without question. But still, see my answer for why you might consider not bothering at all.
0

You have also DBMS_REDEFINITION package for that kind of operations: https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_REDEFINITION.html#GUID-69422709-7A4E-4212-871D-C4C94F34820C If I remember correctly they was also an ATOH session speaking about it.

Improve this answer

2 Comments

Can you write a usage example?
@ulxanxv, dbms_redefinition simply implements the CTAS/replace method others have already recommended in a more convenient way (a single PL/SQL package call rather than a series of DDLs). The underlying mechanics are the same.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.