Oracle12c中功能及性能新特点之with子句的增强

1. 设置创建测试表。DROP TABLE test PURGE;CREATE TABLE test ASSELECT 1 AS idFROM ? dualCONNECT BY level <= 1000000;2. WITH子句中的函数WITH子句声明部分可用来定义函数，如下所示。WITH? FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS? BEGIN? ? RETURN p_id;? END;SELECT with_function(id)FROM ? testWHERE ?rownum = 1/WITH_FUNCTION(ID)-----------------? ? ? ? ? ? ? ? 1SQL>有意思的是，当WITH子句中包含PL/SQL声明时，分号";"不再能用作SQL语句的终止符。如果我们使用它，SQL*Plus会等待更多命令文本输入。即使在官方文档中，也是使用了分号“;”和反斜杠“/”的组合。?从名字解析角度看，WITH子句PL/SQL声明部分定义的函数比当前模式中其他同名对象优先级要高。3. WITH子句中的过程即使不被使用，我们也可以在声明部分定义过程。SET SERVEROUTPUT ONWITH? PROCEDURE with_procedure(p_id IN NUMBER) IS? BEGIN? ? DBMS_OUTPUT.put_line(‘p_id=‘ || p_id);? END;SELECT idFROM ? testWHERE ?rownum = 1/? ? ? ? ID----------? ? ? ? ?1SQL>现实中，如果你打算从声明部分的函数中调用一个过程，你可以在声明部分定义一个过程。WITH? PROCEDURE with_procedure(p_id IN NUMBER) IS? BEGIN? ? DBMS_OUTPUT.put_line(‘p_id=‘ || p_id);? END;? FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS? BEGIN? ? with_procedure(p_id);? ? RETURN p_id;? END;SELECT with_function(id)FROM ? testWHERE ?rownum = 1/WITH_FUNCTION(ID)-----------------? ? ? ? ? ? ? ? 1p_id=1SQL>4. PL/SQL支持PL/SQL并不支持该特点。如果视图在PL/SQL中使用将会报编译错误，如下所示。BEGIN? FOR cur_rec IN (WITH? ? ? ? ? ? ? ? ? ? FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS? ? ? ? ? ? ? ? ? ? BEGIN? ? ? ? ? ? ? ? ? ? ? RETURN p_id;? ? ? ? ? ? ? ? ? ? END;? ? ? ? ? ? ? ? ? SELECT with_function(id)? ? ? ? ? ? ? ? ? FROM ? test? ? ? ? ? ? ? ? ? WHERE ?rownum = 1)? LOOP? ? NULL;? END LOOP;END;/FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS? ? ? ? ? ? ? ? ? ? ? ? ? ? ?*ERROR at line 3:ORA-06550: line 3,column 30:PL/SQL: ORA-00905: missing keywordORA-06550: line 2,column 19:PL/SQL: SQL Statement ignoredORA-06550: line 5,column 34:PLS-00103: Encountered the symbol ";" when expecting one of the following:loopSQL>使用动态SQL可以绕过这个限制。SET SERVEROUTPUT ONDECLARE? l_sql ? ? VARCHAR2(32767);? l_cursor ?SYS_REFCURSOR;? l_value ? NUMBER;BEGIN? l_sql := ‘WITH? ? ? ? ? ? ? FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS? ? ? ? ? ? ? BEGIN? ? ? ? ? ? ? ? RETURN p_id;? ? ? ? ? ? ? END;? ? ? ? ? ? SELECT with_function(id)? ? ? ? ? ? FROM ? test? ? ? ? ? ? WHERE ?rownum = 1‘;??? OPEN l_cursor FOR l_sql;? FETCH l_cursor INTO l_value;? DBMS_OUTPUT.put_line(‘l_value=‘ || l_value);? CLOSE l_cursor;END;/l_value=1PL/SQL procedure successfully completed.SQL>PL/SQL中将该特点用于静态SQL是未来版本的事情。5. 性能优势定义行内PL/SQL代码的原因是为了改善性能。下面创建常规函数来进行比较。CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER ISBEGIN? RETURN p_id;END;/运行如下测试，测量行内函数查询消耗的时间和CPU。SET SERVEROUTPUT ONDECLARE? l_time ? ?PLS_INTEGER;? l_cpu ? ? PLS_INTEGER;??? l_sql ? ? VARCHAR2(32767);? l_cursor ?SYS_REFCURSOR;??? TYPE t_tab IS TABLE OF NUMBER;? l_tab t_tab;BEGIN? l_time := DBMS_UTILITY.get_time;? l_cpu ?:= DBMS_UTILITY.get_cpu_time;? l_sql := ‘WITH? ? ? ? ? ? ? FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS? ? ? ? ? ? ? BEGIN? ? ? ? ? ? ? ? RETURN p_id;? ? ? ? ? ? ? END;? ? ? ? ? ? SELECT with_function(id)? ? ? ? ? ? FROM ? test‘;? ? ? ? ? ??? OPEN l_cursor FOR l_sql;? FETCH l_cursor? BULK COLLECT INTO l_tab;? CLOSE l_cursor;??? DBMS_OUTPUT.put_line(‘WITH_FUNCTION ?: ‘ ||? ? ? ? ? ? ? ? ? ? ? ?‘Time=‘ || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ‘ hsecs ‘ ||? ? ? ? ? ? ? ? ? ? ? ?‘CPU Time=‘ || (DBMS_UTILITY.get_cpu_time - l_cpu) || ‘ hsecs ‘);? l_time := DBMS_UTILITY.get_time;? l_cpu ?:= DBMS_UTILITY.get_cpu_time;? l_sql := ‘SELECT normal_function(id)? ? ? ? ? ? FROM ? test‘;? ? ? ? ? ??? OPEN l_cursor FOR l_sql;? FETCH l_cursor? BULK COLLECT INTO l_tab;? CLOSE l_cursor;??? DBMS_OUTPUT.put_line(‘NORMAL_FUNCTION: ‘ ||? ? ? ? ? ? ? ? ? ? ? ?‘Time=‘ || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ‘ hsecs ‘ ||? ? ? ? ? ? ? ? ? ? ? ?‘CPU Time=‘ || (DBMS_UTILITY.get_cpu_time - l_cpu) || ‘ hsecs ‘);?END;/WITH_FUNCTION ?: Time=45 hsecs CPU Time=39 hsecsNORMAL_FUNCTION: Time=129 hsecs CPU Time=113 hsecsPL/SQL procedure successfully completed.SQL>从该测试可以看到，行内函数值消耗了普通函数三分之一的时间和CPU。6. PRAGMA UDF12c 版本前，人们经常会提到PRAGMA UDF，据说可通过行内PL/SQL来提升性能，同时，允许在SQL语句外定义PL/SQL对象。下列代码用PRAGMA重新定义之前的常规函数。CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS? PRAGMA UDF;BEGIN? RETURN p_id;END;/一旦函数被编译，从先前部分运行该函数会产生相当有趣的结果。SET SERVEROUTPUT ONDECLARE? l_time ? ?PLS_INTEGER;? l_cpu ? ? PLS_INTEGER;??? l_sql ? ? VARCHAR2(32767);? l_cursor ?SYS_REFCURSOR;??? TYPE t_tab IS TABLE OF NUMBER;? l_tab t_tab;BEGIN? l_time := DBMS_UTILITY.get_time;? l_cpu ?:= DBMS_UTILITY.get_cpu_time;? l_sql := ‘WITH? ? ? ? ? ? ? FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS? ? ? ? ? ? ? BEGIN? ? ? ? ? ? ? ? RETURN p_id;? ? ? ? ? ? ? END;? ? ? ? ? ? SELECT with_function(id)? ? ? ? ? ? FROM ? test‘;? ? ? ? ? ??? OPEN l_cursor FOR l_sql;? FETCH l_cursor? BULK COLLECT INTO l_tab;? CLOSE l_cursor;??? DBMS_OUTPUT.put_line(‘WITH_FUNCTION ?: ‘ ||? ? ? ? ? ? ? ? ? ? ? ?‘Time=‘ || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ‘ hsecs ‘ ||? ? ? ? ? ? ? ? ? ? ? ?‘CPU Time=‘ || (DBMS_UTILITY.get_cpu_time - l_cpu) || ‘ hsecs ‘);? l_time := DBMS_UTILITY.get_time;? l_cpu ?:= DBMS_UTILITY.get_cpu_time;? l_sql := ‘SELECT normal_function(id)? ? ? ? ? ? FROM ? test‘;? ? ? ? ? ??? OPEN l_cursor FOR l_sql;? FETCH l_cursor? BULK COLLECT INTO l_tab;? CLOSE l_cursor;??? DBMS_OUTPUT.put_line(‘NORMAL_FUNCTION: ‘ ||? ? ? ? ? ? ? ? ? ? ? ?‘Time=‘ || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ‘ hsecs ‘ ||? ? ? ? ? ? ? ? ? ? ? ?‘CPU Time=‘ || (DBMS_UTILITY.get_cpu_time - l_cpu) || ‘ hsecs ‘);?END;/WITH_FUNCTION ?: Time=44 hsecs CPU Time=40 hsecsNORMAL_FUNCTION: Time=33 hsecs CPU Time=29 hsecsPL/SQL procedure successfully completed.SQL>用PRAGMA UDF的独立函数似乎一直比行内函数还快。我以为从PL/SQL中调用PRAGMA UDF定义的函数会失败，可事实似乎不是这么个情况。DECLARE? l_number NUMBER;BEGIN? l_number := normal_function(1);END;/PL/SQL procedure successfully completed.SQL>7. WITH_PLSQL Hint如果包含PL/SQL声明部分的查询不是顶级查询，那么，顶级查询必须包含WITH_PLSQL hint。没有该hint，语句在编译时会失败，如下所示。UPDATE test aSET a.id = (WITH? ? ? ? ? ? ? FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS? ? ? ? ? ? ? BEGIN? ? ? ? ? ? ? ? RETURN p_id;? ? ? ? ? ? ? END;? ? ? ? ? ? SELECT with_function(a.id)? ? ? ? ? ? FROM ? dual);/SET a.id = (WITH? ? ? ? ? ? *ERROR at line 2:ORA-32034: unsupported use of WITH clauseSQL>加上WITH_PLSQL hint后，语句编译通过且如期运行。UPDATE /*+ WITH_PLSQL */ t1 aSET a.id = (WITH? ? ? ? ? ? ? FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS? ? ? ? ? ? ? BEGIN? ? ? ? ? ? ? ? RETURN p_id;? ? ? ? ? ? ? END;? ? ? ? ? ? SELECT with_function(a.id)? ? ? ? ? ? FROM ? dual);/1000000 rows updated.SQL>8. DETERMINISTIC Hint就像刘易斯指出的那样，WITH子句中使用函数会阻止发生DETERMINISTIC优化。SET TIMING ON ARRAYSIZE 15WITH? FUNCTION slow_function(p_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS? BEGIN? ? DBMS_LOCK.sleep(1);? ? RETURN p_id;? END;SELECT slow_function(id)FROM ? testWHERE ?ROWNUM <= 10;/SLOW_FUNCTION(ID)-----------------222222222210 rows selected.Elapsed: 00:00:10.07SQL>9. 标量子查询缓冲前面部分，我们看到行内函数定义对DETERMINISTIC hint优化上的负面影响。 庆幸的是，标量子查询缓冲并不被同样被影响。SET TIMING ONWITH? FUNCTION slow_function(p_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS? BEGIN? ? DBMS_LOCK.sleep(1);? ? RETURN p_id;? END;SELECT (SELECT slow_function(id) FROM dual)FROM ? testWHERE ?ROWNUM <= 10;/(SELECTSLOW_FUNCTION(ID)FROMDUAL)---------------------------------222222222210 rows selected.Elapsed: 00:00:01.04SQL>

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!