M to m
>>relationships are typically something like: students, registrations, and
>>courses.  Each student registers for several
>>courses and each course has many students and the registration record
>>consists only of a student id and a course
>>number (it's a junction record),  Firebird has to read the registration
>>records.  This was done  knowingly because
>>keeping transaction information would greatly increase the size of index
>>entries - one transaction id for the transaction
>>that created the record version with the value sought, plus one for the
>>transaction that changed the value.  Obviously
>>that also increases the amount of I/O because modifying a key value
>>modifies two index entries.  A fully mature
>>record (only one version) could skip both transaction ids, at the cost of
>>even more I/O.

>>At one time, Firebird kept only one index entry for each value of a key
>>for a record so if you typically modified a value
>>between A and B, then back to A, then back to B, you'd only have two index
>>entries for that record.   That optimization
>>makes index garbage collection trickier and I think it was abandoned.  If
>>it wasn't, obviously you'd need to have
>>separate index entries for each instance of a value in a record version
>>chain to keep track of transaction ids.

>>It would be possible, I guess, to add a second index type that does keep
>>transaction identifications so a designer
>>would be able to optimize indexes for junction records.  That's easy for
>>me to say because I don't do that kind of
>>work anymore.


Hi Ann and others,

i read this once again and i see that not all is clear to me.
If i modify record without modifing fields with indexes - then i suppose
index entry also must be modified

No.   

because it contain dbKey.

Ah!  The DBKey for a given record is constant.  It has three parts.  The

first two identify a page in the database, going through a table of pages 

of page pointers, then a pointer page.  The third part of the DBKey identifies

an entry in the page index - an array of offset length pairs stored on the

page that indicate the start and size of records on the page.

When a record is stored it gets a DBKey.  That's the DBKey for the

primary version of the record.  As records are modified, the primary

record version is always the most recent and points backward to

older versions.

 

When the record is modified,  Firebird checks for space on the same page.   

If there's enough space Firebird stores the new version on the page,creating

a new DBKey for the old version, which is stored as as a back pointer in the 

new record version.  It sets the original DBKey to point to the new version.  

If there isn't space on the original page for both record versions there's a 

more complicated dance, but the result in the end is that the new version 

has the old DBKey and points backward to the old version.   

Index entries for the old and new versions of a record all contain the DBKey

of the primary (newest) record version.  When doing an indexed lookup,

Firebird walks backward through the record versions until it finds one that

the current transaction can see, then check whether the key values in that

record version match the lookup values.

And if i update record then we got new record
version with new dbKey (once for transaction).

Nope.

 

If this is true then i see that only benefit of current index is size of
whole index because it not contain transaction id
but I/O cost is not reduced.

If all versions of a record have the same values for all key fields, the

indexes are not touched during modifications.  

Good luck,

Ann