ITPUB论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

楼主: newkid

[翻译]Jonathan Lewis 关于直方图的系列文章(33楼更新至第三篇) [复制链接]

精华贴数
13
专家积分
2176
技术积分
92843
社区积分
21122
注册时间
2008-1-16
论坛徽章:
344
比亚迪
日期:2013-07-30 17:57:36三菱
日期:2013-09-29 09:46:51比亚迪
日期:2013-10-10 18:45:31劳斯莱斯
日期:2013-10-20 20:22:38奔驰
日期:2013-10-25 18:50:53宝马
日期:2013-10-30 12:02:48宝马
日期:2013-11-18 10:01:57奔驰
日期:2013-11-18 14:06:28路虎
日期:2013-11-18 14:24:33宝马
日期:2013-11-22 20:41:54比亚迪
日期:2013-09-22 19:26:17劳斯莱斯
日期:2013-09-11 16:38:36
发表于 2013-9-15 05:46:19 |显示全部楼层
newkid 发表于 2013-9-14 10:19
我只挑自己感兴趣的,oo也来翻几篇吗。

关于压缩我觉得没啥可译的
tom博客最近也不更新了
我的新浪微博,目前被冻结:http://weibo.com/lu01

译作Oracle PL/SQL实战已经上架销售
网购地址:china-pub|京东图灵社区(源代码)卓越亚马逊当当

剑破冰山—Oracle开发艺术 已经上架销售
网购地址:互动|京东电子工业出版社书店卓越亚马逊当当华储
在线阅读:5lcto华储
源代码:博文视点ITPUB

使用道具 举报

注册会员

咖啡猫

精华贴数
3
专家积分
167
技术积分
5375
社区积分
12762
注册时间
2006-5-14
论坛徽章:
110
奥迪
日期:2014-05-13 20:14:25一汽
日期:2013-11-18 15:42:59凯迪拉克
日期:2013-11-18 15:43:22奔驰
日期:2013-11-18 11:23:08马上加薪
日期:2014-07-26 10:56:47奔驰
日期:2013-11-26 19:05:46雪铁龙
日期:2013-11-28 10:51:342014年世界杯参赛球队: 比利时
日期:2014-07-28 16:06:24SQL大赛参与纪念
日期:2013-12-06 14:03:45保时捷
日期:2013-12-11 20:56:08Jeep
日期:2013-11-18 15:42:59奔驰
日期:2013-11-18 15:42:59
发表于 2013-9-15 09:34:20 |显示全部楼层
感谢大师的翻译!
猫言猫语:
             人生无非是笑笑人家,再被人家笑笑而已.
             男人是用来靠的,所以要可靠;女人是用来爱的,所以要可爱.
             发怒一分钟便失去六十秒的幸福.
             人品就象内裤,表面看不出来,如果你没有还显摆,就挺讨厌.
             ......

使用道具 举报

版主

资深新手

精华贴数
25
专家积分
1777
技术积分
43269
社区积分
254
注册时间
2004-6-26
论坛徽章:
177
复活蛋
日期:2013-06-13 22:20:06奥运会纪念徽章:乒乓球
日期:2012-10-20 00:13:42ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:37奥运会纪念徽章:体操
日期:2012-10-08 11:34:22奥运会纪念徽章:自行车
日期:2012-10-08 11:34:12奥运会纪念徽章:跆拳道
日期:2012-09-18 13:36:08奥运会纪念徽章:蹦床
日期:2012-09-10 10:35:46奥运会纪念徽章:手球
日期:2012-10-22 18:05:08奥运会纪念徽章:铁人三项
日期:2012-08-29 21:35:02奥运会纪念徽章:羽毛球
日期:2012-12-10 17:55:46奥运会纪念徽章:棒球
日期:2012-12-10 17:55:46奥运会纪念徽章:艺术体操
日期:2012-12-10 17:55:46
发表于 2013-9-15 10:49:06 |显示全部楼层
stevendba 发表于 2013-9-14 19:44
status           COUNT(*)
        C                529,100
        P                    3 ...

如果是C或者X,这时我们是不希望用索引的,直接写 status='C' 或 status='X' 就可以了。

世界上只有两种编程方法:Oracle的方法和错误的方法。

