-- Original Message ---
From: Wewe sullen...@yahoo.com
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.)
-Philip
