1. CASE문 활용

왼쪽에 있는 월별납입방법별집계 테이블을 읽어 오른쪽 월요금납부실적과 같은 형태로 가공하려고 함.

가) 1차 변환


INSERT INTO 월별요금납부실적 (고객번호,
                              납입월,
                              지로,
                              자동이체,
                              신용카드,
                              핸드폰,
                              인터넷)
SELECT K.고객번호,
       '200903' 납입월,
       A.납입금액 지로,
       B.납입금액 자동이체,
       C.납입금액 신용카드, 
       D.납입금액 핸드폰,
       E 납입금액 인터넷
FROM   고객 K,
       (SELECT 고객변호,
               납입금액
        FROM   월별납입방법별집계
        WHERE  납입월 = '200903'
        AND    납입방법코드 = 'A') A,
       (SELECT 고객번호,
               납입금액
        FROM   월별납입방법별집계
        WHERE  납입월 = '200903'
        AND    납입방법코드 = 'B') B,
       (SELECT 고객변호,
               납입금액
        FROM   월별납입방법별집계
        WHERE  납입 월 = '200903'
        AND    납입방법코드 = 'C') C,
       (SELECT 고객변호,
               납입금액
        FROM   월별납입방법별집계
        WHERE  납입월 = '200903'
        AND    납입방법코드 = 'D') D,
       (SELECT 고객변호,
               납입금액
        FROM   월별납입방법별집계
        WHERE  납입월 = '200903'
        AND    납입방법코드 = 'E') E
WHERE  A.고객번호(+) = K.고객번호
AND    B.고객변호(+) = K.고객번호
AND    C.고객변호(+) = K.고객번호
AND    D.고객변호(+) = K.고객변호
AND    E.고객변호(+) = K.고객번호
AND    NVL(A.납입금액, O) + NVL(B.납입금액, 0) + NVL(C.납입금액, 0) + NVL(D.납입금액, 0) + NVL(E.납입금액, 0) >  0;

위의 SQL은 납입방법코드를 코드별로 5번 액세스하여 처리하였기 때문에, 불필요한 과다 액세스로 인해 성능이 떨어짐

나) 2차 변환


INSERT INTO 월별요금납부실적 (고객변호,
                              납입월,
                              지로,
                              자동이체,
                              신용카드,
                              핸드폰,
                              인터넷)
SELECT *
FROM   (SELECT 고객변호,
               납입월,
               NVL(SUM(CASE WHEN 납입방법코드 = 'A' THEN 납입금액 END), O) 지로,
               NVL(SUM(CASE WHEN 납입방법코드 = 'B' THEN 납입금액 END), 0) 자동이체,
               NVL(SUM(CASE WHEN 납입방법코드 = 'C' THEN 납입금액 END), 0) 신용카드,
               NVL(SUM(CASE WHEN 납입방법코드 = 'D' THEN 납입금액 END), 0) 핸드폰,
               NVL(SUM(CASE WHEN 납입방법코드 = 'E' THEN 납입금액 END), O) 인터넷
        FROM   월별납입방법별집계
        WHERE  납입월 = '200903'
        GROUP  BY 고객변호,
                  납입월
        )
WHERE  지로 + 자동이체 + 신용카드 + 핸드폰 + 인터넷 > 0;

위와 같이 CASE문을 사용할 경우 엑셀의 PIVOT처럼 데이터를 처리할 수 있으며, 불필요한 중복 액세스를 방지할 수 있음

다) 3차 변환(Oracle-11g Pivot 사용)

Oracle-11g Pivot 구문


[Syntax]
 
SELECT ...
FROM   ...
PIVOT [XML]
   ( pivot_clause
     pivot_for_clause
     pivot_in_clause )
WHERE  ...

clause	description
pivot_clause	defines the columns to be aggregated (pivot is an aggregate operation);
pivot_for_clause	defines the columns to be grouped and pivoted;
pivot_in_clause	defines the filter for the column(s) in the pivot_for_clause (i.e. the range of values to limit the results to). The aggregations for each value in the pivot_in_clause will be transposed into a separate column (where appropriate).

Oracle-11g Pivot 변환


INSERT INTO 월별요금납부실적 (고객변호,
                              납입월,
                              지로,
                              자동이체,
                              신용카드,
                              핸드폰,
                              인터넷)
SELECT *
FROM   (SELECT 고객변호,
               납입월,
               납입방법코드,
               납입금액
        FROM   월별납입방법별집계
        WHERE  납입월 = '200903')
