Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1277889
  • 博文数量: 127
  • 博客积分: 2286
  • 博客等级: 大尉
  • 技术积分: 1943
  • 用 户 组: 普通用户
  • 注册时间: 2010-06-10 10:37
文章分类

全部博文(127)

文章存档

2018年(1)

2015年(2)

2014年(1)

2013年(30)

2012年(88)

2011年(5)

分类: 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表示没压力
阅读(4972) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~