Database hints: not a panacea, but not the devil either
When trying to improve the performance of their programs ABAP developers have a difficult relationship with database hints: they usually go from never to often and afterwards to never again. This post is to try to bring those in the third phase to a forth use-them-when-appropriate phase.
The first never phase is caused by sheer ignorance. Junior abapers often don’t even know what an explain plan is, let alone a database hint. Ergo, they never use them.
The second is a often phase, or maybe a too often phase. As abapers begin to see that sometimes the database optimizer does not behave exactly as they expect, database hints are an obvious and easy solution, and it is often not easy to resist the temptation to force a badly behaved optimizer to work as we want it to. After all, the programmer should be in charge, right?
The third phase is when the developer realizes that by blindly adding hints and therefore trying to outsmart the optimizer, he will often make more bad than good. In his classic book +Economics in One Lesson +Henry Hazlitt says that “economics is the science of seeing general consequences”, and I would argue that applies perfectly to performance optimization. The problem, of course, is that these general consequences are often quite hard to anticipate: you try to improve for a specific situation and you end up making it worse in other cases. The natural reaction? To be afraid, to be very afraid of database hints, and so go back to never using them. The most experienced abapers usually recommend that database hints should only be used as a last resort (which is a sensible recommendation), and the wisest among us understand this basically as a +never use it +recommendation.
So should database hints ever be used? Well, yes, if you understand exactly what you are doing. Lately I have had to solve two similar performance problems, and I believe they are cases where hints are a good solution.
The first case was a select to a Z table (the real select was a bit more complicated, but let’s keep it simple here):
WHERE aufnr = p_aufnr
AND charg = ' '.
The primary key for ZTABLE is order number and item (AUFNR and POSNR), and there is a secondary index by batch (CHARG). For this query the optimizer chooses the index by CHARG because the batch is very selective (even more selective than the order number). The problem is that the batch is very selective except when we are looking for rows with no batch (as is the case here). A little over 6% of the rows have no batch, so AUFNR would be much more selective. Database hints allow us to easily force Oracle not to use the index by batch in this case.
WHERE aufnr = p_aufnr
AND charg = ' '
* do not use index by batch because it is bad for this case (charg = ' ')
%_HINTS ORACLE ‘NO_INDEX(“&TABLE&” “ZTABLEBAT”)’.</p></pre><p>Notice that we are not telling Oracle what to do, but rather what not to do. This is often better since it still gives Oracle some freedom to choose the best plan. In this case Oracle chooses the primary key (AUFNR + POSNR), which is a pretty good choice.</p><p> </p><p> </p><p>Our second problem was similar. This time, however, it was already coded with a dynamic where clause so that if batch is empty then it is not considered. Here is (a simplified part of) the code:</p><pre> IF NOT <charg> IS INITIAL.<p> l_where = ‘charg = <charg>’.</p><p> APPEND l_where TO lt_where.</p><p> ENDIF.</p><p> </p><p> SELECT </p><p> INTO TABLE lt_mseg</p><p> FROM mseg </p><p> WHERE werks = <werks></p><p> AND matnr = <matnr></p><p> AND (lt_where).</p></pre><p>Table MSEG has a Z index by CHARG. Again, MSEG has lots of entries with empty CHARG (for non-batch managed materials), but here we were never adding CHARG = ‘ ‘ to the where clause. So what is the catch? For non-batch managed materials +with split valuation +then field CHARG will end up with the valuation type (BWTAR), and that is also a very unselective value. The solution here was to add the same hint as before, but only when we are dealing with non batch managed materials.</p><pre> IF NOT <charg> IS INITIAL.<p> l_where = ‘charg = <charg>’.</p><p> APPEND l_where TO lt_where.</p><p> ENDIF.</p><p> </p><p> IF <xchpf> IS INITIAL.</p><p> l_hints = ‘NO_INDEX(“&TABLE&” “MSEGZ01″)’.
Quick conclusions then: (1) while database hints should be used with care and by experienced abapers only, they are not necessarily the root of all evil; (2) instead of forcing the path to use it is often safer to force not to use a certain path; and (3) if the hint only applies in certain cases then they can be added dynamically as shown in the last example.</p>