The bits of Oracle which aren’t documented always seem to be the bits that are hard to resist, so I thought I’d make a brief comment on undocumented hints.
Of course, you should not take advantage of any undocumented feature without first getting approval from Oracle support, but some hints seem to me to fall into a special category where you are more likely to get that approval – and here are my thoughts on why.
There seem to be four main reasons why some hints are not documented:
- There are hints that you’re absolutely not supposed to know about. One such example is the infamous no_trigger hint that appeared briefly in a version that I will deliberately leave unspecified (in case anyone is still using it). And if anyone posts the version number in a comment, I will delete it.
- There are hints which are actually internal hints that operate in situations that the end-user cannot manufacture. The index_rrs() hint that used to appear in some parallel query slave code (for parallel index fast full scans) was an example of this type of hint.
- There are hints which I assume are not yet documented because there is some beta code that is still subject to finalisation sitting in the production release. The hint may become official, it may cease to exist. The not-quite-documented selectivity() hint of 9i – which subsequently disappeared – may have been such a case.
- There are hints which never got into the (right place in the) documentation because no-one told the editors about it in time. I like to think that most of the undocunented hints like this – but I may be wrong. The dynamic_sampling_est_cdn() hint is probably an example of this type. It’s in the 9.2 Performance Tuning Guide and Reference – but not in the table of contents, the index, or the lists of hints given in the chapter on hints. Instead it appeared as an afterthought in the section on the dynamic_sampling() hint. It’s gone from the 10gR2 manual (and the documentation for hints has moved into the SQL Reference)
So when you look at undocumented hints, you have to ask yourself – are they supposed to be official, or will they soon be official, or are they actually official if only you can find where they’ve been documented.
Footnote: If you want to see a complete list of hints, including the version where they appeared, and the version where they got into the “stored outline” code, then 11g gives you the view v$sql_hint.