When I want to check the selectivity of a column or group of columns I
analyze our tables with only 1 bucket so I can get column-level
statistics in dba_tab_columns.
I use the following syntax. ... estimate statistics sample 10000 rows
for table for all columns size 1
the 'for table' clause just analyzes the table, not the associated
indexes, the 'for [all] column[s]' clause will also do histograms for
every column or for the columns you specify. size defaults to 75 so be
careful on how many 'buckets' you want created. You can check
dba_histograms to see how many are created for what columns in what
tables now. The syntax below will show the max created by column. If
size was 50 but there are very few distinct values for the column only
2 buckets may be created for this column.
col column_name for a30;
col table_name for a40;
select owner||'.'||table_name table_name, column_name, count(*)
num_buckets
from dba_histograms
where owner||'.'||table_name like upper('&1')
group by owner||'.'||table_name, column_name
having count(*) > 1;
- Brian
- Cherie_Machler_at_gelco.com wrote:
> I discovered over the weekend that we apparently have old histograms
> generated for every column in every table of one of our production
> databases.
> We don't need all of these histograms but the existence of the aged
> ones
> is fouling up the database. I'm not sure which ones can be safely
> removed
> at this point (I've been here less than two months) but while we're
> trying to
> figure that out, I've got to recreate them so they're at least fresh.
>
> I'm looking at the analyze command and the FOR clause to generate
> these histograms. I need a histogram recreated for every column in
> every table. Which clause is best to use:
>
> for table
> for table for column (repeat for every column in the table)
>
> If I just use the for table clause by itself, what will that do?
> Will it
> generate histograms
> for all columns in that table?
>
> Thanks,
>
> Cherie
>
>
> --
> Author:
> INET: Cherie_Machler_at_gelco.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Do You Yahoo!?
Yahoo! Mail – Free email you can access from anywhere!
Received on Tue Aug 15 2000 - 13:15:06 CDT
