On Tue, Mar 15, 2016 at 3:15 AM, liviuslivius liviuslivius@... [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