在 Oracle 10g 中启用/禁用 ROW_MOVEMENT 有什么影响？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19430145/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the impact of enabling/disabling ROW_MOVEMENT in Oracle 10g?
提问by Sachin Thapa
We are using oracle 10G and one of the table takes lot of time if we query/delete data. This table contains around 10 million records.
我们使用的是 oracle 10G，如果我们查询/删除数据，其中一张表会花费很多时间。该表包含大约 1000 万条记录。
Recently we discovered that ROW_MOVEMENT is disabled on this table, we want to understand following:
最近我们发现这张表上禁用了 ROW_MOVEMENT，我们想了解以下内容：
- What performance gain we can get if enable ROW_MOVEMENT ?
- Are there any downsides of enabling ROW_MOVEMENT ?
- What trigger's row movement ? How does oracle decides it needs to move ROWS ?
- 如果启用 ROW_MOVEMENT ，我们可以获得什么性能提升？
- 启用 ROW_MOVEMENT 有什么缺点吗？
- 什么触发器的行移动？oracle 如何决定它需要移动 ROWS ？
Any help would be highly appreciated.
任何帮助将不胜感激。
Thanks in advance !!
提前致谢 ！！
回答by Vincent Malgrat
Row movement is mainly applied to partition tables. It allows rows to be moved across partitions. With row movement disabled, which is the default, you can't move a row with an 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 allow row movement, you can move rows with an update:
当您允许行移动时，您可以通过更新移动行：
SQL> ALTER TABLE part_table ENABLE ROW MOVEMENT;
Table altered
SQL> UPDATE part_table SET ID = 2;
1 row updated
This feature doesn't affect performance in most cases: the rows are stored and queried in exactly the same manner whether the feature is enabled or not. However, when row movement is enabled, the rows can be physically moved (similar to delete+insert) with ALTER TABLE SHRINK SPACEfor example. Thismay in turn affect index cluster factor for instance, which may affect the performance of some queries.
在大多数情况下，此功能不会影响性能：无论是否启用该功能，行都以完全相同的方式存储和查询。但是，当启用行移动时，可以使用例如物理移动行（类似于删除+插入）ALTER TABLE SHRINK SPACE。这可能反过来影响指数集群的因素，例如，这可能会影响某些查询的性能。
Row movement is disabled by default because it implies that the rowidof a row may change, which is not the usual behaviour in Oracle.
默认情况下禁用行移动，因为这意味着rowid行的 可能会更改，这不是 Oracle 中的通常行为。
回答by Kacper
Downside of row movement is that ROWIDs may be changed. So if you have any queries based on ROWIDthey may return wrong results
行移动的缺点是ROWIDs 可能会改变。因此，如果您有任何基于ROWID它们的查询，它们可能会返回错误的结果
回答by TheShadow
Also see http://www.dba-oracle.com/t_callan_oracle_row_movement.htm
另见http://www.dba-oracle.com/t_callan_oracle_row_movement.htm
ROW MOVEMENT is necessary for following operations:
以下操作需要行移动：
- Move Rows between Partitions (Partitioned Tables)
- Flashback of tables
- Shrink Tables (Compact, cascade, normal)
- 在分区之间移动行（分区表）
- 表的闪回
- 收缩表（紧凑型、级联型、普通型）
Answer to your Question: no performance increase if you enable row movement, but you can shrink tables which would increase your performance for full table queries.
回答您的问题：如果启用行移动，则不会提高性能，但您可以缩小表，这将提高全表查询的性能。
ASKTOM also gives a very good answer to your question:
ASKTOM 也很好的回答了你的问题：
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:35203106066718
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:35203106066718
Regarding Shrink (which requires ROW MOVEMENT):
关于收缩（需要 ROW MOVEMENT）：
I would suggest benchmarking -- collect performance metrics about the table before and after performing the operation. You would expect full scans to operate more efficiently after, you would expect index range scans to either be unchanged or "better" as you have more rows per block packed together (less data spread). You would be looking for that to happen -- statspack or the tools available in dbconsole would be useful for measuring that (the amount of work performed by your queries over time)
我建议进行基准测试——在执行操作之前和之后收集有关表的性能指标。您会期望完全扫描在之后更有效地运行，您会期望索引范围扫描保持不变或“更好”，因为每个块有更多的行打包在一起（更少的数据传播）。您会希望这种情况发生 - statspack 或 dbconsole 中可用的工具可用于衡量这一点（您的查询随时间执行的工作量）
