Re: ?s regarding sparse indexes

[Charles Wilt <charles.wilt@xxxxxxxxx>]

​You're a bit confused on how sparse indexes work...

Say you have
create index myidx on mytbl (keyfld)
where statusfld = 'A'


You wouldn't do the following:
select * from myidx


You'd do
select * from mytbl
where statusfld = 'A'


So unlike select/omit logicals, you write the application without regard to
the sparse index existence.  The DB will use the sparse index if and when
it can automatically.

Charles
​


On Thu, May 1, 2014 at 2: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.