Index selection differs between FIND and FOR EACH

Posted by jbijker on 01-Nov-2016 13:22

We have a table called wft_activity_log with a non-unique index on process_log_obj and status_key.

Now when I have the following code code and do a XREF compile (only checked under OE11.3):

FIND FIRST wft_activity_log NO-LOCK
    WHERE (wft_activity_log.process_log_obj = 1 AND wft_activity_log.status_key = "Active")
       OR (wft_activity_log.process_log_obj = 1 AND wft_activity_log.status_key = "Busy").

FOR EACH wft_activity_log NO-LOCK
  WHERE (wft_activity_log.process_log_obj = 1 AND wft_activity_log.status_key = "Active")
     OR (wft_activity_log.process_log_obj = 1 AND wft_activity_log.status_key = "Busy"):

   DISPLAY wft_activity_log.
END.

The FIND FIRST surprisingly does a WHOLE-INDEX scan
SEARCH mipdb.wft_activity_log xPK_wft_activity_log WHOLE-INDEX

But the FOR EACH is using the correct index (2 times):
SEARCH mipdb.wft_activity_log xIE1_wft_activity_log

So it seems the FIND and FOR EACH doesn't follow the same rules?

Anyone had the same behaviour before? And maybe an explanation of why the compiler does this?

Posted by Mike Lonski on 01-Nov-2016 13:42

FIND statements can only use a single index. With the OR breaking it up into two brackets on the index you want, both are essentially non-selectable so you default to the primary index and a full search.

That's the reason many advocate using "FOR FIRST" instead of "FIND FIRST".

Mike Lonski

Allegro

All Replies

Posted by Mike Lonski on 01-Nov-2016 13:42

FIND statements can only use a single index. With the OR breaking it up into two brackets on the index you want, both are essentially non-selectable so you default to the primary index and a full search.

That's the reason many advocate using "FOR FIRST" instead of "FIND FIRST".

Mike Lonski

Allegro

Posted by Marco Mendoza on 01-Nov-2016 13:43

http://knowledgebase.progress.com/articles/Article/21098

OpenEdge documentation bug PSC00312958 was logged to stress that even when the WHERE clause is the same, different query statements (FIND, FOR, Open QUERY, etc) may select different indexes.

 

Posted by smat-consulting on 01-Nov-2016 14:47

It doesn't surprise me the FIND is doing a whole-Index scan. It does surprise me that FOR EACH does not. :)

I never use OR in the where clause (except maybe with a temp-table and then only when I am 100% sure that Hell would freeze over before there would be more than a couple of records in the temp-table). I rather do two separate statements. FIND FIRST WHERE (case a). IF NOT AVAILABLE FIND FIRST (case b). Similar with FOR EACH - extract the body of the loop and do two loops...

I'd be curious to see whether that index would be used, if you extract the "wft_activity_log.process_log_obj = 1" and just have the "status_key part" within the OR. I forgot the exact rule, but it could be that it would just ignore the OR part of the WHERE but use the first part...

WHERE wft_activity_log.process_log_obj = 1

AND ( wft_activity_log.status_key = "Active"

    OR wft_activity_log.status_key = "Busy"

       )

Posted by Eric Andruscavage on 01-Nov-2016 16:43

It would need to be:

WHERE (wft_activity_log.process_log_obj = 1 AND wft_activity_log.status_key = "Active")

   OR (wft_activity_log.process_log_obj = 1 AND wft_activity_log.status_key = "Busy")

This thread is closed