Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1118009
  • 博文数量: 231
  • 博客积分: 2500
  • 博客等级: 少校
  • 技术积分: 2662
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-03 16:35
个人简介

学无止境

文章分类

全部博文(231)

文章存档

2014年(7)

2013年(103)

2011年(11)

2010年(53)

2009年(57)

分类: Oracle

2010-09-02 15:27:40

先关注与柱状图有关的2个数据字典视图。
 
DBA_TAB_HISTOGRAMS
该视图列出了所有表上列的柱状图信息。
 
字段名称              描述
---------------------------------------------------------
OWNER                 表的所有者
TABLE_NAME            表名
COLUMN_NAME           列名(字段名)
ENDPOINT_NUMBER       终点数值,也就是bucket的编号值
ENDPOINT_VALUE        根据该bucket的编号值,规格化的终点字段值,
                      如果字段类型是数字,则显示该字段值,
                      如果是其他类型的,则是被规格化的数值。
 
DBA_TAB_COLUMNS
该视图包含了所有表上列的描述信息。
(注意:虽然视图和簇的信息也包含在该视图中,但没有相关的柱状图信息)
 
字段名称              描述
----------------------------------------------------------
OWNER                 表的所有者
TABLE_NAME            表名
COLUMN_NAME           列名(字段名)
DATA_TYPE             字段的数据类型
DATA_LENGTH           该列定义的长度
DATA_PRECISION        NUMBER或FLOAT数值类型的精度
DATA_SCALE            定义的小数位数
NULLABLE              是否允许NULL值
COLUMN_ID             该列在表中的顺序的位置,表中第几个字段
DEFAULT_LENGTH        字段默认值的长度
DATA_DEFAULT          字段默认值
NUM_DISTINCT          该字段不同值的数量
LOW_VALUE             该字段的最小值,
                      该值是内部按16进制存储的该值的前32 bytes内容
HIGH_VALUE            该字段的最大值,
                      该值是内部按16进制存储的该值的前32 bytes内容
DENSITY               该字段的密度(不同值的比例值,
                      比如一个字段只有2个不同值,那么该字段密度为0.5)
NUM_NULLS             该字段NULL值的数量
NUM_BUCKETS           该字段柱状图中bucket的数量
LAST_ANALYZED         最近一次分析表的时间
SAMPLE_SIZE           采样数据的数量(根据分析时的百分比得出的数量,
                      例如100%分析,该采样值就是行的数量)
 
 
创建一张表SKEW,有100w行记录,其中字段ID的内容是倾斜的,已知该ID字段数据分布如下:
id有700个不同值
值为501占30%
值为414占19%
值为2占15%
值为123占7%
值为其他的占29%,也就是其他每种值大概占
 

对字段ID,进行一次bucket数量为254的柱状图分析,分析信息如下:

TABLE_NAME COLUMN_NAME  NUM_DISTINCT LOW_VALUE  HIGH_VALUE  NUM_BUCKETS HISTOGRAM     
---------- ------------ ------------ ---------- ----------- ----------- ---------------
SKEW       ID                    700 C102       C208                254 HEIGHT BALANCED

TABLE_NAME COLUMN_NAME     ENDPOINT_NUMBER ENDPOINT_VALUE
---------- --------------- --------------- --------------
SKEW       ID                            0              1
SKEW       ID                           38              2
SKEW       ID                           39              8
SKEW       ID                           40             18
SKEW       ID                           41             28
SKEW       ID                           42             37
SKEW       ID                           43             47
SKEW       ID                           44             56
SKEW       ID                           45             65
SKEW       ID                           46             75
SKEW       ID                           47             84
SKEW       ID                           48             94
SKEW       ID                           49            103
SKEW       ID                           50            113
SKEW       ID                           51            122
SKEW       ID                           69            123
SKEW       ID                           70            133
SKEW       ID                           71            142
SKEW       ID                           72            152
SKEW       ID                           73            162
SKEW       ID                           74            171
SKEW       ID                           75            181
SKEW       ID                           76            190
SKEW       ID                           77            200
SKEW       ID                           78            209
SKEW       ID                           79            219
SKEW       ID                           80            228
SKEW       ID                           81            238
SKEW       ID                           82            247
SKEW       ID                           83            256
SKEW       ID                           84            266
SKEW       ID                           85            275
SKEW       ID                           86            285
SKEW       ID                           87            295
SKEW       ID                           88            304
SKEW       ID                           89            313
SKEW       ID                           90            323
SKEW       ID                           91            332
SKEW       ID                           92            341
SKEW       ID                           93            351
SKEW       ID                           94            360
SKEW       ID                           95            370
SKEW       ID                           96            379
SKEW       ID                           97            389
SKEW       ID                           98            398
SKEW       ID                           99            408
SKEW       ID                          147            414
SKEW       ID                          148            416
SKEW       ID                          149            425
SKEW       ID                          150            435
SKEW       ID                          151            444
SKEW       ID                          152            454
SKEW       ID                          153            463
SKEW       ID                          154            473
SKEW       ID                          155            482
SKEW       ID                          156            492
SKEW       ID                          233            501
SKEW       ID                          234            511
SKEW       ID                          235            521
SKEW       ID                          236            530
SKEW       ID                          237            540
SKEW       ID                          238            549
SKEW       ID                          239            558
SKEW       ID                          240            568
SKEW       ID                          241            577
SKEW       ID                          242            587
SKEW       ID                          243            597
SKEW       ID                          244            606
SKEW       ID                          245            616
SKEW       ID                          246            625
SKEW       ID                          247            634
SKEW       ID                          248            644
SKEW       ID                          249            653
SKEW       ID                          250            663
SKEW       ID                          251            672
SKEW       ID                          252            682
SKEW       ID                          253            691
SKEW       ID                          254            700
 

