1. HOME
  2. [종료]구루비 DB 스터디
  3. 2013년 상반기 - 오라클 트러블슈팅 스터디
  4. 3.6 SJC (Set To Join Conversion) 집합연산을 조인으로 바꾸어라

3.6 SJC (Set To Join Conversion) 집합연산을 조인으로 바꾸어라 (by openlsw) [2013.06.29]

3.6 SJC (Set To Join Conversion) : 집합연산을 조인으로 바꾸어라

집합연산개념변환방법
MINUS차집합ANTI JOIN
INTERSECT교집합JOIN
UNION합집합N/A (FULL OUTER JOIN)
INTERSECT
{code:sqlborderStyle=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;

--

IdOperationNameStartsA-RowsA-TimeBuffersOMem1MemUsed-Mem

--

0SELECT STATEMENT1200:00:00.0112
1INTERSECTION1200:00:00.0112<< INTERSECTION 정상 수행2SORT UNIQUE1500:00:00.014204820482048 (0)<< SORT UNIQUE3MERGE JOIN1500:00:00.014
4TABLE ACCESS BY INDEX ROWIDDEPARTMENT1700:00:00.012
5INDEX FULL SCANDEPT_ID_PK1700:00:00.011
  • 6
SORT JOIN7500:00:00.012204820482048 (0)
7TABLE ACCESS BY INDEX ROWIDEMPLOYEE1500:00:00.012
  • 8
INDEX RANGE SCANEMP_JOB_IX1500:00:00.011
9SORT UNIQUE15700:00:00.018614461446144 (0)<< SORT UNIQUE10MERGE JOIN15700:00:00.018
11TABLE ACCESS BY INDEX ROWIDDEPARTMENT12700:00:00.012
12INDEX FULL SCANDEPT_ID_PK12700:00:00.011
  • 13
SORT JOIN275700:00:00.016409640964096 (0)
  • 14
TABLE ACCESS FULLEMPLOYEE15800:00:00.016

--

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:sqlborderStyle=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 발생)

-

IdOperationNameStartsA-RowsA-TimeBuffersOMem1MemUsed-Mem

-

0SELECT STATEMENT1200:00:00.016
1HASH UNIQUE1200:00:00.0161001K1001K619K (0)
2NESTED LOOPS1200:00:00.016
3NESTED LOOPS1200:00:00.014
  • 4
TABLE ACCESS BY INDEX ROWIDEMPLOYEE1200:00:00.012
  • 5
INDEX RANGE SCANEMP_JOB_IX1500:00:00.011
  • 6
INDEX UNIQUE SCANDEPT_ID_PK2200:00:00.012
7TABLE ACCESS BY INDEX ROWIDDEPARTMENT2200:00:00.012

-

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 수행 됨)

|

  • SJC 는 INTERSECT 연산시 수반 되는 SORT UNIQUE 의 성능 문제에 대한 대안 (HASH UNIQUE) 으로 사용할 수 있음
MINUS
{code:sqlborderStyle=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;

--

IdOperationNameStartsA-RowsA-TimeBuffersOMem1MemUsed-Mem

--

0SELECT STATEMENT1300:00:00.0112
1MINUS1300:00:00.0112<<<<< MINUS 정상 수행2SORT UNIQUE1500:00:00.014204820482048 (0)<<<<< SORT UNIQUE3MERGE JOIN1500:00:00.014
4TABLE ACCESS BY INDEX ROWIDDEPARTMENT1700:00:00.012
5INDEX FULL SCANDEPT_ID_PK1700:00:00.011
  • 6
SORT JOIN7500:00:00.012204820482048 (0)
7TABLE ACCESS BY INDEX ROWIDEMPLOYEE1500:00:00.012
  • 8
INDEX RANGE SCANEMP_JOB_IX1500:00:00.011
9SORT UNIQUE15700:00:00.018614461446144 (0)<<<<< SORT UNIQUE10MERGE JOIN15700:00:00.018
11TABLE ACCESS BY INDEX ROWIDDEPARTMENT12700:00:00.012
12INDEX FULL SCANDEPT_ID_PK12700:00:00.011
  • 13
SORT JOIN275700:00:00.016409640964096 (0)
  • 14
TABLE ACCESS FULLEMPLOYEE15800:00:00.016

--

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:sqlborderStyle=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 발생 실행 계획과 같음

---

IdOperationNameStartsA-RowsA-TimeBuffersOMem1MemUsed-Mem

---

0SELECT STATEMENT1300:00:00.0112
1HASH UNIQUE1300:00:00.01121001K1001K741K (0)<< SORT UNIQUE 대신 HASH UNIQUE
  • 2
HASH JOIN ANTI1300:00:00.0112894K894K777K (0)
3VIEW1500:00:00.014
4MERGE JOIN1500:00:00.014
5TABLE ACCESS BY INDEX ROWIDDEPARTMENT1700:00:00.012
6INDEX FULL SCANDEPT_ID_PK1700:00:00.011
  • 7
SORT JOIN7500:00:00.012204820482048 (0)
8TABLE ACCESS BY INDEX ROWIDEMPLOYEE1500:00:00.012
  • 9
INDEX RANGE SCANEMP_JOB_IX1500:00:00.011
10VIEWVW_SQ_115700:00:00.018
11MERGE JOIN15700:00:00.018
12TABLE ACCESS BY INDEX ROWIDDEPARTMENT12700:00:00.012
13INDEX FULL SCANDEPT_ID_PK12700:00:00.011
  • 14
SORT JOIN275700:00:00.016409640964096 (0)
  • 15
TABLE ACCESS FULLEMPLOYEE15800:00:00.016

---

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)

|

  • SJC 는 SORT UNIQUE 부하를 줄일 수 있는 대안 (HASH UNIQUE) 이지만, HASH JOIN 의 추가적인 부하가 있으므로 적절히 활용 필요 (TRADE OFF)
  • 오라클의 Cost-Based Query Transformation in Oracle(Rafi Ahmed 외 6명) 문서에 의하면 SJC 는 CBQT 로 분류 되나, 현재 버전(11.2.0.1 ~ 11.2.0.3)에서는 Cost 가 아니라 파라미터(_convert_set_to_join)에 영향을 받는 HQT 임 (_optimizer_cost_based_transformation 파라미터에 영향 받지 않고 Search Type, Iteration 역시 없음)
  1. HOME
  2. [종료]구루비 DB 스터디
  3. 2013년 상반기 - 오라클 트러블슈팅 스터디
  4. 3.6 SJC (Set To Join Conversion) 집합연산을 조인으로 바꾸어라