woong:WOONG >
t2 explain plan for
2 select
3 c1, c2
4 from t1
5 where c1 in (select
6 c1
7 from t2
8 where c1 between :b1 and :b2);
woong:WOONG >
t2 @plan_all
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 14161060
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 0 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS SEMI | | 1 | 39 | 0 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 26 | 0 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_N1 | 1 | | 0 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T2_N1 | 1 | 13 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$0C6FB14C
3 - SEL$0C6FB14C / T1@SEL$1
4 - SEL$0C6FB14C / T1@SEL$1
5 - SEL$0C6FB14C / T2@SUB
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2))
4 - access("C1">=TO_NUMBER(:B1) AND "C1"<=TO_NUMBER(:B2))
5 - access("C1"="C1")
filter("C1">=TO_NUMBER(:B1) AND "C1"<=TO_NUMBER(:B2))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "C1"[NUMBER,22], "C2"[NUMBER,22]
2 - (#keys=0) "C1"[NUMBER,22], "C2"[NUMBER,22]
3 - "C1"[NUMBER,22], "C2"[NUMBER,22]
4 - "T1".ROWID[ROWID,10], "C1"[NUMBER,22]
36 개의 행이 선택되었습니다.
경 과: 00:00:00.04
woong:WOONG >
t2
woong:WOONG >
t2 explain plan for
2 select
3 c1, c2
4 from t1
5 where c1 in (select /*+ no_unnest qb_name(sub) */
6 c1
7 from t2
8 where c1 between :b1 and :b2);
해석되었습니다.
경 과: 00:00:00.03
woong:WOONG >
t2
woong:WOONG >
t2 @plan_all
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2638085224
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T1 | 1 | 26 | 2 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | INDEX RANGE SCAN| T2_N1 | 1 | 13 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
3 - SUB
4 - SUB / T2@SUB
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ QB_NAME ("SUB") NO_UNNEST */ 0 FROM
"T2" "T2" WHERE TO_NUMBER(:B1)<=TO_NUMBER(:B2) AND :B1<=TO_NUMBER(:B2)
AND :B2>=TO_NUMBER(:B1) AND "C1"=:B3 AND "C1">=TO_NUMBER(:B1) AND
"C1"<=TO_NUMBER(:B2)))
3 - filter(TO_NUMBER(:B1)<=TO_NUMBER(:B2) AND :B1<=TO_NUMBER(:B2)
AND :B2>=TO_NUMBER(:B1))
4 - access("C1"=:B1)
filter("C1">=TO_NUMBER(:B1) AND "C1"<=TO_NUMBER(:B2))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "C1"[NUMBER,22], "C2"[NUMBER,22]
2 - "C1"[NUMBER,22], "C2"[NUMBER,22]
37 개의 행이 선택되었습니다.
경 과: 00:00:00.04