HighLight in your life~

1. 소트 튜닝 방안

 1) 데이터 모델 측면에서의 검토

 2) 소트가 발생하지 않도록 SQL 작성

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

 4) 소트 영역을 적게 사용하도록 SQL 작성

 5) 소트 영역 크기 조정

2. 메모리 소트와 디스크 소트

- SQL 수행 도중 Sort 오퍼레이션으 필요할 때마다 DBMS는 정해진 메모리 공간에 Sort Area를 할당하고 정렬을 수행한다. Oracle은 Sort Area를 PGA(Private Global Area) 영역에 할당하고, SQL Server는 버퍼 캐시에 할당한다.

- 소트 오퍼레이션 수행 중에 메모리 공간이 부족할 때는 디스크 공간을 사용한다. Oracle에서는 Temp Tablespace를 이용하고, SQL Server에서는 tempdb를 이용한다.

- 대량의 데이터를 정렬할 땐 디스크 소트가 불가피하다.

- 전체 대상 집합을 디스크에 기록했다가 다시 읽는 작업을 여러 번 반복하는 경우 SQL 수행 성능은 극도로 나빠진다.

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

 1) Sort Aggregate - 전체 로우를 대상으로 집계를 수행할 때

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

 2) Sort Order by - 정렬된 결과집합을 얻고자 할 때

  SQL> SELECT * FROM EMP ORDER BY SAL DESC;

 3) Sort Group by - Sorting 알고리즘을 사용해 그룹별 집계를 수행할 때

  SQL> SELECT DEPTNO, JOB, SUM(SAL), MAX(SAL), MIN(SAL)

FROM EMP

GROUP BY DEPTNO, JOB

;

 4) Sort Unique - 선택된 결과집합에서 중복 레코드를 제거하고자 할 때(Union 연산자, Distinct 연산자)

  SQL> SELECT DISTINCT DEPTNO

FROM EMP

ORDER BY DEPTNO

;

 5) Sort Join - Sort Merge Join을 수행할 때

  SQL> SELECT /*+ ordered use_merge(e) */

FROM EMP E, DEPT D

WHERE D.DEPTNO = E.DEPTNO

;

 6) Window Sort - 윈도우 함수를 수행할 때

  SQL> SELECT EMPNO, ENAME, JOB, MGR, SAL, ROW_NUMBER() OVER (ORDER BY HIREDATE)

FROM EMP

;

4. 소트 오퍼레이션은 메모리 집약적(Memory-intensive) 일뿐만 아니라 CPU 집약적(CPU-intensive)이기도 하며, 데이터량이 많을 때는 디스크 I/O까지 발생시키므로 쿼리 성능을 크게 떨어뜨린다. 특히, 부분범위처리를 할 수 없게 만들어 OLTP 환경에서 성능을 떨어뜨리는 주요인이 되곤 한다. 될 수 있으면 소트가 발생하지 않도록 SQL을 작성해야 하고, 소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 한다.

목적: 왼쪽의 저장된 테이블 값을 기준으로 '원하는 SELECT 결과 값'을 출력하는 것이다. 

1차 단계
SELECT 일련번호, 측정값
     ,(SELECT MAX(상태코드)
       FROM 장비측정
       WHERE 일련번호 <= O.일련번호
       AND 상태코드 IN NOT NULL) 상태코드
FROM 장비측정 O
ORDER BY 일련번호
;

2차 단계
SELECT 일련번호, 측정값

     , LAST_VALUE(상태코드 IGNORE NULLS)

OVER(ORDER BY 일련번호 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

상태코드

FROM 장비측정

ORDER BY 일련번호

;

페이징 처리: 조회할 데이터가 일정량 이상이고 수행빈도가 높다면 반드시 페이징 처리를 해야 한다.

1) 1차 단계

-- <첫 화면이거나, '다음(-->)' 버튼을 클릭했을 때>

SELECT TOP 11 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래 A

WHERE :페이지이동 = 'NEXT'

AND 종목코드 = :isu_cd

AND 거래일시 >= :trd_time

ORDER BY 거래일시

)

;

-- <'이전(<--)' 버튼을 클릭했을 때>

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM (

SELECT TOP 11 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래 A

WHERE :페이지이동 = 'PREV'

AND 종목코드 = :isu_cd

      AND 거래일시 <= :trd_time

ORDER BY 거래일시 DESC

)

ORDER BY 거래일시

;

 
2) 2차 단계 - Union All을 활용하면 사용자가 어떤 버튼(조회, 다음, 이전)을 눌렀는지에 따라 하나의 SQL로 처리가 가능하다.

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM (

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래 A

WHERE :페이지이동 = 'NEXT'  -- 첫 페이지 출력 시에도 'NEXT' 입력

AND 종목코드 = :isu_cd

AND 거래일시 >= :trd_time

ORDER BY 거래일시

)

WHERE ROWNUM <= 11

UNION ALL

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM (

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래 A

WHERE :페이지이동 = 'PREV'

AND 종목코드 = :isu_cd

AND 거래일시 <= :trd_time

ORDER BY 거래일시 DESC

)

WHERE ROWNUM <= 11

ORDER BY 거래일시

;

방법- M:M 관계의 조인을 해결하거나 Full Outer Join을 대체하는 용도로 Union All을 활욜할 수 있다.

목적 - 부서별판매계획과 채널별판매실적 테이블이 있다. 이 두 테이블을 이용해 월별로 각 상품의 계획 대비 판매 실적을 집계하려고 한다.

1) 1차 단계 - DBMS와 버전에 따라 Full Outer Join을 비효율적으로 처리하기도 한다. 한 테이블을 두 번씩 액세스하기도 한다.

SELECT NVL(A.상품, B.상품) AS 상품

, NVL(A.계획연월, B.판매연월) AS 연월

, NVL(계획수량, 0) 계획수량

, NVL(판매수량, 0) 판매수량

FROM

(SELECT 상품, 계획연월, SUM(계획수량) 계획수량

FROM 부서별판매계획

WHERE 계획연월 BETWEEN '201201' AND '201203'

GROUP BY 상품, 계획연월

) A

FULL OUTER JOIN

(SELECT 상품, 판매연월, SUM(판매수량)

FROM 채널별판매실적

WHERE 판매연월 BETWEEN '201201' AND '201203'

GROUP BY 상품, 판매연월

) B

ON A.상품 = B.상품

AND A.계획연월 = B.판매연월

;

2) 2차 단계 - 조금 더 효과적인 방법이 될 수 있다.

                 - Union All을 이용하면 M:M 관계의 조인이나 Full Outer Join을 쉽게 해결할 수 있다.

                 - SQL Server에선 NVL 대신 ISNULL 함수를 사용하고, TO_NUMBER 대신 CAST 함수를 사용하기 바란다.

SELECT '계획' AS 구분, 상품, 계획연월 AS 연월, 판매부서, NULL AS 판매채널

, 계획수량, TO_NUMBER(NULL) AS 실적수량

FROM 부서별판매계획

WHERE 계획연월 BETWEEN '201201' AND '201203'

UNION ALL

SELECT '실적', 상품, 판매연월 AS 연월, NULL AS 판매부서, 판매채널

, TO_NUMBER(NULL) AS 계획수량, 판매수량

FROM 채널판매실적

WHERE 판매연월 BETWEEN '201201' AND '201203'

;

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

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

-- 위의 전체 집합을 상품, 연월 기준으로 GROUP BY하면서 계획수량과 실적수량을 집계해 보자.

-- 아래와 같이 월별 판매계획과 실적을 대비해 보져줄 수 있다.

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

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

SELECT 상품, 연월, NVL(SUM(계획수량), 0) AS 계획수량, NVL(SUM(실적수량), 0) AS 실적수량

FROM (

) A

GROUP BY 상품, 연월

;

목적: '월별납입방법별집계' 테이블을 읽어 월요금납부실적 테이블을 가공하려 한다.

1차 단계 - '월별납입방법별집계' 테이블에서 동일 레코드를 반복 액세스 한다.

INSERT INTO 월별요금납부실적

(고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷)

SELECT K.고객번호, '201203' 납입월

, A.납입금액 지로

, B.납입금액 자동이체

, C.납입금액 신용카드

, D.납입금액 핸드폰

, E.납입금액 인터넷

 FROM 고객 K

, (SELECT 고객번호, 납입금액

FROM 월별납입방법별집계

WHERE 납입월 = '201203'

AND 납입방법코드 ='A') A

, (SELECT 고객번호, 납입금액

FROM 월별납입방법별집계

WHERE 납입월 = '201203'

AND 납입방법코드 ='B') B

, (SELECT 고객번호, 납입금액

FROM 월별납입방법별집계

WHERE 납입월 = '201203'

AND 납입방법코드 ='C') C

, (SELECT 고객번호, 납입금액

FROM 월별납입방법별집계

WHERE 납입월 = '201203'

AND 납입방법코드 ='D') D

, (SELECT 고객번호, 납입금액

FROM 월별납입방법별집계

WHERE 납입월 = '201203'

AND 납입방법코드 ='E') E

WHERE A.고객번호(+) = K.고객번호

AND B.고객번호(+) = K.고객번호

AND C.고객번호(+) = K.고객번호

AND D.고객번호(+) = K.고객번호

AND E.고객번호(+) = K.고객번호

AND NVL(A.납입금액, 0) + NVL(B.납입금액, 0) + NVL(C.납입금액, 0)

     + NVL(D.납입금액, 0) + NVL(E.납입금액, 0) > 0

;

*****

2차 단계 - I/O 효율을 고려하여 One-SQL로 작성한다.

             - One-SQL을 구현하는데 있어서 CASE문이나 DECODE 함수를 활용하는 기법은 IFELSE 같은 분기조건을 포함한 복잡한 처리절차를 단순화 시키기 위해 꼭 필요하다.

             - SQL Server에서는 2005 버전 부터 Pivot 구문을 지원하고, Oracle도 11g부터 지원하기 시작했으로 Pivot을 사용하면 된다.

INSERT INTO 월별요금납부실적

 (고객번호, 납입월, 지로, 자동이체, 신용카드, 핸드폰, 인터넷)

SELECT 고객번호, 납입월

, NVL(SUM(CASE WHEN 납입방법코드 = 'A' THEN 납입금액 END), 0) 지로

1. 목적: 주식시장에서 과거 20년 동안 당일 최고가로 장을 마친(종가=최고가) 종목을 조회하는 쿼리다.

        일별종목거래및시세 테이블로부터 시가, 종가, 거래 데이터를 읽고 그 당시 종목명과 상장주식수는 종목이력으로부터 가져오는데, 조인 연산자 '='이 아니라 between이라는 점이 특징적이다.

SELECT a.거래일자, a.종목코드, b.종목한글명, b.종목영문명, b.상장주식수

             , a.시가, a.종가, a.체결건수, a.체결수량, a.거래대금

FROM 일별종목거래및시세 a, 종목이력 b

WHERE a.거래일자 BETWEEN TO_CHAR(ADD_MONTHS(SYSDATE, -20*12), 'YYYYMMDD')

AND TO_CHAR(SYSDATE-1, 'YYYYMMDD')

AND a.종가 = a.최고가

AND b.종목코드 =  a.종목코드

AND a.거래일자 BETWEEN b.시작일자 AND b.종료일자

-- Star of Comment

-- 거래 시점이 아니라 현재(최종) 시점의 종목명과 상장주식수를 출력하려면 BETWEEN 조인 대신 상수조건으--- 로 입력해야 한다.

-- AND TO_CHAR(SYSDATE, 'YYYYMMDD') BETWEEN b.시작일자 AND b.종료일자

-- End of Comment

;

목적: '=' 연산자가 아닌 BETWEEN, LIKE, 부등호 같은 연산자로 조인해야 할 때도 있다.

*월별지점매출 테이블이 있다고 하자. 이 테이블을 이용해 각 지점별로 판매월과 함게 증가하는 누적매출(RUNNING TOTAL)을 구하려 한다.

1. 윈도우 함수(오라클에서는 분석함수(Analytic Function)라고 함)를 이용하여 아래와 같이 간단히 원하는 결과를 얻을 수 있다.

  SELECT 지점, 판매월, 매출

