꿈꾸는 개발자, DBA 커뮤니티 구루비

(1) 누적매출 구하기

월별지점매출
{code:sql} SQL> CREATE TABLE 월별지점매출 2 AS 3 SELECT deptno 지점 4 , ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY empno) 판매월 5 , sal 매출 6 FROM emp 7 ;

Table created.

SQL>

|

|| 지점별 누적매출 - 분석함수 ||
|{code:sql}
SQL> SELECT 지점, 판매월, 매출
  2       , SUM(매출) OVER(PARTITION BY 지점 ORDER BY 판매월) 누적매출
  3    FROM 월별지점매출
  4  ;

      지점     판매월       매출   누적매출
---------- ---------- ---------- ----------
        10          1       2450       2450
        10          2       5000       7450
        10          3       1300       8750
        20          1        800        800
        20          2       2975       3775
        20          3       3000       6775
        30          1       1600       1600
        30          2       1250       2850
        30          3       1250       4100
        30          4       2850       6950
        30          5       1500       8450
        30          6        950       9400

12 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1511517784

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |    12 |   468 |     3  (34)| 00:00:01 |
|   1 |  WINDOW SORT       |        |    12 |   468 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| 월별지 |    12 |   468 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        821  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         12  rows processed

SQL>

|

지점별 누적매출 - 분석함수 없이
{code:sql} SQL> SELECT a.지점, a.판매월, a.매출 2 , SUM(b.매출) 누적매출 3 FROM 월별지점매출 a 4 , 월별지점매출 b 5 WHERE a.지점 = b.지점 6 AND a.판매월 >= b.판매월 7 GROUP BY a.지점, a.판매월, a.매출 8 ORDER BY a.지점, a.판매월 9 ;

지점 판매월 매출 누적매출

--

--
10 1 2450 2450
10 2 5000 7450
10 3 1300 8750
20 1 800 800
20 2 2975 3775
20 3 3000 6775
30 1 1600 1600
30 2 1250 2850
30 3 1250 4100
30 4 2850 6950
30 5 1500 8450
30 6 950 9400

12 rows selected.

Execution Plan

--
Plan hash value: 3972213044

--

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

--

0	SELECT STATEMENT		2	156	6 (34)	00:00:01
1	SORT GROUP BY		2	156	6 (34)	00:00:01
2	HASH JOIN		2	156	5 (20)	00:00:01
3	TABLE ACCESS FULL	월별지	12	468	2 (0)	00:00:01
4	TABLE ACCESS FULL	월별지	12	468	2 (0)	00:00:01

--

Predicate Information (identified by operation id):

---

2 - access("A"."지점"="B"."지점")
filter("A"."판매월">="B"."판매월")

Note

-

dynamic sampling used for this statement (level=2)

Statistics

--
13 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
821 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
12 rows processed

SQL>

|

h2. 선분이력 끊기

그림2-25

|| 선분 이력을 월단위로 쪼개기 ||
|{code:sql}
SQL> WITH 선분이력 AS
  2  (
  3  SELECT 'A' cd, '2016-07-13' sdt, '2016-08-08' edt, 'A1' v FROM dual
  4  UNION ALL SELECT 'A', '2016-08-09', '2016-08-20', 'A2'  FROM dual
  5  UNION ALL SELECT 'A', '2016-08-21', '2016-10-07', 'A3'  FROM dual
  6  )
  7  , 월도 AS
  8  (
  9  SELECT TO_CHAR(ADD_MONTHS(ym, LEVEL)        , 'yyyy-mm'   ) ym
 10       , TO_CHAR(ADD_MONTHS(ym, LEVEL)        , 'yyyy-mm-dd') sdt
 11       , TO_CHAR(ADD_MONTHS(ym, LEVEL + 1) - 1, 'yyyy-mm-dd') edt
 12    FROM (SELECT TO_DATE('2016-06', 'yyyy-mm') ym FROM dual)
 13   CONNECT BY LEVEL <= 5
 14  )
 15  SELECT a.*, b.*
 16       , GREATEST(a.sdt, b.sdt) s
 17       , LEAST   (a.edt, b.edt) e
 18    FROM 선분이력 a
 19       , 월도 b
 20   WHERE a.sdt < b.edt
 21     AND b.sdt < a.edt
 22  ;

