오라클 성능 고도화 원리와 해법 II (2012년)
목록
고급 조인 테크닉 - 점이력 조회 0 0 28,978

by 구루비스터디 고급조인테크닉 점이력조회 점이력 [2023.10.01]

(7) 점이력 조회

  • 데이터 변경이 발생할 때마다 변경일자와 함께 새로운 이력 레코드를 쌓는 방식을 점이력이라고 함.



  • 점이력 모델에서 이력을 조회할 때 흔히 아래와 같이 서브쿼리를 이용함
  • 찾고자 하는 시점(서비스만료일) 보다 앞선 변경일자 중 가장 마지막 레코드를 찾는 것

select a.고객명, a.거주지역, a.주소, a.연락처, b.연체금액
from   고객 a, 고객별연체이력 b
where  a.가입회사 = 'C70'
and    b.고객번호 = a.고객번호
and    b.변경일자 = (select /*+ no_unnest */ max(변경일자)
                     from   고객별연체이력
                     where  고객번호 = a.고객번호
                     and    변경일자 <= a.서비스만료일);

-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |     1 |    60 |   332   (0)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID    | 고객별연체이력|     2 |    34 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                  |               |    16 |   960 |    32   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID  | 고객          |    10 |   430 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN            | 고객_IDX01    |    10 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN             | 고객별연체이력_IDX01     2 |       |     2   (0)| 00:00:01 |
|   6 |     SORT AGGREGATE              |               |     1 |    13 |            |          |
|   7 |      FIRST ROW                  |               |  5039 | 65507 |     3   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN (MIN/MAX)| 고객별연체이력_IDX01  5039 | 65507 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------


  • 서브쿼리 내에서 서비스만료일보다 작은 레코드를 모두 스캔하지 않고 오라클이 인덱스를 거꾸로 스캔하면서 가장 큰 값 하나만을 찾는 방식
  • (7번재 라인 First row, 8번째 라인 min/max, 오라클8 버전에서 구현)
  • 서브쿼리를 아래와 같이 바꿔줄 수 있지만 실제 수행해 보면 서브쿼리 내에서 액세스되는 인덱스 루트 블록에 대한 버퍼 Pinning효과가 사라져 블록 I/O가 더 많이 발생

select a.고객명, a.거주지역, a.주소, a.연락처, b.연체금액
from   고객 a, 고객별연체이력 b
where  a.가입회사 = 'C70'
and    b.고객번호 = a.고객번호
and    b.변경일자 = (select /*+ index_desc(b 고객별연체이력_IDX01 */ 변경일자
                     from   고객별연체이력 b
                     where  b.고객번호 = a.고객번호
                     and    b.변경일자 <= a.서비스만료일
                     and    rownum <= 1);

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     1 |    60 |   332   (0)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | 고객별연체이력|     2 |    34 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |               |    16 |   960 |    32   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| 고객          |    10 |   430 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | 고객_IDX01    |    10 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | 고객별연체이력_IDX01|     2 |       |     2   (0)| 00:00:01 |
|*  6 |     COUNT STOPKEY             |               |       |       |            |          |
|*  7 |      INDEX RANGE SCAN         | 고객별연체이력_IDX01|     2 |    26 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
  • 고객별연체이력_idx 인덱스를 두 번 액세스하는 비효율은 피할 수 없음


Index_desc 힌트와 rownum <=1 조건 사용시, 주의사항
  • 인덱스 구성이 변경되면 쿼리 결과가 틀리게 될 수 있음을 반드시 기억 해야함
  • first row(min/max) 알고리즘이 작동할 때는 반드시 min/max 함수를 사용하는 것이 올바른 선택
  • 낮은 성능 때문에 어쩔수 없이 Index(또는 index_desc) + rownum조건을 써야만 하는 경우는
  • 프로그램 목록을 관리했다가 인덱스 구성 변경시 확인하는 프로세스를 반드시 거쳐야함.


  • 참고로, min 또는 max 함수 내에서 컬럼을 가공하면 first row 알고리즘이 작동하지 않는다.

select a.고객명, a.거주지역, a.주소, a.연락처, b.연체금액
from   고객 a, 고객별연체이력 b
where  a.가입회사 = 'C70'
and    b.고객번호 = a.고객번호
and    b.변경일자 = (select /*+ no_unnest */  substr(max(변경일자 || 연체개월수), 9)
                     from   고객별연체이력
                     where  고객번호 = a.고객번호
                     and    변경일자 <= a.서비스만료일);

