Re: My database is too fast

"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 ;-) 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.

>
> > the joins in real time (maybe it was just that we didn't do it the right
> > way, but we had to go back to normal joins).
>
> I'd say that was the case.

Probably, but then I must admit that I don't see what's the purpose of MV's in Oracle...

>
> > That's actually the point with
> > SQL Server, it's easy, it's clear, it's powerful enough and everything
> > behaves as expected...
>
> Attaboy! You tell them! ;) BTW, this means I
> agree with you. So don't come back here calling
> me a zealot, ta?

Not at all, don't worry, I appreciate just exchanging ideas ;-) And as I said, currently I'm working with Oracle and once I got used to its philosophy I'm quite happy with it, and I have to admit some features are far better in Oracle and others just don't exist in SQL Server... I'm not a zealot either, just don't like seeing that kind of jokes (often based in wrong concepts) about SQLS and Microsoft...

>
> <soap box>
> He's got a point here, folks. It's getting too
> complex. Oracle cannot expect all their users
> to be Steve's class. Most aren't and do NOT
> want to be: they just want the darn job done.
> Has anyone looked at the OEM performance monitor
> of late? I know a bit about Oracle and I got confused
> with all the crap in there... BIG overkill. Same
> applies to all the "managers" in it. It's getting
> way out of control. No one can expect normal users
> to go for that. OCP or no OCP.
> </soap box>
>
> > any experience with it... Why everything has to be faster in Oracle just
for
> > being Oracle??? You sound like a blind Oracle talliban shouting "Oracle
has
> > to be the best because Allah told us one century ago... Death to the
> > unbelieving!!!" ;-)
>
> Allah is over-rated. Larry said so. ;)
>
> > Not the case as I see. But just for the sake of information, I'll tell
you
> > it was over 400 millions rows... Now go and make a complex query against
> > that MV and tell me if 2 seconds (and in these 2 seconds you have to
include
> > network and asp overhead) is slow...
>
> It doesn't add up. A MV on a 400 million row table (isn't that
> past the max of SQLS?) is NOT necessarily a 400 million row table.

Nope, the MV itself was 400 million rows... Hope the explanation I did before get to clarify this once for all... ;-)

Best regards

Francesc Gil Received on Tue Jul 01 2003 - 04:54:54 CDT