Wolfgang Rohdewald wrote:
Reading the mailing list I just learned that this is possible. Experimenting shows that (using FB 2.5) this does have limitations. FB lets me define such indices using computed fields or even select() statements like:
create index idx on buchung computed by (valuta);
where valuta is a computed field and "show index" does indeed show those indices as existing but they are never used. If instead I replace "valuta" by its computation like in
create index idx2 on buchung computed by (case when condition
then datum else datum+1 end)
this index IS used.
So it seems
- FB 2.5 does not use a computed index if its expression uses computed fields or select() statements and probably in more cases
- FB 2.5 has a bug: It should not accept creation of unusable indices
I absolutely understand that index expressions using computed fields or select() statements are not possible in the general case and very difficult to implement for most practical cases (when FB could theoretically analyze dependencies of the expression).
Is that correct so far? Then another bug would be that http://www.firebirdsql.org/refdocs/langrefupd20-create-index.html does not mention those restrictions.
Dmitry Yemanov answers:
Such complex index expressions are supported but you're correct that they're practically useless. FB just provides you with a tool but it's up to you to ensure that the tool is used properly.