Dean Harding wrote:

> Sergio H. Gonzalez wrote:
>
>> Why FB does not uses both indices?
>>
>
> Because it can't. Indexes are not magic, and for me, the easiest way to
> think of an index is as a pre-sorted list of pointers to the actual rows
> (sorted by the columns of the index).
>
> Let's do a quick example. Say you've got two columns like this:
>
> Row# COLA COLB
> 1 A G
> 2 A B
> 3 B F
> 4 B A
> 5 C D
> 6 A C
> 7 B E
> 8 C H
>
> And a separate index on each, the indexes will be stored somewhat like this:
>
> IX_COLA A->1, A->4, A->6, B->3, B->4, B->7, C->5, C->8
> IX_COLB A->4, B->2, C->6, D->5, E->7, F->3, G->1, H->8
>
> So you can see the database can easily walk through IX_COLA in order, or
> it can walk through IX_COLB in order, but it can't walk through both in
> order.
>
> If you have "SELECT * FROM TBL WHERE COLB > B" it can walk the index on
> COLB to select out C, D, E, F, G and H values quickly.
>
> However, if you had "SELECT * FROM TBL WHERE COLA=A AND COLB > B", it
> can use the index on COLA to find the three "A"s and then it's got to
> look at the row data directly to see whether COLB > b.
>
> Hope that helps!
>
> Dean.
>

FB can use multiple indices for a single statement. FB use a two-phase
index scan, first it looks on each index and find what datapages has
potential visible records (potential because the index has no
transaction information, so the only way to know if a given record is
visible to the transaction asking for it is to go to the datapage and
look at the version) and mount a bitmap of that pages, if FB decides to
use a second index it will again look for potential datapages that has
records for that given condition, after all chosen indices is scanned
the results of the potential datapages that could have valid records for
that condition is ANDed (it's a bitmap of page numbers) and the result
bitmap will then be the intersection of the pages that has potential
records for each index used.

The same could be applied for OR condition too, the only difference is
that instead of do an AND the bitmap of pages FB will do an OR on it.

So, multiple indices could and will be used by FB when the optimizer
find it useful, this feature makes virtually not necessary the need of
composite index (of course there is exceptions to the rule).

Taking you example

select * from Table where COLA = 'A' and COLB = 'C'

FB could do something like:
Use index on COLA and find that potentially visible records for the
first criteria would be #1, #2, #6, then FB could use index on COLB and
find that potentially visible records for the second criteria would be
#6, if you intersect both sets (#1, #2, #6) AND (#6) you will get the
answer as (#6) so the only potentially visible record would be #6. FB
does it at the page level not the record level, but it's just an example
of how it works behind the scenes.


see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br