Re: FOR ALL INDEXED COLUMNS SIZE 254
Date: Tue, 13 Sep 2011 14:05:11 +0000 (UTC)
Message-ID: <pan.2011.09.13.14.05.11_at_gmail.com>
On Mon, 12 Sep 2011 15:45:27 -0700, Charles Hooper wrote:
> Why not index the STATUS column in my example? You could, but then what
> if you are looking for something different, like sales in the state of
> Alaska (probably much fewer than for California) - would you necessarily
> index a column that indicates the state of a customer? (On second
> thought, I probably would not have thought to manually build a histogram
> on this column either, but the statistics collection process might).
Charles, the issue here is how to collect statistics, the regular job of collecting statistics. In Oracle11g, it's not a problem any longer because I have SET_TABLE_PREFS routine which can help me with setting the preferences for the individual table. However, collecting stats for all columns will consume a fairly large amount of space, that is something that I don't necessarily want to do. In oracle10g, I solved the problem by disabling the built-in database collecting stats and writing my own script, which would run every Saturday at 9PM. This new procedure would run "GATHER_DATABASE_STATS" followed by special treatment for 14 tables. The "special treatment" included changing the method to "FOR ALL COLUMNS SIZE 254" for some tables, as well as dividing the clustering factor by 100 for some indexes. The problem is what to collect by default, without wasting too much space.
-- http://mgogala.byethost5.comReceived on Tue Sep 13 2011 - 09:05:11 CDT