[20180602]函式與標量子查詢4.txt
[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.具體多少,不在探究了,放棄!!
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2155564/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180602]函式與標量子查詢3.txt函式
- [20180625]函式與標量子查詢13(補充)函式
- 標量子查詢
- [20180612]函式與標量子查詢10.txt函式
- [20180626]函式與標量子查詢14.txt函式
- 20180601]函式與標量子查詢2.txt函式
- [20180611]函式與標量子查詢9.txt函式
- [20180607]函式與標量子查詢8.txt函式
- 標量子查詢（二）
- 標量子查詢(一)
- 用WITH…AS改寫標量子查詢
- 都是標量子查詢惹的禍
- MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]MySql筆記
- MYSQL count標量子查詢改left joinMySql
- 用LEFT JOIN優化標量子查詢優化
- Oracle常用抓取SQL-標量子查詢等OracleSQL
- 帶彙總的標量子查詢改寫
- 標量子查詢優化（用group by 代替distinct）優化
- mysql 標量子查詢和現金盤程式製作非法子查詢MySql
- MySQL之集合函式與分組查詢MySql函式
- Solr複雜查詢一：函式查詢Solr函式
- pytest標記：查詢測試策略、標記測試函式函式
- 影響Oracle標量子查詢效能的三個因素Oracle
- 宣告與函式、函式指標函式指標
- 【TUNE_ORACLE】列出有標量子查詢的SQL參考OracleSQL
- 效能為王：SQL標量子查詢的優化案例分析SQL優化
- [20150709]慎用標量子查詢.txt
- PostgreSQL 查詢替換函式SQL函式
- Solr的函式查詢（FunctionQuery）Solr函式Function
- 子串查詢函式strstr函式
- [20200325]慎用標量子查詢.txt
- GreatSQL 最佳化技巧：將 MINUS 改寫為標量子查詢SQL
- Vlookup大叔與一對多查詢（Excel函式集團）Excel函式
- 二分查詢 - 手寫模板與自帶函式函式
- Java-MySql-函式、多表查詢JavaMySql函式
- [Mysql 查詢語句]——集合函式MySql函式
- SQL查詢中用到的函式SQL函式
- iPhone查詢序列號生成函式iPhone函式