分类： Oracle
2013-12-18 16:56:35
先测试字符串的精度：
truncate table t;
begin
for i in 2..80 loop
for j in 1..i*i loop
insert into t values(i,lpad('2',i,'2'));
end loop;
insert into t values(i,rpad(lpad('2',18,'2'),30,'m'));
end loop;
end;
/
exec dbms_stats.gather_table_stats(user,'T', estimate_percent=>100,method_opt=>'FOR all COLUMNS size 80');
10g:
用我写的脚本查看各值的分布情况：
ENDPOINT_VALUE ENDPOINT_ROWS EP_PCT
-------------------------------- --------------- -------
2 1.00 .00
22 4.00 .00
222 9.00 .01
2222 16.00 .01
22222 25.00 .01
222222 36.00 .02
2222222 49.00 .03
22222222 64.00 .04
222222222 81.00 .05
2222222222 100.00 .06
22222222222 121.00 .07
222222222222 144.00 .08
2222222222222 169.00 .10
22222222222222 196.00 .11
222222222222222 225.00 .13
2222222222222222 256.00 .15
22222222222222222 289.00 .17
222222222222222222 324.00 .19
2222222222222222222 361.00 .21
22222222222222222222 400.00 .23
222222222222222222222 441.00 .25
2222222222222222222222 484.00 .28
22222222222222222222222 529.00 .30
222222222222222222222222 576.00 .33
2222222222222222222222222 625.00 .36
22222222222222222222222222 676.00 .39
222222222222222222222222222 729.00 .42
2222222222222222222222222222 784.00 .45
22222222222222222222222222222 841.00 .48
222222222222222222222222222222 900.00 .52
2222222222222222222222222222222 961.00 .55
22222222222222222222222222222222 163,464.00 94.01
32 rows selected.
32字节长度的以上全合并到一起了
select endpoint_value,count(*) from user_tab_histograms
where table_name='T' and column_name='NAME'
group by endpoint_value;
ENDPOINT_VALUE COUNT(*)
-------------- ----------
########## 1
########## 1
########## 1
########## 27
########## 1
########## 1
但是endpoint_value只有6个值，也就是6个字节长度，
说明这个字段只能表示6个字节的字符串，早就被弃用了，
真正起作用的是ENDPOINT_ACTUAL_VALUE
测试发现如果有长度超过6的字符串，ENDPOINT_ACTUAL_VALUE
就有值了，说明这时候使用新的字段来表示精度。
问题：这个字段长度是1000，为什么要限制为32呢？
估计有技术上实现的难度。
注明：
我再写显示endpoint_value字符串值的时候，实际上前7个字节有效，
最后一个字节为了比较会看情况-1操作，就是比实际值小一
SQL> select count(*) from t where name =2222222222222222222222222222222;
COUNT(*)
----------
961
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 62 | 457 (1)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 62 | | |
|* 2 | TABLE ACCESS FULL| T | 961 | 59582 | 457 (1)| 00:00:06 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("NAME")=2222222222222222222222222222222)
优化器能识别31字节，显示的和我脚本的值是一致的
SQL> select count(*) from t where name =22222222222222222222222222222222;
COUNT(*)
----------
1024
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 62 | 457 (1)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 62 | | |
|* 2 | TABLE ACCESS FULL| T | 163K| 9897K| 457 (1)| 00:00:06 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("NAME")=22222222222222222222222222222222)
经测试32~40字节的字符串都用这个选择率163k, 41的时候就变为1了：
SQL> select count(*) from t where name =2222222222222222222222222222222299999999;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 62 | 457 (1)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 62 | | |
|* 2 | TABLE ACCESS FULL| T | 163K| 9897K| 457 (1)| 00:00:06 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_NUMBER("NAME")=2222222222222222222222222222222299999999
再看看12c的情况：
12c:
HISTOGRAM EPV(EndPointValue) EP_ROWS EP% Density%
--------------- -------------------------------- ------------ ------- --------
FREQUENCY 22 4 .00 .0000
FREQUENCY 222 9 .01 .0000
FREQUENCY 2222 16 .01 .0000
FREQUENCY 22222 25 .01 .0000
FREQUENCY 222222 36 .02 .0000
FREQUENCY 2222222 49 .03 .0000
FREQUENCY 22222222 164 .09 .0000
FREQUENCY 222222222 81 .05 .0000
FREQUENCY 2222222222 100 .06 .0000
FREQUENCY 22222222222 121 .07 .0000
FREQUENCY 222222222222 144 .08 .0000
FREQUENCY 2222222222222 169 .10 .0000
FREQUENCY 22222222222222 196 .11 .0000
FREQUENCY 222222222222222 225 .13 .0000
FREQUENCY 2222222222222222 256 .15 .0000
FREQUENCY 22222222222222222 289 .17 .0000
FREQUENCY 222222222222222222 324 .19 .0000
FREQUENCY 2222222222222222222 361 .21 .0000
FREQUENCY 22222222222222222222 400 .23 .0000
FREQUENCY 222222222222222222222 441 .25 .0000
FREQUENCY 2222222222222222222222 484 .28 .0000
FREQUENCY 22222222222222222222222 529 .30 .0000
FREQUENCY 222222222222222222222222 576 .33 .0000
FREQUENCY 2222222222222222222222222 625 .36 .0000
FREQUENCY 22222222222222222222222222 676 .39 .0000
FREQUENCY 222222222222222222222222222 729 .42 .0000
FREQUENCY 2222222222222222222222222222 784 .45 .0000
FREQUENCY 22222222222222222222222222222 841 .48 .0000
FREQUENCY 222222222222222222222222222222 900 .52 .0000
FREQUENCY 2222222222222222222222222222222 961 .55 .0000
......
FREQUENCY 22222222222222222222222222222222 88,536 50.89 .0000
22222222222222222222222222222222
-------
sum 100.00
明显可以支持64byte了
63 rows selected.
ENDPOINT_VALUE COUNT(*)
-------------- ----------
2.6063E+35 1
2.6063E+35 1
2.6063E+35 1
2.6063E+35 59
2.6063E+35 1
2.6063E+35 1
ENDPOINT_VALUE一样只能表示6，7个字节；7字节开始使用ENDPOINT_ACTUAL_VALUE_raw,
ENDPOINT_ACTUAL_VALUE向前兼容并方便查询。
这个改动（后面会讲）是为了提高数字类型的表示精度。
看看优化器能识别否“
SQL> select count(*) from t where name =lpad('2',63,'2');
COUNT(*)
----------
3969
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 62 | 479 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 62 | | |
|* 2 | TABLE ACCESS FULL| T | 3969 | 240K| 479 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NAME"='22222222222222222222222222222222222222222222222222
2222222222222')
长度63没问题
SQL> select count(*) from t where name =lpad('2',64,'2');
COUNT(*)
----------
4096
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 62 | 479 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 62 | | |
|* 2 | TABLE ACCESS FULL| T | 88536 | 5360K| 479 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NAME"='22222222222222222222222222222222222222222222222222
22222222222222')
长度64没问题
SQL> select count(*) from t where name =lpad('2',120,'2');
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 62 | 479 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 62 | | |
|* 2 | TABLE ACCESS FULL| T | 88536 | 5360K| 479 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NAME"='2222222…2222')
SQL> select count(*) from t where name =rpad(lpad('2',64,'2'),800,'x');
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 62 | 479 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 62 | | |
|* 2 | TABLE ACCESS FULL| T | 88536 | 5360K| 479 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NAME"='222222222222….xxxxx')
大于64字节，优化器都用88k的行数，这与11g又不同了
再看看内部怎么存放的：
set linesize 130
col epvalue for a64
select row#,intcol#,bucket, endpoint,epvalue
from sys.histgrm$
where obj#=13499
and col#=2
order by bucket,row#;
ROW# INTCOL# BUCKET ENDPOINT EPVALUE
---------- ---------- ---------- ---------- ----------------------------------------------------------------
0 2 4 2.6063E+35 22
0 2 13 2.6063E+35 222
0 2 29 2.6063E+35 2222
0 2 54 2.6063E+35 22222
0 2 90 2.6063E+35 222222
0 2 139 2.6063E+35 2222222
0 2 203 2.6063E+35 22222222
0 2 284 2.6063E+35 222222222
0 2 384 2.6063E+35 2222222222
0 2 505 2.6063E+35 22222222222
0 2 649 2.6063E+35 222222222222
0 2 818 2.6063E+35 2222222222222
0 2 1014 2.6063E+35 22222222222222
0 2 1239 2.6063E+35 222222222222222
0 2 1495 2.6063E+35 2222222222222222
0 2 1784 2.6063E+35 22222222222222222
0 2 2108 2.6063E+35 222222222222222222
0 2 2469 2.6063E+35 2222222222222222222
0 2 2869 2.6063E+35 22222222222222222222
0 2 3310 2.6063E+35 222222222222222222222
1 2 3794 2.6063E+35 2222222222222222222222
1 2 4323 2.6063E+35 22222222222222222222222
1 2 4899 2.6063E+35 222222222222222222222222
1 2 5524 2.6063E+35 2222222222222222222222222
1 2 6200 2.6063E+35 22222222222222222222222222
1 2 6929 2.6063E+35 222222222222222222222222222
1 2 7713 2.6063E+35 2222222222222222222222222222
1 2 8554 2.6063E+35 22222222222222222222222222222
1 2 9454 2.6063E+35 222222222222222222222222222222
1 2 10415 2.6063E+35 2222222222222222222222222222222
1 2 173879 2.6063E+35 22222222222222222222222222222222
11g以前放到EPVALUE字段（when any length >6bytes）
col epvalue_raw for a64
select row#,intcol#,bucket, endpoint,epvalue_raw
from sys.histgrm$
where obj#=19903
and col#=2
order by bucket,row#;
ROW# INTCOL# BUCKET ENDPOINT EPVALUE_RAW
---------- ---------- ---------- ---------- ----------------------------------------------------------------
0 2 4 2.6063E+35 3232
0 2 13 2.6063E+35 323232
0 2 29 2.6063E+35 32323232
0 2 54 2.6063E+35 3232323232
0 2 90 2.6063E+35 323232323232
0 2 139 2.6063E+35 32323232323232
0 2 203 2.6063E+35 3232323232323232
0 2 284 2.6063E+35 323232323232323232
0 2 384 2.6063E+35 32323232323232323232
0 2 505 2.6063E+35 3232323232323232323232
0 2 649 2.6063E+35 323232323232323232323232
0 2 818 2.6063E+35 32323232323232323232323232
0 2 1014 2.6063E+35 3232323232323232323232323232
0 2 1239 2.6063E+35 323232323232323232323232323232
0 2 1495 2.6063E+35 32323232323232323232323232323232
0 2 1784 2.6063E+35 3232323232323232323232323232323232
0 2 2108 2.6063E+35 323232323232323232323232323232323232
0 2 2469 2.6063E+35 32323232323232323232323232323232323232
0 2 2869 2.6063E+35 3232323232323232323232323232323232323232
0 2 3310 2.6063E+35 323232323232323232323232323232323232323232
1 2 3794 2.6063E+35 32323232323232323232323232323232323232323232
1 2 4323 2.6063E+35 3232323232323232323232323232323232323232323232
......
1 2 10415 2.6063E+35 32323232323232323232323232323232323232323232323232323232323232
12c以前放到EPVALUE_raw字段（when any length >6bytes）,EPVALUE只兼容
=====================================================================
再看看数字的表示精度问题：
truncate table t;
begin
for i in 1..10 loop
for j in 1..i*i loop
insert into t values(to_number(lpad('9',15+i,'6')),i);
IF 0 = mod(j,3) THEN
insert into t values(to_number(lpad('3',15+i,'6')),i);
END IF;
end loop;
end loop;
end;
/
col id for 999999999999999999999999999
SQL> select id,count(*) from t group by id order by 1;
ID COUNT(*)
---------------------------- ----------
6666666666666669 1
66666666666666663 1
66666666666666669 4
666666666666666663 3
666666666666666669 9
6666666666666666663 5
6666666666666666669 16
66666666666666666663 8
66666666666666666669 25
666666666666666666663 12
666666666666666666669 36
6666666666666666666663 16
6666666666666666666669 49
66666666666666666666663 21
66666666666666666666669 64
666666666666666666666663 27
666666666666666666666669 81
6666666666666666666666663 33
6666666666666666666666669 100
exec dbms_stats.gather_table_stats(user,'T', estimate_percent=>100,method_opt=>'FOR all COLUMNS size 20');
首先产看统计信息和数据分布情况：
ENDPOINT_VALUE ENDPOINT_ROWS EP_PCT
-------------------------------- --------------- -------
6666666666666670 1.00 .20
66666666666666700 1.00 .20
66666666666666700 4.00 .78
666666666666667000 3.00 .59
666666666666667000 9.00 1.76
6666666666666670000 5.00 .98
6666666666666670000 16.00 3.13
66666666666666700000 8.00 1.57
66666666666666700000 25.00 4.89
666666666666667000000 12.00 2.35
666666666666667000000 36.00 7.05
6666666666666670000000 16.00 3.13
6666666666666670000000 49.00 9.59
66666666666666700000000 21.00 4.11
66666666666666700000000 64.00 12.52
666666666666667000000000 27.00 5.28
666666666666667000000000 81.00 15.85
6666666666666670000000000 33.00 6.46
6666666666666670000000000 100.00 19.57
可以看出，可以统计出，但是临近2行的ep是一样的，所以这是normalize的问题
12c呢
HISTOGRAM EPV(EndPointValue) EP_ROWS EP% Density%
--------------- -------------------------------- ------------ ------- --------
FREQUENCY 6666666666666669 1 .20 .0000
FREQUENCY 66666666666666663 1 .20 .0000
FREQUENCY 66666666666666669 4 .78 .0000
FREQUENCY 666666666666666663 3 .59 .0000
FREQUENCY 666666666666666669 9 1.76 .0000
FREQUENCY 6666666666666666663 5 .98 .0000
FREQUENCY 6666666666666666669 16 3.13 .0000
FREQUENCY 66666666666666666663 8 1.57 .0000
FREQUENCY 66666666666666666669 25 4.89 .0000
FREQUENCY 666666666666666666663 12 2.35 .0000
FREQUENCY 666666666666666666669 36 7.05 .0000
FREQUENCY 6666666666666666666663 16 3.13 .0000
FREQUENCY 6666666666666666666669 49 9.59 .0000
FREQUENCY 66666666666666666666663 21 4.11 .0000
FREQUENCY 66666666666666666666669 64 12.52 .0000
FREQUENCY 666666666666666666666663 27 5.28 .0000
FREQUENCY 666666666666666666666669 81 15.85 .0000
FREQUENCY 6666666666666666666666663 33 6.46 .0000
FREQUENCY 6666666666666666666666669 100 19.57 .0000
-------
sum 100.00
12c的脚本显示没有问题。
看看优化器怎么工作的？
11g:
SQL> select count(*) from t where id=666666666666666666666663;
COUNT(*)
----------
27
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | TABLE ACCESS FULL| T | 27 | 378 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=666666666666666666666663)
SQL> select count(*) from t where id=666666666666666666666669;
COUNT(*)
----------
81
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | TABLE ACCESS FULL| T | 27 | 378 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=666666666666666666666669)
显然优化器没方法区别这两种情况，用了第一个。
再看12c:
SQL> select count(*) from t where id=666666666666666666666663;
COUNT(*)
----------
27
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | TABLE ACCESS FULL| T | 27 | 378 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=666666666666666666666663)
SQL> select count(*) from t where id=666666666666666666666669;
COUNT(*)
----------
81
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | TABLE ACCESS FULL| T | 81 | 1134 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=666666666666666666666669)
12c表示没压力