[20200213]函式nullif使用.txt

[20200213]函式nullif使用.txt

--//nullif函式自己知道很少使用，我發現人家寫的指令碼使用它避免做除法時如果除數為0報ORA-01476的問題。
--//透過例子說明，比如我寫的指令碼：

$ cat d_buffer.sql
col executions1 new_value x1
col buffer_gets1 new_value x2
col ELAPSED_TIME1 new_value x3
col ROWS_PROCESSED1 new_value x4

col executions2 new_value y1
col buffer_gets2 new_value y2
col ELAPSED_TIME2 new_value y3
col ROWS_PROCESSED2 new_value y4

col var3 new_value var3

select executions executions1,buffer_gets buffer_gets1,elapsed_time elapsed_time1,rows_processed rows_processed1  from v$sqlarea where sql_id='&&1';

prompt ... sleep &&2 , waiting ....
host sleep &&2

select executions executions2,buffer_gets buffer_gets2,elapsed_time elapsed_time2 ,rows_processed rows_processed2 from v$sqlarea where sql_id='&&1';

select &y2-&x2 "總buffer_gets", (&y2-&x2)/(&y1-&x1) "每次buffer_gets",&y1- &x1 "執行次數" , &y3-&x3 "總執行時間", (&y3-&x3)/(&y1-&x1) "每次執行時間" , &y4-&x4 "總處理記錄數",(&y4-&x4)/(&y1-&x1) "平均處理記錄數" from dual ;


--//主要是最佳化sql語句後，我發現有時候清除sql語句從共享池後，如果再次執行後executions,buffer_gets等資訊並沒有徹底清除。
--//這樣我很難比較最佳化後效果，因此寫了上面指令碼。指令碼有1個問題如果檢測時間內沒有執行，這樣executions沒有變化，作為除數就會報錯。

SYS@book> @ d_buffer 4xamnunv51w9j 2
EXECUTIONS1 BUFFER_GETS1 ELAPSED_TIME1 ROWS_PROCESSED1
----------- ------------ ------------- ---------------
          1           62          5988               1

... sleep 2 , waiting ....

EXECUTIONS2 BUFFER_GETS2 ELAPSED_TIME2 ROWS_PROCESSED2
----------- ------------ ------------- ---------------
          1           62          5988               1

select         62-        62 "總buffer_gets", (        62-        62)/(         1-         1) "每次buffer_gets",         1-          1 "執行次數" ,       5988-      5988 "總執行時間", (      5988-      5988)/(         1-         1) "每次執行時間" ,          1-         1 "總處理記錄數",(         1-         1)/(         1-         1) "平均處理記錄數" from dual
                                                                     *
ERROR at line 1:
ORA-01476: divisor is equal to zero

--//利用nullif函式就能很好的避免這個問題。
SCOTT@book> select nullif(1,0),nullif(0,0),nullif(1,1) from dual ;
NULLIF(1,0) NULLIF(0,0) NULLIF(1,1)
----------- ----------- -----------
          1
--//nullif就是比較引數1，引數2，如果相等就輸出NULL。
--//只要第1個引數等於第2個引數，輸出就是NULL型別。改寫如下就不會報錯了。

$ cat d_buffer.sql
col executions1 new_value x1
col buffer_gets1 new_value x2
col ELAPSED_TIME1 new_value x3
col ROWS_PROCESSED1 new_value x4

col executions2 new_value y1
col buffer_gets2 new_value y2
col ELAPSED_TIME2 new_value y3
col ROWS_PROCESSED2 new_value y4

col var3 new_value var3

select executions executions1,buffer_gets buffer_gets1,elapsed_time elapsed_time1,rows_processed rows_processed1  from v$sqlarea where sql_id='&&1';

prompt ... sleep &&2 , waiting ....
host sleep &&2

select executions executions2,buffer_gets buffer_gets2,elapsed_time elapsed_time2 ,rows_processed rows_processed2 from v$sqlarea where sql_id='&&1';

SELECT &y2 - &x2 "總buffer_gets"
      , (&y2 - &x2) / NULLIF (&y1 - &x1, 0) "每次buffer_gets"
      ,&y1 - &x1 "執行次數"
      ,&y3 - &x3 "總執行時間"
      , (&y3 - &x3) / NULLIF (&y1 - &x1, 0) "每次執行時間"
      ,&y4 - &x4 "總處理記錄數"
      , (&y4 - &x4) / NULLIF (&y1 - &x1, 0) "平均處理記錄數"
  FROM DUAL;


SYS@book> @ d_buffer 4xamnunv51w9j 1
EXECUTIONS1 BUFFER_GETS1 ELAPSED_TIME1 ROWS_PROCESSED1
----------- ------------ ------------- ---------------
          2           50          3834               2

... sleep 1 , waiting ....

EXECUTIONS2 BUFFER_GETS2 ELAPSED_TIME2 ROWS_PROCESSED2
----------- ------------ ------------- ---------------
          2           50          3834               2

總buffer_gets 每次buffer_gets   執行次數 總執行時間 每次執行時間 總處理記錄數 平均處理記錄數
------------- --------------- ---------- ---------- ------------ ------------ --------------
            0                          0          0                         0