通过柱状图来计算:
1/255=0.003921568627450980392156862745098=0.39%
ID=2             (38-0)/255=0.14901960784313725490196078431373=15%
ID=123           (69-51)/255=0.070588235294117647058823529411765=7%
ID=从409到414    (147-99)/255=0.18823529411764705882352941176471=19%
ID=从493到501    (233-156)/255=0.30196078431372549019607843137255=30%
其他值:从664到672   共9个值  1/255/9=0.04357%

通过计算,该柱状图的分析的比例和预设的基本一致。

通过直接查询表的内容来看柱状图:
如下:
select wb, cnt,
to_char(round( 100*cnt/(max(cnt) over ()),2),'999.00') rat,
rpad( '*', 40*cnt/(max(cnt) over ()), '*' ) hist
from (
select wb,
count(*) cnt
from (
select width_bucket( r, 0, (select count(distinct &cname) from &tname)+1,
&buckets) wb
from (
select dense_rank() over (order by &cname) r
from &tname
)
)
group by wb
)
order by wb
/
其中:
&tname为表名
&cname为列名
&buckets为柱状图bucket的数量
 
 
select wb, cnt,
to_char(round( 100*cnt/(max(cnt) over ()),2),'999.00') rat,
rpad( '*', 40*cnt/(max(cnt) over ()), '*' ) hist
from (
select wb,
count(*) cnt
from (
select width_bucket( r, 0, (select count(distinct ID) from SKEW)+1,
254) wb
from (
select dense_rank() over (order by ID) r
from SKEW
)
)
group by wb
)
order by wb
/
 
显示信息如下:
 
  WB        CNT RAT     HIST
