Re: [firebird-support] Re: Composite index - issue or not existing feature?

Hi Ann, This is the crux of the matter “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,

Re: [firebird-support] Re: Composite index - issue or not existing feature?

>>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,

Re: [firebird-support] Re: Composite index - issue or not existing feature?

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

Re: Re: [firebird-support] Re: Composite index - issue or not existing feature?

Thanks you Ann and Dmitry for your detailed answer - now i know exactly why this happened. I see that better is use separate indexes then composite in Firebird for range scans.   2 more questions :)   1. Ann you say that index have dbkey bat have it also transaction number or it must access

Re: [firebird-support] Re: Composite index - issue or not existing feature?

On Mon, Mar 14, 2016 at 3:41 AM, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: > W dniu 2016-03-14 08:36:40 użytkownik Dmitry Yemanov > dim...@users.sourceforge.net [firebird-support] < > firebird-support@yahoogroups.com> napisał: > > > >

[firebird-support] Re: Composite index - issue or not existing feature?

14.03.2016 10:41, liviuslivius wrote: > Why? > Index is a Tree? And if i found VALUE 2 in A key then i can fast find > value 5 in sub key (leaf) > You scan throught keys in A, and then in finded nodes you look for leafs > in B Compound index key is a single concatenated value, not two separate

Re: [firebird-support] Re: Composite index - issue or not existing feature?

    W dniu 2016-03-14 08:36:40 użytkownik Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] napisał:   14.03.2016 10:32, liviuslivius wrote: > > simple table > CREATE TABLE XXX( > A INTEGER > , B INTEGER > , CONSTRAINT UK_XXX__A__B UNIQUE(A, B) > )

[firebird-support] Re: Composite index - issue or not existing feature?

14.03.2016 10:32, liviuslivius wrote: > > simple table > CREATE TABLE XXX( > A INTEGER > , B INTEGER > , CONSTRAINT UK_XXX__A__B UNIQUE(A, B) > ) > commit; > SELECT * FROM dbo.XXX X WHERE X.A BETWEEN 2 AND 30 *AND* X.B BETWEEN 5 > AND 60 > Select Expression > -> Filter > -> Table