개발자의 질문
개발자 한명이 DBA 에게 질문을 던졌다. ‘스칼라 서브쿼리를 사용한 SQL 인라인뷰로 싸고 인라인뷰 외부에서 스칼라 서브쿼리를 Filter 조건으로 사용하면 스칼라 서브쿼리가 없어지는 현상이 발생합니다. 현상이 정상인가요?’  그런데 DBA 그런일은 발생할 수가 없다고 하였다. 과연 사실일까? 정답은 스칼라 서브쿼리가 없어진다는 것이다. 예리한 눈을 가진 개발자 임에 틀림없다.

 

백견이 불여일행
SSTS( Scalar Subquery To Subquery )
스칼라 서브쿼리를 서브쿼리로 변경시키는 Transformation 과정이다. 하지만 항상 변환되지 않는다. 스칼라 서브쿼리를 인라인뷰 외부에서 Filter 조건으로 사용할때만 가능하다. 아래의 SQL 보자

인덱스 상황 :

EMP_JOB_IX : employee (job_id)

DEPT_ID_PK1 : department(department_id)

SELECT a.employee_id, a.first_name, a.last_name, a.email

  FROM (SELECT e.employee_id, e.first_name, e.last_name, email,

               (SELECT location_id

                  FROM department d

                 WHERE d.department_id = e.department_id) AS location_id

          FROM employee e

         WHERE e.job_id = 'IT_PROG') a

 WHERE a.location_id > 0;


위의 SQL Location 스칼라 서브쿼리로 구현하였다. 하지만 스칼라 서브쿼리를 Select 하는데는 사용하지 않고 Where 조건으로 사용하는 것을 주목하라.

----------------------------------------------------------------------------------

| Id  | Operation                    | Name        | E-Rows |E-Bytes| Cost (%CPU)|

----------------------------------------------------------------------------------

|*  1 |  FILTER                      |             |        |       |            |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEE    |      5 |   195 |     2   (0)|

|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IX  |      5 |       |     1   (0)|

|   4 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT  |      1 |     7 |     1   (0)|

|*  5 |    INDEX UNIQUE SCAN         | DEPT_ID_PK1 |      1 |       |     0   (0)|

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(>0)

   3 - access("E"."JOB_ID"='IT_PROG')

   5 - access("D"."DEPARTMENT_ID"=:B1)

 

위의 Plan 보면 본능적으로 스칼라 서브쿼리가 서브쿼리로 바뀐 것을 알수 있어야 한다. 다시말하면 옵티마이져가 SQL 아래처럼 바꾼 이다.

 

SELECT e.employee_id, e.first_name, e.last_name, email

  FROM employee e

 WHERE e.job_id = 'IT_PROG'

   AND (SELECT location_id

          FROM department d

         WHERE d.department_id = e.department_id) > 0 ;

 

----------------------------------------------------------------------------------

| Id  | Operation                    | Name        | E-Rows |E-Bytes| Cost (%CPU)|

----------------------------------------------------------------------------------

|*  1 |  FILTER                      |             |        |       |            |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEE    |      5 |   195 |     2   (0)|

|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IX  |      5 |       |     1   (0)|

|   4 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT  |      1 |     7 |     1   (0)|

|*  5 |    INDEX UNIQUE SCAN         | DEPT_ID_PK1 |      1 |       |     0   (0)|

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(>0)

   3 - access("E"."JOB_ID"='IT_PROG')

   5 - access("D"."DEPARTMENT_ID"=:B1)

 

실행계획과 Predicate Information 완전히 같음을 알수 있다. 이제 10053 Trace 분석해보자.

***************************

Order-by elimination (OBYE)

***************************

중간생략

