Re: Extending my question. Was: The relational model and relational algebra - why did SQL become the industry standard?
Date: 3 Mar 2003 12:00:02 +0100
Lauri Pietarinen wrote:
>Jan Hidders wrote:
>>>And, regarding "Distinct" support in DBMS's, you suggested that bad support
>>>resulted from the fact that the keyword was not used.
>>When did I say that?
>[snipped from previous posting...]
>>- optimizing efforts will be concentrated on the most used features, that
>> being queries without disticnt.
>If those queries are the ones that are the most used, then that is what the
>users apparently want, and so indeed those should be optimized the most.
Er, yes, so you see that *you* suggested it, and I merely said that if that was the case then IMO this wouldn't be a big problem.
>I will offer one last example to try to clarify what I mean.
Promises, promises. :-)
>Take the standard S, SP and P -tables. I want to hide the joins from
>the user so I create a view:
>CREATE VIEW V_S_SP_P
> ( S#, SNAME, QTY, P#, PNAME)
>SELECT S.S#, S.SNAME, SP.QTY, P.P#, P.PNAME
> FROM S, SP, P
> WHERE S.S# = SP.S# AND
> SP.P# = P.P#;
>There are lots of interesting questions that can be
>easily answered using this view. [...]
>Ok, let's try
>SELECT DISTINCT P#, PNAME
> FROM V_S_SP_P;
>Correct answer, but why is performance
>Oh, the DBMS materialises the whole join
>(even reading S, which is not needed at all)
>and then groups by P# and PNAME!
>(Just tried it on SQLServer2000).
Excellent example. No, really. What happens if you add DISTINCT to the view definition?
>So while you might be right in theory, i.e. that supporting two modes at
>once (sets and bags), is no burden for the DBMS optimiser, you are
>certainly wrong in practice! If it is so easy, why did they (the
>SQL-vendors) not support it a long time ago?
Well, your specific example involves recognizing that duplicate elimination
is not necessary in the view. That was something that was researched as
(relatively) recently as 1994:
>would claim that the SQL-mode of thinking is hindering us from achieving
>this. And this (in my view) is ultimately Date's criticism of Hector
>Garcia-Molina, Jeffrey D. Ullman, and Jennifer Widom, DATABASE SYSTEM
Is he really criticizing this book? I don't get that impression from the "double trouble" article on dbdebunk.
- Jan Hidders