剑破冰山—Oracle开发艺术 即将隆重推出
http://www.china-pub.com/197199
http://www.huachu.com.cn/itbook/itbookinfo.asp?lbbh=10114321

使用道具 举报

精华贴数
0
专家积分
5
技术积分
404
社区积分
2
注册时间
2011-9-7
认证徽章
论坛徽章:
12
ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:00红宝石
日期:2014-06-03 13:13:19马上有对象
日期:2014-04-09 11:35:46马上有对象
日期:2014-02-18 16:44:082014年新春福章
日期:2014-02-18 16:44:08兰博基尼
日期:2013-12-26 20:23:03法拉利
日期:2013-12-25 19:17:01优秀写手
日期:2013-12-18 09:29:13问答徽章
日期:2013-10-24 14:58:00马上有对象
日期:2014-08-18 10:22:372013年新春福章
日期:2013-02-25 14:51:242014年世界杯参赛球队: 比利时
日期:2014-06-18 15:55:16
发表于 2013-9-15 13:48:55 |显示全部楼层
本帖最后由 stevendba 于 2013-9-15 13:50 编辑
newkid 发表于 2013-9-15 10:49
如果是C或者X,这时我们是不希望用索引的,直接写 status='C' 或 status='X' 就可以了。

谢谢,明白了,SQL需要拼装。

使用道具 举报

注册会员

橙子长大

精华贴数
0
专家积分
76
技术积分
2238
社区积分
15
注册时间
2011-2-24
论坛徽章:
7
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:29ITPUB 11周年纪念徽章
日期:2012-10-09 18:16:002013年新春福章
日期:2013-02-25 14:51:24迷宫蛋
日期:2013-03-06 17:43:59鲜花蛋
日期:2013-04-26 22:57:09蛋疼蛋
日期:2013-06-05 15:38:56林肯
日期:2013-08-16 16:46:32
发表于 2013-9-22 18:50:29 |显示全部楼层






王者伐道,智者伐交,武者伐谋

Sunner:everytime kissed
http://blog.sina.com.cn/oraerror
Oracle Online Documents

使用道具 举报

版主

djゆoracle

精华贴数
24
专家积分
1911
技术积分
46414
社区积分
61850
注册时间
2006-7-28
论坛徽章:
1025
金色在线徽章
日期:2007-04-25 04:02:08金色在线徽章
日期:2007-06-29 04:02:43金色在线徽章
日期:2007-03-11 04:02:02在线时间
日期:2007-04-11 04:01:02在线时间
日期:2007-04-12 04:01:02在线时间
日期:2007-03-07 04:01:022008版在线时间
日期:2010-05-01 00:01:152008版在线时间
日期:2011-05-01 00:01:342008版在线时间
日期:2008-06-03 11:59:43ITPUB年度最佳技术原创精华奖
日期:2013-03-22 13:18:30ITPUB年度最佳技术原创精华奖
日期:2012-03-13 17:12:05ITPUB年度最佳技术原创精华奖
日期:2011-04-08 18:37:54
发表于 2013-9-22 22:25:34 |显示全部楼层
第2部分出来了吗??
My BLOG: http://blog.chinaunix.net/uid/7655508.html

ORACLE是个分外妖娆的女人,她总能勾起你的欲望,去探索她!
                                                                                     --BY DJ
About me:optimistic,passionate and harmonious
focus on  oracle sql and plsql programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc


长期不限数量收鲨鱼章和各种蛋章,600每个,卖的短消息我

使用道具 举报

注册会员

初级会员

精华贴数
0
专家积分
0
技术积分
66
社区积分
162
注册时间
2005-7-5
论坛徽章:
2
2014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11
发表于 2013-9-23 21:08:09 |显示全部楼层
不错,不错,不错

使用道具 举报

版主

资深新手

