一條SQL語句的執行計劃變化探究（r10筆記第9天）

繼續上次分析的一個問題，一個簡單的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'