Oracle Scratchpad

November 8, 2013

Row Movement

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 1:26 pm GMT Nov 8,2013

Here’s a question that appeared recently on OTN, and it’s one I’ve wondered about a few times but never spent any time investigating. Are there any overheads to enabling row movement on a table? If not, why is it not enabled by default and eliminated as an option?

Obviously there are costs when a row moves – it will be updated, deleted and re-inserted with all relevant index entries adjusted accordingly – but is there an inherent overhead even if you do nothing that moves a single row?

Equally obviously you’ve made it possible for someone to “accidentally” shrink the table, cause short term locking problems, and longer term performance problems; similarly it becomes possible to update rows in partitioned tables in a way that causes them to move; but “someone might do it wrong” doesn’t really work as an argument for “de-featurising” something that need not have been a feature in the first place.

What have I missed?

Answers in the comments gratefully received – and possibly discussed.

Update (Oct 2016)

At the Trivadis Performance Days Roger MacNicol (of the blog Smart-Scan Deep Dive) made the comment that when you enable row movement there is no guarantee that Oracle won’t do something you are not expecting. The only thing the manual says is that if your application depends on rowids then you should not enable row movement.

Consider, for example, that an update to a row in an Exadata compression unit currently (2016) flags the row and migrates it to another block but leaves any indexes on the row pointing to the original rowid. There is no guarantee that Oracle won’t, one day, change the code to delete the whole row, re-insert it and update the indexes if you’ve enabled row movement.

