Sequence of a table

[Former Member]

Hi,

is there a command in ABAP to test whether a table (internal) is sorted on a specific field?

If I remember correctly, DB tables are always sorted by their keyfields, so logically, when I SELECT a subset of any DB tables into internal tables, those should still be sorted by that field, no? - Well, that is just the point, I'm not sure, so I would like to test it. Only I haven't yet found anything...

Thanks a lot!

Best regards,

Sapperdapper

[Former Member]

Hi

Internal table may be sorted on the key fields or on the indexes. If you want the table to be sorted in specific sequence of the coloum(s), then you can specify the same using SORT command. e.g

SORT: itab BY field1, field2...

Regards,

Hardik Mehta

[kesavadas_thekkillath]

The sorting sequence is not determined during selection. You have to specify it explicitly.

select.....order by primary key.

You will get a lot of information via sap help. Please check.

[Former Member]

Hi Kesavadas,

thanks! I have already checked, sorting the table is easy - I just thought I wouldn't have to do it because I think I remember that SAP tables ARE always sorted on keyfields - so I asked how I could check if that is so - I have searched the SAP help, but not found anything.

Also, when I view DD02T in SE16, it appears sorted ascending on TABNAME and my internal table appears sorted, too... so I wonder whether I have to use system performance to SORT the tables or whether it's actually superfluous?

Best regards,

Sapperdapper

[kesavadas_thekkillath]

Hi,

In this link Specifying a Sort Order (SAP Library - ABAP Programming (BC-ABA)) it clearly says that the sorting order is not determined while selection.

"If you do not use the ORDER BY clause, the sequence of the lines in the selection is indeterminate, and can vary each time the SELECTstatement is executed"

[Former Member]

Sapperdapper,

I believe Hardik has the answer you are looking for.  the sort order depends on the query where clause.  If you select the entire table, then it will be in the primary key order.  However if your where clause causes it to be accessed by an alternate index, then it will be in the indexes order.  You can take that knowledge and code accordingly.  However, I would always sort the table anyway, just to be safe.  Also, the next programmer to look at your code may not have as complete understanding of the query as you do and not realize he has to add a sort after he changes the query.  Or even worse, Trying to find the bug in a totally unrelated program as to why it quit working, when someone adds a new index to a table.

To my knowledge there is no command or FM that returns what the sort key of an internal table is.

[RaymondGiuseppi]

From my experience it'is depending on where clause and exact database manager level and SAP/OS kernel level, changing of database as well as level of db manager or kernel has sometimes changed the order of selected records, and surprised some young programmers. So, as Kesavadas Thekkillath wrote, better consider it as unpredictable and adjust your program.

Ex: from Note 743113 - iSeries: Known Issues with V5R3M0 during an upgrade of OS...

Correct the report(s) by adding the 'ORDER BY' clause to the
   SELECTs. If the clause is not specified then the records may be
   returned in a random sequence. Even if the records are usually
   returned in primary key sequence, you should not rely on this
   happening.

Regards,

Raymond

[Former Member]

Hi Raymond,

you are right. I will play it safe by just sorting those tables - that is done only once in my program while the tables are then accessed up to maybe 2000 times (field_texts in DD04T, once for every field in, like, 40 tables), so that is really worthwhile.

@ Larry,

You are also right. It is also in the interest of others reading my code to add the SORT command.

Such a command would be really handy - the last scripting_language I worked with had one called SEQUENCE and it was really handy to decide whether SORTing a table was a necessary "price" (in terms of performance) or not - that was before SSD disks that executed a program in seconds rather than minutes so tweaking became kind of superfluous...

Thanks for all the help! I'll close this now.

Best regards,

Sapperdapper

[wol]

Hello Friedrich,

this is a dangerous thing: Many developers think that table content is selected in order of primary key. But this is - as mentioned in the other answers - not always the case.

The thing is that on SELECTion you get just the order on DB. This is often but not always the order of the primary key. Especially if the key is a number this is most times the case.

There are two reasons known by me when the order changes:

1. DB-Reorganisation: Parts of the table data get moved, new table spaces are created...
2. If the Key is a generated Number from a number range: Numbers are not processed in order of creation. Because of buffering there is no order in the numbers you get.

--> Although often seen, one MUST NOT think it is fixed that SELECTion without order results in any predictable order!

Regards

Stefan Wolf