[20190706]Same dog, different leash – functions in SQL.txt

[20190706]Same dog, different leash – functions in SQL.txt



--//重複測試：

1.環境：
SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.測試：
SCOTT@test01p> create table t as select * from dba_objects where rownum <= 20;
Table created.

create or replace
function xgetrand(pval in number) return number is
  begin
    return round(dbms_random.value(0,20));
  end;
/
Function created.

SCOTT@test01p> alter session set statistics_level = all;
Session altered.

SCOTT@test01p> select * from ( select object_id, xgetrand(object_id) gg from t) where gg = 10;
 OBJECT_ID         GG
---------- ----------
         6         17
        35         15

--//可以發現gg<>10.檢查執行計劃.

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  dxnxprt1v9fw2, child number 1
-------------------------------------
select * from ( select object_id, xgetrand(object_id) gg from t) where gg = 10
Plan hash value: 1601196873
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |      10 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |      1 |     3 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |      10 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$F5BB74E1 / T@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("XGETRAND"("OBJECT_ID")=10)

--//可以發現sql展開變成了 select object_id, xgetrand(object_id) gg from t where xgetrand(object_id) = 10;
--//但是使用dbms_utility.expand_sql_text展開,發現並沒有轉換.

SCOTT@test01p> @ expand_sql_text12c.sql dxnxprt1v9fw2
PL/SQL procedure successfully completed.

M_SQL_OUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "A1"."OBJECT_ID" "OBJECT_ID","A1"."GG" "GG" FROM  (SELECT "A2"."OBJECT_ID" "OBJECT_ID","SCOTT"."XGETRAND"("A2"."OBJECT_ID") "GG" FROM "SCOTT"."T" "A2") "A1" WHERE "A1"."GG"=10

--//改寫如下:
SCOTT@test01p> with a as (select /*+ MATERIALIZE */ object_id, xgetrand(object_id) gg  from t) select * from a where gg=10;
no rows selected

--//如果使用提示MATERIALIZE,發現結果正確!!
--//作者的一些說明:

Already you can see the scope for the function being called twice per row – once for the WHERE clause, and once for the
SELECT part. In fact, for all we know it could be called three times, or four times. You (the coder) does not have
control over that decision. For the sake of discussion, let's assume it is called only twice. The first execution of the
function (on line 2 above) returned 10 twice across the 20 rows (because we got 2 rows back in the result set), but then
we ran the function again (on line1) as we gave back each row, hence the counter-intuitive output.

--//您已經可以看到函式的作用域每行呼叫兩次-一次用於WHERE子句，一次用於SELECT部分。事實上，據我們所知，它可以被稱為三次，
--//或者四次。您(編碼器)對該決定沒有控制權。為了便於討論，讓我們假設它只被呼叫了兩次。函式的第一次執行(在上面的第2行)在
--//20行中返回10次(因為結果集中有2行)，但是在返回每一行時，我們再次執行該函式(在第1行)，因此產生了違反直覺的輸出。

In the original example, ALL_OBJECTS is being used as a source for rows which is a complex view. The function call could
be pushed anywhere deep into this view, which means it might be executed tens of thousands of times, and many of those
executions might return a result of "10".

--//在最初的示例中，All_Objects被用作行的源，這是一個複雜的檢視。函式呼叫可以被推入此檢視的任何地方，這意味著它可能被執
--//行數萬次，其中許多執行可能返回“10”的結果。

3.繼續測試:
--//修改函式定義:
create or replace
function xgetrand(pval in number) return number is
  begin
    dbms_application_info.set_client_info(userenv('client_info')+1 );
    return round(dbms_random.value(0,20));
  end;
/

SCOTT@test01p> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.

SCOTT@test01p> select * from ( select object_id, xgetrand(object_id) gg from t) where gg = 10;
 OBJECT_ID         GG
---------- ----------
        49         17

SCOTT@test01p> select  userenv('client_info')  from dual ;
USERENV('CLIENT_INFO')
----------------------
21
--//你可以發現呼叫了21次,T表記錄的行數是20行.一個非常奇怪的情況.

SCOTT@test01p> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.

SCOTT@test01p> with a as (select /*+ MATERIALIZE */ object_id, xgetrand(object_id) gg  from t) select * from a where gg=10;
 OBJECT_ID         GG
---------- ----------
        38         10

SCOTT@test01p> select  userenv('client_info')  from dual ;
USERENV('CLIENT_INFO')
----------------------
20

SCOTT@test01p> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.

SCOTT@test01p> select object_id, xgetrand(object_id) gg from t where xgetrand(object_id) = 10;
 OBJECT_ID         GG
---------- ----------
        24         12

SCOTT@test01p> select  userenv('client_info')  from dual ;
USERENV('CLIENT_INFO')
----------------------
21

--//可以這麼理解查詢條件是xgetrand(object_id) = 10,如果發現相等,在select輸出時再次呼叫xgetrand(object_id)一次.
--//這樣輸出就不會是10.同樣像我前面的測試輸出2行,如果使用下面的測試你可以發現select  userenv('client_info')  from dual ;
--//的輸出是22.我建立執行指令碼aaa.txt
--//cat aaa.txt
exec dbms_application_info.set_client_info(0);
select * from ( select object_id, xgetrand(object_id) gg from t) where gg = 10;
select  userenv('client_info')  from dual ;

--//反覆測試:
SCOTT@test01p> @ aaa.txt
PL/SQL procedure successfully completed.

 OBJECT_ID         GG
---------- ----------
        45         17
        56          4
        35         11

USERENV('CLIENT_INFO')
-------------------------
23
--//出現1個呼叫23次的情況

4.附上expand_sql_text12c.sql指令碼.
--//expand_sql_text12c.sql
variable m_sql_out clob

declare
    m_sql_in  clob ;
begin
        select sql_fulltext into m_sql_in from v$sqlarea where sql_id='&&1';
--        dbms_sql2.expand_sql_text(        -- 11g
--                m_sql_in,
--                :m_sql_out
--        );
        dbms_utility.expand_sql_text(     -- 12c
                m_sql_in,
                :m_sql_out
        );
end;
/
set long 20000
column m_sql_out format a160
print m_sql_out