[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)
RETURN NUMBER
is
d_date date;
BEGIN
select sysdate into d_date from dual;
--//sys.dbms_lock.sleep(0.01);
RETURN seconds;
END;
/
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 := 1;
/
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.
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,256,259,262,265,268,274,278)
and id2<=289;
Execution Plan
----------------------------------------------------------
Plan hash value: 1032660217
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 532 | 4256 | 4 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | | 2 (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
0 db block gets
9 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
--//正好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
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<=:x;
:x 递归次数
290 257
291 258
--//说明作者的链接http://www.cnblogs.com/kerrycode/p/9099507.html提到的哈希表只包含了255个Buckets，也就是说它能存储255个不同值不对.
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,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.具体多少,不在探究了,放弃!!