-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |     1 |    60 |  3836   (1)| 00:00:47 |
|   1 |  TABLE ACCESS BY INDEX ROWID    | 고객별연체이력|     2 |    34 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                  |               |    16 |   960 |    32   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID  | 고객          |    10 |   430 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN            | 고객_IDX01    |    10 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN             | 고객별연체이력_IDX01|     2 |       |     2   (0)| 00:00:01 |
|   6 |     SORT AGGREGATE              |               |     1 |    16 |            |          |
|   7 |      TABLE ACCESS BY INDEX ROWID| 고객별연체이력|  5039 | 80624 |    38   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | 고객별연체이력_IDX01|   907 |       |     6   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------


  • 스칼라 서브쿼리도 아래와 같이 max함수 사용하고 싶지만 first row 알고리즘이 작동하지 않아 부득이하게 index_desc힌트와 ronum 조건을 사용한 경우

select .....
     ,(selct substr(max(변경일자 || 연체금액), 9) from ...)
from 고객 a where .....


  • 스칼라 서브쿼리로 변환하면 인덱스를 두번 액세스하지 않아도 되기 때문에 I/O를 그만큼 줄일 수 있음
  • 여기서도 인덱스 루트 블록에 대한 버퍼 Pinning 효과는 사라진 것(10번 액세스하면서 30개 블록 I/O발생, 인덱스 height = 3)

select a.고객명, a.거주지역, a.주소, a.연락처
      ,(select /*+ index_desc(b 고객별연체이력_idx01) */ 연체금액
        from   고객별연체이력 b
        where  b.고객번호 = a.고객번호
        and    b.변경일자 <= a.서비스만료일
        and    rownum <= 1) 연체금액
from   고객 a
where  가입회사 = 'C70';

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |    10 |   430 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |               |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | 고객별연체이력|     2 |    34 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN DESCENDING| 고객별연체이력_IDX01|   907 |       |     3   (0)| 00:00:01 |
|   4 |  TABLE ACCESS BY INDEX ROWID  | 고객          |    10 |   430 |     2   (0)| 00:00:01 |
|*  5 |   INDEX RANGE SCAN            | 고객_IDX01    |    10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------


  • 고객별연체이력 테이블로부터 연체금액 하나만 읽기 때문에 스칼라 서브쿼리로 변경하기가 수월했다
  • 두개이상 컬럼을 읽어야 한다면 스칼라 서브쿼리 내에서 필요한 컬럼 문자열을 연결하고, 메인쿼리에서 substr함수로 잘라쓰는 방법을 사용해야 한다.

select 고객명, 거주지역, 주소, 연락처
     , to_number(substr(연체, 3)) 연체금액
     , to_number(substr(연체, 1, 2)) 연체개월수
from  (select a.고객명, a.거주지역, a.주소, a.연락처
            ,(select /*+ index_desc(b 고객별연체이력_idx01) */
                     lpad(연체개월수, 2) || 연체금액
              from   고객별연체이력
              where  고객번호 = a.고객번호
              and    변경일자 <= a.서비스만료일
              and    rownum <= 1) 연체
       from   고객 a
       where  가입회사 = 'C70'
);

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |    10 |   700 |     2   (0)| 00:00:01 |
|   1 |  VIEW                        |          |    10 |   700 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| 고객     |    10 |   430 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | 고객_IDX0|    10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------


  • 이력 테이블에서 읽어야 할 컬럼 개수가 많다면 일일이 문자열로 연결하는 작업은 여간 번거롭지 않다.
  • 스칼라 서브쿼리에서 rowid값만 취하고 고객별연체이력을 한번더 조인하는 방법을 생각해볼수 있다.

select /*+ ordered use_nl(b) rowid(b) */ a.*, b.연체금액, b.연체개월수
from  (select a.고객명, a.거주지역, a.주소, a.연락처
            ,(select /*+ index_desc(b 고객별연체이력_idx01) */ rowid rid
              from   고객별연체이력 b
              where  b.고객번호 = a.고객번호
              and    b.변경일자 <= a.서비스만료일
              and    rownum <= 1) rid
       from   고객 a
       where  가입회사 = 'C70') a, 고객별연체이력 b
