ABAP DB hints and sorting order

[eddy_declercq]

Hi,

I've this discussion concerning DB hints and the effect after a HANA migration.

It's obvious that DB hints (in casu Oracle hits) with ABAP are ignored when the database is migrated to HANA.

The point of discussion is what happens with the sorting order of the result set when the DB hints are omitted. My feeling is that sort order can only be determined by the order by and not by the DB hints. Thus when an order by is absent, the sort order is the one of the DB and not the index pointed to in the BD hints. Is my feeling correct about this?

The result is of course that after migrating to HANA, the sort order of the result set in unreliable, unless one specifies an order by or sort it afterwards.

The whole point of discussion is whether or not the sort order of the result is influenced by the fact that the DB hint is ignored or the lack of having an order by. I wonder why DB hints are only considered as a priority 2 problem within the ATC.

Cheers

Eddy

[lbreddemann]

Hi Eddy,

when ABAP code relies on a specific sort order without specifying ORDER BY - that's a bug.

Always. Not a single exception.

Even with hints in place, Oracle cannot and does not guarantee any sorting. Think of what happens if the hinted index had been renamed or is unusable. Oracle will execute the query, silently ignoring the hint and producing whatever order of records comes along.

So, when you switch to a different DBMS, even a different Oracle version may be enough here, relying on such side-effects of hints can lead to massive problems.

I cannot comment on the severity rating in the ABAP checker, but I'd consider every single DB-hint to be at least worth a warning. After all, hints are often not well enough understood/documented and inherently unstable as an API. Using them requires re-validation of their actual effectiveness for every new version of the software (at least). On top they force the ABAP coder to know and understand the inner mechanics of the query optimizer, which is a complicated matter at least.

Hope that answers your question.

- Lars