[20211213]提示precompute_subquery.txt

来源：这里教程网时间：2026-03-03 17:20:09 作者：

[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); DEPTNO DNAME LOC ---------- -------------- ------------- 40 OPERATIONS BOSTON SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- 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 | -------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / DEPT@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("DEPTNO"<>10 AND "DEPTNO"<>20 AND "DEPTNO"<>30)) --//注意看执行计划以及过滤条件实际上分开2步先执行select /*+ PRECOMPUTE_SUBQUERY */ deptno from emp,然后直接使用值查询第2 --//步。 4.做10046跟踪看看： SCOTT@book> @ 10046on 12 Session altered. SCOTT@book> select * from dept where deptno not in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from emp); DEPTNO DNAME LOC ---------- -------------- ------------- 40 OPERATIONS BOSTON SCOTT@book> @ 10046off Session altered. 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); DEPTNO DNAME LOC ---------- -------------- ------------- 20 RESEARCH DALLAS 40 OPERATIONS BOSTON 10 ACCOUNTING NEW YORK 30 SALES CHICAGO SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- 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 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------ Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$5DA710D3 2 - SEL$5DA710D3 / DEPT@SEL$1 3 - SEL$5DA710D3 / TX@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("DEPTNO"="DEPTNO") --//可以发现提示失效。 SCOTT@book> select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from tx where rownum<=10); DEPTNO DNAME LOC ---------- -------------- ------------- 20 RESEARCH DALLAS 40 OPERATIONS BOSTON SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ---------------------------------------------------------------------- SQL_ID fr77zgfanduxf, child number 0 ------------------------------------- select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */ 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 | ---------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / DEPT@SEL$1 3 - SEL$1 / DEPT@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 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提示失效时，结果有一点点小意外。 SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 1xzm1bn3ru86q, child number 1 ------------------------------------- select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */ deptno from tx where rownum<=10000) 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)| | 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 | -------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / DEPT@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 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); DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 3rwsvv3qbtgkm, child number 0 ------------------------------------- select * from dept where deptno in (select /*+ PRECOMPUTE_SUBQUERY */ 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 | ----------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / DEPT@SEL$1 3 - SEL$2 5 - SEL$2 / TX@SEL$2 Predicate Information (identified by operation id): --------------------------------------------------- 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) alter session set events '10046 trace name context off' --//再补充一个例子： SCOTT@book> select * from dept where (deptno,dname) in (select /*+ PRECOMPUTE_SUBQUERY */ deptno,'zzz' from tx where rownum<=1); no rows selected SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- 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 | --------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / DEPT@SEL$1 2 - SEL$1 / DEPT@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DNAME"='zzz') 2 - access("DEPTNO"=20) --//2个字段的也可以使用。