[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) ;
| 0 | SELECT STATEMENT | | 4 | 32 | 4 (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)
3 recursive calls
7 consistent gets
878 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 rows processed
--//修改函數定義
CREATE OR REPLACE FUNCTION sleep1 (seconds IN NUMBER)
sys.dbms_lock.sleep(seconds/10);
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) ;
AAAWP4AAEAAAAJbAAw 49 49 49
AAAWP4AAEAAAAJdAAw 49 49 49
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) ;
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 := 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.總結:
亂,好多概念不好了解.
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)