[20141205]關於sql_id.txt

[20141205]關於sql_id.txt

--昨天跟別人聊天，講sql_id字串裡面沒有字元'o'.實際上他在學習sql_id與hash_value轉換時copy 和 paste少貼上1位，手工輸入時以
--為是--字母'o'，實際上是數字'0'.

--順便找一個sql_id,把最後以為換成o,看看結果：
SYS@test> select dbms_utility.SQLID_TO_SQLHASH('f7nhbjdn5rx9o') from dual ;
select dbms_utility.SQLID_TO_SQLHASH('f7nhbjdn5rx9o') from dual
       *
ERROR at line 1:
ORA-13797: invalid SQL Id specified, f7nhbjdn5rx9o
ORA-06512: at "SYS.DBMS_UTILITY", line 1293

--做一個簡單的查詢：
SYS@test> select sql_id from v$sqlarea where instr(sql_id,'o')>0;
no rows selected

--很明顯沒有字元'o'.

--sql_id的計算是使用MD5演算法進行雜湊，生成一個128位的Hash Value，其中低32位作為HASH VALUE顯示，SQL_ID則取了後64位。
--實際上sql_id使用32進製表示，hash_value使用10進製表示。

--既然使用32進位制，0-9,a-z 總共10+26=36個字元，明顯多了4個。也就是講有4個字元不會出現在sql_id中。

--寫一個簡單的sql看看。

select b.x from (select chr(level+96) x from dual connect by level<=26 ) b where not exists (select 1 from v$sqlarea
  2  where instr(sql_id,b.x)>0);
X
---
e
i
l
o

--還包括eilo 4個字元。看看大師寫的sql_id轉換hash_value的語句就知道了。

http://blog.tanelpoder.com/2009/02/22/sql_id-is-just-a-fancy-representation-of-hash-value

select
     lower(trim('&1')) sql_id
  , trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',substr(lower(trim('&1')),level,1))-1)
                *power(32,length(trim('&1'))-level)),power(2,32))) hash_value
from
     dual
connect by
     level <= length(trim('&1'))
/

--轉換表裡面沒有eilo。指令碼乍看有點暈，實際上很簡單。實際的思路是把sql_id當作32進位制，先取出單個字元，然後轉成10進位制，
--然後全部加起來，再與power（2，32）取mod ，結果就是hash_value.展開看看：

SYS@test> select sql_id,hash_value from v$sqlarea where rownum<=1;
SQL_ID        HASH_VALUE
------------- ----------
1fkh93md0802n 3657695316

SELECT 14-level,
    '&1' sql_id,
           SUBSTR ('&1', LEVEL, 1) x1,
           INSTR ('0123456789abcdfghjkmnpqrstuvwxyz', SUBSTR ('&1', LEVEL, 1)) - 1  x2 ,
           (INSTR ('0123456789abcdfghjkmnpqrstuvwxyz', SUBSTR ('&1', LEVEL, 1)) - 1)*power(32,length('&1')-level) x3
      FROM DUAL
CONNECT BY LEVEL <= LENGTH (TRIM ('&1'))
13 rows selected.

14-LEVEL SQL_ID        X1  X2                   X3
-------- ------------- -- --- --------------------
      13 1fkh93md0802n 1    1  1152921504606846976
      12 1fkh93md0802n f   14   504403158265495552
      11 1fkh93md0802n k   18    20266198323167232
      10 1fkh93md0802n h   16      562949953421312
       9 1fkh93md0802n 9    9        9895604649984
       8 1fkh93md0802n 3    3         103079215104
       7 1fkh93md0802n m   19          20401094656
       6 1fkh93md0802n d   13            436207616
       5 1fkh93md0802n 0    0                    0
       4 1fkh93md0802n 8    8               262144
       3 1fkh93md0802n 0    0                    0
       2 1fkh93md0802n 2    2                   64
       1 1fkh93md0802n n   20                   20
13 rows selected.

select mod(sum(x3),power(2,32)) from (
    SELECT 14-level,
    '&1' sql_id,
           SUBSTR ('&1', LEVEL, 1) x1,
           INSTR ('0123456789abcdfghjkmnpqrstuvwxyz', SUBSTR ('&1', LEVEL, 1)) - 1  x2 ,
           (INSTR ('0123456789abcdfghjkmnpqrstuvwxyz', SUBSTR ('&1', LEVEL, 1)) - 1)*power(32,length('&1')-level) x3
      FROM DUAL
CONNECT BY LEVEL <= LENGTH (TRIM ('&1')));

MOD(SUM(X3),POWER(2,32))
------------------------
              3657695316

--這樣就很好理解了。

--而且sql_id取64位，2^5表示1個32位，剩下小於2^4表示sql_id第1個字元，這樣sql_id第1個字元不會大於'h'.
SYS@test> select sql_id from v$sqlarea where substr(sql_id,1,1)>='h';
no rows selected

--為什麼去掉eilo，我不知道，我的感覺就是去掉lo，主要可能就是它與數字01太相近，比較容易混淆。一般程式設計都要求規避l,o作為變
--量.至於ei，估計也是一樣的原因，純粹是我自己亂猜...................