[사례연구 22] 잘못된 아우터 조인에서 비롯된 NULL 처리

----------------------------------------------------
IN절과 SUBSQL 와 비교하는 컬럼에는 (+)를 붙일 수 없다

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

- 원하는 결과 (Page 204)

 READING_ID    YMD   VALUE   REVISION_NO
   100      20040510   108         3
   101      20040502   110         0
   102      20040509   110         1
   103      20040508   212         2
   104      20040504   150         0

Page 205 : SQL#22_01

SELECT  A.READING_ID, NVL(B.YMD, A.YMD),
        NVL(B.VALUE, A.VALUE), NVL(B.REVISION_NO, 0)
  FROM  EC_S_READING  A,  EC_S_READING_REV  B
 WHERE  A.READING_ID  =  B.READING_ID(+)
   AND  B.READING_ID||B.REVISION_NO
        IN (SELECT  READING_ID||MAX (REVISION_NO)
              FROM  EC_S_READING_REV
             GROUP BY  READING_ID);

READI NVL(B.YM NVL(B.VALUE,A.VALUE) NVL(B.REVISION_NO,0)
----- -------- -------------------- --------------------
100   20040510                  108                    3
102   20040509                  110                    1
103   20040508                  212                    2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          7          8           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.01          0          7          8           3

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      3   MERGE JOIN
      9    SORT (JOIN)
      8     NESTED LOOPS (OUTER)
      6      TABLE ACCESS (FULL) OF 'EC_S_READING'
      6      TABLE ACCESS (BY INDEX ROWID) OF 'EC_S_READING_REV'
     11       INDEX (RANGE SCAN) OF 'EC_S_READING_REV_PK' (UNIQUE)
      3    SORT (JOIN)
      3     VIEW OF 'VW_NSO_1'
      3      SORT (UNIQUE)
      3       SORT (GROUP BY)
      6        TABLE ACCESS (FULL) OF 'EC_S_READING_REV'

Q.위 SQL의 실행으로 원하는 결과가 추출됩니까?

  --> 101 , 104에 대한 건수(0건)을 표현하지 못함

SELECT  A.READING_ID, NVL(B.YMD, A.YMD),
        NVL(B.VALUE, A.VALUE), NVL(B.REVISION_NO, 0)
  FROM  EC_S_READING  A,  EC_S_READING_REV  B
 WHERE  A.READING_ID  =  B.READING_ID(+);
READI NVL(B.YM NVL(B.VALUE,A.VALUE) NVL(B.REVISION_NO,0)
----- -------- -------------------- --------------------
100   20040504                  102                    1
100   20040506                  110                    2
100   20040510                  108                    3
101   20040502                  110                    0
102   20040509                  110                    1
103   20040503                  205                    1
103   20040508                  212                    2
104   20040504                  150                    0

8 rows selected.

SELECT  READING_ID||MAX (REVISION_NO)
              FROM  EC_S_READING_REV
             GROUP BY  READING_ID;

READING_ID||MAX(REVISION_NO)
---------------------------------------------
1003
1021
1032

--> AND  B.READING_ID||B.REVISION_NO
    이부분 때문에 조회건수가 줄어듬
    null을 차지 못함(건수가 없는건)

Page 206 : SQL#22_02

SELECT  A.READING_ID, NVL(B.YMD, A.YMD),
        NVL(B.VALUE, A.VALUE), NVL(B.REVISION_NO, 0)
  FROM  EC_S_READING  A,  EC_S_READING_REV  B
 WHERE  A.READING_ID  =  B.READING_ID(+)
   AND  (B.READING_ID||B.REVISION_NO IS NULL  OR
         B.READING_ID||B.REVISION_NO
         IN  (SELECT  READING_ID||MAX( REVISION_NO)
           FROM  EC_S_READING_REV
                        GROUP BY  READING_ID));

READI NVL(B.YM NVL(B.VALUE,A.VALUE) NVL(B.REVISION_NO,0)
----- -------- -------------------- --------------------
100   20040510                  108                    3
101   20040502                  110                    0
102   20040509                  110                    1
103   20040508                  212                    2
104   20040504                  150                    0

==> data는 원하는 결과가 나오나
    잘못된 (+) 조인때문에 null을 이용하여 해결했다
    null사용은 좋치 않는 방법이다

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         14         28           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.00          0         14         28           5

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      5   FILTER
      9    NESTED LOOPS (OUTER)
      6     TABLE ACCESS (FULL) OF 'EC_S_READING'
      6     TABLE ACCESS (BY INDEX ROWID) OF 'EC_S_READING_REV'
     11      INDEX (RANGE SCAN) OF 'EC_S_READING_REV_PK' (UNIQUE)
      6    FILTER
     18     SORT (GROUP BY)
     36      TABLE ACCESS (FULL) OF 'EC_S_READING_REV'

---------------------------------------------------
문제점 : 1) Nested 서브쿼리가 먼저 실행되어야 하나 나중에 실행되고 있음
            => 서브 쿼리의 반복실행을 뜻함
         2) EC_S_READING_REV table의 반복된 사용

문제점 : 1) Nested 서브쿼리가 먼저 실행되어야 하나 나중에 실행되고 있음
            => 서브 쿼리의 반복실행을 뜻함

Anser: 서브쿼리의 선 수행을 위해 join을 확인함

Page 207 : SQL#22_03

