# FOR ALL INDEXED COLUMNS SIZE 254

From: Mladen Gogala <gogala.mladen_at_gmail.com>

Date: Mon, 12 Sep 2011 21:15:21 +0000 (UTC)

Message-ID: <pan.2011.09.12.21.15.20_at_gmail.com>

I have frequently used the method_opt from the title to analyze my tables and have been castigated for it with amazing regularity. I still don't quite understand why is that method bad. My logic is quite simple: The main decision that an optimizer has to make about the particular column condition is whether to use index to resolve it or not. That decision is based on the column statistics, as well as the the index statistics. The things that influence the decision are the condition itself, the existence of the histograms which helps to estimate the number of rows that need to be read, the clustering factor which will help estimate the number of blocks to read, the values from SYS.AUX_STAT$ to estimate the I/O cost and the statistics from the table itself. There are two possible decisions that optimizer can make with respect to the column: whether to use an index or not. There is no decision to be made if the index is not there, except with respect to join method, whether to use sort or hash. That means that using "for all indexed columns size 254" actually makes a lot of sense and it doesn't waste as much space as "FOR ALL COLUMNS SIZE 254". There is an option "FOR ALL COLUMNS SIZE SKEWONLY" which would be nice, except that I have no idea of the histogram size. This method_opt was called "a bane":

http://structureddata.org/2008/10/14/dbms_stats-method_opt-and-for-all- indexed-columns/

Date: Mon, 12 Sep 2011 21:15:21 +0000 (UTC)

Message-ID: <pan.2011.09.12.21.15.20_at_gmail.com>

I have frequently used the method_opt from the title to analyze my tables and have been castigated for it with amazing regularity. I still don't quite understand why is that method bad. My logic is quite simple: The main decision that an optimizer has to make about the particular column condition is whether to use index to resolve it or not. That decision is based on the column statistics, as well as the the index statistics. The things that influence the decision are the condition itself, the existence of the histograms which helps to estimate the number of rows that need to be read, the clustering factor which will help estimate the number of blocks to read, the values from SYS.AUX_STAT$ to estimate the I/O cost and the statistics from the table itself. There are two possible decisions that optimizer can make with respect to the column: whether to use an index or not. There is no decision to be made if the index is not there, except with respect to join method, whether to use sort or hash. That means that using "for all indexed columns size 254" actually makes a lot of sense and it doesn't waste as much space as "FOR ALL COLUMNS SIZE 254". There is an option "FOR ALL COLUMNS SIZE SKEWONLY" which would be nice, except that I have no idea of the histogram size. This method_opt was called "a bane":

http://structureddata.org/2008/10/14/dbms_stats-method_opt-and-for-all- indexed-columns/

So, why is this a bane? The explanation on the page is less than clear.

-- http://mgogala.byethost5.comReceived on Mon Sep 12 2011 - 16:15:21 CDT