R
Robert Morley
I've found materialized views to be nothing but a pain the ass in SQL...but
that, of course, is another issue not related to the issue at hand.
You're correct, though, that a materialized view would in fact perform as
you say. But when you get right down to it, that's simply denormalization
done by the RDBMS rather than the DBA...sometimes the DBA needs (or just
plain likes) to have more control over the process.
Then, of course,
there are the times when you want a certain degree of control over the
process (i.e., multiple tables published at different times saved as
backups, or a table that's guaranteed to only be updated at midnight every
day, etc.). Many of these things COULD be done as materialized views,
granted, but I think it would be more effort than it's worth in many cases,
or simply undesirable by the implementing DBA for a host of other reasons.
As for semijoins that get optimized out of existence, I wasn't thinking of
that, but of course you're right there as well. But then you need to take
into account the (admittedly comparatively minimal) time taken by the
optimization process itself. If you have no joins, that portion of the
optimization process is skipped, and thus there's absolutely no time taken
(well, at least for that portion of the process...I seem to remember that
SQL Server goes through huge optimization processes for any query that's
longer than a zero-length string [and maybe even then! <grin>]).
As always, I think it really comes down to what the DBA most wants to do
given the situation at-hand.
Rob
that, of course, is another issue not related to the issue at hand.
You're correct, though, that a materialized view would in fact perform as
you say. But when you get right down to it, that's simply denormalization
done by the RDBMS rather than the DBA...sometimes the DBA needs (or just
plain likes) to have more control over the process.
Then, of course,there are the times when you want a certain degree of control over the
process (i.e., multiple tables published at different times saved as
backups, or a table that's guaranteed to only be updated at midnight every
day, etc.). Many of these things COULD be done as materialized views,
granted, but I think it would be more effort than it's worth in many cases,
or simply undesirable by the implementing DBA for a host of other reasons.
As for semijoins that get optimized out of existence, I wasn't thinking of
that, but of course you're right there as well. But then you need to take
into account the (admittedly comparatively minimal) time taken by the
optimization process itself. If you have no joins, that portion of the
optimization process is skipped, and thus there's absolutely no time taken
(well, at least for that portion of the process...I seem to remember that
SQL Server goes through huge optimization processes for any query that's
longer than a zero-length string [and maybe even then! <grin>]).
As always, I think it really comes down to what the DBA most wants to do
given the situation at-hand.
Rob