Re: time/date as key fields

[CRPence <CRPbottle@xxxxxxxxx>]

  I mentioned nothing about LEFT JOIN nor use of such information only 
for join implementations from parent to child.  The optimizer is free to 
use any information seen fit to make decisions.  A JOIN, EXISTS, or IN 
could easily make use of parent relationship and check constraints to 
eliminate rows\values in the primary file irrespective of join type and 
similarly for worthless probes or inquiries of a child TABLE even if 
having failed to eliminate the rows\values from the other side of a 
join.  That the RI relationship is established, means there is more 
"knowns" about the child TABLE being queried; i.e. the parent need not 
be explicitly named anywhere in the query, because the database has 
previously been informed of the relationships of the named files\data.

Regards, Chuck

On 11/20/10 11:43 PM, dieter.bender@xxxxxxxxxxxx wrote:
> ... maybe I don't use left outer joins from a parent with its child
> (why should one do this) and so I've had no chance to recognize the
> effects of this feature. BTW: I'm using RI constraints, wherever
> possible and allowed by my customers; in most cases they don't wanna
> use them...
>
> CRPence on Saturday, November 20, 2010 9:51 PM wrote:
> > The DB2 SQL for i [the SQE] does utilize information from
> > constraints for making some decisions. There had even been a
> > defect, early-on, whereby an error in implementation effected
> > incorrect output for having omitted rows for values which were
> > actually in the database.
> >
> > For example, the database can implicitly know that a value that is
> > not in the parent will not be in the child, and can optimize the
> > access plan and path for data retrieval with that understanding.
> > That specific utilization of constraint awareness was the source of
> > the aforementioned defect, though I do not recall the details
> > [perhaps applying rules from a constraint that was not enforced per
> > CHGPFCST STATE(*DISABLED) or in CHECK PENDING status]. Similarly,
> > logic in a check constraint can eliminate selection rules, probes,
> > and actual retrieval of data that "can not exist" according to the
> > rules. Note that such logic makes little sense with regard to the
> > TABLE [on which the constraints are defined] for the latter two,
> > since the physical data is just not there [else there is a highly
> > negative defect]; the logic applied from constraints definitely
> > assists for improvements on [implementations effected as or similar
> > to] join logic. If you have many values 'X' in a column used to
> > join on a column in a child file whose parent has no value 'X',
> > then there is no need to inquire of any INDEX on the child if there
> > are any rows with the value 'X'; i.e. there must not be any
> > 'X'-rows in the child, thus do not perform any actions that might
> > otherwise lead down the path to locate any such rows.