Re: vacuum, performance, and MVCC

On Jun 22, 2006, at 12:56 PM, Greg Stark wrote:
> Just for the record, if i understood correctly -- this was all a
> bit black
> magicky -- Oracle found the data in the rollback segment by storing
> a pointer
> to it in the block header where the updated data is. Ie, it could jump
> straight to the right place. Of course the record could have been
> updated
> multiple times which would necessitate following a linked list of
> rollback
> segment entries. And those are all random access reads though you
> could hope
> it was mostly cached.

Correct. Mark is basically suggesting we do the same thing in the
heap so that updates that don't touch any indexed fields could just
update the indexes to point them at the new row version.

Question: do we currently create new index entries even if the index
key hasn't changed? If so, what's the purpose of storing the CTID of
the next version in the old version of the row?

> There are other solutions too. I never used DB2 but I was led to
> believe they
> used their transaction log to retrieve old versions of the records.
> Someone
> else here claimed DB2 didn't implement MVCC at all so perhaps
> that's wrong
> though.

DB2 switched to MVCC in version 8. MSSQL switched in MSSQL2005. At
this point, I don't know of any major databases that don't use some
form of MVCC (err, maybe Sybase?)
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461