精华贴数
25
专家积分
1777
技术积分
43269
社区积分
254
注册时间
2004-6-26
论坛徽章:
177
复活蛋
日期:2013-06-13 22:20:06奥运会纪念徽章:乒乓球
日期:2012-10-20 00:13:42ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:37奥运会纪念徽章:体操
日期:2012-10-08 11:34:22奥运会纪念徽章:自行车
日期:2012-10-08 11:34:12奥运会纪念徽章:跆拳道
日期:2012-09-18 13:36:08奥运会纪念徽章:蹦床
日期:2012-09-10 10:35:46奥运会纪念徽章:手球
日期:2012-10-22 18:05:08奥运会纪念徽章:铁人三项
日期:2012-08-29 21:35:02奥运会纪念徽章:羽毛球
日期:2012-12-10 17:55:46奥运会纪念徽章:棒球
日期:2012-12-10 17:55:46奥运会纪念徽章:艺术体操
日期:2012-12-10 17:55:46
发表于 2013-9-23 22:35:26 |显示全部楼层
dingjun123 发表于 2013-9-22 22:25
第2部分出来了吗??

跳票了,到现在也没个影,可能他在忙OOW

世界上只有两种编程方法:Oracle的方法和错误的方法。

剑破冰山—Oracle开发艺术 即将隆重推出
http://www.china-pub.com/197199
http://www.huachu.com.cn/itbook/itbookinfo.asp?lbbh=10114321

使用道具 举报

精华贴数
0
专家积分
22
技术积分
1596
社区积分
2264
注册时间
2008-4-21
论坛徽章:
113
2014年世界杯参赛球队: 日本
日期:2014-06-26 17:04:59生肖徽章:羊
日期:2013-12-06 14:15:45生肖徽章:马
日期:2013-12-06 14:15:45生肖徽章:蛇
日期:2013-12-06 14:15:45生肖徽章:龙
日期:2013-12-06 14:15:45生肖徽章:兔
日期:2013-12-06 14:15:45生肖徽章:虎
日期:2013-12-06 14:15:45生肖徽章:牛
日期:2013-12-06 14:15:45生肖徽章:鼠
日期:2013-12-06 14:15:45SQL大赛参与纪念
日期:2013-12-06 14:03:45SQL数据库编程大师
日期:2013-12-06 13:59:43现代
日期:2013-11-04 19:11:58
发表于 2013-9-24 10:57:23 |显示全部楼层
之前兔子发了一贴也是将这个东西的
倾斜直方图的时候用函数索引,利用索引不存null的特性。

根据我看的文章,如果数据分布已知的话,而且固定查很少的值。是不是可以固定执行计划,表就不需要收集统计信息了?

使用道具 举报

版主

资深新手

精华贴数
25
专家积分
1777
技术积分
43269
社区积分
254
注册时间
2004-6-26
论坛徽章:
177
复活蛋
日期:2013-06-13 22:20:06奥运会纪念徽章:乒乓球
日期:2012-10-20 00:13:42ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:37奥运会纪念徽章:体操
日期:2012-10-08 11:34:22奥运会纪念徽章:自行车
日期:2012-10-08 11:34:12奥运会纪念徽章:跆拳道
日期:2012-09-18 13:36:08奥运会纪念徽章:蹦床
日期:2012-09-10 10:35:46奥运会纪念徽章:手球
日期:2012-10-22 18:05:08奥运会纪念徽章:铁人三项
日期:2012-08-29 21:35:02奥运会纪念徽章:羽毛球
日期:2012-12-10 17:55:46奥运会纪念徽章:棒球
日期:2012-12-10 17:55:46奥运会纪念徽章:艺术体操
日期:2012-12-10 17:55:46
发表于 2013-10-4 02:07:37 |显示全部楼层
本帖最后由 newkid 于 2014-1-28 01:09 编辑


直方图系列文章(二)

Jonathan Lewis 发表于2013年10月1号

原文链接:
http://allthingsoracle.com/histograms-pt-2/
(经作者同意发布中文版,如有转载请注明出处)

在这个系列的第一篇中,我们讨论了为什么我们可能需要创建直方图来帮助优化器处理倾斜分布的数据。我们用了一张订单表(orders)中的一个简单的状态列(status)作为例子来展示这个规律,特别讨论了频度直方图,并且着重指出了几个直方图相关的问题。

