Re: VIEWS compared to Nodes as Windows into data

"Laconic2" <laconic2_at_comcast.net> wrote in message news:_ICdnTYUYccZEwvd4p2dnA_at_comcast.com...
> I cringe at the phrase "implementation of SQL". To me, SQL has been an
> interface language, nothing more, ever since I heard of it. I don't
think
> of Oracle or DB2 as "implementing SQL". I think of SQL as "interfacing
to"
> those databases. Having said that, I think I know what you mean.
>
> I've heard, from usually reliable sources, that the Oracle CBO was based,
> in large measure, on the optimizer in DEC Rdb. (Oracle bought the rights
to
> DEC Rdb in 1994)
>
> The optimizer for DEC Rdb was smart enough to "expand the view" before
> optimizing. That is to say, it comes up with an equivalent query, using
> the underlying base tables. Therefore, the answer to your question in
the
> Rdb case boils down to whether the optimizer is smart enough to skip the
> work of doing a join if there is an equivalent query that doesn't use the
> other table.
>
> I don't know. I no longer work with Rdb. If you are interested, you
might
> post the question over in comp.databases.rdb
> Norm Lastovica can probably give you an answer.
>
> Here's what I do know about the DEC Rdb optimizer. It was (is) good.
Good
> enough so that my attention, when writing a query, was ALWAYS on things
> other than performance. I only got interested in performance when it was
a
> problem. And that was rare.
>
> In the case you outline with the three way join, if there were a
constraint
> that said that person.maritalstatusabbreviation references
> maritalstatus.maritalstatusabbreviation that would be the clue that a good
> optimizer would need. The case of
> NULL in person.mariatlstatusabbreviation can be resolved without
> referencing the third table. NULL never matches anything.

Good point -- it would be the rows that could potentially drop if there is no constraint to ensure that MaritalStatusAbbreviation in PERSON can always be found in the MARITALSTATUS table that would be the issue. So, to optimize, it would need to first see that there is a constraint that makes it so that we wouldn't drop any rows when doing the join if it were to skip joining this table in.

--dawn Received on Mon May 03 2004 - 20:40:34 CEST