本文的目的是要研究一下nested subquery。所谓nested subquery，就是指出现在where语句中用括号括起来的子查询。通常oracle对这些子查询的处理情况是把它们当做一个独立的对象，嵌套在父查询中执行。比如说下面这个例子
SQL> select /*+ gather_plan_statistics */ ename from emp where exists (select /*+ no_unnest */ 1 from dept where deptno=emp.deptno and deptno=20); -------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | -------------------------------------------------------------------------------------- |* 1 | FILTER | | 1 | | 5 |00:00:00.01 | 7 | | 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 4 | |* 3 | FILTER | | 3 | | 1 |00:00:00.01 | 3 | |* 4 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 3 | --------------------------------------------------------------------------------------
仔细看一下执行计划，如果你了解FILTER操作，就会明白，子查询嵌套在父查询中执行了。这里的执行过程大概就是父查询对EMP表返回的每一条记录，都要拿给子查询去执行。跟NESTED LOOP差不多。但是随着CBO的升级，很多时候oracle可以把这种嵌套关系解开，让子查询中的表可以和父查询一起join，当然CBO这样做的前提是它认为这样做开销更小。 但是CBO并没有这么智能，有时候它可能错误的把嵌套解开了，或者错误的使用了嵌套执行，那么我们的任务就是纠正它的错误，下面要了解的内容就是什么情况下CBO无法解开嵌套执行 以及 我们怎么样利用hint 让cbo选择 嵌套或者解开嵌套。 下面是本文主要要讲的内容。
- 1. 官方文档上关于 nested subquery的信息
- 2. 什么样的subquery无法unnest
- 3. 如何使用un_nest 和 unnest hint
- 4. 扩展一下，如何使用push_subq
1 官方文档上关于 nested subquery的信息
Subqueries are nested when they appear in the WHERE clause of the parent statement.When Oracle Database evaluates a statement with a nested subquery, it must evaluate the subquery portion multiple times and may overlook some efficient access paths or joins.(这不一定 按照之前的实验，有时候sub query 即使是 correlated subquery 也可以采用join)
Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins. The optimizer can unnest most subqueries, with some exceptions. Those exceptions include hierarchical subqueries and subqueries that contain a ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or a correlated reference to a query block that is not the immediate outer query block of the subquery.
Assuming no restrictions exist, the optimizer automatically unnests some (but not all) of the following nested subqueries:
-
Uncorrelated
INsubqueries -
INandEXISTScorrelated subqueries, as long as they do not contain aggregate functions or aGROUPBYclause
You can enable extended subquery unnesting by instructing the optimizer to unnest additional types of subqueries:
-
You can unnest an uncorrelated
NOTINsubquery by specifying theHASH_AJorMERGE_AJhint in the subquery.（这里的两个AJ到底是什么意思） -
You can unnest other subqueries by specifying the
UNNESThint in the subquery.
2 什么样的subquery无法unnest
总结一下上面官方文档的内容，要注意这里的subquery是指在where从句中的，不是from中的。这些subquery通常会被嵌套调用，但是CBO在计算了开销之后可以根据情况解开。或者你可以通过HINT让cbo去解开嵌套。当然有些情况CBO无法解开嵌套。 这些情况列举如下：
- 2.1 hierarchical subqueries
- 2.2 subqueries that contain a
ROWNUMpseudocolumn - 2.3 one of the set operators
- 2.4 a nested aggregate function
- 2.5 correlated reference to a query block that is not the immediate outer query block of the subquery
我们实验一下看看哪些情况无法解开。
2.1 hierarchical subqueries
ok， 这个例子不太好想，也不常见，再议
2.2 subqueries that contain a ROWNUM pseudocolumn
我们使用两个查询来对比。第一个是普通的nested subquery。它要查出deptno为20的ename。 第二个查询简单做了些修改，在subquery中加了个rownum>0.这是一个不影响逻辑的更改。按理说执行计划应该不会变。
SQL> select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20); ENAME ------------------------------ FORD ADAMS SCOTT JONES SMITH SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------- SQL_ID 5v00mt710844w, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20) Plan hash value: 2319207582 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------- |* 1 | HASH JOIN SEMI | | 1 | 5 | 5 |00:00:00.01 | 7 | | 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 3 | | 3 | VIEW | VW_SQ_1 | 1 | 1 | 1 |00:00:00.01 | 4 | |* 4 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 4 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("DEPTNO"="EMP"."DEPTNO") 4 - filter("DEPTNO"=20) 22 rows selected. SQL> select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20 and rownum>0); ENAME ------------------------------ SMITH JONES SCOTT ADAMS FORD SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------- SQL_ID f5r68h4r1man7, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20 and rownum>0) Plan hash value: 351916621 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- |* 1 | FILTER | | 1 | | 5 |00:00:00.01 | 7 | | 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 4 | | 3 | COUNT | | 3 | | 1 |00:00:00.01 | 3 | |* 4 | FILTER | | 3 | | 1 |00:00:00.01 | 3 | |* 5 | FILTER | | 3 | | 1 |00:00:00.01 | 3 | |* 6 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 3 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( IS NOT NULL) 4 - filter(ROWNUM>0) 5 - filter(:B1=20) 6 - filter("DEPTNO"=:B1) 26 rows selected.
但是我们看到，第二个执行计划变成了嵌套执行的模式。因为subquery中使用了rownum，所以无法unnest。为什么加了rownum就无法unnest了呢？ 很好理解。因为subquery中有了rownum的限制就无法把subquery和上层查询 join了。
2.3 one of the set operators。
SQL> select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20); ENAME ------------------------------ FORD ADAMS SCOTT JONES SMITH SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 5v00mt710844w, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20) Plan hash value: 2319207582 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------- |* 1 | HASH JOIN SEMI | | 1 | 5 | 5 |00:00:00.01 | 7 | | 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 3 | | 3 | VIEW | VW_SQ_1 | 1 | 1 | 1 |00:00:00.01 | 4 | |* 4 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 4 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("DEPTNO"="EMP"."DEPTNO") 4 - filter("DEPTNO"=20) 22 rows selected. SQL> select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20 union select 1 from dept where deptno=emp.deptno and deptno=20); ENAME ------------------------------ SMITH JONES SCOTT ADAMS FORD SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 63yydqmyfp006, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20 union select 1 from dept where deptno=emp.deptno and deptno=20) Plan hash value: 850121941 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------- |* 1 | FILTER | | 1 | | 5 |00:00:00.01 | 10 | | 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 4 | | 3 | SORT UNIQUE | | 3 | 2 | 1 |00:00:00.01 | 6 | | 4 | UNION-ALL | | 3 | | 2 |00:00:00.01 | 6 | |* 5 | FILTER | | 3 | | 1 |00:00:00.01 | 3 | |* 6 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 3 | |* 7 | FILTER | | 3 | | 1 |00:00:00.01 | 3 | |* 8 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 3 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( IS NOT NULL) 5 - filter(:B1=20) 6 - filter("DEPTNO"=:B1) 7 - filter(:B1=20) 8 - filter("DEPTNO"=:B1)
只是加了一段 不影响逻辑的 Union，就让子查询无法unnest
2.4 a nested aggregate function
如下面的例子所示，子查询改写成具有aggregate function的形式 ， subquery就会嵌套执行
SQL> select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20); ENAME ------------------------------ FORD ADAMS SCOTT JONES SMITH SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 5v00mt710844w, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20) Plan hash value: 2319207582 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------- |* 1 | HASH JOIN SEMI | | 1 | 5 | 5 |00:00:00.01 | 7 | | 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 3 | | 3 | VIEW | VW_SQ_1 | 1 | 1 | 1 |00:00:00.01 | 4 | |* 4 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 4 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("DEPTNO"="EMP"."DEPTNO") 4 - filter("DEPTNO"=20) 22 rows selected. SQL> select /*+ gather_plan_statistics */ ename from emp where (select count(*) from dept where deptno=emp.deptno and deptno=20) >0; ENAME ------------------------------ SMITH JONES SCOTT ADAMS FORD SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID f8j8ssu947nfc, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ ename from emp where (select count(*) from dept where deptno=emp.deptno and deptno=20) >0 Plan hash value: 868944553 --------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------- |* 1 | FILTER | | 1 | | 5 |00:00:00.01 | 7 | | 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 4 | | 3 | SORT AGGREGATE | | 3 | 1 | 3 |00:00:00.01 | 3 | |* 4 | FILTER | | 3 | | 1 |00:00:00.01 | 3 | |* 5 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 3 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(>0) 4 - filter(:B1=20) 5 - filter("DEPTNO"=:B1) 24 rows selected. SQL>
2.5 correlated reference to a query block that is not the immediate outer query block of the subquery
SQL> select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20); ENAME ------------------------------ FORD ADAMS SCOTT JONES SMITH SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 5v00mt710844w, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20) Plan hash value: 2319207582 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------- |* 1 | HASH JOIN SEMI | | 1 | 5 | 5 |00:00:00.01 | 7 | | 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 3 | | 3 | VIEW | VW_SQ_1 | 1 | 1 | 1 |00:00:00.01 | 4 | |* 4 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 4 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("DEPTNO"="EMP"."DEPTNO") 4 - filter("DEPTNO"=20) 22 rows selected. SQL> select /*+ gather_plan_statistics */ ename from emp e1 where exists 2 (select 1 from dual where exists ( select 1 from dept where deptno=e1.deptno and deptno=20)); ENAME ------------------------------ SMITH JONES SCOTT ADAMS FORD SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 5sg5s2u5wfbbh, child number 0 ------------------------------------- select /*+ gather_plan_statistics */ ename from emp e1 where exists (select 1 from dual where exists ( select 1 from dept where deptno=e1.deptno and deptno=20)) Plan hash value: 1158821859 --------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------- |* 1 | FILTER | | 1 | | 5 |00:00:00.01 | 7 | | 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 |00:00:00.01 | 4 | |* 3 | FILTER | | 3 | | 1 |00:00:00.01 | 3 | | 4 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | |* 5 | FILTER | | 3 | | 1 |00:00:00.01 | 3 | |* 6 | TABLE ACCESS FULL| DEPT | 1 | 1 | 1 |00:00:00.01 | 3 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( IS NOT NULL) 3 - filter( IS NOT NULL) 5 - filter(:B1=20) 6 - filter("DEPTNO"=:B1) 27 rows selected. SQL>
这里第一个查询像以前一样，把子查询unnest用join的方式来连接两个表。 第二个查询把第一个的子查询外面又加了一层子查询，这样第三层子查询就会引用第一层子查询的表。这种情况下subquery无法unnest。
3 如何使用un_nest 和 unnest hint
这篇转载的文章讲的非常好并且提到了filter操作 。
http://www.cnblogs.com/kramer/archive/2013/04/12/3017013.html
4 扩展一下，如何使用push_subq
push_subq是让子查询先进行join。 先看一下这个例子
SQL> create table kramer1 as select * from dba_objects; Table created. SQL> create table kramer2 as select * from dba_objects; Table created. SQL> create table kramer3 as select * from dba_objects; Table created. SQL> create table kramer4 as select * from dba_objects; Table created. SQL> create index kramer3_ind1 on kramer3(object_id); Index created. SQL> exec dbms_stats.GATHER_TABLE_STATS('SYS','KRAMER1'); PL/SQL procedure successfully completed. SQL> exec dbms_stats.GATHER_TABLE_STATS('SYS','KRAMER2'); PL/SQL procedure successfully completed. SQL> exec dbms_stats.GATHER_TABLE_STATS('SYS','KRAMER3',CASCADE=>TRUE); PL/SQL procedure successfully completed. SQL> exec dbms_stats.GATHER_TABLE_STATS('SYS','KRAMER4'); PL/SQL procedure successfully completed.
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ KRAMER1.OBJECT_ID FROM KRAMER1,KRAMER2,KRAMER4 WHERE 2 (KRAMER1.OBJECT_ID+KRAMER2.OBJECT_ID)>50 3 AND 4 KRAMER1.OBJECT_TYPE=KRAMER4.OBJECT_TYPE 5 AND 6 8 IN (SELECT KRAMER3.OBJECT_ID FROM KRAMER3 WHERE KRAMER1.OBJECT_ID=KRAMER3.OBJECT_ID); ^CSELECT /*+ GATHER_PLAN_STATISTICS */ KRAMER1.OBJECT_ID FROM KRAMER1,KRAMER2,KRAMER4 WHERE * ERROR at line 1: ORA-01013: user requested cancel of current operation SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------- SQL_ID fbykd58kquryg, child number 0 ------------------------------------- SELECT /*+ GATHER_PLAN_STATISTICS */ KRAMER1.OBJECT_ID FROM KRAMER1,KRAMER2,KRAMER4 WHERE (KRAMER1.OBJECT_ID+KRAMER2.OBJECT_ID)>50 AND KRAMER1.OBJECT_TYPE=KRAMER4.OBJECT_TYPE AND 8 IN (SELECT KRAMER3.OBJECT_ID FROM KRAMER3 WHERE KRAMER1.OBJECT_ID=KRAMER3.OBJECT_ID) Plan hash value: 2572408404 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ----------------------------------------------------------------------------------------------- |* 1 | FILTER | | 1 | | 0 |00:00:00.01 | 0 | |* 2 | HASH JOIN | | 1 | 303G| 13M|00:00:00.07 | 839 | | 3 | TABLE ACCESS FULL | KRAMER4 | 1 | 56711 | 56711 |00:00:00.01 | 786 | | 4 | NESTED LOOPS | | 1 | 160M| 3785 |00:00:00.02 | 53 | | 5 | TABLE ACCESS FULL| KRAMER1 | 1 | 56708 | 1 |00:00:00.01 | 4 | |* 6 | TABLE ACCESS FULL| KRAMER2 | 1 | 2835 | 3785 |00:00:00.01 | 49 | |* 7 | FILTER | | 1 | | 0 |00:00:00.01 | 0 | |* 8 | INDEX RANGE SCAN | KRAMER3_IND1 | 0 | 1 | 0 |00:00:00.01 | 0 | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( IS NOT NULL) 2 - access("KRAMER1"."OBJECT_TYPE"="KRAMER4"."OBJECT_TYPE") 6 - filter("KRAMER1"."OBJECT_ID"+"KRAMER2"."OBJECT_ID">50) 7 - filter(8=:B1) 8 - access("KRAMER3"."OBJECT_ID"=8) 31 rows selected. SQL>
上面这个查询非常耗时，因为它是先把KRAMER1 和 KRAMER2 join，然后结果集和 KRAMER4 join。产生了一个非常大的结果集后再通过FILTER嵌套执行KRAMER3的subquery。这样会非常慢。所以我们通过ctrl+c先取消执行。 如果你仔细看的话就会发现，KRAMER1和KRAMER3如果先进行join那么只会返回一行数据，这样就会非常的快，所以我们通过hint push_subq 来让子查询先join。
SQL> explain plan for 2 SELECT /*+ GATHER_PLAN_STATISTICS push_subq(@tmp) */ KRAMER1.OBJECT_ID FROM KRAMER1,KRAMER2,KRAMER4 WHERE 3 (KRAMER1.OBJECT_ID+KRAMER2.OBJECT_ID)>50 4 AND 5 KRAMER1.OBJECT_TYPE=KRAMER4.OBJECT_TYPE 6 AND 7 8 IN (SELECT/*+QB_Name(tmp)*/ KRAMER3.OBJECT_ID FROM KRAMER3 WHERE KRAMER1.OBJECT_ID=KRAMER3.OBJECT_ID); Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 1216539656 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 15G| 396G| | 1034K (53)| 03:26:57 | |* 1 | HASH JOIN | | 15G| 396G| 1168K| 1034K (53)| 03:26:57 | | 2 | TABLE ACCESS FULL | KRAMER4 | 56711 | 498K| | 180 (4)| 00:00:03 | | 3 | NESTED LOOPS | | 8039K| 145M| | 501K (4)| 01:40:18 | |* 4 | TABLE ACCESS FULL | KRAMER1 | 2835 | 39690 | | 179 (4)| 00:00:03 | |* 5 | FILTER | | | | | | | |* 6 | INDEX RANGE SCAN| KRAMER3_IND1 | 1 | 5 | | 1 (0)| 00:00:01 | |* 7 | TABLE ACCESS FULL | KRAMER2 | 2835 | 14175 | | 177 (4)| 00:00:03 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("KRAMER1"."OBJECT_TYPE"="KRAMER4"."OBJECT_TYPE") 4 - filter( EXISTS (SELECT /*+ PUSH_SUBQ QB_NAME ("TMP") */ 0 FROM "KRAMER3" "KRAMER3" WHERE 8=:B1 AND "KRAMER3"."OBJECT_ID"=8)) 5 - filter(8=:B1) 6 - access("KRAMER3"."OBJECT_ID"=8) 7 - filter("KRAMER1"."OBJECT_ID"+"KRAMER2"."OBJECT_ID">50) 24 rows selected.
可以看到子查询先join，事实上这样执行也确实非常快，一秒内就可以返回结果。