Skip to Content
-3

Determine default sort order

Jul 13, 2017 at 03:48 AM

288

avatar image
Former Member

How do we determine the default Sort order of an open SQL select query for a table having secondary indexes. Usually an extraction is sorted by primary key. Is there a function available in ABAP/HANA to determine this order?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Matthew Billingham
Jul 13, 2017 at 05:10 AM
1

There is no default sort order on relational databases. There is no concept of sort order for relational databases. If you do

DATA myinternaltable TYPE standard table of mytable.
SELECT * FROM mytable INTO TABLE myinternaltable 

The order of records in myinternaltable is undefined. If you want an order, you must define it in you SELECT statement or you internal table, using ORDER BY, and TYPE SORTED TABLE, respectively. You absolutely cannot rely on the order of the records as read from the database.

Show 7 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Matthews

The open SQL select earlier hit the Oracle DB. Are you sure even in there we never had a default sort order w.rt primary keys/ secondary indexes field list? SAP recommends using "ORDER BY PRIMARY KEY" for sorting out a select if we knew the sort order in ORACLE/ other databases. The table I need to sort is having secondary indexes. Can this sort order data be pulled out from a trace? If yes which tcode can give me the best clarity?

Diganto

0

"Are you sure even in there we never had a default sort order w.rt primary keys/ secondary indexes field list?"

100% totally certain. Databases 101: Relational databases do not have a default sort order. Here is Oracle's own documentation: https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702

The GROUPBY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDERBY clause.

Use the ORDERBY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

0

"SAP recommends using "ORDER BY PRIMARY KEY" for sorting out a select if we knew the sort order in ORACLE/ other databases."

Where? Please point out where this recommendation comes from.

ORDER BY PRIMARY KEY sorts by the primary key, what else? Do you want to extract the key fields of a secondary index in order to sort with a dynamic order by clause?

0
Former Member
Horst Keller

Dear Keller,

The filter criteria the select uses in our landscape are not the key fields, when extracting out the data from a non HANA DB, does the query not sort the extraction wrt indexes? Per hanatisation best practices, it is advised to add "ORDER BY PRIMARY KEY" in a select query.

Please recommend whether we need to concerned about any other default sort order.

Thanks

Diganto

0

Please recommend whether we need to concerned about any other default sort order.

No, you don't. Because no sort order, default or otherwise, exists.

0

As Matthew already pointed out:

"There is no default sort order on relational databases. There is no concept of sort order for relational databases"

Therefore, no, "the query does not sort the extraction wrt indexes"

In former times, people tended to rely on a sort order, because on some platforms or for special tables (pool, cluster) entries were returned in a sorted way. But you never were allowed to rely on that. On Hana, the sort order definitely is undefined, and that's why SAP recommends to use the ORDER BY explicitly in the best practices for Hana (but that is true independently from Hana). In fact ORDER BY PRIMARY KEY is only one way of using it.

0
Former Member

Got your point. Thanks

Diganto

0