CD SDT          EDT          V    YM        SDT          EDT          S            E
-- ------------ ------------ ---- --------- ------------ ------------ ------------ ------------
A  2016-07-13   2016-08-08   A1   2016-07   2016-07-01   2016-07-31   2016-07-13   2016-07-31
A  2016-07-13   2016-08-08   A1   2016-08   2016-08-01   2016-08-31   2016-08-01   2016-08-08
A  2016-08-09   2016-08-20   A2   2016-08   2016-08-01   2016-08-31   2016-08-09   2016-08-20
A  2016-08-21   2016-10-07   A3   2016-08   2016-08-01   2016-08-31   2016-08-21   2016-08-31
A  2016-08-21   2016-10-07   A3   2016-09   2016-09-01   2016-09-30   2016-09-01   2016-09-30
A  2016-08-21   2016-10-07   A3   2016-10   2016-10-01   2016-10-31   2016-10-01   2016-10-07

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1217576180

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |     1 |    50 |     8   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |      |     1 |    50 |     8   (0)| 00:00:01 |
|   2 |   VIEW                         |      |     1 |    19 |     2   (0)| 00:00:01 |
|*  3 |    CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|*  5 |   VIEW                         |      |     1 |    31 |     6   (0)| 00:00:01 |
|   6 |    UNION-ALL                   |      |       |       |            |          |
|   7 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|   8 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|   9 |     FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(LEVEL<=5)
   5 - filter("A"."SDT"<"B"."EDT" AND "B"."SDT"<"A"."EDT")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1277  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          6  rows processed

SQL>

|

데이터 복제를 통한 소계 구하기

부서별 소계
{code:sql} SQL> column deptno format 9999 SQL> column empno format a10 SQL> break on deptno SQL> SELECT deptno 2 , DECODE(lv, 1, TO_CHAR(empno), 2, '소계') empno 3 , SUM(sal) sum_sal 4 , ROUND(AVG(sal)) avg_sal 5 FROM emp 6 , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 2) 7 GROUP BY deptno, DECODE(lv, 1, TO_CHAR(empno), 2, '소계') 8 ORDER BY deptno, empno 9 ;

DEPTNO EMPNO SUM_SAL AVG_SAL

--

--
10 7782 2450 2450
7839 5000 5000
7934 1300 1300
소계 8750 2917
20 7369 800 800
7566 2975 2975
7902 3000 3000
소계 6775 2258
30 7499 1600 1600
7521 1250 1250
7654 1250 1250
7698 2850 2850
7844 1500 1500
7900 950 950
소계 9400 1567

15 rows selected.

Execution Plan

--
Plan hash value: 1155168346

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0	SELECT STATEMENT		12	624	5 (20)	00:00:01
1	SORT GROUP BY		12	624	5 (20)	00:00:01
2	MERGE JOIN CARTESIAN		12	624	4 (0)	00:00:01
3	VIEW		1	13	2 (0)	00:00:01
4	CONNECT BY WITHOUT FILTERING
5	FAST DUAL		1		2 (0)	00:00:01
6	BUFFER SORT		12	468	5 (20)	00:00:01
7	TABLE ACCESS FULL	EMP	12	468	2 (0)	00:00:01

Predicate Information (identified by operation id):

---

4 - filter(LEVEL<=2)

Note

-

dynamic sampling used for this statement (level=2)

Statistics

--
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
923 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
15 rows processed

SQL>

|

|| 부서별 소계 및 총계 ||
|{code:sql}
SQL> SELECT DECODE(lv, 3, TO_NUMBER(''), deptno) deptno
  2       , DECODE(lv, 1, TO_CHAR(empno), 2, '소계', 3, '총계') empno
  3       , SUM(sal) sum_sal
  4       , ROUND(AVG(sal)) avg_sal
  5    FROM emp
  6       , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 3)
  7   GROUP BY DECODE(lv, 3, TO_NUMBER(''), deptno)
  8        , DECODE(lv, 1, TO_CHAR(empno), 2, '소계', 3, '총계')
  9   ORDER BY deptno, empno
 10  ;