在第二篇中,我们将会花些时间来讨论截至Oracle版本11g为止唯一的另一种直方图:等高直方图。等高直方图可能在几种数据形态下会有用,我们将会在第三篇文章中讨论。但我们将会以一个例子作为开始,在这例子中我们的数据不允许Oracle创建频度直方图,并且等高直方图可能成为一种障碍而不是便利,我们将会描述一种策略(你在12c中将不再需要)来绕过这个限制。在这个过程中我们会查看一个很简单的例子,它展示了Oracle用于产生等高直方图的算法。

一个简单的例子

这里是关于一些重要的客户数据的报表(当然,已经屏蔽处理过了),显示了一个特别重要的列的值的不均匀分布:

select
         specifier, count(*)
from
         messages
group by
         specifier
order by
         count(*) desc
;    -- 表中有 10M (一千万)行数据

SPECIFIER  COUNT(*)
BVGFJB          1,851,177
LYYVLH            719,582
MTVMIE            672,823
YETSDP            659,661
DAJYGS            504,641
...
KDCFVJ             75,328
JITCRI             74,104
DNRYKC             70,029
BEWPEQ             68,681
...
JXXXRE                  1
OHMNVU                  1
YGOBWQ                  1
UBBWQH                  1

352 rows selected.

显然,在这里我的值分布是很不均匀的,所以直方图可能就很有用。不幸的是总共有352个不同的值,这已经超出Oracle的限制(到12c之前),所以如果我创建了一个直方图,那么它就必须是一个等高直方图。

为了创建等高直方图,Oracle开始先对数据排序,然后选出第一行和每个第N行(这里N=“列中非空值的个数”除以“要求的桶(bucket)的个数”)。在我们的样本数据中,我们在排序后的数据中差不多每隔40,000行选出一行(10M/254)。(译者注:12c之前最大桶数为254, 即数据最多可被分成254组)

每个选中的数被称作端点值,每个数字都被标上编号(称作端点编号),从0至“桶的个数”。使用这个算法,一个值如果在数据中出现的频度足够大,那么它就会被选中很多次,这就允许Oracle将其识别为“频繁”值。在存储直方图选择值的时候,Oracle并不重复保存端点值——如果一个值在列表中出现许多次,它只会被存储一次,取最高的端点值标号。

为了展示涉及到的机制,以及随后的基数计算,我将采用下列(已经排序)的20个值并且建立一个五个桶的直方图:
5, 6, 6, 6, 9, 11, 11, 12, 12, 12, 12, 12, 13, 13, 13, 13, 13, 16, 17, 17

取出第一个,以及每4个取一次 (20 / 5 = 4)
5, 6, 6, 6, 9, 11, 11, 12, 12, 12, 12, 12, 13, 13, 13, 13, 13, 16, 17, 17

保存起来,从0开始标号,因为我们已经包含了列表中最小的原始值:
0    5
1    6
2    12
3    12
4    13
5    17

把重复值中早出现的去掉:
0    5
1    6
3    12
4    13
5    17

这就是Oracle为原始数据集所存储的等高直方图。从中可推断出12是个常见的值(它出现了不止一次);但是,即使我们能看出12和13的个数是一样多的,Oracle也不能推断出13是个常见的值。你可以考虑将其中的一个11改为16——如果你这么做,12就不再作为频繁值出现,而13变成了频繁值;反之,如果你把一个16变成11, 那么12和13都不会作为频繁值出现。

至于基数计算,Oracle对于频繁值采用“计算桶数”——在我们这个小小的例子中,我们选择了每个第四行,我们的“桶”的大小为4。我们知道在整个直方图中12出现了两次,所以Oracle将谓词“n1=12”的基数计算为8 (也即:2个桶*每桶4行)。对于那些非频繁值,优化器计算基数的方法一直在改变——在版本11.2.0.3中,有一种计算方法是很简单的:非频繁的行数/非频繁值的个数


在我们的例子中,计算如下:



频繁值:1个        (12)
非频繁值:7个        (5,6,9,11,13,16,17)
频繁值总行数:8        (2 个桶,每桶4行)
非频繁值总行数:12        (20 – 8)
非频繁值基数 = 12/7 = 1.714, 进位为2。

当然,计算有复杂的变种,特别是当Oracle使用了采样数据来建立直方图,优化器就必须将它的计算乘以一个系数加以放大,以反映表中的相关行数以及直方图所捕获到的行数之间的差异。

