Re: Isolation levels in Informix vs Oracle

"PK" <pk26au@yahoo.com> wrote in message news:667bf0c6.0412150133.7e4ce123@posting.google.com... > Hi, > > As you know Informix offers 'set isolation to dirty read' - a facility > to read dirty buffers. I believe DB2 UDB too offers it but not Oracle. > > What is the neccessity or justification for an RDBMS to offer such a > feature and do applications really need it ? I was told by an Oracle > guy that Informix and DB2 are 'forced' to offer this because of their > architecture and this is not a 'feature' as such ! He says Oracle can > offer it in a 'jiffy' by pointing to its undo tablespace (where before > images of a buffer are kept before modifications) but they will not as > this is not justified ! > > I myself (worked with Informix for 9 yrs but now into Oracle for past > 1 yr ) feel its quite cool. However I would like to get expert > technical opinion. I don't intend to start a flame at all ! > > Many thanks > Prashant Oracle could duplicate dirty read by showing information from the before image buffer????? Informix dirty read returns information based on a presumptive commit of open transactions, not information at a point in time preceding open transactions. I'm sure the Oracle guy your talking to knows Oracle, but he probably doesn't know Informix terms and functionality. I don't know what read options Oracle offers, but I would certainly rephrase the question(without using Informix read option terminology) when asking an Oracle expert. As for neccessity or justification...I would agree with Obnoxio's example. Dirty read is a good option any time the user understands they are looking at a moving target, but wants to see the most recent information possible. Given that over 99.99% of my transactions are committed and not rolled back, I like seeing the open transactions included instead of excluded. One of the things I use it for is to check the progress of batch jobs. If I know a program is going to insert 100,000 rows into a table, I can use dirty read to see how many rows it has inserted so far and can give a good estimate of when it will finish. If you are only allowed to see committed data, you wouldn't have that monitoring option. As for not wanting to start a flame war...Your posting on an Informix message board and your using exclamations after every Oracle viewpoint mentioned...seems like your looking for a flame war to me. Dave Griffen

Dave Griffen wrote: > One of > the things I use it for is to check the progress of batch jobs. If I know a > program is going to insert 100,000 rows into a table, I can use dirty read > to see how many rows it has inserted so far and can give a good estimate of > when it will finish. If you are only allowed to see committed data, you > wouldn't have that monitoring option. In Oracle one would use the DBMS_APPLICATION_INFO built-in package as it not only tells you what percentage of a batch is done it uses the transaction rate to an estimate of the completion time. The information is available via OEM and by querying v$session_longops. Each product has its way, or workaround, for accomplishing just about any required task. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond) -----------== Posted via Newsfeed.Com - Uncensored Usenet News ==---------- http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----