Some Things To Consider Now ROWIDs Are Updated When Rows Migrate Part II (“Look Back In Anger”)

Some weekend reading…

In my previous post, I discussed a couple of potential areas of concern with the ROWIDs of migrated rows now being updated on the fly in Oracle Autonomous Databases, namely that it can cause issues with applications that reply on stored ROWIDs not changing and that there are additional resources required to maintain such ROWIDs in corresponding indexes, especially if there are many indexes on a table.

In this post, I’ll discuss another issue to just bear in mind with this change in behaviour.

To illustrate, I’ll run a demo similar to the previous post, first creating and populating a table with no ENABLE ROW MOVEMENT set:

SQL> create table ziggy(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(142)) PCTFREE 0;

Table ZIGGY created.

SQL> insert into ziggy SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 200000;

200,000 rows inserted.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

SQL> analyze table ziggy compute statistics;

Table ZIGGY analyzed.

I’ll next create a bunch of indexes on the table:

SQL> create index ziggy_id_i on ziggy(id);

Index ZIGGY_ID_I created.

SQL> create index ziggy_code1_i on ziggy(code1);

Index ZIGGY_CODE1_I created.

SQL> create index ziggy_code2_i on ziggy(code2);

Index ZIGGY_CODE2_I created.

SQL> create index ziggy_code3_i on ziggy(code3);

Index ZIGGY_CODE3_I created.

SQL> create index ziggy_code4_i on ziggy(code4);

Index ZIGGY_CODE4_I created.

SQL> create index ziggy_code5_i on ziggy(code5);

Index ZIGGY_CODE5_I created.

SQL> create index ziggy_code6_i on ziggy(code6);

Index ZIGGY_CODE6_I created.

SQL> create index ziggy_code7_i on ziggy(code7);

Index ZIGGY_CODE7_I created.

SQL> create index ziggy_code8_i on ziggy(code8);

Index ZIGGY_CODE8_I created.

SQL> create index ziggy_code9_i on ziggy(code9);

Index ZIGGY_CODE9_I created.

SQL> create index ziggy_code10_i on ziggy(code10);

Index ZIGGY_CODE10_I created.

SQL> create index ziggy_code11_i on ziggy(code11);

Index ZIGGY_CODE11_I created.

SQL> create index ziggy_code12_i on ziggy(code12);

Index ZIGGY_CODE12_I created.

SQL> create index ziggy_code13_i on ziggy(code13);

Index ZIGGY_CODE13_I created.

SQL> create index ziggy_code14_i on ziggy(code14);

Index ZIGGY_CODE14_I created.

SQL> create index ziggy_code15_i on ziggy(code15);

Index ZIGGY_CODE15_I created.

SQL> create index ziggy_code16_i on ziggy(code16);

Index ZIGGY_CODE16_I created.

SQL> create index ziggy_code17_i on ziggy(code17);

Index ZIGGY_CODE17_I created.

SQL> create index ziggy_code18_i on ziggy(code18);

Index ZIGGY_CODE18_I created.

SQL> create index ziggy_code19_i on ziggy(code19);

Index ZIGGY_CODE19_I created.

SQL> create index ziggy_code20_i on ziggy(code20);

Index ZIGGY_CODE20_I created.

Let’s take note of the size of the table and its associated indexes:

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt
from user_tables where table_name='ZIGGY';

   TABLE_NAME    NUM_ROWS    BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
ZIGGY              200000      3268              60          857            113            0

SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes 
where table_name='ZIGGY';

       INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
_________________ _________ ______________ ____________________
ZIGGY_CODE20_I            1            473                 3250
ZIGGY_ID_I                1            473                 3250
ZIGGY_CODE1_I             1            473                 3250
ZIGGY_CODE2_I             1            473                 3250
ZIGGY_CODE3_I             1            473                 3250
ZIGGY_CODE4_I             1            473                 3250
ZIGGY_CODE5_I             1            473                 3250
ZIGGY_CODE6_I             1            473                 3250
ZIGGY_CODE7_I             1            473                 3250
ZIGGY_CODE8_I             1            473                 3250
ZIGGY_CODE9_I             1            473                 3250
ZIGGY_CODE10_I            1            473                 3250
ZIGGY_CODE11_I            1            473                 3250
ZIGGY_CODE12_I            1            473                 3250
ZIGGY_CODE13_I            1            473                 3250
ZIGGY_CODE14_I            1            473                 3250
ZIGGY_CODE15_I            1            473                 3250
ZIGGY_CODE16_I            1            473                 3250
ZIGGY_CODE17_I            1            473                 3250
ZIGGY_CODE18_I            1            473                 3250
ZIGGY_CODE19_I            1            473                 3250

