• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 1849
15
Eclipse IDE Spring VI Editor Java Linux Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic