Index

For some reference on index selection, check the knowledge base article ID: P12969

Title: "4GL Query concepts (FOR EACH, FIND, GET, INDEX)"

Which index will be used and why in

Difficult to answer without knowledge of all your indexes. CAN-FIND (and FIND) will never use more than a single index.

When i compiled my program i found in xref file that progress (v9) uses "alloc_status".

Probable because it's either the primary index of the table or alphabetically the first. If all other indexes have a single equality match as well, there is no better decision the compiler is able to do. The compiler isn't able to make decisions on data distribution. It may actually be one of the exceptions where USE-INDEX is a good choice.

To boost performance on that query you need a single index with 4 components:

po_number

customer

deleted

allow_status

Years ago, I've found some interesting stuff about the "index selection" alogrithm. It is still available there: http://www.fast4gl.com/downloads/monographs/query/query.html

You can also look at the "OpenEdge 102.B Database Essentials" guide: http://communities.progress.com/pcom/docs/DOC-103525

Finally, I made (ages ago) a summary document about this. See attachment.

Hope it helps.

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/27/about-index-selection.pdf:550:0]

can-find always use 1 index. for each can use many indexes

so just rewrite this part to:

dev var found as logical.

found = false.

for FIRST porder.po_header  WHERE po_header.po_number = 123456

              AND    po_header.customer  = "CUST"           

              AND    po_header.deleted   = FALSE
              AND    po_header.alloc_status = 'U'  NO-LOCK:

  found = true.

  leave.

end.

if found then

.....

Thanks to all of you. You were very helpful.