/*---join으로 서브쿼리의 반복 사용 없앰-------------------*/

SELECT  B1.READING_ID, B1.REVISION_NO, B1.YMD, B1.VALUE
           FROM  EC_S_READING_REV  B1,
                 (SELECT  READING_ID, MAX(REVISION_NO) AS REVISION_NO
                    FROM  EC_S_READING_REV
                   GROUP BY  READING_ID) B2
          WHERE  B1.READING_ID = B2.READING_ID
            AND  B1.REVISION_NO = B2.REVISION_NO

READI REVISION_NO YMD           VALUE
----- ----------- -------- ----------
100             3 20040510        108
102             1 20040509        110
103             2 20040508        212

SELECT  A.READING_ID,  NVL(B.YMD, A.YMD),
 NVL(B.VALUE, A.VALUE), NVL(B.REVISION_NO, 0)
  FROM  EC_S_READING  A,
        (SELECT  B1.READING_ID, B1.REVISION_NO, B1.YMD, B1.VALUE
           FROM  EC_S_READING_REV  B1,
                 (SELECT  READING_ID, MAX(REVISION_NO) AS REVISION_NO
                    FROM  EC_S_READING_REV
                   GROUP BY  READING_ID) B2
          WHERE  B1.READING_ID = B2.READING_ID
            AND  B1.REVISION_NO = B2.REVISION_NO)  B
 WHERE  A.READING_ID  =  B.READING_ID(+);

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          7          8           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0          7          8           5

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      5   MERGE JOIN (OUTER)
      6    SORT (JOIN)
      5     TABLE ACCESS (FULL) OF 'EC_S_READING'
      3    SORT (JOIN)
      3     VIEW
      3      NESTED LOOPS
      4       VIEW
      4        SORT (GROUP BY)
      6         TABLE ACCESS (FULL) OF 'EC_S_READING_REV'
      3       TABLE ACCESS (BY INDEX ROWID) OF 'EC_S_READING_REV'
      6        INDEX (UNIQUE SCAN) OF 'EC_S_READING_REV_PK' (UNIQUE)

/*---self - join을 없애기 위해 분석 함수 이용-------------------*/

Page 208 : SQL#22_04

SELECT  A.READING_ID,  NVL(B.YMD, A.YMD),
 NVL(B.VALUE, A.VALUE), NVL(B.REVISION_NO, 0)
  FROM  EC_S_READING  A,
        (SELECT READING_ID, REVISION_NO, YMD, VALUE
           FROM (SELECT READING_ID, REVISION_NO, YMD, VALUE,
                        MAX(REVISION_NO)
                        OVER(PARTITION BY READING_ID) AS MAX_REVISION_NO
                   FROM EC_S_READING_REV)
          WHERE REVISION_NO = MAX_REVISION_NO) B
  WHERE A.READING_ID  =  B.READING_ID(+);

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          2          8           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0          2          8           5

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      5   MERGE JOIN (OUTER)
      6    SORT (JOIN)
      5     TABLE ACCESS (FULL) OF 'EC_S_READING'
      3    SORT (JOIN)
      3     VIEW
      6      WINDOW (SORT)
      6       TABLE ACCESS (FULL) OF 'EC_S_READING_REV'

/***********************다른 분석 함수 이용

1) FIRST_VALUE() OVER() 또는 LAST_VALUE() OVER()

SELECT  A.READING_ID,  NVL(B.YMD, A.YMD),
 NVL(B.VALUE, A.VALUE), NVL(B.REVISION_NO, 0)
  FROM  EC_S_READING  A,
        (SELECT READING_ID, REVISION_NO, YMD, VALUE
           FROM (SELECT READING_ID, REVISION_NO, YMD, VALUE,
                        FIRST_VALUE(REVISION_NO)
                        OVER(PARTITION BY READING_ID
                             ORDER BY REVISION_NO DESC
                             ROWS BETWEEN UNBOUNDED PRECEDING
                                     AND  CURRENT ROW) AS MAX_REVISION_NO
                   FROM EC_S_READING_REV)
          WHERE REVISION_NO = MAX_REVISION_NO) B
  WHERE A.READING_ID  =  B.READING_ID(+);

SELECT  A.READING_ID,  NVL(B.YMD, A.YMD),
 NVL(B.VALUE, A.VALUE), NVL(B.REVISION_NO, 0)
  FROM  EC_S_READING  A,
        (SELECT READING_ID, REVISION_NO, YMD, VALUE
           FROM (SELECT READING_ID, REVISION_NO, YMD, VALUE,
                        LAST_VALUE(REVISION_NO)
                        OVER(PARTITION BY READING_ID
                             ORDER BY REVISION_NO
                             ROWS BETWEEN CURRENT ROW
                                     AND  UNBOUNDED FOLLOWING) AS MAX_REVISION_NO
                   FROM EC_S_READING_REV)
          WHERE REVISION_NO = MAX_REVISION_NO) B
  WHERE A.READING_ID  =  B.READING_ID(+);

[사례연구 21] 배타적 관계성을 지닌 테이블 간의 조인

 테이블 상호간에 배타적(EXCLUSIVE) 관계성을 지니고 있을
경우 이들 테이블 간 조인에 있어 Outer 조인의 활용방안을
찾고자 합니다.

참고 : Page 199 (배타적 관계)

- 요구사항 (Page 200)

Page 201 : SQL#21_01

Q.아래의 두 SQL의 경우 원하는 결과가 추출됩니까?

SELECT   A.ACCOUNT_NO, A.OPEN_YMD, B.NAME, C.NAME
  FROM   EC_ACCOUNT A, EC_PERSONAL B, EC_COMPANY C
 WHERE   A.OPEN_YMD LIKE '200403%'
   AND   A.CUSTOMER_ID = B.CUSTOMER_ID
   AND   A.CUSTOMER_ID = C.CUSTOMER_ID
 ORDER BY A.OPEN_YMD, A.ACCOUNT_NO DESC;

SELECT   A.ACCOUNT_NO, A.OPEN_YMD, B.NAME, C.NAME
  FROM   EC_ACCOUNT A, EC_PERSONAL B, EC_COMPANY C
 WHERE   A.OPEN_YMD LIKE '200403%'
   AND   (A.CUSTOMER_ID = B.CUSTOMER_ID
          OR
          A.CUSTOMER_ID = C.CUSTOMER_ID)
 ORDER BY A.OPEN_YMD, A.ACCOUNT_NO DESC;

①의 경우    …
      A.CUSTOMER_ID = B.CUSTOMER_ID 와
      A.CUSTOMER_ID = C.CUSTOMER_ID 를 동시에 만족하는 데이터
      가 없기 때문에 결과가 한 건도 조회되질 않습니다.
      (B와 C가 배타적이므로 동시 동일한 ID존재하지 않는다)

 ②의 경우   …
       A.CUSTOMER_ID = B.CUSTOMER_ID 또는
       A.CUSTOMER_ID = C.CUSTOMER_ID 라 했을 때 처리 결과는
       (A.CUSTOMER_ID = B.CUSTOMER_ID) 의 처리 결과가 법인고객(C)
       의 로우(rows) 만큼의 카테시안 곱(Cartesian Product) 과
       (A.CUSTOMER_ID = C.CUSTOMER_ID) 의 처리 결과가 개인고객(B)
       의 로우(rows) 만큼의 카테시안 곱(Cartesian Product) 의 합한 결과
       가 조회됩니다.

/* OR를 풀어보면 아래와 같은 의미 : 각각 C, B를 사용안함으로
   Cartesian Product이됨  */

SELECT   A.ACCOUNT_NO, A.OPEN_YMD, B.NAME, C.NAME
  FROM   EC_ACCOUNT A, EC_PERSONAL B, EC_COMPANY C
 WHERE   A.OPEN_YMD LIKE '200403%'
   AND   A.CUSTOMER_ID = B.CUSTOMER_ID
UNION ALL
SELECT   A.ACCOUNT_NO, A.OPEN_YMD, B.NAME, C.NAME
  FROM   EC_ACCOUNT A, EC_PERSONAL B, EC_COMPANY C
 WHERE   A.OPEN_YMD LIKE '200403%'
   AND   A.CUSTOMER_ID = C.CUSTOMER_ID
 ORDER BY 1, 2 DESC;

Page 202 : SQL#21_02

SELECT   A.ACCOUNT_NO, A.OPEN_YMD, B.NAME, C.NAME
  FROM   EC_ACCOUNT A, EC_PERSONAL B, EC_COMPANY C
 WHERE   A.OPEN_YMD LIKE '200403%'
   AND   A.CUSTOMER_ID = B.CUSTOMER_ID(+)
   AND   A.CUSTOMER_ID = C.CUSTOMER_ID(+)
 ORDER BY A.OPEN_YMD, A.ACCOUNT_NO DESC;

SELECT   A.ACCOUNT_NO, A.OPEN_YMD, B.NAME, C.NAME
  FROM   EC_ACCOUNT A, EC_PERSONAL B, EC_COMPANY C
 WHERE   A.OPEN_YMD LIKE '200403%'
   AND   DECODE(A.CUSTOMER_GBN,'1',A.CUSTOMER_ID) = B.CUSTOMER_ID(+)
   AND   DECODE(A.CUSTOMER_GBN,'2',A.CUSTOMER_ID) = C.CUSTOMER_ID(+)
 ORDER BY A.OPEN_YMD, A.ACCOUNT_NO DESC;

Page 203 : SQL#21_03

SELECT   A.ACCOUNT_NO, A.OPEN_YMD, B.NAME, C.NAME
  FROM   EC_ACCOUNT01 A, EC_PERSONAL B, EC_COMPANY C
 WHERE   A.OPEN_YMD LIKE '200403%'
   AND   A.CUSTOMER_ID1 = B.CUSTOMER_ID(+)
   AND   A.CUSTOMER_ID2 = C.CUSTOMER_ID(+)
 ORDER BY A.OPEN_YMD, A.ACCOUNT_NO DESC;

[사례연구 20] 서브쿼리에서 IN 과 EXISTS 의 동시 사용

 서브쿼리는 조인과 더불어 자주 사용되고 있습니다.

 현재 오라클 기반에서 사용할 수 있는 서브쿼리의 종류로는
Nested 서브쿼리, Correlated 서브쿼리, Inline view,
Scalar 서브쿼리 등을 열거해 볼 수 있습니다.
 
 흔히 이들 서브쿼리의 용도를 생각해 볼 때, 조인에 대한 대안으로써
