Use Index

[atuldalvi]

Can we use use-index option for 2 indexes in for each ?

 

[TomBascom]

Except for FIND statements (which always use just one index) 4GL queries (FOR EACH, OPEN QUERY etc...) can potentially use multiple indexes if you allow the compiler to choose them.

If you override the compiler by specifying an index via USE-INDEX then that index, and only that index, will be used.

FWIW multiple indexes aren't usually very helpful. You are almost always better off with a good compound index.

The compiler is almost always smarter than you are. If you think you are smarter than the compiler you should carefully document your proof. Don't forget to include run-time test cases using real data. Comments such as:

FIND myTable USE-INDEX myIndex.  /* added USE-INDEX to improve performance */

Are a big red flag which almost always means that someone is providing me with an income opportunity

 

[atuldalvi]

Can u please give me one example of for each ?

 

[TomBascom]

As demonstrated by the XREF output the following will use both the "name" and "custNum" indexes of the sports2000 database:

for each customer no-lock where
  name >= "d" or
  custNum > 1000:

  display custNum name.

end.

compile "multidx.p" xref "multidx.xrf".

./multidx.p ./multidx.p 1 COMPILE multidx.p
./multidx.p ./multidx.p 1 CPINTERNAL ISO8859-1
./multidx.p ./multidx.p 1 CPSTREAM ISO8859-1
./multidx.p ./multidx.p 1 STRING "Customer" 8 NONE UNTRANSLATABLE
./multidx.p ./multidx.p 1 ACCESS sports2000.Customer Name
./multidx.p ./multidx.p 1 ACCESS sports2000.Customer CustNum
./multidx.p ./multidx.p 1 STRING "d" 1 NONE TRANSLATABLE
[COLOR="#A52A2A"][B]./multidx.p ./multidx.p 1 SEARCH sports2000.Customer Name
./multidx.p ./multidx.p 1 SEARCH sports2000.Customer CustNum
[/B][/COLOR]./multidx.p ./multidx.p 5 ACCESS sports2000.Customer CustNum
./multidx.p ./multidx.p 5 ACCESS sports2000.Customer Name
./multidx.p ./multidx.p 5 STRING ">>>>9" 5 NONE TRANSLATABLE  FORMAT
./multidx.p ./multidx.p 5 STRING "x(30)" 5 NONE TRANSLATABLE  FORMAT
./multidx.p ./multidx.p 7 STRING "Cust Num" 8 LEFT TRANSLATABLE
./multidx.p ./multidx.p 7 STRING "Name" 4 LEFT TRANSLATABLE
./multidx.p ./multidx.p 7 STRING "CustNum" 7 NONE UNTRANSLATABLE
./multidx.p ./multidx.p 7 STRING "Name" 4 NONE UNTRANSLATABLE
./multidx.p ./multidx.p 7 STRING "-------- ------------------------------" 39 NONE UNTRANSLATABLE