[20211213]提示precompute_subquery.txt

[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 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 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 deptno from emp,然后直接使用值查询第2
--//步。

4.做10046跟踪看看：

SCOTT@book> @ 10046on 12
Session altered.

SCOTT@book> select * from dept where deptno not in (select 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 DISTINCT * FROM (select deptno from emp)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select * from dept where deptno not in (select deptno from emp)
alter session set events "10046 trace name context off"

--//可以看出执行计划先执行SELECT DISTINCT * FROM (select 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 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
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 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
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
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 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
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 DISTINCT * FROM (select deptno from tx where rownum<=10001)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
select * from dept where deptno  in (select 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 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 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个字段的也可以使用。