On 09/10/2013 13:35, [email protected] 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
