{"id":73138,"date":"2016-01-14T13:48:46","date_gmt":"2016-01-14T13:48:46","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/dropping-columns\/"},"modified":"2025-06-27T14:51:08","modified_gmt":"2025-06-27T14:51:08","slug":"dropping-columns","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/dropping-columns\/","title":{"rendered":"Dropping Columns"},"content":{"rendered":"

I have my Oracle Support account configured to send me a daily email of \u201chot topics\u201d, and I\u2019ve set it up so that the email lists the 100 bugs that have been updated most recently by Oracle support. One of the bugs that came up on 9th Jan was described as: \u201cBug 18700681 : DROP COLUMNS USING ALTER TABLE VERY SLOW\u201d<\/strong><\/em>. My first thought when I saw this was that it wasn\u2019t a bug it was expected behaviour (with the caveat that \u201cvery slow\u201d might actually mean \u201ceven slower than the \u2018very slow\u2019 that I was expecting\u201d)<\/p>\n

I was a little curious so I logged on to MoS to see what the bug note said and found (to my complete lack of surprise) that the status was set to \u201c32 \u2013 Not a Bug. To Filer\u201d<\/em>; then I noticed that it did, nevertheless, raise a couple of interesting little issues.<\/p>\n

When you issue a \u201cdrop column\u201d<\/em> command Oracle has to read every block of the table, then rewrite it after removing the target column from every row; and the table in question was roughly 2TB in size (240,000,000 blocks at 8KB) with a few partitions \u2013 and the drop command had been running for 8 hours.<\/p>\n

    \n
  1. Is this an unreasonable amount of time?<\/li>\n
  2. If I tell you this was on an Exadata X3-2 does that change your answer to (1)?<\/li>\n
  3. Have you considered what might happen if the table is compressed with OLTP compression?<\/li>\n
  4. What about Hybrid Columnar Compression?<\/li>\n<\/ol>\n

    As a little extra aid to trouble-shooting it\u2019s worth noting that most of the waits reported from a 10046 trace were \u201ccell multiblock physical read\u201d and \u201cresmgr:cpu quantum\u201d \u2013 does that surprise you, does it give you a better idea of what\u2019s going on in this case?<\/p>\n

    I can\u2019t give you the answers to all the questions we could ask about this example, and I don\u2019t know whether that \u201c8 hours and running\u201d<\/em> was a natural consequence of how this table was constructed and how Resource Manager had been configured; but I will tell you something about dropping columns and how to choose and test your strategy if you ever need to do it on a production system.<\/p>\n

    Overview<\/h3>\n

    When I started to write this note I did a search on my blog for \u201cdrop column\u201d<\/em>, and found that I had a draft on the topic which I had started in 2007 and updated in 2012; and in that draft I referred back to two previous answers I had given on the topic, one to a posting on the OTN database forum from 2011<\/strong><\/em> and the other to a question on the Oracle-L list server in 2002<\/strong><\/em><\/a>. Both questions were about the volume of undo and redo generated by the drop column command. Conveniently my draft pointed me back to a script I had written (for Oracle 8i, to check something that I had written in my first book \u201cPractical Oracle 8i\u201d) to demonstrate the feature \u2013 and that script was still relevant to Oracle 12c.<\/p>\n

    The Options<\/h3>\n

    To \u201cremove\u201d a column from a table you can either mark it as \u201cunused\u201d, or you can drop it. Both these options are irreversible (short of recovering an older version of the database), but the former simply updates the data dictionary quickly and cheaply, the latter actually removes all the column data from the entire table as well. Technically, of course, you could achieve a similar effect to \u201cunused\u201d by setting the column to \u201cinvisible\u201d in the latest versions of Oracle \u2013 and this would also be a reversible change if you later realized it was a mistake.<\/p>\n

    Examples of syntax:<\/p>\n

    alter table t set unused column c;\nalter table t set unused (c);\nalter table t set unused (c1, c2);\n\nalter table t drop column c;\nalter table t drop (c);\nalter table t drop (c1, c2) checkpoint 2000;\n\nalter table t drop unused columns;\nalter table t drop unused columns checkpoint;\nalter table t drop unused columns checkpoint 5000;\nalter table t drop columns continue checkpoint;\nalter table t drop columns continue checkpoint 5000;\n<\/pre>\n
    For the syntax diagram and further details of options here\u2019s the precise URL<\/strong><\/em><\/a> from the 12.1.0.2 manual.<\/p>\n
    There are some restrictions on dropping or marking columns as unused \u2013 for example you can\u2019t drop a column that\u2019s part of the partition key of a partitioned table (for obvious reasons), similarly you can\u2019t drop a column that\u2019s part of the primary key of an index organized table, and there are other restrictions relating to constraints and virtual columns.<\/p>\n
    On the other hand there are cases where dropping a column is allowed to have large side effects \u2013 for example I can drop all the columns in the primary key of a table, at which point the primary key index will be dropped at the same time. Of course I have a little problem if there\u2019s a table with a foreign key referencing that primary key \u2013 but then I just have to remember that there\u2019s a \u201ccascade constraints\u201d option \u2013 consider this examples which starts with a table called parent<\/strong><\/em> with a primary key of (id)<\/strong><\/em> and a table called child<\/strong><\/em> with a primary key of (id_p, id)<\/strong><\/em> where child.id_p<\/strong><\/em> references parent.id<\/strong><\/em> as a foreign key:<\/p>\n
    SQL> alter table parent drop column id;\nalter table parent drop column id\n                               *\nERROR at line 1:\nORA-12992: cannot drop parent key column\n\n\nSQL> alter table parent drop column id cascade constraints;\n\nTable altered.\n\nSQL> alter table child drop (id_p);\nalter table child drop (id_p)\n                        *\nERROR at line 1:\nORA-12991: column is referenced in a multi-column constraint\n\n\nSQL> alter table child drop (id_p, id);\n\nTable altered.\n<\/pre>\n
    I couldn\u2019t drop the parent primary key until I cascaded the constaint then, as Oracle cascades the drop on the parent the parent index and the child foreign key constraint disappear. I then had a problem dropping a single column from the child table because it was part of the child\u2019s primary key, but when I dropped both primary key columns at once I had no problem.<\/p>\n
    The Costs<\/h3>\n
    When you drop a column, Oracle reads every row of every block in the table, and rewrites that row after removing the dropped column from it. If you drop multiple columns at the same time Oracle treats each column in the row as a separate update, and each change generates undo and redo. Note that if you mark a column as unused there is no such work \u2013 but if you subsequently drop a second column Oracle silently does the work for both columns.<\/p>\n
    The table is locked exclusively while the drop is executing and the entire drop is executed as a single massive transaction unless you include a \u201ccheckpoint\u201d<\/strong><\/em> clause. In the syntax examples above we saw two versions of the checkpoint clause, one with and one without a number \u2013 if you omit the number the default value is 512 which tells Oracle to commit after every 512 rows modified, with the unusual feature that the commit does NOT release the table lock.<\/p>\n
    The question on OTN all those years ago was this:<\/p>\n
    In my 10.2.0.3 I\u2019ve got table size 90 GB which suffers from :
    \nORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE
    \nI need to know how to estimate redo size of the:
    \nalter table DROP COLUMNS CONTINUE checkpoint 10000 .
    \nIs 2x table size good estimate?<\/em><\/p>\n
    And my reply was this:<\/p>\n
    \u201cAs a very rough guide for simple columns, the rollback generated for each row will be about 100 bytes plus the average column size being dropped, the redo will be around 250 bytes plus the average column size \u2013 even when the table is a NOLOGGING table.\u201d
    \n<\/em><\/p>\n
    This introduces a new idea \u2013 the \u201ccontinue\u201d<\/strong><\/em> option. If you\u2019ve used the checkpoint syntax with your drop command then you could kill the session (or shutdown the database, perhaps) halfway through the drop. The table will not be available for DML or query<\/strong><\/span> , but it is in a safe state and you can finish off the \u201cdrop columns\u201d command using the \u201ccontinue\u201d (with an optional checkpoint) option. (Strangely there doesn\u2019t seem to be a \u201csuspend drop\u201d command, so it looks like the only reason for using the \u201ccontinue\u201d option would be to handle a crash of some sort \u2013 whether accidental or deliberate.)<\/p>\n
    I gave some figures in my OTN answer, but what I failed to say at the time was that they represented the approximate workload if you are dropping just ONE column. How did I get the figures, and what happens if there are more columns? Let\u2019s just do the experiment \u2013 first creating a simple test table:<\/p>\n
    create table t1\nas\nwith generator as (\n        select  --+ materialize\n                rownum id\n        from dual\n        connect by\n                level <= 1e4\n)\nselect\n        rownum                  id,\n        'XX'                    v2,\n        lpad('x',30)            v30,\n        rpad('x',100)           padding\nfrom\n        generator       v1,\n        generator       v2\nwhere\n        rownum <= 1e6\n;\n<\/pre>\n
    I\u2019m going to be using 12.1.0.2 for the demonstration so I don\u2019t need to gather stats at this point. Here\u2019s the basic code I\u2019m going to use to do some testing:<\/p>\n
    alter table t1 set unused column v2;\nalter table t1 set unused column v30;\n\nexecute snap_enqueues.start_snap\nexecute snap_rollstats.start_snap\nexecute snap_my_stats.start_snap\n\nalter table t1\ndrop unused columns\ncheckpoint 1000\n;\n\nexecute snap_my_stats.end_snap\nexecute snap_rollstats.end_snap\nexecute snap_enqueues.start_snap\n<\/pre>\n
    The calls to the \u201csnap\u201d packages are simple procedures I use to take snapshot of some of the dynamic performance views and report the differences resulting from the test. In this case I\u2019m choosing to look at v$enqueue_stat<\/strong><\/em> (enqueue\/lock statistics), v$rollstat<\/strong><\/em> (basic rollback\/undo segment statistics), and a join between v$statname<\/strong><\/em> and v$mystat<\/strong><\/em> (my session statistics). I won\u2019t report all the statistics, I\u2019ll just tell you a little about some of the figures. The code allows for several variations \u2013 with or without the checkpoint 1000<\/strong><\/em>; marking just v2<\/strong><\/em> unused, marking just v30<\/strong><\/em> unused, marking both unused.<\/p>\n
    The really significant results of the test are the following (from the session stats):<\/p>\n
    12.1.0.2 - dropping v2 with no checkpoint\n=========================================\nName                                       Value\n----                                       -----\ndb block changes                       2,010,425\nredo entries                           1,000,231\nredo size                            236,735,648\nundo change vector size               79,845,720\ntable scan rows gotten                 1,008,525\nHSC Heap Segment Block Changes         1,000,011\n\n12.1.0.2 - dropping v30 with no checkpoint\n==========================================\nName                                       Value\n----                                       -----\ndb block changes                       2,034,190\nredo entries                           1,020,470\nredo size                            266,507,104\nundo change vector size              107,840,960\ntable scan rows gotten                 1,007,385\nHSC Heap Segment Block Changes         1,000,008\n\n\n12.1.0.2 - dropping both with no checkpoint\n===========================================\nName                                       Value\n----                                       -----\ndb block changes                       4,048,432\nredo entries                           2,023,290\nredo size                            503,565,712\nundo change vector size              187,929,544\ntable scan rows gotten                 1,008,297\nHSC Heap Segment Block Changes         2,000,011\n\n<\/pre>\n
    The most obvious point we can see is that the block changes<\/strong><\/em>, undo<\/strong><\/em> and redo<\/strong><\/em> for dropping the two columns at the same time is the sum of the individual drops; and this isn\u2019t because we do two table scans (one for each column); we do one table scan of 1M rows, but we handle the changes for the first column then handle the changes for the second column separately \u2013 so dropping two columns gives us two sets of undo and redo overhead per row.<\/p>\n
    The second point we can pick up is the size (approximately) of the overhead per row per column. The actual data sizes for the columns in the two individual tests are 2 bytes and 30 bytes respectively, and with 1 million rows affected we see that the undo change vector size changes from 79.8MiB to 107.8MiB, that\u2019s a change of 28MiB \u2013 i.e. 1 million x 28 bytes. So the undo overhead is (approximately) 77,800,000 which means about 78 bytes per column per row (which may vary slightly with version of Oracle).<\/p>\n
    Similarly the figures for the redo size are 236.7Mib, 266.5 MiB, 503.6Mib: the increment from v2<\/strong><\/em> to v30<\/strong><\/em> is about 30 bytes per row, so if we subtract a couple of extra bytes per row from the figures for v2<\/strong><\/em> we can infer that the redo overhead is about 234 bytes per column per row \u2013 so drop 4 columns from a table and your redo is going to be in the order of 1KB per row! The \u201cbug\u201d that kicked this article off told us about the number of blocks in the table that would have to be read and re-written \u2013 but it may be the number of rows and dropped columns that is even more important.<\/p>\n
    The figures don\u2019t change very much for the test with the checkpoint \u2013 the most important change is that instead of one undo segment getting all the undo writes, Oracle rotates round the available undo segments, switching segment after every 1,000 rows of data, so we don\u2019t see one segments stealing all the extents from the rest of the segments and don\u2019t run the risk of the process crashing due to a full undo tablespace. It\u2019s possible, of course, that this checkpoint strategy could result in other long-running sessions getting an ORA-01555 error, \u201csnapshot too old\u201d if the drop manages to overwrite the undo that other sessions need.<\/p>\n
    With the checkpoint in place the enqueues snapshot shows us roughly 1,000 TX enqueues taken and released \u2013 but we have to set event 10704 to see that the TM enqueue on the underlying table is held in mode 6 (exclusive) for the duration.<\/p>\n
    Miscellaneous<\/h3>\n
    You can\u2019t drop a column in a table that you\u2019ve created with basic compression \u2013 but you can if the table is \u201ccompress for OLTP\u201d (or \u201crow store compress advanced\u201d as the newer syntax puts it). Interestingly when I tested this by dropping column v2 in a compressed version of my demonstration table the redo size was about 2KB \u2013 clearly Oracle doesn\u2019t really drop the column, it\u2019s just a data dictionary adjustment. Perhaps the same is true for Hybrid Columnar Compression on Exadata \u2013 it seems reasonable to assume that it would be.<\/p>\n
    This leads on to a new topic \u2013 and one which I will be writing about in the near future. The manuals make the following comments about dropping columns:<\/p>\n
      \n
    • 11g manual: \u201cThis provides a convenient means to free space in a database \u2026\u201d<\/li>\n
    • 12c manual: \u201c\u2026 lets you free space in the database by dropping columns ..\u201d<\/li>\n<\/ul>\n
      Neither manual is correct (particularly with respect to the word \u201cconvenient\u201d). When you drop columns from a table you don\u2019t restore space to \u201cthe database\u201d you may not even be able to re-use the space for new rows in the same table if the column you\u2019ve dropped was quite small.<\/p>\n
      If you want to reclaim space in the tablespace, or even use the space that has become available in the table safely you\u2019re going to have to take further steps after dropping the column(s). This may mean you decide to stick with marking columns unused until you have an opportunity to rebuild the table properly.<\/p>\n
      Next time:<\/strong> I\u2019ll be looking at this problem and the related problem of \u201cmassive deletes\u201d.<\/em><\/p>\n
      Footnote:<\/strong> My series on the Cost Based Optimizer will be continued in the near future.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"
      I have my Oracle Support account configured to send me a daily email of \u201chot topics\u201d, and I\u2019ve set it up so that the email lists the 100 bugs that have been updated most recently by Oracle support. One of the bugs that came up on 9th Jan was described as: \u201cBug 18700681 : DROP COLUMNS USING ALTER TABLE VERY…<\/p>\n","protected":false},"author":101205,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48402],"coauthors":[39048],"class_list":["post-73138","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-drop-column"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73138","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/101205"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73138"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73138\/revisions"}],"predecessor-version":[{"id":107309,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73138\/revisions\/107309"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73138"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73138"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73138"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73138"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}