189 8069 5689
在Oracle 数据库中，我们经常需要去手工分析表和索引，还有某个模式下所有的表和索引等等。oracle 提供了两种分析方法，分别是dbms_stats 包和analyze 命令。这些是我们在日常项目管理中的真实使用的方法，供参考。
网站建设哪家好，找创新互联公司！专注于网页设计、网站建设、微信开发、小程序定制开发、集团企业网站建设等服务项目。为回馈新老客户创新互联还提供了石泉免费建站欢迎大家使用！
在10g 中分析某一个用户下所有的对象，如user_miki 用户
Sql代码 收藏代码
sys.dbms_stats.gather_schema_stats(ownname = 'user_miki',estimate_percent = 30,method_opt = 'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade = true,options = 'GATHER');
这是我在项目中分析一个用户所有对象的分析方法，取样比例为30% ，分析所有索引字段，生成柱形图，并且也分析索引。
(miki西游 @mikixiyou 文档，原文链接: )
在10g 中分析某一个用户下某一个表及其索引，如user_miki 用户下info 表和它的索引
Sql代码 收藏代码
execute dbms_stats.gather_table_stats(ownname = 'user_miki',tabname = 'info',estimate_percent = 30,method_opt = 'for all indexed columns size auto');
这是我在项目中单独分析一个用户中一张表的分析方法，取样比例为30% ，分析所有索引字段，生成柱形图，并且也分析索引。
我们在google 时，还能看到使用analzye 命令分析表的方法。
Sql代码 收藏代码
analyze table table_name compute statistics;
这个会将索引和表一起分析；
Sql代码 收藏代码
analyze table table_name compute statistics for table;
这个仅分析表；
analyze table table_name compute statistics for all columns;
这个仅分析表，和上面所有的字段，生成柱形图
analyze table table_name compute statistics for all indexed columns;
这个仅分析表，和上面所有建立索引的字段，生成柱形图
在9i 中，我们常常使用analyze 方法，在10g 以后，就改为dbms_stats.gather_schema_stats 类似的包来分析数据库对象了。
10g 的分析函数包比9i 的analyze 方法更灵活一些。
在10g 中，会有个系统的计划任务，每天10 点和周末两天都自动分析新的数据库对象和它认为统计信息已经过期的数据库对象。这个分析结果有时候不靠谱。
因此，我都采用10g 中提供的dbms_stats.gather_schema_stats 方法作为补充，设置一个计划任务定期去分析业务用户下所有数据库对象。
Sql代码 收藏代码
variable job number;
begin
sys.dbms_job.submit(job = :job,
what = 'sys.dbms_stats.gather_schema_stats(ownname = ''USR_MIKI'',estimate_percent = 30,method_opt = ''FOR ALL INDEXED COLUMNS SIZE AUTO'',cascade = true,options = ''GATHER'');',
next_date = to_date('16-12-2012 00:56:24', 'dd-mm-yyyy hh24:mi:ss'),
interval = 'sysdate+7');
commit;
end;
/
例如，设置为每周六运行一次。
分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后基于组计算某种统计值，并且每一组的每一行都可以返回一个统计值。
说白了,分析函数就是 over([query_partition_clase] order_by_clause)。比如说，我采用sum求和，rank排序等等，根据什么来呢？over提供一个窗口,使用partition by进行分组，在组内使用order by进行排序。over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用
分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。
用于合计的函数:
用于排列的函数:
其他:
而这样使用就是分析函数:
它们得出的结果是相同的，都是：
请注意，这里我们用到了distinct 关键字，如果不用distinct，第2个查询将返回20行数据，即earnings表的每行记录都将返回一行总收入，因为不用distinct的含义是：针对每个打工者计算他/她所在的月份和地区的总收入。
在这个例子中，聚合函数是更好的选择，但在另外一些情形下，我们更应该使用分析函数。下面通过几个实例来介绍排序分析函数的用途。
问题：统计每个月份,不同地区工资最高的前3名。
利用我们传统的聚合函数max可以方便地取出工资最高的一个员工,但是取出多个就无能为力了,同样，如果不分组我们可以通过排序取出工资最高的前3名，但无法实现对多个月份和地区的分组。而采用rank()分析函数，可以方便地实现我们的要求。
完整的语句如下:
结果为：
我们在开窗函数over()中使用earnmonth(月份)和area(地区)作为分组标志,并按照personincome(收入)倒序排列。
注意:RANK()函数有3组,分别是rank(), dense_rank(), row_number(),它们的区别是：
RANK()如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,比如：当第2名和第3名的利润相同时,rank的结果是1,2,2,4;而dense_rank()则不会跳过这个排名,结果是1,2,2,3;而row_number()哪怕是两个数据完全相同,排名也会不一样,结果是1,2,3,4.
完整的语句如下：
结果为：
完整的语句如下：
结果为：
结果为:
结果为:
在以上例子中,是用rollup()和cube()函数都会对结果集产生null，这时候可用grouping函数来确认该记录是由哪个字段得出来的.
grouping函数用法，带一个参数，参数为字段名，如果当前行是由rollup或者cube汇总得来的，结果就返回1，反之返回0.
完整语句如下:
结果为:
以上语句统计结果和如下语句使用group by的查询结果一样:
说明：Lag和Lead函数可以在一次查询中取出某个字段的前N行和后N行的数据（可以是其他字段的数据，比如根据字段甲查询上一行或下两行的字段乙），原来没有分析函数的时候采用子查询方法，但是比较麻烦:
语法如下：
其中：
统计每个打工者上个月和下个月有没有赚钱(personincome大于0即为赚钱):
结果为:
从目前来看，分析Oracle日志的唯一方法就是使用Oracle公司提供的LogMiner来进行，
Oracle数据库的所有更改都记录在日志中，但是原始的日志信息我们根本无法看懂，
而LogMiner就是让我们看懂日志信息的工具。从这一点上看，它和tkprof差不多，一个是用来分析日志信息，
一个则是格式化跟踪文件。
通过对日志的分析我们可以实现下面的目的：
1、查明数据库的逻辑更改；
2、侦察并更正用户的误操作；
3、执行事后审计；
4、执行变化分析。