Subject | Re: Odp: [firebird-support] Computed index |
---|---|
Author | Tim Ward |
Post date | 2013-10-09T13:01:17Z |
On 09/10/2013 13:35,
liviuslivius@... wrote:
Thanks. I didn't notice that documented anywhere.In your statement result type is boolean and is not supported before fb 3.0
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