ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 如何改变字段的统计信息

如何改变字段的统计信息

原创 Linux操作系统 作者:lfree 时间:2007-04-28 00:00:00 0 删除 编辑

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-83018/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2548
  • 访问量
    6333741