Oracle12c中功能及效能新特點之with子句的增強
1. 設定
建立測試表。
DROP TABLE test PURGE;
CREATE TABLE test AS
SELECT 1 AS id
FROM dual
CONNECT 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 test
WHERE rownum = 1
/
WITH_FUNCTION(ID)
-----------------
1
SQL>
有意思的是，當WITH子句中包含PL/SQL宣告時，分號";"不再能用作SQL語句的終止符。如果我們使用它，SQL*Plus會等待更多命令文字輸入。即使在官方文件中，也是使用了分號“;”和反斜槓“/”的組合。
從名字解析角度看，WITH子句PL/SQL宣告部分定義的函式比當前模式中其他同名物件優先順序要高。
3. WITH子句中的過程
即使不被使用，我們也可以在宣告部分定義過程。
SET SERVEROUTPUT ON
WITH
PROCEDURE with_procedure(p_id IN NUMBER) IS
BEGIN
DBMS_OUTPUT.put_line('p_id=' || p_id);
END;
SELECT id
FROM test
WHERE rownum = 1
/
ID
----------
1
SQL>
現實中，如果你打算從宣告部分的函式中呼叫一個過程，你可以在宣告部分定義一個過程。
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 test
WHERE rownum = 1
/
WITH_FUNCTION(ID)
-----------------
1
p_id=1
SQL>
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 keyword
ORA-06550: line 2, column 19:
PL/SQL: SQL Statement ignored
ORA-06550: line 5, column 34:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop
SQL>
使用動態SQL可以繞過這個限制。
SET SERVEROUTPUT ON
DECLARE
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=1
PL/SQL procedure successfully completed.
SQL>
PL/SQL中將該特點用於靜態SQL是未來版本的事情。
5. 效能優勢
定義行內PL/SQL程式碼的原因是為了改善效能。下面建立常規函式來進行比較。
CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
/
執行如下測試，測量行內函式查詢消耗的時間和CPU。
SET SERVEROUTPUT ON
DECLARE
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 hsecs
NORMAL_FUNCTION: Time=129 hsecs CPU Time=113 hsecs
PL/SQL procedure successfully completed.
SQL>
從該測試可以看到，行內函式值消耗了普通函式三分之一的時間和CPU。
6. PRAGMA UDF
12c 版本前，人們經常會提到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 ON
DECLARE
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 hsecs
NORMAL_FUNCTION: Time=33 hsecs CPU Time=29 hsecs
PL/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 a
SET 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 clause
SQL>
加上WITH_PLSQL hint後，語句編譯透過且如期執行。
UPDATE /*+ WITH_PLSQL */ t1 a
SET 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 15
WITH
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 test
WHERE ROWNUM <= 10;
/
SLOW_FUNCTION(ID)
-----------------
1
1
1
1
1
1
1
1
1
1
10 rows selected.
Elapsed: 00:00:10.07
SQL>
9. 標量子查詢緩衝
前面部分，我們看到行內函式定義對DETERMINISTIC hint最佳化上的負面影響。 慶幸的是，標量子查詢緩衝並不被同樣被影響。
SET TIMING ON
WITH
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 test
WHERE ROWNUM <= 10;
/
(SELECTSLOW_FUNCTION(ID)FROMDUAL)
---------------------------------
1
1
1
1
1
1
1
1
1
1
10 rows selected.
Elapsed: 00:00:01.04
SQL>
建立測試表。
DROP TABLE test PURGE;
CREATE TABLE test AS
SELECT 1 AS id
FROM dual
CONNECT 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 test
WHERE rownum = 1
/
WITH_FUNCTION(ID)
-----------------
1
SQL>
有意思的是，當WITH子句中包含PL/SQL宣告時，分號";"不再能用作SQL語句的終止符。如果我們使用它，SQL*Plus會等待更多命令文字輸入。即使在官方文件中，也是使用了分號“;”和反斜槓“/”的組合。
從名字解析角度看，WITH子句PL/SQL宣告部分定義的函式比當前模式中其他同名物件優先順序要高。
3. WITH子句中的過程
即使不被使用，我們也可以在宣告部分定義過程。
SET SERVEROUTPUT ON
WITH
PROCEDURE with_procedure(p_id IN NUMBER) IS
BEGIN
DBMS_OUTPUT.put_line('p_id=' || p_id);
END;
SELECT id
FROM test
WHERE rownum = 1
/
ID
----------
1
SQL>
現實中，如果你打算從宣告部分的函式中呼叫一個過程，你可以在宣告部分定義一個過程。
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 test
WHERE rownum = 1
/
WITH_FUNCTION(ID)
-----------------
1
p_id=1
SQL>
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 keyword
ORA-06550: line 2, column 19:
PL/SQL: SQL Statement ignored
ORA-06550: line 5, column 34:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop
SQL>
使用動態SQL可以繞過這個限制。
SET SERVEROUTPUT ON
DECLARE
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=1
PL/SQL procedure successfully completed.
SQL>
PL/SQL中將該特點用於靜態SQL是未來版本的事情。
5. 效能優勢
定義行內PL/SQL程式碼的原因是為了改善效能。下面建立常規函式來進行比較。
CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
/
執行如下測試，測量行內函式查詢消耗的時間和CPU。
SET SERVEROUTPUT ON
DECLARE
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 hsecs
NORMAL_FUNCTION: Time=129 hsecs CPU Time=113 hsecs
PL/SQL procedure successfully completed.
SQL>
從該測試可以看到，行內函式值消耗了普通函式三分之一的時間和CPU。
6. PRAGMA UDF
12c 版本前，人們經常會提到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 ON
DECLARE
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 hsecs
NORMAL_FUNCTION: Time=33 hsecs CPU Time=29 hsecs
PL/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 a
SET 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 clause
SQL>
加上WITH_PLSQL hint後，語句編譯透過且如期執行。
UPDATE /*+ WITH_PLSQL */ t1 a
SET 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 15
WITH
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 test
WHERE ROWNUM <= 10;
/
SLOW_FUNCTION(ID)
-----------------
1
1
1
1
1
1
1
1
1
1
10 rows selected.
Elapsed: 00:00:10.07
SQL>
9. 標量子查詢緩衝
前面部分，我們看到行內函式定義對DETERMINISTIC hint最佳化上的負面影響。 慶幸的是，標量子查詢緩衝並不被同樣被影響。
SET TIMING ON
WITH
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 test
WHERE ROWNUM <= 10;
/
(SELECTSLOW_FUNCTION(ID)FROMDUAL)
---------------------------------
1
1
1
1
1
1
1
1
1
1
10 rows selected.
Elapsed: 00:00:01.04
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8484829/viewspace-2121148/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle12c功能增強 新特性之管理功能的增強Oracle
- Oracle12c中效能最佳化&功能增強新特性之臨時undoOracle
- Oracle12c 中RAC功能增強新特性之ASM&GridOracleASM
- Oracle12c中效能最佳化增強新特性之資料庫智慧快閃記憶體Oracle資料庫記憶體
- Oracle12c中效能最佳化&功能增強新特性之重大突破——記憶體列儲存新特性Oracle記憶體
- Oracle12c中效能最佳化&功能增強新特性之全域性索引DROP和TRUNCATE 分割槽的非同步維護Oracle索引非同步
- oracle12c新特點之可插拔資料庫（Pluggable Database,PDB）Oracle資料庫Database
- Oracle12c中容錯&效能新特性之表空間組Oracle
- Oracle12c中資料泵新特性之功能增強(expdp, impdp)Oracle
- Oracle12c功能增強新特性之維護&升級&恢復&資料泵等Oracle
- Java 7新特點與效能測試Java
- Oracle12c(12.1)中效能最佳化&功能增強之透過引數THREADED_EXECTION使用多執行緒模型Oraclethread執行緒模型
- MySQL 8 複製效能的增強MySql
- Oracle12c新特性之基本操作Oracle
- 詳談Oracle12c新特點容器資料庫&可插拔資料庫（CDB&PDB）Oracle資料庫
- oracle dataguard的兩個新特點。Oracle
- PHP中陣列賦值效能及過程PHP陣列賦值
- PostgreSQL10.0preview效能增強-分割槽表效能增強(plan階段加速)SQLView
- 阿里雲記憶體增強型re4e雲伺服器配置效能及優惠價格阿里記憶體伺服器
- 機器學習之良好特徵的特點機器學習特徵
- ABAP 740新的OPEN SQL增強特性SQL
- SQLite中的WHERE子句SQLite
- SQLite中的FROM子句SQLite
- PostgreSQL10.0preview效能增強-hashindexmetapagecache、高併發增強SQLViewIndex
- Java 8 中 CAS 的增強Java
- JAVA中函式的特點Java函式
- Oracle利用Windows的高階特性增強效能OracleWindows
- oracle12c新特性（6）--備份特定使用者特權Oracle
- 洗掃車工作原理，效能特點
- IBM P570 效能特點IBM
- SQL之limit子句的使用SQLMIT
- 關於Oracle中ASH功能的特別說明，oracle10個/11g的新特點Oracle
- C# 9 新特性 —— 增強的 foreachC#
- C# 9 新特性 —— 增強的模式匹配C#模式
- Kotlin的特點及各版本新特性Kotlin
- SQL中 where 子句和having子句中的區別SQL
- 用歸納偏置來增強你的模型效能模型
- VS Code 中的增強 code CLI