记住这个小小的例子,然后我们来考虑我客户的一千万行和352个值的数据。

精确性很重要

我们有一千万行数据——并且我们为了创建等高直方图所做的第一件事就是对数据排序,所以第一步是相当耗费资源的。如果我们对数据采样,这会减少排序的数据量——但是这个采样可能会漏掉很多值,以至于Oracle以为能够创建一个频度直方图——那样就会使得优化器对某些实际上有几千行的值做出过低的估算。

对数据排序之后,当我们每隔40,000取一行,我们的“常见”值将有多少个可能会被视为常见?我们最常见的值有1.8M行,这相当于46个桶,接下来的三个值占去了63个桶,总共才剩下155桶——既然识别一个频繁值需要两个桶,我们最多只能识别另外72个频繁值——剩下的东西将一概被处理为“平常值”。

实际上,这个频度直方图和等高直方图之间的灰色地带是最令人头疼的特性之一。频度直方图可以处理254个频繁值,但如果Oracle不得不切换到等高直方图,它最多只能识别127(即 254/2)个频繁值——如果你的数据超出这个频度限制一点点,你的精确度可能急剧下降。

稳定性是另一个问题——在我的列表中间,我报了几个数值,每个差不多有70,000行。这些会被捕获到直方图中吗?既然一个桶代表40,000行,一个有40,000行的值就可能被我们选中两次,另一方面一个具有79,999行的值却可能无法出现两次。你只需每天增加或者删除几行,这些值就会在你收集直方图的时候“随机地”出现或者消失。当我具体分析数据集时,我得出了下列结论——基于100%的取样:

有25个值每天都一定会被捕获(每个值多于80,000行)
有35个值可能这一天被捕获,下一天就消失了(40,000至80,000行)

等高直方图可能很不稳定——如果这35个值的任何一个是用户真正感兴趣的,那么它们的执行计划可能在每次收集直方图的时候剧烈地变化。取样的随机性使得事情雪上加霜,如果我们不用100%的数据来采集直方图的话。

但是详细分析数据也揭示了另一方面:
最频繁的140个值占据了数据的99%。
最频繁的210个值占据了数据的99.9%。
最频繁的250个值占据了数据的99.98%。
最后的102个值占据了10M行数据中的大约2,000行

(顺便请记住,我们无论如何最多只能够捕获81(77+3+1)个频繁值,因为最频繁的那些值占用了多数的桶。)

我们在表中有352个不同的值,但是其中的102个基本上是看不到的。如果我们能够在最频繁的250个值上创建频度直方图(必要的话把这列的最高、最低值也加入),并且告诉Oracle如何假设其他任何一个值平均只有20行,岂不是一件很美妙的事?顺便说一下,那正是12c的Top-N直方图所干的事,如果我们觉得这确实重要,只需要几个dbms_stats包的调用就可以做到。

伪造频度数据

让我们回顾一下前一篇文章所使用的数据集,我们用它来展示如何建立一个简单的频度直方图,当我们对想使用的值很了解的时候。记得我们有一个状态值的列,它的分布看起来是这样子的:
S       COUNT(*)
C        529,100
P            300
R            300
S            300
X        500,000

既然这些值是一个很小的集合,我们可以高兴地说,不管什么时候我们需要优化器来运行我们的关键查询,这个集合的数字已经是数据的一个“足够好”的模型,每当Oracle常规的统计信息收集机制在这张表上创建了新的统计信息,很容易就可以写一个过程来重新创建频度直方图。

代码依赖于包中三个关键的过程:get_column_stats(), prepare_column_stats() 和 set_column_stats(), 并且假设表的统计信息(某些方面的描述信息)已经被收集了,因为它需要将表的当前统计信息读取到本地变量中,修改变量然后把它们写回到数据字典中。

declare

     m_distcnt       number;         -- num_distinct
     m_density       number;         -- density
     m_nullcnt       number;         -- num_nulls
     m_avgclen       number;         -- avg_col_len
     srec            dbms_stats.statrec;         -- stats record
     c_array         dbms_stats.chararray;       -- varchar2 array