We next perform an update on the table that will increase the row size sufficiently to result in a bunch of migrated rows:

SQL> update ziggy set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS';

200,000 rows updated.
Elapsed: 00:00:07.716

I’ll then perform a COMMIT and look at differences in the table statistics:

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

SQL> analyze table ziggy compute statistics;

Table ZIGGY analyzed.

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt 
from user_tables where table_name='ZIGGY';

   TABLE_NAME   NUM_ROWS     BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
ZIGGY              200000      4906              86          415            170        56186

 

We can see that there are indeed a bunch of migrated/chained rows, some 56186 of them.

We also notice that the size of the table has increased to 4906 blocks (previously is was 3268). This increase is in large part due to the increased size of the NAME column value, but also partly due to the storage allocated to pointers that are stored in the original block to denote the new location of the migrated rows (as discussed previously here).

If we look at the current state of the indexes:

SQL> select index_name, blevel, leaf_blocks, clustering_factor 
from user_indexes where table_name='ZIGGY';

       INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
_________________ _________ ______________ ____________________
ZIGGY_CODE7_I             1            473                 3250
ZIGGY_CODE8_I             1            473                 3250
ZIGGY_CODE9_I             1            473                 3250
ZIGGY_CODE10_I            1            473                 3250
ZIGGY_CODE11_I            1            473                 3250
ZIGGY_CODE12_I            1            473                 3250
ZIGGY_CODE13_I            1            473                 3250
ZIGGY_CODE14_I            1            473                 3250
ZIGGY_CODE15_I            1            473                 3250
ZIGGY_CODE16_I            1            473                 3250
ZIGGY_CODE17_I            1            473                 3250
ZIGGY_CODE18_I            1            473                 3250
ZIGGY_CODE19_I            1            473                 3250
ZIGGY_CODE20_I            1            473                 3250
ZIGGY_ID_I                1            473                 3250
ZIGGY_CODE1_I             1            473                 3250
ZIGGY_CODE2_I             1            473                 3250
ZIGGY_CODE3_I             1            473                 3250
ZIGGY_CODE4_I             1            473                 3250
ZIGGY_CODE5_I             1            473                 3250
ZIGGY_CODE6_I             1            473                 3250

We notice that the indexes remain unchanged. As the table does NOT have ENABLE ROW MOVEMENT set, the indexes are NOT updated at all as part of the migrated row process (thus the same behaviour as non-autonomous database environments).

However, if I perform the same demo but instead perform a ROLLBACK of the transaction rather than the commit:

SQL> rollback;

Rollback complete.

Elapsed: 00:00:06.919

Note that the rollback takes 00:00:06.919 to complete.

If we now look at the size of the table and corresponding indexes:

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

PL/SQL procedure successfully completed.

SQL> analyze table ziggy compute statistics;

Table ZIGGY analyzed.

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt 
from user_tables where table_name='ZIGGY';

   TABLE_NAME    NUM_ROWS    BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
ZIGGY              200000      4906              86         2899            113            0

We notice that the size of the table has increased to be the same 4906 blocks as when we performed the commit. The extra storage remains allocated even after the rollback operation.

That’s because Oracle does NOT deallocate any additional storage that might have been consumed during the original transaction. We notice that the AVG_SPACE has increased substantially as a result (now 2899, previously it was just 857).

If we look at the current state of the indexes after the rollback:

SQL> select index_name, blevel, leaf_blocks, clustering_factor 
from user_indexes where table_name='ZIGGY';

       INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
