Sanity Checks (1st April 2006)
Nested Loop sanity checks (1st April 2006)
Stats check (30th Dec 2005)
Page 460: I mentioned the multi-column (one-side) sanity check, but I didn’t mention the index sanity check. If the join columns match a multi-column index, then the distinct_keys in the index can be used as the join selectivity, rather than the details of the individual columns. This seems to be relevant only for unique indexes. Despite earlier comments about when it appeared, I have now realized that it is present all the way back to 18.104.22.168.
One of the interesting side effects of this sanity check is that you can drop an index which is not being used in an execution plan, and find that the plan changes because although Oracle chose not to use that index, it was using the distinct_keys value from that index in order to work out the best execution plan. There is an article on the main web site about this.
In the addendum to chapter 11, I have described the sanity check that appeared in 10g Release 2 for nested loops. If (outer cardinality * inner unit cost) exceeds the clustering_factor of the index on the inner table, then the value of the clustering_factor is used in the cost calculation. This means that when you upgrade from anything before 10.2.0.1 you may find that queries that used to perform hash joins from TableA to TableB will suddenly reverse direction and perform nested loop joins from TableB to TableA.
There isn’t really an appropriate place to post this note – but Volume 2 is going to examine statistics (and dbms_stats in particular), so it seems appropriate to mention the following in this appendix.
I’ve had an email from Juan Carlos Reyes Pacheco pointing out that Oracle has recently published a script on Metalink that scans the data dictionary in an attempt to identify statistics that are bad because of known bugs. The script can be found in note 337096.1. Currently it only handles a couple of problems, but it’s possible that it may grow in time – so keep an eye on it.