Re: Snapshot too old, but I don't care about read consistency for this query

>In article <87hfn86zcf.fsf_at_mit.edu>, Greg Stark <gsstark_at_mit.edu> wrote:
>
>I have a long-running "select *" query--it takes hours to process all the
>records--and sometimes I get "snapshot too old" for some of the rows. Is there
>any way to convince Oracle that I really don't care about consistent reads
>here, just fetch the most recent data from that record? This is especially
>important because I'm considering enabling discrete transactions for the
>updates anyways, so these errors will become much more frequent.

 and Paul de Anguera <7mh6c0$dl6$7_at_news.chatlink.com> replied ...

>There should be, or EXPORT wouldn't have a CONSISTENT parameter.
>But I don't know how you would allow a dirty read.

Paul is confused about the meaning of CONSISTENT as an export parameter: It has nothing to do with consistent reads. What the export parameter means is make each of the exported tables consistent with respect to each other. Each individual table is always exported consistently, but without this parameter, if a transaction which updates tables A and B is started whilst table A is being exported and completes before table B is exported, the exported table A will not show the update (as it will be consistent relative to the point when table A started being exported) but table B will show the update, as it was complete when table B started to be exported.

Answering Greg's question, you can't switch off the consistent read mechanism, but you can rewrite your transaction process the rows in batches and commit after every batch, but you'll have to make sure you close your reading cursor after every commit to make this work and to avoid fetch across commits.

Dave.
--
Remove "nospam" from my address to reply by email Received on Wed Jul 14 1999 - 01:38:51 CDT