Re: My database is too fast

"Teppicamon" <quicog_at_hotmail.com> wrote in message news:bdrlpi$10h6kd$1_at_ID-162849.news.dfncis.de...
> "Nuno Souto" <wizofoz2k_at_yahoo.com.au> wrote in message
> news:73e20c6c.0306301723.2fdb01af_at_posting.google.com...
> > "Teppicamon" <quicog_at_hotmail.com> wrote in message
> news:<bdpnrv$v0qdd$1_at_ID-162849.news.dfncis.de>...
> >
> > > Not at all, we just used MV because they gave us better performance
and
> let
> > > us index everything exactly the way we wanted.
> >
> > What's a MV got to do with indexing of base tables?
> > I'm lost here.
>
> 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 ;-)

ah the old, 'can you give me the sales for the northwest made to one legged egyptians as compared to the average temperature in Tunisa for the last 84 years' problem :(. ad-hoc queries tend to cause this sort of thing

> 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.

That sounds exactly like the sort of thing that MVs are used for in the Oracle world as well, with the exception that Oracle can decide wether to rewrite the query so that it uses the MV (or summary table) or not at run time (i.e you don't need , in principle, to change your code). I don't believe that MSSQL can do this rewrite bit but I'd be glad to be proven incorrect.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK