튜닝이론 - 3

조인 이론 

 

조인시, 드라이빙 테이블은 조건에 대한 인덱스 생성이 권장되고 룩업 테이블은 조인 기준칼럼 + 조건에 대한 인덱스의 생성이 권장됩니다.

 

드라이빙 테이블의 조건절에서 BETWEEM과 LIKE의 성능상 차이점은 없습니다. LIKE 조건절을 드라이빙 테이블에 사용하면 수행결과에서 FILTER가 발생하지만 성능상의 문제는 발생하지 않습니다.

 

룩업 테이블의 인덱스 생성시 조인기준칼럼이 조건절 칼럼보다 선두에 위치하는 것이 범용성이 높습니다.

 

조인시, 드라이빙 테이블의 대상 레코드만큼 조인이 진행됩니다. 룩업테이블 인덱스의 루트블록은 조인시 항상 거쳐가므로 버퍼 피닝이 발생합니다.

테이블 렌덤 액세스 비효율을 낮추는 방법은 조건절의 칼럼이 모두 인덱스에 포함되어 테이블에서 필터링이 발생하지 않도록 하는 것입니다.

수평적 스캔을 효율화하는 방법은 최대한 많은 조건이 드라이빙 조건으로 읽히도록 하여 인덱스 매칭도를 높이는 것입니다.

 

룩업 테이블은 드라이빙 테이블의 대상 레코드만큼 수직적 탐색을 진행하므로 룩업 테이블의 DEPTH를 낮춰야 수직적 탐색의 효율이 높아집니다.

NL 조인

대상레코드의 수가 적은 쪽이 드라이빙 테이블이 되고 많은 쪽이 룩업 테이블이 됩니다. 조인기준칼럼은 룩업 테이블의 인덱스에 반드시 존재해야 합니다. 소량의 데이터를 조인하기 위해 인덱스를 사용하는 렌덤 액세스 위주의 조인입니다. 조인기준 조건절이 등차조건이 아니라도 조인이 가능합니다.

HASH JOIN

대상 레코드의 수가 적은 집합을 BUILD INPUT, 많은 집합을 PROBE INPUT으로 하여 조인합니다. BUILD INPUT의 레코드를 HASH FUNCTION에 대입해 나온 결과값을 PGA 테이블에 적제합니다. PROBE INPUT의 조인대상칼럼의 값을 HASH FUNCTION에 대입한 결과값과 동일한 해시 테이블의 레코드와 조인합니다. HASH JOIN은 등치(=) 연산자 조인만 가능합니다.

 

ALTER SESSION SET HASH_AREA_SIZE = '2GB'

 

해시조인의 성능은 BUILD INPUT의 크기와 조인기준칼럼의 중복정도에 좌우됩니다.

 

BUILD INPUT 의 크기가 해시 테이블에 저장될만큼 충분히 작아야하고 조인기준칼럼의 중복값이 적어 해시링크가 적게 발생해야합니다. 일반적으로 조인은 조인대상 레코드가 적은 테이블을  조인대상 레코드가 많은 테이블로 조인하는게 유리합니다.

 

소트머지 조인의 경우 적은 쪽 조차 SORT AREA에 저장하지 못할 정도로 크기가 큰 경우 큰쪽에서 작은 쪽으로 조인하는게 유리합니다. 이는 LOOKUP 테이블에서 발생하는 디스크 I/O라도 최소화 하기 위함입니다.

 

OUTER JOIN

 

일반적인 조인은 OUTER TABLE이 항상 DRIVING 테이블이 됩니다. 하지만 HASH JOIN에서는 INNER TABLE 도 DRIVING 테이블의 역할을 할 수 있습니다. SWAP_JOIN_INPUTS 힌트를 활용해 DRIVING 테이블을 지정합니다.

 

3개 이사의 테이블을 해시조인하는 실행계획은 계단식과 등호형이 있습니다. 계단식은 조인 결과를 PROBE-INPUT으로 다음 테이블을 BUILD-INPUT으로 설정해 조인합니다.

 

실행계획과 실제 실행 순서가 다른 대표적인 경우는 3가지입니다.

 

- SELECT 절에 있는 스칼라 서브쿼리
- 해시조인 계단식 ( 실행계획 상 상위 테이블이 늦게 읽힌다)
- PREFETCH

 

스칼라 서브쿼리

 

스칼라 서브쿼리는 하나의 칼럼에 대응하는 값을 반환합니다. SELECT 절과 WHERE 절에 사용됩니다. SELECT 절에 위치하면 단일 값을 반환하고 조건절의 경우, IN 연산자 뒤에서 다수의 값을 반환할 수 있습니다.

 

스칼라 서브쿼리는 캐싱이 되는 이점이 있습니다. 부모쿼리와 연결되는 기준 칼럼과 반환값을 캐싱합니다. 스칼라 서브쿼리를 조인문으로 풀때는 반드시 부모 테이블을 OUTER 조인해야합니다.

 

선분이력 끊기

 

DROP TABLE TODO
CREATE TABLE TODO
(
    CUSTNO VARCHAR2(10),
    STARTDT DATE,
    ENDDT DATE,
    RATIO NUMBER(1,1)
)

INSERT INTO HALIN VALUES
('C101', TO_DATE('20190601', 'YYYYMMDD'), TO_DATE('20190708', 'YYYYMMDD'), 0.3);
INSERT INTO HALIN VALUES
('C101', TO_DATE('20190709', 'YYYYMMDD'), TO_DATE('20190807', 'YYYYMMDD'), 0.2);
INSERT INTO HALIN VALUES
('C101', TO_DATE('20190808', 'YYYYMMDD'), TO_DATE('20190828', 'YYYYMMDD'), 0.5);
INSERT INTO HALIN VALUES
('C101', TO_DATE('20190821', 'YYYYMMDD'), TO_DATE('20190910', 'YYYYMMDD'), 0.2);
INSERT INTO HALIN VALUES
('C101', TO_DATE('20190911', 'YYYYMMDD'), TO_DATE('99991231', 'YYYYMMDD'), 0.3);

select CUSTNO,
TO_CHAR(GREATEST(STARTDT, TO_DATE('20190801', 'YYYYMMDD')), 'YYYYMMDD') STARTDT,
TO_CHAR(LEAST(ENDDT, TO_DATE('20190801', 'YYYYMMDD')),'YYYYMMDD') ENDDT,
RATIO
from HALIN
where custno = 'C101'
-- 할인 일자가 8월에 하루라도 속하는 레코드
AND STARTDT <= TO_DATE('20190831', 'YYYYMMDD')
AND ENDDT >= TO_DATE('20190801', 'YYYYMMDD')

 

데이터 복제를 통한 소계

 

SELECT B.NO
, A.DEPTNO
, A.EMPNO
, A.SAL
FROM EMP A,
SELECT (
    SELECT ROWNUM NO FROM DUAL
    CONNECT BY LEVEL <= 2
) B
ORDER BY 2;

 

이를 응용하여 개인별 급여와 부서별 급여합을 통합해 조회할 수 있습니다.

 

SELECT
A.DEPTNO DEPTNO,
DECODE(B.NO, 1 , TO_CHAR(A.EMPNO), 2, '부서계') EMPNO, 
SUM(A.SAL) T_SAL,
ROUND(AVG(A.SAL)) A_SAL
FROM EMP A,
(
  SELECT ROWNUM NO FROM DUAL
  CONNECT BY LEVEL <= 2
) B
GROUP BY A.DEPTNO, B.NO, DECODE(B.NO, 1 , TO_CHAR(A.EMPNO), 2, '부서계')
ORDER BY 1, 2;

 

ROLLUP WINDOW 함수를 활용하면 쿼리를 좀 더 단순하게 작성할 수 있습니다.

 

SELECT
DEPTNO, EMPNO,
  CASE WHEN GROUPING(DEPTNO) = 1 AND GROUPING(EMPNO) = 1 THEN '총계'
  WHEN GROUPING(EMPNO) = 1 THEN '부서계'
  ELSE TO_CHAR(EMPNO)
END 사원번호,
SUM(SAL) 급여합,
ROUND(AVG(SAL)) 급여평균
FROM EMP
GROUP BY ROLLUP(DEPTNO, EMPNO)
ORDER BY DEPTNO, DIV;

 

GROUPING SET

 

아래는 고객의 일별/월별 주문합계를 구하는 쿼리입니다.

SELECT SUBSTR(ORDER_DT 1, 8),
CUST_NO,
SUM(ORDER_PRICE)
FROM T_ORDER
WHERE ORDER_DT <= '20160601'
AND ORDER_DT < '20160701'
GROUP BY SUBSTR(ORDER_DT 1, 8), CUST_NO
UNION ALL
SELECT SUBSTR(ORDER_DT 1, 6),
CUST_NO,
SUM(ORDER_PRICE)
FROM T_ORDER
WHERE ORDER_DT <= '20160601'
AND ORDER_DT < '20160701'
GROUP BY SUBSTR(ORDER_DT 1, 6), CUST_NO

 

GROUPING SET 구문을 활용하면 UNION ALL 없이 표현할 수 있습니다.

 

SELECT 구분, DECODE(ORDER_MM, NULL, ORDER_DT, ORDER_MM) 일자
,CUST_NO, ORDER_PRICE_SUM
FROM (
    SELECT
    DECODE(TO_CHAR(ORDER_DT, 'YYYYMM'), NULL, '일별', '월별') 구분
    ,TO_CHAR(ORDER_DT, 'YYYYMMDD') ORDER_DT
    ,TO_CHAR(ORDER_DTM 'YYYYMM') ORDER_NM
    ,CUST_NO
    ,SUM(ORDER_PRICE) ORDER_PRICE_SUM
    FROM T_ORDER
    WHERE ORDER_DT BETWEEN TO_DATE('20160601', 'YYYYMMDD')
    AND TO_DATE('20160630 235959', 'YYYYMMDD HH25MISS')
    GROUP BY GROUPING SETS(
    (TO_CHAR(ORDER_DT, 'YYYYMMDD', CUST_NO))
    ,(TO_CHAR(ORDER_DT, 'YYYYMM', CUST_NO))
)
ORDER BY DECODE(구분, '일별' , 1, 2) ORDER_DT, ORDER_MM, CUST_NO

 

아래 쿼리는 연도별, 월별 입사직원 수를 구하는 쿼리입니다.

 

SELECT 'MONTHLY' DIV
,SUBSTR(TO_CHAR(HIREDATE, 'YYYYMMDD'), 1, 6) D
,COUNT(EMPNO) COUNT
FROM EMP
GROUP BY SUBSTR(TO_CHAR(HIREDATE, 'YYYYMMDD'), 1, 6)
UNION ALL
SELECT 'YEALY' DIV
,SUBSTR(TO_CHAR(HIREDATE, 'YYYYMMDD'), 1, 4) D
,COUNT(EMPNO) COUNT
FROM EMP
GROUP BY SUBSTR(TO_CHAR(HIREDATE, 'YYYYMMDD'), 1, 4)
ORDER BY D
;

 

GROUPING SETS를 활용에 UNION ALL 없이 조회가 가능합니다.

 

SELECT 
TO_CHAR(HIREDATE, 'YYYY') YEARLY
, TO_CHAR(HIREDATE, 'YYYYMM') MONTHRY
, COUNT(EMPNO) COUNT
FROM EMP
GROUP BY GROUPING SETS (
  (TO_CHAR(HIREDATE, 'YYYY'))
  ,(TO_CHAR(HIREDATE, 'YYYYMM'))
);

 

상호배타적 칼럼

 

한 칼럼에 값이 있으면 한 칼럼에는 값이 반드시 없는 상호배타적인 두 칼럼을 아크라고 합니다. 아크 칼럼이 있는 테이블의 구성방법은 외래키 분리방법, 외래키 통합방법 두 가지가 있습니다.

 

-- 외래키 분리방법
-- 온라인권
-- # 온라인권번호
-- * 발행일시
-- * 유효기간

-- 실권
-- # 실권번호
-- * 발행일시

-- 상품권 결제
-- # 온라인권번호(FK)
-- # 실권번호(FK)
-- * 결제금액
-- * 결제일자

SELECT
A.주문번호, A.결제일자, A.결제금액
, NVL(B.온라인권번호, C.실권번호) 상품권번호
, NVL(B.발행일시, C.발행일시) 발행일시
FROM 상품권결제 A, 온라인권 B, 실권 C
WHERE A.결제일자 BETWEEN :DATE1 AND :DATE2
AND A.온라인권번호 = B.온라인권번호(+)
AND A.실권번호 = C.실권번호(+)

-- 외래키 통합방법
-- 온라인권
-- # 온라인권번호
-- * 발행일시
-- * 유효기간

-- 실권
-- # 실권번호
-- * 발행일시

-- 상품권 결제
-- # 상품권번호(FK)
-- * 상품권구분코드
-- * 결제금액
-- * 결제일자


-- 상품권 결제 인덱스가 [결제일자 + 상품권구분] 일 경우 
SELECT a.주문번호, a.결제일자, a.결제금액
, nvl(b.온라인권번호, c.실권번호) 상품권번호
, nvl(b.발행일시, c.발행일시) 발행일시
FROM 상품권결제 A, 온라인권 B, 실권 C
WHERE A.결제일자 BETWEEN :DATE1 AND :DATE2
AND  DECODE(A.상품권구분, '1', A.상품권변호) = B.온라인권번호 (+)
AND  DECODE(A.상품권구분, '2', A.상품권변호) = C.실권번호 (+)

-- 상품권 결제 인덱스가 [상품권구분 + 결제일자] 일 경우 
SELECT a.주문번호, a.결제일자, a.결제금액, b.발행일시
FROM 상품권결제 A, 온라인권 B
WHERE A.상품권구분 = '1'
AND A.결제일자 BETWEEN :DATE1 AND :DATE2
AND B.온라인권번호 = A.상품권번호
UNION ALL
SELECT A.주문번호, A.결제일자, A.결제금액, B.발행일시
FROM 상품권결제 A, 실권 B
WHERE A.상품권구분 = '2'
AND A.결제일자 BETWEEN :DATE1 AND :DATE2
AND B.실권번호 = A.상품권번호

 

TOP-N 쿼리

 

정렬된 N 개의 레코드를 출력하기 위해서는 아래와 같은 방식의 TOP-N 서브쿼리를 활용해야 합니다.

 

SELECT CODE , PRODID, CUSTID, ORDERDT
FROM (
  SELECT CODE , PRODID, CUSTID, ORDERDT
  FROM T_ORDER51
  ORDER BY ORDER_DT
)
WHERE ROWNUM <= 10

-- OPERATION
-- SELECT STATEMENT
--  COUNT STOPKEY
--    VIEW
--      SORT ORDER BY STOPKEY
--        COUNT
--          TABLE ACCESS FULL OF T_ORDER51

 

SQL 실행순서

 

- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY

 

TOP-N 쿼리의 부하경감 원리 (STOPKEY)

 

출력할 ROWNUM의 수가 일정하다면 처음 읽은 N개의 레코드를 정렬한 상태로 두고 이후 레코드들에 대해서는 처음 읽은 N개의 정렬된 레코드중 가장 우측에 있는 값보다 작을때만 N개에 포함시켜 재정렬 합니다. N개의 레코드만 활용하기 때문에 대부분의 경우 IN-MEMEORY SORT가 가능합니다.

단, 서브쿼리의 PSEUDO 칼럼인 ROWNUM을 사용하지 않으면 STOPKEY는 작동하지 않습니다.

 

SELECT CODE , PRODID, CUSTID, ORDERDT
FROM (
  SELECT X.*, ROWNUM R_NUM
  FROM (
      SELECT CODE , PRODID, CUSTID, ORDERDT
      FROM T_ORDER51
      ORDER BY ORDER_DT
  )
)
WHERE R_NUM <= 10

-- OPERATION
-- SELECT STATEMENT
--    VIEW
--      SORT ORDER BY
--        COUNT
--          TABLE ACCESS FULL OF T_ORDER51

 

윈도우 함수 중 RANK() 와 ROW_NUMBER 는 사용지 TOP-N 알고리즘이 작동하지만 MAX는 작동하지 않습니다.

 

수동으로 PGA 메모리 관리방식을 변경할 시 주의사항

 

병렬 프로세스 N개를 띄우고 프로세스당 2GB의 PGA를 설정하면 2*N GB의 메모리를 차지합니다.

 

ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
ALTER SESSION SET SORT_AREA_SIZE = 2GB

SELECT /*+FULL(A) PARALLEL(A 8)*/
FROM A
ORDER BY 배송일자

 

게시판 QUERY

 

SELECT *
(
  SELECT ROWNUM RNUM
  FROM (
      SELECT
      FROM A
      ORDER BY REG_DTM DESC
  )
  WHERE ROWNUM <= 20 -- 끝
)
WHERE RNUM >= 11 -- 시작

 

최종출력건에 대해서만 조인하기

 

TOP-N 쿼리

 

select no, 주문번호, 주문일자, 주문금액, 주문고객명, 판매부서명, 판매사원명
from (
  select rownum no, 주문번호, 주문일자, 주문금액, 주문고객명, 판매부서명, 판매사원명
  from (
    select o.주문번호, o.주문일자, o.주문금액
            , c.고객명 주문고객명
            , d.부서명 판매부서명
            , e.사원명 판매사원명
    from 주문 o, 고객 c, 사원 e,  부서 d
    where o.판매부서번호 = 'D004'
    and c.고객번호 = o.주문고객번호
    and e.사원번호 = o.판매사원번호
    and d.부서번호 = o 판매부서번호
    order by o.주문일자, o.판매사원번호    
  )
  where rownum <= 41
)
where no between 31 and 40
order by no;

--- OPERATION

SELECT STATEMENT
  SORT ORDER BY
    VIEW
      COUNT STOPKEY
        VIEW
          SORT ORDER BY STOPKEY
            NESTED LOOPS
              NESTED LOOPS
                NESTED LOPS
                  TABLE ACCESS BY INDEX ROWID OF 주문
                    INDEX RANGE SCAN OF IX_주문_01
                  TABLE ACCESS BY INDEX ROWID OF 고객
                    INDEX UNIQUES SCAN OF IX_고객_01
                TABLE ACCESS BY INDEX ROWID OF 사원
                  INDEX UNIQUE SCAN OF PK_사원
              INDEX UNIQUE SCAN OF PK_부서
            TABLE ACCESS BY INDEX ROWID 부서

 

서브쿼리가 아닌 메인쿼리에서 조인하는 것으로 부하를 줄일 수 있습니다.

 

select 주문번호, 주문고객번호, 판매사원번호, 판매부서번호, 주문금액
from
(
    select rowid r_id
    from 주문 n
    where 판매부서번호 = 'D004'
) x, 주문 o
where o.rowid = x.r_id

 

인덱스 구성을 달리하여 소트를 위한 TABLE RANDOM ACCESS의 발생량을 줄일 수 있습니다.

 

select X,no, o.주문번호, o.주문일자, o.주문금액
       , c.고객명 주문고객명
       , d.부서명 판매부서명
       , e.사원명 판매사원명
from (
    select r_id, no
    from (
    -- 정렬 조건 칼럼이 인덱스에 존재하면 소트연산과 테이블랜덤 엑세스 발생을 막을 수 있습니다.
      select rowid r_id, rownum no
      from 주문 n
      where 판매부서번호 = 'D004'
      order by 판매사원번호, 주문일자
    )
    where rownum <= 41
) X, 주문 O, 고객 c, 사원 e,  부서 d
WHERE X.NO BETWEEN 31 AND 40
AND c.고객번호 = o.주문고객번호
AND e.사원번호 = o.판매사원번호
AND d.부서번호 = o 판매부서번호

 

주문 인덱스가 [판매부서번호 + 주문일자 + 판매사원번호] 라면 정렬과 테이블랜덤엑세스가 발생하지 않습니다.

 

징검다리 테이블 조인을 이용한 튜닝

 

-- T_CUST1 : PK(CUST_NO)
-- T_ORDER1 : PK(ORDER_NO), (CUST_NO)
-- T_ORDER_PROD_PK (ORDER_NO, PROD_NO)
-- T_ORDER_PROD_1 (ORDER_NO + ONDER_QTY + DEL_DT)

SELECT /*+ORDERED USE_NL(O) USE_NL(OP) INDEX(OP)*/ * 
FROM T_CUST C, T_ORDER O, T_ORDER_PROB OP
WHERE C.CUST_NO = 'C00100'
AND O.CUST_NO = C.CUST_NO
AND OP.ORDER_NO = O.ORDER_NO
AND OP.PROB_DEL_DT = '20200809'
AND OP.ORDER_QTY >= 50
AND OP.DEL_TYPE = '2'

-- SELECT STATEMENT
--  NESTED LOOPS
--    NESTED LOOPS -- 1만번의 수직적 탐색
--      NESTED LOOPS  - 1번의 수직적 탐색
--        TABLE ACCESS BY INDEX ROWID OF T_CUST51 -- A_ROW : 1
--          INDEX UNIQUE SCAN OF PK_T_CUST51 -- A_ROW : 1
--        TABLE ACCESS BY INDEX ROWID OF T_ORDER51 -- A_ROW : 10000
--          INDEX UNIQUE SCAN OF IX_T_ORDER -- A_ROW : 10000
--     INDEX RANGE SCAN OF IX_T_ORDER_PROD51_01 -- 27개의 만족하는 인덱스
--  TABLE ACCESS BY INDEX ROWID OF T_ORDER_PROD51 -- 13개의 만족하는 테이블 레코드

 

T_CUST와 T_ORDER 테이블의 조인결과가 1만건이면 T_ORDER_PROOB와 조인하는 과정에서 1만번의 테이블 렌덤엑세스와 인덱스 수직적 탐색이 발생합니다. 이 부담을 줄이기 위해 T_ORDER에 대한 필터조건이 필요하지만 없습니다.

 

-- T_CUST51 : PK(CUST_NO)
-- T_ORDER51 : PK(ORDER_NO), (CUST_NO + ORDER_NO)
-- T_ORDER_PROD51
-- PK (ORDER_NO, PROD_NO)
-- (PROD_DEL_DT + DEL_TYPE +  ONDER_QTY  + ORDER_NO )


SELECT /*+
  NO_QUERY_TRANSFORMATION
  LEADING(C O_BRG OP_BRG O OP) USE_NL(O_BRG) USE_HASH(OP_BRG) SWAP_JOIN_INPUTS(OP_BRG)
*/ ...
FROM T_CUST51 C, T_ORDER51 O_BRG, T_ORDER_PROD51 OP_BRG, T_ORDER51 O, T_ORDER_PROD51 OP

WHERE C.CUST_NO = 'C000100'
AND O_BRG.CUST_NO = C.CUST_NO
AND OP_BRG.ORDER_NO = O_BRG.ORDER_NO
AND OP_BRG.PROD_DEL_DT = '20200809'
AND OP_BRG.ORDER_QTY >= 50
AND OP_BRG.DEL_TYPE = '2'
AND O.ROWID = O_BRG.ROWID
AND OP.ROWID = OP_BRG.ROWID;

-- SELECT STATEMENT
--  NESTED LOOPS
--    NESTED LOOPS
--      HASH JOIN
--        INDEX RANGE SCAN OF IX_T_ORDER_PROD51_02
--        NESTED LOOP
--          TABLE ACCESS BY INDEX ROWID OF T_CUST51
--            INDEX UNIQUE SCAN PK__T_CUST51
--          INDEX RANGE SCAN IX_T_ORDER51
--      TABLE ACCESS BY USER_ROWID OF T_ORDER51
--   TABLE ACCESS BY USER_ROWID OF T_ORDER_PRO51

 

이력관리방법

 

점이력

 

변경일자만 있는 경우 점이력으로 조회합니다. 아래는 특정상품의 마지막 변경이력을 조회하는 쿼리입니다.

 

SELECT *
(
  SELECT /*+INDEX_DESC(X PK_상품변경이력_59)*/
  ... , ROW_NUMBER() OVER(PARTITION BY 상품ID ORDER BY 변경일자 DESC 순번 DESC) R_NUM
  FROM 상품변경이력_59 X
  WHERE 상품ID = '1'
)
WHERE R_NUM = 1;

 

아래는 특정 상품의 최종변경건을 찾는 쿼리입니다.

 

SELECT * 
FROM (
    SELECT SANGPUMID, UPDATEDT, NUMNO, STATECODE, SANGPUMPRICE
    , ROW_NUMBER() OVER (PARTITION BY SANGPUM_ID ORDER BY UPDATE_DT DESC, NUM_NO DESC)
    FROM SANGPUM_UPDATE_RECORD
    WHERE SANGPUM_ID = '1'
)
WHERE R_NUM = 1;

SELECT *
FROM (
  SELECT SANGPUMID, UPDATEDT, NUMNO, STATECODE, SANGPUMPRICE
  ,MAX(SANGPUMPRICE) OVER (PARTITION BY SANGPUMID, STATECODE) SANGPUMPRICE_MAX
  ,MIN(SANGPUMPRICE) OVER (PARTITION BY SANGPUMID, STATECODE) SANGPUMPRICE_MIN
  ,AVG(SANGPUMPRICE) OVER (PARTITION BY SANGPUMID, STATECODE) SANGPUMPRICE_AVG
  ,ROW_NUMBER() OVER (PARTITION BY SANGPUMID, STATECODE ORDER BY UPDATEDT DESC, NUMNO DESC) R_NUM
  FROM SANGPUM_UPDATE_RECORD_59
)
WHERE R_NUM = 1;

 

아래는 KEEP 함수를 활용한 예시입니다.

 

SELECT SANGPUMID, STATECODE
  ,MAX(SANGPUMPRICE)  SANGPUMPRICE_MAX
  ,MIN(SANGPUMPRICE)  SANGPUMPRICE_MIN
  ,AVG(SANGPUMPRICE)  SANGPUMPRICE_AVG
  ,MAX(UPDATEDT) UPDATEDT
  ,MAX(NUMNO) KEEP (DENSE_RANK LAST ORDER BY UPDATEDT) NUMNO_LAST
  ,MAX(SANGPUMPRICE) KEEP (DENSE_RANK LAST ORDER BY UPDATEDT, NUMNO) SANGPUMPRICE_LAST
  FROM SANGPUM_UPDATE_RECORD_59
  GROUP BY SANGPUMID, STATECODE;

 

고급SQL 활용

 

CASE문 활용

 

SELECT CUST_NO, DATE
, SUM(DECODE(CODE, 'A', AMOUNT)) SUM_A
, SUM(DECODE(CODE, 'B', AMOUNT)) SUM_B
, SUM(DECODE(CODE, 'C', AMOUNT)) SUM_C
, SUM(DECODE(CODE, 'D', AMOUNT)) SUM_D
, SUM(DECODE(CODE, 'E', AMOUNT)) SUM_E
FROM 월별납입방법
WHERE 납입월 = '200903'
GROUP BY CUST_NO, DATE

 

UNION ALL

 

FULL OUTER JOIN을 대체할 용도로 UNION ALL을 활용할 수 있습니다. FULL OUTER JOIN 시 다수의 카티션 곱이 발생하므로 한 번만 읽고 처리할 수 있도록 UNION ALL을 활용합니다.

 

select
부서번호, 년월, nvl(max(계획금액), 0) 계획금액, nvl(max(실적금액), 0) 실적금액
from (
  select 부서번호, 년월, 계획금액, to_number(null) 실적금액
  from 계획
  where 부서 = '10'
  and 년월 between '202001' and '202007'
  union all
  select 부서번호, 년월, to_number(null) 계획금액,  실적금액
  from 계획
  where 부서 = '10'
  and 년월 between '202001' and '202007'
)
group by 부서번호, 년월;

 

WITH 구문 활용

 

WITH T_DEPT AS (SELECT DEPTNO, DNAME, LOC FROM DEPT)
SELECT E.EMPNO, ENAME, D.DNAME
FROM EMP E, T_DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND DEPT IN (
SELECT DEPTNO
FROM T_DEPT
WHERE LOC = 'NEW YORK'
)

 

동일한 테이블을 여러번 읽는 쿼리는 성능상 좋지 않습니다. WITH 구문은 두가지 동작방식이 있습니다.

 

- MATERIALIZE : 내부적으로 임시 테이블을 생성하여 반복적으로 재사용
- INLINE : 참조된 횟수만큼 런타임 시 반복 수행

 

소트튜닝

 

소트와 성능

 

정렬 대상 데이터의 정렬작업을 메모리 공간내에서 완료하는 것을 메모리 소트, 메모리를 넘어 디스크 공간까지 사용하는 것을 디스크 소트라 합니다.

 

소트를 발생시키는 오퍼레이션

 

SORT AGGREGATE

 

전체 레코드를 대상으로 집계를 수행할 때 발생하는 오퍼레이션 입니다. 실제 소트가 발생하지는 않습니다.

 

SELECT MAX(SAL), MIN(SAL), SUM(SAL) FROM EMP;

-- SELECT STATEMENT
--  SORT AGGREGATE
--    TABLE ACCESS FULL OF EMP

-- STATISTICS
-- 0 SORTS (MEMORY)
-- 0 SORTS (DISK)

 

SORT ORDER BY

 

ORDER BY 로 레코드를 정렬할때 발생하는 오퍼레이션입니다. 실제 소트연산이 발생합니다.

 

SELECT * FROM EMP ORDER BY SAL;;

-- SELECT STATEMENT
--  SORT ORDER BY
--    TABLE ACCESS FULL OF EMP

-- STATISTICS
-- 1 SORTS (MEMORY)
-- 0 SORTS (DISK)

 

SORT GROUP BY

 

GROUP BY 명령어를 통해 그룹별 집계를 수행할때 발생합니다.

 

SELECT DEPTNO, SUM(SAL), MAX(SAL)
FROM SCOTT.EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

-- SELECT STATEMENT
--  SORT GROUP BY
--    TABLE ACCESS FULL OF EMP

-- STATISTICS
-- 1 SORTS (MEMORY)
-- 0 SORTS (DISK)

 

새로 도입된 HASH GROUP BY의 경우 결과값이 정렬되지 않기 때문에 추가적인 ORDER BY 가 필요합니다.

 

SORT UNIQUE(HASH UNIQUE)

 

UNNESTING 된 서브쿼리가 M쪽 집합이거나, 세미 조인으로 수행되지 않으면 메인쿼리에 조인되기 전에 레코드 제거를 위해 SORT UNIQUE 오퍼레이션이 먼저 수행됩니다.

 

SELECT *
FROM DEPT
WHERE DEPTNO IN (
    SELECT /*+unnesting*/
    DEPTNO 
    FROM EMP
    WHERE SAL >= 3000
)

-- SELECT STATEMENT
--  MERGE JOIN SEMI
--    TABLE ACCESS BY INDEX ROWID OF DEPT
--      INDEX FULL SCAN OF PK_DPET
--    SORT UNIQUE
--      TABLE ACCESS FULL OF EMP

-- STATISTICS
-- 1 SORTS (MEMORY)
-- 0 SORTS (DISK)

 

SORT JOIN

 

소트머지 조인을 수행할 때 발생

 

SELECT
*
FROM DEPT D, EMP E
WHERE D.DEPTNO = E.DEPTNO

-- SELECT STATEMENT
--    MERGE JOIN
--        TABLE ACCESS BY INDEX ROWID OF DEPT
--            INDEX FULL SCAN OF PK_DEPT
--        TABLE ACCESS BY INDEX ROWID OF EMP
--            INDEX FULL SCAN OF PK_EMP

 

WINDOW SORT

 

윈도우 함수를 사용하면 WINDOW SORT 정렬을 수행합니다.

 

SELECT EMPNO, ENAME, COUNT(*) OVER (PARTITION BY DEPTNO) CNT
FROM EMP

-- SELECT STATEMENT
--     WINDOW SORT
--        TABLE ACCESS FULL OF EMP
-- STATISTICS
-- 1 SORTS (MEMORY)
-- 0 SORTS (DISK)

 

소트 발생하지 않도록 하는 SQL 작성

 

UNION을 UNION ALL로 대체 / DISTINCT를 EXISTS로 대체

 

SELECT DISTINCT M.MCODE, M.M_NM
FROM T_ORDER O, T_PRODUCT P, T_MANUF M
WHERE O.ORDER_DT >= '20090101'
AND P.PROD_ID = O.PROD_ID
AMD M.M_CODE = P.M_CODE

-- SELECT STATEMENT
--     HASH UNIQUE
--         NESTED LOOPS
--             NESTED LOOPS
--                 VIEW
--                     HASH UNIQUE -- 소트오퍼레이션 발생
--                        INDEX FAST FULL SCAN OF IDX_ORDER_01
--                     TABLE ACCESS BY INDEX ROWID OF T_PRODUCT
--                        INDEX UNIQUE SCAN OF PK_T_PRODUCT
--                 INDEX UNIQUE SCAN OF PK_T_MANUF
--             TABLE ACCESS BY INDEX ROWID OF T_MANUF

 

EXISTS 로 쿼리를 변환하면 NESTED LOOP SEMI로 동작하므로 한 번 성공하면 다음 조인은 진행하지 않습니다.

 

SELECT M.M_CODE, M.M_NM
FROM T_MANUF M
WHERE EXISTS
(
    SELECT 1
    FROM T_PRODCT P
    WHERE M_CODE = M.M_CODE
    AND EXISTS(
        SELECT 1
        FROM T_ORDER
        WHERE ORDER_DT >= '20090101'
        AND PROD_ID = P.PROD_ID
    )
)
-- SELECT STATEMENT
--  NESTED LOOPS SEMI
--     TABLE ACCESS FULL OF T_MANUF53
--     VIEW PUSHED PREDICATE
--      NESTED LOOPS SEMI
--        INDEX RANGE SCAN OF IX_PRODUCT53_01
--        INDEX RANGE SCAN OF IX_ORDER53_01

 

불필요한 COUNT 연산 제거

 

특정레코드 유무를 체크하기 위한 서브쿼리는 rownum 조건을 통해 더이상 읽지 않도록 합니다.

 

declare
  v_cnt number
begin
  -- 고객등급이 vip 인 사람이 '존재'하는지 확인
  select count(*) into v_cnt
  from (
    select 고객번호
    from 고객
    where 고객등급 = 'vip'
    -- 1건만 읽어오도록 한다.
    and rownum <= 1
  ):

 

인덱스를 이용한 소트연산 대체

 

SORT ORDER BY

인덱스에 정렬 기준 칼럼을 추가하면 별도의 SORT OPERATION이 발생하지 않습니다.

 

SORT AREA를 적게 사용하는 SQL 작성

 

소트를 완료하고 데이터 가공

 

select  lpad(카드번호) || lpad(고객번호, 30)
from (
  select 카드번호, 고객번호
  from 카드
  where 발급일자 between :1 and :2
  order by 배송일자;
)

 

TOP_N 쿼리를 사용하는 분석함수

 

WINDOW SORT 중 RANK 및 ROW_NUMBER 사용시 TOP-N 알고리즘이 적용되어 max 함수보다 부하가 경감됩니다.

옵티마이저

 

오브젝트 통계정보를 이용해 실행계획의 예상비용을 산정합니다. 옵티마이저는 크게 규칙기반 옵티마이저, 비용기반 옵티마이저 두 가지 종류가 있습니다.

 

규칙기반 옵티마이저는 미리 정해 놓은 규칙에 따라 액세스 경로를 평가하고 실행계획을 선택합니다. 경로별 우선순위로 인덱스 구조, 연산자, 조건절 형태가 순위를 결정짓습니다.

비용기반 옵티마이저는 쿼리를 수행하는데 소요되는 일량 또는 시간을 비용으로 산정합니다. 비용은 테이블, 인덱스에 대한 통계정보에 기초하여 산정합니다.

 

최적화 과정

 

PARSER가 SYNTAX, SEMANTIC 을 체크하고 파싱된 SQL을 표준형태로 변환합니다. 실행계획 별로 실제 실행할 수 있는 코드형태로 포맷팅합니다.

 

최적화 목표

 

최종 결과집합을 끝까지 읽는것을 전제로 하는 전체처리속도 최적화와 전체 결과집합 중 일부만 읽다가 멈추는 것을 전제로 하는 최초 응답속도 최적화(FIRST_ROWS) 가 있습니다.

 

alter system set optimizer_mode = all_rows;
alter system set optimizer_mode = first_rows;

select /*+all_rows*/ * from t where ... ;

 

옵티마이저는 바인드 변수 사용시 균등분포를 가정하기 때문에 한 데이터가 분포의 큰 부분을 차지하더라도 이를 고려할 수 없습니다.

 

선택도

1 / DISTINCT VALUE 수

카디널리티

특정 엑세스 단계를 거치고 난 후 출력될 것으로 예상되는 결과 건수로 총 로우 수 / DISTINCT VALUE 입니다

히스토그램

대표적인 히스토그램에는 도수분포 와 높이균형 히스토그램이 있습니다. 도수분포 히스토그램은 DISTINCT VALUE의 수가 적을 때 사용하는데 최대 254개의 HASH BUCKET 별 수를 표현합니다. 높이균형 히스토그램은 DISTINCT VALUE의 수가 가 254개를 넘을때 사용하는데 하나의 버킷이 여러개의 값을 담당합니다. 버킷의 수가 많을 수록 너비가 길어집니다.

 

인덱스를 경유하는 테이블 엑세스 비용

 

비용 = Blevel(수직적 탐색 비용) + (리프 브록 수 * 유효 인덱스 선택도) + (클러스터링 팩터 * 유효 테이블 선택도)

쿼리변환

옵티마이저가 사용자의 쿼리를 변환하는 것을 말합니다. 대표적인 쿼리변환은 다음과 같습니다.

 

SUBQUERY UNNESTING

서브쿼리를 풀어 메인 쿼리와 동등한 계층에서 조인합니다.

 

SELECT EMPNO, ENAME, SAL, COMM, DEPTNO
FROM EMP
WHERE DEPTNO IN(
  SELECT /*+NO_UNNEST*/ DEPTNO FROM DEPT
);

-- SELECT STATMENT
--   FILTER
--      TABLE ACCESS FULL OF EMP
--        INDEX UNIQUE SCAN PK_DPET

 

 

FILTER OPERATION은 메인쿼리의 레코드를 하나씩 서브쿼리에 전달합니다. SUBQUERY UNNESTING 되면 쿼리는 다음과 같이 변환됩니다.

 

-- 서브쿼리 UNNESTING
SELECT EMPNO, ENAME, SAL, COMM, D.DEPTNO
FROM SCOTT.EMP E,
(
  SELECT  DEPTNO FROM SCOTT.DEPT
) D
WHERE D.deptno = E.deptno;

-- SELECT STATEMENT
--  NESTED LOOPS -- SUBQUERY UNNESTING
--    TABLE ACCESS FULL EMP
--    INDEX UNIQUE SCAN PK_DEPTNO

 

SUB QUERY UNNESTING 과 관련된 힌트는 다음과 같습니다.

 

- unnest : 서브쿼리를 Unnesing 하여 조인 방식으로 유도
- no_unnest : 원본 쿼리 변경 없이, filter 방식으로 유도

 

서브쿼리가 M 쪽 집합이거나 NON-UNIQUE 인덱스인 경우

 

select *
from emp
where deptno in (
  select deptno from dept
)

 

위 쿼리처럼 일반적인 서브쿼리는 레코드간 중복이 없는 1쪽 집합입니다. 위 쿼리에 사용된 DEPT 테이블의 경우 DEPTNO가 PK입니다.
결과집합은 항상 M쪽 집합인 EMP 테이블 수준으로 출력됩니다.

 

select *
from dept
where deptno in(select deptno from emp)

-- subquery unnesting

select * from
(select deptno from emp) a, dept b
where a.deptno = b.deptno

 

위 쿼리는 반대의 경우로 SUB_QUERY가 M쪽 집합입니다. 이와 유사한 경우로 SUB_QUERY에 pK 가 없다면 옵티마이저가 SUBQUERY가 1쪽 집합임을 확신하지 못하기 때문에 결과집합은 서브쿼리 M쪽 수준으로 출력됩니다. 이로인해 결과값이 달라집니다.

 

옵티마이저는 1쪽 집합임을 확신할 수 없는 테이블이 드라이빙 되면 SORT UNIQUE 오퍼레이션을 수행하여 1쪽집합으로 만들고 조인합니다.

 

-- SELECT STATEMENT
--    NESTED LOOPS
--      NESTED LOOPS
--        SORT UNIQUE
--          INDEX FULL SCAN OF IX_EMP_01
--        INDEX UNIQUE SCAN OF PK_DEPT
--    TABLE ACCESS BY INDEX ROWID OF DEPT

 

메인쿼리 쪽 테이블이 드라이빙 되면 1쪽 집합임을 확신할 수 없는 서브쿼리에 대해 세미조인방식으로 조인합니다.

 

-- SELECT STATEMENT
--    NESTED LOOPS
--      TABLE ACCESS FULL
--      INDEX RANGE SCAN OF IX_EMP_01

 

PUSH_SUBQ

 

서브쿼리 필터링을 가능한 한 앞 단계에서 처리하도록 강제하는 기능입니다.

 

SELECT C.고객번호, C.고객명. C.고객선향코드
FROM 고객 C
WHERE C.고객성향코드 = 'C020'
AND EXISTS (
  SELECT /*+*NO_UNNEST PUSH_SUBQ*/ 1
  FROM 주문 O
  WHERE 주문고객번호 = C.고객번호
  AND   주문유형코드 = '010'
  AND   주문일자 IS NULL
)
-- INDEX 고객(고객성향코드 + 고객번호), 주문(고객번호 + 주문유형코드 + 주문일자)
--  B TABLE ACCESS
-- SELECT STATEMENT
--  TABLE ACCESS BY INDEX ROWID OF 고객  -- 3
--    INDEX RANGE SCAN OF IX_고객  -- 1
--      INDEX RANGE SCAN OF IX_주문  -- 2

 

NO_PUSH_SUBQ

 

전통적인 FILTER 방식으로 쿼링합니다.

 

-- 인덱스 : 고객(고객성향코드 + 고객번호), 주문(고객번호 + 주문유형코드 + 주문일자)
SELECT C.고객번호, C.고객명. C.고객선향코드, H.C11
FROM 고객 C
, 고객취미 H
where C.고객성향코드 = 'C020'
AND H.고객번호 = C.고객번호
AND EXISTS (
  SELECT /*+*NO_UNNEST NO_PUSH_SUBQ*/ 1
  FROM 주문
  WHERE 주문고객번호 = C.고객번호
  AND   주문유형코드 = '010'
  AND   주문일자 IS NULL
)

-- SELECT STATEMENT
--  FILTER
--    NESTED LOOPS  -- 고객취미데이터와 조인하고
--      TABLE ACCESS BY INDEX ROWID OF 고객 -- 고객 데이터 읽고
--        INDEX RANGE SCAN OF IDX_고객_01
--      TABLE ACCESS BY INDEX ROWID OF 고객취미
--        INDEX RANGE SCAN OF IDX_고객취미
--   INDEX RANGE SCAN OF IX_주문_02 -- 서브쿼리에 한 행씩 전달

 

PUSH_SUBQ 방식으로 동작하면 실행계획이 다음과 같이 변환됩니다.

 

-- SELECT STATEMENT
--    NESTED LOOPS
--      TABLE ACCESS BY INDEX ROWID
--        INDEX RANGE SCAN OF IDX_고객
--          INDEX RANGE SCAN OF IDX_주문
--      TABLE ACCESS BY INDEX ROWID OF 고객취미
--        INDEX RANGE SCAN OF 고객취미

 

서브쿼리는 pinning 을 못써서 Filter 방식보다 비효율적입니다.

 

뷰 MERGING

 

SELECT *
FROM (SELECT * FROM EMP WHERE JOB = 'SALESMAN') A, (SELECT * FROM DEPT WHER LOC = 'CHICAGO') B
WHERE A.DEPTNO = B.DEPTNO;

 

위 쿼리를 머징하면 다음과 같이 동작합니다.

 

-- 뷰 MERGING
SELECT *
FROM EMP A, DEPT B
WHERE A.JOB = 'SALESMAN'
AND B.LOC = 'CHICAGO'
AND A.DPETNO = B.DEPTNO

CREATE OR REPLACE VIEW VW_DEPT AS
SELECT DEPTNO, DNAME, LOC
FROM DEPT
WHERE LOC = "NEW YORK"

SELECT /* NO_QUERY_TRANSEFORMATION */ *
FROM SCOTT.EMP E, VW_DEPT D
WHERE E.DEPT_NO = D.DEPT_NO

-- SELECT STATEMENT
--  NESTED LOOPS
--    NESTED LOOPS
--      VIEW OF VW_DEPT
--        TABLE ACCESS OF DEPT
--      INDEX RANCE SCAN OF IX_EMP_01
--  TABLE ACCESS OF EMP

 

위 쿼리를 뷰머징하면 다음과 같이 동작합니다.

 

SELECT *
FROM EMP E, VW_DEPT D
WHERE E.DEPT_NO = D.DEPT_NO

-- SELECT STATEMENT
--  NESTED LOOPS
--    NESTED LOOPS
--      TABLE ACCESS FULL OF DEPT
--      INDEX RANCE SCAN OF IX_EMP_01
--  TABLE ACCESS BY INDEX ROWID OF EMP

 

단순한 뷰는 MERGING 해도 성능이 나빠지지 않지만 복잡한 연산을 포함하는 뷰 MERGING은 성능 저하될 수 있습니다.

 

뷰 MERGING이 불가능한 경우

 

- 집합 연산자(union, union all, intersect, minus)
- connect by 절
- ROWNUM PSEUDO 컬럼
- SELECT-LIST 집계함수(avg, count, max, min, sum)
- 분석함수