Oracle Scratchpad

January 18, 2016

Drop Column

Filed under: Infrastructure,Oracle,Performance — Jonathan Lewis @ 8:14 am GMT Jan 18,2016

I published a note on AllthingsOracle a few days ago discussing the options for dropping a column from an existing table. In a little teaser to a future article I pointed out that dropping columns doesn’t reclaim space or, rather, probably doesn’t; and even if it did you probably wouldn’t like the way it did it.

I will  be writing about “massive deletes” for AllthingsOracle in the near future but I thought I’d expand on the comment about not reclaiming space straight away. The key point is this – when you drop a column you are probably dropping a small fraction of each row. (Obviously there are some extreme variants on the theme – for example you might have decided to move a large varchar2() to a separate table with a shared primary key).

If you’ve dropped a small fraction of each row you’ve freed up a small fraction of each block, which probably means the block hasn’t been identified as having free space available for inserts. In many cases this is probably  a good thing – because it’s quite likely that if every block in your table is suddenly labelled as having sufficient free space for new rows then you could end up with a difficult and ongoing performance problem.

Many large tables have a “time-based” component to their usage – i.e. as time passes the most recently entered rows are the ones that get most usage and older rows are no longer accessed; this means you get a performance benefit from caching because the “interesting” data is fairly well clustered and the most useful portions of such tables are often well cached .

In a case like this imagine what would happen if every block in your table suddenly acquired enough free space to accept a couple of new rows – over the next few days the incoming data will be spread across the length of the entire table and, for the next couple of months or years or decades, you will have to keep the entire table cached in memory if the performance is to stay constant; moreover the clustering_factor of the most useful indexes is likely to jump from “quite small” to “absolutely massive”, and the optimizer will start changing lots of plans because it will decide that your favourite indexes are probably much too expensive to user.

I am, of course, painting a very grim picture – but it is a possible scenario that should be considered before you drop a column from a table. Combined with my observations about the locking and overheads of dropping a column you might (probably ought to) decide that you should never drop a column you should only mark it as unused or (better still if you’re on 12c) mark it invisible for a while before marking it unused. You can worry about space reclamation at a later date when you have considered all the ramifications of how it might impact on performance.


If you’re still using freelist management then dropping a column won’t put a block on the freelist until the total used space in the block falls below the value dictated by pctused (default 40%); if you’re using ASSM then the block doesn’t become available for reuse until (by default) the free space exceeds 25% of the block’s usable space.




  1. Jonathan,
    so – I guess – in this scenario it could be a better idea to reorganize the table (online) with dbms_redefinition? Under the assumption that I want to reclaim space (and have the necessary resources to do the reorganization).

    Comment by Martin Preiss — January 18, 2016 @ 11:01 am GMT Jan 18,2016 | Reply

  2. […] the problem does not appear. It’s a great shame that in the past I’ve given advice that setting columns unused and dropping them later is a better option than doing an immediate […]

    Pingback by Drop Column bug | Oracle Scratchpad — January 14, 2020 @ 1:22 pm GMT Jan 14,2020 | Reply

  3. […] cleaning the table up, all the unused columns are set to null while the rows are copied so you get the space back – and it’s more likely to be usable and safe to use than it would be with the […]

    Pingback by Partitioning existing data | Oracle Scratchpad — May 29, 2021 @ 10:35 am BST May 29,2021 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: