[20211213]提示precompute_subquery.txt
–//学习了提示precompute_subquery，提示很明显就是先计算子查询的结果集，直接通过例子说明：
1:环境：
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
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
SQL_ID 7gt57qty3rnw4, child number 0
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
Query Block Name / Object Alias (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
SQL_ID b58wqt9dq1sqq, child number 0
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
Query Block Name / Object Alias (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
20 RESEARCH DALLAS
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
30 SALES CHICAGO
SCOTT@book> @ dpc ” ”
PLAN_TABLE_OUTPUT
select * from dept where deptno in (select /+ PRECOMPUTE_SUBQUERY /
deptno from tx)
Plan hash value: 1476295187
| 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 | | | |
1 – SEL$5DA710D3
2 – SEL$5DA710D3 / DEPT@SEL$1
3 – SEL$5DA710D3 / TX@SEL$2
Predicate Information (identified by operation id):
Original: https://www.cnblogs.com/lfree/p/15682592.html
Author: lfree
Title: [20211213]提示precompute_subquery.txt
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/606220/
转载文章受原作者版权保护。转载请注明原作者出处！