Re: ?s regarding sparse indexes

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

S/O for file I/O? Yes, and also for optimization. Now that the default 
for the QAQQINI setting, IGNORE_DERIVED_INDEX, is *NO, S/O LFs are 
available to the optimizer.

This is basically the same as the sparse index - which has a WHERE 
clause - and we can also specify the columns to be presented by the 
INDEX - so this becomes almost exactly the same as an S/O logical.

We have been able to use indexes in RPG in F-specs - the newer kind of 
index is just as useful for I/O as S/O logicals, and also for 
optimization of SELECT statements that include a matching element in the 
WHERE clause or probably in a JOIN.

HTH
Vern

On 5/1/2014 1:51 PM, Alan Campin wrote:
> If you are using SQL, use the physical and let SQL tell you what indexes to
> use with the Index Advisor. Select/Omit logicals is for file I/O
>
>
> On Thu, May 1, 2014 at 12:20 PM, Vicki Wilson <VWilson@xxxxxxxxxxxxx> wrote:
>
> > I'm new at a company that has launched a DDS to DDL initiative.
> >
> > The idea has been introduced to replace our select/omit logicals with
> > sparse indexes.
> >
> > The idea of a sparse index is new to me and I'm wondering about best
> > practices regarding sparse indexes.
> >
> > The first thought that comes to mind is that I was always taught not to
> > use select/omit logicals.  Quite frankly this has been so ingrained in me
> > that I'm not sure why anymore.  The most obvious argument is that when
> > researching code it's frustrating.  Some of the business logic is hidden in
> > the logical and being new to the environment I don't always realize what
> > might be happening outside of the program.
> >
> > For that reason alone I'm not a fan of creating sparse indexes to replace
> > select/omit logicals.  For review/analysis purposes - it would hide some of
> > the business logic.
> >
> > But the idea of a sparse index also perplexes me for another reason.  I
> > thought the idea in SQL was always to reference the table - not a logical.
> >   And let the engine do the work.  If you used sparse indexes wouldn't you
> > have to reference the specific index?
> >
> > Thoughts on best practices and references would be appreciated.  I tried a
> > google search and did not come up with much.
> >
> > Thanks.
> > Vicki Wilson
> >
> >
> > --
> > 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.