Re: VIEWS compared to Nodes as Windows into data

"x" <x-false_at_yahoo.com> wrote in message news:40967812$1_at_post.usenet.com...
> **** Post for FREE via your newsreader at post.usenet.com ****
> "Dawn M. Wolthuis" <dwolt_at_tincat-group.com> wrote in message
> news:c75rev$tdq$1_at_news.netins.net...
> > Yes -- good --that might be the question I should ask -- if one creates
a
> > view that joins in table G and then Pat queries against this view and
> > includes no columns from G, does the join get executed? I would think
> that
> > under certain conditions the join would be required, such as in the case
> of
> > an inner join where there would be more rows if the join were not
> performed.
>
> What do you mean by "performing the join" or "the join get executed" ? :-)
> When Pat query against G, she gets what she was asking for .

First, for clarity, let's restrict this to a question about SQL, not about relational operators in general. Additionally, since each implementation of SQL is different, I'm thinking of the best commonly used implementation of SQL for this question (I don't know which one, but Oracle, DB2, and SQL Server are certainly in the commonly used category).

An example (at the risk of inviting issues that are not germane):

PERSON: (PersonID, FirstName, MaritalStatusAbbreviation) MARITALSTATUS: (MaritalStatusAbbreviation, Description) ORDER: (OrderID, PersonID, OrderDate)

Create a View that results in the same as this one: CREATE VIEW PERSONVIEW AS SELECT * FROM PERSON, MARITALSTATUS, ORDER WHERE ORDER.PersonID=PERSON.PersonID AND
PERSON.MaritalStatusAbbreviation=MARITALSTATUS.MaritalStatusAbbreviation;

Now do a query against that view:
SELECT FirstName, OrderID FROM PERSONVIEW;

Is the MARITALSTATUS table ever accessed when doing this query against that view? What if MaritalStatusAbbreviation in the PERSON table can be NULL? Does SQL recognize when it needs to and when it does not need to perform this join?:

> What atomic operations would be "executed" by the DBMS in
> the two scenarios/DBMSs you have in mind ?

Does the previous example clarify that?

> Why there would be more operations for a RDBMS than for any other DBMS ?
> As I said "Relational" is just a language.

If a view of the data is not the result of a join operation, but of navigation and then a field is not requested in the query, there is no need to involve such navigation.

> > But do current RDBMS's figure out when they don't need to include the
join
> > bz they know (how?) that the join will not result in a different row set
> and
> > there are no attributes from table G in the query against the view?
>
> There is a current RDBMS ? :-)
> If Pat asked for the join, an RDBMS would need to consider it.
> If Pat haven't asked for a field from table G, then an important
> part of the join may be skipped.
> Sometime the other part of the join can be skipped too. ;-)
>
> It is called query optimization.

Yes -- does SQL do the type of optimization in the example above where they would ignore a table in the view when it was not required?

The abstraction part of this question relates to the number of "portals into the data" that are required for any particular user and I don't know if I have the facts clear related to how SQL works. Then stepping up from that, it is not a SQL issue, per se, if this is not optimized, but an issue of using set-processing operations rather than, for example, tree navigation operations, when preparing "views" or windows into the entire set of data.

Thanks. --dawn Received on Mon May 03 2004 - 19:31:33 CEST