jump to navigation

When Does A ROWID Change? Part II (“You’ve Got A Habit Of Leaving”) December 9, 2022

Posted by Richard Foote in Autonomous Database, CBO, Changing ROWID, Global Indexes, Multiple Indexes, Oracle, Oracle 21c, Oracle Blog, Oracle Cloud, Oracle General, Partitioning, Performance Tuning, Richard's Blog, ROWID.
3 comments

In my previous post, I discussed how a row is generally “migrated”, but the ROWID remains unchanged, when a row is updated such that it can no longer fit into its current block. Hence the general rule has always been that the ROWID of a row does not change.

However, even before the changes now present with Oracle Autonomous Databases (to be discussed in future posts), there has always been (since Oracle 8) one classic scenario when this “ROWID never changes after an update” rule has not been true.

To illustrate, I’m going to create and populate a basic little Range-based Partitioned table, with the RELEASE_DATE column being the partitioned column:

SQL> CREATE TABLE big_bowie(id 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));

Table created.

SQL> INSERT INTO big_bowie SELECT rownum, sysdate-mod(rownum,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_BOWIE');

PL/SQL procedure successfully completed.

Let’s look at the current ROWIDs of a few random rows:

SQL> select id, release_date, rowid from big_bowie where id in (424, 444, 482) order by id;

        ID RELEASE_D ROWID
---------- --------- ------------------
       424 08-OCT-21 AAASe9AAMAAAAj7ABU
       444 18-SEP-21 AAASe9AAMAAAAj7ABo
       482 11-AUG-21 AAASe9AAMAAAAj7ACO

I’m now going to try and update for these rows, the partitioned column value, such that they would now logically belong in the other partition:

SQL> update big_bowie set release_date='06-DEC-22' where id in (424, 444, 482);
update big_bowie set release_date='06-DEC-22' where id in (424, 444, 482)
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

I now get a very key and important error. By default, Oracle does not allow you to update a row if it results in the row having to physically move to a different partition.

I suspect there are at least 3 good reasons for this default restriction.

One is to protect the business integrity of the data, where it might just not make any business sense for a row to be updated in this manner.

The second is that it protects any applications out there that explicitly uses ROWIDs and relies on the ROWIDs not suddenly changing behind the scenes.

And finally, it protects perhaps valuable database resources and ensures that the database does not have to incur any additional workloads, that would be necessary if such an operation were to proceed.

But we have the ability and control to override this default behaviour in the following manner with the ENABLE ROW MOVEMENT clause:

SQL> alter table big_bowie enable row movement;

Table altered.

If we now try and update these rows again:

SQL> update big_bowie set release_date='06-DEC-22' where id in (424, 444, 482);

3 rows updated.

SQL> commit;

Commit complete.

The updates are now successful.

As these rows no longer logically belong in the previous partition, they have to be physically moved to its new partition. This is effectively implemented by deleting the rows from the previous partition and then re-inserting them in the new partition segment.

If we now look the ROWIDs of these updated rows:

SQL> select id, release_date, rowid from big_bowie where id in (424, 444, 482) order by id;

        ID RELEASE_D ROWID
---------- --------- ------------------
       424 06-DEC-22 AAASe+AAMAAAATQABR
       444 06-DEC-22 AAASe+AAMAAAATQABS
       482 06-DEC-22 AAASe+AAMAAAATQABT

We notice that they now all have different ROWIDs, because they indeed now all exist in a different physical location.

In my next post, I’ll highlight but one obvious disadvantage and consequence of allowing rows to be physically moved in this manner…

12c: Intro To Multiple Indexes On Same Column List (Repetition) July 2, 2013

Posted by Richard Foote in 12c, Multiple Indexes, Oracle Indexes.
6 comments

From an indexing perspective, one of the bigger ticket items introduced with Oracle Database 12c is the new capability to create multiple indexes on the same column list. It’s even a feature listed in the New Features Guide 🙂 This can be particularly useful when you want to change the index definition in some way but you want to reduce the time in which the corresponding columns might not indexed.

Previous to 12c, you could not create an index if the same column list is already indexed and would generate an ORA-01408: such column list already indexed error. So, if you wanted to change an index from being say a B-Tree index to a Bitmap index, or from being Unique to Non-Unique or from being Non-Partitioned to Partitioned in same manner, etc. then you had to first drop the index and re-create it again as required. This means for the period in which the index is being re-created (which could be a considerable period for a larger index), the column list is not covered by an index, which might prove to be problematic.

Here, we create a partitioned table and create a Non-Unique, Non-Partitioned index on the ID column:

SQL> create table ziggy (id number, name varchar2(30)) partition by range (id) (partition ziggy1 values less than (1000), partition ziggy2 values less than (2000), partition ziggy3 values less than (maxvalue));

Table created.

SQL> insert into ziggy select rownum, 'DAVID BOWIE' from dual connect by level <=5000;

5000 rows created.

SQL> commit;

Commit complete.

SQL> create index ziggy_id_i1 on ziggy(id);              

Index created.

SQL> alter table ziggy add constraint ziggy_pk primary key(id);

Table altered.

If we decide for whatever reason we want to have say a Unique index instead:

SQL> create unique index ziggy_id_i2 on ziggy(id);
create unique index ziggy_id_i2 on ziggy(id)
                                         *
ERROR at line 1:
ORA-01408: such column list already indexed

No good, we can’t, even if we make it initially INVISIBLE:

SQL> create unique index ziggy_id_i2 on ziggy(id) invisible;
create unique index ziggy_id_i2 on ziggy(id) invisible
                                         *
ERROR at line 1:
ORA-01408: such column list already indexed

Because the index is used to police a PK constraint, we can’t even just drop the index:

SQL> drop index ziggy_id_i1;
drop index ziggy_id_i1
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

We have to first drop or disable the PK constraint, then drop the index, then re-create the index.

SQL> alter table ziggy drop primary key;

Table altered.

SQL> drop index ziggy_id_i1;

Index dropped.

SQL> alter table ziggy add primary key(id) using index (create unique index ziggy_id_i2 on ziggy(id));

Table altered.

This means the constraint is not automatically enforced (unless we disable it with validate, thus locking the table) and the ID column is not available via an index during the entire duration of creating the new index.

The 12c database has given us more flexibility in this regard.

Providing we create the index as INVISIBLE (meaning there is only ever the one Visible index on the column list), we can now create multiple indexes on the same column list, providing the new index has a different characteristic to existing indexes. So with a 12c database:

SQL> create unique index ziggy_id_i2 on ziggy(id) invisible;

Index created.

We now have two indexes on the same column list (the ID column). An attempt however to create or alter an index such that two visible indexes have the same column list will fail:

SQL> alter index ziggy_id_i2 visible;
alter index ziggy_id_i2 visible
*
ERROR at line 1:
ORA-14147: There is an existing VISIBLE index defined on the same set of
columns.

This means we can now (say) replace the index policing the PK constraint quicker (or convert a B-Tree to a Bitmap index or convert a Non-Partitioned index to a Partitioned index, etc.) as we don’t now have to wait for the new index to be created first:

SQL> alter table ziggy drop primary key;

Table altered.

SQL> drop index ziggy_id_i1;

Index dropped.

SQL> alter index ziggy_id_i2 visible;

Index altered.

SQL> alter table ziggy add constraint ziggy_pk primary key(id);

Table altered.

We can create as many indexes as we like on the ID, providing only one index is ever visible at a time and providing all the indexes have some form of differing characteristic. For example, all these indexes could potentially all co-exist:

SQL> create index ziggy_id_i3 on ziggy(id) local invisible;

Index created.

SQL> create bitmap index ziggy_id_i4 on ziggy(id) local invisible;

Index created.

SQL> create index ziggy_id_i5 on ziggy(id) reverse invisible;

Index created.

Of course, having many multiple indexes on the same column list in this manner is very likely a very bad idea …