If you don't specify a value for the method_opt parameter in
dbms_stats.gather_table_stats the default is used which is "FOR ALL
COLUMNS SIZE 1"
It's unlikely that you don't want any stats on any of your columns as
the optimiser finds this information very useful especially if the data
in the columns is particularly skewed, so check which fields are used
regularly in sql statements and just gather column stats on those
eg:
dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'MYTABLE',cascade=>true,estimate_percent=>5,method_opt=>'FOR
COLUMNS COL1 SIZE 1, COL 4 SIZE 5');
The columns you leave unspecified will not have any stats gathered for
them.
dimitris_at_cs.umb.edu wrote:
> Hi,
>
> It appears that when I tried to use dbms_stats.gather_table_stats
> without specifying the method_opt parameter, or by setting it to null,
> the procedure tries to compute some statistics for all the columns in
> the table. I know this because I did a SQL_TRACE and saw a query
> running on behalf of this procedure. Is there any way to produce
> table statistics without column statistics for any column.
>
> Thanks a lot,
>
> Dimitris
Received on Wed Oct 12 2005 - 10:12:15 CDT
