Appian Dual Sorting

Hello,

We observed a similar behavior in our project. On a table with a large number of rows (20,000,000 rows), performing a multi-column sort including the primary key caused the index that was supposed to be used to be considered irrelevant by the DBMS (Oracle). We had to create a composite index, including the desired column and the primary key, in order for it to be used.

 victor.Bouchery Mike Schmitt Irshad Mohammed 

I m having a similar kind of issue, where in query entity, i have applied sorting on one field but its showing error with orderby: sort with 2 fields. So i m unable to find the exact root cause for this error. We are using appian 24.2 version. 

I'm not sure it's an "error" still, I believe it simply has to do with using a sort column that is not unique - thus the PKID is automatically included to introduce some consistent sorting order, because otherwise different query executions would return apparently-different sorts when sorting by a column where multiple rows can have the same value (i believe this behavior was observed in the past and people complained about it, so this behavior was introduced as a way to fix it).  I stand behind my belief that it's unlikely to ever cause noticeable performance issues, even in heavy queries.

In the original example, there is a very simple explanation.

I've seen this behavior when sorting query entities by non-unique columns. That use case could try ensuring the database has a UNIQUE constraint on vertBrokerCode column. The query logic needs a unique sort in order to have idempotent and repeatable queries.

It makes sense. You will get very different results on two identical queries if the results are not sorted the same exact way, every single time. In the original posting's particular case, if vertBrokerCode is not unique then which one of the potentially many results should be returned as a result (given a batchSize of 1)? 

In the original example, there is a very simple explanation.

I've seen this behavior when sorting query entities by non-unique columns. That use case could try ensuring the database has a UNIQUE constraint on vertBrokerCode column. The query logic needs a unique sort in order to have idempotent and repeatable queries.

It makes sense. You will get very different results on two identical queries if the results are not sorted the same exact way, every single time. In the original posting's particular case, if vertBrokerCode is not unique then which one of the potentially many results should be returned as a result (given a batchSize of 1)?