Mooyoung2020

Mooyoung2020

SQL Tunner

SQL 스칼라쿼리의 통합 방법

Updated:

예제 테이블 생성

오라클 11G DBMS를 설치하면 샘플 유저인 SCOTT 유저가 설치되는데 EMP 라는 테이블을 기준으로 테스트해봅니다.

Empno ename job mgr hiredate sal comm deptno
7369 SMITH CLERK 7902 1980/12/17 00:00:00 800   20
7499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981/02/22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981/04/02 00:00:00 2975   20
7654 MARTIN SALESMAN 7698 1981/09/28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981/05/01 00:00:00 2850   30
7782 CLARK MANAGER 7839 1981/06/09 00:00:00 2450   10
7788 SCOTT ANALYST 7566 1987/04/19 00:00:00 3000   20
7839 KING PRESIDENT   1981/11/17 00:00:00 5000   10
7844 TURNER SALESMAN 7698 1981/09/08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987/05/23 00:00:00 1100   20
7900 JAMES CLERK 7698 1981/12/03 00:00:00 950   30
7902 FORD ANALYST 7566 1981/12/03 00:00:00 3000   20
7934 MILLER CLERK 7782 1982/01/23 00:00:00 1300   10
  • 생성 스크립트

CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));

INSERT INTO EMP VALUES (7369, ‘SMITH’, ‘CLERK’, 7902, TO_DATE(‘17-12-1980’, ‘DD-MM-YYYY’), 800, NULL, 20);
INSERT INTO EMP VALUES (7499, ‘ALLEN’, ‘SALESMAN’, 7698, TO_DATE(‘20-02-1981’, ‘DD-MM-YYYY’), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, ‘WARD’, ‘SALESMAN’, 7698, TO_DATE(‘22-02-1981’, ‘DD-MM-YYYY’), 1250, 500, 30);
INSERT INTO EMP VALUES (7566, ‘JONES’, ‘MANAGER’, 7839, TO_DATE(‘02-04-1981’, ‘DD-MM-YYYY’), 2975, NULL, 20);
INSERT INTO EMP VALUES (7654, ‘MARTIN’, ‘SALESMAN’, 7698, TO_DATE(‘28-09-1981’, ‘DD-MM-YYYY’), 1250, 1400, 30);
INSERT INTO EMP VALUES (7698, ‘BLAKE’, ‘MANAGER’, 7839, TO_DATE(‘01-05-1981’, ‘DD-MM-YYYY’), 2850, NULL, 30);
INSERT INTO EMP VALUES (7782, ‘CLARK’, ‘MANAGER’, 7839, TO_DATE(‘09-06-1981’, ‘DD-MM-YYYY’), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, ‘SCOTT’, ‘ANALYST’, 7566, TO_DATE(‘09-12-1982’, ‘DD-MM-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES (7839, ‘KING’, ‘PRESIDENT’, NULL, TO_DATE(‘17-11-1981’, ‘DD-MM-YYYY’), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, ‘TURNER’, ‘SALESMAN’, 7698, TO_DATE(‘08-09-1981’, ‘DD-MM-YYYY’), 1500, NULL, 30);
INSERT INTO EMP VALUES (7876, ‘ADAMS’, ‘CLERK’, 7788, TO_DATE(‘12-01-1983’, ‘DD-MM-YYYY’), 1100, NULL, 20);
INSERT INTO EMP VALUES (7900, ‘JAMES’, ‘CLERK’, 7698, TO_DATE(‘03-12-1981’, ‘DD-MM-YYYY’), 950, NULL, 30);
INSERT INTO EMP VALUES (7902, ‘FORD’, ‘ANALYST’, 7566, TO_DATE(‘03-12-1981’, ‘DD-MM-YYYY’), 3000, NULL, 20);
INSERT INTO EMP VALUES (7934, ‘MILLER’, ‘CLERK’, 7782, TO_DATE(‘23-01-1982’, ‘DD-MM-YYYY’), 1300, NULL, 10);

스칼라쿼리 통합

스칼라쿼리 통합 기술은 SQL 튜닝을 수행시 Select절에 있는 스칼라쿼리가 동일 테이블을 접근하고 있는경우 이를 서로 통합하여 SQL 부하흫 줄여주는 기술을 말합니다.
실전현장에서는 대단히 빠른 속도가 필요한 SQL들이 있는데 이런 경우 극한적인 튜닝기술이 필요하게 됩니다. 이때 스칼라쿼리가 서로 통합되어 있지 않으면 불필요한 부하를 발생시키므로 통합한 가능한 범위내에서 스칼라쿼리를 통합시켜주는것이 좋습니다.

튜닝대상 스칼라쿼리

이름, 월급, 사원 테이블의 최대월급, 최소월급, 전체월급을 현 월급을 많이 받는순으로 출력하시오 

Select  ename, sal, 
         (select max(sal) from  emp) max_sal,
         (select min(sal) from  emp) min_sal,
         (select sum(sal) from  emp) sum_sal 
From  emp
Order by sal desc;
ENAME SAL MAX_SAL MIN_SAL SUM_SAL
KING 5000 5000 800 29025
FORD 3000 5000 800 29025
SCOTT 3000 5000 800 29025
JONES 2975 5000 800 29025
BLAKE 2850 5000 800 29025
CLARK 2450 5000 800 29025
ALLEN 1600 5000 800 29025
TURNER 1500 5000 800 29025
MILLER 1300 5000 800 29025
WARD 1250 5000 800 29025
MARTIN 1250 5000 800 29025
ADAMS 1100 5000 800 29025
JAMES 950 5000 800 29025
SMITH 800 5000 800 29025 
Execution Plan
-------------------------------------------------------------
   0    SELECT STATEMENT Optimizer=ALL_ROWS 
          (Cost=4 Card=14 Bytes=140)
   1    0    SORT (AGGREGATE) (Card=1 Bytes=4)
   2    1      TABLE ACCESS (FULL) OF 'EMP' (TABLE)
   3    0    SORT (AGGREGATE) (Card=1 Bytes=4)
   4    3      TABLE ACCESS (FULL) OF 'EMP' (TABLE)
   5    0    SORT (AGGREGATE) (Card=1 Bytes=4)
   6    5      TABLE ACCESS (FULL) OF 'EMP' (TABLE)
   7    0    SORT (ORDER BY) (Cost=4 Card=14 Bytes=140)
   8    7      TABLE ACCESS (FULL) OF 'EMP' (TABLE)

튜닝 스칼라쿼리 1

모든 RDBMS에서 사용할수 있는 방법입니다. 대부분의 RDBMS들은 RPAD와 SUBSTR 함수를 제공합니다. 이것을 이용하여 간단하게 스칼라쿼리를 통합할수 있습니다.
통합시 실행계획을 보면 스칼라쿼리가 3번 동일테이블로 접근하는것이 1번 접근하는것으로 개선되었다.

select ename, sal, 
       substr(totalsal,1,10)  max_sal,
       substr(totalsal,11,10) min_sal,
       substr(totalsal,21,10) sum_sal
 from (select  ename, sal, 
               (select rpad(max(sal),10,'  ')||rpad(min(sal),10,‘  ‘)||
                       rpad(sum(sal),10,'  ') 
                from  emp )  totalsal    
          from  emp)
 order by sal desc;

Execution Plan
------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=140)
   1    0    SORT (AGGREGATE) (Card=1 Bytes=4)
   2    1      TABLE ACCESS (FULL) OF 'EMP' (TABLE) 
   3    0    SORT (ORDER BY) (Cost=4 Card=14 Bytes=140)
   4    3      TABLE ACCESS (FULL) OF 'EMP' (TABLE)

튜닝 스칼라쿼리 2

다음은 오라클 11G 이상의 DBMS에서만 사용할수 있는 방법입니다. regexp_substr 함수를 이용하여 자리수를 원하는 만큼 끊어서 읽어 처리합니다.

Select ename, sal, 
       regexp_substr(tot, '[^/]+', 1, 1) as max_sal,  
       regexp_substr(tot, '[^/]+', 1, 2 ) as min_sal,
       regexp_substr(tot, '[^/]+', 1, 3 ) as sum_sal 
From  (select ename, sal, 
             (select max(sal)||'/'||min(sal)||'/'||sum(sal) from emp) as tot
       from emp)
Order by sal desc;

튜닝 스칼라쿼리 3

아래의 예제는 오라클의 row control 방법을 사용하여 통합한 예제입니다. 이방법은 스칼라쿼리내에 있는 테이블이 from절에 있는 테이블과 동일해야만 사용할수 있는 약점이 있습니다. 일반적으로 from절의 테이블과 스칼라쿼리내의 테이블이 틀릴경우가 많은데 이경우에는 안타깝게 이방법을 사용할수 없다. 그러나 테이블을 한번만 읽기 떄문에 성능은 가장 좋습니다고 생각됩니다.

Select ename,  sal, 
        max(sal) over(order by sal rows between unbounded  
                            preceding and unbounded following) max_sal,
        min(sal) over(order by sal desc rows between unbounded  
                           preceding and unbounded following) min_sal,
        sum(sal) over(order by sal desc rows between unbounded 
                           preceding and unbounded following) sum_sal
From  emp
Order by sal desc;

Execution Plan
---------------------------------------------------------------
   0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=140)
   1    0    WINDOW (SORT) (Cost=3 Card=14 Bytes=140)
   2    1      TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=140)

튜닝 실전예제

실제현장에서 튜닝한 SQL입니다. 스칼라쿼리를 통합하여 성능을 개선하는 기술은 실전현장에서 많이 사용할수 있는 기술입니다. 매우 빈번하게 사용되는 SQL을 극한적으로 튜닝하여 성능을 개선할때 스칼라쿼리 통합의 관점에서도 검토해주길 바랍니다.
아래의 예제는 통합할 테이블의 칼럼을 인라인뷰에서 || 연결자와 ‘/’를 사용하여 구분하고 바깥쪽 스칼라쿼리에서 Regexp_substr 함수를 사용하여 원하는 자리수만큼 끊어 읽어서 스칼라쿼리에서 일어나는 테이블 접근을 최소화 하였습니다.

SELECT AA.HOUSE_NO, 
       (select regexp_substr(tot, '[^/]+', 1, 1) as CUSTOMER_NO from  dual)  CUSTOMERNO, 
       (select regexp_substr(tot, '[^/]+', 1, 2) as CUST_NAME  from  dual )  CUST_NAME,
       aa.BUILD_CD,   aa.BIL_BUILD_NAME, 
       (select regexp_substr(tot2, '[^/]+', 1, 1) as CENTER_ID from  dual )  CENTER_ID, 
       (select regexp_substr(tot2, '[^/]+', 1, 2) as CUST_NAME  from  dual )  CENTER_NAME, 
       aa.DONG_HO, aa.ADDR, aa.ADDR_ALL, aa.ADDRESS_DESC,  aa.LIVING_IN_DATE, 
       aa.LOOKUP_CODE_NAME, ROWNUM RNUM 
  from ( SELECT  HI.HOUSE_NO, 
                  (select CUSTOMER_NO||'/'||CUST_NAME
                   from EBL_CUSTOMER_INFO 
                   where HI.CUSTOMER_NO = CUSTOMER_NO ) as tot , 
                   BM.BUILD_CD, 
                   BM.BIL_BUILD_NAME, 
                  (select CENTER_ID||'/'||CENTER_NAME 
                   from EBL_CENTER_INFO ct 
                   where BM.BIL_CENTER_ID = CT.CENTER_ID) as tot2
        FROM table ………

You May Also Enjoy