PIVOT  (SUM(납입금액)
        FOR 납입방법코드 IN ('A' AS 지로,
                             'B' AS 자동이체,
                             'C' AS 신용카드,
                             'D' AS 핸드폰,
                             'E' AS 인터넷)
       );

2. 데이터 복제 기법 활용

가) CONNECT BY를 이용한 데이터 복제 방법


SELECT *
FROM   (SELECT OWNER,
               OBJECT_NAME
        FROM   DBA_OBJECTS
        WHERE  OBJECT_NAME = 'DBA_TABLES'
        AND    ROWNUM = 1),
       (SELECT LEVEL CNT
        FROM   DUAL
        CONNECT BY LEVEL <= 5)
;

OWNER OBJECT_NAME  CNT                 
----- ------------ ---                 
SYS   DBA_TABLES     1                 
SYS   DBA_TABLES     2                 
SYS   DBA_TABLES     3                 
SYS   DBA_TABLES     4                 
SYS   DBA_TABLES     5                 

5 rows selected.

나) 실 예제


BREAK ON 카드상품분류

SELECT A.카드상품분류,
       (CASE WHEN B.NO 1 THEN A.고객등급 ELSE '소계' END) AS 고객등급,
        SUM(A. 거래금액) AS 거래금액
FROM   (SELECT 카드.카드상품분류 AS 카드상품분류,
               고객.고객등급 AS 고객등급,
               SUM(거래금액) AS 거래금액
        FROM   카드월실적,
               카드,
               고객
        WHERE  실적년월 = '201008'
        AND    카드.카드번호 = 카드월실적.카드변호
        AND    고객.고객변호 = 카드.고객변호
        GROUP  BY 카드.카드상품분류,
                  고객.고객등급) A,
       (SELECT ROWNUM NO,
               LPAD(ROWNUM, 2, '0') NO2
        FROM   BIG_TABLE
        WHERE  ROWNUM(= 31) B
WHERE  B.NO <= 2
GROUP BY A.카드상품분류,
         B.NO, 
         (CASE WHEN B.NO 1 THEN A.고객등급 ELSE '소계' END)
;

카드상품분류    고객등급          거래금액
--------------  -----------  -------------
상품분류A       VIP              500000000
                일반             300000000
                소계             800000000
상품분류B       VIP              100000000
                일반              50000000
                소계             150000000

3. Union All을 활용한 M_M 관계의 조인


SELECT NVL(A.상품, B.상품) AS 상품,
       NVL(A.계획연월, B.판매연월) AS 연월,
       NVL(계획수량, 0) 계획수량,
       NVL(판매수량, 0) 판매수량
FROM   (SELECT 상품.계획연월,
               SUM(계획수량) 계획수량
        FROM   부서별판매계획
        WHERE  계획연월 BETWEEN '200901' 
                        AND     '200903'
        GROUP  BY 상품.계획연월
       ) A
FULL   OUTER JOIN (SELECT 상품.판매연월,
                          SUM(판매수량) 판매수량
                   FROM   채널별판매실적
                   WHERE  판매연월 BETWEEN '200901' 
                                   AND     '200903'
                   GROUP BY 상품.판매연월) B 
ON     A.상품     = B.상품
AND    A.계획연월 = B.판매연월

Execution Plan
-----------------------------------------------------------------------------------------
 0      SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=8 Bytes=352)
 1  0    VIEW (Cost=14 Card=8 Bytes=352)
 2  1     UNION-ALL
 3  2      HASH JOIN (OUTER) (Cost=8 Card=7 Bytes=308)
 4  3       VIEW (Cost=4 Card=7 Bytes=154)
 5  4        SORT (GROUP BY) (Cost=4 Card=7 Bytes=98)
 6  5         TABLE ACCESS (FULL) OF '부서별판매계획' (Cost=2 Card=7 Bytes=98)
 7  3       VIEW (Cost=4 Card=6 Bytes=132)
 8  7        SORT (GROUP BY) (Cost=4 Card=6 Bytes=84)
 9  8         TABLE ACCESS (FULL) OF '채널별판매실적' (Cost=2 Card=6 Bytes=84)
