RE: RE: sqlrpgle select statements

rpg400-l-request@xxxxxxxxxxxx wrote:

>  10. RE: sqlrpgle select statements (Matt.Haas)
>
>If you have select/omit criteria in the logical file, the query
>optimizer will not consider it when determining the best access path.
>When you include select/omit criteria in DDS based logical files, they
>are both an index and a view at the same time which isn't normal in the
>SQL world.

Since it seems to work this way, I can accept it. But it also seems 
counter-intuitive. Now, if we were talking about DYNSLT, then it'd make more 
sense to me.

But simple Select/Omit ought to result in an index that has exactly what is 
needed... hmmm... And maybe that's the point of confusion.

As implemented under OS/400, SQL has no way to know that the index is 
appropriate. Under the covers, query engine hasn't been programmed to examine 
the Select/Omit specs of the LF. It's only been programmed to check whether or 
not Select/Omit has been specified at all.

And when it sees that attribute is set, it doesn't look farther to determine if 
the LF Select/Omit precisely satisfies the SELECT/WHERE. It simply says 
"Oooops, can't use this index because it _might_ not have all rows that I need. 
I better build my own index." It was probably thought to be too expensive by 
the SQL developers.

But when the LF is explicitly named in SELECT/FROM, it has effectively been 
given permission to rely on the keys in the index. It has been allowed by the 
developer to read only the indexed rows.

For now, that makes enough sense.

Tom Liotta