Oracle - Will the output of a Select query depend on how long the Query took for execution?
Joined: Mar 09, 2010
I have a very basic doubt in Oracle.
Will the output of a Select query depend on how long the Query took for execution?
For example suppose i issue a query 'Select * from Employees' at 10.00 am. And suppose my query takes 20 mins to return with the results. And suppose meanwhile at 10.05 am new data gets commited to Employees table. In this case will/can my select query issued at 10.00 am return the new data.
My feeling is that it will not. Oracle would use some sort of ROW_SCN to determine what rows to return and what not. Thus my feeling is that the output of the Select query would be independent of the Query execution duration. It wont even depend on the IsolationLevels (ReadCommited, ReadUncommited etc). I.e if IsolationLevel is ReadUncommited, then the Select Query would return even Uncommited data as of 10.00 am (but no later).
Please let me know if i am right. If there are any good articles on this please send me the links.
The default operation is that you can only read data that was committed before your read began.
I got this a while back from bytes.com.... looks like their source was a book called Oracle 9i Concepts:
Oracle provides these transaction isolation levels.
This is the default transaction isolation level. Each query executed
by a transaction sees only data that was committed before the query
(not the transaction) began. An Oracle query never reads dirty
Because Oracle does not prevent other transactions from modifying the
data read by a query, that data can be changed by other transactions
between two executions of the query. Thus, a transaction that executes
a given query twice can experience both nonrepeatable read and
Serializable transactions see only those changes that were committed
at the time the transaction began, plus those changes made by the
transaction itself through INSERT, UPDATE, and DELETE statements.
Serializable transactions do not experience nonrepeatable reads or
Read-only transactions see only those changes that were committed at
the time the transaction began and do not allow INSERT, UPDATE, and
When you do things right, people won't be sure you've done anything at all.