|Subject||Re: [firebird-support] Re: Composite index - issue or not existing feature?|
>>No, the index entry does not include transaction information. So yes, ifHi Ann and others,
>>you have a database that includes something
>>m to m relationship, the junctions records have to be read to validate
>>them for the current transaction. 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
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
because it contain dbKey. And if i update record then we got new record
version with new dbKey (once for transaction).
If this is true then i see that only benefit of curent index is size of
whole index because it not contain transaction id
but I/O cost is not reduced.
I missed something?