A carefree attitude to row movement in the current version of Oracle might lead to horrible surprises on the next upgrade.



  1. Jonathan,

    I think I have quite simple explanation. Unfortunately, there is plenty of old application code which tried to be clever (and save few LIO) in the past.

    1) fetch row from table t1 and place it’s ROWID to PL/SQL variable (instead of PK)
    2) perform some application code
    3) update same row in table 1, of course partitioned key included causing row movement and ROWID change
    4) perform some application code
    5) try to select same row in table t1 by USER ROWID ACCESS, which fails due 3) with NO DATA FOUND

    As far I can say it happened several times after some table was re-partitioned (since developers realized it is too big). After installation of application patch which changed table to partitioned, some code was found which needed to be fixed.

    If Oracle had introduced enabling row movement by default in 12c, it would have caused issues to many application procedures, I think.

    On the other hand, be everybody should be carefull when enabling row movement in production system since enabling row movement invalidates all dependent views, which could result into plenty invalidate objects, discarded package states etc.

    Pavol Babel

    Comment by Pavol Babel — November 8, 2013 @ 2:20 pm GMT Nov 8,2013 | Reply

    • True, the dead hand of bad code – explains a number of oddities in Oracle.
      That one even makes it hard to argue for a system level parameter.

      Update: SQL*Forms used to do that, of course; then again, Oracle didn’t have primary keys in those days ;)

      Comment by Jonathan Lewis — November 8, 2013 @ 2:41 pm GMT Nov 8,2013 | Reply

      • Exactly, Oracle Forms. And we are still using it for one customer (huge application and customer does not have enough money to build new frontend), Forms plugin on WebLogic 10.3.6. Plenty of messy code in our Forms still (and I think Oracle itself has plenty of that, too). Our developers started using this “technique” because they though “if Oracle is must be very efficient, let’s do it same way”


        Comment by Pavol Babel — November 8, 2013 @ 2:55 pm GMT Nov 8,2013 | Reply

        • * … if oracle is using ROWIDS instead of PK, it must be very efficient, let’s do it same way

          Comment by Pavol Babel — November 9, 2013 @ 5:09 pm GMT Nov 9,2013

        • As usual, you shall know what are you doing. Accessing by rowid is quite efficient and fast. But using rowid and row movement? Sounds more like misunderstanding.
          It’s interesting from where is coming the misunderstanding… from the app which is using rowid or from the dba/dev which is using row movement whit this app?

          Comment by Nikolay Kovachev — November 9, 2013 @ 7:02 pm GMT Nov 9,2013

  2. Jonathan,

    not a problem of overhead but I remember a few situations with merge operations resulting in an error “ORA-08006: specified row no longer exists” (as described in Todor Botev’s blog: – so maybe there are still some minor internal problems related to the option.


    Comment by Martin Preiss — November 8, 2013 @ 2:24 pm GMT Nov 8,2013 | Reply

    • Martin,

      Funnily enough I posted a note on OTN earlier on today relating to the non-partitioned versions of Pavol’s example. Eliminate all references to partitions and you get Oracle error: “ORA-30926: unable to get a stable set of rows in the source tables” – you’re not supposed to supply duplicates to the merge command (it’s a bit like the problem of key-preserved tables for updateable views).

      Comment by Jonathan Lewis — November 8, 2013 @ 2:46 pm GMT Nov 8,2013 | Reply

      • It seems actually that the merge can succeed even supplying duplicates as soon as the ordering in the USING select is in such way that the values that come first for the updated columns, are the same as the current ones, so that each row is updated only once. Correct ?

        Comment by Attanasio — November 10, 2013 @ 4:37 pm GMT Nov 10,2013 | Reply

  3. This is a good question and one that I’ve wondered several times, particularly when introducing partitioning on an updateable column and thinking there must be some significant downside somewhere that means you have to explicitly enable it.

    Could it be as simple as it (the feature) came relatively late in the day and there’s just not been the demand for it to be enabled by default?

    Comment by Dom Brooks — November 8, 2013 @ 3:15 pm GMT Nov 8,2013 | Reply

    • Dom,

      Quite possibly. Backwards compatibility / inertia / unwillingness to test every possible side effect plays an important role in any change.

      Comment by Jonathan Lewis — November 11, 2013 @ 2:54 pm GMT Nov 11,2013 | Reply

  4. I have long suspected the use case for “enable row movement” was as a remedy for inappropriate coding techniques.

    Changing partition keys is the most common manifestation I see, typically someone choosing to list partition on STATUS. I suppose from the developers point of view it that “it just happens, no coding needed”. It comes unstuck when we have a busy transactional system and many status changes, rows are moving all over the place and this takes, IO, redo and lengthens transaction time. Sad really, as there are many other techniques we could use to meet our goal (which I imagine is going to be about throughput, rather than LCM), maybe developers are scared of indexes, tables, joins etc :)

    The other group of row movement issues I encounter are related to the need to use shrink to resolve table bloat (I am speaking in a DW context where full table scans are often welcome) – this may come from poor choices on table storage options or the inappropriate use of parallel append; again poor design or development choices. Although shrinking tables has less impact then moving them I still feel the amount of time locks are held (even with a two pass shrink process) is significant on a large table

    Intuitively , I feel row movement is bad for transactional systems and not that good for data warehouse either

    Comment by Peter Scott — November 8, 2013 @ 3:42 pm GMT Nov 8,2013 | Reply

  5. Peter,
    I’m actually quite fond of the “list partition” strategy for the status change. Obviously it has the threat that you have to update a row, delete a row, insert a row (and modify ALL the indexes twice) when you change the partition key and that can be such a threat that I pick an alternative strategy, but the benefit of having a small segment for the “active” data often has attractions that outweigh the costs of the extra redo.

    I’m not a fan of the shrink command – I’d prefer to use dbms_redefinition. To me the problem of the shrink command is the threat inherent in the potentially dramatic change of the order and distribution of data.

    Comment by Jonathan Lewis — November 8, 2013 @ 9:02 pm GMT Nov 8,2013 | Reply

    • indeed…dbms_redef became a very cool tool when they added the ‘order by’ option

      Comment by connormcdonald — November 9, 2013 @ 2:48 am GMT Nov 9,2013 | Reply

    • Jonathan,

      could you please explain which alternatives to the “list partition strategy for the status change” would you recommend in case the overhead of row movement is unacceptable? Maybe using a function based index (or a virtual column with an index on it) to selectively index only the “active” subset of data?

      Jure Bratina

      Comment by Jure Bratina — November 9, 2013 @ 7:58 am GMT Nov 9,2013 | Reply

      • you can use functional index whit decode:
        create index active_idx on big_table(decode(status,’A’,’A’);

        And then in your where clause:
        where decode(status,’A’,’A’)=’A’;

        In this case you’ll have a small index which will contain your “active” data only.

        Comment by Nikolay Kovachev — November 9, 2013 @ 12:04 pm GMT Nov 9,2013 | Reply

      • Jure,

        The function-based index is one of my preferred methods – even to the extent of creating a covering index as a function-based index to include every relevant column in an index that identifies only the required rows. (This means every column in the index uses the same CASE statement, as does every column in the query – I prefer case to decode() – and potentially creating a separate index for every different status).

        The code has to change, of course – and if you can do that then changing the application to set the final status to NULL may be a sufficient change anyway (if there is only one extreme final state).

        Comment by Jonathan Lewis — November 11, 2013 @ 2:53 pm GMT Nov 11,2013 | Reply

        • To avoid having to do the CASE/decode trick on all subsequent columns in the covering index, what about globally partitioning the index on the status column and leaving unusable the parts of the index that correspond to values you don’t care about?

          Having a non-null default for the status CASE would likely help… I’ve sometimes looked at how to force null leading columns into their own partition and IIRC it’s not always been pretty.

          Comment by Jason Bucata — November 12, 2013 @ 2:27 am GMT Nov 12,2013

        • Regarding your idea of having a different index for each status value, I’m really starting to like that approach! It basically gives you a quasi-histogram where the CBO will have different stats available (e.g. clustering factor) for each value.

          I first thought of the downside, of not getting leaf block recycling, which depending on your access pattern might become a problem. But I’ve wished for per-value clustering factors enough before that I’d likely be willing to take that tradeoff.

          Comment by Jason Bucata — November 12, 2013 @ 2:30 am GMT Nov 12,2013

        • Jonathan,

          thank you for the explanation.

          Could you please also explain if there are any advantages/drawbacks of having separate indexes (or indexed virtual columns) for every different status versus having one index with multiple CASE statements? As Jason Bucata already mentioned, having multiple indexes probably gives the CBO more granular statistics for every single status and the SQL code is also probably neater since you don’t have to list the whole CASE statement if you’re interested in only one specific value.

          I hope the next question isn’t too much off-topic, if it is please ignore it.
          Since you mentioned function based indexes with CASE statements, I was wondering if there’s any difference for the CBO if an index (or virtual column) is declared with syntactically different CASE statements (possibly even using a NOT IN operator) which return the same result? As far as I tested it seems there are no differences, since the estimates the CBO makes for equality predicates are exactly the same in all cases, probably because the different CASE statements all return exactly the same output and the statistics describing that data should probably also be exactly the same.

          Maybe it’s better if I make a short example. In your first article on histograms you defined a virtual column like this:

          alter table orders
          add (
          	interesting_status    generated always as (
          		case status
          			 when 'C' then null
          			 when 'X' then null
          					else status
          	) virtual

          I was wondering if two definitions below are equivalent as far as it concerns the CBO:

          alter table orders
          add (
              interesting_status    generated always as (
                  case when 
                       status not in ('C','X') then status 
                              else null  
              ) virtual
          alter table orders
          add (
              interesting_status    generated always as (
                  case when 
                       status in ('P','R','S') then status 
                              else null  
              ) virtual

          Jure Bratina

          Comment by Jure Bratina — November 12, 2013 @ 12:57 pm GMT Nov 12,2013

    • Well, actually you can access you “active” data in many different ways. Better ways than moving your rows around. Also I think that the row movement is not enabled by default, because your rowid will change and oracle behavior shall not be like this, no?

      Comment by Nikolay Kovachev — November 9, 2013 @ 11:57 am GMT Nov 9,2013 | Reply

  6. Jonathan,
    can you tell us what are the advantages of using dbms_redefinition instead of using shrink? Do you ever use shrink command?

    Comment by sarayu — November 10, 2013 @ 10:54 am GMT Nov 10,2013 | Reply

    • Some data has a strong time-dependency – i.e. rows that people query together were entered at around the same time. If you shrink a table this moves rows from the end of the table to the beginning of the table in reverse order. Unless you have a case where the beginning (or at least a large consecutive set of blocks) of the table is virtually empty then you risk spread time-dependent rows over a larger number of blocks, resulting in less efficient queries and a greater need to cache table blocks. You may also find that this physical change is reflected in an increase in the clustering_factor of critical indexes, which could result in the optimizer ignoring your preferred index.

      dbms_redefinition will typically rebuild the table in the same order – though there is a problem there with wasting space because of the problem of handling the pctfree parameter for the new table, (Shrinking can also waste space for a similar reason, but the problem is a little more subtle and the circumstances would be different.)

      Comment by Jonathan Lewis — November 11, 2013 @ 2:49 pm GMT Nov 11,2013 | Reply

  7. I have come across one case where row movement incurs an overhead; it prevents CBO from taking certain shortcuts. This was back in 9i, and apparently CBO was not aware that it could not take the shortcut, which is how I found this.
    My case was exactly of the “list partition by status” ones. It had the twist that the status update was done recursively, something like:

    update tbl
       set status='C'
     where rowid in (
        select rowid
          from tbl
         start with id=:id
       connect by parent_id=prior id
         where status='S');

    This had worked fine for years, with an execution plan … nested loop … connect by pump … until one day it failed with “ORA-01410: invalid ROWID”. It turned out that the execution plan had changed to something with full scan over the active partition and hash joins. I never reached a full explanation, but my guess is that CBO created an execution plan that somehow tried to use the rowid to access a row that had already been updated, and thus had gotten a new rowid in the meantime.
    (By the way, the problem was solved by gathering new stats that brought the execution plan back to the old one.)

    Comment by Karsten Spang — December 11, 2013 @ 5:01 pm GMT Dec 11,2013 | 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 )

Google photo

You are commenting using your Google 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.

Powered by