_________________ _________ ______________ ____________________
ZIGGY_CODE20_I            1            473                 3250
ZIGGY_ID_I                1            473                 3250
ZIGGY_CODE1_I             1            473                 3250
ZIGGY_CODE2_I             1            473                 3250
ZIGGY_CODE3_I             1            473                 3250
ZIGGY_CODE4_I             1            473                 3250
ZIGGY_CODE5_I             1            473                 3250
ZIGGY_CODE6_I             1            473                 3250
ZIGGY_CODE7_I             1            473                 3250
ZIGGY_CODE8_I             1            473                 3250
ZIGGY_CODE9_I             1            473                 3250
ZIGGY_CODE10_I            1            473                 3250
ZIGGY_CODE11_I            1            473                 3250
ZIGGY_CODE12_I            1            473                 3250
ZIGGY_CODE13_I            1            473                 3250
ZIGGY_CODE14_I            1            473                 3250
ZIGGY_CODE15_I            1            473                 3250
ZIGGY_CODE16_I            1            473                 3250
ZIGGY_CODE17_I            1            473                 3250
ZIGGY_CODE18_I            1            473                 3250
ZIGGY_CODE19_I            1            473                 3250

We notice that all the indexes again remain unchanged. As the indexes are not updated during the transaction, this is of course to be expected.

 

Let’s now repeat the same demo, but this time on a table with ENABLE ROW MOVEMENT set:

SQL> create table ziggy2(id number, code1 number, code2 number, code3 number, code4 number, code5 number, code6 number, code7 number, code8 number, code9 number, code10 number, code11 number, code12 number, code13 number, code14 number, code15 number, code16 number, code17 number, code18 number, code19 number, code20 number, name varchar2(142)) PCTFREE 0 ENABLE ROW MOVEMENT;

Table ZIGGY2 created.

SQL> insert into ziggy2 SELECT rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, rownum, 'BOWIE' FROM dual CONNECT BY LEVEL <= 200000;

200,000 rows inserted.

SQL> commit;

Commit complete.

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

PL/SQL procedure successfully completed.

SQL> analyze table ziggy2 compute statistics;

Table ZIGGY2 analyzed.

SQL> create index ziggy2_id_i on ziggy2(id);

Index ZIGGY2_ID_I created.

SQL> create index ziggy2_code1_i on ziggy2(code1);

Index ZIGGY2_CODE1_I created.

SQL> create index ziggy2_code2_i on ziggy2(code2);

Index ZIGGY2_CODE2_I created.

SQL> create index ziggy2_code3_i on ziggy2(code3);

Index ZIGGY2_CODE3_I created.

SQL> create index ziggy2_code4_i on ziggy2(code4);

Index ZIGGY2_CODE4_I created.

SQL> create index ziggy2_code5_i on ziggy2(code5);

Index ZIGGY2_CODE5_I created.

SQL> create index ziggy2_code6_i on ziggy2(code6);

Index ZIGGY2_CODE6_I created.

SQL> create index ziggy2_code7_i on ziggy2(code7);

Index ZIGGY2_CODE7_I created.

SQL> create index ziggy2_code8_i on ziggy2(code8);

Index ZIGGY2_CODE8_I created.

SQL> create index ziggy2_code9_i on ziggy2(code9);

Index ZIGGY2_CODE9_I created.

SQL> create index ziggy2_code10_i on ziggy2(code10);

Index ZIGGY2_CODE10_I created.

SQL> create index ziggy2_code11_i on ziggy2(code11);

Index ZIGGY2_CODE11_I created.

SQL> create index ziggy2_code12_i on ziggy2(code12);

Index ZIGGY2_CODE12_I created.

SQL> create index ziggy2_code13_i on ziggy2(code13);

Index ZIGGY2_CODE13_I created.

SQL> create index ziggy2_code14_i on ziggy2(code14);

Index ZIGGY2_CODE14_I created.

SQL> create index ziggy2_code15_i on ziggy2(code15);

Index ZIGGY2_CODE15_I created.

SQL> create index ziggy2_code16_i on ziggy2(code16);

Index ZIGGY2_CODE16_I created.

SQL> create index ziggy2_code17_i on ziggy2(code17);

Index ZIGGY2_CODE17_I created.

SQL> create index ziggy2_code18_i on ziggy2(code18);

Index ZIGGY2_CODE18_I created.

SQL> create index ziggy2_code19_i on ziggy2(code19);

Index ZIGGY2_CODE19_I created.

SQL> create index ziggy2_code20_i on ziggy2(code20);

Index ZIGGY2_CODE20_I created.

 

The table and indexes all have the same initial size as the previous example:

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt
from user_tables where table_name='ZIGGY2';

   TABLE_NAME    NUM_ROWS    BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
