13 July, 2011

ENABLE ROW MOVEMENT with MSSM

As I demonstrated in my previous blog post "ENABLE ROW MOVEMENT" the ALTER TABLE ... ENABLE ROW MOVEMENT is not just for supporting the ALTER TABLE ... SHRINK SPACE.

Furthermore, unlike ALTER TABLE ... SHRINK SPACE which requires that the Table be created in a Tablespace with Segment Space Management AUTO ("ASSM"), ENABLE ROW MOVEMENT can be done for a table in a Segment Space Management MANUAL ("MSSM") Tablespace as well.


SQL> create tablespace MSSM
2 datafile '/addtl/oracle/oradata/orcl/MSSM.dbf' size 100M
3 extent management local segment space management manual
4 /

Tablespace created.

SQL>
SQL>
SQL> -- create a "normal" partitoned table
SQL> -- this time create it in an MSSM tablespace
SQL> drop table my_emp_tbl purge;
drop table my_emp_tbl purge
*
ERROR at line 1:
ORA-00942: table or view does not exist


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 tablespace MSSM
13 /

Table created.

SQL>
SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'MY_EMP_TBL'
4 /

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
MY_EMP_TBL_IN MSSM
MY_EMP_TBL_SG MSSM
MY_EMP_TBL_US MSSM

SQL>
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 WS46VU0RIJDKOIXXAZSEPWUIVG0QBHTL4FUBNYPY
17-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>



I know for a fact that ALTER TABLE ... ENABLE ROW MOVEMENT appears in the 8i documentation --- predating ASSM Tablespaces.

Thus, ALTER TABLE ... ENABLE ROW MOVEMENT is independent of and significantly predates ALTER TABLE ... SHRINK.

.
.
.

2 comments:

Joel Garry said...

Yes, if you search for "enable row movement" (with the quotes) in all docs at MOS, you see things with 10 year old dates referring to bugs and what-all. Also, it is unfortunate that some docs lose their original date overridden by the modified ("checked for relevance") date, but Partitioning enhancements in Oracle8i [ID 73474.1] tantalizingly refers to pre-8i row movement:

Updatable partition keys
========================
When ROW MOVEMENT is enabled users have the ability to update partitioning key
columns in such a way that a row no longer belongs in its current partition,
causing such rows to migrate to the appropriate partition.

Prior to Oracle8i, and when ROW MOVEMENT is disabled, this sort of updates are
disallowed, resulting in an error returned to the user. For example...


word: disca

Hemant K Chitale said...

Joel,
My apologies for not commenting on your update earlier -- I had only glanced through it.

Yes, thank you for the additional information.

Hemant