Subject Re: Odp: [firebird-support] Computed index
Author Tim Ward
On 09/10/2013 13:35, liviuslivius@... wrote:
 

In your statement result type is boolean and is not supported before fb 3.0

Thanks. I didn't notice that documented anywhere.

So, I tried

    IIF( (MYFIELD in (1,3,4,5,7,8,9,11,12,13)), 0, 1 )

for the index and

    IIF( (MYFIELD in (1,3,4,5,7,8,9,11,12,13)), 0, 1 ) = 0

in the WHERE clause ...

... and it didn't use the index (perhaps because the index has binary selectivity, which is because it's specifically designed for this query!), and on trying to force it with PLAN I get only part of the expected performance improvement: "fetches" does indeed go down by a factor of 10 which is about what I expected, but wall clock time doesn't change as much as I hoped.

Still, knowing to surround "in" with "iif" in a computed index may be useful to someone else.
-- 
Tim Ward