[20180602]函數與标量子查詢4.txt
--//前面看http://www.cnblogs.com/kerrycode/p/9099507.html連結,裡面提到:
通俗來将，當使用标量子查詢的時候，ORACLE會将子查詢結果緩存在哈希表中， 如果後續的記錄出現同樣的值，優化器通過緩存在哈希
表中的值，判斷重複值不用重複調用函數，直接使用上次計算結果即可。進而減少調用函數次數，進而達到優化性能的效果。另外在
ORACLE 10和11中， 哈希表隻包含了255個Buckets，也就是說它能存儲255個不同值，如果超過這個範圍，就會出現散列沖突，那些出現
散列沖突的值就會重複調用函數，即便如此，依然能達到大幅改善性能的效果。
--//我前面的測試我一直沒有測試出哈希表隻包含了255個Buckets,而且在48,75存在hash沖突時,猜測實際上采用蠻力猜測得到的結果.
--//注:我前面的測試僅僅知道75與前面1-74的某個數hash存在沖突,具體到底是那個猜測還是挺浪費時間的.
--//今天繼續驗證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 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(0.01);
create table t as select rownum id1,rownum id2 from dual connect by level<=400;
ALTER TABLE t MINIMIZE RECORDS_PER_BLOCK ;
insert into t select * from t;
commit ;
2.測試:
--//我前面的測試已經測出75,84,87,89,93,96,103,104,109,122,139,152,163,169,171,176,193,195,196,206,216,228,231,234,244,246,254
--//存在hash沖突,共27個數.
set autot traceonly
select rowid,t.*,(select sleep(id2) from dual) s from t
where id2 not in (75,84,87,89,93,96,103,104,109,122,139,152,163,169,171,176,193,195,196,206,216,228,231,234,244,246,254)
and id2<=255+27;
Execution Plan
----------------------------------------------------------
Plan hash value: 1032660217
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 510 | 19380 | 4 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T | 510 | 19380 | 4 (0)| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID2"<>75 AND "ID2"<>84 AND "ID2"<>87 AND "ID2"<>89 AND
"ID2"<>93 AND "ID2"<>96 AND "ID2"<>103 AND "ID2"<>104 AND "ID2"<>109
AND "ID2"<>122 AND "ID2"<>139 AND "ID2"<>152 AND "ID2"<>163 AND
"ID2"<>169 AND "ID2"<>171 AND "ID2"<>176 AND "ID2"<>193 AND "ID2"<>195
AND "ID2"<>196 AND "ID2"<>206 AND "ID2"<>216 AND "ID2"<>228 AND
"ID2"<>231 AND "ID2"<>234 AND "ID2"<>244 AND "ID2"<>246 AND "ID2"<>254
AND "ID2"<=282)
Statistics
262 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
16660 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
--//262 遞歸,說明還是存在hash沖突.不能這樣操作.
--//262-255 = 7 還有7個存在沖突.
--//建立腳本:
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 := 2;
....
exec :x := 252;
exec :x := 253;
exec :x := 254;
exec :x := 255;
..
exec :x := 300;
set autot off
--//多執行幾次,避免其它遞歸影響.
spool bz.txt
@ ay.txt
spool off
--//取出數字
$ egrep 'recursive calls|rows processed' bz.txt | paste - - | cut -c9-11,37-40 >| bb.txt
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
256 284 282
259 288 286
262 292 290
265 296 294
268 300 298
274 307 305
278 312 310
290 325 323
295 331 329
299 336 334
300 338 336
38 rows selected.
where id2 not in (75,84,87,89,93,96,103,104,109,122,139,152,163,169,171,176,193,195,196,206,216,228,231,234,244,246,254,256,259,262,265,268,274,278)
and id2<=289;
Plan hash value: 1032660217
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 532 | 4256 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T | 532 | 4256 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID2"<=289 AND "ID2"<>75 AND "ID2"<>84 AND "ID2"<>87 AND
"ID2"<>89 AND "ID2"<>93 AND "ID2"<>96 AND "ID2"<>103 AND "ID2"<>104 AND
"ID2"<>109 AND "ID2"<>122 AND "ID2"<>139 AND "ID2"<>152 AND "ID2"<>163
AND "ID2"<>169 AND "ID2"<>171 AND "ID2"<>176 AND "ID2"<>193 AND
"ID2"<>195 AND "ID2"<>196 AND "ID2"<>206 AND "ID2"<>216 AND "ID2"<>228
AND "ID2"<>231 AND "ID2"<>234 AND "ID2"<>244 AND "ID2"<>246 AND
"ID2"<>254 AND "ID2"<>256 AND "ID2"<>259 AND "ID2"<>262 AND "ID2"<>265
AND "ID2"<>268 AND "ID2"<>274 AND "ID2"<>278)
Statistics
255 recursive calls
9 consistent gets
510 rows processed
--//正好255.
--//也就是1-289,排除75,84,87,89,93,96,103,104,109,122,139,152,163,169,171,176,193,195,196,206,216,228,231,234,244,246,254,256,259,262,265,268,274,278這些值.
select rowid,t.*,(select sleep(id2) from dual) s from t
and id2<=:x;
:x 遞歸次數
290 257
291 258
--//說明作者的連結http://www.cnblogs.com/kerrycode/p/9099507.html提到的哈希表隻包含了255個Buckets，也就是說它能存儲255個不同值不對.
where id2 not in (75,84,87,89,93,96,103,104,109,122,139,152,163,169,171,176,193,195,196,206,216,228,231,234,244,246,254,256,259,262,265,268,274,278,290)
and id2<=291;
SCOTT@book> select a,b/2 b from t1 where b/2>=280;
A B
---------- ----------
314 280
315 281
316 282
317 283
318 284
319 285
320 286
321 287
322 288
323 289
325 290 <= 這個值還是存在hash沖突.
326 291
327 292
328 293
329 294
331 295
332 296
333 297
334 298
336 299
338 300
21 rows selected.
--//也就是不止255個Buckets.具體多少,不在探究了,放棄!!
![阿裡雲天池龍珠計劃SQL訓練營day1[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)