In a multi-component index, adding a DESCENDING sort on the last field results in XREF considering all fields as "SORT-ACCESS", is this correct ?

Feb 19, 2020
Knowledge

Title

URL Name

In-a-multi-component-index-adding-a-DESCENDING-sort-on-the-last-field-results-in-XREF-considering-all-fields-as-SORT-ACCESS-is-this-correct

Article Number

000113782

Environment

Product: OpenEdge Version: 11.x OS: All supported platforms

Question/Problem Description

In a multi-component index, adding a DESCENDING sort on the last field results in XREF considering all fields as "SORT-ACCESS", is this correct ?

Main.p

compile order.p xref "order.xref".

def var lcc as longchar no-undo.
def var ilines as int no-undo.
def var ic as int no-undo.

copy-lob from file "order.xref" to lcc.

ilines = num-entries( lcc, "~n" ).

do ic = 1 to ilines:
message string( entry( ic, lcc, "~n" ) ).
end.

order.p

message "ascending".

for each OrderLine
where OrderLine.OrderNum = 10000
no-lock
by OrderLine.OrderNum
by OrderLine.LineNum
:

message OrderLine.LineNum.

end.

message "descending".

for each OrderLine
where OrderLine.OrderNum = 10000
no-lock
by OrderLine.OrderNum
by OrderLine.LineNum descending
:

message OrderLine.LineNum.

end.

Output

order.p order.p 1 COMPILE order.p
order.p order.p 1 CPINTERNAL UTF-8
order.p order.p 1 CPSTREAM UTF-8
order.p order.p 1 STRING "ascending" 9 NONE TRANSLATABLE
order.p order.p 3 STRING "OrderLine" 9 NONE UNTRANSLATABLE
order.p order.p 3 ACCESS sport.OrderLine OrderNum
order.p order.p 3 ACCESS sport.OrderLine OrderNum
order.p order.p 3 ACCESS sport.OrderLine LineNum
order.p order.p 3 SEARCH sport.OrderLine OrderLine
order.p order.p 10 ACCESS sport.OrderLine LineNum
order.p order.p 14 STRING "descending" 10 NONE TRANSLATABLE
order.p order.p 16 ACCESS sport.OrderLine OrderNum
order.p order.p 16 ACCESS sport.OrderLine OrderNum
order.p order.p 16 ACCESS sport.OrderLine LineNum
order.p order.p 16 SEARCH sport.OrderLine OrderLine
order.p order.p 16 SORT-ACCESS sport.OrderLine OrderNum
order.p order.p 16 SORT-ACCESS sport.OrderLine LineNum
order.p order.p 23 ACCESS sport.OrderLine LineNum

Steps to Reproduce

Clarifying Information

Error Message

Defect Number

Enhancement Number

Cause

Resolution

SORT-ACCESS- Indicates that the query result is to be ordered by a particular column value and no suitable index exists. A sort of the query result on the noted column value is required.

The Output is showing the correct behavior as the Orderline table 'orderline' index is an ascending OrderNum+LineNum index. There isn't an index that defines an ascending ordernum component and a descending LineNum component, so the results will need resorting if they are to be presented in that order. SORT-ACCESS indicates that no suitable index exists, and that's true here.

Workaround

Notes

References to Other Documentation:

Use ABL Database Triggers and Indexes: Database Index Usage, Find out which indexes are used
https://docs.progress.com/bundle/openedge-abl-database-trigger-and-indexes/page/Find-out-which-indexes-are-used.html

Progress Article:

Using XREF Within Indexes Explained

Keyword Phrase

Last Modified Date

11/20/2020 6:56 AM

In a multi-component index, adding a DESCENDING sort on the last field results in XREF considering all fields as "SORT-ACCESS", is this correct ?

Files(0)

Was this article helpful?

Was this article helpful?

In a multi-component index, adding a DESCENDING sort on the last field results in XREF considering all fields as "SORT-ACCESS", is this correct ?

Information

Related Articles