如何改变字段的统计信息

关键字：DBA_TAB_COL_STATISTICS DBMS_STATS set_column_stats

前几天优化一个sql语句，语句很长，里面有一段where条件status =1 or status is null,单独执行条件status=1 很快完成，但是单独使用status is null条件，执行计划就发生了变化，仔细询问，status的取值范围很小，仅仅0，1，2，null，我修改了回话的optimizer_index_cost_adj 参数，发现可以改变执行计划。适当的加大表分析以及直方图分析的取样数据，并不能使执行计划变好。

于是查询TAB_COL_STATISTICS，发现相关字段的NUM_NULLS很大，推测把这个数值修改小一些，就可以改变执行计划。查询DBMS_STATS,确定过程set_column_stats的参数：

-- Set column-related information

--

-- Input arguments:

-- ownname - The name of the schema

-- tabname - The name of the table to which this column belongs

-- colname - The name of the column

-- partname - The name of the table partition in which to store

-- the statistics. If the table is partitioned and partname

-- is null, the statistics will be stored at the global table

-- level.

-- stattab - The user stat table identifier describing where

-- to store the statistics. If stattab is null, the statistics

-- will be stored directly in the dictionary.

-- statid - The (optional) identifier to associate with these statistics

-- within stattab (Only pertinent if stattab is not NULL).

-- distcnt - The number of distinct values

-- density - The column density. If this value is null and distcnt is

-- not null, density will be derived from distcnt.

-- nullcnt - The number of nulls

-- srec - StatRec structure filled in by a call to prepare_column_values

-- or get_column_stats.

-- avgclen - The average length for the column (in bytes)

-- flags - For internal Oracle use (should be left as null)

-- statown - The schema containing stattab (if different then ownname)

EXECUTE SYS.DBMS_STATS.set_column_stats(OWNNAME=>user, tabname=>'XXX',colname=>'STATUS',nullcnt =>200,distcnt =>100);