10  2      SORT (GROUP BY) (Cost=4 Card=1 Bytes=14)
11 10       FILTER
12 11        TABLE ACCESS (FULL) OF '채널별판매실적' (Cost=2 Card=1 Bytes=14)
13 11        SORT (GROUP BY NOSORT) (Cost=2 Card=1 Bytes=14)
14 13         FILTER
15 14          TABLE ACCESS (FULL) OF '부서별판매계획' (Cost=2 Card=1 Bytes=14)

DBMS 버전에 따라 FULL OUTER JOIN을 할 경우, 위와 같이 비 효율적인 실행계획을 보임
이를 개선하기 위해 실제 데이터를 출력하여 개선할 수 있는지 여부 확인


SELECT '계획' AS 구분,
       상품,
       계획연월 AS 연월,
       판매부서,
       NULL AS 판매채널,
       계획수량,
       TO_NUMBER(NULL) AS 실적수량
FROM   부서 별판매계획
WHERE  계획연월 BETWEEN '200901' 
                AND     '200903'
UNION ALL
SELECT '실적',
       상품,
       판매연월 AS 연월,
       NULL AS 판매부서,
       판매채널,
       TO_NUMBER(NULL) AS 계획수량,
       판매수량
FROM   채널별판매실적
WHERE  판매연월 BETWEEN '200901' 
                AND     '200903'
;

구분      상품      연월      판매부서      판매채널        계획수량      실적수량
-----     ------    -------   ---------     -----------   ----------    ----------
계획      상품A     200901    10                               10000    
계획      상품A     200902    10                               10000    
계획      상품A     200903    10                               10000    
계획      상품B     200901    10                               10000    
계획      상품B     200902    10                               10000    
계획      상품C     200901    10                               10000    
계획      상품C     200903    10                               10000    
실적      상품A     200901                  대리점                            7000
실적      상품A     200903                  온라인                            8000
실적      상품B     200902                  온라인                           12000
실적      상품B     200903                  위탁                             19000
실적      상품C     200901                  대리점                           18000
실적      상품C     200902                  위탁                             18000

데이터를 확인한 결과, 상품, 연월을 GROUP BY 하여 계획수량과 실적수량을 구할 경우 FULL OUTER JOIN을 대체할 수 있음


SELECT 상품,
       연월,
       NVL(SUM(계획수량), 0) AS 계획수량,
       NVL(SUM(실적수량), 0) AS 실적수량
FROM   (SELECT 상품,
               계획연월 AS 연월,
               계획수량,
               TO_NUMBER(NULL) AS 실적수량
        FROM   부서 별판매계획
        WHERE  계획연월 BETWEEN '200901' 
                        AND     '200903'
        UNION ALL
        SELECT 상품,
               판매연월 AS 연월,
               TO_NUMBER(NULL) AS 계획수량,
               판매수량
        FROM   채널별판매실적
        WHERE  판매 연월 BETWEEN '200901' 
                         AND     '200903'
       ) A
GROUP  BY 상품,
          연월
;

상품      연월        계획수량      실적수량
------    -------   ----------    ----------
상품A     200901         10000          7000
상품A     200902          5000             0
상품A     200903         20000          8000
상품B     200901         20000             0
상품B     200902         15000         12000
상품B     200903             0         19000
상품C     200901         15000         13000
상품C     200902             0         18000
상품C     200903         20000             0

4. 페이징 처리

가) 일반적인 페이징 처리용 SQL

관심 종목에 대해 사용자가 입력한 거래일시 이후, 거래 데이터를 페이징 처리 방식으로 조회하는 SQL


SELECT *
FROM   (SELECT ROWNUM NO,
               거래일시,
               체결건수,
               체결수량,
               거래대금,
               COUNT(*) OVER() CNT -- ①
        FROM   (SELECT 거래일시,
                       체결건수,
                       체결수량,
                       거래대금
                FROM   시간별종목거래
                WHERE  종목코드 = :ISU_CD
                AND    거래일시 >= :TRD_TIME
                ORDER  BY 거래일시 -- ②
                )
        WHERE  ROWNUM <= :PAGE * :PGSIZE + 1 -- ③
        )
WHERE  NO BETWEEN (:PAGE - 1) * :PGSIZE + 1  -- ④
          AND     :PGSIZE * :PAGE
;

