1.限制子查询展开情况(rownum或者Row_Number() Over 函数): SQL>select ename, deptno from emp where deptno in (select deptno from dept where dname = 'SALES' ); 2 3 4 5 6 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 844388907 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 165 | 6 (17)| 00:00:01 | | 1 | MERGE JOIN | | 5 | 165 | 6 (17)| 00:00:01 | |* 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | |* 4 | SORT JOIN | | 14 | 280 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMP | 14 | 280 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("DNAME"='SALES') 4 - access("DEPTNO"="DEPTNO") filter("DEPTNO"="DEPTNO") SQL> select ename, deptno from emp where deptno in (select deptno from dept where dname = 'SALES' and rownum<2); 2 3 4 5 6 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1827978183 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 100 | 6 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | EMP | 15 | 300 | 3 (0)| 00:00:01 | |* 3 | FILTER | | | | | | |* 4 | COUNT STOPKEY | | | | | | |* 5 | TABLE ACCESS FULL| DEPT | 1 | 22 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS (<not feasible>) 3 - filter("DEPTNO"=:B1) 4 - filter(ROWNUM<2) 5 - filter("DNAME"='SALES') 可以看到ROWNUM会影响子查询展开 2. union unionall 会限制 子查询 展开 SQL> select ename, deptno from emp where deptno in (select deptno from dept where dname = 'SALES' union select deptno from dept where dname ='OPERATIONS' ); 2 3 4 5 6 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2859252660 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 200 | 11 (19)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | EMP | 15 | 300 | 3 (0)| 00:00:01 | | 3 | SORT UNIQUE | | 2 | 44 | 8 (63)| 00:00:01 | | 4 | UNION-ALL | | | | | | |* 5 | TABLE ACCESS FULL| DEPT | 1 | 22 | 3 (0)| 00:00:01 | |* 6 | TABLE ACCESS FULL| DEPT | 1 | 22 | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS ( (SELECT "DEPTNO" FROM "DEPT" "DEPT" WHERE "DEPTNO"=:B1 AND "DNAME"='SALES')UNION (SELECT "DEPTNO" FROM "DEPT" "DEPT" WHERE "DEPTNO"=:B2 AND "DNAME"='OPERATIONS'))) 5 - filter("DEPTNO"=:B1 AND "DNAME"='SALES') 6 - filter("DEPTNO"=:B1 AND "DNAME"='OPERATIONS') 3.子查询外面使用的是exists，CBO里面有个限制：当子查询里面有 start with....connect by ， CBO不会对子查询进行转换 如果子查询外面是In就可以展开 SQL> select * from departments a where exists (SELECT 1 FROM employees b START WITH employee_id = 101 CONNECT BY PRIOR employee_id = manager_id and a.department_id=b.department_id); 2 3 4 5 6 7 8 27 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3077864849 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 21 | 44 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 567 | 3 (0)| 00:00:01 | |* 3 | CONNECT BY WITH FILTERING (UNIQUE)| | | | | | | 4 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 11 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 | | 6 | NESTED LOOPS | | 1 | 24 | 2 (0)| 00:00:01 | | 7 | CONNECT BY PUMP | | | | | | |* 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 11 | 1 (0)| 00:00:01 | |* 9 | INDEX RANGE SCAN | EMP_MANAGER_IX | 6 | | 0 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS (SELECT 0 FROM "EMPLOYEES" "B" START WITH "EMPLOYEE_ID"=101 CONNECT BY "MANAGER_ID"=PRIOR "EMPLOYEE_ID" AND "B"."DEPARTMENT_ID"=:B1)) 3 - access("MANAGER_ID"=PRIOR "EMPLOYEE_ID") filter("B"."DEPARTMENT_ID"=:B1) 5 - access("EMPLOYEE_ID"=101) 8 - filter("B"."DEPARTMENT_ID"=:B1) 9 - access("connect$_by$_pump$_003"."PRIOR employee_id "="MANAGER_ID") 4.如果子查询外面是In就可以展开 SQL> select * from departments a where a.department_id in (SELECT b.department_id FROM employees b START WITH employee_id = 101 CONNECT BY PRIOR employee_id = manager_id ) 2 3 4 5 6 7 8 9 ; 6 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3045523330 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 7 | 238 | 6 (17)| 00:00:01 | | 1 | MERGE JOIN SEMI | | 7 | 238 | 6 (17)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 27 | 567 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 | |* 4 | SORT UNIQUE | | 7 | 91 | 4 (25)| 00:00:01 | | 5 | VIEW | VW_NSO_1 | 7 | 91 | 5 (40)| 00:00:01 | |* 6 | CONNECT BY WITH FILTERING | | | | | | | 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 11 | 1 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 | | 9 | NESTED LOOPS | | 6 | 144 | 2 (0)| 00:00:01 | | 10 | CONNECT BY PUMP | | | | | | | 11 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 66 | 1 (0)| 00:00:01 | |* 12 | INDEX RANGE SCAN | EMP_MANAGER_IX | 6 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."DEPARTMENT_ID"="DEPARTMENT_ID") filter("A"."DEPARTMENT_ID"="DEPARTMENT_ID") 6 - access("MANAGER_ID"=PRIOR "EMPLOYEE_ID") 8 - access("EMPLOYEE_ID"=101) 12 - access("connect$_by$_pump$_003"."PRIOR employee_id "="MANAGER_ID")