Re: Is mysql a RDBMS ?

"Heikki Tuuri" <Heikki.Tuuri_at_innodb.com> wrote in message news:tVE2b.88$4X.37_at_read3.inet.fi...
> Bob,
>
> "Bob Badour" <bbadour_at_golden.net> kirjoitti viestissä
> news:5Az2b.699$gN.82067528_at_mantis.golden.net...
> > "Heikki Tuuri" <Heikki.Tuuri_at_innodb.com> wrote in message
> > news:Rpx2b.4189$G37.2895_at_read3.inet.fi...
> ...
> > > > Just off the top of my head, I can think of the following identities
> > that
> > > > improve performance for relations but not necessarily for multisets:
> > > >
> > > > COUNT(A UNION B) = COUNT(A) + COUNT(B) - COUNT(A JOIN B)
> > > >
> > > > A WHERE EXISTS ( B WHERE P(A,B) AND Q(B) )
> > > > = ( A JOIN ( B' WHERE P(A,B') AND Q(B') )) PROJECT { ATTRIB(A) }
> > > > (B' indicates the dbms may have to rename some attributes of B)
> > > >
> > > > ( A(w,u) JOIN B(w,v) ) PROJECT { w }
> > > > = ( A(w,u) PROJECT { w } ) JOIN ( B(w,v) PROJECT { w } )
> > > >
> > > > ( A UNION A ) = A
> > > >
> > > > ( A JOIN A ) = A
> > > >
> > > > ( A INTERSECT A ) = A
> ...
> > Replacing a union with an intersection can dramatically improve
> performance
> > if the dbms can perform an index merge.
>
> what rule is that? Are you referring to the COUNT() rule above? But, for
> multisets,
>
> COUNT(A UNION ALL B) = COUNT(A) + COUNT(B);
>
> That rule is not available if duplicates are not allowed.

First, UNION ALL is a different operation from UNION. The fact that SQL can use a different operation to answer the wrong question doesn't help much.

Second, a user who wants to know the total cardinality of two relations can do so. Because the relational algebra is less redundant than SQL, the user has fewer options for expressing the query and would probably ask for COUNT(A) + COUNT(B) in the first place.

Third, you overlook another important optimization: P(A)*P(B)=0 -> (A INTERSECT B)={} -> COUNT(A INTERSECT B)=0 Thus, if one expresses the logical equivalent of UNION ALL using relations, one generally has the same optimization available in any case because the logical equivalent of UNION ALL involves forcing mutually exclusive predicates.

In this light, UNION ALL is nothing but syntactic sugar to explicitly confuse the logical and physical levels of discourse by instructing the DBMS to include physical location in the predicates of the arguments to the UNION operation.

> > > The example is not a real-world query.
> >
> > What's not real about it? The fact that it is a self-join? That happens
> all
>
> The fact that it is not from some real application.

Self-join is not from some real application?!? Don't be stupid. I use self-joins all the time, and so does everybody else. Of course, no real application I write will allow duplicate rows, because I am neither stupid enough nor ignorant enough to allow duplicates in the first place. But you argue for duplicates, so you must accept them as real-world in your world.

Since self-join comes from real applications and you argue for the value of duplicates, I fail to see how the query is not a real-world query in your universe of useful queries.

> > the time--especially when joining two views that each join the same base
> > table. Or the fact that it has duplicate rows? I think duplicates are a
> dumb
> > idea in the first place; you are the one arguing for their use.
> >
> > SQL must either perform work to generate 9 rows from 3 rows or it must
> > perform work to generate 1 row from 3 rows. A relational dbms does not
> have
> > to do any of this work. Duplicates impede optimization.
>
> Can you show a real-world query where this is true?

See above. Any self-join involving any table with duplicates. If you argue for the value of duplicates, you must allow them as real world data. Self-join is absolutely a real-world query.

> A weakness of 'mathematical-relationists'
> is that they fail to provide hard evidence for the claim.

A weakness of vendors is an absolute refusal to accept hard evidence when provided. Your denial doesn't change the fact that I have already provided hard evidence for my point. It only demonstrates your intellectual dishonesty. Received on Tue Aug 26 2003 - 16:15:24 CEST