, SUM(매출) OVER (PARTITION BY 지점 ORDER BY 판매월

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 누적 매출

  FROM 월별지점매출

  ;

2. 만약 윈도우 함수가 지원되지 않는 DBMS를 사용하고 있다면 아래와 같이 부등호 조인을 이용해 같은 결과를 얻을 수 있다.

  SELECT T1.지점, T1.판매월, MIN(T1.매출) 매출, SUM(T2.매출) 누적매출

  FROM 월별지점매출 T1, 월별지점매출 T2

  WHERE T2.지점 = T1.지점

  AND T2.판매월 <= T1.판매월

  GROUP BY T1.지점, T1.판매월

  ORDER BY T1.지점, T1.판매월

  ;

목적: 'CHICAGO'인 부서(dept)만 대상으로 급여 수준을 집계

1차 단계
 (가) 사원(emp) 테이블 전체를 다 읽어야 하는 비효율
  - 아래의 두 쿼리는 동일한 쿼리다.

 SELECT D.DEPTNO, D.DNAME, AVG_SAL, MIN_SAL, MAX_SAL
 FROM DEPT D RIGHT OUTER JOIN
             (SELECT DEPTNO, AVG(SAL) AVG_SAL, MIN(SAL) MIN_SAL, MAX(SAL) MAX_SAL
             FROM EMP GROUP BY DEPTNO) E
 ON E.DEPTNO = D.DEPTNO
 WHERE D.LOC = 'CHICAGO'
 ;

 SELECT D.DEPTNO, D.DNAME, AVG_SAL, MIN_SAL, MAX_SAL
 FROM DEPT D 
     ,(SELECT DEPTNO, AVG(SAL) AVG_SAL, MIN(SAL) MIN_SAL, MAX(SAL) MAX_SAL
             FROM EMP GROUP BY DEPTNO) E
 WHERE E.DEPTNO = D.DEPTNO(+)
 AND D.LOC = 'CHICAGO'
 ;

 (나) 스칼라 서브쿼리는 한 레코드당 하나의 값만 리턴한다.

  - 아래와 같이 작성이 불가하다.

  SELECT D.DEPTNO, D.DNAME

              , (SELECT AVG(SAL), MIN(SAL), MAX(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO)

  FROM DEPT D

  WHERE D.LOC = 'CHICAGO'

  ;

 (다) EMP에서 같은 범위를 반복적으로 액세스하는 비효율이 생긴다.

  SELECT D.DEPTNO, D.DNAME

              , (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) AVG_SAL

              , (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) MIN_SAL

, (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = D.DEPTNO) MAX_SAL

  FROM DEPT D

  WHERE D.LCO = 'CHICAGO'

  ;

2차 단계 - 구하고자 하는 값들을 모두 결합하고서 바깥쪽 액세스 쿼리에서 SUBSTR 함수로 분리

SELECT DEPTNO, DNAME

, TO_NUMBER(SUBSTR(SAL, 1, 7)) AVG_SAL

, TO_NUMBER(SUBSTR(SAL, 8, 7)) MIN_SAL

, TO_NUMBER(SUBSTR(SAL, 15)) MAX_SAL

FROM (

  SELECT D.DEPTNO, D.DNAME

               , (SELECT LPAD(AVG(SAL), 7) || LPAD(MIN(SAL), 7) || MAX(SAL)

FROM EMP WHERE DEPTNO = D.DEPTNO) SAL

  FROM DEPT D

  WHERE D.LOC = 'CHICAGO'

  ;

*Sort Merge Join의 특징 - 2단계로 진행

 (1)소트 단계: 양쪽 집합을 조인 칼럼 기준으로 정렬

 (2)머지 단계: 정렬된 양쪽 집합을 서로 Merge

1. 조인 하기 전에 양쪽 집합을 정렬한다.

  ---> 양쪽 집합을 조인 칼럼 기준으로 정렬한 후에 조인을 시작

2. 부분적으로, 부분범위처리가 가능하다.

  ---> Outer 집합이 조인 칼럼 순으로 미리 정렬된 상태에서 사용자가 일부 로우만 Fetch 하다가 멈춘다면 Outer 집합은 끝까지 읽지 않아도 된다.

3. 테이블별 검색 조건에 의해 전체 일량이 좌우된다.

  ---> 두 집합을 각각 정렬한 후에 조인함으로써 각 집합의 크기, 즉 테이블별 검색 조건에 의해 전체 일량이 좌우된다.

4. 스캔 위주의 조인 방식이다.

  ---> 각 테이블 검색 조건에 해당하는 대상 집합을 찾을 때 인덱스를 이용한 Random 액세스 방식을 처리될 수도 있다. 이때 발생하는 Random 액세스량이 많다면 Sort Merge Join의 이점이 사라질 수 있다.

<예제>

SELECT /*+ ordered use_merge(e) */ D.DEPTNO, D.DNAME, E.EMPNO, E.ENAME

FROM DEPT D, EMP E

WHERE D.DEPTNO = E.DEPTNO

;

*NL Joing의 특징(Nested Loop Join) 

 - 소량의 데이터를 주로 처리하거나 부분범위처리가 가능한 OLTP 환경에 적합한 조인 방식

1. Random 액세스 위주의 조인 방식

   ---> 인덱스 구성이 아무리 완벽하더라도 대량의 데이터를 조인할 때 매우 비효율적이다.

2. 조인을 한 레코드씩 순차적으로 진행

   ---> 부분범위처리가 가능한 상황에서 대용량 집항에서 매우 극적인 응답 속도를 낼 수 있다.

   ---> 순차적으로 진행하는 특징 때문에 먼저 액세스되는 테이블의 처리 범위에 의해 전체 일량이 결정된다.

3. 인덱스 구성 전략 중요

   ---> 조인 칼럼에 대한 인덱스가 존재하느냐 아니냐, 있다면 어떻게 구성됐느냐에 따라 조인 효율이 크게 달라진다.

<예제>

SELECT /*+ ordered use_nl(d) */ E.EMPNO, E.ENAME, D.DNAME

FROM EMP E, DEPT D

WHERE D.DEPTNO = E.DEPTNO

;

SELECT /*+ leading(e) use_nl(d) */ E.EMPNO, E.ENAME, D.DNAME

FROM DEPT D, EMP E

WHERE D.DEPTNO = E.DEPTNO

;