Re: Query Optimizer Access Path Selection via Opnqryf

It's been a while since I've played with this particular case, but it
appears to still be the same as I remember.  Interestingly, if you put
DYNSLT in your L2's DDS, the OPNQRYF will probably use it.  Of course, then
the logical won't perform any better than the OPNQRYF, so it doesn't really
gain you anything.

Another interesting exercise would be to build three logicals with only
single keys - one with FRMLC9, one with FRMLOC, and one with SHPFOR.  If you
do that, you should find it uses the one which enables it to reduce the
number of records the most.

----- Original Message ----- 
From: <rick.baird@xxxxxxxxxxxxxxx>
To: <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, March 18, 2004 12:45 PM
Subject: Query Optimizer Access Path Selection via Opnqryf


> hey all,
>
> I decided to do some bench testing on using opnqry with different types of
> logicals to see what access paths are chosen.
>
> i have a file - about  500,000 records, not keyed.
>
> My CL is as follows:
>
> PGM
> OVRDBF     FILE(SHP72PTD) SHARE(*YES)
> OPNQRYF    FILE((SHP72PTD)) QRYSLT('FRMLC9 = "9" *AND +
>              FRMLOC = "81" *AND SHPFOR = "SHP"') +
>              KEYFLD((FRMLC9) (FRMLOC) (TOLOC) (PRDCDE) +
>              (SHPFOR))
>
> /* shptestr does nothing but read every record in the file, input primary,
> by key */
>
> CALL       PGM(SHPTESTR)
>
> CLOF       OPNID(SHP72PTD)
> DLTOVR     FILE(*ALL)
> ENDPGM
>
> I have 2 logical file DDS members (as yet not built)
>
>  * SHP72PTDL1
> A          R SHP72PTDR                  PFILE(SHP72PTD)
> A          K FRMLC9
> A          K FRMLOC
> A          K TOLOC
> A          K PRDCDE
> A          K SHPFOR
>
> and
>
>  *SHP72PTDL2
> A          R SHP72PTDR                  PFILE(SHP72PTD)
> A          K FRMLC9
> A          K FRMLOC
> A          K TOLOC
> A          K PRDCDE
> A          K SHPFOR
> A          S FRMLC9                    COMP(EQ '9')
> A            FRMLOC                    COMP(EQ '81')
> A            SHPFOR                    COMP(EQ 'SHP')
>
> note, the L2 logical is exactly the sequence and selection criteria as the
> opnqryf.
>
> I ran the call the CL 3 times with debug turned on so I could see the
query
> optimizer messages.
>
> 1. no logicals built:  it created the access path, as expected.
>
> 2. L1 only built:  it considered all paths and used the existing L1 path,
> as expected.
>
> 3. L2 only built.  it considered all paths, and decided to create it's
own.
>
> why didn't it use the L2 logical?  it was exactly what it needed?
>
> I'm assuming it's because it won't consider existing access paths that
have
> record selections, because the chances if it meeting the selection
criteria
> is slim to none.
>
> I guess what I'm asking is, if you're going to do record selection, rather
> do it via opnqryf than by logical, because you'll end up having a ton of
> single purpose logicals, and if you do ever happen to use opnqryf, your
> logicals with record selection won't ever be considered - wasted access
> paths.
>
> Comments?
>
> BTW, I have the bench times involved too if anyone is interested.
>
> Thanks,
>
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>