Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Rebuilding Histograms - Single columns vs. all columns

Re: Rebuilding Histograms - Single columns vs. all columns

From: Brian Wisniewski <brian_wisniewski_at_yahoo.com>
Date: Tue, 15 Aug 2000 11:15:06 -0700 (PDT)
Message-Id: <10590.114638@fatcity.com>


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;


Do You Yahoo!?
Yahoo! Mail Free email you can access from anywhere! Received on Tue Aug 15 2000 - 13:15:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US