英文:
Can data change while export?
问题
让我先解释一下情景，
假设我们有一些用于销售目的的交易表格。根据用户在特定销售应用程序上的操作，数据可以被插入或更新。由于这些数据可能被用于财务目的，财务部门希望获得截止到中午12点之前创建或更新的数据。但我们不能停止程序。所以，数据也可以在12点之后更新。
现在，我有一个复杂的选择语句。它生成了大量数据。
现在，在我们生成数据然后开始将其导出为CSV时，花费了很多时间。超过了半个小时。
所以，我的问题是，在导出过程中，如果用户正在使用应用程序，因此数据正在被更新，这是否也会影响生成数据的导出功能？生成的数据在导出过程中是否会发生变化？由于SQL只在开始时生成可见数据，并根据分页更改加载其他数据。
如果是这样，如何控制这种行为？
英文:
Let me explain scenario first,
Let's say we've some transaction tables for sales purposes. And data can be inserted or updated as per user's actions on a particular sales application. Since the data can be used for financial purposes, finance department wants to have data which has been created or updated till 12 noon. But we cannot stop the program. So, data can be updated after 12noon as well.
Now, I've a complex select statement. Which generates lots of data.
Now, when we generate the data and then started to export it into CSV, it's taking lot of time. More than half hour.
So, my question is during export, if users are using application and thus data is getting updated, does that also affects the export functionality of generated data? Does the generated data can get changed during export? Since SQL only generates the visible data at first and loads another data as per pagination changes.
If so, how to control such behavior?
答案1
得分: 2
不，数据不会改变。当您在Oracle中运行查询时，它会获取那一刻的数据。
即使您的查询运行数小时，其他会话更改数据并提交这些更改，您的查询也不会察觉到这些更改。Oracle将使用其内部的变更历史，以确保您获取的数据是从您启动查询的时间戳开始的数据。
英文:
No, the data will not change. When you run a query in Oracle, it gets you the data of that very moment.
Even if your query runs for hours and other sessions change data and commit these changes, your query will be oblivious to these changes. Oracle will use its internal change history, to make sure you get the data as of the timestamp you started your query.
答案2
得分: 2
只要你的“分页”是一个仅执行一次的单个SQL语句，然后逐步提取，Oracle将通过应用从你的SQL开始之后发生的任何更改的撤消来构建一致性读取（CR）缓冲区（它在块的ITL中找到大于其自己事务开始SCN的SCN，然后读取撤消并应用撤消记录以重构块在你的事务开始SCN点上的外观），以便你获得与表格完全闲置时相同的结果。这是一件美妙的事情（尽管在某些情况下可能会导致严重的性能问题），也是Oracle的一个重要特性。
然而，如果你的“分页”是一系列SQL语句（使用可编程构建的WHERE子句来实现分页），不管出于什么原因，那么它们中的每一个都是CR保证的一致性，但在它们之间不是。如果这是你正在使用的方法，而其他人正在更新表格，你将获得不一致的数据。
因此，请确保你的提取是一个单一的原子执行。在执行之后进行多次提取是可以的，但多次执行则不可以。
英文:
As long as your "pagination" is a single SQL statement executed only once and then fetched progressively, oracle will construct consistent read (CR) buffers by applying undo from any changes that happen after your SQL starts (it finds SCNs in the block ITLs that are greater than its own transaction start SCN, then reads undo and applies undo records to reconstruct how the block looked at the point of your transaction's start SCN), so that you get the same results you would if the table were 100% idle. It's a beautiful thing (though it can cause some severe performance issues in some cases) and an important feature of Oracle.
However, if your "pagination" is a series of SQL statements (using the WHERE clause programmatically constructed to accomplish pagination) for any reason, then each of them is CR guaranteed consistent individually, but not between them. You will get inconsistent data if that's the method you're using and others are updating the table.
Therefore make sure your extract is a single atomic execution. Multiple fetches after an exec is okay, but multiple execs are not.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论