채택할 수 있을지 없을지를 가늠해야 합니다. 물론 역으로 서브쿼리에
대한 대안으로써 조인을 채택할 수 있을런지도 생각해 볼 수 있습니다.
 

 本 사례에서는 요구사항의 특성으로 인해 SQL 의 WHERE 절에 IN 과
EXISTS 를 동시에 사용할 수 밖에 없는 경우를 제시하면서 이럴 경우
에 효과적으로 처리할 수 있는 방안을 찾아 보고자 합니다.

인덱스정보
 EC_COURSE_PK : COURSE_CODE
        EC_APPLY_PK  : COURSE_CODE + YEAR + COURSE_SQ_NO
                       + MEMBER_TYPE + MEMBER_ID

=============================================================
IN 을 사용하는 Sub query (Nested Subquery)
EISTS를 사용하는 Sub query (Correlated Subquery)

-- Main query의 조건이 없는 Subquery가 먼저 실행됨

여기서는 IN(Subquery먼저실행) , EXISTS(Main이 먼저실행)
되는 것이 or로 되어 있어 optimizer도 어느 것을 먼저
해결할지 몰라 수행 조건이 매우 나쁨

이처럼 서로다른 우선순위를 갖는 Subsquery가 있는 경우는
반드시 듀팅해야 한다.

SQL > SET AUTOTRACE TRACEONLY EXPLAIN : 실행문만 보고자 할때
===============================================================

- 요구사항

  전체 과정(COURSE_CODE)에 대하여 2000년에 신청자가 전혀
  없거나 500 명 미만인 과정에 대해 과정코드와 과정명을 조
  회하고자 합니다.

Page 193 : SQL#20_01

SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A
 WHERE A.COURSE_CODE IN (SELECT COURSE_CODE
                           FROM EC_APPLY
                          WHERE YEAR = '2000'
                          GROUP BY COURSE_CODE
                          HAVING COUNT(COURSE_CODE) < 500)
    OR NOT EXISTS (SELECT 'X'
                     FROM EC_APPLY C
                    WHERE C.COURSE_CODE = A.COURSE_CODE
                      AND C.YEAR = '2000');

예상 실행 회수 : 101,033,865
SQL> SELECT COUNT(*) FROM EC_APPLY WHERE YEAR='2000';

  COUNT(*)
----------
     59607

SQL> SELECT COUNT(*) FROM EC_COURSE;

  COUNT(*)
----------
      1695

SQL> SELECT 59607 * 1695 FROM DUAL;

59607*1695
----------
 101033865

SQL > SET AUTOTRACE TRACEONLY EXPLAIN

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'EC_COURSE'
   3    1     FILTER <-----------------------having절에 대한건
   4    3       SORT (GROUP BY)
   5    4         TABLE ACCESS (FULL) OF 'EC_APPLY'
   6    1     INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE)

---> 문제점 : 1) Nested subquery가 나중에 실행됨으로서
                수많은 데이터를 처리하게 되었음
                특히 Subquery의 반복실행시 index사용을
                못하고 있음 <-- OR 때문에

              2) 각 유형별 Subquery에서 EC_APPLY에 대한
                 반복된 사용이 있음

SQL> set autotrace off

Page 194 : SQL#20_02
  --> OR의 기능을 제거함
  --> OR를 UNION ALL로 처리함
      sql이 개별적으로처리 됨으로

SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A
 WHERE A.COURSE_CODE IN (SELECT COURSE_CODE
                           FROM EC_APPLY
                          WHERE YEAR = '2000'
                          GROUP BY COURSE_CODE
                          HAVING COUNT(COURSE_CODE) < 500)
UNION ALL
SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A
  WHERE NOT EXISTS (SELECT 'X'
                      FROM EC_APPLY C
                     WHERE C.COURSE_CODE = A.COURSE_CODE
                       AND C.YEAR = '2000');

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      113      2.07       2.09      12392      19495          8        1674
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      115      2.08       2.09      12392      19495          8        1674

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
   1674   UNION-ALL
    387    NESTED LOOPS
    408     VIEW OF 'VW_NSO_1'
    408      FILTER
    429       SORT (GROUP BY)
  59607        TABLE ACCESS (FULL) OF 'EC_APPLY'
    387     TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE'
    794      INDEX (UNIQUE SCAN) OF 'EC_COURSE_PK' (UNIQUE)
   1287    FILTER
   1696     TABLE ACCESS (FULL) OF 'EC_COURSE'
   1695     INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE)

Q.위 SQL의 문제점과 해결방안은 무엇입니까? (참조  Page 196)

인덱스를 사용할수 있도록 함

SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A
 WHERE A.COURSE_CODE IN (SELECT COURSE_CODE
                           FROM EC_APPLY B
                          WHERE B.COURSE_CODE = A.COURSE_CODE
                            AND B.YEAR = '2000'
                          GROUP BY COURSE_CODE
                          HAVING COUNT(COURSE_CODE) < 500)
UNION ALL
SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A
  WHERE NOT EXISTS (SELECT 'X'
                      FROM EC_APPLY C
                     WHERE C.COURSE_CODE = A.COURSE_CODE
                       AND C.YEAR = '2000');

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      113      0.53       3.35        326      10734          8        1674
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      115      0.54       3.36        326      10734          8        1674

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
   1674   UNION-ALL
    387    FILTER     <---------------------------SUBSQL의 반복
   1696     TABLE ACCESS (FULL) OF 'EC_COURSE'
   1695     FILTER    <---------------------------HAVING절
   1716      SORT (GROUP BY)
  59518       INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE)
   1287    FILTER
   1696     TABLE ACCESS (FULL) OF 'EC_COURSE'
   1695     INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE)

-->서브쿼리의 반복된 실행을 제외하려면?
   Join을 활용하면 가능할것 같다

 SELECT A.COURSE_CODE, A.COURSE_NAME
   FROM EC_APPLY B, EC_COURSE A
  WHERE  B.COURSE_CODE = A.COURSE_CODE
    AND B.YEAR = '2000'
  GROUP BY A.COURSE_CODE, A.COURSE_NAME
 HAVING COUNT(B.COURSE_CODE) < 500
UNION ALL
SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A
  WHERE NOT EXISTS (SELECT 'X'
                      FROM EC_APPLY C
                     WHERE C.COURSE_CODE = A.COURSE_CODE
                       AND C.YEAR = '2000');

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      113      0.64       0.83         78       9018          8        1674
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      115      0.65       0.83         78       9018          8        1674

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
   1674   UNION-ALL
    387    FILTER
    409     SORT (GROUP BY)
  59518      NESTED LOOPS
   1696       TABLE ACCESS (FULL) OF 'EC_COURSE'
  59518       INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE)
   1287    FILTER
   1696     TABLE ACCESS (FULL) OF 'EC_COURSE'
   1695     INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE)

--> 테이블에 대한 반복사용 해결

SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A,
       (SELECT COURSE_CODE, COUNT(COURSE_CODE) CNT
          FROM EC_APPLY B
         WHERE COURSE_CODE > 0
           AND YEAR = '2000'
         GROUP BY B.COURSE_CODE) D
  WHERE A.COURSE_CODE = D.COURSE_CODE(+) <-- Null을 찾기위해
    AND (D.CNT < 500 OR D.CNT IS NULL);

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch      114      1.67       2.41       1635       2638          4        1695
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      116      1.67       2.42       1635       2638          4        1695

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
   1695   MERGE JOIN (OUTER)
   1696    SORT (JOIN)
   1695     TABLE ACCESS (FULL) OF 'EC_COURSE'
    408    SORT (JOIN)
    428     VIEW
    428      SORT (GROUP BY)
  59607       INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE)

Page 197 : SQL#20_03

SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A,
       (SELECT B.COURSE_CODE, COUNT(B.COURSE_CODE) CNT
          FROM EC_APPLY B, EC_COURSE C
         WHERE B.COURSE_CODE = C.COURSE_CODE
           AND B.YEAR = '2000'
         GROUP BY B.COURSE_CODE) D
  WHERE A.COURSE_CODE = D.COURSE_CODE(+)
    AND (D.CNT < 500 OR D.CNT IS NULL);

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      113      0.55       0.94        191       3849          8        1674
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      115      0.56       0.95        191       3849          8        1674

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
   1674   FILTER
   1695    MERGE JOIN (OUTER)
   1696     SORT (JOIN)
   1695      TABLE ACCESS (FULL) OF 'EC_COURSE'
    408     SORT (JOIN)
    408      VIEW
    408       SORT (GROUP BY)
  59518        NESTED LOOPS
   1696         TABLE ACCESS (FULL) OF 'EC_COURSE'
  59518         INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE)

--------------------------------------------------------
SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A,
       (SELECT B.COURSE_CODE, COUNT(B.COURSE_CODE) CNT
          FROM EC_APPLY B, EC_COURSE C
         WHERE B.COURSE_CODE = C.COURSE_CODE
           AND B.YEAR = '2000'
         GROUP BY B.COURSE_CODE) D
  WHERE A.COURSE_CODE = D.COURSE_CODE(+)
    AND D.CNT(+) < 500 ;

이렇게 되면
AND (D.CNT < 500 OR D.CNT IS NULL);
이렇게 했을때 보다 원하지 않는 결과를 더 갖고 오게된다.

이렇게 되면
AND (D.CNT < 500 OR D.CNT IS NULL);
이렇게 했을때 보다 원하지 않는 결과를 더 갖고 오게된다.

AND D.CNT(+) < 500 ; 의 의미는
500보다 작은 모든 것은 다 나오는 것 까지는 좋으나
A.COURSE_CODE = D.COURSE_CODE(+) 이존건때문에
COURSE_CODE 때문에 상관없는 500보다 작은 모든 건이된다

[사례연구 19] (코드+년도+월)별 및 (코드)별 그리고 전체총액을 한번에

  일상적으로 보면, 사용자가 원하는 각종 리포트에 있어서
 품목별 판매현황, 품목별 년도별 판매현황, 품목별 년도별
 월별 현황, 품목별 월별, etc 등 기타 다양한 기준 하에서
 판매현황에 대한 요청을 하는 경우가 많이 있습니다.

 앞서 일부 사례에서 얘기한 바와 같이, 리포트 마다 다를 수는
 있겠지만 이러한 유형의 리포트에서 처리하고자 하는 데이터는
 그 양이 방대하며, 리포트 자체의 output의 특성과 기타 등등의
 이유로 말미암아 SQL로 구현해서 처리하기에는 현실적으로 어렵
 다고 생각하는 것이 현실입니다.

  本 사례에서는 이러한 유형의 사례 중 하나를 제시하면서
 어떻게 처리해야 속도향상을 얻을 수 있는지를 찾아 보고자
 합니다.

