SQLP 3-5-1 One SQL
in Devlopment on SQLP
3과목 SQL 고급 활용 및 Tuning
5장 고급 SQL Tuning
5.1 One SQL 구현 기법
5.1.1 CASE(DECODE) 활용
One SQL 기법중 가장 기본인 CASE를 이용하여 record로 나눠져 있는 데이터를 한 row에 표현하는 방법이다.
월별납입방법별집계
# 고객번호
# 납입월
# 납입방법코드
* 납입금액
위 Table을 읽어서 아래 형식으로 가공하고자 할 경우
월별요금납부실적
# 고객번호
# 납입월
* 지로 금액
* 자동이체 금액
* 신용카드 금액
* 핸드폰 금액
* 인터넷 금액
CASE를 이용한 One SQL로 구현해보자. (이건 너무 자주 나온 방법이라… 별 다른 설명이 필요없을듯 하다.)
SELECT 고객번호, 납입월,
NVL(SUM(DECODE(납입방법코드, '지로', 납입금액))) 지로,
NVL(SUM(DECODE(납입방법코드, '자동이체', 납입금액))) 자동이체,
NVL(SUM(DECODE(납입방법코드, '신용카드', 납입금액))) 신용카드,
NVL(SUM(DECODE(납입방법코드, '인터넷', 납입금액))) 인터넷,
NVL(SUM(DECODE(납입방법코드, '핸드폰', 납입금액))) 핸드폰
FROM 월별납입방법별집계
GROUP BY 고객번호, 납입월;
5.1.2 Table 복제 기법
Data를 복제를 해서 활용을 해야하는 경우 예전에는 복제용 Table을 생성해 두고 묻지마 JOIN을 활용하여 복제하였다.
CREATE TABLE COPY_T (no NUMBER); -- 복사용 Table을 생성해 두고,
INSERT INTO COPY_T
SELECT ROWNUM FROM EMP WHERE ROWNUM <= 10; -- 1 ~ 10의 값을 넣어두고 (최대 10배까지 복사)
SELECT * FROM EMP, COPY_T -- 복사할 값에 대해서 묻지마 JOIN
WHERE COPY_T.NO <= 2; -- no에 대한 조건으로 복사건수 입력
Oracle 9i부터는 dual Table에 start with 없는 connect by 구문으로 inline view로 활용이 가능하다.
SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 2; -- 2개의 값을 가진 임시 복제 Table
SELECT * FROM EMP, (SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 2); -- 2개로 복제
그럼 복사를 이용해서 emp Table을 1번만 읽고 JOB별 SAL의 합계와 전체 합계를 구해보자.
SELECT DECODE(no, 1, job, 'Total') as JOB,
SUM(sal) as SAL
FROM (SELECT job, no, sal
FROM EMP, (SELECT ROWNUM no FROM dual CONNECT BY LEVEL <= 2))
GROUP BY no, DECODE(no, 1, job, 'Total')
ORDER BY job, no;
사실 위 방식도 옜날 방식이긴하다. 그냥 ROLLUP을 이용하면 쉽게 가능하다.
SELECR DECODE(GROUPING(job), 0, job, 'Total') JOB,
SUM(sal) SAL
FROM emp
GROUP BY ROLLUP(job)
ORDER BY job;
ROLLUP을 이용한게 메모리도 훨씬 더 적게 사용한다.
- dual을 이용한 복제 Table 방식의 Execute Plan
SELECT STATEMENT OPTIMIZER=ALL_ROWS SORT (GROUP BY) (Bytes=125) MERGE JOIN (CARTESIAN) (Bytes=350) VIEW COUNT CONNECT BY (WITHOUT FILTERING) FAST DUAL BUFFER (SORT) (Bytes=168) TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Bytes=168)
위 경우에는 MERGE JOIN 단계에서 Table 크기의 2배만큼의 메모리를 사용한다.
- ROLLUP을 이용한 Execute Plan
SELECT STATEMENT OPTIMIZER=ALL_ROWS SORT (ORDER BY) (Bytes=60) SORT (GROUP BY) (Bytes=60) TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Bytes=168)
5.1.3 UNION ALL을 활용한 M:M Join
M:M 관계 Join이나 Full Outer Join을 대신해서 UNION ALL을 활용할 수 있다.
부서별판매계획 채널별판매실적
# 상품 # 상품
# 계획연월 # 판매연월
# 판매부서 # 판매채널
* 계획수량 * 판매수량
위와 같은 2개의 Table을 이용하여 월별로 각 상품의 계획 대비 판매 실적을 집계할려면 M:M 관계로 Join을 해야한다. 하지만 아래와 같이 무턱대로 Full Outer Join을 하면 잘못된 결과가 출력된다.
SELECT NVL(a.상품, b.상품) 상품,
NVL(a.계획연월, b.판매연월) 연월,
SUM(계획수량) 계획,
SUM(판매수량) 판매
FROM 부서별판매계획 a FULL OUTER JOIN 채널별판매실적 b
ON a.상품 = b.상품
AND a.계획연월 = b.판매연월
GROUP BY NVL(a.상품, b.상품) , NVL(a.계획연월, b.판매연월)
ORDER BY NVL(a.상품, b.상품) , NVL(a.계획연월, b.판매연월);
어떤 잘못된 결과가 나오는지 SQL만 보고 판단이 가능한가 ?
예를 들어서 상품A, 201501 대해서 계획에서는 1개의 record가 있고, 판매에는 2개의 record가 있다면 SUM(계획수량)에는 계획수량 X 2 개의 값이 나온다.
만약 Join 조건에 판매부서, 판매채널도 포함시킨다면 위와 같은 문제없이 정상적으로 활용이 가능하다.
그럼 판매부서를 Join조건에서 뺀 상태에서 동일 데이터가 2번 집계되지 않도록 하려면 어떻게 해야 할까 ? 그렇다면 미리 [상품 + 연월] 별로 GROUP BY 한 결과들로 Full Outer Join을 하면 된다.
SELECT NVL(a.상품, b.상품) 상품,
NVL(a.연월, b.연월) 연월,
SUM(계획) 계획,
SUM(판매) 판매
FROM (SELECT 상품, 계획연월 연월, SUM(계획수량) 계획
FROM 부서별판매계획 GROUP BY 상품, 계획연월) a
FULL OUTER JOIN
(SELECT 상품, 판매연월 연월, SUM(판매수량) 판매
FROM 채널별판매실적 GROUP BY 상품, 판매연월) b
ON a.상품 = b.상품
AND a.연월 = b.연월
GROUP BY NVL(a.상품, b.상품), NVL(a.연월, b.연월)
ORDER BY NVL(a.상품, b.상품), NVL(a.연월, b.연월);
원하는대로 출력된다.
책에는 Execute Plan이 비효율적이라고 나왔다는데, Oracle 11g에서는 별로 비효율적이지 않았다.
SELECT STATEMENT OPTIMZER=ALL_ROWS
SORT (GROUP BY)
VIEW OF '임시' (VIEW)
HASH JOIN (FULL OUTER)
VIEW
HASH
TABLE ACCESS (FULL) OF '채널별판매실적' (TABLE)
VIEW
HASH
TABLE ACCESS (FULL) OF '부서별판매계획' (TABLE)
전형적인 Hash Join의 Execute Plan이다.
위와 같은 SQL문 보다는 UNION ALL일 이용하면 더 간단히 구현이 가능하다.
SELECT 상품, 연월, SUM(계획) 계획, SUM(판매) 판매
FROM (SELECT 상품, 계획연월 연월, SUM(계획수량) 계획, NULL 판매
FROM 부서별판매계획 GROUP BY 상품, 계획연월
UNION ALL
SELECT 상품, 판매연월, NULL, SUM(판매수량)
FROM 채널별판매실적 GROUP BY 상품, 판매연월)
GROUP BY 상품, 연월
ORDER BY 상품, 연월;
실행계획도 보면 Hash Join이 아닌 UNION ALL로 바뀌었다.
SELECT STATEMENT OPTIMIZER=ALL_ROWS
SORT (GROUP BY)
VIEW
UNION-ALL
HASH (GROUP BY)
TABLE ACCESS (FULL) OF '부서별판매계획' (TABLE)
HASH (GROUP BY)
TABLE ACCESS (FULL) OF '채널별판매실적' (TABLE)
5.1.4 Paging 처리
Webpage에서 게시판을 보여줄 경우 한번에 모든 목록을 다 보여주지않고, Page 단위로 끊어서 목록을 보여준다.
이럴 경우 Paging 처리를 해주지 않고, 무조건적으로 읽은 다음 FETCH로 넘기디가 필요한 만큼만 보여주고 끝내는 방식으로 구현했다가는 DB에 엄청난 부하가 걸린다.
BBS
# Catalog
# num
* RegDate
* Title
* Description
* Writer
위와 같이 게시판을 관리하는 Table이 있는 경우 Paging 처리는 다음과 같이 하면 된다.
SELECT *
FROM (SELECT ROWNUM no, num, title,
COUNT(*) OVER () CNT -- #1
FROM (SELECT num, title
FROM bbs
WHERE RegDate >= :dt
AND Catalog = :cat
ORDER BY num DESC)
WHERE ROWNUM <= :page * :pageSize + 1) -- #2
WHERE no BETWEEN (:page - 1) * :pagesize + 1 AND :pageSize * :page; -- #3
SELECT STATEMENT Optimizer=ALL_ROWS
VIEW
WINDOW (BUFFER)
COUNT (STOPKEY)
VIEW
TABLE ACCESS (BY INDEX ROWID) OF 'BBS' (TABLE)
INDEX (RANGE SCAN) OF 'BBS_PK' (INDEX (UNIQUE))
- #1 : 다음 page에 데이터가 있는지 확인할 용도이다. 가장 안쪽 Inline View의 record 수가 return 된다. 이 값을 no값과 비교해서 더 크다면 다음 page에 출력할 값이 있다고 판단 할 수 있다.
- #2 : List의 첫 page 부터 현재 page까지의 데이터를 출력한다. 사실 비효율적이긴 하다. (index + first_rows(n)을 활용해서 부분범위 처리해야 한다.)
- #3 : 현재 page에 보여줄 내용만 걸러낸다.
성능과 I/O효율을 위해서는 num의 index가 필요하다. 그러면 sort 작업을 수행하지 않아도 되며, first_rows(N) Hint를 활용해서 부분범위 처리를 유도할수도 있다.
위 예제는 1 page만 볼때는 괜찮은데, 다음 page 조회를 자주 할 경우에는 비효율적이다.
매번 보여줄 필요가 없는 앞 page들의 데이터를 다 읽어와야 한다.
다음 page 버튼을 눌렀을때의 SQL문은 간단히 구현이 가능하다.
SELECT num, title
FROM bbs
WHERE Catalog = :cat
AND num < :no -- 현재 page의 마지막 num값
AND rownum <= :pageSize
ORDER BY num DESC;
위 SQL이 맞게 보일수도 있다. 더군다가 읽은 데이터도 딱 한 page에 보여줄 만큼만 읽었다.
하지만, Index가 어떻게 설정되어 있냐에 따라 다른 값이 나올 수 있다.
이 경우에는 성능보다는 어떤 상황에서도 정확한 값이 나오는게 더 중요하기 때문에 아래와 같이 Inline View로 처리를 해주어야 안전하다.
SELECT *
FROM (SELECT num, title
FROM bbs
WHERE Catalog = :cat
AND num < :no -- 현재 page의 마지막 num값
ORDER BY num DESC)
WHERE rownum <= :pageSize
SELECT STATEMENT Optimzer=ALL_ROWS
COUNT (STOPKEY)
VIEW
TABLE ACCESS (BY INDEX ROWID) OF 'BBS' (TABLE)
INDEX (RANGE SCAN DESCENDING) OF 'BBS_PK' (INDEX (UNIQUE))
그럼 이전 page는 ???
SELECT *
FROM (SELECT num, title
FROM bbs
WHERE Catalog = :cat
AND num > :no -- 현재 page의 첫번째 num값
AND rownum <= :pageSize
ORDER BY num)
ORDER BY num DESC;
이 경우에는 rownum 비교를 Inline View 안으로 넣어서 읽는 데이터 수를 줄였다.
이건 index 구성과 화면에 보여주는 data의 순서를 생각해서 다음 page, 이전 page 둘 중 하나는 안에 넣는게 가능하다.
다음 page의 SQL문 같이 밖으로 빼도 결과는 똑같다.
그럼 다음 page 와 이전 page를 하나의 SQL문으로 묶을순 없을까 ?
UNION ALL을 이용하면 된다.
어느 버튼을 눌렀는지에 대한 조건만 추가를 해주면 된다.
만약 다음 page를 눌렀을때 ‘N’을 전달하고, 이전 page를 눌렀을때 ‘P’를 전달하다고 하면 다음과 같이 작성이 가능하다.
SELECT num, title
FROM (SELECT num, title
FROM bbs
WHERE 'N' = :btn
AND Catalog = :cat
AND num < :no
ORDER BY num DESC)
WHERE rownum <= :pageSize
UNION ALL
SELECT num, title
FROM (SELECT num, title
FROM bbs
WHERE 'P' = :btn
AND Catalog = :cat
AND num > :no
AND rownum <= :pageSize
ORDER BY num)
ORDER BY num DESC;
얼핏 생각하면 아래,위의 Inline View를 UNION ALL로 하고 겉에 SELECT로 씌우는게 가능할것 같지만, 안된다.
겉의 SELECT까지 해야 원하는 데이터가 순서래도 출력되기 때문이다.
5.1.5 Window 함수 활용
왼쪽 그럼과 같이 저장되어 있는 Table에서 오른쪽과 같은 형태로 출력하고 싶을 때 Query를 어떻게 만들어야 할까 ?
- 먼저 STATUS 값이 NULL이면 자신보다 이전 값중에 NULL이 아닌 최근값을 찾아야 하는데… (이 부분에서 Sub-query로 한겹)
- 그러기 위해서는 STATUS가 NULL이 아닌 최근값의 SN을 구해야 한다. (이 부분에서 다시 Sub-query)
즉 다음과 같은 Query를 작성해야 한다.
SELECT a.SN,
NVL(a.STATUS, (SELECT b.STATUS
FROM EQ_MES b
WHERE b.SN = (SELECT MAX(SN)
FROM EQ_MES c
WHERE c.SN < a.SN
AND c.STATUS IS NOT NULL))) STATUS,
a.VALUE
FROM EQ_MES a;
SN에 PK가 설정되어 있다는 가정하에서 생각해보면,
- 가장 안쪽의 Sub-query에서 MAX(SN)을 구하기 위해서 INDEX RANGE SCAN
- 해당 SN값으로 b.STATUS값을 구하기 위해서 INDEX UNIQUE SCAN
- 위 1,2 과정을 모든 레코드 별로 1번씩 수행. 필터링 캐시 ? 입력값이 늘 다르므로 의미없음
SELECT STATEMENT OPTIMIZER=ALL_ROWS
TABLE ACCESS (BY INDEX ROWID) OF 'EQ_MES' (TABLE)
INDEX (UNIQUE SCAN) OF 'EQ_MES_PK' (INDEX (UNIQUE))
SORT (AGGREGATE)
TABLE ACCESS (BY INDEX ROWID) OF `EQ_MES` (TABLE)
INDEX (RANGE SCAN) OF `EQ_MES_PK` (INDEX (UNIQUE))
TABLE ACCESS (FULL) OF `EQ_MES` (TABLE)
SN에 PK가 설정되어 있으면 그냥 간단하게 index를 활용하여 NULL이 아닌 첫번째 값을 가져오게끔도 가능하다.
SELECT a.SN,
NVL(a.STATUS, (SELECT /*+ index_desc(b EQ_MES_PK) */ b.STATUS
FROM EQ_MES b
WHERE b.SN < a.SN
AND b.STATUS IS NOT NULL
AND ROWNUM <= 1)) STATUS,
a.VALUE
FROM EQ_MES a;
SELECT STATEMENT OPTIMIZER=ALL_ROWS
COUNT (STOPKEY)
TABLE ACCESS (BY INDEX ROWID) OF `EQ_MES` (TABLE)
INDEX (RANGE SCAN DESCENDING) OF EQ_MES_PK` (INDEX (UNIQUE))
TABLE ACCESS (FULL) OF `EQ_MES` (TABLE)
앞에서 배운 Window 함수를 이용하면 쉽게 해결이 가능하다.
- LAST_VALUE : Partition 내에서 가장 마지막 값을 출력
IGNORE NULLS 옵션을 사용해서 바로 가져 올 수가 있다.
SELECT SN,
LAST_VALUE(STATUS IGNORE NULLS) OVER
(ORDER BY SN ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) STATUS,
VALUE
FROM EQ_MES
ORDER BY SN;
- IGNORE NULLS : NULL값은 무시하고 마지막값을 가져옴
- PARTITION 은 여기서 지정하지 않았음
- ORDER BY SN : SN 순으로 정렬
- BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW : 검색 RANGE를 이전의 모든 데이터에서 현재까지
SELECT STATEMENT OPTIMIZER=ALL_ROWS
WINDOW(SORT)
TABLE ACCESS (FULL) OF `EQ_MES` (TABLE)
5.1.6 WITH 활용
PL/SQL 에서 내부적으로 임시 테이블을 생성하여 재활용이 가능하다.
WITH 임시테이블명
AS
(SELECT ... )
로 선언을 해두고 재활용이 가능하다.
하지만 이것을 활용한 성능향상은 Oracle (9i이후)만 가능하다.
MS-SQL은 Inline 방식으로 항상 해당 Query를 새로 실행한다.
Oracle은 materialize, inline Hint를 활용하여 임시 테이블로 결과를 저장하고 재사용할 것인지, View같이 항상 새로 실행한 것인지의 선택이 가능하다.
이 글이 도움이 되셨다면 공감 및 광고 클릭을 부탁드립니다 :)
