Geek Answers Handbook

What is the effect of enabling / disabling ROW_MOVEMENT in Oracle 10g?

We use oracle 10G, and one of the tables takes a lot of time if we request / delete data. This table contains about 10 million records.

We recently discovered that ROW_MOVEMENT is disabled in this table, we want to understand the following:

  • What performance gain can we get if we enable ROW_MOVEMENT?
  • Are there any disadvantages of including ROW_MOVEMENT?
  • What is the movement of the trigger? How does the oracle decide that it needs to move the ROWS?

Any help would be greatly appreciated.

Thanks in advance!

+7
oracle oracle10g
source share
3 answers

Row movement is mainly applied to partition tables . It allows you to move lines across sections. If line moving is disabled by default, you cannot move the line with the update:

SQL> CREATE TABLE part_table (ID NUMBER) 2 PARTITION BY RANGE (ID) 3 (PARTITION p0 VALUES LESS THAN (1), 4 PARTITION p1 VALUES LESS THAN (MAXVALUE)); Table created SQL> INSERT INTO part_table VALUES (0); 1 row inserted SQL> UPDATE part_table SET ID = 2; UPDATE part_table SET ID = 2 ORA-14402: updating partition key column would cause a partition change

When you enable row moving, you can move rows with the update:

 SQL> ALTER TABLE part_table ENABLE ROW MOVEMENT; Table altered SQL> UPDATE part_table SET ID = 2; 1 row updated

This function does not affect performance in most cases: rows are stored and requested in the same way, whether this function is enabled or not. However, when row moving is activated, rows can be physically moved (similar to delete + insert) using ALTER TABLE SHRINK SPACE . This can, in turn, affect the index cluster factor, for example, which can affect the performance of some queries.

Line movement is disabled by default because it means that the row rowid row may change, which is not normal behavior in Oracle.

+8
source share

Also see http://www.dba-oracle.com/t_callan_oracle_row_movement.htm

ROW MOVEMENT is necessary for the following operations:

  • Moving rows between partitions (partitioned tables)
  • Flashback from tables
  • Shrink tables (compact, cascading, normal)

Answer your question: there is no performance increase if you enable row moving, but you can reduce the number of tables, which will increase your performance for full queries in a table.

ASKTOM also gives a very good answer to your question:

https://asktom.oracle.com/pls/asktom/f?p=100:11mail:::::1111QUESTION_ID:35203106066718

Regarding the reduction (which requires ROW MOVEMENT):

I would suggest to conduct a comparative analysis - to collect the performance indicators of the table before and after the operation. You would expect a full scan to work more efficiently after that, you expect the index range the scan is either unchanged or "better" because you have more rows per block packed together (except for data distribution). You would be looking for this to happen - statspack or the tools available in dbconsole can be useful for measuring this (the amount of work your queries do over time)

+1
source share

The downside of the line movement is that the ROWID can be changed. Therefore, if you have any ROWID based ROWID , they may return incorrect results.

+1
source share

More articles:


All Articles