Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL>SELECT /*+ gather_plan_statistics */
t1.c1, t2.c2
FROM
t1, t2
WHERE
t1.c1 = t2.c1 and
t2.c2 IN (SELECT /*+ no_unnest no_push_subq */ c2 FROM t3)
;
999 dummy
1000 dummy
1000 개의 행이 선택되었습니다.
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID 5r5nxjgu2393k, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ t1.c1, t2.c2 FROM t1, t2
WHERE t1.c1 = t2.c1 and t2.c2 IN (SELECT /*+ no_unnest
no_push_subq */ c2 FROM t3)
Plan hash value: 1175524174
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13 (100)| 1000 |00:00:00.02 | 104 | | | |
|* 1 | FILTER | | 1 | | | 1000 |00:00:00.02 | 104 | | | |
|* 2 | HASH JOIN | | 1 | 1000 | 11 (10)| 1000 |00:00:00.02 | 99 | 870K| 870K| 1237K (0)|
| 3 | TABLE ACCESS FULL | T2 | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 7 | | | |
| 4 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.01 | 92 | | | |
|* 5 | TABLE ACCESS FULL | T3 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 5 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - access("T1"."C1"="T2"."C1")
5 - filter("C2"=:B1)
SQL>SELECT /*+ gather_plan_statistics */
t1.c1, t2.c2
FROM
t1, t2
WHERE
t1.c1 = t2.c1 and
t2.c2 IN (SELECT /*+ no_unnest push_subq */ c2 FROM t3)
;
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last'));
SQL_ID 7h89fcqh3r1w3, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ t1.c1, t2.c2 FROM t1, t2
WHERE t1.c1 = t2.c1 and t2.c2 IN (SELECT /*+ no_unnest push_subq
*/ c2 FROM t3)
Plan hash value: 1423053827
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 13 (100)| 1000 |00:00:00.02 | 104 | | | |
|* 1 | HASH JOIN | | 1 | 50 | 11 (10)| 1000 |00:00:00.02 | 104 | 870K| 870K| 1213K (0)|
|* 2 | TABLE ACCESS FULL | T2 | 1 | 50 | 3 (0)| 1000 |00:00:00.01 | 12 | | | |
|* 3 | TABLE ACCESS FULL | T3 | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 5 | | | |
| 4 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.01 | 92 | | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="T2"."C1")
2 - filter( IS NOT NULL)
3 - filter("C2"=:B1)
- 강좌 URL : http://www.gurubee.net/lecture/3922
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.