公众号：杨建荣的学习笔记 · 作者：杨建荣 · 发布：2016-09-01 23:21:06 · 原文链接

    继续上次分析的一个问题，一个简单的SQL语句执行计划有些奇怪，明明可以走唯一性索引但是却走了另外一个索引。
    当然了，最后逐步定位，发现是在直方图的地方有一些差别。取消直方图之后，执行计划立刻恢复了正常。
    当然问题来了，这个是为什么呢,收集统计信息中的auto选项是什么含义呢。为什么两个数据类型一样的(varchar2(64)）的列，境遇却大大不同。
    我们来看看一些统计信息的数据。

    为了跟进一步验证数据的分布律和选取代价，我们查询它的直方图信息。
SQL>   select to_char(endpoint_value) value,endpoint_number,column_name from dba_tab_histograms where table_name = ‘OP_ORDER’ and column_name in (‘ORDER_ID’,’USER_ID’) ORDER  BY endpoint_number；

可以这两条结果对应的查询结果有248行，ORDER_ID只有两行，而USER_ID却又246行，也就意味着USER_ID对应有246个bucket，对于数据的分布情况统计更为周密。
这又是为什么呢，两个字段都是varchar2，怎么会差别这么大呢。
我们取出几条数据来。
SQL> select order_id from ordermob.OP_ORDER where rownum<10;
ORDER_ID
—————————————————————-
160526163113314574
160526163122274152
160526163130777725
160526164612542552
160526172953321536
160526173306557175
160526173335364777
160526180054556153
160526180101316451
看得出来签名的很多位都是一样的，这种订单业务的数据，订单号都有一定的规范，签名的值还是有一定的规律可循。
SQL>  select to_char(endpoint_value) value,endpoint_number,column_name from dba_tab_histograms where table_name = ‘OP_ORDER’ and column_name in (‘ORDER_ID’,’USER_ID’) ORDER BY endpoint_number
VALUE                                    ENDPOINT_NUMBER COLUMN_NAME
—————————————- ————— —————————————-
255521615291332000000000000000000000                   0 ORDER_ID
255521616530467000000000000000000000                   1 ORDER_ID
可以看到端点值(endpoint_value),endpoint_value就是列的值，非数字类型（VARCHAR2，CHAR,NVARCHAR2，NCHAR）必须进行转换，仅取前六个字节（不是字符）。从10g实测数据来看取前15个字节，前30个字符有效转换，其他都会忽略。也就是收集直方图相当于只对字段B的substr(B,1,30)收集桶信息。
这个信息怎么进行确认呢。我们取出一条数据来测试。
以max(order_id)为例，先取得dump的元数据信息。
SQL> select to_char(substrb(dump(max(“ORDER_ID”),16,0,32),1,120))  from “ORDERMOB”.”OP_ORDER” t ；
TO_CHAR(SUBSTRB(DUMP(MAX(“ORDER_ID”),16,0,32),1,120))
————————————————————————————————————————
Typ=1 Len=18: 31,36,30,38,32,36,31,35,35,30,33,38,33,35,31,33,32,35
然后进行转换，转换进制。
SQL> select to_number(‘313630383236313535303338333531′,’xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’) aa from dual;
                                          AA
——————————————–
        255521616530467185179705496063653169
看看这个转换后的值是否为 255521616530467，也就是转换进制后的前15位保留值。
SQL> select length(‘255521616530467’) from dual;
LENGTH(‘255521616530467’)
————————-
                       15
发现确实如此。
而整个串有36位。对于这类场景来看就很难去区分出数据的细粒度差别来。
SQL> select length(‘255521616530467000000000000000000000’) from dual;
LENGTH(‘255521616530467000000000000000000000’)
———————————————-
                                            36
所以对于order_id的直方图信息就会只分配2个bucket,而这个过程如何验证，那就是使用经典的10046事件了。
里面的计算方式 to_char(substrb(dump(max(“ORDER_ID”),16,0,32),1,120))  from “ORDERMOB”.”OP_ORDER” 正式出自10046的trace文件。

当然可以自己找个环境继续验证一下。
>  create table test_stats (order_id varchar2(64),user_id varchar2(64),channel_id number);
Table created.

> insert into test_stats values(‘0000000000001241414′,’test’,1);
1 row created.

> insert into test_stats values(‘0000000000001251414′,’test2’,2);
1 row created.

> insert into test_stats values(‘0000000000001251514′,’test3’,2);
1 row created.

> commit;
Commit complete.

生成10046事件来查看。
ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 12’
exec dbms_stats.gather_table_stats(tabname => ‘test_stats’,ownname => ‘TEST’,method_opt => ‘FOR ALL COLUMNS SIZE AUTO’);
ALTER SESSION SET EVENTS ‘10046 trace name context off’