DEPTNO EMPNO         SUM_SAL    AVG_SAL
------ ---------- ---------- ----------
    10 7782             2450       2450
       7839             5000       5000
       7934             1300       1300
       소계             8750       2917
    20 7369              800        800
       7566             2975       2975
       7902             3000       3000
       소계             6775       2258
    30 7499             1600       1600
       7521             1250       1250
       7654             1250       1250
       7698             2850       2850
       7844             1500       1500
       7900              950        950
       소계             9400       1567
       총계            24925       2077

16 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1155168346

----------------------------------------------------------------------------------------
| Id  | Operation                       | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |      |    12 |   624 |     5  (20)| 00:00:01 |
|   1 |  SORT GROUP BY                  |      |    12 |   624 |     5  (20)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN          |      |    12 |   624 |     4   (0)| 00:00:01 |
|   3 |    VIEW                         |      |     1 |    13 |     2   (0)| 00:00:01 |
|*  4 |     CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   5 |      FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |
|   6 |    BUFFER SORT                  |      |    12 |   468 |     5  (20)| 00:00:01 |
|   7 |     TABLE ACCESS FULL           | EMP  |    12 |   468 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(LEVEL<=3)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1008  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
         16  rows processed

SQL>

|

Rollup
{code:sql} SQL> SELECT deptno 2 , DECODE(GROUPING_ID(deptno, empno), 0, TO_CHAR(empno), 1, '소계', 3, '총계') empno 3 , SUM(sal) sum_sal 4 , ROUND(AVG(sal)) avg_sal 5 FROM emp 6 GROUP BY ROLLUP(deptno, empno) 7 ORDER BY deptno, empno 8 ;

DEPTNO EMPNO SUM_SAL AVG_SAL

--

--
10 7782 2450 2450
7839 5000 5000
7934 1300 1300
소계 8750 2917
20 7369 800 800
7566 2975 2975
7902 3000 3000
소계 6775 2258
30 7499 1600 1600
7521 1250 1250
7654 1250 1250
7698 2850 2850
7844 1500 1500
7900 950 950
소계 9400 1567
총계 24925 2077

16 rows selected.

Execution Plan

--
Plan hash value: 1419477654

--

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

--

0	SELECT STATEMENT		12	468	4 (50)	00:00:01
1	SORT ORDER BY		12	468	4 (50)	00:00:01
2	SORT GROUP BY ROLLUP		12	468	4 (50)	00:00:01
3	TABLE ACCESS FULL	EMP	12	468	2 (0)	00:00:01

--

Note

-

dynamic sampling used for this statement (level=2)

Statistics

--
4 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1041 bytes sent via SQL*Net to client
430 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
16 rows processed

SQL>

|

h2. (4) 상호배타적 관계의 조인

그림2-26

|| 1. 온라인권번호/실권번호 컬럼 두개 따로 - Outer Join ||
|{code:sql}
SELECT a.주문번호, a.결재일자, a.결재금액
     , NVL(b.온라인권번호, c.실권번호) 상품권번호
     , NVL(b.발행일시, d.발행일시) 발행일시
  FROM 상품권결재 a
     , 온라인권 b
     , 실권 c
     , 실권발행 d
 WHERE a.결재일자 BETWEEN :sdt AND :edt
   AND a.온라인권번호 = b.온라인권번호(+)
   AND a.실권번호 = c.실권번호(+)
   AND c.발행번호 = d.발행번호(+)
;

|

2. 상품권구분 에 따라 상품권번호 컬럼에 온라인권/실권번호 저장 - UNION ALL(중복 엑세스)

{code:sql}
SELECT a.주문번호, a.결재일자, a.결재금액
, a.상품권번호
, b.발행일시
FROM 상품권결재 a
, 온라인권 b
WHERE a.결재일자 BETWEEN :sdt AND :edt
AND a.상품권번호 = b.온라인권번호
AND a.상품권구분 = 1
UNION ALL
SELECT a.주문번호, a.결재일자, a.결재금액
, a.상품권번호
, d.발행일시
FROM 상품권결재 a
, 실권 c
, 실권발행 d
WHERE a.결재일자 BETWEEN :sdt AND :edt
AND a.상품권번호 = c.실권번호
AND c.발행번호 = d.발행번호
AND a.상품권구분 = 2
;
{code}

