firebird-devel

[Firebird-devel] "Covering" index?

[Thomas S. <ts...@ib...>]

Hello,

SQL Server supports something called covering index, which basically 
means, that additional columns can be added to the index definiton, 
which aren't in the index key then, but at index lead level only.

I know Firebird doesn't support something like that, but I wonder if 
Firebird can serve result fields directly out of an index without going 
to the underlaying table data?

For example (name is an indexed field):

select name from t1 where name like 'Hugo%'


Is Firebird able here to serve the result here out of the index or does 
it need to lookup data by following the index key to the row data?


Thanks,
Thomas

Re: [Firebird-devel] "Covering" index?

[Dimitry S. <sd...@ib...>]

27.01.2013 21:17, Thomas Steinmaurer wrote:
> Is Firebird able here to serve the result here out of the index or does
> it need to lookup data by following the index key to the row data?

   There is no data in index. And no information about transaction created an index entry. 
So, yes, Firebird has to look at real data.

-- 
   WBR, SD.

Re: [Firebird-devel] "Covering" index?

[Ann H. <an...@qb...>]

On Sun, Jan 27, 2013 at 3:23 PM, Dimitry Sibiryakov <sd...@ib...>wrote:

> 27.01.2013 21:17, Thomas Steinmaurer wrote:
> > Is Firebird able here to serve the result here out of the index or does
> > it need to lookup data by following the index key to the row data?
>
>    There is no data in index. And no information about transaction created
> an index entry.
> So, yes, Firebird has to look at real data.
>
>
Just to amplify Dimitry's answer, without transaction information, there's
no way to tell whether an index entry points to a record version that's
appropriate to your transaction.  So even if you find an index entry that
exactly matches the data you want, you still have to read the record.

Cheers,

Ann

Re: [Firebird-devel] "Covering" index?

[Thomas S. <ts...@ib...>]

> On Sun, Jan 27, 2013 at 3:23 PM, Dimitry Sibiryakov <sd...@ib...
> <mailto:sd...@ib...>> wrote:
>
>     27.01.2013 21:17, Thomas Steinmaurer wrote:
>      > Is Firebird able here to serve the result here out of the index
>     or does
>      > it need to lookup data by following the index key to the row data?
>
>         There is no data in index. And no information about transaction
>     created an index entry.
>     So, yes, Firebird has to look at real data.
>
>
> Just to amplify Dimitry's answer, without transaction information,
> there's no way to tell whether an index entry points to a record version
> that's appropriate to your transaction.  So even if you find an index
> entry that exactly matches the data you want, you still have to read the
> record.

Makes sense. How does the engine know when to garbage collect an index 
entry?


Thanks,
Thomas

Re: [Firebird-devel] "Covering" index?

[Dimitry S. <sd...@ib...>]

28.01.2013 17:27, Thomas Steinmaurer wrote:
> How does the engine know when to garbage collect an index
> entry?

   When list of record references in it is empty.

-- 
   WBR, SD.

Re: [Firebird-devel] "Covering" index?

[Thomas S. <ts...@ib...>]

> 28.01.2013 17:27, Thomas Steinmaurer wrote:
>> How does the engine know when to garbage collect an index
>> entry?
>
>     When list of record references in it is empty.

Thanks.

Regards,
Thomas

Re: [Firebird-devel] "Covering" index?

[Ann H. <an...@qb...>]

On Mon, Jan 28, 2013 at 11:42 AM, Dimitry Sibiryakov <sd...@ib...>wrote:

> 28.01.2013 17:27, Thomas Steinmaurer wrote:
> > How does the engine know when to garbage collect an index
> > entry?
>
>    When list of record references in it is empty.
>
> -

Expanding again, when Firebird garbage collects a record version, it checks
keys for all the indexes on the record, building a list of "staying and
going"  values  - values that are, or are not, duplicated in other versions
of the same record.  It then removes the index entries for the "going"
values.

Cheers,

Ann