RE: SQL select via logicals

["Cotes, Steven" <cotess@xxxxxxxxxxx>]

Paul,
If you know the best access path to use, then specify it. The optimizer
will check it first.

John,
No change so far. This is from "DB2 for AS/400 SQL Programming V4R1"  :

"To ensure that an access path is considered for optimization, specify
the
logical file associated with the access path as the file to be queried.
The optimizer will consider the access path of the file specified on the
query or SQL statement first."

Also you are correct. It doesn't matter if a logical with select/omit
criteria
is dynamic or access path built. Any access of data through the logical
should only contain records which meet the criteria. The same goes for
SQL Views and Indexes which are described as being logical files in the
SQL Reference.

 -Steve Cotes
 -cotes@data-io.com

> ----------
> From:         PaulMmn[SMTP:PaulMmn@ix.netcom.com]
> Sent:         Sunday, November 30, 1997 1:52 PM
> To:   MIDRANGE-L@midrange.com
> Subject:      Re: SQL select via logicals
> 
> >And it is better to do the SQL on the base physical and let the
> optimizer
> >find the index.  However the search order for the index (I believe)
> is
> >still chronological.  Meaning it will look at indexes in the order of
> >when they were made.  So if you have 20 logicals(with possible index
> >candidates) the optimizer may not get to number 19 or 20 to find the
> >best index to use.  It might give up before then,  thinking
> > "Hey, I'm wasteing too much time, I could have built an index by
> now" .
> >
> >In this case (where you know that logical number 20 would be the best
> >index to use) you can "help" the optimizer by specifing a logical in
> >the SQL statement to look at first.
> >
> >If anyone knows if this has changed recently(V4 or so) please post.
> >
> >John Carr
> 
> 
> 
> This really burns me.  We have some physicals in the millions of
> records,
> with 60 (yes, Sixty) or more logicals.  And the @#^&&*$% optimizer
> thinks
> it can do it faster by building its own before it searches all of the
> available keys.
> 
> GIVE US AN OPTION!  Let us decide to force the optimizer to evaluate
> all
> available access paths or not.
> 
> 
> --Paul E Musselman
> PaulMmn@ix.netcom.com
> 
> 
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to
> "MIDRANGE-L@midrange.com".
> | To unsubscribe from this list send email to
> MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator:
> david@midrange.com
> +---
> uucp
> 
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@midrange.com".
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---