A self-join query in combination with BY clause generates an SQL query with an ORDER BY clause on the unique_id_0 index instead of the unique, single field index that exists for the table.

Performance issue where a dynamic ABL query (with a join) causes the DataServer to generate an Oracle SQL query where the BY clause becomes ORDER BY T0.U##FOO_ID2, unique_id_0

ABL dynamic query: 

FOR EACH b_Foo FIELDS( <some fields> ) NO-LOCK ,
EACH b_ParentFoo FIELDS( foo_Id foo_Id2 ) NO-LOCK OUTER-JOIN
WHERE b_ParentFoo.foo_Id = b_Foo.foo_ParentFoo
BY b_Foo.foo_Id2 INDEXED-REPOSITION QUERY-TUNING(HINT 'ORDERED' )

Generated SQL query: 
 

SELECT /*+ ORDERED */ <some fields>
FROM ORAGLIMS.FOO T0 LEFT OUTER JOIN ORAGLIMS.FOO T1 ON (T0.FOO_PARENTFOO = T1.FOO_ID) ORDER BY T0.U##FOO_ID2, unique_id_0

 

By using  USE-INDEX instead of the BY clause in this particular case, the DataServer strips the unique_id_0 from the SQL query and replaces it  ORDER BY T0.U##FOO_ID2, T1.FOO_ID

Yet, this doesn't result in an index-sorted query.

In a join where a sort is performed on the unique key of the first buffer, multiple records of the second table may come in for a given record of the first table:
 

A , <row1 of joined table>
A , <row2 of joined table>
A , <row3 of joined table>

 

So in the above case, an additional sort is required to achieve consistent sorting.

In the example, at most 1 row of the second table can satisfy the join clause:

There's an exact match on the unique primary key foo_Id.

So in those cases, no additional BY clause is required to obtain consistent sorting.

The DataServer schema defines a unique index with 1 field (foo_Id2) as follows, where unique_id0 is not part of the index.
 

ADD INDEX "foo_Id2" ON "FOO"
AREA "Schema Area"
UNIQUE
INDEX-NUM 11
FOREIGN-NAME "foo_Id2"
RECID-INDEX "u"
INDEX-FIELD "foo_Id2" ASCENDING

Whenever there is a non-unique indexed column in the BY clause,  unique_id_0 is added to make the sort unique.

Fixed version(s): 11.7.6.0, OpenEdge 12.3

 

Progress Articles:

 Oracle Dataserver: Sort order of unique_id_0 does not change when reversing main sort order
 Oracle DataServer returns rows in the wrong order
 DataServer generates different queries depending on use of USE-INDEX or BY clause
 REPOSITION-TO-ROWID( ) method reads all records to reach the specified record using DataServer for Oracle