3. 상품권구분 에 따라 상품권번호 컬럼에 온라인권/실권번호 저장 - 중복 엑세스 해소

{code:sql}
SELECT a.주문번호, a.결재일자, a.결재금액
, a.상품권번호
, NVL(b.발행일시, d.발행일시) 발행일시
FROM 상품권결재 a
, 온라인권 b
, 실권 c
, 실권발행 d
WHERE a.결재일자 BETWEEN :sdt AND :edt
AND b.온라인권번호(+) = DECODE(a.상품권구분, 1, a.상품권번호)
AND c.실권번호(+) = DECODE(a.상품권구분, 2, a.상품권번호)
AND c.발행번호 = d.발행번호(+)
;
{code}

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

조인 후 페이징

{code:sql}
SELECT *
FROM (SELECT ROWNUM rn
, 등록일자, 번호, 제목, 회원명, 게시판유형명, 질문유형명
FROM (SELECT a.등록일자, a.번호, a.제목
, b.회원명
, c.게시판유형명
, d.질문유형명
FROM 게시판 a
, 회원 b
, 게시판유형 c
, 질문유형 d
WHERE a.게시판유형 = :v_type
AND a.회원번호 = b.회원번호
AND a.게시판유형 = c.게시판유형
AND a.질문유형 = d.질문유형
ORDER BY a.등록일자 DESC, a.번호
)
WHERE ROWNUM <= 30 -- 중요.
)
WHERE rn BETWEEN 21 AND 30
;
{code}

페이징 후 조인

{code:sql}
SELECT /*+ ORDERED USE_NL(b) USE_NL© USE_NL(d) ROWID(a) ? */
a.등록일자, a.번호, a.제목
, b.회원명
, c.게시판유형명
, d.질문유형명
FROM (SELECT rid
, ROWNUM rn
FROM (SELECT a.ROWID rid
FROM 게시판 a
WHERE a.게시판유형 = :v_type
ORDER BY a.등록일자 DESC, a.번호
) a
WHERE ROWNUM <= 30
) x
, 게시판 a
, 회원 b
, 게시판유형 c
, 질문유형 d
WHERE x.rn BETWEEN 21 AND 30
AND x.rid = a.ROWID
AND a.회원번호 = b.회원번호
AND a.게시판유형 = c.게시판유형
AND a.질문유형 = d.질문유형
ORDER BY a.등록일자, a.번호 -- 정렬 추가
;
{code}

반정규화는 최후의 수단

게시판의 댓글수, 수발신함의 발송건수/수신건수 등의 추출 속성
전체 자료 조회에 대한 서브쿼리 부담으로 반정규화를 수행
위처럼 조인 후에 페이징하여 최종 건에대해서만 서브쿼리 수행한다면? 반정규화는 불필요.

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

from절에 조인되는 테이블 개수를 늘려 성능을 향상시키는 사례( 교재 참고 page. 319~ )
1. 최종 결과 건수는 얼마 되지 않으면서, 필터 조건만으로 각 부분을 따로 읽으면 결과 건수가 아주 많으 때 튜닝하기가 어렵다.
2. 조인 테이블을 추가하여 인덱스만 읽도록 하고, 인덱스만 읽은 결과끼리 먼저 조인하고 최종 결과집합에 대해서만 테이블을 액세스하도록 한다.
3. 테이블을 액세스할 때는 추가적인 인덱스 탐색 없이 인덱스에서 읽은 rowid 값을 가지고 직접 액세스한다. ( 실행계획에 Table Access By User ROWID 라고 표시)

인조 식별자 사용에 의한 조인 성능 이슈 ( 교재 참고 page. 324~ )
1. 액세스 경로에 대한 고려 없이 인조 식별자를 설계하면, 조인 성능 이슈가 자주 발생한다.
2. 자식 테이블에 부모 테이블의 액세스 조건 컬럼이 상속되지 않고 인조 식별자 컬럼을 둘 경우 조인과정에 비 효율을 일으킨다.
3. 인조 식별자 설계가 필요할 경우 주요 액세스 경로에 따른 컬럼을 자식 테이블에 추가하여 반정규화를 고려한다.