ZIGGY2             200000      3268              60          857            113            0

SQL> select index_name, blevel, leaf_blocks, clustering_factor from user_indexes 
where table_name='ZIGGY2';

        INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
__________________ _________ ______________ ____________________
ZIGGY2_ID_I                1            473                 3250
ZIGGY2_CODE1_I             1            473                 3250
ZIGGY2_CODE2_I             1            473                 3250
ZIGGY2_CODE3_I             1            473                 3250
ZIGGY2_CODE4_I             1            473                 3250
ZIGGY2_CODE5_I             1            473                 3250
ZIGGY2_CODE6_I             1            473                 3250
ZIGGY2_CODE7_I             1            473                 3250
ZIGGY2_CODE8_I             1            473                 3250
ZIGGY2_CODE9_I             1            473                 3250
ZIGGY2_CODE10_I            1            473                 3250
ZIGGY2_CODE11_I            1            473                 3250
ZIGGY2_CODE12_I            1            473                 3250
ZIGGY2_CODE13_I            1            473                 3250
ZIGGY2_CODE14_I            1            473                 3250
ZIGGY2_CODE15_I            1            473                 3250
ZIGGY2_CODE16_I            1            473                 3250
ZIGGY2_CODE17_I            1            473                 3250
ZIGGY2_CODE18_I            1            473                 3250
ZIGGY2_CODE19_I            1            473                 3250
ZIGGY2_CODE20_I            1            473                 3250

 

If we now perform the same Update followed by the commit:

SQL> update ziggy2 set name='THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS';

200,000 rows updated.
Elapsed: 00:00:33.390

SQL> commit;

Commit complete.

If we look at the current size of the table after the update:

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

PL/SQL procedure successfully completed.

SQL> analyze table ziggy2 compute statistics;

Table ZIGGY2 analyzed.

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt 
from user_tables where table_name='ZIGGY2';

   TABLE_NAME    NUM_ROWS   BLOCKS     EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
ZIGGY2             200000      4654              82          367            169            0

We notice it has increased to 4654 blocks (previously 3268). But the table is not quite as large in size as in the first demo, where the table grew to 4906 blocks (so 252 fewer blocks).

The increase in table size is now due entirely as a result in the increased NAME column values, as Oracle has not had to consume any storage for pointers in the original table blocks to denote a new location of the rows, as the ROWIDs are now updated in the indexes on the fly.

So this is a positive, a DECREASE in the comparative size of table after such updates that migrate rows.

And of course, there are no migrated/chained rows.

But if we look at the index statistics after the commit:

SQL> select index_name, blevel, leaf_blocks, clustering_factor 
from user_indexes where table_name='ZIGGY2';

        INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
__________________ _________ ______________ ____________________
ZIGGY2_ID_I                2            945               109061
ZIGGY2_CODE1_I             2            945               109061
ZIGGY2_CODE2_I             2            945               109061
ZIGGY2_CODE3_I             2            945               109061
ZIGGY2_CODE4_I             2            945               109061
ZIGGY2_CODE5_I             2            945               109061
ZIGGY2_CODE6_I             2            945               109061
ZIGGY2_CODE7_I             2            945               109061
ZIGGY2_CODE8_I             2            945               109061
ZIGGY2_CODE9_I             2            945               109061
ZIGGY2_CODE10_I            2            945               109061
ZIGGY2_CODE11_I            2            945               109061
ZIGGY2_CODE12_I            2            945               109061
ZIGGY2_CODE13_I            2            945               109061
ZIGGY2_CODE14_I            2            945               109061
ZIGGY2_CODE15_I            2            945               109061
ZIGGY2_CODE16_I            2            945               109061
ZIGGY2_CODE17_I            2            945               109061
ZIGGY2_CODE18_I            2            945               109061
ZIGGY2_CODE19_I            2            945               109061
ZIGGY2_CODE20_I            2            945               109061

 

We notice that ALL the indexes have significantly increased in size and now have 945 leaf blocks (previously it was just 473 leaf blocks). Additionally as a result of this increase in index size, the BLEVEL of the indexes has also increased and are now 2 (previously it was 1).

Here’s the thing. As I’ve discussed many times before, when Oracle performs an “Update” of an index entry, this is actually implemented as a Delete/Insert operation. By changing the ROWID of an index entry, Oracle first deletes the original index entry and inserts a new index entry with the new ROWID. So the previous index entry remains (with the space likely eventually reused by another new index entry in the future).

