关键字：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);
