It's not a secret anymore!*
The moose likes Oracle/OAS and the fly likes Oracle - Will the output of a Select query depend on how long the Query took for execution? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Oracle - Will the output of a Select query depend on how long the Query took for execution?" Watch "Oracle - Will the output of a Select query depend on how long the Query took for execution?" New topic
Author

Oracle - Will the output of a Select query depend on how long the Query took for execution?

Joseph Sajee
Greenhorn

Joined: Mar 09, 2010
Posts: 1
Hi,

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.

Regards,
Sajee
Janeice DelVecchio
Saloon Keeper

Joined: Sep 14, 2009
Posts: 1660
    
  11

Welcome to JavaRanch

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.

"Read committed"
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
(uncommitted) data.

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
phantoms.

"Serializable"
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
phantoms.

"Read-only"
Read-only transactions see only those changes that were committed at
the time the transaction began and do not allow INSERT, UPDATE, and
DELETE statements.


When you do things right, people won't be sure you've done anything at all.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle - Will the output of a Select query depend on how long the Query took for execution?