认识达内从这里开始

继续上次分析的一个问题，一个简单的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'