집합연산 | 개념 | 변환방법 |
---|---|---|
MINUS | 차집합 | ANTI JOIN |
INTERSECT | 교집합 | JOIN |
UNION | 합집합 | N/A (FULL OUTER JOIN) |
INTERSECT | |
---|---|
{code:sql | borderStyle=solid} – IT 프로그래머 중 급여 5000 이상 조회 SELECT /*+ QB_NAME (ABOVE) */ e1.employee_id, e1.phone_number, d1.department_name FROM employee e1, department d1 WHERE e1.department_id = d1.department_id AND e1.job_id = 'IT_PROG' INTERSECT SELECT /*+ QB_NAME (BELOW) */ e2.employee_id, e2.phone_number, d2.department_name FROM employee e2, department d2 WHERE e2.department_id = d2.department_id AND e2.salary > 5000; |
Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
0 | SELECT STATEMENT | 1 | 2 | 00:00:00.01 | 12 | ||||||||||||||||||||||||||
1 | INTERSECTION | 1 | 2 | 00:00:00.01 | 12 | << INTERSECTION 정상 수행 | 2 | SORT UNIQUE | 1 | 5 | 00:00:00.01 | 4 | 2048 | 2048 | 2048 (0) | << SORT UNIQUE | 3 | MERGE JOIN | 1 | 5 | 00:00:00.01 | 4 | |||||||||
4 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 | 7 | 00:00:00.01 | 2 | |||||||||||||||||||||||||
5 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 7 | 00:00:00.01 | 1 | |||||||||||||||||||||||||
| SORT JOIN | 7 | 5 | 00:00:00.01 | 2 | 2048 | 2048 | 2048 (0) | |||||||||||||||||||||||
7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 5 | 00:00:00.01 | 2 | |||||||||||||||||||||||||
| INDEX RANGE SCAN | EMP_JOB_IX | 1 | 5 | 00:00:00.01 | 1 | |||||||||||||||||||||||||
9 | SORT UNIQUE | 1 | 57 | 00:00:00.01 | 8 | 6144 | 6144 | 6144 (0) | << SORT UNIQUE | 10 | MERGE JOIN | 1 | 57 | 00:00:00.01 | 8 | ||||||||||||||||
11 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 | 27 | 00:00:00.01 | 2 | |||||||||||||||||||||||||
12 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 00:00:00.01 | 1 | |||||||||||||||||||||||||
| SORT JOIN | 27 | 57 | 00:00:00.01 | 6 | 4096 | 4096 | 4096 (0) | |||||||||||||||||||||||
| TABLE ACCESS FULL | EMPLOYEE | 1 | 58 | 00:00:00.01 | 6 |
Predicate Information (identified by operation id):
6 - access("E1"."DEPARTMENT_ID"="D1"."DEPARTMENT_ID")
filter("E1"."DEPARTMENT_ID"="D1"."DEPARTMENT_ID")
8 - access("E1"."JOB_ID"='IT_PROG')
13 - access("E2"."DEPARTMENT_ID"="D2"."DEPARTMENT_ID")
filter("E2"."DEPARTMENT_ID"="D2"."DEPARTMENT_ID")
14 - filter("E2"."SALARY">5000)
|
||INTERSECT - SJC 적용 후 실행 계획||
|{code:sql|borderStyle=solid}
ALTER SESSION SET "_convert_set_to_join" = TRUE;
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 |00:00:00.01 | 10 | | | |
| 1 | HASH UNIQUE | | 1 | 2 |00:00:00.01 | 10 | 1001K| 1001K| 619K (0)| << HASH UNIQUE (INTERSECT 사라짐)
| 2 | NESTED LOOPS | | 1 | 2 |00:00:00.01 | 10 | | | |
| 3 | NESTED LOOPS | | 1 | 2 |00:00:00.01 | 8 | | | |
| 4 | NESTED LOOPS | | 1 | 2 |00:00:00.01 | 6 | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 2 |00:00:00.01 | 2 | | | |
|* 6 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 5 |00:00:00.01 | 1 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 2 | 2 |00:00:00.01 | 4 | | | |
|* 8 | INDEX UNIQUE SCAN | DEPT_ID_PK | 2 | 2 |00:00:00.01 | 2 | | | |
|* 9 | INDEX UNIQUE SCAN | DEPT_ID_PK | 2 | 2 |00:00:00.01 | 2 | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 2 | 2 |00:00:00.01 | 2 | | | |
--------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_convert_set_to_join' 'true')
ALL_ROWS
OUTLINE_LEAF(@"SEL$0D4CE47B")
ELIMINATE_JOIN(@"SEL$B1AEC6DA" "E1"@"ABOVE") <<<<< 책과다름(JE 발생)
OUTLINE(@"SEL$B1AEC6DA")
MERGE(@"ABOVE")
MERGE(@"BELOW")
OUTLINE(@"SET$25A08358")
SET_TO_JOIN(@"SET$1") <<<<<
OUTLINE(@"ABOVE")
OUTLINE(@"BELOW")
OUTLINE(@"SET$1")
INDEX_RS_ASC(@"SEL$0D4CE47B" "E2"@"BELOW" ("EMPLOYEE"."JOB_ID"))
INDEX_RS_ASC(@"SEL$0D4CE47B" "D2"@"BELOW" ("DEPARTMENT"."DEPARTMENT_ID"))
INDEX(@"SEL$0D4CE47B" "D1"@"ABOVE" ("DEPARTMENT"."DEPARTMENT_ID"))
LEADING(@"SEL$0D4CE47B" "E2"@"BELOW" "D2"@"BELOW" "D1"@"ABOVE")
USE_NL(@"SEL$0D4CE47B" "D2"@"BELOW")
USE_NL(@"SEL$0D4CE47B" "D1"@"ABOVE")
NLJ_BATCHING(@"SEL$0D4CE47B" "D1"@"ABOVE")
USE_HASH_AGGREGATION(@"SEL$0D4CE47B")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(("E2"."SALARY">5000 AND SYS_OP_MAP_NONNULL("PHONE_NUMBER") IS NOT NULL)) <<<<< 책과다름(JE 발생)
6 - access("JOB_ID"='IT_PROG')
8 - access("E2"."DEPARTMENT_ID"="D2"."DEPARTMENT_ID")
9 - access("DEPARTMENT_ID"="D1"."DEPARTMENT_ID")
10 - filter("D1"."DEPARTMENT_NAME"="D2"."DEPARTMENT_NAME")
|
INTERSECT - QT가 재작성 한 SQL | |
---|---|
{code:sql | borderStyle=solid} SELECT DISTINCT a.employee_id, a.phone_number, a.department_name FROM (SELECT /*+ QB_NAME (ABOVE) */ e1.employee_id, e1.phone_number, d1.department_name FROM employee e1, department d1 WHERE e1.department_id = d1.department_id AND e1.job_id = 'IT_PROG') a, (SELECT /*+ QB_NAME (BELOW) */ e2.employee_id, e2.phone_number, d2.department_name FROM employee e2, department d2 WHERE e2.department_id = d2.department_id AND e2.salary > 5000) b WHERE a.employee_id = b.employee_id AND sys_op_map_nonnull (a.phone_number) = sys_op_map_nonnull (b.phone_number) AND a.department_name = b.department_name; |
-- 실행 계획이 책보다 더 간단해짐 (JE 발생)
Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
0 | SELECT STATEMENT | 1 | 2 | 00:00:00.01 | 6 | ||||
1 | HASH UNIQUE | 1 | 2 | 00:00:00.01 | 6 | 1001K | 1001K | 619K (0) | |
2 | NESTED LOOPS | 1 | 2 | 00:00:00.01 | 6 | ||||
3 | NESTED LOOPS | 1 | 2 | 00:00:00.01 | 4 | ||||
| TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 2 | 00:00:00.01 | 2 | |||
| INDEX RANGE SCAN | EMP_JOB_IX | 1 | 5 | 00:00:00.01 | 1 | |||
| INDEX UNIQUE SCAN | DEPT_ID_PK | 2 | 2 | 00:00:00.01 | 2 | |||
7 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 2 | 2 | 00:00:00.01 | 2 |
Query Block Name / Object Alias (identified by operation id):
1 - SEL$5ED275C6
4 - SEL$5ED275C6 / E1@ABOVE
5 - SEL$5ED275C6 / E1@ABOVE
6 - SEL$5ED275C6 / D2@BELOW
7 - SEL$5ED275C6 / D2@BELOW
Outline Data
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
OPT_PARAM('_convert_set_to_join' 'true')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5ED275C6")
ELIMINATE_JOIN(@"SEL$CE5AAD88" "D1"@"ABOVE")
OUTLINE(@"SEL$CE5AAD88")
ELIMINATE_JOIN(@"SEL$17709FCC" "E2"@"BELOW")
OUTLINE(@"SEL$17709FCC")
MERGE(@"ABOVE")
MERGE(@"BELOW")
OUTLINE(@"SEL$1")
OUTLINE(@"ABOVE")
OUTLINE(@"BELOW")
INDEX_RS_ASC(@"SEL$5ED275C6" "E1"@"ABOVE" ("EMPLOYEE"."JOB_ID"))
INDEX(@"SEL$5ED275C6" "D2"@"BELOW" ("DEPARTMENT"."DEPARTMENT_ID"))
LEADING(@"SEL$5ED275C6" "E1"@"ABOVE" "D2"@"BELOW")
USE_NL(@"SEL$5ED275C6" "D2"@"BELOW")
NLJ_BATCHING(@"SEL$5ED275C6" "D2"@"BELOW")
USE_HASH_AGGREGATION(@"SEL$5ED275C6")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
4 - filter(("SALARY">5000 AND SYS_OP_MAP_NONNULL("E1"."PHONE_NUMBER") IS NOT NULL))
5 - access("E1"."JOB_ID"='IT_PROG')
6 - access("DEPARTMENT_ID"="D2"."DEPARTMENT_ID")
|
{info:title=sys_op_map_nonnull}
SJC 발생 시 NULL 허용 컬럼에 대해서 JOIN 실패를 방지 하기 위해 발생 (NULL = NULL 을 처리)
{info}
||INTERSECT - 10053 Trace||
|{code:sql}
*************************
Set-Join Conversion (SJC)
*************************
SJC: Checking validity of SJC on query block SET$1 (#0)
SJC: Passed validity checks.
SJC: SJC: Applying SJC on query block SET$1 (#0) << 쿼리블럭 SET$1 에 대해서 SJC가 수행 됨
Registered qb: SEL$25A08358 0xec12620 (SET QUERY BLOCK SET$1; SET$1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$25A08358 nbfros=2 flg=0
fro(0): flg=5 objn=0 hint_alias="VW_STJ_1"@"SEL$25A08358"
fro(1): flg=5 objn=0 hint_alias="VW_STJ_2"@"SEL$25A08358"
... 중략 ...
-- 실행계획에 VIEW OPERATION 이 없었음 (VIEW MERGING 수행 됨)
SJC: Considering set-join conversion in query block ABOVE (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
SJC: Considering set-join conversion in query block BELOW (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
SJC: performed
CVM: Considering view merge in query block SET$25A08358 (#0)
CVM: Checking validity of merging in query block ABOVE (#0)
CVM: Considering view merge in query block ABOVE (#0)
CVM: Merging SPJ view ABOVE (#0) into SET$25A08358 (#0)
CVM: Checking validity of merging in query block BELOW (#0)
CVM: Considering view merge in query block BELOW (#0)
CVM: Merging SPJ view BELOW (#0) into SET$25A08358 (#0)
Registered qb: SEL$B1AEC6DA 0xe667d2c (VIEW MERGE SET$25A08358; ABOVE BELOW) <<<<< CVM 으로 새로 생긴 쿼리 블록 (인라인뷰 ABOVE, BELOW 가 MERGE 됨)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$B1AEC6DA nbfros=4 flg=0
fro(0): flg=0 objn=74260 hint_alias="D1"@"ABOVE"
fro(1): flg=0 objn=74263 hint_alias="E1"@"ABOVE"
fro(2): flg=0 objn=74260 hint_alias="D2"@"BELOW"
fro(3): flg=0 objn=74263 hint_alias="E2"@"BELOW"
-- CVM 이 SJC 자리에서 수행 됨 (ABOVE, BELOW 뷰에 대해서 각각 CVM 수행 됨)
|
MINUS | |
---|---|
{code:sql | borderStyle=solid} ALTER SESSION SET "_convert_set_to_join" = FALSE; (기본값) |
SELECT /*+ QB_NAME (ABOVE) */
e1.employee_id, e1.phone_number, d1.department_name
FROM employee e1, department d1
WHERE e1.department_id = d1.department_id
AND e1.job_id = 'IT_PROG'
MINUS
SELECT /*+ QB_NAME (BELOW) */
e2.employee_id, e2.phone_number, d2.department_name
FROM employee e2, department d2
WHERE e2.department_id = d2.department_id
AND e2.salary > 5000;
Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
0 | SELECT STATEMENT | 1 | 3 | 00:00:00.01 | 12 | ||||||||||||||||||||||||||
1 | MINUS | 1 | 3 | 00:00:00.01 | 12 | <<<<< MINUS 정상 수행 | 2 | SORT UNIQUE | 1 | 5 | 00:00:00.01 | 4 | 2048 | 2048 | 2048 (0) | <<<<< SORT UNIQUE | 3 | MERGE JOIN | 1 | 5 | 00:00:00.01 | 4 | |||||||||
4 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 | 7 | 00:00:00.01 | 2 | |||||||||||||||||||||||||
5 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 7 | 00:00:00.01 | 1 | |||||||||||||||||||||||||
| SORT JOIN | 7 | 5 | 00:00:00.01 | 2 | 2048 | 2048 | 2048 (0) | |||||||||||||||||||||||
7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 5 | 00:00:00.01 | 2 | |||||||||||||||||||||||||
| INDEX RANGE SCAN | EMP_JOB_IX | 1 | 5 | 00:00:00.01 | 1 | |||||||||||||||||||||||||
9 | SORT UNIQUE | 1 | 57 | 00:00:00.01 | 8 | 6144 | 6144 | 6144 (0) | <<<<< SORT UNIQUE | 10 | MERGE JOIN | 1 | 57 | 00:00:00.01 | 8 | ||||||||||||||||
11 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 | 27 | 00:00:00.01 | 2 | |||||||||||||||||||||||||
12 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 00:00:00.01 | 1 | |||||||||||||||||||||||||
| SORT JOIN | 27 | 57 | 00:00:00.01 | 6 | 4096 | 4096 | 4096 (0) | |||||||||||||||||||||||
| TABLE ACCESS FULL | EMPLOYEE | 1 | 58 | 00:00:00.01 | 6 |
Query Block Name / Object Alias (identified by operation id):
1 - SET$1
3 - ABOVE
4 - ABOVE / D1@ABOVE
5 - ABOVE / D1@ABOVE
7 - ABOVE / E1@ABOVE
8 - ABOVE / E1@ABOVE
10 - BELOW
11 - BELOW / D2@BELOW
12 - BELOW / D2@BELOW
14 - BELOW / E2@BELOW
Outline Data
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"ABOVE")
OUTLINE_LEAF(@"BELOW")
OUTLINE_LEAF(@"SET$1")
OUTLINE(@"ABOVE")
OUTLINE(@"BELOW")
INDEX(@"BELOW" "D2"@"BELOW" ("DEPARTMENT"."DEPARTMENT_ID"))
FULL(@"BELOW" "E2"@"BELOW")
LEADING(@"BELOW" "D2"@"BELOW" "E2"@"BELOW")
USE_MERGE(@"BELOW" "E2"@"BELOW")
INDEX(@"ABOVE" "D1"@"ABOVE" ("DEPARTMENT"."DEPARTMENT_ID"))
INDEX_RS_ASC(@"ABOVE" "E1"@"ABOVE" ("EMPLOYEE"."JOB_ID"))
LEADING(@"ABOVE" "D1"@"ABOVE" "E1"@"ABOVE")
USE_MERGE(@"ABOVE" "E1"@"ABOVE")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
6 - access("E1"."DEPARTMENT_ID"="D1"."DEPARTMENT_ID")
filter("E1"."DEPARTMENT_ID"="D1"."DEPARTMENT_ID")
8 - access("E1"."JOB_ID"='IT_PROG')
13 - access("E2"."DEPARTMENT_ID"="D2"."DEPARTMENT_ID")
filter("E2"."DEPARTMENT_ID"="D2"."DEPARTMENT_ID")
14 - filter("E2"."SALARY">5000)
|
||MINUS - SJC 적용 후 실행 계획||
|{code:sql|borderStyle=solid}
SELECT /*+ QB_NAME (ABOVE) SET_TO_JOIN(@SET$1) */
e1.employee_id, e1.phone_number, d1.department_name
FROM employee e1, department d1
WHERE e1.department_id = d1.department_id
AND e1.job_id = 'IT_PROG'
MINUS
SELECT /*+ QB_NAME (BELOW) */
e2.employee_id, e2.phone_number, d2.department_name
FROM employee e2, department d2
WHERE e2.department_id = d2.department_id
AND e2.salary > 5000;
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 |00:00:00.01 | 12 | | | |
| 1 | HASH UNIQUE | | 1 | 3 |00:00:00.01 | 12 | 1001K| 1001K| 741K (0)|
|* 2 | HASH JOIN ANTI | | 1 | 3 |00:00:00.01 | 12 | 894K| 894K| 775K (0)| <<<<< HASH JOIN ANTI
| 3 | VIEW | VW_STJ_1 | 1 | 5 |00:00:00.01 | 4 | | | | <<<<< VIEW MERGING 안됨
| 4 | MERGE JOIN | | 1 | 5 |00:00:00.01 | 4 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 | 7 |00:00:00.01 | 2 | | | |
| 6 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 7 |00:00:00.01 | 1 | | | |
|* 7 | SORT JOIN | | 7 | 5 |00:00:00.01 | 2 | 2048 | 2048 | 2048 (0)|
| 8 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 1 | 5 |00:00:00.01 | 2 | | | |
|* 9 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 5 |00:00:00.01 | 1 | | | |
| 10 | VIEW | VW_STJ_2 | 1 | 57 |00:00:00.01 | 8 | | | |
| 11 | MERGE JOIN | | 1 | 57 |00:00:00.01 | 8 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 | 27 |00:00:00.01 | 2 | | | |
| 13 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 |00:00:00.01 | 1 | | | |
|* 14 | SORT JOIN | | 27 | 57 |00:00:00.01 | 6 | 4096 | 4096 | 4096 (0)|
|* 15 | TABLE ACCESS FULL | EMPLOYEE | 1 | 58 |00:00:00.01 | 6 | | | |
---------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$25A08358
3 - ABOVE / VW_STJ_1@SEL$25A08358
4 - ABOVE
5 - ABOVE / D1@ABOVE
6 - ABOVE / D1@ABOVE
8 - ABOVE / E1@ABOVE
9 - ABOVE / E1@ABOVE
10 - BELOW / VW_STJ_2@SEL$25A08358
11 - BELOW
12 - BELOW / D2@BELOW
13 - BELOW / D2@BELOW
15 - BELOW / E2@BELOW
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"BELOW")
OUTLINE_LEAF(@"ABOVE")
OUTLINE_LEAF(@"SEL$25A08358")
SET_TO_JOIN(@"SET$1")
OUTLINE(@"BELOW")
OUTLINE(@"ABOVE")
OUTLINE(@"SET$1")
NO_ACCESS(@"SEL$25A08358" "VW_STJ_1"@"SEL$25A08358")
NO_ACCESS(@"SEL$25A08358" "VW_STJ_2"@"SEL$25A08358")
LEADING(@"SEL$25A08358" "VW_STJ_1"@"SEL$25A08358" "VW_STJ_2"@"SEL$25A08358")
USE_HASH(@"SEL$25A08358" "VW_STJ_2"@"SEL$25A08358")
USE_HASH_AGGREGATION(@"SEL$25A08358")
INDEX(@"BELOW" "D2"@"BELOW" ("DEPARTMENT"."DEPARTMENT_ID"))
FULL(@"BELOW" "E2"@"BELOW")
LEADING(@"BELOW" "D2"@"BELOW" "E2"@"BELOW")
USE_MERGE(@"BELOW" "E2"@"BELOW")
INDEX(@"ABOVE" "D1"@"ABOVE" ("DEPARTMENT"."DEPARTMENT_ID"))
INDEX_RS_ASC(@"ABOVE" "E1"@"ABOVE" ("EMPLOYEE"."JOB_ID"))
LEADING(@"ABOVE" "D1"@"ABOVE" "E1"@"ABOVE")
USE_MERGE(@"ABOVE" "E1"@"ABOVE")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"="EMPLOYEE_ID" AND SYS_OP_MAP_NONNULL("PHONE_NUMBER")=SYS_OP_MAP_NONNULL("PHONE_NUMBER")
AND "DEPARTMENT_NAME"="DEPARTMENT_NAME")
7 - access("E1"."DEPARTMENT_ID"="D1"."DEPARTMENT_ID")
filter("E1"."DEPARTMENT_ID"="D1"."DEPARTMENT_ID")
9 - access("E1"."JOB_ID"='IT_PROG')
14 - access("E2"."DEPARTMENT_ID"="D2"."DEPARTMENT_ID")
filter("E2"."DEPARTMENT_ID"="D2"."DEPARTMENT_ID")
15 - filter("E2"."SALARY">5000)
|
MINUS - QT가 재작성 한 SQL | |
---|---|
{code:sql | borderStyle=solid} SELECT DISTINCT a.employee_id, a.phone_number, a.department_name FROM (SELECT /*+ QB_NAME (ABOVE) NO_MERGE */ e1.employee_id, e1.phone_number, d1.department_name FROM employee e1, department d1 WHERE e1.department_id = d1.department_id AND e1.job_id = 'IT_PROG') a WHERE NOT EXISTS (SELECT /*+ QB_NAME (BELOW) UNNEST */ 1 FROM employee e2, department d2 WHERE e2.department_id = d2.department_id AND e2.salary > 5000 AND e2.employee_id = a.employee_id AND sys_op_map_nonnull (e2.phone_number) = sys_op_map_nonnull (a.phone_number) AND d2.department_name = a.department_name ); |
– SJC 발생 실행 계획과 같음
Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
0 | SELECT STATEMENT | 1 | 3 | 00:00:00.01 | 12 | |||||||||||||||
1 | HASH UNIQUE | 1 | 3 | 00:00:00.01 | 12 | 1001K | 1001K | 741K (0) | << SORT UNIQUE 대신 HASH UNIQUE |
| HASH JOIN ANTI | 1 | 3 | 00:00:00.01 | 12 | 894K | 894K | 777K (0) | ||
3 | VIEW | 1 | 5 | 00:00:00.01 | 4 | |||||||||||||||
4 | MERGE JOIN | 1 | 5 | 00:00:00.01 | 4 | |||||||||||||||
5 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 | 7 | 00:00:00.01 | 2 | ||||||||||||||
6 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 7 | 00:00:00.01 | 1 | ||||||||||||||
| SORT JOIN | 7 | 5 | 00:00:00.01 | 2 | 2048 | 2048 | 2048 (0) | ||||||||||||
8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 5 | 00:00:00.01 | 2 | ||||||||||||||
| INDEX RANGE SCAN | EMP_JOB_IX | 1 | 5 | 00:00:00.01 | 1 | ||||||||||||||
10 | VIEW | VW_SQ_1 | 1 | 57 | 00:00:00.01 | 8 | ||||||||||||||
11 | MERGE JOIN | 1 | 57 | 00:00:00.01 | 8 | |||||||||||||||
12 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 | 27 | 00:00:00.01 | 2 | ||||||||||||||
13 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 00:00:00.01 | 1 | ||||||||||||||
| SORT JOIN | 27 | 57 | 00:00:00.01 | 6 | 4096 | 4096 | 4096 (0) | ||||||||||||
| TABLE ACCESS FULL | EMPLOYEE | 1 | 58 | 00:00:00.01 | 6 |
Query Block Name / Object Alias (identified by operation id):
1 - SEL$50A67845
3 - ABOVE / A@SEL$1
4 - ABOVE
5 - ABOVE / D1@ABOVE
6 - ABOVE / D1@ABOVE
8 - ABOVE / E1@ABOVE
9 - ABOVE / E1@ABOVE
10 - SEL$1A621EA5 / VW_SQ_1@SEL$8969955B
11 - SEL$1A621EA5
12 - SEL$1A621EA5 / D2@BELOW
13 - SEL$1A621EA5 / D2@BELOW
15 - SEL$1A621EA5 / E2@BELOW
Outline Data
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1A621EA5")
OUTLINE_LEAF(@"ABOVE")
OUTLINE_LEAF(@"SEL$50A67845")
UNNEST(@"BELOW")
OUTLINE(@"BELOW")
OUTLINE(@"ABOVE")
OUTLINE(@"SEL$8969955B")
OUTLINE(@"SEL$1")
NO_ACCESS(@"SEL$50A67845" "A"@"SEL$1")
NO_ACCESS(@"SEL$50A67845" "VW_SQ_1"@"SEL$8969955B")
LEADING(@"SEL$50A67845" "A"@"SEL$1" "VW_SQ_1"@"SEL$8969955B")
USE_HASH(@"SEL$50A67845" "VW_SQ_1"@"SEL$8969955B")
USE_HASH_AGGREGATION(@"SEL$50A67845")
INDEX(@"SEL$1A621EA5" "D2"@"BELOW" ("DEPARTMENT"."DEPARTMENT_ID"))
FULL(@"SEL$1A621EA5" "E2"@"BELOW")
LEADING(@"SEL$1A621EA5" "D2"@"BELOW" "E2"@"BELOW")
USE_MERGE(@"SEL$1A621EA5" "E2"@"BELOW")
INDEX(@"ABOVE" "D1"@"ABOVE" ("DEPARTMENT"."DEPARTMENT_ID"))
INDEX_RS_ASC(@"ABOVE" "E1"@"ABOVE" ("EMPLOYEE"."JOB_ID"))
LEADING(@"ABOVE" "D1"@"ABOVE" "E1"@"ABOVE")
USE_MERGE(@"ABOVE" "E1"@"ABOVE")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
2 - access("ITEM_1"="A"."EMPLOYEE_ID" AND "ITEM_2"=SYS_OP_MAP_NONNULL("A"."PHONE_NUMBER") AND
"ITEM_3"="A"."DEPARTMENT_NAME")
7 - access("E1"."DEPARTMENT_ID"="D1"."DEPARTMENT_ID")
filter("E1"."DEPARTMENT_ID"="D1"."DEPARTMENT_ID")
9 - access("E1"."JOB_ID"='IT_PROG')
14 - access("E2"."DEPARTMENT_ID"="D2"."DEPARTMENT_ID")
filter("E2"."DEPARTMENT_ID"="D2"."DEPARTMENT_ID")
15 - filter("E2"."SALARY">5000)
|
||MINUS - 10053 Trace||
|{code:sql|borderStyle=solid}
*************************
Set-Join Conversion (SJC)
*************************
SJC: Checking validity of SJC on query block SET$1 (#0)
SJC: Passed validity checks.
SJC: SJC: Applying SJC on query block SET$1 (#0) << SJC 적용 후 새로운 쿼리 블럭 생성
Registered qb: SEL$25A08358 0xec12620 (SET QUERY BLOCK SET$1; SET$1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$25A08358 nbfros=2 flg=0
fro(0): flg=5 objn=0 hint_alias="VW_STJ_1"@"SEL$25A08358" << 새로운 쿼리 블럭 내 두개의 뷰를 이용해서 ANTI JOIN 으로 변경
fro(1): flg=5 objn=0 hint_alias="VW_STJ_2"@"SEL$25A08358" << STJ : Set-To-Join
... 중략 ...
SJC: Considering set-join conversion in query block ABOVE (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
SJC: Considering set-join conversion in query block BELOW (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
SJC: performed
CVM: Considering view merge in query block SEL$25A08358 (#0)
CVM: Checking validity of merging in query block ABOVE (#0)
CVM: Considering view merge in query block ABOVE (#0)
SVM: SVM bypassed: Query NO MERGE hint. <<< SVM 실패 (NO_MERGE)
CVM: Checking validity of merging in query block BELOW (#0)
CVM: Considering view merge in query block BELOW (#0)
SVM: SVM bypassed: Anti-joined view. <<< SVM 실패 (ANTI JOIN VIEW)
|