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")