Re: Indexes vs Logical Files

[VERNON HAMBERG Owner <vhamberg@xxxxxxxxxxxxxxx>]

Of course, it is highly recommended that one does not specify an LF in a SELECT (unless occasionally for Select-Omit) - the optimizer will just go out to the PF and see what the indexes are for it, then choose what it thinks best.


On Select-Omit - those are in the class of things called derived-key indexes. I've looked around some on this QAQQINI setting - recent documentation doesn't even include IGNORE_DERIVED_INDEX. The default at first was *NO, then it was changed to *YES at 6.1 of the OS. Maybe these days, this setting is ignored - someone else would need to day. Of course, we now can define indexes to include a WHERE clause - analogous to S/O LFs - how do those work with SQE? This is a sandbox I've not played in.


Cheers
Vern


On Fri, 13 Oct, 2023 at 8:34 AM, Charles Wilt <charles.wilt@xxxxxxxxx<mailto:charles.wilt@xxxxxxxxx>> wrote:
 

To: midrange systems technical discussion

That has never been the case.

And now-a-days, pretty much everything goes through the SQE.

Originally, if you referenced a LF on an SQL statement, you forced the use
of the CQE.

Perhaps that's what you're thinking of.  But again, even that is no longer
the case.


Charles

On Fri, Oct 13, 2023 at 6:52 AM Gavin Inman <midrangelist@xxxxxxxxxxxxxx<mailto:midrangelist@xxxxxxxxxxxxxx>>
wrote:

> Years ago it was explained to me that if the physical has keys, it's
> forced to use the older database engine.  Unless something has changed,
> I believe that's still true today.
>
> Gavin.
>
> On 10/13/2023 8:47 AM, Greg Wilburn wrote:
>
> I've been using Index Advisor and Run Sql Scripts "Run and Explain" to
> analyze some of our queries.  I'm looking for increased performance.
>
> I have a large file/table OEBOXCBX with a logical files defined with the
> following keys:
>
> LF #1: BXCOM#, BXORD#, BXREL#, BXBOX#, BXPRT#
> LF #2: BXCOM#, BXORD#, BXREL#, BXBXNO, BXPRT#
>
> I ran an SQL statement over a complex SQL view that contains this file.
> RSS Run and Explain recommended the following index:
> CREATE INDEX ASTHHOBJ/OEBOXCBX_IDX
>      ON ASTHHOBJ/OEBOXCBX (BXCOM# ASC, BXORD# ASC, BXREL# ASC) UNIT ANY
> KEEP IN MEMORY NO;
>
> My question is why the SQE wouldn't use one of the existing logical files
> as an index?  As an RPG guy, this makes no sense to me.
>
> TIA,
> Greg
>
> [Logo]<https://www.totalbizfulfillment.com/><https://www.totalbizfulfillment.com></a>>     Greg Wilburn
> Director of IT
> 301.895.3792 ext. 1231
> 301.895.3895 direct
> gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx><mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>>
> 1 Corporate Dr
> Grantsville, MD 21536
> www.totalbizfulfillment.com<http://www.totalbizfulfillment.com><http://www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related
> questions.