Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Unanswered: how to select data while updation is in progress

    I have a huge table, which is taking 3 hrs to update all records. In the mean time i want to select the data from the same table. Is there any possible way to do this. Cany anyone help me in finding a solution.
    Thanks & Regards
    meeshareef

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    What "problem" are you trying to solve? In Oracle, writers do not block readers, so it is possible to select data from updated rows. Additionally, the resultset will be in consistent state from the time when SELECT was called (if it takes long time, it does not reflect updates which happened during its run). Keep in mind, that any UPDATE results are visible to other sessions after its transaction commit.

    You may find more information in Oracle Concepts. It is part of the documentation, found e.g. online on http://tahiti.oracle.com/.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I couldn't agree more, *unless* the one who wrote the UPDATE script got the idea of "committing after every 1000 records are being updated". If so, well, it was most probably a bad idea and promises unexpected errors sooner or later (such as ORA-01555, for example).

    Otherwise, I second Flyboy's statement entirely.

  4. #4
    Join Date
    Nov 2008
    Posts
    2
    Thank you both for the quick reply. But, i would like to find out the impact of updates on select statement. If the table locks are enable during updates, how the data effected with the select query. Please give me some more inputs.
    Thanks

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by meeshareef
    If the table locks are enable during updates, how the data effected with the select query. Please give me some more inputs.
    Thanks
    Unless the person who wrote the UPDATE statement explicitely requested an exclusive table lock, there won't be any locks that will prevent the SELECT from executing.

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shammat
    Unless the person who wrote the UPDATE statement explicitely requested an exclusive table lock, there won't be any locks that will prevent the SELECT from executing.
    Not true. Even exclusive table lock does not prevent from selecting table data, as may be easily checked.

    Session 1:
    Code:
    SQL> lock table tt1 in exclusive mode;
    
    Table(s) Locked.
    Session 2:
    Code:
    SQL> select * from tt1;
    
    C1
    --------------------
    11/19/2008
    01/01/2009
    12/31/2007
    This feature is deeply described in Oracle Concepts book, Part III Oracle Database Features, Chapter 13 Data Concurrency and Consistency; even with the mechanisms for achieving the resultset consistency. As it is quite large and easily available on the page I posted, I do not see any reason to copy it here.

  7. #7
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by flyboy
    Not true. Even exclusive table lock does not prevent from selecting table data
    Right, I always forget that

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •