我在单个 Oracle 事务中可以完成的工作量是否有限制？
我必须在单个事务中更新三个表。我正在使用 Oracle Spatial 与潜在的数十万条记录进行交叉。为了保持交叉引用完整性，我确实需要在单个事务中更新具有大量交叉引用的三个表。
我在单笔交易中可以做的事情是否有可衡量的限制？在单个事务中塞满数千个插入/更新/删除操作是否存在陷阱？对于这些陷阱，是否有任何众所周知的模式/实践来处理它们？
I have to update three tables in a single transaction. I'm using Oracle Spatial intersecting against potentially hundreds of thousands of records. To maintain cross-reference integrity, I really need to update three tables with large volumes of cross-references in a single transaction.
Is there a measurable limit to how much I can do in a single transaction? Are there pitfalls to cramming thousands of insert/update/delete operations in a single transaction? For the pitfalls, are there any well-known patterns/practices for handling them?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
该限制由撤消表空间大小设置。每个事务的更改必须完全适合撤消表空间。
the limit is set by the undo tablespace size. Each transaction's change must fully fit into the undo tablespace.
不，假设您提供足够的 UNDO 空间来容纳更改，那么您在单个事务中可以执行的工作量没有限制。当然，如果有很多人同时执行事务，并且有可能多个人会尝试影响同一行，则长时间运行的事务可能会引入锁争用。如果您的事务需要全程人工输入（即，您的事务是一个人尝试各种方式在一个地理区域上运行管道），则单个长时间运行的事务可能会产生问题，即人们想要在工作完全完成之前离开。
Oracle Workspace Manager 是 Oracle 数据库的一个组件，专门创建用于处理大型空间数据集上此类长时间运行的事务，允许用户在单独的工作区中工作，这些工作区可以在几天或几周后合并回父工作区。工作区管理器的语义与事务的语义非常相似，但提供了离开和重新加入工作区、在工作区之间切换以及具有子工作区层次结构的能力。
No, there is no limit to the amount of work you can do in a single transaction, assuming you provide enough UNDO space to accomodate the change. Of course, if you have many people doing transactions at the same time and you have the possibility that multiple people will be trying to affect the same row, long-running transactions can introduce lock contention. And if your transactions require human input throughout (i.e. your transaction is a human trying various ways to run a pipeline over a geographic region), a single long-running transaction may create problems where the human wants to leave work before they are completely done.
Oracle Workspace Manager is a component of the Oracle database that was specifically created to handle these sorts of very long-running transactions on large spatial data sets by allowing users to work in separate workspaces that can be merged back into the parent workspace days or weeks later. The semantics of Workspace Manager are very similar to the semantics of transactions but provide the ability to leave and rejoin a workspace, to switch between workspaces, and to have a hierarchy of child workspaces.