Subject Re: [firebird-support] firebird computed field
Author unordained
---------- Original Message -----------
From: Wewe <sullen_00@...>
> > select * from y
> > select value1+value2 as total from y
> Which is better performance and speed? table x or table y ?
------- End of Original Message -------

I've never noticed a difference in performance at select time. I think the main
reason for picking computed-by columns is that the same formula gets used
everywhere, is accessible even inside triggers, and can be changed in a single
place, leading to fewer coding mistakes.
I *think* you can grant column-level select privileges such that the underlying
(value1, value2) fields are hidden, but the computation (total) is not, depending
on the user. That could be useful for last-four-digits, etc. situations.
Using computed-by columns is easier than creating a separate view, for which you
have to manage permissions and possibly triggers (to make it updateable, depending
on the exact view definition) -- that's the solution used in Oracle. While I was
an Oracle dev, I missed being able to just add computed columns.

I would suggest that it also makes it really easy to use computed-by indices, but
CORE-1212 and CORE-1173 are still open. You can however create a separate index on
the computation (repeat the expression), and FB should use the index when you
filter by the computed-by column, as if you had typed it out. At least you still
have the advantage that by using the computed-by column, if its expression exactly
matches that of the index you create, anyone using the table and filtering/sorting
by that computed column should get the expected indexing automatically; if done by
hand, there's always a chance someone will write the expression out differently in
their SQL and the optimizer won't see the index as being useful (on top of them
possibly writing it out wrong.)