begin

     dbms_stats.get_column_stats(
          ownname         => user,
          tabname         => 'orders',
          colname         => 'status',
          distcnt         => m_distcnt,
          density         => m_density,
          nullcnt         => m_nullcnt,
          srec           => srec,
          avgclen         => m_avgclen
     );

     m_distcnt  := 5;                 -- 设置我自己的 num_distinct 值
     m_density  := 0.00001;           -- 用于那些“被漏掉的值”
     srec.epc   := 5;                 -- 直方图的桶数

     c_array    := dbms_stats.chararray(
                     rpad('C',15),
                     rpad('P',15),
                     rpad('R',15),
                     rpad('S',15),
                     rpad('X',15)
                 );                    -- 依次列出所有的值
     srec.bkvals     := dbms_stats.numarray(
                          529100,
                             300,
                             300,
                             300,
                          500000
                 );                    -- 对应的频度

     dbms_stats.prepare_column_values(srec, c_array);

     dbms_stats.set_column_stats(
          ownname         => user,
          tabname         => 'orders',
          colname         => 'status',
          distcnt         => m_distcnt,
          density         => m_density,
          nullcnt         => m_nullcnt,
          srec            => srec,
          avgclen         => m_avgclen
     );
end;
/

有几个值得注意的要点。首先,在前一篇文章中,我说过Oracle对那些包含了直方图中缺失的值的谓词使用的是“最不常见的频度的一半”;似乎是这样,当你使用set_column_sets()来生成统计信息时,Oracle使用density(密度)参数来计算缺失值的基数——这就是我为什么在这个代码中设置了density参数,因为我的决定是,如果你查询一个缺失的值,它的基数应该是10。(如果你检查user_tab_columns试图,自动收集和手动设置的统计信息之间的唯一区别是,当你设置统计信息时user_stats列被置为YES, 如果是自动收集,则为NO)

其次,你会注意到,在我的列表中,我把每个值用空格向右补足到15个字符。我的表将状态值(status)定义为CHAR()型的列,Oracle在将字符串转化为数值时,对CHAR()和VARCHAR2()的处理稍有不同——对于CHAR()列你必须像我那样rpad()到15个字符,对于VARCHAR2()列你不能补齐。如果你把这个弄错了,计算就会出错。

最后,我用了一个dbms_stats.chararray类型的数组;dbms_stats保重还定义了其他几个数组类型(例如numarray, datearray),你必须为你要修改的列选择合适的类型。

选择你的统计信息

如果你知道使用哪些值及其频度,你可以很容易地设立一个假的频度直方图。如果你对数据不是很了解,你可能想要从数据中推导出直方图信息。我对直方图的观点是这样,你真正需要有的直方图只有少数几个,它们通常是频度直方图,你通常需要对数据很了解,所以我从未写过一个通用的(因而也是复杂的)脚本来处理所有的选项;我有一个自己使用的框架,为每个需要它的列创建一个定制的过程。这个过程的核心基本上是一个简单的SQL语句,用于识别最大、最小值和最常出现的N个值,连同它们的频度,还有一些高层次的汇总信息,这是一个例子及其输出的样本:

select
     sample_size           sample,
     sum(ct) over()        hist_size,
     count(ct) over()      buckets,
     min_n1,
     max_n1,
     n1,
     ct
from (
     select
           sum(ct) over ()            sample_size,
           min(n1) over()  min_n1,
           max(n1) over()  max_n1,
           n1,
           ct
     from (
           select
                n1,
                count(*)   ct
           from
                t1         -- 这里可能使用SAMPLE子句
           group by
                n1
           )
     order by
          ct desc
     )
where
     rownum <= 20
or   min_n1 = n1
or   max_n1 = n1
order by
     n1
;

   SAMPLE  HIST_SIZE    BUCKETS     MIN_N1     MAX_N1         N1         CT