where  b.rowid = a.rid;

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |   100K|  7381K|    12   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |          |   100K|  7381K|    12   (0)| 00:00:01 |
|   2 |   VIEW                        |          |    10 |   560 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| 고객     |    10 |   430 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | 고객_IDX0|    10 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY USER ROWID  | 고객별연체별이력 | 10079 |   187K|     1   (0)| 00:00:01 | --
------------------------------------------------------------------------------------------


  • 고객별연체이력 테이블과 조인을 두 번 했지만 실행계획상 으로는 조인을 한 번만 한 것과 일량이 같다.
  • 스칼라 서브쿼리 수행부분이 'VIEW'에 감춰져 보이지 않지만, 인덱스 이외의 컬럼을 참조하지 않았으므로 인덱스만 읽었을것이다.
  • 거기서 얻은 rowid값으로 바로 테이블을 엑세스(Table Access by User ROWID)하기 때문에
  • 일반적인 NL조인과 같은 프로세스(Outer 인덱스 -> Outer 테이블 -> Inner인덱스 -> Inner테이블)로 진행된다.
  • 스칼라 서브쿼리를 이용하지 않고 아래와 같이 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(c 고객별연체이력_idx01) */ rowid
                  from   고객별연체이력 c
                  where  c.고객번호 = a.고객번호
                  and    c.변경일자 <= a.서비스만료일
                  and    rownum <= 1);


----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   957 | 59334 |   312   (0)| 00:00:04 |
|   1 |  NESTED LOOPS                |               |  9574K|   566M|    12   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| 고객          |    10 |   430 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | 고객_IDX01    |    10 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY USER ROWID | 고객별연체이력|  1007K|    18M|     1   (0)| 00:00:01 |
|*  5 |    COUNT STOPKEY             |               |       |       |            |          |
|*  6 |     INDEX RANGE SCAN         | 고객별연체이력|     2 |    38 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


  • 고객(a)에서 읽은 고객번호로 서브쿼리 쪽 고객별연체이력©과 조인하고, 거기서 얻으 rowid값으로 고객별연체이력(b)을 곧바로 액세스한다. a와 b간에 따로 조인문을 기술하는 것은 불필요하다.
  • 고객별연체이력을 두 번 사용했지만 실행계획 상으로는 한 번만 조인하면서 일반적인 NL조인과 같은 프로세스
  • (Outer인덱스 -> Otuer 테이블 -> Inner인덱스 -> Inner테이블)로 진행되는 것에 주목하기 바란다.


정해진 시점 기준으로 조회
  • 앞에서는 가입회사 = 'C70'에 속하는 고객 수가 10명.
  • 만약 가입회사별 고객수가 많아지면 서브쿼리 수행횟수가 늘어나 Random I/O부하도 심해질 것이다.
  • 가입회사 조건절없이 모든 고객을 대상으로 이력을 조회한다면 ?
  • 고객 테이블로부터 읽히는 미지의 시점(서비스 만료일)을 기준으로 이력을 조회하는 경우이기 때문에 위와 같이 Random 액세스 위주의 서브쿼리를 쓸수 밖에 없다.
  • 정해진 시점을 기준으로 조회하는 경우라면 서브쿼리를 쓰지 않음으로써 Random 액세스 부하를 줄일 방법들이 몇가지 생긴다.

select /*+ full(a) full(b) full(c) use_hash(a b c) no_merge(b) */
       a.고객명, a.거주지역, a.주소, a.연락처, c.연체금액, c.연체개월수
from   고객 a
     ,(select 고객번호, max(변경일자) 변경일자
       from   고객별연체이력
       where  변경일자 <= to_char(sysdate, 'yyyymmdd')
       group by 고객번호) b, 고객별연체이력 c
where  b.고객번호 = a.고객번호
and    c.고객번호 = b.고객번호
and    c.변경일자 = b.변경일자;

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |    10 |   680 |  1603   (4)| 00:00:20 |
|*  1 |  HASH JOIN            |         |    10 |   680 |  1603   (4)| 00:00:20 |
|*  2 |   HASH JOIN           |         |    10 |   490 |   809   (5)| 00:00:10 |
|   3 |    TABLE ACCESS FULL  | 고객    |    10 |   300 |     3   (0)| 00:00:01 |
|   4 |    VIEW               |         |    10 |   190 |   805   (4)| 00:00:10 |
|   5 |     HASH GROUP BY     |         |    10 |   130 |   805   (4)| 00:00:10 |
|*  6 |      TABLE ACCESS FULL| 고객별연|  9881 |   125K|   804   (4)| 00:00:10 |
|   7 |   TABLE ACCESS FULL   | 고객별연|  1007K|    18M|   788   (2)| 00:00:10 |
---------------------------------------------------------------------------------


  • 가장 단순하게 작성된 위 쿼리는 고객별연체이력 테이블을 두번 Full Scan하는 비효율을 가짐