인조 식별자를 둘 때 주의 사항
장점
1. 단일 컬럼으로 구성되므로 테이블 간 연결 구조가 단손해지고, 인덱스 저장공간이 최소화된다.
2. 다중 컬럼으로 조인할 때보다 조인 연산을 위한 CPU사용량이 조금 줄 수 있다.
단점
1. 조인 연산 횟수와 블록 I/O 증가로 시스템 리소스를 낭비한다.
2. 실질 식별자를 찾기 어려워 데이터 모델이 이해하기 어려워진다.
TIP
1. 논리적인 데이터 모델링 단계에서는 가급적 인조 식별자를 두지 않는 것이 좋다.
2. 의미상 주어에 해당하는 속성들을 식별자로 사용했다가 물리 설계 단계에서 저장 효율과 액세스 효율 등을 고려해서 결정한다.

(7) 점이력 조회

고객의 서비시만료일 이전 연체이력 자료중 가장 최신 변경일자의 연체금액 조회

1. Max 서브쿼리
{code:sql} SELECT a.고객명, a.거주지역, a.주소, a.연락처 , b.연체금액 FROM 고객 a , 고객별연체이력 b WHERE a.가입회사 = 'C70' AND a.고객번호 = b.고객번호 AND b.변경일자 = (SELECT MAX(변경일자) FROM 고객별연체이력 WHERE 고객번호 = a.고객번호 AND 변경일자 <= a.서비스만료일 ) ; {code}

2. Index Desc & Rownum = 1

{code:sql}
SELECT a.고객명, a.거주지역, a.주소, a.연락처
, b.연체금액
FROM 고객 a
, 고객별연체이력 b
WHERE a.가입회사 = 'C70'
AND a.고객번호 = b.고객번호
AND b.변경일자 = (SELECT /*+ INDEX_DESC(c 고객별연체이력_IDX01) */
변경일자
FROM 고객별연체이력 c
WHERE 고객번호 = a.고객번호
AND 변경일자 <= a.서비스만료일
AND ROWNUM <= 1
)
;
{code}

인덱스 구성이 변경되는 경우 힌트가 무효화 되어 결과가 틀릴 수 있다.
따라서 First Row Operation 이 동작하는 상황에서는 MIN/MAX 를 사용하는게 더 좋다.
First Row Operation 이 동작할 수 없는 상황에서 제한적으로 사용해야 한다.

3. 스칼라서브쿼리 : 고객연체이력 2번 읽는 비효율 제거
{code:sql} SELECT a.고객명, a.거주지역, a.주소, a.연락처 , (SELECT /+ INDEX_DESC(c 고객별연체이력_IDX01) / 연체금액 FROM 고객별연체이력 c WHERE 고객번호 = a.고객번호 AND 변경일자 <= a.서비스만료일 AND ROWNUM <= 1 ) 연체금액 FROM 고객 a WHERE a.가입회사 = 'C70' ; {code}

4. 조회 항목이 2개 이상이라면? : 하나로 합쳤다가 다시 쪼개기(번거로움)
{code:sql} SELECT a.고객명, a.거주지역, a.주소, a.연락처 , TO_NUMBER(SUBSTR(연체, 1, 2)) 연체개월수 , TO_NUMBER(SUBSTR(연체, 3 )) 연체금액 FROM (SELECT a.고객명, a.거주지역, a.주소, a.연락처 , (SELECT /+ INDEX_DESC(c 고객별연체이력_IDX01) / LPAD(연체개월수, 2, '0')	연체금액 FROM 고객별연체이력 c WHERE 고객번호 = a.고객번호 AND 변경일자 <= a.서비스만료일 AND ROWNUM <= 1 ) 연체 FROM 고객 a WHERE a.가입회사 = 'C70' ) a ; {code}

5. ROWID 이용

{code:sql}
SELECT /*+ ORDERED USE_NL(b) ROWID(b) */
a.고객명, a.거주지역, a.주소, a.연락처
, b.연체개월수
, b.연체금액
FROM (SELECT a.고객명, a.거주지역, a.주소, a.연락처
, (SELECT /*+ INDEX_DESC(c 고객별연체이력_IDX01) */
ROWID
FROM 고객별연체이력 c
WHERE 고객번호 = a.고객번호
AND 변경일자 <= a.서비스만료일
AND ROWNUM <= 1
) rid
FROM 고객 a
WHERE a.가입회사 = 'C70'
) a
, 고객별연체이력 b
WHERE b.ROWID = a.rid
;
{code}

