On Tue, Mar 15, 2016 at 3:15 AM, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] <firebird-support@yahoogroups.com> wrote:
> > > > 1. > Ann you say that index have dbkey bat have it also transaction number or > it must access table data for that info > 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. 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. > > 2. > Is this considered to change index structure for composite indexes? > I should let Dmitry answer this, but my guess is no. > Or there is no plan for it in near feature. > This have low priority i know - but i see that you consider create R-TREE > indexes - maybe this can be joined together ;-) > I know that this looks like separate tasks but.. > It really is a different task, and one that I wouldn't suggest Firebird undertake since there's a workaround for applications that need ranges on multiple values - define another index. Firebird indexes are dense and don't require user maintenance other than an occasional resetting of selectivity. . Cheers, Ann