select a.고객명, a.거주지역, a.주소, a.연락처
     , to_number(substr(연체, 11)) 연체금액
     , to_number(substr(연체, 9, 2)) 연체개월수
from   고객 a
     ,(select 고객번호, max(변경일자 || lpad(연체개월수, 2) || 연체금액 ) 연체
       from   고객별연체이력
       where  변경일자 <= to_char(sysdate, 'yyyymmdd')
       group by 고객번호) b
where  b.고객번호 = a.고객번호;

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   251 | 15311 |   395   (1)| 00:00:05 |
|   1 |  HASH GROUP BY               |               |   251 | 15311 |   395   (1)| 00:00:05 |
|   2 |   TABLE ACCESS BY INDEX ROWID| 고객별연체이력|   988 | 18772 |    39   (0)| 00:00:01 |
|   3 |    NESTED LOOPS              |               |  9881 |   588K|   393   (0)| 00:00:05 |
|   4 |     TABLE ACCESS FULL        | 고객          |    10 |   420 |     3   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN         | 고객별연체이력|   988 |       |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


  • 이력 테이블에서 읽어야 할 컬럼 개수가 많다면 위와 같이 일일이 문자열로 연결하는 작업은 여간 번거롭지 않음
  • 그때는 아래와 같이 분석함수를 이용하는 것이 편하고, 수행 속도 면에서도 전혀 불리하지 않음

select a.고객명, a.거주지역, a.주소, a.연락처, b.연체금액, b.연체개월수
from   고객 a
     ,(select 고객번호, 연체금액, 연체개월수, 변경일자
            , row_number() over (partition by 고객번호 order by 변경일자 desc) no
       from   고객별연체이력
       where  변경일자 <= to_char(sysdate, 'yyyymmdd')) b
where  b.고객번호 = a.고객번호
and    b.no = 1;

---------------------------------------------------------------------------------------------
| Id  | Operation                 | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |  9881 |   810K|       |   869   (4)| 00:00:11 |
|*  1 |  HASH JOIN                |         |  9881 |   810K|       |   869   (4)| 00:00:11 |
|   2 |   TABLE ACCESS FULL       | 고객    |    10 |   300 |       |     3   (0)| 00:00:01 |
|*  3 |   VIEW                    |         |  9881 |   521K|       |   865   (4)| 00:00:11 |
|*  4 |    WINDOW SORT PUSHED RANK|         |  9881 |   183K|   632K|   865   (4)| 00:00:11 |
|*  5 |     TABLE ACCESS FULL     | 고객별연|  9881 |   183K|       |   804   (4)| 00:00:10 |
---------------------------------------------------------------------------------------------


  • 아래와 같이 max함수를 이용할 수도 있지만 방금처럼 row_number를 이용하는 것이 더 효과적인데, 자세한 원리는 5장 6절에서 설명.

select a.고객명, a.거주지역, a.주소, a.연락처, b.연체금액, b.연체개월수
from   고객 a
     ,(select 고객번호, 연체금액, 연체개월수, 변경일자
            , max(변경일자) over (partition by 고객번호) max_dt
       from   고객별연체이력
       where  변경일자 <= to_char(sysdate, 'yyyymmdd')) b
where  b.고객번호 = a.고객번호
and    b.변경일자 = b.max_dt;

----------------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |  9881 |   781K|       |   869   (4)| 00:00:11 |
|*  1 |  HASH JOIN           |         |  9881 |   781K|       |   869   (4)| 00:00:11 |
|   2 |   TABLE ACCESS FULL  | 고객    |    10 |   300 |       |     3   (0)| 00:00:01 |
|*  3 |   VIEW               |         |  9881 |   492K|       |   865   (4)| 00:00:11 |
|   4 |    WINDOW SORT       |         |  9881 |   183K|   632K|   865   (4)| 00:00:11 |
|*  5 |     TABLE ACCESS FULL| 고객별연|  9881 |   183K|       |   804   (4)| 00:00:10 |
----------------------------------------------------------------------------------------


"구루비 데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/4437

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입