FOR EACH employees NO-LOCK WHERE employee# <> '' :
DISP employees WITH 1 COL SCROLLABLE .
END.
i have two index
1. employee#(unique index)
2. last_name , first_name , employee# (primary) .
which index will be used in query?
The primary index.
FOR EACH employees NO-LOCK WHERE employee# GT '' :
In this case the query will use the employee# index.
the "<>" ("not equal to" or "different than") operator does not help the ABL to pick up the most appropriate index concerned by a field of your where clause. As George said, using WHERE employee# > "" would help to make it pick up the employee# unique index.
Besides, when I wonder about that, I prefer to ask the ABL itself rather than searching in a large doc or in a large set of KBase articles. Two options for that:
a) For a simple FOR EACH loop, do a COMPILE XREF and pay attention to SEARCH entries
b) for a QUERY object, you can look at its hQuery:INDEX-INFORMATION attribute.
HTH
/S
why so?
why it is using primary @ [mention:c01c86410f7441368d7c57bf49e3fe08:e9ed411860ed4f2ba0265705b8793d05]
SORRY george potemkin
'NE' is not a bracketing. It does not help for compiler to choose an index. That is why the compiler choose a primary index.
Any query predicate that uses a NOT or a NE will cause trouble with index selection and reads.
This isn't the most super-technical in-depth analysis of query selection or anything, but it's got lots of guidelines and gotchas to watch out for. www.jdpalmer.co.uk/.../index.php
Most importantly it includes methods for finding out which indexes are selected, and for finding out records read by the query.
tnx to oll.
Ritish, you might want to download Mike Lonski's Pick an Index, Any Index presentation. He's presented it at numerous PUGS, Exchanges, PUG Challenges, and coffee shops (I think ;) ). He may have a newer version available, but you can grab this one from an old Exchange conference:
community.progress.com/.../2141.exchange-2006-breakout-sessions
Search for "Pick an index" and you can open his presentation from there. It explains how the 4GL decides what index to use based on the query involved.
thanks @ Scott and james