[20211213]提示precompute_subquery.txt
--//學習了提示precompute_subquery，提示很明顯就是先計算子查詢的結果集，直接通過例子說明：
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
SCOTT@book> @ sqlhint PRECOMPUTE_SUBQUERY
NAME SQL_FEATURE CLASS INVERSE TARGET_LEVEL PROPERTY VERSION VERSION_OUTLINE
------------------- -------------------- ------------------------------ --------- ------------ ---------- -------- ---------------
PRECOMPUTE_SUBQUERY QKSFM_TRANSFORMATION PRECOMPUTE_SUBQUERY 2 0 10.2.0.1
2.測試：
SCOTT@book> @sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book> select * from dept where deptno not in (select deptno from emp);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7gt57qty3rnw4, child number 0
select * from dept where deptno not in (select deptno from emp)
Plan hash value: 2100826622
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | 1 | | | 216M(100)| | 1 |00:00:00.01 | 12 | 5 | | | |
|* 1 | HASH JOIN ANTI NA | | 1 | 1 | 23 | 216M (1)|722:44:39 | 1 |00:00:00.01 | 12 | 5 | 1321K| 1321K| 984K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 6 | 0 | | | |
| 3 | TABLE ACCESS FULL| EMP | 1 | 200M| 572M| 216M (1)|722:44:33 | 14 |00:00:00.01 | 6 | 5 | | | |
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / DEPT@SEL$1
3 - SEL$5DA710D3 / EMP@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
3.使用提示PRECOMPUTE_SUBQUERY：
SCOTT@book> select * from dept where deptno not in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from emp);
SQL_ID b58wqt9dq1sqq, child number 0
select * from dept where deptno not in (select /*+ PRECOMPUTE_SUBQUERY
*/ deptno from emp)
Plan hash value: 3383998547
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.01 | 6 |
|* 1 | TABLE ACCESS FULL| DEPT | 1 | 2 | 40 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 6 |
1 - SEL$1 / DEPT@SEL$1
1 - filter(("DEPTNO"<>10 AND "DEPTNO"<>20 AND "DEPTNO"<>30))
--//注意看執行計劃以及過濾條件實際上分開2步先執行select /*+ PRECOMPUTE_SUBQUERY */ deptno from emp,然後直接使用值查詢第2
--//步。
4.做10046跟蹤看看：
SCOTT@book> @ 10046on 12
SCOTT@book> @ 10046off
SCOTT@book> @ttt
tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_36494.trc
--//抽取sql執行語句：
$ extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_36494.trc
SELECT /*+ BYPASS_RECURSIVE_CHECK */ DISTINCT * FROM (select /*+ PRECOMPUTE_SUBQUERY */ deptno from emp)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select * from dept where deptno not in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from emp)
alter session set events '10046 trace name context off'
--//可以看出執行計劃先執行SELECT /*+ BYPASS_RECURSIVE_CHECK */ DISTINCT * FROM (select /*+ PRECOMPUTE_SUBQUERY */ deptno from emp).
--//仔細想想這個提示估計如果執行計劃展開很複雜，先做内層的查詢，然後再做外層查詢，也許在這樣的情況下也許有用。
--//自己學習oracle很久，記憶裡也從來沒人介紹使用過這個提示。
5.補充測試：
--//你還可以看出一個問題，in或者not in進制僅僅支援1000個值，超過會報錯，使用提示PRECOMPUTE_SUBQUERY呢？
SCOTT@book> create table tx as select object_id deptno from all_objects;
Table created.
SCOTT@book> @ gts tx
Gather Table Statistics for table tx...
PL/SQL procedure successfully completed.
SCOTT@book> select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from tx);
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK
30 SALES CHICAGO
SQL_ID 7jzhytdbtvjg7, child number 0
select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */
deptno from tx)
Plan hash value: 1476295187
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 0 | SELECT STATEMENT | | 1 | | | 44 (100)| | 4 |00:00:00.01 | 10 | | | |
|* 1 | HASH JOIN SEMI | | 1 | 4 | 100 | 44 (3)| 00:00:01 | 4 |00:00:00.01 | 10 | 1321K| 1321K| 1017K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL| TX | 1 | 84825 | 414K| 40 (0)| 00:00:01 | 46 |00:00:00.01 | 4 | | | |
3 - SEL$5DA710D3 / TX@SEL$2
--//可以發現提示失效。
SCOTT@book> select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from tx where rownum<=10);
----------------------------------------------------------------------
SQL_ID fr77zgfanduxf, child number 0
deptno from tx where rownum<=10)
Plan hash value: 1996571942
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 2 |00:00:00.01 | 7 |
| 1 | INLIST ITERATOR | | 1 | | | | | 2 |00:00:00.01 | 7 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 10 | 4 | 80 | 2 (0)| 00:00:01 | 2 |00:00:00.01 | 7 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 10 | 4 | | 1 (0)| 00:00:01 | 2 |00:00:00.01 | 5 |
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$1 / DEPT@SEL$1
3 - access(("DEPTNO"=3 OR "DEPTNO"=15 OR "DEPTNO"=20 OR "DEPTNO"=25 OR "DEPTNO"=28 OR "DEPTNO"=29 OR "DEPTNO"=40 OR
"DEPTNO"=41 OR "DEPTNO"=46 OR "DEPTNO"=54))
--//可以發現我加入rownum<=10，可以發現提示生效。在我以為如果rownum<=1001提示失效時，結果有一點點小意外。
SQL_ID 1xzm1bn3ru86q, child number 1
deptno from tx where rownum<=10000)
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 4 |00:00:00.01 | 7 |
|* 1 | TABLE ACCESS FULL| DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 |
1 - filter(("DEPTNO"=2 OR "DEPTNO"=3 OR "DEPTNO"=4 OR "DEPTNO"=5 OR "DEPTNO"=6 OR "DEPTNO"=7 OR
"DEPTNO"=8 OR "DEPTNO"=9 OR "DEPTNO"=10 OR "DEPTNO"=11 OR "DEPTNO"=12 OR "DEPTNO"=13 OR "DEPTNO"=14 OR
"DEPTNO"=15 OR "DEPTNO"=16 OR "DEPTNO"=17 OR "DEPTNO"=18 OR "DEPTNO"=19 OR "DEPTNO"=20 OR "DEPTNO"=21 OR
"DEPTNO"=22 OR "DEPTNO"=23 OR "DEPTNO"=24 OR "DEPTNO"=25 OR "DEPTNO"=26 OR "DEPTNO"=27 OR "DEPTNO"=28 OR
"DEPTNO"=29 OR "DEPTNO"=30 OR "DEPTNO"=31 OR "DEPTNO"=32 OR "DEPTNO"=33 OR "DEPTNO"=34 OR "DEPTNO"=35 OR
"DEPTNO"=36 OR "DEPTNO"=37 OR "DEPTNO"=38 OR "DEPTNO"=39 OR "DEPTNO"=40 OR "DEPTNO"=41 OR "DEPTNO"=42 OR
"DEPTNO"=43 OR "DEPTNO"=44 OR "DEPTNO"=45 OR "DEPTNO"=46 OR "DEPTNO"=47 OR "DEPTNO"=48 OR "DEPTNO"=49 OR
"DEPTNO"=50 OR "DEPTNO"=51 OR "DEPTNO"=52 OR "DEPTNO"=53 OR "DEPTNO"=54 OR "DEPTNO"=55 OR "DEPTNO"=56 OR
"DEPTNO"=57 OR "DEPTNO"=58 OR "DEPTNO"=59 OR "DEPTNO"=60 OR "DEPTNO"=61 OR "DEPTNO"=62 OR "DEPTNO"=63 OR
"DEPTNO"=64 OR "DEPTNO"=65 OR "DEPTNO"=66 OR "DEPTNO"=67 OR "DEPTNO"=68 OR "DEPTNO"=69 OR "DEPTNO"=70 OR
"DEPTNO"=71 OR "DEPTNO"=72 OR "DEPTNO"=73 OR "DEPTNO"=74 OR "DEPTNO"=75 OR "DEPTNO"=76 OR "DEPTNO"=77 OR
"DEPTNO"=78 OR "DEPTNO"=79 OR "DEPTNO"=80 OR "DEPTNO"=81 OR "DEPTNO"=82 OR "DEPTNO"=83 OR "DEPTNO"=84 OR
"DEPTNO"=85 OR "DEPTNO"=86 OR "DEPTNO"=87 OR "DEPTNO"=88 OR "DEPTNO"=89 OR "DEPTNO"=90 OR "DEPTNO"=91 OR
"DEPTNO"=92 OR "DEPTNO"=93 OR "DEPTNO"=94 OR "DEPTNO"=95 OR "DEPTNO"=96 OR "DEPTNO"=97 OR "DEPTNO"=98 OR
"DEPTNO"=99 OR "DEPTNO"=100 OR "DEPTNO"=101 OR "DEPTNO"=102 OR "DEPTNO"=103 OR "DEPTNO"=104 OR "DEPTNO"=105
OR "DEPTNO"=106 OR "DEPTNO"=107 OR "DEPTNO"=108 OR "DEPTNO"=109 OR "DEPTNO"=112 OR "DEPTNO"=113 OR
"DEPTNO"=114 OR "DEPTNO"=115 OR "DEPTNO"=116 OR "DEPTNO"=117 OR "DEPTNO"=118 OR "DEPTNO"=119 OR
"DEPTNO"=120 OR "DEPTNO"=121 OR "DEPTNO"=122 OR "DEPTNO"=123 OR "DEPTNO"=126 OR "DEPTNO"=127 OR
"DEPTNO"=128 OR "DEPTNO"=129 OR "DEPTNO"=130 OR "DEPTNO"=131 OR "DEPTNO"=132 OR "DEPTNO"=133 OR
"DEPTNO"=134 OR "DEPTNO"=135 OR "DEPTNO"=136 OR "DEPTNO"=137 OR "DEPTNO"=138 OR "DEPTNO"=139 OR
"DEPTNO"=140 OR "DEPTNO"=141 OR "DEPTNO"=142 OR "DEPTNO"=143 OR "DEPTNO"=144 OR "DEPTNO"=145 OR
"DEPTNO"=146 OR "DEPTNO"=147 OR "DEPTNO"=148 OR "DEPTNO"=149 OR "DEPTNO"=150 OR "DEPTNO"=151 OR
"DEPTNO"=152 OR "DEPTNO"=153 OR "DEPTNO"=154 OR "DEPTNO"=155 OR "DEPTNO"=158 OR "DEPTNO"=159 OR
"DEPTNO"=160 OR "DEPTNO"=161 OR "DEPTNO"=162 OR "DEPTNO"=163 OR "DEPTNO"=164 OR "DEPTNO"=165 OR
"DEPTNO"=166 OR "DEPTNO"=167 OR "DEPTNO"=168 OR "DEPTNO"=169 OR "DEPTNO"=170 OR "DEPTNO"=171 OR
"DEPTNO"=172 OR "DEPTNO"=173 OR "DEPTNO"=174 OR "DEPTNO"=175 OR "DEPTNO"=176 OR "DEPTNO"=177 OR
"DEPTNO"=178 OR "DEPTNO"=179 OR "DEPTNO"=180 OR "DEPTNO"=181 OR "DEPTNO"=182 OR "DEPTNO"=185 OR
"DEPTNO"=186 OR "DEPTNO"=187 OR "DEPTNO"=188 OR "DEPTNO"=189 OR "DEPTNO"=190 OR "DEPTNO"=191 OR
"DEPTNO"=192 OR "DEPTNO"=195 OR "DEPTNO"=196 OR "DEPTNO"=201 OR "DEPTNO"=202 OR "DEPTNO"=203 OR
"DEPTNO"=206 OR "DEPTNO"=207 OR "DEPTNO"=208 OR "DEPTNO"=213 OR "DEPTNO"=214 OR "DEPTNO"=217 OR
"DEPTNO"=218 OR "DEPTNO"=219 OR "DEPTNO"=220 OR "DEPTNO"=221 OR "DEPTNO"=222 OR "DEPTNO"=223 OR
"DEPTNO"=224 OR "DEPTNO"=225 OR "DEPTNO"=226 OR "DEPTNO"=227 OR "DEPTNO"=228 OR "DEPTNO"=229 OR
"DEPTNO"=230 OR "DEPTNO"=231 OR "DEPTNO"=232 OR "DEPTNO"=233 OR "DEPTNO"=234 OR "DEPTNO"=235 OR
"DEPTNO"=236 OR "DEPTNO"=237 OR "DEPTNO"=238 OR "DEPTNO"=239 OR "DEPTNO"=240 OR "DEPTNO"=241 OR
"DEPTNO"=242 OR "DEPTNO"=245 OR "DEPTNO"=246 OR "DEPTNO"=247 OR "DEPTNO"=248 OR "DEPTNO"=249 OR
"DEPTNO"=250 OR "DEPTNO"=251 OR "DEPTNO"=252 OR "DEPTNO"=253 OR "DEPTNO"=254 OR "DEPTNO"=255 OR
"DEPTNO"=256 OR "DEPTNO"=257 OR "DEPTNO"=258 OR "DEPTNO"=259 OR "DEPTNO"=260 OR "DEPTNO"=261 OR
"DEPTNO"=262 OR "DEPTNO"=263 OR "DEPTNO"=264 OR "DEPTNO"=265 OR "DEPTNO"=266 OR "DEPTNO"=267 OR
"DEPTNO"=268 OR "DEPTNO"=269 OR "DEPTNO"=270 OR "DEPTNO"=271 OR "DEPTNO"=272 OR "DEPTNO"=273 OR
"DEPTNO"=274 OR "DEPTNO"=275 OR "DEPTNO"=276 OR "DEPTNO"=277 OR "DEPTNO"=278 OR "DEPTNO"=279 OR
"DEPTNO"=280 OR "DEPTNO"=281 OR "DEPTNO")
64 rows selected.
SCOTT@book> select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from tx where rownum<=10001);
SQL_ID 3rwsvv3qbtgkm, child number 0
deptno from tx where rownum<=10001)
Plan hash value: 176097179
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | | 7 (100)| | 4 |00:00:00.01 | 19 |
|* 1 | FILTER | | 1 | | | | | 4 |00:00:00.01 | 19 |
| 2 | TABLE ACCESS FULL | DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 |
|* 3 | FILTER | | 4 | | | | | 4 |00:00:00.01 | 12 |
|* 4 | COUNT STOPKEY | | 4 | | | | | 95 |00:00:00.01 | 12 |
| 5 | TABLE ACCESS FULL| TX | 4 | 1 | 5 | 2 (0)| 00:00:01 | 95 |00:00:00.01 | 12 |
3 - SEL$2
5 - SEL$2 / TX@SEL$2
1 - filter( IS NOT NULL)
3 - filter("DEPTNO"=:B1)
4 - filter(ROWNUM<=10001)
--//實際上在ROWNUM<=10001時，提示失效，可以猜測應該有一個10000長度的數組接受這些值。超過提示失效，另外我做了跟蹤可以發現
--//這樣情況依舊會多做1步的查詢。
$ extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_36894.trc
SELECT /*+ BYPASS_RECURSIVE_CHECK */ DISTINCT * FROM (select /*+ PRECOMPUTE_SUBQUERY */ deptno from tx where rownum<=10001)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from tx where rownum<=10001)
--//再補充一個例子：
SCOTT@book> select * from dept where (deptno,dname) in (select /*+ PRECOMPUTE_SUBQUERY */ deptno,'zzz' from tx where rownum<=1);
no rows selected
SQL_ID 5ar509pmhp08h, child number 0
select * from dept where (deptno,dname) in (select /*+
PRECOMPUTE_SUBQUERY */ deptno,'zzz' from tx where rownum<=1)
Plan hash value: 2852011669
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 0 |00:00:00.01 | 2 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 20 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 1 |
1 - filter("DNAME"='zzz')
2 - access("DEPTNO"=20)
--//2個字段的也可以使用。