---- ---------- ------- ----------------------------------------
   1     151162   50.30 ********************                    
   2       1225     .41                                         
   3       1230     .41                                         
   4       1202     .40                                         
   5        845     .28                                         
   6       1193     .40                                         
   7       1252     .42                                         
   8       1204     .40                                         
   9        835     .28                                         
  10       1224     .41                                         
  11       1287     .43                                         
  12       1287     .43                                         
  13        787     .26                                         
  14       1227     .41                                         
  15       1232     .41                                         
  16       1249     .42                                         
  17        828     .28                                         
  18       1258     .42                                         
  19       1203     .40                                         
  20       1220     .41                                         
  21        822     .27                                         
  22       1295     .43                                         
  23       1263     .42                                         
  24       1264     .42                                         
  25        826     .27                                         
  26       1263     .42                                         
  27       1239     .41                                         
  28       1213     .40                                         
  29       1284     .43                                         
  30        864     .29                                         
  31       1210     .40                                         
  32       1258     .42                                         
  33       1219     .41                                         
  34        815     .27                                         
  35       1213     .40                                         
  36       1256     .42                                         
  37       1274     .42                                         
  38        848     .28                                         
  39       1227     .41                                         
  40       1263     .42                                         
  41       1244     .41                                         
  42        818     .27                                         
  43       1262     .42                                         
  44       1264     .42                                         
  45      71700   23.86 *********                               
  46        853     .28                                         
  47       1255     .42                                         
  48       1233     .41                                         
  49       1244     .41                                         
  50        800     .27                                         
  51       1283     .43                                         
  52       1248     .42                                         
  53       1210     .40                                         
  54       1206     .40                                         
  55        835     .28                                         
  56       1204     .40                                         
  57       1215     .40                                         
  58       1237     .41                                         
  59        802     .27                                         
  60       1227     .41                                         
  61       1209     .40                                         
  62       1297     .43                                         
  63        787     .26                                         
  64       1235     .41                                         
  65       1266     .42                                         
  66       1239     .41                                         
  67        857     .29                                         
  68       1300     .43                                         
  69       1214     .40                                         
  70       1247     .41                                         
  71        795     .26                                         
  72       1285     .43                                         
  73       1229     .41                                         
  74       1268     .42                                         
  75        822     .27                                         
  76       1244     .41                                         
  77       1266     .42                                         
  78       1201     .40                                         
  79       1187     .39                                         
  80        836     .28                                         
  81       1236     .41                                         
  82       1258     .42                                         
  83       1275     .42                                         
  84        863     .29                                         
  85       1261     .42                                         
  86       1214     .40                                         
  87       1301     .43                                         
  88        821     .27                                         
  89       1272     .42                                         
  90       1249     .42                                         
  91       1303     .43                                         
  92        836     .28                                         
  93       1311     .44                                         
  94       1245     .41                                         
  95       1238     .41                                         
  96        822     .27                                         
  97       1212     .40                                         
  98       1220     .41                                         
  99       1172     .39                                         
 100        858     .29                                         
 101       1178     .39                                         
 102       1246     .41                                         
 103       1231     .41                                         
 104       1222     .41                                         
 105        802     .27                                         
 106       1181     .39                                         
 107       1241     .41                                         
 108       1206     .40                                         
 109        853     .28                                         
 110       1265     .42                                         
 111       1292     .43                                         
 112       1298     .43                                         
 113        877     .29                                         
 114       1273     .42                                         
 115       1258     .42                                         
 116       1266     .42                                         
 117        788     .26                                         
 118       1295     .43                                         
 119       1327     .44                                         
 120       1265     .42                                         
 121        822     .27                                         
 122       1253     .42                                         
 123       1227     .41                                         
 124       1228     .41                                         
 125        887     .30                                         
 126       1237     .41                                         
 127       1268     .42                                         
 128       1243     .41                                         
 129       1205     .40                                         
 130        844     .28                                         
 131       1295     .43                                         
 132       1243     .41                                         
 133       1251     .42                                         
 134        845     .28                                         
 135       1220     .41                                         
 136       1259     .42                                         
 137       1226     .41                                         
 138        834     .28                                         
 139       1237     .41                                         
 140       1282     .43                                         
 141       1211     .40                                         
 142        879     .29                                         
 143       1205     .40                                         
 144       1184     .39                                         
 145       1248     .42                                         
 146        806     .27                                         
 147       1285     .43                                         
 148       1247     .41                                         
 149       1229     .41                                         
 150        777     .26                                         
 151     190834   63.50 *************************               
 152       1270     .42                                         
 153       1253     .42                                         
 154       1207     .40                                         
 155        831     .28                                         
 156       1239     .41                                         
 157       1174     .39                                         
 158       1225     .41                                         
 159        838     .28                                         
 160       1291     .43                                         
 161       1251     .42                                         
 162       1253     .42                                         
 163        826     .27                                         
 164       1261     .42                                         
 165       1278     .43                                         
 166       1200     .40                                         
 167        830     .28                                         
 168       1222     .41                                         
 169       1219     .41                                         
 170       1236     .41                                         
 171        816     .27                                         
 172       1275     .42                                         
 173       1278     .43                                         
 174       1267     .42                                         
 175        794     .26                                         
 176       1243     .41                                         
 177       1217     .40                                         
 178       1257     .42                                         
 179       1208     .40                                         
 180        813     .27                                         
 181       1257     .42                                         
 182     300537  100.00 ****************************************
 183       1213     .40                                         
 184        846     .28                                         
 185       1223     .41                                         
 186       1256     .42                                         
 187       1190     .40                                         
 188        804     .27                                         
 189       1243     .41                                         
 190       1190     .40                                         
 191       1202     .40                                         
 192        896     .30                                         
 193       1276     .42                                         
 194       1227     .41                                         
 195       1280     .43                                         
 196        872     .29                                         
 197       1307     .43                                         
 198       1289     .43                                         
 199       1269     .42                                         
 200        834     .28                                         
 201       1221     .41                                         
 202       1233     .41                                         
 203       1271     .42                                         
 204       1225     .41                                         
 205        784     .26                                         
 206       1238     .41                                         
 207       1278     .43                                         
 208       1199     .40                                         
 209        838     .28                                         
 210       1224     .41                                         
 211       1253     .42                                         
 212       1248     .42                                         
 213        791     .26                                         
 214       1184     .39                                         
 215       1263     .42                                         
 216       1209     .40                                         
 217        841     .28                                         
 218       1273     .42                                         
 219       1233     .41                                         
 220       1229     .41                                         
 221        790     .26                                         
 222       1228     .41                                         
 223       1241     .41                                         
 224       1265     .42                                         
 225        835     .28                                         
 226       1285     .43                                         
 227       1278     .43                                         
 228       1234     .41                                         
 229       1234     .41                                         
 230        843     .28                                         
 231       1271     .42                                         
 232       1227     .41                                         
 233       1277     .42                                         
 234        857     .29                                         
 235       1238     .41                                         
 236       1218     .41                                         
 237       1242     .41                                         
 238        831     .28                                         
 239       1271     .42                                         
 240       1236     .41                                         
 241       1259     .42                                         
 242        860     .29                                         
 243       1271     .42                                         
 244       1171     .39                                         
 245       1249     .42                                         
 246        831     .28                                         
 247       1245     .41                                         
 248       1228     .41                                         
 249       1310     .44                                         
 250        798     .27                                         
 251       1261     .42                                         
 252       1213     .40                                         
 253       1260     .42                                         
 254        893     .30                                         
阅读(1584) | 评论(1) | 转发(0) |
给主人留下些什么吧!~~

chinaunix网友2011-01-01 15:07:13

很好的, 收藏了 推荐一个博客,提供很多免费软件编程电子书下载: http://free-ebooks.appspot.com