When Does A ROWID Change? Part III (“Arriving Somewhere But Not Here”)

In Part II of this series, I discussed how updating the Partitioned Key of a row from a Partitioned table will result in the row physically moving and the associated ROWID changing.

One of the reasons why changing the ROWID has historically has not been the default behaviour and requires the explicit setting of the ENABLE ROW MOVEMENT clause for Partitioned tables is because changing a ROWID comes at a cost. The cost being not only having to delete and re-insert the row within the table, but also delete and re-insert the associated index entry for each corresponding index on the table.

To illustrate, I’m going to create and populate another simple little Partitioned Table:

SQL> CREATE TABLE big_bowie2(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, release_date date, name varchar2(42)) PARTITION BY RANGE (release_date)
(PARTITION ALBUMS_2021 VALUES LESS THAN (TO_DATE('01-JAN-2022', 'DD-MON-YYYY')),
PARTITION ALBUMS_2022 VALUES LESS THAN (MAXVALUE)) ENABLE ROW MOVEMENT;

Table created.

SQL> INSERT INTO big_bowie2 SELECT rownum, mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,100
ownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), mod(rownum,1000), sysdate-
m,500), 'DAVID BOWIE' FROM dual CONNECT BY LEVEL <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=> null, tabname=> 'BIG_BOWIE2');

PL/SQL procedure successfully completed.

I’m now going to create a number of basic Global indexes on this table:

SQL> create index big_bowie2_id_i on big_bowie2(id);

Index created.

SQL> create index big_bowie2_code1_i on big_bowie2(code1);

Index created.

SQL> create index big_bowie2_code2_i on big_bowie2(code2);

Index created.

SQL> create index big_bowie2_code3_i on big_bowie2(code3);

Index created.

SQL> create index big_bowie2_code4_i on big_bowie2(code4);

Index created.

SQL> create index big_bowie2_code5_i on big_bowie2(code5);

Index created.

SQL> create index big_bowie2_code6_i on big_bowie2(code6);

Index created.

SQL> create index big_bowie2_code7_i on big_bowie2(code7);

Index created.

SQL> create index big_bowie2_code8_i on big_bowie2(code8);

Index created.

SQL> create index big_bowie2_code9_i on big_bowie2(code9);

Index created.

SQL> create index big_bowie2_code10_i on big_bowie2(code10);

Index created.

If I run a simple single row UPDATE that updates a non-indexed, non-partitioned key column:

SQL> update big_bowie2 set name='ZIGGY STARDUST' where id=424;

1 row updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923

-------------------------------------------------------------------------------------
| Id | Operation          | Name            | Rows | Bytes | Cost (%CPU) | Time     |
-------------------------------------------------------------------------------------
|  0 | UPDATE STATEMENT   |                 |    1 |    16 |       2 (0) | 00:00:01 |
|  1 |  UPDATE            | BIG_BOWIE2      |      |       |             |          |
|* 2 |   INDEX RANGE SCAN | BIG_BOWIE2_ID_I |    1 |    16 |       1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=424)

Statistics
----------------------------------------------------------
          1 recursive calls
          1 db block gets
          2 consistent gets
          0 physical reads
        328 redo size
        204 bytes sent via SQL*Net to client
         41 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

We can see that the number of db block gets is just 1 and consistent gets just 2, as only the one table block needs to be updated and easily accessed via the index on the ID column.

If we now run a single row update of an indexed column:

SQL> update big_bowie2 set code1=42 where id = 424;

1 row updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923

-------------------------------------------------------------------------------------
| Id | Operation          | Name            | Rows | Bytes | Cost (%CPU) | Time     |
-------------------------------------------------------------------------------------
|  0 | UPDATE STATEMENT   |                 |    1 |     8 |       2 (0) | 00:00:01 |
|  1 |  UPDATE            | BIG_BOWIE2      |      |       |             |          |
|* 2 |   INDEX RANGE SCAN | BIG_BOWIE2_ID_I |    1 |     8 |       1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=424)

Statistics
----------------------------------------------------------
          1 recursive calls
          5 db block gets
          2 consistent gets
          1 physical reads
        948 redo size
        204 bytes sent via SQL*Net to client
         41 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed

We can see that the number of db block gets increases to 5, as not only does the table block have to be updated but so also do the associated index blocks.

If we now finally run a single row update on the partitioned table’s partition key column that results in the row having to physically move to another partition:

SQL> update big_bowie2 set release_date='06-DEC-22' where id = 424;

1 row updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 3590621923

-------------------------------------------------------------------------------------
| Id | Operation          | Name            | Rows | Bytes | Cost (%CPU) | Time     |
-------------------------------------------------------------------------------------
|  0 | UPDATE STATEMENT   |                 |    1 |    64 |       2 (0) | 00:00:01 |
|  1 |  UPDATE            | BIG_BOWIE2      |      |       |             |          |
|* 2 |   INDEX RANGE SCAN | BIG_BOWIE2_ID_I |    1 |    64 |       1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=424)

Statistics
----------------------------------------------------------
          1 recursive calls
         49 db block gets
          3 consistent gets
          0 physical reads
       5996 redo size
        204 bytes sent via SQL*Net to client
         41 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
         11 sorts (memory)
          0 sorts (disk)
          1 rows processed

We see that the number of db block gets jumps significantly to 49, as all the corresponding indexes require their associated index entries to be likewise deleted/re-inserted in order to change all their ROWIDs.

So this additional cost of updating the indexes has been a cost that Oracle has traditionally attempted to avoid, by generally not changing the ROWID when performing an update of a row.

Of course, the update of a Partitioned Key column is not the only manner in which ROWIDs have previously easily changed as we’ll see in Part IV…

UPDATE: As my buddie Martin Widlake makes in this comment. it’s also well worth mentioning the increase in associated redo (as redo is an excellent measurement of “work” the Oracle Database has to perform), if Oracle has to change the ROWID of a row and make the necessary changes to all its corresponding indexes. In the example above, the redo increases significantly from 328 bytes to 5996 bytes, when Oracle has to move the row to another partition and so update the ROWID on the 11 indexes. More on all this when I discuss the changes implemented with the current Autonomous Databases…