Execution Plan
--------------------------------------------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=l Bytes=75)
1  0  FILTER
2  1   VIEW (Cost=5 Card=l Bytes=75)
3  2    WINDOW (BUFFER) (Cost=5 Card=l Bytes=49)
4  3     COUNT (STOPKEY)
5  4      VIEW (Cost=5 Card=l Bytes=49)
6  5       TABLE ACCESS (BY INDEX ROWID) OF '시간별종목거래' (TABLE) (Card=l Bytes=56)
7  6       INDEX (RANGE SCAN) OF '시간별종목거래 PK' (INDEX (UNIQUE)) (Card=l)

:PGSIZE 변수에는 사용자가 '다음(▶)' 벼튼을 누를 때마다 Fetch해 올 데이터 건수를 입력하고, :PAGE 변수에는 그때 출력하고자 하는 페이지 변호를 입력함.

번호	설명
②	<종목코드 + 거래일시> 순으로 정렬된 인텍스가 있을 때는 지동으로 Sort 오퍼레이션이 생략됨. NoSort를 위해 활용 가능한 인텍스가 없을 경우 전체를 읽는 비 효율은 발생할 수 있지만, TOP-N 쿼리 알고리즘으로 인해 Sort 부하는 최소화 가능
①	'다음' 페이지에 읽을 데이터가 더 있는지 확인하는 용도. 결과집합에서 CNT 값을 읽었을 때 :PGSIZE * :PAGE 보다 크면 '다음' 페이지에 출력할 데이터가 더 있음을 알 수 있음. 전체 건수를 세지 않고도 '다음' 버튼을 활성화할지를 판단할 수 있어 유용함.
③	:PGSIZE = 10 이고 :PAGE = 3 일 때, 거래일시 순으로 31건만 읽음.
④	:PGSIZE = 10 이고 :PAGE = 3 일 때, 안쪽 인라인 뷰에서 읽은 31건 중 21~30번째 데이터 즉, 3 페이지만 리턴함.

'다음' 페이지 테스트


var page NUMBER
var pgsize NUMBER

EXEC :page := 3
EXEC :pgsize := 10

SELECT *
FROM   (SELECT NO,
               TRADE_DATE,
               TRADE_COUNT,
               COUNT(*) OVER() CNT
        FROM   (SELECT LEVEL NO,
                       TO_DATE('20130101', 'YYYYMMDD') + LEVEL - 1 TRADE_DATE,
                       LEVEL * 10 TRADE_COUNT
                FROM   DUAL
                CONNECT BY LEVEL <= 35)
        WHERE  ROWNUM <= :PAGE * :PGSIZE + 1
        )
WHERE  NO BETWEEN (:PAGE - 1) * :PGSIZE + 1   
          AND     :PGSIZE * :PAGE
;

        NO TRADE_DATE   TRADE_COUNT        CNT 
---------- ------------ ----------- ---------- 
        21 21-JAN-13            210         31 
        22 22-JAN-13            220         31 
        23 23-JAN-13            230         31 
        24 24-JAN-13            240         31 
        25 25-JAN-13            250         31 
        26 26-JAN-13            260         31 
        27 27-JAN-13            270         31 
        28 28-JAN-13            280         31 
        29 29-JAN-13            290         31 
        30 30-JAN-13            300         31 
;

EXEC :page   := 4
EXEC :pgsize := 10

SELECT *
FROM   (SELECT NO,
               TRADE_DATE,
               TRADE_COUNT,
               COUNT(*) OVER() CNT
        FROM   (SELECT LEVEL NO,
                       TO_DATE('20130101', 'YYYYMMDD') + LEVEL - 1 TRADE_DATE,
                       LEVEL * 10 TRADE_COUNT
                FROM   DUAL
                CONNECT BY LEVEL <= 35)
        WHERE  ROWNUM <= :PAGE * :PGSIZE + 1
        )
WHERE  NO BETWEEN (:PAGE - 1) * :PGSIZE + 1   
          AND     :PGSIZE * :PAGE
;

        NO TRADE_DATE   TRADE_COUNT        CNT
---------- ------------ ----------- ----------
        31 31-JAN-13            310         35
        32 01-FEB-13            320         35
        33 02-FEB-13            330         35
        34 03-FEB-13            340         35
        35 04-FEB-13            350         35
;

나) 뒤쪽 페이지까지 자주 조회할 때

뒤쪽 페이지를 자주 조회할 경우 가)의 경우는 이전 데이터를 계속 액세스해야 하므로 비효율적임
이처럼 앞쪽의 페이지를 읽지 않고 해당되는 페이지만 바로 액세스 하고자 할 경우 아래의 방법을 사용함

1) 다음 페이지를 클릭할 경우 SQL


