Search My Oracle Blog

Custom Search

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

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016