So these “on the fly” updates of the indexes to keep the ROWIDs current due to row migrations increases the likelihood of index block splits and the subsequent increase in index storage allocations.

In very rare, extreme cases (and this demo is indeed an extreme case as I’m updating all rows in my table), this extra index storage could potentially result in an increase in the index BLEVEL.

However, in most scenarios, this increase in index storage is likely to be moderate and result in extra index storage that will eventually be consumed by subsequent new rows anyways.

If instead of the commit operation, we instead performed a rollback:

SQL> rollback;

Rollback complete.

Elapsed: 00:00:36.639

We notice that the rollback takes considerably longer at 00:00:36.639 (previously in the first demo, it was just 00:00:06.919).

As the ROWIDs are now all updated on the fly on all the corresponding indexes, there’s that much more data that needs to be rolled back within these indexes.

If we look at the current size of the table after the rollback:

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

PL/SQL procedure successfully completed.

SQL> analyze table ziggy2 compute statistics;

Table ZIGGY2 analyzed.

SQL> select table_name, num_rows, blocks, empty_blocks, avg_space, avg_row_len, chain_cnt
from user_tables where table_name='ZIGGY2';

   TABLE_NAME    NUM_ROWS    BLOCKS    EMPTY_BLOCKS    AVG_SPACE    AVG_ROW_LEN    CHAIN_CNT
_____________ ___________ _________ _______________ ____________ ______________ ____________
ZIGGY2             200000      4654              82         2823            113            0

We notice it has increased to the same 4654 blocks as with the commit. The extra storage remains allocated even after the rollback operation.

That’s again because Oracle does NOT deallocate any additional storage that might have been consumed during the original transaction. We notice that the AVG_SPACE has again increased substantially as a result (now 2823, previously it was just 857).

If we look at the current state of the indexes after the rollback:

 

SQL> select index_name, blevel, leaf_blocks, clustering_factor 
from user_indexes where table_name='ZIGGY2';

        INDEX_NAME    BLEVEL    LEAF_BLOCKS    CLUSTERING_FACTOR
__________________ _________ ______________ ____________________
ZIGGY2_ID_I                2            945                 3250
ZIGGY2_CODE1_I             2            945                 3250
ZIGGY2_CODE2_I             2            945                 3250
ZIGGY2_CODE3_I             2            945                 3250
ZIGGY2_CODE4_I             2            945                 3250
ZIGGY2_CODE5_I             2            945                 3250
ZIGGY2_CODE6_I             2            945                 3250
ZIGGY2_CODE7_I             2            945                 3250
ZIGGY2_CODE8_I             2            945                 3250
ZIGGY2_CODE9_I             2            945                 3250
ZIGGY2_CODE10_I            2            945                 3250
ZIGGY2_CODE11_I            2            945                 3250
ZIGGY2_CODE12_I            2            945                 3250
ZIGGY2_CODE13_I            2            945                 3250
ZIGGY2_CODE14_I            2            945                 3250
ZIGGY2_CODE15_I            2            945                 3250
ZIGGY2_CODE16_I            2            945                 3250
ZIGGY2_CODE17_I            2            945                 3250
ZIGGY2_CODE18_I            2            945                 3250
ZIGGY2_CODE19_I            2            945                 3250
ZIGGY2_CODE20_I            2            945                 3250

We notice that they all remain at their increased size of 945 leaf blocks and with the Blevel of 2.

Again, when Oracle performs the rollback, Oracle does NOT undo all the index block splits and leaves all the additional storage allocated to the indexes.

So, just a word of caution.

Updating all the ROWIDs on the fly when a row migrates does not come for free. There’s additional resources that need to be consumed during these updates AND there is a potential issue with indexes having to perform additional index block splits and consume additional storage (at least immediately) after such operations.

If you have applications that makes bulk changes to data that can result in rollbacks, again, it’s just worth noting the extra storage that may be consumed as a result (until the additional data is finally added to the table).

Yes, index rebuilds can be performed to reduce the subsequent size of these inflated indexes.

BUT, for those of you with sharp eyes, you might also have noted another potential issue with this new behaviour, which I’ll discuss in my next post… 🙂