SELECT 거래일시,
       체결건수,
       체결수량,
       거래대금
FROM   (SELECT 거래일시,
               체결건수,
               체결수량,
               거래대금
        FROM   시간별종목거래 A
        WHERE  :페이지이동 =  'NEXT'
        AND    종목코드    =  :ISU_CD
        AND    거래일시    >= :TRD_TIME
        ORDER  BY 거래일시)
WHERE  ROWNUM <= 11
;

Execution PLAN
--------------------------------------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=l Bytes=49)
1  0  COUNT (STOPKEY)
2  1   VIEW (Cost=5 Card=l Bytes=49)
3  2    FILTER
4  3     TABLE ACCESS (BY INDEX ROWID) OF '시간별종목거래' (TABLE) (Card=l Bytes=56)
5  4      INDEX (RANGE SCAN) OF '시간별종목거래_PK' (INDEX (UNIQUE)) (Card=l)

위의 내용은 첫 번째 페이지를 출력하고 나서 '다음' 벼튼을 누를 때의 구현 예시
인덱스는 '종목코드, 거래일시'로 되어 있으며, :TRD_TIME 변수값은, 이전 페이지에서 출력한 마지막 거래일시를 입력함.

1. 페이징 처리에 대한 의문?

1) 인덱스 설계를 페이징 처리를 위해 설계?

테이블의 인덱스 설계를 특정 화면의 페이징 처리를 위해서 만들어야 하나?
그렇다면, 무수히 많은 어플리케이션에 페이징 처리가 필요하고, 조회조건들도 많을텐데 그것에 맞게 모두 만들어 주어야 하나?

2) 만약 여러 페이지를 건너뛰면?

뒤쪽 페이지만 읽기 위해 바로 이전 페이지의 거래일시를 변수값으로 받도록 되어 있다.
사용자가 친절히 1, 2, 3... 페이지 순으로 넘어가면 좋으나, 만약 1 페이지 조회 후 바로 5페이지로 넘어가면 1페이지의 거래일시 변수값을 받아야 하지 않나?

2. 페이징 처리에 대한 의견

1) 페이징 처리를 위한 인덱스 설계

페이징 처리를 위한 인덱스 설계는 중요한 화면을 제외하면 지양해야 할 것으로 보임.
인덱스 설계는 해당 테이블을 가장 자주 액세스 하는 조건으로 설계되어야 하는데, 특정 페이징 처리를 위해서 설계될 경우 무분별하게 만들어서 성능에 악영향이 있을 것으로 보임.

2) 여러 페이지를 건너뛸 경우?

위에서는 :페이지이동 바인드 변수를 'PREV', 'NEXT' 2개만 받도록 설계되어, 여러 페이지를 바로 이동하는 구조가 아니므로 SQL 구성이 가능하나 여러 페이지를 한 번에 넘길경우 이 SQL은 구현이 불가능함.
만약, 여러 페이지를 한 번에 넘겨야 할 경우에는 '가) 일반적인 페이징 처리용 SQL' 방식으로만 처리 가능함.

2) 이전 페이지를 클릭할 경우 SQL


SELECT 거래일시,
       체결건수,
       체결수량,
       거래대금
FROM   (SELECT 거래일시,
               체결건수,
               체결수량,
               거래대금
        FROM   시간별종목거래 A
        WHERE  :페이지이동 =  'PREV'
        AND    종목코드    =  :ISU_CD
        AND    거래일시    <= :TRD_TIME
        ORDER  BY 거래일시 DESC)
WHERE  ROWNUM <= 11
ORDER  BY 거래일시
;

Execution Plan
------------------------------------------------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=l Card=l Bytes=49)
1  0  SORT (ORDER BY) (Cost=l Card=l Bytes=49)
2  1   COUNT (STOPKEY)
3  2    VIEW (Cost=5 Card=l Bytes=49)
4  3     FILTER
5  4      TABLE ACCESS (BY INDEX ROWID) OF '시간별종목거래' (TABLE) (Card=l Bytes=56)
6  5      INDEX (RANGE SCAN DESCENDING) OF '시간별종목거래_PK' (INDEX (UNIQUE)) (Card=l)

:TRD_TIME 바인드 변수 또한, 이전 페이지의 첫 번째 값을 가져는데 거래일시를 DESC로 액세스하고 'ROWNUM <= 11'로 처리를 하기 때문에 구현에는 문제 없음.
'SORT (ORDER BY)' 실행계획이 나오나, 'COUNT (STOPKEY)' 실행계획 다음에 나오기 때문에(이 실행계획은 'ROWNUM <= 11' 구문으로 인해 생김) 부담이 적음.

