Skip to Content
Author's profile photo Lars Breddemann

Make query hints great again…

In case you ever got frustrated by how 80’s like the SAP HANA hints infrastructure looks like, go and support my improvement idea on SAP Idea place:  SAP Customer Influence site: Make query hints great again

Go and upvote! Now! 🙂


[Update 23.1.2017]: After some of the folks that I really liked to review my idea reported that they don’t have access to it, I decided to put the idea text as a copy in here as well.
So here it is:

Currently (HANA 1 SPS12/HANA 2 SP0), SAP HANA supports the usage of query hints via information view parameters or – most commonly used – the WITH HINT query parameter.

Most of the times, the hints are used to avoid a specific bug in SAP HANA and are only applicable to a specific version/revision or application state.

For all practical matters, the hints don’t serve a functional purpose for any application but are necessary band-aids to enable the application to run successfully on SAP HANA.

As many bugs do get fixed with later SPS/revisions, the hints could and should be reviewed in order to allow the application leverage the formerly disabled feature again.

Right now, this means, that a developer/system operator has to manually document and keep track of all hints used in the system, the reason for the usage, the HANA revision the hint applies, maybe a incident numbers where the hint usage had been discussed and probably a target HANA version when the hint shouldn’t be required anymore.

This task is obviously not delivering a lot of value for any developer, so in reality, a hint, once introduced, will likely stay in place forever, as nobody remembers what it was used for and if it’s safe to remove it now.

The proposal is as follows:

Instead of a simple WITH HINT syntax, allow for a more comprehensive hint management that allows for identifying a specific statement (e.g. provide a custom statement identifier in a statement annotation or comment) and provide HANA function to specify a set of hints for any statement ID, including the already mentioned metadata.

Upon SQL query compilation SAP HANA would automatically match the statement ID against the maintained statement hints (this could be a single table structure) and include the hint into the statement execution.

The fact that this happened (and against which statement id hint setting this match had been performed) should be visible in the usual query execution analysis tools like EXPLAIN PLAN or PLANVIZ.

The proposed solution has the following benefits:

Application code does not need to be modified to add hints, as long as the statements can be uniquely identified.

The hint application can be controlled centrally, without the involvement of the original developer – essentially the hint can be applied


  • operations/support staff
  • The query compiler in HANA can automatically check for the valid HANA versions and ignore the hint for non-matching versions
  • The hints for a given installation can be transported like a configuration data set across multiple servers
  • The central list of statements with hints settings allows an efficient follow-up of HANA issues and – given an option to ignore the hint list during plan compilation – also allows for very efficient testing of whether a hint is still required or not.

I haven’t found an obvious way to contact support for the SAP Ideas Place, so my best bet is that in order to view any idea a registration is required.

  • Lars


Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      This is the whole blog? C'mon, Lars...

      Idea promotions are posted as [Status update] in the Coffee Corner. That's where all the cool kids are on SCN these days anyway. 🙂

      Author's profile photo Lars Breddemann
      Lars Breddemann
      Blog Post Author

      Yep, that's the whole content and I would have loved to not use the blog function for that. Unfortunately, the discussion forums are a thing of a past and this isn't really a "gimme the answer" question.

      Using the Coffee Corner for this doesn't make sense to me - this idea is about HANA so it seems to fit into the HANA tag quite well...

      Anyway, say Hi to the cool kids, when you meet them!


      Author's profile photo Benedict Venmani Felix
      Benedict Venmani Felix

      When I saw 'Lars Breddemann' and 'query hints' in my activity stream....I got excited.

      Got myself a nice cup of tea, pulled my chrome tab from my laptop screen to my big monitor for better reading and boo hoo hooo...what a disappointment on a fun Friday morning

      And if that wasn't enough, I go this from the idea place


      Author's profile photo Lars Breddemann
      Lars Breddemann
      Blog Post Author

      Hi Benedict,

      you weren't the only one who couldn't access the idea in Idea Space - I suspect one needs to register first. Could you try that and let me know if this fixes the problem? (would be half-decent if the error message would let one know what to do about the error...).

      In case this access issue is one of the 1DX related topics that may or may not be resolved anytime soon, I copied the idea text into this blog post so that everyone can review it.

      Sorry about this,


      Author's profile photo Benedict Venmani Felix
      Benedict Venmani Felix

      Thank Lars. I am able to log-in to the idea place and check my activity stream, but as mentioned in the error, I may not have access to the 'SAP HANA Platform space'.

      I 'liked' this blog. We can count it towards one vote 🙂

      Author's profile photo Lars Breddemann
      Lars Breddemann
      Blog Post Author

      Seems to have helped - the idea is now on the "For Future Consideration" (aka recycle later) pile.