CVM:   Merging SPJ view SEL$2 (#0) into SEL$1 (#0)

Registered qb: SEL$F5BB74E1 0xc4643d0 (VIEW MERGE SEL$1; SEL$2)

---------------------

QUERY BLOCK SIGNATURE

---------------------

  signature (): qb_name=SEL$F5BB74E1 nbfros=1 flg=0

    fro(0): flg=0 objn=70296 hint_alias="E"@"SEL$2"

 

View Merging 발생하여 SEL$2(인라인뷰 a) SEL$1(메인쿼리) 통합 되어 버렸다. View Merging 발생하여 새로운 쿼리블럭인 SEL$F5BB74E1 생성 되었다. 하지만 쿼리블럭 SEL$F5BB74E1 From 절을 보면 employee(Alias 로는 E) 존재하고 department(D) 존재 하지 않는다. 그렇다면 스칼라 서브쿼리는 어디로 갔을까? 해답은 FPD(Filter Push Down) 기능에 있다.

 

**************************

Predicate Move-Around (PM)

**************************

중간생략

query block SEL$F5BB74E1 (#0) unchanged

FPD: Considering simple filter push in query block SEL$F5BB74E1 (#0)

 (SELECT "D"."LOCATION_ID" FROM "DEPARTMENT" "D")>0 AND "SYS_ALIAS_1"."JOB_ID"='IT_PROG'

FPD: Considering simple filter push in query block SEL$3 (#0)

"D"."DEPARTMENT_ID"=:B1

try to generate transitive predicate from check constraints for query block SEL$3 (#0)

finally: "D"."DEPARTMENT_ID"=:B1


FPD
기능에 의해서 위에서 새로 생성된 쿼리블럭 SEL$F5BB74E1 조건절에 서브쿼리를 생성하고 있다. 또한 새로 생성된 서브쿼리에 "D"."DEPARTMENT_ID"=:B1 조건을 밀어넣고 있다.

 

검증

스칼라  서브쿼리가 서브쿼리로 바뀌었으므로 서브쿼리에 사용할수 있는 힌트 Push_subq 사용해보자. 이것이 가능해야지만 진정한 서브쿼리이다.

 

SELECT /*+ PUSH_SUBQ(@SUB) */

        a.employee_id, a.first_name, a.last_name, a.email

  FROM (SELECT e.employee_id, e.first_name, e.last_name, email,

               (SELECT /*+ QB_NAME(SUB) */ location_id

                  FROM department d

                 WHERE d.department_id = e.department_id) AS location_id

          FROM employee e

         WHERE e.job_id = 'IT_PROG') a

 WHERE a.location_id > 0;

 

----------------------------------------------------------------------------------

| Id  | Operation                    | Name        | E-Rows |E-Bytes| Cost (%CPU)|

----------------------------------------------------------------------------------

|*  1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEE    |      1 |    39 |     2   (0)|

|*  2 |   INDEX RANGE SCAN           | EMP_JOB_IX  |      5 |       |     1   (0)|

|   3 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT  |      1 |     7 |     1   (0)|

|*  4 |    INDEX UNIQUE SCAN         | DEPT_ID_PK1 |      1 |       |     0   (0)|

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(>0)

   2 - access("E"."JOB_ID"='IT_PROG')

   4 - access("D"."DEPARTMENT_ID"=:B1)

 

실행계획을 보면 알겠지만 Subquery Pushing 이 발생하여 Id 기준으로 2번과 3번이 동일 Level 상에 존재한다. Subquery Pushing 이 성공적으로 수행된것을 알수 있다.

 

한단계 나아가 보자

CREATE INDEX HR.EMP_JOB_DEPT_IX ON HR.EMPLOYEE (JOB_ID, DEPARTMENT_ID);

 

EMPLOYEE 테이블에 JOB_ID, DEPARTMENT_ID 생성 하였다. 바로 위에서 실행한 SQL 다시 실행 해보자.

--> 여기서 PUSH_SUBQ 를 적용한 SQL 실행한다.

 

---------------------------------------------------------------------------------------

| Id  | Operation                     | Name            | E-Rows |E-Bytes| Cost (%CPU)|

---------------------------------------------------------------------------------------

|   1 |  TABLE ACCESS BY INDEX ROWID  | EMPLOYEE        |      1 |    39 |     2   (0)|

|*  2 |   INDEX RANGE SCAN            | EMP_JOB_DEPT_IX |      1 |       |     1   (0)|

|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENT      |      1 |     7 |     1   (0)|

|*  4 |     INDEX UNIQUE SCAN         | DEPT_ID_PK1     |      1 |       |     0   (0)|

---------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("E"."JOB_ID"='IT_PROG')

       filter(>0)

   4 - access("D"."DEPARTMENT_ID"=:B1)

  

이것은 제공자 서브쿼리가 아니다

실행계획은 마치 제공자 서브쿼리처럼 바뀌었지만 이것은 제공자 서브쿼리가 아니고 Subquery Pushing 에 의한 효과이다. Predicate Information 을 주목하라. ID 기준으로 2번에서 Filter 가 수행된다. . (JOB_ID, DEPARTMENT_ID) 인덱스를 사용하지 않은 SQLPredicate Information을 보면 EMPLOYEE 테이블 엑세스 시에 filter 가 발생함을 알수 있다. 이것이 바로 Subquery Pushing 과 인덱스에 의한 Early Filter 의 효과이다. 물론 Early Filter 의 개념은 조인순서상에 서브쿼리를 최대한 먼저 조인 하는것으로 바꾸는 것이다. 하지만 위와 같은 Early Filter의 부가적인 기능도 있음을 알아야 한다.

 Subquery Pushing 에 의한 Early Filter 기능은 Using Sub query Method( Filter / Access sub Query ) 글을 참조하라

결론:
이처럼
Oracle Query Transformer
스칼라 서브쿼리를 Filter 조건으로 사용할 SSTS를 발생시킨다.. 여러분이 Query Transformation 의도 하던 의도하지 않던 말이다.

PS :
제목이 이상하다는 의견이 있다. 제목은 옵티마이져가  변환을 수행한다는 의미이다. 수동으로 스칼라 서브쿼리를 서브쿼리로 고치라는 의미가 아니므로 착오가 없길 바란다.


Posted by extremedb
,

부분범위 처리 혹은 페이징 처리시 탁월한 힘을 발휘하는것이 스칼라 서브쿼리이다. 여러가지 테스트 결과 스칼라 서브쿼리는 부분범위처리시 가장 좋다는 Nested Loop 조인보다 성능상 좀더 유리하다.  과다호출로 몸살을 앟고 있는 사용자 함수를 스칼라 서브쿼리로만 바꾸어도 튜닝이 된다. 또한 View Merging 이나 JPPD(Join Push Predicate Down) 등의 성공여부를 고민할 필요도 없으므로 특히 개발자 들에게 인기가 많은것 또한 사실이다.

모든 기능은 용도에 맞게 사용해야 한다.
  이렇게 많이 활용되고 있는 스칼라 서브쿼리도 한계는 있다. Multi Column, Multi Row 을 RETURN 하는 것은 만들수가 없다는 것이다. 물론 Pipilined Funtion 으로 해결이 가능하지만 모든경우에 이것을 만들기란 여간 힘든것이 아니다. 바로 이럴때 사용가능한것이 Cast + MultiSet 함수를 이용한 스칼라 서브쿼리이다. MultiSet 함수는 Oracle 8.1.7  부터 사용이 가능하다. 필자가 이기능을 소개하는 이유는 실무에서 이런기능을 꼭 써야하는 경우임에도 Pipelined 함수등을 사용하는것을 많이 보아서이다. Pipelined 등의 함수를 사용해야 할때는 자주사용하는 SQL 이 아주 길어서 Network 의 부하가 생길때 혹은 모듈로서 공유를 해야될때 이다.

그럼 한번 만들어보자.
아래의 스크립트는 customers 테이블과 sales 테이블을 이용하여 MultiSet 스칼라 서브쿼리를 구현한것이다.
Cast + MultiSet 스칼라 서브쿼리를 사용하려면 먼저 type 2개를 만들어야 한다.


CREATE OR REPLACE TYPE SALES_CUST_TYPE AS OBJECT
(
 PROD_COUNT NUMBER(5),
 CHANNEL_COUNT NUMBER(2),       --> 스칼라 서브쿼리가 RETURN 할 TYPE 선언
 AMOUNT_TOT NUMBER
)
;
  
CREATE OR REPLACE TYPE ARRAY_SALES_CUST  --> 위에서 선언한 TYPE 을 배열로 선언
AS VARRAY(1) OF SALES_CUST_TYPE ;       --> 고객별로 여러건이 RETURN 될 경우 값을 넉넉히 줄것.
         --> 아래의 예제는 고객당 1건만 RETURN 하므로 VARRAY 에 1을 준것이다.

이렇게 2개의 type 을 선언하면 모든 준비가 끝난다. Pipilined Funtion 의 코딩량에 비하면 사용하기가 훨신 간편함을 알수 있다. 그럼 이제 SQL 을 만들어 보자.

SELECT C.CUST_ID, C.CUST_YEAR_OF_BIRTH
             ,S.PROD_COUNT, S.CHANNEL_COUNT, S.AMOUNT_TOT
  FROM (
               SELECT /*+ FULL(C) */
                            C.CUST_ID, C.CUST_YEAR_OF_BIRTH,
                            CAST( MULTISET ( SELECT count(distinct s.PROD_ID)        AS PROD_COUNT
                                                                       ,count(distinct s.CHANNEL_ID)  AS CHANNEL_COUNT
                                                                       ,sum(s.AMOUNT_SOLD)           AS AMOUNT_TOT
                                                            FROM sh.sales s
                                                           WHERE s.cust_id = c.cust_id
                                                         ) AS  ARRAY_SALES_CUST --> 위에서 선언한 배열 TYPE 이용.
                                     ) AS SALES_CUST --> MultiSet 함수의 Alias 선언
                FROM sh.customers c
            )  C,
            TABLE(SALES_CUST) S                     --> 위에서 선언한 MultiSet 함수의 Alias 를 테이블로 이용함.
 WHERE S.PROD_COUNT  <> 0 ;    --> 스칼라 서브쿼리내의 특정컬럼을 조건절에 이용한 예제임.

SQL 의 결과 :

사용자 삽입 이미지















결과는 성공적이다. 성능면에서도 스칼라서브쿼리와 동일하다. 이것은 마치 스칼라서브쿼리의 장점과 PIPELINED TABLE 함수의 장점을 취한것과 같다. 하지만 대용량 배치 SQL 에서는 Hash 조인이나 Sort Merge 조인보다 성능이 좋지 못하므로 주의 해야한다.

결론 :
Cast + MultiSet 스칼라 서브쿼리는 부분범위 처리시 혹은 페이징 처리시에 탁월한 성능을 발휘하며 스칼라 서브쿼리의 단점인  Multi Column, Multi Row 을 RETURN 할수 있다. 뿐만아니라 View Merging 이나 JPPD (Join Predicate Push Down) 이 불가능할 경우의 훌륭한 해결책이 될수 있다.

P.S :
위의 예제는 Multi Column을 RETURN 하지만 Multi Row 를 RETURN 하지 않는다. 독자 여러분이 위의  Cast + MultiSet 스칼라 서브쿼리를 Multi Column, Multi Row 를 RETURN 하는것으로 바꾸어 보라. 백견이 불여일행 이다.

'Oracle > SQL Pattern' 카테고리의 다른 글

오라클 Regular Expressions 완전정복  (22) 2009.10.07
간단한 집합개념 Test  (7) 2009.07.06
Model 절에 대하여  (10) 2009.05.28
SQL 포기하지마라.  (7) 2009.05.28
Upgrade Select For Update  (3) 2009.05.19
Posted by extremedb
,