"Teppicamon" <quicog_at_hotmail.com> wrote in message news:<bdrlpi$10h6kd$1_at_ID-162849.news.dfncis.de>...
>
> Uhm... Maybe I'm not explaining quite well about our use of the MV's in our
> app... OK, to try to let you see the point: let's say you have a table of
> authors, related to a table of books, related to a table of paragraphs,
> related to a damn huge table of words through another table containing the
> position of each word in each paragraph (that's the one over 50 million
> rows) And suppose you must execute queries like 'show me the times that some
> author used this word in books written between 1800 and 1815, sorted by the
> second word to the left of that one"... and obviously for each appearance of
> that word they want to know the author, the book, and the reconstructed line
> where it appears... Try to make that join and then filter in real time, or
> better do not ;-) So, our MV is a smart (IMHO) and dynamic join of all those
> tables, without filtering. Once we have the MV, we can index it the way we
> need (maybe even using fields from different tables in one index) and make
> the queries faster. That is, we use the MV as if it was a huge table with
> all the info we need so then we can make the right queries in the right
> moment, saving the RDBMS the effort of making the join everytime... It's not
> just a predefined filter as I guess you're thinking, but a way of having the
> join pre-built before we apply the filter.
>
There are some confusion here. I am not a SQL Server expert. But I have seen
indexed views, or you may call it MV. Unless you are referring to something
completely different, these are not merely logical views. The moment you create
an index on it, the results from the view are physicalized and stored at the
leaf level of the index. When you query the indexed view, you are not doing
any dynamic joins or aggregations. The indexed views are indeed just pre-built
tables. They are very similar to Oracle's materialized views. The obvious
difference is that an indexed view in SQL Server requires at least a clustered,
index. By contrast, Oracle's MV doesn't have to have any indexes and you need
create indexes wherever appropriate. That could be part of the reason you were
not getting improved performance from Oracle.
Received on Wed Jul 02 2003 - 17:26:43 CDT
