[20180602]函數與标量子查詢3.txt
--//前面看http://www.cnblogs.com/kerrycode/p/9099507.html連結,裡面提到:
通俗來将，當使用标量子查詢的時候，ORACLE會将子查詢結果緩存在哈希表中， 如果後續的記錄出現同樣的值，優化器通過緩存在哈希
表中的值，判斷重複值不用重複調用函數，直接使用上次計算結果即可。進而減少調用函數次數，進而達到優化性能的效果。另外在
ORACLE 10和11中， 哈希表隻包含了255個Buckets，也就是說它能存儲255個不同值，如果超過這個範圍，就會出現散列沖突，那些出現
散列沖突的值就會重複調用函數，即便如此，依然能達到大幅改善性能的效果。
--//昨天的測試有一些問題自己沒有很好的了解.晚上看連結http://blog.itpub.net/267265/viewspace-2144765/
--//當時可以發現,如果鄰近的值一樣,函數調用僅僅1次.
--//這樣才明白oracle如何操作的.還是通過例子說明問題.
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
grant execute on sys.dbms_lock to scott;
CREATE OR REPLACE FUNCTION sleep1 (seconds IN NUMBER)
RETURN NUMBER
is
d_date date;
BEGIN
select sysdate into d_date from dual;
sys.dbms_lock.sleep(seconds/10);
RETURN seconds;
END;
/
CREATE OR REPLACE FUNCTION sleep (seconds IN NUMBER)
--//sys.dbms_lock.sleep(1);
create table t as select rownum id1,mod(rownum-1,255)+1 id2 from dual connect by level<=255;
ALTER TABLE t MINIMIZE RECORDS_PER_BLOCK ;
insert into t select * from t;
commit ;
--//分析表略.
2.我前面的測試帶入參數48,75存在hash沖突.
SCOTT@test01p> select rowid,t.*,(select sleep1(id2) from dual) s from t where id2 in ( 48 , 75) ;
ROWID ID1 ID2 S
------------------ ---------- ---------- ----------
AAAaQxAAJAAAAC7AAv 48 48 48
AAAaQxAAJAAAAC7ABK 75 75 75
AAAaQxAAJAAAAC+AAv 48 48 48
AAAaQxAAJAAAAC+ABK 75 75 75
Elapsed: 00:00:19.82
SCOTT@test01p> set autot traceonly
Elapsed: 00:00:19.83
Execution Plan
----------------------------------------------------------
Plan hash value: 1032660217
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 4 | 32 | 8 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T | 4 | 32 | 4 (0)| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID2"=48 OR "ID2"=75)
Statistics
3 recursive calls
....
4 rows processed
--//注:多執行幾次,這樣反應的遞歸調用結果比較準确.
--//(48+75+75)/10= 19.80
--//哈希表隻包含了255個Buckets，也就是說它能存儲255個不同值，
--//還真不好表達,我前面的遞規調用3次.sleep1(48) 1次,sleep1(75) 2次.
--//第1次,先執行sleep1(48),結果放入哈希表,
--//第2次,然後執行sleep1(75),由于48與75存在hash沖突,隻能重複調用函數sleep1(75),但是結果不放入哈希表.
--//第3次,接着執行sleep1(48),這個已經存在哈希表,不必調用sleep1(48).
--//第4次,執行sleep1(75),由于前面執行的結果不放入哈希表,隻能重複調用函數sleep1(75).
3.如果執行如下:
select a ,(select sleep1(a) from dual) s from
(select 48 a from dual
union all
select 75 a from dual
select 48 a from dual
select 75 a from dual);
A S
---------- ----------
48 48
75 75
--//再次執行:
--//(48+75+75)/10 = 19.8
--//從結果看sleep1(48) 2次,sleep1(75) 3次.
--//第3次,接着執行sleep1(75),由于前面執行sleep1(75),雖然結果不存在哈希表,因為可以從前面的執行知道結果,不必調用sleep1(75).
--//說明: 也就是如果鄰近的值一樣,函數調用僅僅1次.
--//第4次,執行sleep1(48),由于結果存在哈希表,不必調用sleep1(48).
--//第5次,執行sleep1(75),由于前面的查詢sleep1(48),并且sleep1(78)的結果不存在哈希表,隻能重複調用函數sleep1(75).
--//這樣sleep1(48) 1次,sleep1(75) 2次.按照這個模式分析,就知道調用測試.
(select 75 a from dual
Elapsed: 00:00:12.30
SCOTT@test01p> /
2 recursive calls
--//(48+75)/10 = 12.3
--//按照前面的模式分析,就知道調用測試.
--//從結果看sleep1(48) 1次,sleep1(75) 1次.
3.總結:
--//如果你仔細看連接配接http://blog.itpub.net/267265/viewspace-2144765/
--//可以發現與11g的不同.但是如果執行如下測試:
SCOTT@test01p> alter session set statistics_level = all;
Session altered.
SCOTT@test01p> select rowid,t.*,(select sleep(id2) from dual) s from t ;
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 54u1r6skg6pt7, child number 0
select rowid,t.*,(select sleep(id2) from dual) s from t
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | | 514 (100)| | 510 |00:00:00.01 | 10 |
| 1 | FAST DUAL | | 282 | 1 | | 2 (0)| 00:00:01 | 282 |00:00:00.01 | 0 |
| 2 | TABLE ACCESS FULL| T | 1 | 510 | 4080 | 4 (0)| 00:00:01 | 510 |00:00:00.01 | 10 |
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2 / DUAL@SEL$2
2 - SEL$1 / T@SEL$1
20 rows selected.
--//id=2 fast dual 執行282次.說明遞歸才282次.還是許多問題不了解,有計劃還是在11g下測試看看.
![Oracle的基本操作[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)