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

[20190706]Same dog, different leash – functions in SQL.txt https://connor-mcdonald.com/2019/07/03/same-dog-different-leash-functions-in-sql/ --//重复测试： 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