3) SQL 서버에서 구현 시 SQL


-- 첫 화면이거나, '다음(▶)' 버튼을 클릭했을 때 
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 거래일시
;

다) Union All 활용


SELECT 거래일시,
       체결건수,
       체결수량,
       거래대금
FROM   (SELECT 거래일시,
               체결건수,
               체결수량,
               거래대금
        FROM   시간별종목거래
        WHERE  :페이지이동 =  'NEXT' -- 첫 페이지 출력 시에도 'NEXT' 입력 
        AND    종목코드    =  :ISU_CD
        AND    거래일시    >= :TRD_TIME
        ORDER  BY 거래일시)
WHERE  ROWNUM <= 11
UNION ALL
SELECT 거래일시,
       체결건수,
       체결수량,
       거래대금
FROM   (SELECT 거래일시,
               체결건수,
               체결수량,
               거래대금
        FROM   시간별종목거래
        WHERE  :페이지이동 =  'PREV'
        AND    종목코드    =  :ISU_CD
        AND    거래일시    <= :TRD_TIME
        ORDER  BY 거래일시 DESC)
WHERE  ROWNUM <= 11
ORDER  BY 거래일시

5. 윈도우 함수 활용

Oracle 에 의해 처음 소개된 윈도우 함수(Oracle에서는 '분석 함수(Analytic Function)'라고 함)가 지금은 ANSI 표준으로 채택돼 대부분 DBMS 에서 지원함.

목표 : 상태코드가 NULL이면 가장 최근에 상태코드가 바뀐 레코드의 값을 보여주어야 함

1) SQL_1


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

윈도우 함수를 사용하지 않을 경우, 위와 같이 작성하여 구현 가능
성능 측면에서는 '일련번호, 상태코드' 조합으로 구성된 인덱스가 있을 경우 좋음

2) SQL_2


SELECT 일련번호,
       측정값,
       (SELECT /*+ index_desc(장비측정 장비측정_idx) */
               상태코드
        FROM   장비측정
        WHERE  일련번호 <= O.일련번호
        AND    상태코드 IS NOT NULL
        AND    ROWNUM <= 1) 상태코드
FROM   장비측정 O
ORDER  BY 일련번호
;

좀 더 I/O 부하를 줄인다면, INDEX_DESC로 액세스하여 1건을 읽을 경우 추가 튜닝 가능

3) SQL_3


SELECT 일련번호,
       측정값,
       LAST_VALUE(상태코드 IGNORE NULLS) OVER(ORDER BY 일련번호 ROWS BETWEEN UNBOUNDED PRECEDING 
                                                                     AND     CURRENT ROW) 상태코드
FROM   장비측정
ORDER  BY 일련번호

윈도우 함수를 사용할 경우 위와 같이 구현 가능함

6. With 구문 활용

With 구문을 Oracie은 9i 버전부터 SQL Server는 2005 버전부터 지원하기 시작했으며, 구현방식은 아래와 같음
- Materialize 방식 : 내부적으로 임시 테이블을 생성함으로써 반복 재사용
- lnline 방식 : 물리적으로 임시 테이블을 생성하지 않으며, 참조된 횟수만큼 런타임 시 반복 수행함.

만약 반복 액세스 해야 하는 집합이 있을 경우, 이를 With절 사용 시 Inline 방식이 아닌 Materialize 방식으로 처리하여 1회만 액세스하도록 튜닝 가능함