---------- ---------- ---------- ---------- ---------- ---------- ----------
   10000       7062         22        162        239        162          1
   10000       7062         22        162        239        190        233
   10000       7062         22        162        239        191        259
   10000       7062         22        162        239        192        268
   10000       7062         22        162        239        193        300
   10000       7062         22        162        239        194        317
   10000       7062         22        162        239        195        312
   10000       7062         22        162        239        196        394
   10000       7062         22        162        239        197        415
   10000       7062         22        162        239        198        389
   10000       7062         22        162        239        199        406
   10000       7062         22        162        239        200        777
   10000       7062         22        162        239        201        400
   10000       7062         22        162        239        202        405
   10000       7062         22        162        239        203        393
   10000       7062         22        162        239        204        358
   10000       7062         22        162        239        205        315
   10000       7062         22        162        239        206        306
   10000       7062         22        162        239        207        284
   10000       7062         22        162        239        208        276
   10000       7062         22        162        239        209        253
   10000       7062         22        162        239        239          1

22 rows selected.

这个查询最核心的部分是对我们需要的列值进行聚合,我们希望它能够将一个大数据集减少为一个小的结果集。然后我们根据频度对这个列排序,使用了几个分析函数来得到最小、最大的列值。然后我们将出现频度的前N个值对应的行及其最小、最大列值选出来。最后我们用了几个分析函数来计算我们实际选中的桶的个数,以及直方图的总数据量,将结果按列值排序返回。

sample和hist_size这两个列的差异告诉我们这是否是对整个数据集的一个“足够好”的近似值。buckets列的值告诉我们是否应该把最小和最大值加入到列表中,或者它们已经天生就落在我们的Top-N列表中,min_n1和max_n1列告诉我们这个列实际的最大、最小值。N1和CT列给了我们将会用来放到两个dbms_stat数组的详细信息(可能有少许修改)。

一旦我们有了这么一个查询,我们将会修改前一个版本的过程来去掉数组的赋值,代之以FOR游标循环:

for r in  ({query}) loop
     n_array.extend;
     srec.bkvals.extend;
     ct := ct+1;
     n_array(ct)     := r.n1;
     srec.bkvals(ct) := r.ct;
end loop;

还有几个小细节需要打磨一下,才能成为一个完整的解决方案——初始化变量,决定一个合适的密度值,决定当最小最大值不出现在Top-N中要如何处理,如果捕获到的数据量和样本的尺寸相比太小要怎么办,等等。进一步的完善留给读者作为练习。

结论

有时候你得使用一些伎俩才能让Oracle得到最好的利用,当你在处理直方图的时候,这个领域是你最有可能需要一些定制化机制来让事情理顺——但可能只是在几个特殊的情况下需要。

其中一个特殊的情况是,当你有一个列,它含有的不同的值超过了Oracle的频度直方图当前允许的限度(254),而你发现等高直方图不能够捕获你需要的足够信息,并且很可能在每次你收集统计信息的时候导致执行计划的改变。在这样的情况下,你可能决定构造一个“假”的频度直方图,它包含了表中最常见的那些值的详细信息,并且告诉优化器如何为其余的值算出一个有代表性的基数。

如果你很了解你的数据,你可以设立一个过程来使用一个固定的数据集来创建直方图,否则你可以查询数据来生成一个合适的"前N个值"的统计数据的集合。不管你选择哪种方法,你必须记住优化器将会放大你所产生的直方图的尺寸来匹配行数的值 (user_tables.num_rows – user_tab_cols.num_nulls),所以仅当频繁值代表了表中总数据量的很大的百分比(可能多达98%)你才应该使用这种方法。




世界上只有两种编程方法:Oracle的方法和错误的方法。

剑破冰山—Oracle开发艺术 即将隆重推出
http://www.china-pub.com/197199
http://www.huachu.com.cn/itbook/itbookinfo.asp?lbbh=10114321

使用道具 举报


您需要登录后才可以回帖 登录 | 注册

2014 Oracle技术嘉年华(11.14-15)
OTN2014正式启动 限时免费

大会时间:2014年11月14日-15日
大会地点:北京五洲皇冠国际酒店
大会主题:数据库技术企业应用最佳实践
第四届Oracle技术嘉年华热力来袭!作为国内最顶尖的Oracle数据库技术盛会,参加Oracle技术嘉年华,这里不会让你失望。
欢迎参与,报名从速~~

查看详情>>
TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 邮箱 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | IXPUB | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP证:060528号 北京市公安局海淀分局网监中心备案编号:1101082001 广播电视节目制作经营许可证:编号(京)字第1149号
  
回顶部