One of the relatively newer
features in Oracle concerns the moving of rows. Why would a row move and who or
what controls that movement? Furthermore, by “move,” what exactly does move
mean? Does a row move to another table, or is row movement constrained to the
row’s container (i.e., a table)? An early use of row movement was highlighted
in Oracle8i, and row movement then, as well as now, applied to moving rows in a
partitioned table. With newer releases of the Oracle RDBMS, where else does row
movement come into play, and are there any gotcha’s with respect to row
movement operations?
This article looks at three
common cases or situations where row movement needs to be enabled.
Partitioned Tables
As far back as Oracle 8.1.5
documentation, row movement applied to updatable partition keys. A list partition
where the partition key is a specific value is a good example of this. Many
partitioned table examples use regions, cities and states as list examples.
What happens if you use a city as a partition key and an office in that city
moves elsewhere? Or two offices in the same city (from different groups or
business units within the same company) merge into one location? You could
split the default partition and add the new location name. How would you move
records from the old partitioned into the new one? Short of deleting from one
partition and inserting same into a new one, wouldn’t it be easier to be able
to perform a single update?
Let’s create a quick
partitioned table example and see how moving a row works.
SQL> CREATE TABLE CITY_OFFICES
2 (
3 OFFICE_NUMBER NUMBER NOT NULL,
4 CITY_ID VARCHAR2(12) NOT NULL,
5 OFFICE_NAME VARCHAR2(30) NOT NULL
6 )
7 PARTITION BY LIST (CITY_ID)
8 (
9 PARTITION P282 VALUES (‘282’),
10 PARTITION P283 VALUES (‘283’),
11 PARTITION P284 VALUES (‘284′));
Table created.
SQL>
SQL> INSERT INTO CITY_OFFICES VALUES (1,’282′,’DENVER’);
1 row created.
SQL> INSERT INTO CITY_OFFICES VALUES (2,’282′,’DENVER TECH CTR’);
1 row created.
SQL> INSERT INTO CITY_OFFICES VALUES (3,’282′,’DENVER WEST’);
1 row created.
SQL> INSERT INTO CITY_OFFICES VALUES (4,’282′,’BROOMFIELD’);
1 row created.
SQL> COMMIT;
Commit complete.
All of the cities are
located in the Denver area (city ID of 282 in this example). Broomfield is
further north of the Denver metro area and has been slated to become part of the
Boulder area office group (using a city ID of 283). Let’s try and move it by
updating the partition key value for that office.
SQL> UPDATE CITY_OFFICES SET CITY_ID = ‘283’ WHERE OFFICE_NUMBER = 4;
UPDATE CITY_OFFICES SET CITY_ID = ‘283’ WHERE OFFICE_NUMBER = 4
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change
Why didn’t the update work?
The error message doesn’t come right out and say it, but the reason is that row
movement is not enabled. How would you know this right off the bat? The reason
why you would know this is due to the fact that row movement – by default – is
not enabled. A simple alter table statement remedies the update problem.
SQL> ALTER TABLE CITY_OFFICES ENABLE ROW MOVEMENT;
Table altered.
SQL> UPDATE CITY_OFFICES SET CITY_ID = ‘283’ WHERE OFFICE_NUMBER = 4;
1 row updated.
SQL> COMMIT;
Commit complete.
SQL> ALTER TABLE CITY_OFFICES DISABLE ROW MOVEMENT;
Table altered.
Why did I disable the row
movement? Well, tables are partitioned for a reason, and to help ensure data
goes where it is supposed to, you should disallow inadvertent updates or row
movements. Obviously, if your application requires those types of updates, this
guideline wouldn’t necessarily apply to you.
Flashback
Being able to flashback DML
operations has greatly reduced the frequency of how often you hear a DBA say,
“Oops.” In the next example, I’ll delete a row, commit the operation, and try
to recover the deleted record.
SQL> DELETE FROM CITY_OFFICES WHERE OFFICE_NUMBER = 1;
1 row deleted.
SQL> COMMIT;
Commit complete.
SQL> FLASHBACK TABLE CITY_OFFICES
2 TO TIMESTAMP (SYSTIMESTAMP – INTERVAL ’05’ minute);
FLASHBACK TABLE CITY_OFFICES
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
The error message in this
case is much clearer as to the nature of the failed statement. I find it handy
to keep the flashback syntax around in an easily (and quickly) identifiable
location, and that is chiefly because I don’t use timestamps that much in my
applications. Time is still somewhat of the essence and the quicker you can
recover the table to a good state, the better.
SQL> ALTER TABLE CITY_OFFICES ENABLE ROW MOVEMENT;
Table altered.
SQL> FLASHBACK TABLE CITY_OFFICES
2 TO TIMESTAMP (SYSTIMESTAMP – INTERVAL ’05’ minute);
Flashback complete.
SQL> SELECT * FROM CITY_OFFICES;
OFFICE_NUMBER CITY_ID OFFICE_NAME
————- ———— ——————————
1 282 DENVER
2 282 DENVER TECH CTR
3 282 DENVER WEST
4 283 BROOMFIELD
If the table has row
movement disabled, why is it you can drop the table and flashback the table to
before the drop without row movement being enabled?
SQL> ALTER TABLE CITY_OFFICES DISABLE ROW MOVEMENT;
Table altered.
SQL> DROP TABLE CITY_OFFICES;
Table dropped.
SQL> FLASHBACK TABLE CITY_OFFICES TO BEFORE DROP;
Flashback complete.
Chapter 15 in the Database
Concepts guide states the following:
For Flashback Table to
succeed, the system must retain enough undo information to satisfy the
specified SCN or timestamp, and the integrity constraints specified on the
tables cannot be violated. Also, row movement must be enabled.
The example above shows that
“row movement must be enabled” is not entirely true.
Space Management
The third and final example
of where row movement comes into play can be found in shrink operations. If you
think about it, shrinking a table may entail moving data around within a table
(handled internally by Oracle), so the idea of a row moving around makes sense.
First, let’s get a record of the current ROWIDs for each office and then delete
two rows.
SQL> SELECT ROWID, OFFICE_NUMBER FROM CITY_OFFICES;
ROWID OFFICE_NUMBER
—————— ————-
AAANSfAAEAAAEAnAAA 1
AAANSfAAEAAAEAnAAD 2
AAANSfAAEAAAEAnAAE 3
AAANSgAAEAAAEAvAAA 4
SQL> DELETE FROM CITY_OFFICES WHERE OFFICE_NUMBER IN (2,3);
2 rows deleted.
SQL> COMMIT;
Commit complete.
Even in this small table, we
should be able to reclaim some space, so let’s try shrinking the table.
SQL> ALTER TABLE CITY_OFFICES SHRINK SPACE;
ALTER TABLE CITY_OFFICES SHRINK SPACE
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled
Notice that the error number
is different from the flashback example, but the message is pretty clear. We’ll
alter the table and perform the shrink operation.
SQL> ALTER TABLE CITY_OFFICES ENABLE ROW MOVEMENT;
Table altered.
SQL> ALTER TABLE CITY_OFFICES SHRINK SPACE;
Table altered.
Now that the shrink has
taken place, let’s examine the ROWIDs for the remaining two rows. Will the ROWIDs
be the same or different?
SQL> SELECT ROWID, OFFICE_NUMBER FROM CITY_OFFICES;
ROWID OFFICE_NUMBER
—————— ————-
AAANSfAAEAAAEAkAAA 1
AAANSgAAEAAAEAsAAA 4
Interestingly enough, the ROWIDs
for the two remaining rows are different from their original IDs before the
delete statement. You may have expected office_number 4’s ROWID to change, but
not office_number 1’s, but it too changed location after the shrink.
The moral of the story here
supports what the documentation says (in more than one place):
Before you use rowids in
DML statements, they should be verified and guaranteed not to change. The
intended rows should be locked so they cannot be deleted. Under some
circumstances, requesting data with an invalid rowid could cause a statement to
fail.
Using ROWIDs to perform DML
on records can be wicked fast, and is virtually the fastest way to access a
row. However, if there is any chance that someone else’s operation (and even
yours) can alter the ROWIDs of a table, you can find yourself with lots of
messy data. Further, a shrink operation (without the COMPACT option) can
invalidate open cursors. That could spell trouble for an application.
In Closing
We looked at three major
operations where row movement is required: partition key value change,
flashback and space management. Enabling and disabling row movement is very
simple to implement, and in most cases, has no unwanted side effects. Space
management operations using the SHRINK option can have unintended consequences
on other users and operations, and as we all know, good deeds (cleaning up
space) should never go unpunished. Using row movement and understanding what
takes place makes this feature an invaluable asset in your administration
arsenal, and it is up to you to be careful so as not to be bitten by a changed
ROWID.