6. ROWID 이용 : 스칼라서브쿼리 제거

{code:sql}
SELECT /*+ ORDERED USE_NL(b) ROWID(b) */
a.고객명, a.거주지역, a.주소, a.연락처
, b.연체개월수
, b.연체금액
FROM 고객 a
, 고객별연체이력 b
WHERE a.가입회사 = 'C70'
AND b.ROWID = (SELECT /*+ INDEX_DESC(c 고객별연체이력_IDX01) */
ROWID
FROM 고객별연체이력 c
WHERE 고객번호 = a.고객번호
AND 변경일자 <= a.서비스만료일
AND ROWNUM <= 1
)
;
{code}

정해진 시점 기준으로 조회
- 위와 같이 특정 고객을 대상으로 조회시엔 랜덤엑세스 방식을 이용했지만
- 아래와 같이 전체 고객을 대상으로 할 때는 랜덤엑세스 방식을 피해야 한다.

7. 전체 고객 대상 현시점 조회

{code:sql}
SELECT a.고객명, a.거주지역, a.주소, a.연락처
, c.연체개월수
, c.연체금액
FROM 고객 a
, (SELECT 고객번호
, MAX(변경일자) 변경일자
FROM 고객별연체이력
WHERE 변경일자 <= TO_CHAR(sysdate, 'yyyymmdd')
GROUP BY 고객번호
) b
, 고객별연체이력 c
WHERE a.고객번호 = b.고객번호
AND b.고객번호 = c.고객번호
AND b.변경일자 = c.변경일자
;
{code}

8. 연체 한번만 읽기 : 합쳤다가 쪼개기(번거로움)
{code:sql} SELECT a.고객명, a.거주지역, a.주소, a.연락처 , TO_NUMBER(SUBSTR(b.연체, 9, 2)) 연체개월수 , TO_NUMBER(SUBSTR(b.연체, 11 )) 연체금액 FROM 고객 a , (SELECT 고객번호 , MAX(변경일자	LPAD(연체개월수, 2, '0')	연체금액) 연체 FROM 고객별연체이력 WHERE 변경일자 <= TO_CHAR(sysdate, 'yyyymmdd') GROUP BY 고객번호 ) b WHERE a.고객번호 = b.고객번호 ; {code}

9. Row_number : Pushed Rank Operation
{code:sql} SELECT a.고객명, a.거주지역, a.주소, a.연락처 , b.연체개월수, b.연체금액 FROM 고객 a , (SELECT 고객번호, 연체개월수, 연체금액 , ROW_NUMBER() OVER(PARTITION BY 고객번호 ORDER BY 변경일자 DESC) rn FROM 고객별연체이력 WHERE 변경일자 <= TO_CHAR(sysdate, 'yyyymmdd') ) b WHERE a.고객번호 = b.고객번호 AND b.rn = 1 ; {code}

9. MAX() OVER() : Pushed Rank 동작 않함. Row_number 추천

{code:sql}
SELECT a.고객명, a.거주지역, a.주소, a.연락처
, b.연체개월수, b.연체금액
FROM 고객 a
, (SELECT 고객번호, 연체개월수, 연체금액
, 변경일자
, MAX(변경일자) OVER(PARTITION BY 고객번호) max_dt
FROM 고객별연체이력
WHERE 변경일자 <= TO_CHAR(sysdate, 'yyyymmdd')
) b
WHERE a.고객번호 = b.고객번호
AND b.변경일자 = b.max_dt
;
{code}

(8) 선분이력 조인

과거/현재/미래 임의 시점 조회
{code:sql} SELECT a.고객번호, a.고객명 , b.고객등급 , c.전화번호 FROM 고객 a , 고객등급변경이력 b , 전화번호변경이력 c WHERE a.고객번호 = 123 AND a.고객번호 = b.고객번호 AND a.고객번호 = c.고객번호 AND :v_dt BETWEEN b.시작일자 AND b.종료일자 AND :v_dt BETWEEN c.시작일자 AND c.종료일자 ; {code}

