[20180601]函數與標量子查詢2.txt
--//昨天看http://www.cnblogs.com/kerrycode/p/9099507.html鏈接,里面提到:
通俗來將，當使用標量子查詢的時候，ORACLE會將子查詢結果緩存在哈希表中， 如果后續的記錄出現同樣的值，優化器通過緩存在哈希
表中的值，判斷重復值不用重復調用函數，直接使用上次計算結果即可。從而減少調用函數次數，從而達到優化性能的效果。另外在
ORACLE 10和11中， 哈希表只包含了255個Buckets，也就是說它能存儲255個不同值，如果超過這個范圍，就會出現散列沖突，那些出現
散列沖突的值就會重復調用函數，即便如此，依然能達到大幅改善性能的效果。
--//我自己也重復測試驗證看看.
1.環境:
SCOTT@book> @ ver1
PORT_STRING??????????????????? VERSION??????? BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx??????????? 11.2.0.4.0???? Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
grant execute on sys.dbms_lock to scott;
CREATE OR REPLACE FUNCTION sleep (seconds IN NUMBER)
RETURN NUMBER
is
d_date date;
BEGIN
? select sysdate into d_date from dual;
--//sys.dbms_lock.sleep(0.1);
? RETURN seconds;
END;
/
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.測試:
--//建立測試腳本:
set autot traceonly
select rowid,t.*,(select sleep(id2) from dual) s from t ;
--//select rowid,t.*,(select sleep(id1) from dual) s from t ;
SCOTT@book> select rowid,t.*,(select sleep(id2) from dual) s from t ;
510 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1032660217
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? |?? 510 |? 4080 |???? 4?? (0)| 00:00:01 |
|?? 1 |? FAST DUAL??????? |????? |???? 1 |?????? |???? 2?? (0)| 00:00:01 |
|?? 2 |? TABLE ACCESS FULL| T??? |?? 510 |? 4080 |???? 4?? (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
??????? 282? recursive calls
????????? 0? db block gets
????????? 9? consistent gets
????????? 0? physical reads
????????? 0? redo size
????? 16624? bytes sent via SQL*Net to client
??????? 542? bytes received via SQL*Net from client
????????? 4? SQL*Net roundtrips to/from client
????????? 0? sorts (memory)
????????? 0? sorts (disk)
??????? 510? rows processed
--//從遞歸看調用函數282次.注意為了測試準確,多執行幾次,才能比較準確測出函數調用次數.
--//按照前面的介紹,難道存在hash沖突.
--//如果查詢where id2 in ( 48 , 75) ,可以發現遞歸調用3次,返回4行.
--//為什么不是4次呢?
SCOTT@book> select rowid,t.*,(select sleep(id2) from dual) s from t where id2 in ( 48 , 75) ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1032660217
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? |???? 4 |??? 32 |???? 4?? (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
????????? 0? db block gets
????????? 7? consistent gets
????????? 0? physical reads
????????? 0? redo size
??????? 878? bytes sent via SQL*Net to client
??????? 520? bytes received via SQL*Net from client
????????? 2? SQL*Net roundtrips to/from client
????????? 0? sorts (memory)
????????? 0? sorts (disk)
????????? 4? rows processed
--//修改函數定義
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;
/
SCOTT@book> select rowid,t.*,(select sleep1(id2) from dual) s from t where id2 in ( 48 , 75) ;
ROWID???????????????????? ID1??????? ID2????????? S
------------------ ---------- ---------- ----------
AAAWP4AAEAAAAJbAAv???????? 48???????? 48???????? 48
AAAWP4AAEAAAAJbABK???????? 75???????? 75???????? 75
AAAWP4AAEAAAAJdAAv???????? 48???????? 48???????? 48
AAAWP4AAEAAAAJdABK???????? 75???????? 75???????? 75
Elapsed: 00:00:19.81
SCOTT@book> select rowid,t.*,(select sleep1(id2) from dual) s from t where id2 in ( 49 , 75) ;
ROWID???????????????????? ID1??????? ID2????????? S
------------------ ---------- ---------- ----------
AAAWP4AAEAAAAJbAAw???????? 49???????? 49???????? 49
AAAWP4AAEAAAAJbABK???????? 75???????? 75???????? 75
AAAWP4AAEAAAAJdAAw???????? 49???????? 49???????? 49
AAAWP4AAEAAAAJdABK???????? 75???????? 75???????? 75
Elapsed: 00:00:12.41
--// 48+75+75 = 198, 49+75 = 124
--// 可以發現從時間看,查詢 id2 in ( 48 , 75) 時,調用sleep1(48)1次,sleep1(75)2次.
--// 而查詢 id2 in ( 49 , 75) 時,調用sleep1(49)1次,sleep1(75)1次.
--// 換一句話講我的理解,調用48,75存在沖突,都進入bucket中,而出現沖突時會重復調用函數.
--//如果我建立索引:
SCOTT@book> create index i_t_id2 on t(id2);
Index created.
SCOTT@book> set timing on
SCOTT@book> select /*+ index(t i_t_id2 ) */ rowid,t.*,(select sleep1(id2) from dual) s from t where id2 in ( 49 , 75) ;
ROWID???????????????????? ID1??????? ID2????????? S
------------------ ---------- ---------- ----------
AAAWP4AAEAAAAJbAAw???????? 49???????? 49???????? 49
AAAWP4AAEAAAAJdAAw???????? 49???????? 49???????? 49
AAAWP4AAEAAAAJbABK???????? 75???????? 75???????? 75
AAAWP4AAEAAAAJdABK???????? 75???????? 75???????? 75
Elapsed: 00:00:12.41
--//這樣執行時間變成12.4秒,還是不好理解.
--//似乎進入buckets時放在前面.這樣slee1(75)僅僅調用1次.好煩,那位能解析清楚....
SCOTT@book> drop index i_t_id2;
Index dropped.
--//我前面查詢id2=1,255,遞歸282次,估計不存在1個bucket 3個值的情況.
--//這樣有282-255 = 27值存在沖突(假設哈希表只包含了255個Buckets)
3.如何確定hash沖突值呢?
--//建立腳本:
variable x number;
exec :x := 1;
select rowid,t.*,(select sleep(id2) from dual) s from t where id2<=:x;
set autot traceonly
exec :x := 1;
/
exec :x := 2;
/
....
exec :x := 252;
/
exec :x := 253;
/
exec :x := 254;
/
exec :x := 255;
/
set autot off
--//多執行幾次,避免其它遞歸影響.
spool az.txt
@ ay.txt
spool off
$ egrep 'recursive calls|rows processed' az.txt | paste - -
????????? 1? recursive calls????????????? 2? rows processed
????????? 2? recursive calls????????????? 4? rows processed
????????? 3? recursive calls????????????? 6? rows processed
????????? 4? recursive calls????????????? 8? rows processed
????????? 5? recursive calls???????????? 10? rows processed
????????? 6? recursive calls???????????? 12? rows processed
????????? 7? recursive calls???????????? 14? rows processed
????????? 8? recursive calls???????????? 16? rows processed
????????? 9? recursive calls???????????? 18? rows processed
???????? 10? recursive calls???????????? 20? rows processed
???????? 11? recursive calls???????????? 22? rows processed
???????? 12? recursive calls???????????? 24? rows processed
???????? 13? recursive calls???????????? 26? rows processed
???????? 14? recursive calls???????????? 28? rows processed
???????? 15? recursive calls???????????? 30? rows processed
???????? 16? recursive calls???????????? 32? rows processed
???????? 17? recursive calls???????????? 34? rows processed
???????? 18? recursive calls???????????? 36? rows processed
???????? 19? recursive calls???????????? 38? rows processed
???????? 20? recursive calls???????????? 40? rows processed
???????? 21? recursive calls???????????? 42? rows processed
???????? 22? recursive calls???????????? 44? rows processed
???????? 23? recursive calls???????????? 46? rows processed
???????? 24? recursive calls???????????? 48? rows processed
???????? 25? recursive calls???????????? 50? rows processed
???????? 26? recursive calls???????????? 52? rows processed
???????? 27? recursive calls???????????? 54? rows processed
???????? 28? recursive calls???????????? 56? rows processed
???????? 29? recursive calls???????????? 58? rows processed
???????? 30? recursive calls???????????? 60? rows processed
???????? 31? recursive calls???????????? 62? rows processed
???????? 32? recursive calls???????????? 64? rows processed
???????? 33? recursive calls???????????? 66? rows processed
???????? 34? recursive calls???????????? 68? rows processed
???????? 35? recursive calls???????????? 70? rows processed
???????? 36? recursive calls???????????? 72? rows processed
???????? 37? recursive calls???????????? 74? rows processed
???????? 38? recursive calls???????????? 76? rows processed
???????? 39? recursive calls???????????? 78? rows processed
???????? 40? recursive calls???????????? 80? rows processed
???????? 41? recursive calls???????????? 82? rows processed
???????? 42? recursive calls???????????? 84? rows processed
???????? 43? recursive calls???????????? 86? rows processed
???????? 44? recursive calls???????????? 88? rows processed
???????? 45? recursive calls???????????? 90? rows processed
???????? 46? recursive calls???????????? 92? rows processed
???????? 47? recursive calls???????????? 94? rows processed
???????? 48? recursive calls???????????? 96? rows processed
???????? 49? recursive calls???????????? 98? rows processed
???????? 50? recursive calls??????????? 100? rows processed
???????? 51? recursive calls??????????? 102? rows processed
???????? 52? recursive calls??????????? 104? rows processed
???????? 53? recursive calls??????????? 106? rows processed
???????? 54? recursive calls??????????? 108? rows processed
???????? 55? recursive calls??????????? 110? rows processed
???????? 56? recursive calls??????????? 112? rows processed
???????? 57? recursive calls??????????? 114? rows processed
???????? 58? recursive calls??????????? 116? rows processed
???????? 59? recursive calls??????????? 118? rows processed
???????? 60? recursive calls??????????? 120? rows processed
???????? 61? recursive calls??????????? 122? rows processed
???????? 62? recursive calls??????????? 124? rows processed
???????? 63? recursive calls??????????? 126? rows processed
???????? 64? recursive calls??????????? 128? rows processed
???????? 65? recursive calls??????????? 130? rows processed
???????? 66? recursive calls??????????? 132? rows processed
???????? 67? recursive calls??????????? 134? rows processed
???????? 68? recursive calls??????????? 136? rows processed
???????? 69? recursive calls??????????? 138? rows processed
???????? 70? recursive calls??????????? 140? rows processed
???????? 71? recursive calls??????????? 142? rows processed
???????? 72? recursive calls??????????? 144? rows processed
???????? 73? recursive calls??????????? 146? rows processed
???????? 74? recursive calls??????????? 148? rows processed
???????? 76? recursive calls??????????? 150? rows processed
???????? 77? recursive calls??????????? 152? rows processed
???????? 78? recursive calls??????????? 154? rows processed
???????? 79? recursive calls??????????? 156? rows processed
???????? 80? recursive calls??????????? 158? rows processed
???????? 81? recursive calls??????????? 160? rows processed
???????? 82? recursive calls??????????? 162? rows processed
???????? 83? recursive calls??????????? 164? rows processed
???????? 84? recursive calls??????????? 166? rows processed
???????? 86? recursive calls??????????? 168? rows processed
???????? 87? recursive calls??????????? 170? rows processed
???????? 88? recursive calls??????????? 172? rows processed
???????? 90? recursive calls??????????? 174? rows processed
???????? 91? recursive calls??????????? 176? rows processed
???????? 93? recursive calls??????????? 178? rows processed
???????? 94? recursive calls??????????? 180? rows processed
???????? 95? recursive calls??????????? 182? rows processed
???????? 96? recursive calls??????????? 184? rows processed
???????? 98? recursive calls??????????? 186? rows processed
???????? 99? recursive calls??????????? 188? rows processed
??????? 100? recursive calls??????????? 190? rows processed
??????? 102? recursive calls??????????? 192? rows processed
??????? 103? recursive calls??????????? 194? rows processed
??????? 104? recursive calls??????????? 196? rows processed
??????? 105? recursive calls??????????? 198? rows processed
??????? 106? recursive calls??????????? 200? rows processed
??????? 107? recursive calls??????????? 202? rows processed
??????? 108? recursive calls??????????? 204? rows processed
??????? 110? recursive calls??????????? 206? rows processed
??????? 112? recursive calls??????????? 208? rows processed
??????? 113? recursive calls??????????? 210? rows processed
??????? 114? recursive calls??????????? 212? rows processed
??????? 115? recursive calls??????????? 214? rows processed
??????? 116? recursive calls??????????? 216? rows processed
??????? 118? recursive calls??????????? 218? rows processed
??????? 119? recursive calls??????????? 220? rows processed
??????? 120? recursive calls??????????? 222? rows processed
??????? 121? recursive calls??????????? 224? rows processed
??????? 122? recursive calls??????????? 226? rows processed
??????? 123? recursive calls??????????? 228? rows processed
??????? 124? recursive calls??????????? 230? rows processed
??????? 125? recursive calls??????????? 232? rows processed
??????? 126? recursive calls??????????? 234? rows processed
??????? 127? recursive calls??????????? 236? rows processed
??????? 128? recursive calls??????????? 238? rows processed
??????? 129? recursive calls??????????? 240? rows processed
??????? 130? recursive calls??????????? 242? rows processed
??????? 132? recursive calls??????????? 244? rows processed
??????? 133? recursive calls??????????? 246? rows processed
??????? 134? recursive calls??????????? 248? rows processed
??????? 135? recursive calls??????????? 250? rows processed
??????? 136? recursive calls??????????? 252? rows processed
??????? 137? recursive calls??????????? 254? rows processed
??????? 138? recursive calls??????????? 256? rows processed
??????? 139? recursive calls??????????? 258? rows processed
??????? 140? recursive calls??????????? 260? rows processed
??????? 141? recursive calls??????????? 262? rows processed
??????? 142? recursive calls??????????? 264? rows processed
??????? 143? recursive calls??????????? 266? rows processed
??????? 144? recursive calls??????????? 268? rows processed
??????? 145? recursive calls??????????? 270? rows processed
??????? 146? recursive calls??????????? 272? rows processed
??????? 147? recursive calls??????????? 274? rows processed
??????? 148? recursive calls??????????? 276? rows processed
??????? 150? recursive calls??????????? 278? rows processed
??????? 151? recursive calls??????????? 280? rows processed
??????? 152? recursive calls??????????? 282? rows processed
??????? 153? recursive calls??????????? 284? rows processed
??????? 154? recursive calls??????????? 286? rows processed
??????? 155? recursive calls??????????? 288? rows processed
??????? 156? recursive calls??????????? 290? rows processed
??????? 157? recursive calls??????????? 292? rows processed
??????? 158? recursive calls??????????? 294? rows processed
??????? 159? recursive calls??????????? 296? rows processed
??????? 160? recursive calls??????????? 298? rows processed
??????? 161? recursive calls??????????? 300? rows processed
??????? 162? recursive calls??????????? 302? rows processed
??????? 164? recursive calls??????????? 304? rows processed
??????? 165? recursive calls??????????? 306? rows processed
??????? 166? recursive calls??????????? 308? rows processed
??????? 167? recursive calls??????????? 310? rows processed
??????? 168? recursive calls??????????? 312? rows processed
??????? 169? recursive calls??????????? 314? rows processed
??????? 170? recursive calls??????????? 316? rows processed
??????? 171? recursive calls??????????? 318? rows processed
??????? 172? recursive calls??????????? 320? rows processed
??????? 173? recursive calls??????????? 322? rows processed
??????? 174? recursive calls??????????? 324? rows processed
??????? 176? recursive calls??????????? 326? rows processed
??????? 177? recursive calls??????????? 328? rows processed
??????? 178? recursive calls??????????? 330? rows processed
??????? 179? recursive calls??????????? 332? rows processed
??????? 180? recursive calls??????????? 334? rows processed
??????? 181? recursive calls??????????? 336? rows processed
??????? 183? recursive calls??????????? 338? rows processed
??????? 184? recursive calls??????????? 340? rows processed
??????? 186? recursive calls??????????? 342? rows processed
??????? 187? recursive calls??????????? 344? rows processed
??????? 188? recursive calls??????????? 346? rows processed
??????? 189? recursive calls??????????? 348? rows processed
??????? 190? recursive calls??????????? 350? rows processed
??????? 192? recursive calls??????????? 352? rows processed
??????? 193? recursive calls??????????? 354? rows processed
??????? 194? recursive calls??????????? 356? rows processed
??????? 195? recursive calls??????????? 358? rows processed
??????? 196? recursive calls??????????? 360? rows processed
??????? 197? recursive calls??????????? 362? rows processed
??????? 198? recursive calls??????????? 364? rows processed
??????? 199? recursive calls??????????? 366? rows processed
??????? 200? recursive calls??????????? 368? rows processed
??????? 201? recursive calls??????????? 370? rows processed
??????? 202? recursive calls??????????? 372? rows processed
??????? 203? recursive calls??????????? 374? rows processed
??????? 204? recursive calls??????????? 376? rows processed
??????? 205? recursive calls??????????? 378? rows processed
??????? 206? recursive calls??????????? 380? rows processed
??????? 207? recursive calls??????????? 382? rows processed
??????? 208? recursive calls??????????? 384? rows processed
??????? 210? recursive calls??????????? 386? rows processed
??????? 211? recursive calls??????????? 388? rows processed
??????? 213? recursive calls??????????? 390? rows processed
??????? 215? recursive calls??????????? 392? rows processed
??????? 216? recursive calls??????????? 394? rows processed
??????? 217? recursive calls??????????? 396? rows processed
??????? 218? recursive calls??????????? 398? rows processed
??????? 219? recursive calls??????????? 400? rows processed
??????? 220? recursive calls??????????? 402? rows processed
??????? 221? recursive calls??????????? 404? rows processed
??????? 222? recursive calls??????????? 406? rows processed
??????? 223? recursive calls??????????? 408? rows processed
??????? 224? recursive calls??????????? 410? rows processed
??????? 226? recursive calls??????????? 412? rows processed
??????? 227? recursive calls??????????? 414? rows processed
??????? 228? recursive calls??????????? 416? rows processed
??????? 229? recursive calls??????????? 418? rows processed
??????? 230? recursive calls??????????? 420? rows processed
??????? 231? recursive calls??????????? 422? rows processed
??????? 232? recursive calls??????????? 424? rows processed
??????? 233? recursive calls??????????? 426? rows processed
??????? 234? recursive calls??????????? 428? rows processed
??????? 235? recursive calls??????????? 430? rows processed
??????? 237? recursive calls??????????? 432? rows processed
??????? 238? recursive calls??????????? 434? rows processed
??????? 239? recursive calls??????????? 436? rows processed
??????? 240? recursive calls??????????? 438? rows processed
??????? 241? recursive calls??????????? 440? rows processed
??????? 242? recursive calls??????????? 442? rows processed
??????? 243? recursive calls??????????? 444? rows processed
??????? 244? recursive calls??????????? 446? rows processed
??????? 245? recursive calls??????????? 448? rows processed
??????? 246? recursive calls??????????? 450? rows processed
??????? 247? recursive calls??????????? 452? rows processed
??????? 248? recursive calls??????????? 454? rows processed
??????? 250? recursive calls??????????? 456? rows processed
??????? 251? recursive calls??????????? 458? rows processed
??????? 252? recursive calls??????????? 460? rows processed
??????? 254? recursive calls??????????? 462? rows processed
??????? 255? recursive calls??????????? 464? rows processed
??????? 256? recursive calls??????????? 466? rows processed
??????? 258? recursive calls??????????? 468? rows processed
??????? 259? recursive calls??????????? 470? rows processed
??????? 260? recursive calls??????????? 472? rows processed
??????? 261? recursive calls??????????? 474? rows processed
??????? 262? recursive calls??????????? 476? rows processed
??????? 263? recursive calls??????????? 478? rows processed
??????? 264? recursive calls??????????? 480? rows processed
??????? 265? recursive calls??????????? 482? rows processed
??????? 266? recursive calls??????????? 484? rows processed
??????? 267? recursive calls??????????? 486? rows processed
??????? 269? recursive calls??????????? 488? rows processed
??????? 270? recursive calls??????????? 490? rows processed
??????? 272? recursive calls??????????? 492? rows processed
??????? 273? recursive calls??????????? 494? rows processed
??????? 274? recursive calls??????????? 496? rows processed
??????? 275? recursive calls??????????? 498? rows processed
??????? 276? recursive calls??????????? 500? rows processed
??????? 277? recursive calls??????????? 502? rows processed
??????? 278? recursive calls??????????? 504? rows processed
??????? 279? recursive calls??????????? 506? rows processed
??????? 281? recursive calls??????????? 508? rows processed
??????? 282? recursive calls??????????? 510? rows processed
--//取出數字
$ egrep 'recursive calls|rows processed' az.txt | paste - - | cut -c9-11,37-40
SCOTT@book> create table t1 ( a number ,b number);
Table created.
--//改寫成inert插入表t1.
SELECT id2, r, rp
? FROM (? SELECT b / 2 id2, a r, LAG (a) OVER (ORDER BY b) rp
??????????? FROM t1
??????? ORDER BY a)
WHERE r - rp >= 2;
ID2|R|RP
75|76|74
84|86|84
87|90|88
89|93|91
93|98|96
96|102|100
103|110|108
104|112|110
109|118|116
122|132|130
139|150|148
152|164|162
163|176|174
169|183|181
171|186|184
176|192|190
193|210|208
195|213|211
196|215|213
206|226|224
216|237|235
228|250|248
231|254|252
234|258|256
244|269|267
246|272|270
254|281|279
4.總結:
亂,好多概念不好理解.
