2.27 SQC ( Subquery Coalescing );

불필요하게 분리되어 있는 서브쿼리를 하나로 통합하라.

 

SQC 기능은 오라클 11g  에 추가 되었다.

SQC

기능 : 서브쿼리가 여러 개인 경우 성능 향상을 위해서 서브쿼리를 하나로 통합하는 기능.

대상 : 통합이 가능한 서브쿼리

 

예 )

 

SELECT /*+ QB_NAME(MAIN)*/  e.employee_id,e.first_name,e.last_name
FROM employee e
WHERE e.salary > 2000
 AND ( EXISTS ( SELECT /*+QB_NAME(SUB1)*/ 1
        FROM department d1
        WHERE d1.department_id = e.department_id
              AND d1.location_id = 1700 )
        OR EXISTS ( SELECT /*+QB_NAME[SUB2)*/ 1
                    FROM  department d2
                    WHERE d2.department_id = e.department_id
                    AND d2.department_name > 'Acounting' )

 

초보자의 SQL문

서브쿼리 두개를 하나로 합치고. 서브쿼리 내부에서  location_id = 1700 조건과 department_name > 'Acounting' 조건을 OR로 처리하면 된다.

실제로 Logical Optimizer는 이런 의도대로 SQL 을 변형 시킨다.

 

* PLAN *

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |      1 |        |       |     7 (100)|
|   1 |  MERGE JOIN                   |            |      1 |    103 |  4635 |     7  (15)|
|   2 |   SORT UNIQUE                 |            |      1 |     27 |   513 |     2   (0)|
|*  3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENT |      1 |     27 |   513 |     2   (0)|
|   4 |     INDEX FULL SCAN           | DEPT_ID_PK |      1 |     27 |       |     1   (0)|
|*  5 |   SORT JOIN                   |            |     27 |    107 |  2782 |     5  (20)|
|*  6 |    TABLE ACCESS FULL          | EMPLOYEE   |      1 |    107 |  2782 |     4   (0)|
-------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(("D1"."DEPARTMENT_NAME">'Acounting' OR "D1"."LOCATION_ID"=1700))
   5 - access("D1"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
       filter("D1"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   6 - filter("E"."SALARY">2000)

 

위 서브쿼리를 보면 department 테이블을 엑세스 하는 것이 두번 나와야 하지만 한번 만 한다.

왜 그럴까.? 이유는 Outline Data 에있다.

 

Outline Data
-------------
 
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$93EBA524")
      UNNEST(@"SEL$9F76754D")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$9F76754D")
      COALESCE_SQ(@"SEL$2")
      OUTLINE(@"SEL$4B4BED4E")
      COALESCE_SQ(@"SUB1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SUB1")
      INDEX(@"SEL$93EBA524" "D1"@"SUB1" ("DEPARTMENT"."DEPARTMENT_ID"))
      FULL(@"SEL$93EBA524" "E"@"SEL$1")
      LEADING(@"SEL$93EBA524" "D1"@"SUB1" "E"@"SEL$1")
      USE_MERGE(@"SEL$93EBA524" "E"@"SEL$1")
      PX_JOIN_FILTER(@"SEL$93EBA524" "E"@"SEL$1")
      END_OUTLINE_DATA
  */

 

오라클이 내부적으로 COALESCE_SQ라는 힌트를 사용함으로써 SQC 가 작동한 것이다.

변형된 SQL은 다음과 같다.

 

SELECT /*+ QB_NAME(MAIN)*/  e.employee_id,e.first_name,e.last_name
FROM employee e
WHERE e.salary > 2000
 AND ( EXISTS ( SELECT /*+QB_NAME(SUB1)*/ 1
        FROM department d1
        WHERE d1.department_id = e.department_id
              AND (d1.location_id = 1700 OR d1.department_name > 'Acounting')));

 

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |      1 |        |       |     7 (100)|
|   1 |  MERGE JOIN                   |            |      1 |    103 |  4635 |     7  (15)|
|   2 |   SORT UNIQUE                 |            |      1 |     27 |   513 |     2   (0)|
|*  3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENT |      1 |     27 |   513 |     2   (0)|
|   4 |     INDEX FULL SCAN           | DEPT_ID_PK |      1 |     27 |       |     1   (0)|
|*  5 |   SORT JOIN                   |            |     27 |    107 |  2782 |     5  (20)|
|*  6 |    TABLE ACCESS FULL          | EMPLOYEE   |      1 |    107 |  2782 |     4   (0)|
-------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - filter(("D1"."DEPARTMENT_NAME">'Acounting' OR "D1"."LOCATION_ID"=1700))
   5 - access("D1"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
       filter("D1"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
   6 - filter("E"."SALARY">2000)

 

다음은 10053 TRACE 내용을 분석해보자

 

Query before EXDS SQ coalescing:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("MAIN") */ "E"."EMPLOYEE_ID" "EMPLOYEE_ID","E"."FIRST_NAME" "FIRST_NAME","E"."LAST_NAME" "LAST_NAME" FROM "TLO"."EMPLOYEE" "E" WHERE "E"."SALARY">2000 AND ( EXISTS (SELECT /*+ QB_NAME ("SUB1") */ 1 "1" FROM "TLO"."DEPARTMENT" "D1" WHERE "D1"."DEPARTMENT_ID"="E"."DEPARTMENT_ID" AND "D1"."LOCATION_ID"=1700) OR  EXISTS (SELECT 1 "1" FROM "TLO"."DEPARTMENT" "D2" WHERE "D2"."DEPARTMENT_ID"="E"."DEPARTMENT_ID" AND "D2"."DEPARTMENT_NAME">'Acounting'))

 

SQL 수행되기 직전의 SQL모습을 볼 수 있다.

위의 Trace 내용은 SQC 가 연이어 발생할 것임을 암시하고 있다.

 

Registered qb: SEL$4B4BED4E 0x545808 (SUBQUERY COALESCE SUB1; SUB1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$4B4BED4E nbfros=1 flg=0
    fro(0): flg=0 objn=86726 hint_alias="D1"@"SUB1"

Registered qb: SEL$C33E9E0D 0x545808 (SUBQUERY COALESCE SEL$4B4BED4E; SEL$1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
  signature (): qb_name=SEL$C33E9E0D nbfros=1 flg=0
    fro(0): flg=0 objn=86726 hint_alias="D1"@"SUB1"

 

 

쿼리블럭 SUB1에 SQC 가 발생 하여 새로운 쿼리블럭 SEL$4B4BED4E가 생성되었다.

연이어 쿼리블럭 SUB2가 새로 만들어진 쿼리 블럭인 SEL$4B4BED4E에 병합 되었다.

 이 때에도 새로운 쿼리블럭 SEL$5DD85A06 D이 생성 되었으며 쿼리블럭 내부의 FROM 절에는 SUB1 만 남아있다.

즉 SUB2가 SUB1에 포함되어 서브쿼리가 하나로 합처진 것이다.

 

Query before EXDS SQ coalescing:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("MAIN") */ "E"."EMPLOYEE_ID" "EMPLOYEE_ID","E"."FIRST_NAME" "FIRST_NAME","E"."LAST_NAME" "LAST_NAME" FROM "TLO"."EMPLOYEE" "E" WHERE "E"."SALARY">2000 AND ( EXISTS (SELECT /*+ QB_NAME ("SUB1") */ 1 "1" FROM "TLO"."DEPARTMENT" "D1" WHERE "D1"."DEPARTMENT_ID"="E"."DEPARTMENT_ID" AND "D1"."LOCATION_ID"=1700) OR  EXISTS (SELECT 1 "1" FROM "TLO"."DEPARTMENT" "D2" WHERE "D2"."DEPARTMENT_ID"="E"."DEPARTMENT_ID" AND "D2"."DEPARTMENT_NAME">'Acounting'))

 

 

위의 SQL은 변형이 완료된 상태를 나타낸 것이다.

쿼리블럭 SUB2가 없어지고 OR 절로 바뀐 것을 알 수 있다.

 

SQC를 Control 하는 파라미터는 아래와 같다.

 

alter session set "_optimizer_coalesce_subqueries" = FALSE;

 

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)|
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |      1 |        |       |     4 (100)|
|*  1 |  FILTER                      |            |      1 |        |       |            |
|*  2 |   TABLE ACCESS FULL          | EMPLOYEE   |      1 |    107 |  2782 |     4   (0)|
|*  3 |   INDEX RANGE SCAN           | DEPT_IX_01 |     12 |      1 |     7 |     1   (0)|
|*  4 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT |      7 |      1 |    16 |     1   (0)|
|*  5 |    INDEX UNIQUE SCAN         | DEPT_ID_PK |      7 |      1 |       |     0   (0)|
------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(( IS NOT NULL OR  IS NOT NULL))
   2 - filter("E"."SALARY">2000)
   3 - access("D1"."DEPARTMENT_ID"=:B1 AND "D1"."LOCATION_ID"=1700)
   4 - filter("D2"."DEPARTMENT_NAME">'Acounting')
   5 - access("D2"."DEPARTMENT_ID"=:B1)

 

해당 파라미터를 False 로 하자 SQC 기능이 동작하지 않는다

이 파라미터의 Default 값은 TRUE 이다.