SQL> ANALYZE TABLE EC_APPLY DELETE STATISTICS;
SQL> column tot_amt format 999999999999

- 요구사항 (Page 183)

  2000년, 2001년도의 과정을 대상으로 과정의 년도별 월별 입금총액,
  과정별 입금총액, 그리고 전체 입금총액의 현황을 구하려고 합니다.

- 원하는 OUTPUT :

년도 : '2000' ~ '2001'

  과정코드      년도     월     입금총액
    
    14         2000     01     10000000
    14         2000     02      7500000
    14         2000     03     12000000

    14         2000     12     10000000
    14         2001     01     11000000
    14         2001     02      9000000
   
    14         2001     12     13000000
    14         소계           #########
    28         2000     01     10000000
    28         2000     02      7500000
    28         2000     03     12000000

    28         2000     12     10000000
    28         2001     01     11000000
    28         2001     02      9000000
   
    28         2001     12     13000000
    28         소계           #########
   총계                    ############

Page 184 : (SQL#19_01

SELECT DECODE(COURSE_CODE,999,'총계',COURSE_CODE) COURSE_CODE,
       DECODE(YEAR,'9999','소계',YEAR) YEAR, MONTH, TOT_AMT
  FROM (SELECT COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM') MONTH,
               SUM(DEPOSIT_AMOUNT) TOT_AMT
          FROM EC_APPLY
         WHERE COURSE_CODE < 100
           AND YEAR IN ('2001','2000')
           AND APPLY_DATE + 0 >= TO_DATE('20000101','YYYYMMDD')
           AND APPLY_DATE + 0 <  TO_DATE('20011231','YYYYMMDD') + 1
         GROUP BY COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM')
        UNION ALL
        SELECT COURSE_CODE, '9999' YEAR, NULL MONTH,
               SUM(DEPOSIT_AMOUNT) TOT_AMT
          FROM EC_APPLY
         WHERE COURSE_CODE < 100
           AND YEAR IN ('2001','2000')
           AND APPLY_DATE + 0 >= TO_DATE('20000101','YYYYMMDD')
           AND APPLY_DATE + 0 <  TO_DATE('20011231','YYYYMMDD') + 1
         GROUP BY COURSE_CODE
        UNION ALL
        SELECT 999 COURSE_CODE, NULL YEAR, NULL MONTH,
               SUM(DEPOSIT_AMOUNT) TOT_AMT
          FROM EC_APPLY
         WHERE COURSE_CODE < 100
           AND YEAR IN ('2001','2000')
           AND APPLY_DATE + 0 >= TO_DATE('20000101','YYYYMMDD')
           AND APPLY_DATE + 0 <  TO_DATE('20011231','YYYYMMDD') + 1)
 ORDER BY COURSE_CODE, YEAR, MONTH;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       21      2.96       8.17      18232      29970          0         286
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       23      2.97       8.18      18232      29970          0         286

Rows     Row Source Operation
-------  ---------------------------------------------------
    286  SORT ORDER BY
    286   VIEW
    286    UNION-ALL
    255     SORT GROUP BY
   4381      TABLE ACCESS BY INDEX ROWID EC_APPLY
  10077       INDEX RANGE SCAN (object id 3984)
     30     SORT GROUP BY
   4381      TABLE ACCESS BY INDEX ROWID EC_APPLY
  10077       INDEX RANGE SCAN (object id 3984)
      1     SORT AGGREGATE
   4381      TABLE ACCESS BY INDEX ROWID EC_APPLY
  10077       INDEX RANGE SCAN (object id 3984)

Q.위 SQL의 문제점은 무엇이며, 이에 대한 해결방안은 무엇 입니까?
  (참조 - Page 186 - Page 187)

   3개의 sql로 따로 돌려서 값을 처리하고 있음

Page 188 : SQL#19_02 (CARTESIAN PRODUCT 활용)

SELECT DECODE(COURSE_CODE,999,'총계',COURSE_CODE) COURSE_CODE,
       DECODE(YEAR,'9999','소계',YEAR) YEAR, MONTH, TOT_AMT
  FROM (SELECT DECODE(RN, 3, 999, COURSE_CODE) COURSE_CODE,
               DECODE(RN, 1, YEAR, 2, '9999', NULL) YEAR,
               DECODE(RN, 1, MONTH, NULL) MONTH,
               SUM(TOT_AMT) TOT_AMT
          FROM (SELECT COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM') MONTH,
                       SUM(DEPOSIT_AMOUNT) TOT_AMT
                  FROM EC_APPLY
                 WHERE COURSE_CODE < 100
                   AND YEAR IN ('2001','2000')
                   AND APPLY_DATE + 0 >= TO_DATE('20000101','YYYYMMDD')
                   AND APPLY_DATE + 0 <  TO_DATE('20011231','YYYYMMDD') + 1
                 GROUP BY COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM')) V1,
               (SELECT ROWNUM RN
                  FROM EC_COURSE
                 WHERE ROWNUM <= 3) V2
         GROUP BY DECODE(RN, 3, 999, COURSE_CODE),
                  DECODE(RN, 1, YEAR, 2, '9999', NULL),
                  DECODE(RN, 1, MONTH, NULL))
 ORDER BY COURSE_CODE, YEAR, MONTH;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       21      0.77       0.80       4858       9991          4         286
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       23      0.78       0.80       4858       9991          4         286

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
    286   SORT (ORDER BY)
    286    VIEW
    286     SORT (GROUP BY)
    765      NESTED LOOPS
      4       VIEW
      4        COUNT (STOPKEY)
      3         TABLE ACCESS (FULL) OF 'EC_COURSE'
    765       VIEW
    765        SORT (GROUP BY)
   4381         TABLE ACCESS (BY INDEX ROWID) OF 'EC_APPLY'
  10077          INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE)

-> 참조 : Page 190

9i에서부터 사용

SELECT COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM') MONTH,
       SUM(DEPOSIT_AMOUNT) "Total Amt"
  FROM EC_APPLY
 WHERE COURSE_CODE < 100
   AND YEAR IN ('2001','2000')
   AND APPLY_DATE + 0 >= TO_DATE('20000101','YYYYMMDD')
   AND APPLY_DATE + 0 <  TO_DATE('20011231','YYYYMMDD') + 1
 GROUP BY ROLLUP(COURSE_CODE,(YEAR,TO_CHAR(APPLY_DATE,'MM')));

9i : ROLLUP(COURSE_CODE,(YEAR,TO_CHAR(APPLY_DATE,'MM')));
8i : ROLLUP(COURSE_CODE, YEAR,TO_CHAR(APPLY_DATE,'MM'));

8i에서는 원치 않는 부분이 나오고 있음

COURSE_CODE YEAR MO  Total Amt
----------- ---- -- ----------
         69 2000 04      66000
         69 2000 05     132000
         69 2000        660000
         69             660000
         77 2000 01     249000
         77 2000 02     249000
         77 2000 03     415000
         77 2000 04     166000
         77 2000 06      83000
         77 2000       1162000 <== 필요 없는 부분
         77            1162000

COURSE_CODE YEAR MO  Total Amt
----------- ---- -- ----------
         78 2000 01      40000
         78 2000         40000
         78              40000
                     245937400

* 소계를 리포트의 중간이 아니고 과정별로 첫 행으로 나오게 하려면...
  그리고 각 과정별로 과정명을 나타내려면...

Page 191 : SQL#19_03

SELECT DECODE(COURSE_CODE,999,'총계',COURSE_CODE) COURSE_CODE,
       DECODE(YEAR,'0000','소계',YEAR) YEAR, MONTH, TOT_AMT
  FROM (SELECT DECODE(RN, 3, 999, COURSE_CODE) COURSE_CODE,
               DECODE(RN, 1, YEAR, 2, '0000', NULL) YEAR,
               DECODE(RN, 1, MONTH, NULL) MONTH,
               SUM(TOT_AMT) TOT_AMT
          FROM (SELECT COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM') MONTH,
                       SUM(DEPOSIT_AMOUNT) TOT_AMT
                  FROM EC_APPLY
                 WHERE COURSE_CODE < 100
                   AND YEAR IN ('2001','2000')
                   AND APPLY_DATE + 0 >= TO_DATE('20000101','YYYYMMDD')
                   AND APPLY_DATE + 0 <  TO_DATE('20011231','YYYYMMDD') + 1
                 GROUP BY COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM')) V1,
               (SELECT ROWNUM RN
                  FROM EC_COURSE
                 WHERE ROWNUM <= 3) V2
         GROUP BY DECODE(RN, 3, 999, COURSE_CODE),
                  DECODE(RN, 1, YEAR, 2, '0000', NULL),
                  DECODE(RN, 1, MONTH, NULL));

COURSE_CODE                              YEAR MO       TOT_AMT
---------------------------------------- ---- -- -------------
69                                       2000 04         66000
69                                       2000 05        132000
77                                       소계          1162000
77                                       2000 01        249000
77                                       2000 02        249000
77                                       2000 03        415000
77                                       2000 04        166000
77                                       2000 06         83000
78                                       소계            40000
78                                       2000 01         40000
총계                                                 245937400

Page 192 : SQL#19_04
- SQL#19_03 에 대해 과정명 처리를 추가한 것임

-- column course_name format a20;  문자열을 20BYTES로 보이기 위해

SELECT DECODE(B.COURSE_CODE,999,'총계',B.COURSE_CODE) COURSE_CODE,
       DECODE(B.YEAR,'0000',A.COURSE_NAME) COURSE_NAME,
       DECODE(B.YEAR,'0000','소계',B.YEAR) YEAR, B.MONTH, B.TOT_AMT
  FROM EC_COURSE A,
       (SELECT DECODE(RN, 3, 999, COURSE_CODE) COURSE_CODE,
               DECODE(RN, 1, YEAR, 2, '0000', NULL) YEAR,
               DECODE(RN, 1, MONTH, NULL) MONTH,
               SUM(TOT_AMT) TOT_AMT
          FROM (SELECT COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM') MONTH,
                       SUM(DEPOSIT_AMOUNT) TOT_AMT
                  FROM EC_APPLY
                 WHERE COURSE_CODE < 100
                   AND YEAR IN ('2001','2000')
                   AND APPLY_DATE + 0 >= TO_DATE('20000101','YYYYMMDD')
                   AND APPLY_DATE + 0 <  TO_DATE('20011231','YYYYMMDD') + 1
                 GROUP BY COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM')) V1,
               (SELECT ROWNUM RN
                  FROM EC_COURSE
                 WHERE ROWNUM <= 3) V2
         GROUP BY DECODE(RN, 3, 999, COURSE_CODE),
                  DECODE(RN, 1, YEAR, 2, '0000', NULL),
                  DECODE(RN, 1, MONTH, NULL)) B
 WHERE B.COURSE_CODE = A.COURSE_CODE(+);

Outer join을 한 이유  총계를 표현하는 부분을 처리하기 위해
Outer join을 하지 않으면 총계 부분은 누락된다

COURSE_CODE          COURSE_NAME    YEAR MO       TOT_AMT
-------------------- -------------- ---- -- -------------

69                                  2000 01
69                                  2000 02        132000
69                                  2000 03        330000
69                                  2000 04         66000
69                                  2000 05        132000
77                   정보검색사     소계          1162000
77                                  2000 01        249000
77                                  2000 02        249000
77                                  2000 03        415000
77                                  2000 04        166000

COURSE_CODE          COURSE_NAME    YEAR MO       TOT_AMT
-------------------- -------------- ---- -- -------------
77                                  2000 06         83000
78                   중학글쓰기학습 소계            40000
78                                  2000 01         40000
총계                                            245937400

[사례연구 18] 년간 매출액을 주(週) 단위로 구분 처리

  本 사례에서는 특정 년도에 대한 매출액을 한주 단위로 구분
 처리하고자 할 때, 주에 대한 기준을 어떻게 하느냐에 따라서
 무엇이 달라지며 그에 따른 고려할 사항을 찾아 보고자 합니다.

Page 179 :

년 : '2004
  SUN   MON   TUE   WED   THU   FRI   SAT 
 1주                             01/01 01/02 01/03
        2주     01/04 01/05 01/06 01/07 01/08 01/09 01/10
        3주     01/11 01/12 01/13 01/14 01/15 01/16 01/17
        4주     01/18 01/19 01/20 01/21 01/22 01/23 01/24
        5주     01/25 01/26 01/27 01/28 01/29 01/30 01/31
        6주     02/01 02/02 02/03 02/04 02/05 02/06 02/07
                                                        
       52주     12/19 12/20 12/21 12/22 12/23 12/24 12/25
       53주     12/26 12/27 12/28 12/29 12/30 12/31      

가정#1: 2004년 1월 1일 목요일 부터 그 다음주
        수요일까지를 1주로 한다.

가정#2: 2004년 1월 1일 목요일 부터 1월 4일
        까지를 첫째 주로 보고, 둘째 주부터는
        월요일 부터 일요일까지를 1주로 한다.
        --> 당해전도 값이 이듬해 첫 주 값으로 계산되는 경우가 발생한다

예 :

SELECT TO_CHAR(TO_DATE('20051231','YYYYMMDD'),'WW') AS WW,           -- 가정#1
       TO_CHAR(TO_DATE('20051231','YYYYMMDD'),'IW') AS IW FROM DUAL; -- 가정#2

WW IW
-- --
53 52

SELECT TO_CHAR(TO_DATE('20071231','YYYYMMDD'),'WW') AS WW,           -- 가정#1
       TO_CHAR(TO_DATE('20071231','YYYYMMDD'),'IW') AS IW FROM DUAL; -- 가정#2
WW IW
-- --
53 01

Page 180 : SQL#18_01 (가정#1)

SELECT SUBSTR(YMD,1,6) MONTH,
       TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'WW') WEEK_GBN,
       SUM(SALES_AMT) MONTH_WEEK_TOT
  FROM EC_SALES
 WHERE YMD LIKE '2004%'
 GROUP BY SUBSTR(YMD,1,6),
          TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'WW');

  - 참조 : TO_CHAR(date,'WW') ->  1 ~ 52 또는 53이 리턴됨.

Page 181 : SQL#18_01 (가정#2)

SELECT SUBSTR(YMD,1,6) MONTH,
       TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'IW') WEEK_GBN,
       SUM(SALES_AMT) MONTH_WEEK_TOT
  FROM EC_SALES
 WHERE YMD LIKE '2004%'
 GROUP BY SUBSTR(YMD,1,6),
          TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'IW');

  - 참조 : TO_CHAR(date,'IW') ->  1 ~ 52가 리턴됨.

  - 문제점 : 12월 마지막 주에 대한 처리시 문제가 될 수 있음. (Page 181)
             즉, 53주로 처리되지 않고, 그 다음 해의 첫째 주로 처리됨.
             (2002,2007,2008년 등..)

Page 182 : SQL#18_02

SELECT SUBSTR(YMD,1,6) MONTH,
       DECODE(SUBSTR(YMD,1,6),'200412',
          DECODE(TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'IW'),'01','53',
                 TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'IW')),
              TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'IW'))  WEEK_GBN,
       SUM(SALES_AMT) MONTH_WEEK_TOT
  FROM EC_SALES
 WHERE YMD LIKE '2004%'
 GROUP BY SUBSTR(YMD,1,6),
          DECODE(SUBSTR(YMD,1,6),'200412',
             DECODE(TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'IW'),'01','53',
                    TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'IW')),
                 TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'IW'));

(참조) 

  IF 년월 < 2004년 12월 THEN
     주구분 = TO_CHAR(TO_DATE(일자,'YYYYMMDD'),'IW')
  ELSE IF TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'IW') = '01' THEN
          주구분 = '53'
  END IF;