12 July, 2011

ENABLE ROW MOVEMENT

Since the ALTER TABLE SHRINK command appeared and "ENABLE ROW MOVEMENT" has been presented as a requirement, some DBAs have been confused about what it means to enable row movement.

This does *NOT* cause Oracle to automatically move a row. However, a row may be moved as a result of action by the DBA (e.g. ALTER TABLE SHRINK) or a User / Application. The latter is the case where a row in a Partitioned Table has to move from one Partition to another because the Partition Key itself in that row has been updated.
Note that updating the Partition Key (such that a row actually moves to another Partition) is frowned upon and is not enabled by default. In the rare case where your design has a flaw that rows have to move between Partitions, you need to ENABLE ROW MOVEMENT.

In the example below, a row has to move from the 'SG' Partition to the 'IN' Partition. This is disallowed by Oracle until and unless the DBA ENABLEs ROW MOVEMENT :

SQL> -- create a "normal" partitoned table
SQL> drop table my_emp_tbl purge;

Table dropped.

SQL> create table my_emp_tbl
2 (emp_id number not null primary key,
3 country_id varchar2(2),
4 emp_name varchar2(50),
5 join_date date)
6 partition by list (country_id)
7 (
8 partition my_emp_tbl_in values ('IN'),
9 partition my_emp_tbl_sg values ('SG'),
10 partition my_emp_tbl_us values ('US')
11 )
12 /

Table created.

SQL>
SQL> insert into my_emp_tbl
2 select rownum,
3 decode(mod(rownum,3),0,'IN',1,'SG',2,'US'),
4 dbms_random.string('X',40),
5 sysdate-365+rownum
6 from dual connect by level < 100
7 /

99 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- collect statistics on row counts
SQL> exec dbms_stats.gather_table_stats('','MY_EMP_TBL',estimate_percent=>100,granularity=>'ALL');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
2 from user_tab_partitions
3 where table_name = 'MY_EMP_TBL'
4 order by partition_position
5 /

PARTITION_NAME NUM_ROWS
------------------------------ ----------
MY_EMP_TBL_IN 33
MY_EMP_TBL_SG 33
MY_EMP_TBL_US 33

SQL>
SQL> -- identify employee 4
SQL> select * from my_emp_tbl where emp_id = 4;

EMP_ID CO EMP_NAME
---------- -- --------------------------------------------------
JOIN_DATE
---------
4 SG 9WSXMYH66V6I8B1LKKW7YRTG2VYQGY2OPIT54OFW
16-JUL-10


SQL> -- change the employees country
SQL> update my_emp_tbl set country_id = 'IN' where emp_id = 4;
update my_emp_tbl set country_id = 'IN' where emp_id = 4
*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


SQL>
SQL> -- enable row movement
SQL> alter table my_emp_tbl enable row movement;

Table altered.

SQL> update my_emp_tbl set country_id = 'IN' where emp_id = 4;

1 row updated.

SQL>


So it is not just ALTER TABLE SHRINK that the ENABLE ROW MOVEMENT is necessary for. Yet, it is likely a defect in your design if you have frequent movements of rows between Partitions. Remember that GLOBAL and LOCAL Indexes have to be updated.
.
.
.

1 comment:

Anonymous said...

Good Example..

Thanks
Harpreet Singh