WITH 위험고객카드 AS 
( 
SELECT 카드.카드번호,
       고잭.고잭번호
FROM   고잭,
       카드
WHERE  고객.고객번호       = 카드발급.고객번호
AND    카드.카드번호객여부 = 'Y'    
) 
SELECT V.*
FROM   (SELECT A.카드번호 AS 카드번호,
               SUM(A.거래금액) AS 거래금액,
               NULL AS 현금서비스잔액,
               NULL AS 해외거래금액
        FROM   카드거래내역 A,
               위험고객카드 B
        WHERE  조건
        GROUP  BY A.카드번호
        UNION ALL
        SELECT A.카드번호 AS 카드번호,
               NULL AS 현금서비스잔액,
               SUM(AMT) AS 현금서비스금액,
               NULL AS 해외거래금액
        FROM   (SELECT A.카드번호 AS 카드번호,
                       SUM(A.거래금액) AS AMT
                FROM   현금거래내역 A,
                       위험고객카드 B
                WHERE  조건
                GROUP  BY A.카드번호
                UNION ALL
                SELECT A.카드번호 AS 카드번호,
                       SUM(A.결재금액) * -1 AS AMT
                FROM   현금결재내역 A,
                       위험고객카드 B
                WHERE  조건
                GROUP  BY A.카드번호) A
        GROUP  BY A.카드번호
        UNION ALL
        SELECT A.카드번호 AS 카드번호,
               NULL AS 현금서비스잔액,
               NULL AS 현금서비스금액,
               SUM(A.거래금액) AS 해외거래금액
        FROM   해외거래내역 A,
               위험고객카드 B
        WHERE  조건
        GROUP  BY A.카드번호) V
;

Execution PLAN
--------------------------------------------------------------------------------------------------------
TEMP TABLE TRANSFORMATION -- 임시테이블 생성
 LOAD AS SELECT
  VIEW (Cost=94K Card=5K Bytes=345K)
   UNION-ALL
    SORT (GROUP BY) (Cost=57K Card=l Bytes=120)
     HASH JOIN (Cost=57K Card=l Bytes=120)
      PARTITION RANGE (SINGLE)
       PARTITION HASH (ALL)
        TABLE ACCESS (FULL) OF '카드거 래내 역'
      VIEW (Cost=50 Card=833K Bytes=13M)
       TABLE ACCESS (FULL) OF 'SYS. SYS_TEMP OFD9D6B4E 4COC42BA' --임시 테이블 사용
    SORT (GROUP BY) (Cost=36K Card=746 Bytes=20K)
     VIEW (Cost=36K Card=746 Bytes=20K)
      UNION-ALL
       SORT (GROUP BY) (Cost=34K Card=l Bytes=74)
        HASH JOIN (Cost=34K Card=l Bytes=74)
         PARTITION RANGE (ITERATOR)
          PARTITION HASH (ALL)
           TABLE ACCESS (FULL) OF '현금거래내역' (Cost=34K Card=l Bytes=58)
          VIEW (Cost=50 Card=833K Bytes=13M)
           TABLE ACCESS (FULL) OF 'SYS.SYS_TEMP_OFD9D6B4E_4COC42BA' -- 임시 테이블 사용
        SORT (GROUP BY) (Cost=2K Card=745 Bytes=38K)
         HASH JOIN (Cost=2K Card=746 Bytes=38K)
..............
..............
..............

추가 참고
- http://wiki.gurubee.net/pages/viewpage.action?pageId=3899396#9.%EC%BF%BC%EB%A6%AC%EB%B3%80%ED%99%98-3.%EC%84%9C%EB%B8%8C%EC%BF%BC%EB%A6%AC%ED%8C%A9%ED%86%A0%EB%A7%81

문서에 대하여

최초작성자 : ~xsoft
최초작성일 : 2013년 11월 30일
이 문서는 오라클클럽 오라클 성능 트러블슈팅 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 한국 데이터베이스 진흥원에서 출간한 'SQL 전문가 가이드'를 참고하였습니다.*

1.고급 SQL 활용 (by xsoft) [2013.11.13]

목차

1. CASE문 활용

가) 1차 변환

나) 2차 변환

다) 3차 변환(Oracle-11g Pivot 사용)

2. 데이터 복제 기법 활용

가) CONNECT BY를 이용한 데이터 복제 방법

나) 실 예제

3. Union All을 활용한 M_M 관계의 조인

4. 페이징 처리

가) 일반적인 페이징 처리용 SQL

나) 뒤쪽 페이지까지 자주 조회할 때

1) 다음 페이지를 클릭할 경우 SQL

1. 페이징 처리에 대한 의문?

1) 인덱스 설계를 페이징 처리를 위해 설계?

2) 만약 여러 페이지를 건너뛰면?

2. 페이징 처리에 대한 의견

1) 페이징 처리를 위한 인덱스 설계

2) 여러 페이지를 건너뛸 경우?

2) 이전 페이지를 클릭할 경우 SQL

3) SQL 서버에서 구현 시 SQL

다) Union All 활용

5. 윈도우 함수 활용

1) SQL_1

2) SQL_2

3) SQL_3

6. With 구문 활용

문서에 대하여