Re: Logical File Key Suggestion Confusing (well really index).

[Michael Schutte <mschutte369@xxxxxxxxx>]

I should probably also mention that, the visual explained showed that an
index probe was used.  However, it didn't use the index I expected.  It
used an index by item, lot1 - 3, building, floor, aisle, and slot.  I have
a logical that matched the where clause exactly. Which I know that doesn't
necessarily mean anything.  The system will try to pick the best.  I just
find it odd that it suggested an index where item was at the end, but ended
up using one that was first in the key list.

alright enough rambling.


On Tue, Jul 24, 2012 at 3:31 PM, Michael Schutte <mschutte369@xxxxxxxxx>wrote:

> I ran a query over a file and the index adviser suggested something I
> wasn't expecting but I understand.
>
> It suggested floor, lot2, lot3, building, lot1, aisle, tally number, slot,
> and then item.
>
> In our setup, floor isn't used so the distinct values for this field is 1.
>   Lot2 and Lot3 are rarely used.  But still has lower number of distinct
> values.  So I understand why they are suggested first for the index.
>
> The problem is that we you see that logical/index and understand the
> database, you'd ask why was it created like this.  Anyway, Floor, Aisle,
> Slot makes up the location that the item is located within the building.
>  The mask being   Floor/Aisle/Slot  So in other indexes you'll see
> Building, Floor, Aisle, Slot as the key.  Although we are not using floor
> now, it's not to say that it would be used in the future.
>
> Basically, I'm looking for input on how you would go about creating this
> logical/index?  Optimization wise it makes sense, but reading as a human
> and the possibility in the future that it would be used, it doesn't make
> sense.
>
> FYI, I'm creating this as a logical to chain to in an RPGLE program if
> that sways your opinion at all.  Also in reality, this file will contain a
> small amount of records in it anyway.  They only exist while a tally is
> being unload from the truck and being putaway.  So it may be a moot point.
>  Currently only 46 records exist in the file.