Oracle SQL技术避免填充事务日志 [英] Oracle SQL technique to avoid filling trans log

问题描述

从Oracle编程(从Sybase和MS SQL Server)重新开始.避免用大量更新填充事务日志的"Oracle方法"是什么?

Newish to Oracle programming (from Sybase and MS SQL Server). What is the "Oracle way" to avoid filling the trans log with large updates?

在我的特定情况下，我正在更新可能非常多的行.这是我的方法:

In my specific case, I'm doing an update of potentially a very large number of rows. Here's my approach:

UPDATE my_table
SET a_col = null
WHERE my_table_id IN 
(SELECT my_table_id FROM my_table WHERE some_col < some_val and rownum < 1000)

...在循环内执行此操作，直到更新的行计数为零，

...where I execute this inside a loop until the updated row count is zero,

这是最好的方法吗?

谢谢

推荐答案

如果您多次(例如1000条)运行UPDATE，则完全不会减少对重做和撤消日志的更新量.最重要的是，与运行单个大型SQL相比，总查询时间可能会更长.

The amount of updates to the redo and undo logs will not at all be reduced if you break up the UPDATE in multiple runs of, say 1000 records. On top of it, the total query time will be most likely be higher compared to running a single large SQL.

没有真正的方法来解决UPDATEs中的UNDO/REDO日志问题.通过INSERT和CREATE TABLE，您可以使用DIRECT aka APPEND选项，但是我想这对您来说并不容易.

There's no real way to address the UNDO/REDO log issue in UPDATEs. With INSERTs and CREATE TABLEs you can use a DIRECT aka APPEND option, but I guess this doesn't easily work for you.

这篇关于Oracle SQL技术避免填充事务日志的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！