Re: Indexing NULL in the Oracle Database, is this the best practice?

On 01/03/2017 05:57 PM, Michael D O'Shea/Woodward Informatics Ltd wrote:
> Indeed Liz.
>
> There are of course hints and then there are hints. My deity sprinkles
> the latter category with caveats and comments like "Caution My
> experiments with the opt_estimate hint showed the effects changing
> across different versions of 10.1. Do not try using it as a hint on a
> production system until it is documented for public use". This doesn't
> fill me with much confidence at all, and at the time I very much
> appreciated the warning.
>
> ~
>
> Mike
> Long suffering Developer
>
>

Actually, during my tenure as a DBA, I've been using "CARDINALITY" hint, which is still not documented, AFAIK. I've last been a DBA in 2012. Cardinality hint is an excellent and cheap way to provide a cardinality estimate for a GTT to the optimizer. In the version 11G, there was no possibility to gather stats on a GTT. Since global temporary tables change constantly, I don't consider either private statistics or global statistics to be a viable option. An alternative was to specify the documented DYNAMIC_SAMPLING hint, which was much more expensive, as shown by tkprof. In that case your "don't use undocumented hints in production" principle doesn't hold water. The OPT_ESTIMATE hint is a messy new version which supersedes the CARDINALITY hint. I consider the mere fact that Oracle stubbornly refuses to document hints like that a very suspect business practice. I have my guesses about the motives for such a refusal and those guesses do not cast a positive light on whoever may be making the decision to keep both hints undocumented. If it is any consolation, the old CARDINALITY hint can still be specified. In version 12c, I will rather specify the good, old DYNAMIC_SAMPLING hint than mess with OPT_ESTIMATE. As the old proverb goes, there is more than one way to skin a query. By the way, thanks to Pythian for documenting this hint:

https://www.pythian.com/blog/oracles-opt_estimate-hint-usage-guide/

-- 
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com

--
http://www.freelists.org/webpage/oracle-l