USE-INDEX use gives different record count

[LBiddiscombe]

Hi,

There may be a blindingly obvious explanation for the following behaviour but, so far, I've missed it!

BTW, In practice I am use similar logic to break an output file into sizeable 5000 line chunks, so it has more of a purpose than this demonstration shows!...

Run the code as is, and as expected both result set counts are the same.
Now remove the "USE-INDEX main" line and run it again. The counts are different!?

Can anyone explain why??

DEFINE TEMP-TABLE t_item NO-UNDO
FIELD item-id AS INTEGER
FIELD item-desc AS CHARACTER
FIELD active AS LOGICAL
INDEX main AS PRIMARY UNIQUE item-id
INDEX active active.

DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE VARIABLE j AS INTEGER NO-UNDO.
DEFINE VARIABLE i-simple AS INTEGER NO-UNDO.
DEFINE VARIABLE i-split AS INTEGER NO-UNDO.
DEFINE VARIABLE i-last-item AS INTEGER NO-UNDO.

/* Build the dummy demo table */
DO i = 1 TO 10000:
CREATE t_item.
ASSIGN
t_item.item-id = i
t_item.item-desc = SUBSTITUTE("Item_&1", STRING(t_item.item-id))
t_item.active = (i MOD 2 = 0).
END.

/* count the active items */
FOR EACH t_item NO-LOCK WHERE t_item.active:
ASSIGN i-simple = i-simple + 1.
END.

/* count the actve items, in batches of 1000 */
split:
REPEAT:
FOR EACH t_item NO-LOCK WHERE
t_item.active AND
t_item.item-id >= i-last-item
USE-INDEX main
j = 1 TO 1000:
ASSIGN i-last-item = t_item.item-id i-split = i-split + 1.
END.
IF NOT AVAILABLE t_item THEN LEAVE split.
ELSE i-last-item = t_item.item-id.
END.

MESSAGE i-simple SKIP i-split
VIEW-AS ALERT-BOX INFO BUTTONS OK.

 

[mbouchard]

Hi,
I don't think that it's gauranteed that the records will be read in item-id order without the use-index statement, thus explaining why some records are not counted. Note that replacing "use-index main" with "BY item-id BY active" will give the desired results, as would defining your primary index:
INDEX main is PRIMARY UNIQUE item-id asc active asc.

Not a great explanation, but I hope that helps...