METHOD_OPT => SIZE AUTO Quiz (Automatic For The People) August 31, 2011Posted by Richard Foote in Method_Opt Size AUTO, Oracle Indexes, Oracle Statistics.
OK, a nice, easy, simple one today. No tricks, honest
You have a table with 3 columns and lets say 1M rows.
Column 1 is effectively unique, so it has 1M distinct values. Let’s say 1 – 1000000, not that it really matters.
Column 2 has 254 distinct values, all evenly distributed so it has approximately the same number of rows for each value. Let’s say the values are 0-253 again it doesn’t really matter.
Column 3 is identical to Column 2, it also has 254 distinct values, all evenly distributed as well such that it also has approximately the same number of rows for each value. Let’s say the range of values are the same, 0-253, again it doesn’t really matter.
You have various queries in the database in which all 3 columns are referenced somewhere in WHERE conditions (eg. WHERE Column1 = 42).
You then insert just one row that has the following values based on our example: VALUES (1000001, 42, 99999999).
The key points here is that for Column1, it’s just another unique value, just 1 greater than the previous maximum value. Nothing special.
For Column2, it’s just another of the existing 254 values that doesn’t really change the even distribution of the data. Nothing special.
However, for Column 3, it’s not only a new value that didn’t previously exist (and so there’s just the one row with this value in the data, whereas all the other values correspond to roughly 1/254 of the rows) but it’s also a value that is way way way outside the normal range of existing values (nothing comes close to having a value of 99999999).
OK, we have the scenario, hopefully you can see where I going with this.
You decide to collect fresh statistics with DBMS_STATS, you want them to be completely accurate so you use a 100% sample size (or compute with estimate_percent=>null). But because you want to get with the AUTO program and make life easier for yourself, you decide to let Oracle work out which columns might require and benefit from a histogram by using METHOD_OPT=>’ FOR ALL COLUMNS SIZE AUTO’.
Now finally comes the question. Of the three columns, only one column will have a histogram. Which one and why is it so ?
If you understand how Oracle collects statistics, the answer will hopefully be obvious