현재 시점 조회 : 종료일 이퀄 검색 - 미래 자료가 저장 가능하다면? Sysdate Between 검색
{code:sql} SELECT a.고객번호, a.고객명 , b.고객등급 , c.전화번호 FROM 고객 a , 고객등급변경이력 b , 전화번호변경이력 c WHERE a.고객번호 = 123 AND a.고객번호 = b.고객번호 AND a.고객번호 = c.고객번호 AND b.종료일자 = '99991231' AND c.종료일자 = '99991231' ; {code}

(9) 선분이력 조인 튜닝

조인 유형	데이터 상황	튜닝 방안
정해진 시점으로 선분이력과 단순 조인	조회대상이 많지 않을 때	인덱스 순서 조정
''	조회대상이 많을 때	해시조인 (대상별 이력 레코드가 많더라도 Full Scan 비용만 커질 뿐 조인 과정에서의 탐색 비효율은 없음)
Between 조인	조회대상이 많지 않을 때	스칼라서브쿼리 등에 Stopkey 조건을 사용해 인덱스 한건만 스캔하도록 구현
''	조회대상이 많지만 대상별 이력레코드가 많지 않을 때	해시 조인 (대상별 이력레코드가 많지 않아 해시 테이블 탐색비용이 크지 않음)
''	대상별 이력레코드가 많을 때	???

(10) 조인에 실패한 레코드 읽기

국가별 지역별 요금 조회 - 해당 지역 없을 땐 ' ' 지역 값 조회

{code:sql}
SQL> WITH 과금 AS
2 (
3 SELECT '82' 국가, 'A' 지역, 100 요금 FROM dual
4 UNION ALL SELECT '82', 'B', 200 FROM dual
5 UNION ALL SELECT '82', 'C', 300 FROM dual
6 UNION ALL SELECT '84', 'A', 100 FROM dual
7 UNION ALL SELECT '84', 'B', 200 FROM dual
8 UNION ALL SELECT '84', ' ', 300 FROM dual
9 UNION ALL SELECT '86', 'A', 100 FROM dual
10 UNION ALL SELECT '86', ' ', 300 FROM dual
11 )
12 , 통화 AS
13 (
14 SELECT '20160708 010101' 통화시간, '82' 국가, 'A' 지역 FROM dual
15 UNION ALL SELECT '20160708 020101', '82', 'B' FROM dual
16 UNION ALL SELECT '20160708 030101', '82', 'C' FROM dual
17 UNION ALL SELECT '20160708 040101', '84', 'A' FROM dual
18 UNION ALL SELECT '20160708 050101', '84', 'B' FROM dual
19 UNION ALL SELECT '20160708 060101', '84', 'C' FROM dual
20 UNION ALL SELECT '20160708 070101', '86', 'A' FROM dual
21 UNION ALL SELECT '20160708 080101', '86', 'B' FROM dual
22 UNION ALL SELECT '20160708 090101', '86', 'C' FROM dual
23 )
24 SELECT *
25 FROM 통화 a
26 , 과금 b
27 WHERE a.통화시간 LIKE '20160708%%%%%%%'
28 AND (b.국가, b.지역) = (SELECT a.국가, MAX(c.지역)
29 FROM 과금 c
30 WHERE c.국가 = a.국가
31 AND c.지역 IN (a.지역, ' ')
32 )
33 ;

통화시간 국가 지 국가 지 요금

--

--
20160708 010101 82 A 82 A 100
20160708 020101 82 B 82 B 200
20160708 030101 82 C 82 C 300
20160708 040101 84 A 84 A 100
20160708 050101 84 B 84 B 200
20160708 060101 84 C 84 300
20160708 070101 86 A 86 A 100
20160708 080101 86 B 86 300
20160708 090101 86 C 86 300

9 rows selected.

SQL>

08. 고급 조인 테크닉~ (by manon94) [2016.07.07]

(1) 누적매출 구하기

데이터 복제를 통한 소계 구하기

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

반정규화는 최후의 수단

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

(7) 점이력 조회

(8) 선분이력 조인

(9) 선분이력 조인 튜닝

(10) 조인에 실패한 레코드 읽기