Chinaunix首页 | 论坛 | 博客
  • 博客访问: 6683529
  • 博文数量: 1005
  • 博客积分: 8199
  • 博客等级: 中将
  • 技术积分: 13071
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-25 20:19
个人简介

脚踏实地、勇往直前!

文章分类

全部博文(1005)

文章存档

2020年(2)

2019年(93)

2018年(208)

2017年(81)

2016年(49)

2015年(50)

2014年(170)

2013年(52)

2012年(177)

2011年(93)

2010年(30)

分类: Oracle

2011-09-06 11:11:23

1.分析整个schema,分析整个schema下的表和索引
  1. Begin
  2.   Dbms_Stats.Gather_Schema_Stats(Ownname => 'HXL',
  3.                                  Estimate_Percent => Dbms_Stats.Auto_Sample_Size,
  4.                                  Method_Opt => 'for all columns size repeat',
  5.                                  Degree => 2,
  6.                                  Cascade => True);
  7. End;

A.参数method_opt

METHOD_OPT - The value controls column statistics collection and histogram creation. It accepts either of the following options, or both in combination:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
column is defined as column := column_name | extension name | extension

integer : Number of histogram buckets. Must be in the range [1,254].
REPEAT : Collects histograms only on the columns that already have histograms
AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns
SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns
column_name : name of a column
extension : can be either a column group in the format of (column_name, colume_name [, ...]) or an expression
The default is FOR ALL COLUMNS SIZE AUTO.

字段收集信息后再如下视图中有体现

USER_TAB_COL_STATISTICS
USER_PART_COL_STATISTICS
USER_SUBPART_COL_STATISTICS

B.参数cascade

The value determines whether or not index statistics are collected as part of gathering table statistics

2.删除schema下的统计信息

  1. Begin
  2.   Dbms_Stats.Delete_Schema_Stats(Ownname => 'HXL');
  3. End;
阅读(2896) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~