RE: query optimizer, debug and access path suggestion

Rick,

You might try rearranging the order of your where clause, if any.  I
have seen that change the access paths used by the optimizer.

IIRC you should also have a message in the job log that will tell why
existing logicals were omitted.  That might help also.

As I'm writing this a light bulb is going off telling me that that the
optimizer ignores join logicals.  I can't remember where I saw that
though.  Has anyone else seen anything about the optimizer and join
logicals?

Rick

> -----Original Message-----
> From: rick.baird@xxxxxxxxxxxxxxx [mailto:rick.baird@xxxxxxxxxxxxxxx]
> Sent: Monday, October 20, 2003 12:53 PM
> To: midrange-l@xxxxxxxxxxxx
> Subject: query optimizer, debug and access path suggestion
> 
> 
> Long story short - I'm running an opnqryf command over a 
> joined logical of 
> two really large files.
> 
> I tested it with debug running and found that, due to the 
> complexity of 
> the select statement, the query optimizer decided to build 
> it's own join, 
> rather than use the existing join, even though I used KEYFLD(*FILE) 
> 
> I got two access path suggestions, one on each file.
> 
> My question is, should I try to change the key fields of the 
> join logical 
> to match the keys suggested for the primary file, or do what the 
> suggestion says and build the logicals suggested over both 
> files?   fyi, 
> the logical suggested for the secondary file contained only one field.
> 
> thanks,
> 
> Rick