Subject Re: [firebird-support] Re: Composite index - issue or not existing feature?
Author Ann Harrison
On Mon, Apr 11, 2016 at 2:26 PM, liviuslivius@... [firebird-support] <> wrote:
>>No, the index entry does not include transaction information.  So yes, if
>>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. 

Even if you have a record with data that entirely in the index, like a pair of 
primary key field to other table, Firebird has to check record after it identifies
it in the index because that record may not be visible to your transaction.  It
may have been inserted by a concurrent transaction, deleted by a committed
transaction, or modified ... either way.  To the values you're looking for by a
concurrent transaction or from those values by a committed 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
>>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


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).

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,