8. 고급 조인 테크닉 (by kwlee55) [2010.04.08]
I. 누적 매출 구하기
| {code} create table 월별지점매출 as select deptno "지점" , row_number() over (partition by deptno order by empno) "판매월" , round(dbms_random.value(500, 1000)) "매출" from emp order by deptno ; {code} |
분석함수(Analytic Function)이용{}
| {code} select 지점, 판매월, 매출 , sum(매출) over (partition by 지점 order by 판매월 range between unbounded preceding and current row) 누적매출 from 월별지점매출; {code} |
부등호 연산자를 조인{}
| {code} select t1.지점, t1.판매월, min(t1.매출) 매출, sum(t2.매출) 누적매출 from 월별지점매출 t1, 월별지점매출 t2 where t2.지점 = t1.지점 and t2.판매월 <= t1.판매월 group by t1.지점, t1.판매월 order by t1.지점, t1.판매월; {code} |
2. 선분이력 끊기
두 선분이 겹치는 구간에 대한 시작일자 및 종료일자 선택 규칙
=> 
변환된 선분이력 SQL
=> 선분이력을 월도와 조인하여 <변환된 선분이력> 형태를 만드는 SQL ( 두 개 이상의 월도에 걸친 선분을 매 월말 기준으로 끊는다.)
| {code} create table 월도 (기준월, 시작일자, 종료일자) as select '2009/06', '2009/06/01', '2009/06/30' from dual union all select '2009/07', '2009/07/01', '2009/07/31' from dual union all select '2009/08', '2009/08/01', '2009/08/31' from dual union all select '2009/09', '2009/09/01', '2009/09/30' from dual union all select '2009/10', '2009/10/01', '2009/10/31' from dual; |
create table 선분이력(상품번호, 시작일자, 종료일자, 데이터)
as
select 'A', '2009/07/13', '2009/08/08', 'A1' from dual union all
select 'A', '2009/08/09', '2009/08/20', 'A2' from dual union all
select 'A', '2009/08/21', '2009/10/07', 'A3' from dual;
|
!4.jpg!
!5.jpg!
h1. 3. 데이터 복제를 통한 소계 구하기
h5. 부등호 조인을 이용한 데이터 복제 : 위 1, 2 예제
h5. 카티션 곱을 발생시켜 복제
{color:blue}*1. 복제용 테이블(copy_t){*}{color}
|
SQL> create table copy_t ( no number, no2 varchar2(2));
Table created.
SQL> insert into copy_t
2 select rownum, lpad(rownum,2,'0') from all_tables where rownum <= 31;
31 rows created.
SQL> alter table copy_t add constraint copy_t primary key(no);
Table altered.
SQL> create unique index copy_t_no2_idx on copy_t(no2);
Index created.
1 select empno, ename from emp a, copy_t b
2* where b.no <= 3
SQL> /
EMPNO ENAME
--
--
7369 SMITH
7499 ALLEN
7521 WARD
..
42 rows selected.
|
{color:blue}*2. dual 테이블에 connect by 구문으로 복제 집합 생성{*}{color}
|
SQL> select rownum no from dual connect by level <= 2;
NO
--
1
2
SQL> select empno, ename from emp a, ( select rownum no from dual connect by level <= 2 ) b;
EMPNO ENAME
--
--
7369 SMITH
7499 ALLEN
7521 WARD
...
28 rows selected.
|
{color:blue}*3. 데이터 복제 기법을 활용한 단일 SQL : 부서별 소계{*}{color}
|
break on 부서번호
column 부서번호 format 9999
column 사원번호 format a10
select deptno 부서번호
, decode(no, 1, to_char(empno), 2, '부서계') 사원번호
, sum(sal) 급여합, round(avg(sal)) 급여평균
from emp a, (select rownum no from dual connect by level <= 2)
group by deptno, no, decode(no, 1, to_char(empno), 2, '부서계')
order by 1, 2;
|
{color:blue}*4. 데이터 복제 기법을 활용한 단일 SQL : 총계{*}{color}
|
column 부서번호 format a10
select decode(no, 3, null, to_char(deptno)) 부서번호
, decode(no, 1, to_char(empno), 2, '부서계', 3, '총계') 사원번호
, sum(sal) 급여합, round(avg(sal)) 급여평균
from emp a, (select rownum no from dual connect by level <= 3)
group by decode(no, 3, null, to_char(deptno))
, no, decode(no, 1, to_char(empno), 2, '부서계', 3, '총계')
order by 1, 2;
|
{color:blue}*5. rollup 구문 사용{*}{color}
|
break on 부서번호
column 부서번호 format 9999
column 사원번호 format a10
select deptno 부서번호
, case when grouping(empno) = 1 and grouping(deptno) = 1 then '총계'
when grouping(empno) = 1 then '부서계'
else to_char(empno) end 사원번호
, sum(sal) 급여합, round(avg(sal)) 급여평균
from emp
group by rollup(deptno, empno)
order by 1, 2;
|
h1. 4. 상호배타적 관계의 조인
{color:blue}상호배타적 관계 : 어떤 엔터티가 두 개 이상의 다른 엔터티의 합집합과 관계를 갖는 것{color}
{color:blue}ERD에 아래처럼 아크(Arc) 관계로 표시{color}
!9.jpg!
{color:blue}실제 데이터베이스로 구현할때, 상품권결제 테이블은 아래 두가지 방법으로 구축한다.{color}
{color:green}1. 온라인권번호, 실권번호 두 컬럼을 따로 두고, 레코드별로 둘 중 하나의 컬럼에만 값을 입력한다. Outer 조인 이용{color}
|
SELECT /*+ ordered use_nl(b) use_nl© use_nl© use_nl(d) */
a.주문번호, a.결제일자, a.결제금액
, NVL(b.온라인권번호, c.실권번호) 상품권번호
, NVL(b.발행일시, d.발행일시) 발행일시
FROM 상품권결제 a, 온라인권 b, 실권 c, 실권발행 d
WHERE a.결제일자 BETWEEN :dt1 AND :dt2
AND b.온라인권번호(+) = a.온라인권번호
AND c.실권번호(+) = a.실권번호
AND d.발행번호(+) = c.발행번호;
|
{color:green}2. 상품권구분과 상품권번호 컬럼을 두고, 상품권구분이 1일때는 온라인권번호를 입력하고 2일 때는 실퀀번호를 입력한다. Union all 이용{color}
|
SELECT x.주문번호, x.결제일자, x.결제금액, y.온라인권번호 상품권번호, y.발행일시, ...
FROM 상품권결제 x, 온라인권 y
WHERE x.상품권구분 = '1'
AND x.결제일자 BETWEEN :dt1 AND :dt2
AND y.온라인권번호(+) = x.상품권번호
UNION ALL
SELECT x.주문번호, x.결제일자, x.결제금액, y.실권번호 상품권번호, z.발행일시, ...
FROM 상품권결제 x, 실권 y, 실권발행 z
WHERE x.상품권구분 = '2'
AND x.결제일자 BETWEEN :dt1 AND :dt2
AND y.실권번호(+) = x.상품권번호
AND z.발행번호(+) = y.발행번호;
- 쿼리를 위아래 두번 수행하지만, 인덱스구성에 따라 처리 범위는 달라진다.
1. (상품권구분 + 결제일자) : 읽는 범위 중복 없음
2. (결제일자 + 상품권구분) : 인덱스 스캔범위에 중복 발생
3. (결제일자) : 상품권구분을 필터링하기 위한 테이블 Random 액세스까지 중복 발생
|
{color:green}3. 중복 액세스에 의한 비효울 제거{color}
|
SELECT /*+ ordered use_nl(b) use_nl© use_nl© use_nl(d) */
a.주문번호, a.결제일자, a.결제금액
, NVL(b.온라인권번호, c.실권번호) 상품권번호
, NVL(b.발행일시, d.발행일시) 발행일시
FROM 상품권결제 a, 온라인권 b, 실권 c, 실권발행 d
WHERE a.결제일자 BETWEEN :dt1 AND :dt2
AND b.온라인권번호(+) = DECODE(a.상품권구분, '1', a.상품권번호)
AND c.실권번호(+) = DECODE(a.상품권구분, '2', a.상품권번호)
AND d.발행번호(+) = c.발행번호;
|
h1. 5. 최종 출력 건에 대해서만 조인하기
{color:blue}회면 페이지 처리시 흔히 사용되는 방식이다.{color}
{color:blue}조건과 정렬 컬럼을 결합인덱스로 구성하면 인덱스 블록만 읽어 테이블 액세스 범위와 조인 범위를 줄일 수 있다. ( 교재 참고 page. 312~ ) {color}
{color:blue}조건과 정렬 컬럼이 많아져 인덱스 조정이 어려울 때는, 해당 테이블까지 액세스하여 전체를 읽어 정렬한 후 불필요한 조인 횟수를 줄일 수 있다. ( 교재 참고 page. 316~ ){color}
{color:red}*조건과 정렬 컬럼이 여러 테이블 각각 있다면? => 반정규화{*}{color}
{color:blue}반정규화에 따른 고려사항{color}
1. 반정규화 컬럼의 데이터 갱신을 위한 추가 프로그램 필요하다
2. 데이터 정합성 훼손의 위험성이 높아지므로, 데이터 부정합이 발생하지 않도록 조치한다.
3. 반정규화 없이 성능 문제를 해결할 수 있는 방법을 고민한다.
!10.jpg!
=> {color:green}수신확인자수와 수신대상자수를 세고 새글여부를 확인하는 스칼라 서브쿼리 성능문제로 발송메세지건수, 수신인수와 같은 추출 속성을 추가로 생성{color}
|
SELECT ..
FROM (
SELECT ..
FROM (
SELECT a.발신인ID, a,발송일시, a.제목, b.사용자이름 AS 보낸이
, ( SELECT COUNT(수신일시) FROM 메시지수신인 ..) 수신확인자수
, ( SELECT COUNT(*) FROM 메시지수신인 ..) 수신대상자수
, ( CASE WHEN EXISTS ( SELECT 'x' FROM 메시지수신인
WHERE 발신자ID = a.발신자ID
AND 발송일시 = a.발송일시
AND 수신자ID = :로그인사용자ID
AND 수신일시 IS NULL ) THEN 'Y' END ) 새글여부
FROM 메시지 a, 사용자 b
ORDER BY a.발송일시 DESC
) a
WHERE rownum <= 10
)
WHERE no between 1 and 10;
|
=> {color:green}최종 출력되는 10건에 대해서만 수신정보와 새글 여부를 확인하는 방식으로 쿼리 변경하여 성능 문제 해결{color}
|
SELECT a.발신인ID, a,발송일시, a.제목, b.사용자이름 AS 보낸이
, ( SELECT COUNT(수신일시) || '/' || COUNT(*) FROM 메시지수신인 ..) 수신확인
, ( CASE WHEN EXISTS ( .. ) THEN 'Y' END ) 새글여부
FROM (
SELECT ROWNUM NO, ...
FROM ( SELECT 발신인ID, a,발송일시, a.제목 FROM 메시지 ORDER BY a.발송일시 DESC )
WHERE ROWNUM <= 30
) a, 사용자 b
WHERE NO BETWEEN 21 AND 30;
|
h1. 6. 징검다리 테이블 조인을 이용한 튜닝
h5. from절에 조인되는 테이블 개수를 늘려 성능을 향상시키는 사례( 교재 참고 page. 319~ )
1. 최종 결과 건수는 얼마 되지 않으면서, 필터 조건만으로 각 부분을 따로 읽으면 결과 건수가 아주 많으 때 튜닝하기가 어렵다.
2. 조인 테이블을 추가하여 인덱스만 읽도록 하고, 인덱스만 읽은 결과끼리 먼저 조인하고 최종 결과집합에 대해서만 테이블을 액세스하도록 한다.
3. 테이블을 액세스할 때는 추가적인 인덱스 탐색 없이 인덱스에서 읽은 rowid 값을 가지고 직접 액세스한다. ( 실행계획에 Table Access By User ROWID 라고 표시)
h5. 인조 식별자 사용에 의한 조인 성능 이슈 ( 교재 참고 page. 324~ )
1. 액세스 경로에 대한 고려 없이 인조 식별자를 설계하면, 조인 성능 이슈가 자주 발생한다.
2. 자식 테이블에 부모 테이블의 액세스 조건 컬럼이 상속되지 않고 인조 식별자 컬럼을 둘 경우 조인과정에 비 효율을 일으킨다.
3. 인조 식별자 설계가 필요할 경우 주요 액세스 경로에 따른 컬럼을 자식 테이블에 추가하여 반정규화를 고려한다.
h5. 인조 식별자를 둘 때 주의 사항
{color:blue}장점{color}
1. 단일 컬럼으로 구성되므로 테이블 간 연결 구조가 단손해지고, 인덱스 저장공간이 최소화된다.
2. 다중 컬럼으로 조인할 때보다 조인 연산을 위한 CPU사용량이 조금 줄 수 있다.
{color:blue}단점{color}
1. 조인 연산 횟수와 블록 I/O 증가로 시스템 리소스를 낭비한다.
2. 실질 식별자를 찾기 어려워 데이터 모델이 이해하기 어려워진다.
{color:blue}TIP{color}
1. 논리적인 데이터 모델링 단계에서는 가급적 인조 식별자를 두지 않는 것이 좋다.
2. 의미상 주어에 해당하는 속성들을 식별자로 사용했다가 물리 설계 단계에서 저장 효율과 액세스 효율 등을 고려해서 결정한다.
h1. (7) 점이력 조회
데이터 변경이 발생할 때마다 변경일자와 함께 새로운 이력 레코드를 쌓는 방식을 점이력
!Pic_2-35.jpg!
점이력 모델에서 이력을 조회할 때 흔히 아래와 같이 서브쿼리를 이용함
찾고자 하는 시점(서비스만료일) 보다 앞선 변경일자 중 가장 마지막 레코드를 찾는 것
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 |
| INDEX RANGE SCAN | 고객_IDX01 | 10 | 1 (0) | 00:00:01 | |
| INDEX RANGE SCAN | 고객별연체이력 | 2 | 2 (0) | 00:00:01 | |
| 6 | SORT AGGREGATE | 1 | 13 | |||
| 7 | FIRST ROW | 5039 | 65507 | 3 (0) | 00:00:01 | |
| INDEX RANGE SCAN (MIN/MAX) | 고객별연체이력 | 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 |
| INDEX RANGE SCAN | 고객_IDX01 | 10 | 1 (0) | 00:00:01 | |
| INDEX RANGE SCAN | 고객별연체이력 | 2 | 2 (0) | 00:00:01 | |
| COUNT STOPKEY | |||||
| INDEX RANGE SCAN | 고객별연체이력 | 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 |
| INDEX RANGE SCAN | 고객_IDX01 | 10 | 1 (0) | 00:00:01 | |
| INDEX RANGE SCAN | 고객별연체이력 | 2 | 2 (0) | 00:00:01 | |
| 6 | SORT AGGREGATE | 1 | 16 | |||
| 7 | TABLE ACCESS BY INDEX ROWID | 고객별연체이력 | 5039 | 80624 | 38 (0) | 00:00:01 |
| INDEX RANGE SCAN | 고객별연체이력 | 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 | |
| COUNT STOPKEY | |||||
| 2 | TABLE ACCESS BY INDEX ROWID | 고객별연체이력 | 2 | 34 | 4 (0) | 00:00:01 |
| INDEX RANGE SCAN DESCENDING | 고객별연체이력 | 907 | 3 (0) | 00:00:01 | |
| 4 | TABLE ACCESS BY INDEX ROWID | 고객 | 10 | 430 | 2 (0) | 00:00:01 |
| 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 |
| INDEX RANGE SCAN | 고객_IDX0 | 10 | 1 (0) | 00:00:01 |
-
select 고객명, 거주지역, 주소, 연락처
, to_number(substr(연체, 11)) 연체금액
, to_number(substr(연체, 9, 2)) 연체개월수
from (select a.고객명, a.거주지역, a.주소, a.연락처
,(select max(변경일자 || lpad(연체개월수, 2) || 연체금액)
from 고객별연체이력
where 고객번호 = a.고객번호
and 변경일자 <= a.서비스만료일) AS 연체
from 고객 a
where 가입회사 = 'C70'
);
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-
| 0 | SELECT STATEMENT | 10 | 740 | 2 (0) | 00:00:01 | |
| 1 | VIEW | 10 | 740 | 2 (0) | 00:00:01 | |
| 2 | TABLE ACCESS BY INDEX ROWID | 고객 | 10 | 430 | 2 (0) | 00:00:01 |
| 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 |
| 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테이블)로 진행
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 |
| INDEX RANGE SCAN | 고객_IDX01 | 10 | 1 (0) | 00:00:01 | |
| 4 | TABLE ACCESS BY USER ROWID | 고객별연체이력 | 1007K | 18M | 1 (0) | 00:00:01 |
| COUNT STOPKEY | |||||
| INDEX RANGE SCAN | 고객별연체이력 | 2 | 38 | 3 (0) | 00:00:01 |
--
고객(a)에서 읽은 고객번호로 서브쿼리 쪽 고객별연체이력(c)과 조인하고, 거기서 얻으 rowid값으로 고객별연체이력(b)을 곧바로
액세스한다. a와 b간에 따로 조인문을 기술하는 것은 불필요함
고객별연체이력을 두 번 사용했지만 실행계획 상으로는 한 번만 조인하면서 일반적인 NL조인과 같은 프로세스
(Outer인덱스 -> Otuer 테이블 -> Inner인덱스 -> Inner테이블)로 진행되는 것에 주목함
h5. 정해진 시점 기준으로 조회
앞에서는 가입회사 = 'C70'에 속하는 고객 수가 10명뿐임
만약
가입회사별 고객수가 많아지면 서브쿼리 수행횟수가 늘어나 Random I/O부하도 심해질것
가입회사 조건절없이 모든 고객을 대상으로 이력을 조회한다면 ?
고객 테이블로부터 읽히는 미지의 시점(서비스 만료일)을 기준으로 이력을 조회하는 경우이기 때문에 위와 같이 Random 액세스 위주의
서브쿼리를 쓸수 밖에 없음
정해진 시점을 기준으로 조회하는 경우라면 서브쿼리를 쓰지 않음으로써 Random 액세스 부하를 줄일 방법들이 몇가지 생김
select /*+ full(a) full(b) full© 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 | |
| HASH JOIN | 10 | 680 | 1603 (4) | 00:00:20 | |
| 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 | |
| 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 |
| 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 | ||
| HASH JOIN | 9881 | 810K | 869 (4) | 00:00:11 | ||
| 2 | TABLE ACCESS FULL | 고객 | 10 | 300 | 3 (0) | 00:00:01 | |
| VIEW | 9881 | 521K | 865 (4) | 00:00:11 | ||
| WINDOW SORT PUSHED RANK | 9881 | 183K | 632K | 865 (4) | 00:00:11 | |
| 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 | ||
| HASH JOIN | 9881 | 781K | 869 (4) | 00:00:11 | ||
| 2 | TABLE ACCESS FULL | 고객 | 10 | 300 | 3 (0) | 00:00:01 | |
| VIEW | 9881 | 492K | 865 (4) | 00:00:11 | ||
| 4 | WINDOW SORT | 9881 | 183K | 632K | 865 (4) | 00:00:11 | |
| TABLE ACCESS FULL | 고객별연 | 9881 | 183K | 804 (4) | 00:00:10 |
h1. (8) 선분이력 조인
조인을 통해 선분이력을 조회하는 방법을 정리하고, 계속해서 선분이력 조인 튜닝방안을 다음하에서 설명함
h5. 과거/현재/미래의 임의 시점 조회
고객등급과 전화번호 변경이력을 관리하는 두 선분이력테이블이 있음
!Pic_2-36.jpg!
고객과 이 두 선분이력 테이블을 조인해서 2004년 9월 1일 시점 데이터를 조회할 때는 아래와 같이 쿼리함
:dt 변수에는 '20040901'(시작일자, 종료일자가 문자열 컬럼일 때)을 입력
select c.고객번호, c.고객명, c1.고객등급, c2.전화번호
from 고객 c, 고객등급변경이력 c1, 전화번호변경이력 c2
where c.고객번호 = 123
and c1.고객번호 = c.고객번호
and c2.고객번호 = c.고객번호
and :dt between c1.시작일자 and c1.종료일자
and :dt betwwen c2.시작일자 and c2.종료일자
123번 고객의 등급과 전화번호 변경이력 레코드를 수평선사에 펼쳐 시계열적으로 표현했을 때
위 쿼리 결과로서 고객등급 'B', 고객전화번호는 '987-6543'으로 조회될 것
!Pic_2-37.jpg!
h5. 현재시점
위 쿼리를 이용해 과거,현재, 미래 어느 시점이든 조회할 수 있지만, 만약 미래 시점 데이터를 미리 입력하는 예약기능
이 없다면 "현재시점(즉, 현재 유효한 시점)" 조회는 아래와 같이 "=" 조건으로 만들어 주는 것이 효과적이다.
select c.고객번호, c.고객명, c1.고객등급, c2.전화번호
from 고객 c, 고객등급변경이력 c1, 전화번호변경이력 c2
where c.고객번호 = 123
and c1.고객번호 = c.고객번호
and c2.고객번호 = c.고객번호
and c1.종료일자 = '99991231'
and c2.종료일자 = '99991232'
!Pic_2-38.jpg!
현재가 2005년 6월 27일인데 그림 2-38처럼 미래 시점인 6월 8일 데이터를 미리 입력해 두는 기능이 있다면
현재 시점을 조회할 때 아래와 같이 sysdate와 between을 사용해야만 함
select c.고객번호, c.고객명, c1.고객등급, c2.전화번호
from 고객 c, 고객등급변경이력 c1, 전화번호변경이력 c2
where c.고객번호 = 123
and c1.고객번호 = c.고객번호
and c2.고객번호 = c.고객번호
and to_char(sysdate, 'yyyymmdd') between c1.시작일자 and c1.종료일자
and to_char(sysdate, 'yyyymmdd') between c2.시작일자 and c2.종료일자
h5. Between조인
지금까지는 선분조인이 상수였다
즉, 조회시점이 정해져 있었다
그림 2-39에서 만약 우측(일별종목거래 및 시세)과 일별 거래 테이블로부터 읽히는 미지의 거래일자 시점으로 선분이력(종목이력)을
조회할때 어떻게 해야 할까? 이때는 between조인을 이용하면 됨
!Pic_2-39.jpg!
주식시장에서 과거 20년 동안 당일 최고가로 장을 마친(종가=최고가) 종목을 조회하는 쿼리
그림 2-39의 일별종목거래및시세 테이블로부터 시가, 종가, 거래데이터를 일고 그당시 종목명과 상장주식수는 종목이력으로부터 가져오는데, 조인연산자가 '='이 아니라 between이라는 점이 특징적임
select a.거래일자, a.종목코드, b.종목한글명, b.종목영문명, b.상장주식수
, a.시가, a.종가, a.체결건수, a.체결수량, a.거래대금
from 일별종목거래및시세 a, 종목이력 b
where a.거래일자 between to_char(add_months(sysdate, -20*12), 'yyyymmdd')
and to_char(sysdate-1, 'yyyymmdd')
and a.종가 = a.초고가
and b.종목코드 = a.종목코드
and a.거래일자 between b.시작일자 and b.종료일자
이런 식으로 조회하면 현재(=최종) 시점의 종목명을 가져오는 것이 아니라 그림 2-40에서 보는것 처럼 거래가 일어난 바로 그 시점의 종목명을
읽게 된다
!Pic_2-40.jpg!
거래 시점이 아니라 현재(=최종) 시점의 종목명과 상장주식수를 출력하려면 between 조인대신 아래와 같이 상수 조건으로
이별해야 한다 (2-41 참조)
select a.거래일자, a.종목코드, b.종목한글명, b.종목영문명, b.상장주식수
, a.시가, a.종가, a.체결건수, a.체결수량, a.거래대금
from 일별종목거래및시세 a, 종목이력 b
where a.거래일자 between to_char(add_months(sysdate, -20*12), 'yyyymmdd')
and to_char(sysdate-1, 'yyyymmdd')
and a.종가 = a.초고가
and b.종목코드 = a.종목코드
and to_char(sysdate, 'yyyymmdd') between b.시작일자 and b.종료일자
!Pic_2-41.jpg!
방금쿼리는 종목테이블을 종목이력과 통합해 하나로 설계했을 때 사용하는 방식이다
그림 2-39처럼 종목과 종목이력을 따로 설계했을 때는 최종 시점을 위해 종목 테이블과 조인하면된다.
h1. (9) 선분이력 조인 튜닝
선분이력과 조인할 때 발생하는 성능 이슈, 그리고 이를 해결할 튜닝 바안에 대해 살펴봄
h5. 정해진 시점을 기준으로 선분이력과 단수 조인할 때
!Pic_2-42.jpg!
그림 2-42와 같은 모델 하에서 아래처럼 특정 회사 (예, 가입회사 = 'C70')를 통해 가입한 모든 고객의 연체금액을 조회하는 경우
select /*+ ordered use_nl(b) */ a.고객명, a.거주지역, a.주소, a.연락처, b.연체금액
from 고객 a, 고객별연체이력 b
where a.가입회사 = 'C70'
and b.고객번호 = a.고객번호
and '20050131' between b.시작일 and b.종료일;
특정 고객 이력만 조회한다면 1장에서 본 것처럼 rownum <=1 조건을 적용해 인덱스를 한 건만 스캔하고 멈추도록 할 수 있지만
위와 같이 다수 고객을 조회할 떼는 그럴 수가 없다.
위 쿼리를 수행해 보면
1) 'C70' 화사를 통해 가입한 모든 고객에 대해 시작일이 2005년 1월 31일보다 작거나 같은 이력을 모두 스캔하거나
(인댁스 구성상 시작일이 종료일보다 선행 컬럼일 때),
종료일이 2005년 1월 31일보다 크거나 같은 이력을 모두 스캔
(종료일이 시작일보다 선행 컬럼일 때)하게된다.
create table 고객
as
select empno 고객번호, ename 고객명, 'C70' 가입회사
, '서울' 거주지역, '...' 주소, '123-' || empno 연락처
, to_char(to_date('20050101','yyyymmdd')+rownum*20000,'yyyymmdd') 서비스만료일
from emp
where rownum <= 10;
create index 고객_idx01 on 고객(가입회사);
create table 고객별연체이력
as
select a.고객번호, b.시작일, b.종료일, b.연체개월수, b.연체금액
from 고객 a
,(select to_char(to_date('20050101', 'yyyymmdd')+rownum*2, 'yyyymmdd') 시작일
, to_char(to_date('20050102', 'yyyymmdd')+rownum*2, 'yyyymmdd') 종료일
, round(dbms_random.value(1, 12)) 연체개월수
, round(dbms_random.value(100, 1000)) * 100 연체금액
from dual
connect by level <= 100000) b;
고객테이블 10명의 고객을 입력
고객별연체이력 테이에는 (비현실적이긴 하지만 테스트 효과를 극대화하기 위해) 고객마다 10만개의 이력 데이터를 입력함
SQL> select min(시작일) MN_시작일, max(시작일) MX_시작일 from 고객별연체이력;
MN_시작일 MX_시작일
20050103 25520801
2005년 1월 3일 부터 2552년 8얼 1일까지의 이력데이터가 들어있음
create index 고객별연체이력_idx01 on 고객별연체이력(고객번호, 종료일, 시작일);
아래는 SQL트레이스를 활성화하고서 실제쿼리를 수행한 결과
select /*+ ordered use_nl(b) */ a.고객명, a.거주지역, a.주소, a.연락처, b.연체금액
from 고객 a, 고객별연체이력 b
where a.가입회사 = 'C70'
and b.고객번호 = a.고객번호
and '20050131' between b.시작일 and b.종료일;
Rows Row Source Operation
---
---
10 TABLE ACCESS BY INDEX ROWID 고객별연체이력 (cr=4636 pr=0 pw=0 time=170 us)
21 NESTED LOOPS (cr=4634 pr=0 pw=0 time=1808 us)
10 TABLE ACCESS BY INDEX ROWID 고객 (cr=4 pr=0 pw=0 time=339 us)
10 INDEX RANGE SCAN 고객_IDX01 (cr=2 pr=0 pw=0 time=144 us)(object id 42151)
10 INDEX RANGE SCAN 고객별연체이력_IDX01 (cr=4630 pr=0 pw=0 time=337 us)(object id 42154)
SQL> drop index 고객별연체이력_idx01;
SQL> create index 고객별연체이력_idx01 on 고객별연체이력(고객번호, 시작일, 종료일);
--> 동일한 비효율 발생 (?)
Rows Row Source Operation
---
---
10 TABLE ACCESS BY INDEX ROWID 고객별연체이력(cr=4636 pr=0 pw=0 time=170 us)
21 NESTED LOOPS (cr=4634 pr=0 pw=0 time=1808 us)
10 TABLE ACCESS BY INDEX ROWID 고객 (cr=4 pr=0 pw=0 time=339 us)
10 INDEX RANGE SCAN 고객_IDX01 (cr=2 pr=0 pw=0 time=144 us)(object id 42151)
10 INDEX RANGE SCAN 고객별연체이력_IDX01 (cr=4630 pr=0 pw=0 time=337 us)(object id 42154)
-> 책내용: 예상했던 대로 고객별연체이력_idx01 인덱스를 스캔하는 단계에서 블록 I/O가 24개만 발생하였음.
'C70' 회사를 통해 가입한 고객만 조회하는 것이 아니라 만약 아래와 같이 전체 고객을 대상으로 조회할 때는 Random 액세스
위주의 NL 조인보다 해시조인을 이용하는 것이 유리
(예제 데이터에는 모든 고객의 가입회사가 'C70'이므로 성능차이가 없겠지만)
select /*+ ordered use_hash(b) */
a.고객명, a.거주지역, a.주소, a.연락처, b.연체금액
from 고객 a, 고객별연체이력 b
where b.고객번호 = a.고객번호
and '20050131' between b.시작일 and b.종료일;
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--
| 0 | SELECT STATEMENT | 10 | 560 | 17 (6) | 00:00:01 | |
| HASH JOIN | 10 | 560 | 17 (6) | 00:00:01 | |
| 2 | TABLE ACCESS FULL | 고객 | 10 | 300 | 3 (0) | 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | 고객별연체이력 | 10 | 260 | 13 (0) | 00:00:01 |
| INDEX SKIP SCAN | 고객별연체이력 | 10 | 12 (0) | 00:00:01 |
--
해시조인을 이용하면 전체이력 레코드를 Full Scan 하는 비용은 있을지언정 해시조인과정에서의 비효율은 없음
고객별연체이력을 해시 테이블로 빌드(build)하더라도 각 고객별로 한 건의 이력 레코드만 해시 테이블에 담기 때문임
뒤에 보겠지만 between조인일 때는 전 구간 이력레코드를 해시 테이블로 빌드함으로 인해 엄청난 비효율을 수반하기도 함
h5. Between 조인 튜닝 - 조회 대상이 많지 않을 때
아래와 같이 미지의 값(고객 테이블에서 실시간으로 읽히는 값)으로 between 조인하는 경우
select /*+ ordered use_nl(b) */ a.고객명, a.거주지역, a.주소, a.연락처, b.연체금액
from 고객 a, 고객별연체이력 b
where a.가입회사 = 'C70'
and b.고객번호 = a.고객번호
and a.서비스만료일 between b.시작일 and b.종료일;
고객테이블에는 서비스 만료일이 아래와 같이 '20591005'부터 '25520801'까지 10개의 값(고객 이 단 10명뿐이므로)이 들어있음
SQL> select min(서비스만료일) 최소만료일, max(서비스만료일) 최대만료일 from 고객;
최소만료일 최대만료일
20591005 25520801
이런상태에서 조금 전 보았던 between조인을 수행했다면, 고객에서 읽힌 값이 '25520801'일 때는 거의 처음부터 끝까지 스캔하고서야
조건을 만족하는 이력 데이터를 찾을 수 있음
현재 인덱스 구성상 시작일자가 종료일자보다 선행컬럼이기 때문임
반대로 '20591005'일때는 스캔량이 그리 많지 않을 것이다.
Rows Row Source Operation
---
---
10 TABLE ACCESS BY INDEX ROWID 고객별연체이력 (cr=2571 pr=0 pw=0 time=2980 us)
21 NESTED LOOPS (cr=2561 pr=0 pw=0 time=58107 us)
10 TABLE ACCESS BY INDEX ROWID 고객 (cr=4 pr=0 pw=0 time=199 us)
10 INDEX RANGE SCAN 고객_IDX01 (cr=2 pr=0 pw=0 time=94 us)(object id 42151)
10 INDEX RANGE SCAN 고개별연체이력_IDX01 (cr=2557 pr=0 pw=0 time=158260 us)(object id 42157)
10명의 연체이력을 조회하는데 고객별연체이력_idx01 인덱스 스캔 단계에서만 2,557개의 블록 I/O가 발생하였음
인덱스를 [종료일+시작일] 순으로바꾸더라도 나아질 것이 없음
rownum과 index(또는 index_desc)힌트를 적절히 사용할 수 있다면 인덱스 구성이 어떻든지 간에 항상 필요한 한 건만 스캔하도록 할수 있는데,
위 형태의 일반 조인문으로는 그럴수 없다는 것이 문제임
이럴때는 조인문을 스칼라 서브쿼리나 중첩된 서브쿼리(nested subquery) 형태로 바꾼다면 각 고객별로 단 하나의 이력만 읽도록 rownum <=1 조건을
추가해 줄수 있음
다행히 위에서는 고객별 연체이력 테이블로부터 연체금액 하나만 읽기때문에 아래와 같이 스칼라 서브쿼리로 간단히 변경할수 있음
select a.고객명, a.거주지역, a.주소, a.연락처
,(select /*+ index_desc(b 고객별연체이력_idx01) */ 연체금액
from 고객별연체이력 b
where b.고객번호 = a.고객번호
and a.서비스만료일 between 시작일 and 종료일
and rownum <= 1) 연체금액
from 고객 a
where 가입회사 = 'C70';
Rows Row Source Operation
---
---
10 COUNT STOPKEY (cr=40 pr=29 pw=0 time=226941 us)
10 TABLE ACCESS BY INDEX ROWID 고객별연체이력 (cr=40 pr=29 pw=0 time=226703 us)
10 INDEX RANGE SCAN DESCENDING 고객별연체이력_IDX01 (cr=30 pr=20 pw=0 time=141240 us)(object id 42157)
10 TABLE ACCESS BY INDEX ROWID 고객 (cr=4 pr=1 pw=0 time=19209 us)
10 INDEX RANGE SCAN 고객_IDX01 (cr=2 pr=1 pw=0 time=19302 us)(object id 42151)
만약 연체금액 과 연체개월수, 두 컬럼을 읽고자 한다면 어떻게 해야할까?
점이력 조회에서 이미 설명한 것처럼 문자열로 연결하고서 바깥쪽 액세스 쿼리에서 substr함수로 잘라쓰거나
아래와 같이 스칼라 서브쿼리에서 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 a.서비스만료일 between 시작일 and 종료일
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 | 7327K | 12 (0) | 00:00:01 | |
| 1 | NESTED LOOPS | 100K | 7327K | 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 |
| INDEX RANGE SCAN | 고객_IDX0 | 10 | 1 (0) | 00:00:01 | |
| 5 | TABLE ACCESS BY USER ROWID | 고객별연 | 10004 | 185K | 1 (0) | 00:00:01 |
--
여기서도 스칼라 서브쿼리 대신 아래와 같이 일반적인 서브쿼리로부터 읽은 rowid로 테이블을 직접 액세스하는 방법을 쓸수 있음
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 c.고객번호 = a.고객번호
and a.서비스만료일 between 시작일 and 종료일
and rownum <= 1);
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1000 | 62000 | 312 (0) | 00:00:04 | |
| 1 | NESTED LOOPS | 10M | 591M | 12 (0) | 00:00:01 | |
| 2 | TABLE ACCESS BY INDEX ROWID | 고객 | 10 | 430 | 2 (0) | 00:00:01 |
| INDEX RANGE SCAN | 고객_IDX01 | 10 | 1 (0) | 00:00:01 | |
| 4 | TABLE ACCESS BY USER ROWID | 고객별연체이력 | 1000K | 18M | 1 (0) | 00:00:01 |
| COUNT STOPKEY | |||||
| INDEX RANGE SCAN DESCENDING | 고객별연체이력 | 2 | 56 | 3 (0) | 00:00:01 |
h5. Between 조인 튜닝 - 조회대상이 많지만 대상별 이력 레코드가 많지 않을 때
조금전 사례 'C70' 회사를 통해 가입한 고객만 조회하는 경우 : 조회대상이 많지 않은 between조인 임
만약 전체 고객을 대상으로 한다면 Random 액세스 위주의 NL조인보다 아래처럼 해시조인을 이용하는 것이 효과적임
select /*+ ordered use_hash(b) */
a.고객명, a.거주지역, a.주소, a.연락처, b.연체금액, b.연체개월수
from 고객 a, 고객별연체이력 b
where b.고객번호 = a.고객번호
and a.서비스만료일 between b.시작일 and b.종료일;
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--
| 0 | SELECT STATEMENT | 2501 | 163K | 1077 (3) | 00:00:13 | |
| HASH JOIN | 2501 | 163K | 1077 (3) | 00:00:13 | |
| 2 | TABLE ACCESS FULL | 고객 | 10 | 390 | 3 (0) | 00:00:01 |
| 3 | TABLE ACCESS FULL | 고객별연 | 1000K | 26M | 1067 (2) | 00:00:13 |
--
h5. Between 조인 튜닝 - 대상별 이력 레코드가 많을때
!P345-Table.jpg!
표에서 맨 아래쪽에 있는 '대상별 이력 레코드가 많을 때의 between 조인' 이 가장 튜닝하기가 어려움
해시 테이블 탐색 비용이 큰이유 ( (5) Build Input 해시 키 값에 중복이 많을 때 발생하는 비효율)
대량 선분이력을 해시 조인하는데 각 해시 버킷에 많은 이력 레코드가 달리는 구조라면 매번 그것들을 스캔하면서 이력을 탐색하기 때문에 비효율이 생긴다는것
필자가 제안하는 첫번째 방안
두 개 이상 월에 걸치는 이력이 생기지 않도록 매월 마일 시점에 강제로 이력을 끊어주는 것
그러면 between 조인에 의한 스캔범위가 한 달을 넘지 않도록 새로운 조인 조건절을 추가해 줄수 있음
해시 체인을 스캔하는 비효율을 완전히 없 앨수는없지만 최대 31개가 넘지 않도록 제한하려는 것임
!Pic_2-43.jpg!
아래 쿼리를 통해 100개 상품에 대한 총 36만 건의 상품거래 데이터가 입력된 것을 알수 있음
SQL> -- 상품개수와 총거래건수 확인
SQL> select count(distinct 상품번호) 상품수, count(*)
2 from 일별상품거래;
상품수 COUNT(*)
--
--
100 365300
아래 결과 또한 상품번호별 거래 데이터가 평균 3,653건임을 알수 있음
SQL> -- 상품별 거래건수 확인
SQL> select avg(cnt)
2 from (select 상품번호, count(*) cnt
3 from 일별상품거래
4 group by 상품번호);
AVG(CNT)
--
3653
아래 쿼리를 통해 상품이력 테이블에는 상품별로 평균 913건의 이력이 존재하는 것을 알수 있음
평균적으로 4일에 한번(=2,653/913)씩 이력 데이터가 생성된 셈임
SQL> -- 상품별 이력건수 확인
SQL> select 상품번호, min(시작일자), max(시작일자), count(*)
2 from 상품이력
3 group by 상품번호
4 order by 1, 2
5 ;
상품번호 MIN(시작일자) MAX(시작일자) COUNT(*)
--
--
A0001 20000409 20100409 914
A0002 20000409 20100408 913
A0003 20000409 20100407 913
A0004 20000409 20100406 913
A0005 20000409 20100409 914
A0006 20000409 20100408 913
A0007 20000409 20100407 913
A0008 20000409 20100406 913
A0009 20000409 20100409 914
A0010 20000409 20100408 913
A0011 20000409 20100407 913
A0012 20000409 20100406 913
A0013 20000409 20100409 914
A0014 20000409 20100408 913
A0015 20000409 20100407 913
A0016 20000409 20100406 913
A0017 20000409 20100409 914
A0018 20000409 20100408 913
A0019 20000409 20100407 913
A0020 20000409 20100406 913
A0021 20000409 20100409 914
A0022 20000409 20100408 913
A0023 20000409 20100407 913
A0024 20000409 20100406 913
A0025 20000409 20100409 914
A0026 20000409 20100408 913
A0027 20000409 20100407 913
A0028 20000409 20100406 913
A0029 20000409 20100409 914
A0030 20000409 20100408 913
A0031 20000409 20100407 913
A0032 20000409 20100406 913
A0033 20000409 20100409 914
A0034 20000409 20100408 913
A0035 20000409 20100407 913
A0036 20000409 20100406 913
A0037 20000409 20100409 914
A0038 20000409 20100408 913
A0039 20000409 20100407 913
A0040 20000409 20100406 913
A0041 20000409 20100409 914
A0042 20000409 20100408 913
A0043 20000409 20100407 913
A0044 20000409 20100406 913
A0045 20000409 20100409 914
A0046 20000409 20100408 913
A0047 20000409 20100407 913
A0048 20000409 20100406 913
A0049 20000409 20100409 914
A0050 20000409 20100408 913
A0051 20000409 20100407 913
A0052 20000409 20100406 913
A0053 20000409 20100409 914
A0054 20000409 20100408 913
A0055 20000409 20100407 913
A0056 20000409 20100406 913
A0057 20000409 20100409 914
A0058 20000409 20100408 913
A0059 20000409 20100407 913
A0060 20000409 20100406 913
A0061 20000409 20100409 914
A0062 20000409 20100408 913
A0063 20000409 20100407 913
A0064 20000409 20100406 913
A0065 20000409 20100409 914
A0066 20000409 20100408 913
A0067 20000409 20100407 913
A0068 20000409 20100406 913
A0069 20000409 20100409 914
A0070 20000409 20100408 913
A0071 20000409 20100407 913
A0072 20000409 20100406 913
A0073 20000409 20100409 914
A0074 20000409 20100408 913
A0075 20000409 20100407 913
A0076 20000409 20100406 913
A0077 20000409 20100409 914
A0078 20000409 20100408 913
A0079 20000409 20100407 913
A0080 20000409 20100406 913
A0081 20000409 20100409 914
A0082 20000409 20100408 913
A0083 20000409 20100407 913
A0084 20000409 20100406 913
A0085 20000409 20100409 914
A0086 20000409 20100408 913
A0087 20000409 20100407 913
A0088 20000409 20100406 913
A0089 20000409 20100409 914
A0090 20000409 20100408 913
A0091 20000409 20100407 913
A0092 20000409 20100406 913
A0093 20000409 20100409 914
A0094 20000409 20100408 913
A0095 20000409 20100407 913
A0096 20000409 20100406 913
A0097 20000409 20100409 914
A0098 20000409 20100408 913
A0099 20000409 20100407 913
A0100 20000409 20100406 913
100 개의 행이 선택되었습니다.
SQL> -- 평균이력건수 확인
SQL> select avg(cnt)
2 from (
3 select 상품번호, count(*) cnt
4 from 상품이력
5 group by 상품번호
6 )
7 ;
AVG(CNT)
--
913.25
마지막으로, 상품이력 테이블에 아래와 같이 인덱스를 만들고 통계정보를 생성했음
-- 인덱스 생성
create index 상품이력_idx on 상품이력(상품번호, 시작일자, 종료일자);
-- 통계정보 수집
exec dbms_stats.gather_table_stats(user, '일별상품거래');
exec dbms_stats.gather_table_stats(user, '상품이력');
인덱스는 NL조인으로 수행할 때의 속도도 함께 비교하려고 만든것
과거부터 최근이력까지 골고로 조회할 것이므로 컬럼 순서는 중요하지 않음
아래는 NL조인방식으로 between 조인을 수행한 결과임
-- 일반적인 선분이력 조회 (NL 조인 시)
select /*+ leading(b) use_nl(a) index(a 상품이력_idx)*/
sum(b.거래수량) 총거래수량
, sum(b.거래수량 * a.판매가) 총판매금액
, round(avg(b.거래수량 * a.판매가)) 평균판매금액
from 상품이력 a, 일별상품거래 b
where b.상품번호 = a.상품번호
and b.거래일자 between a.시작일자 and a.종료일자
call count cpu elapsed disk query current rows
---
--
--
--
--
--
--
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 59.18 59.19 0 1900386 0 1
---
--
--
--
--
--
--
total 4 59.18 59.19 0 1900386 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 38
Rows Row Source Operation
---
---
1 SORT AGGREGATE (cr=1900386 pr=0 pw=0 time=59198602 us)
365300 TABLE ACCESS BY INDEX ROWID 상품이력 (cr=1900386 pr=0 pw=0 time=69041951 us)
730601 NESTED LOOPS (cr=1535086 pr=0 pw=0 time=5902350 us)
365300 TABLE ACCESS FULL 일별상품거래 (cr=1169 pr=0 pw=0 time=1461267 us)
365300 INDEX RANGE SCAN 상품이력_IDX (cr=1533917 pr=0 pw=0 time=54477996 us)(object id 42247)
190만 개 블록을 읽으면서 59초 가량 소요
디스크 I/O가 포함되면 속도가 들쭉날쭉하기 때문에 디스크 I/O가 발생하지 않도록 몇 번 수행한 상태에서 위 트레이스를 수집하였음
실제 상황이라면 디스크 I/O 때문에 이보다 더 많은 시간이 소요될 것임
이번에는 stopkey조건을 적용한 서브쿼리로부터 rowid를 읽어 직접 이력 테이블을 액세스하는 방식으로 수행해 봄
-- 튜닝한 선분이력 조회 (NL 조인 및 rowid 이용)
-- (참고로, 아래 쿼리는 SQL 트레이스 걸면 매우 오래 걸리지만 그냥 수행하면 굉장히 빠르게 조회됩니다.
– 9i, 10g, 11g에서 공통적으로 나타나는 현상이며, 버그라고 생각됩니다.)
select /*+ ordered use_nl(b) rowid(b) */
sum(a.거래수량) 총거래수량
, sum(a.거래수량 * b.판매가) 총판매금액
, round(avg(a.거래수량 * b.판매가)) 평균판매금액
from 일별상품거래 a, 상품이력 b
where b.rowid = (select /*+ index_desc(c 상품이력_idx)*/ rowid
from 상품이력 c
where 상품번호 = a.상품번호
and a.거래일자 between c.시작일자 and c.종료일자
and rownum <= 1)
;
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 24.82 24.83 0 1462636 0 1
---
--
--
--
--
--
--
total 4 24.84 24.83 0 1462636 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 38
Rows Row Source Operation
---
---
1 SORT AGGREGATE (cr=1462636 pr=0 pw=0 time=24832371 us)
365300 NESTED LOOPS (cr=1462636 pr=0 pw=0 time=27762924 us)
365300 TABLE ACCESS FULL 일별상품거래 (cr=1169 pr=0 pw=0 time=1461254 us)
365300 TABLE ACCESS BY USER ROWID 상품이력 (cr=1461467 pr=0 pw=0 time=21880251 us)
365300 COUNT STOPKEY (cr=1096167 pr=0 pw=0 time=13975594 us)
365300 INDEX RANGE SCAN DESCENDING 일별상품거래_IDX (cr=1096167 pr=0 pw=0 time=6960804 us)(object id 42247)
146만 개 블록을 읽으면서 24초 가량 소요됨 (이것은 SQL 트레이스 때문에 그런것임)
SQL 트레이스를 걸지않고 그냥 수행해 보면 불과 4.10초 만에 결과가 나옴
아래는 Autotrace만 걸고 수행해 본 것인데, I/O 횟수까지 똑같은데 수행속도는 천양지차 ( 24초 -> 4.10초)
SQL> set timing on
SQL> set autotrace traceonly
SQL> select /*+ ordered use_nl(b) rowid(b) */
2 sum(a.거래수량) 총거래수량
3 , sum(a.거래수량 * b.판매가) 총판매금액
4 , round(avg(a.거래수량 * b.판매가)) 평균판매금액
5 from 일별상품거래 a, 상품이력 b
6 where b.rowid = (select /*+ index_desc(c 상품이력_idx)*/ rowid
7 from 상품이력 c
8 where 상품번호 = a.상품번호
9 and a.거래일자 between c.시작일자 and c.종료일자
10 and rownum <= 1)
11 ;
경 과: 00:00:04.10
Execution Plan
--
Plan hash value: 3446302624
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
| 0 | SELECT STATEMENT | 1 | 33 | 98G (1) | 999:59:59 | |
| 1 | SORT AGGREGATE | 1 | 33 | |||
| 2 | NESTED LOOPS | 33G | 1015G | 366K (1) | 01:13:23 | |
| 3 | TABLE ACCESS FULL | 일별상품 | 366K | 6441K | 267 (2) | 00:00:04 |
| 4 | TABLE ACCESS BY USER ROWID | 상품이력 | 90170 | 1320K | 1 (0) | 00:00:01 |
| COUNT STOPKEY | |||||
| INDEX RANGE SCAN DESCENDING | 상품이력_ | 2 | 54 | 3 (0) | 00:00:01 |
Predicate Information (identified by operation id):
---
5 - filter(ROWNUM<=1)
6 - access("상품번호"=:B1 AND "C"."종료일자">=:B2 AND "C"."시작일자"<=:B3)
filter("C"."종료일자">=:B1)
Statistics
--
0 recursive calls
0 db block gets
1462636 consistent gets
0 physical reads
0 redo size
589 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL 트레이스를 걸면 쿼리가 비정상적으로 오래 걸리 때가 가끔 있으며, 대개는 버그에 의한것임
SQL 트레이스를 거지 않은 정상적인 상태가 기준이어야 하므로 59초에서 4.10초로 수행속도가 감소했다고 평가할수 있음
(앞에서 between조인문으로 쿼리한 경우는 SQL트레이스를 걸지 않더라도 거의 같은 속도로 보였음)
SQL> set timing on
SQL> set autotrace traceonly
SQL> select /*+ leading(b) use_nl(a) index(a 상품이력_idx)*/
2 sum(b.거래수량) 총거래수량
3 , sum(b.거래수량 * a.판매가) 총판매금액
4 , round(avg(b.거래수량 * a.판매가)) 평균판매금액
5 from 상품이력 a, 일별상품거래 b
6 where b.상품번호 = a.상품번호
7 and b.거래일자 between a.시작일자 and a.종료일자
8 ;
경 과: 00:00:51.09
Execution Plan
--
Plan hash value: 747232455
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-
| 0 | SELECT STATEMENT | 1 | 45 | 1100K (1) | 03:40:04 | |
| 1 | SORT AGGREGATE | 1 | 45 | |||
| 2 | TABLE ACCESS BY INDEX ROWID | 상품이력 | 2 | 54 | 3 (0) | 00:00:01 |
| 3 | NESTED LOOPS | 826K | 35M | 1100K (1) | 03:40:04 | |
| 4 | TABLE ACCESS FULL | 일별상품 | 366K | 6441K | 267 (2) | 00:00:04 |
| INDEX RANGE SCAN | 상품이력_ | 2 | 2 (0) | 00:00:01 |
-
Predicate Information (identified by operation id):
---
5 - access("B"."상품번호"="A"."상품번호" AND "B"."거래일자"<="A"."종료일자" AND
"B"."거래일자">="A"."시작일자")
filter("B"."거래일자"<="A"."종료일자")
Statistics
--
1 recursive calls
2 db block gets
1900386 consistent gets
0 physical reads
0 redo size
589 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
조금 전 쿼리가 4.10초 만에 수행될 수 있었던 것은 디스크 I/O가 전혀 발생하지 않도록 한 상태에서 측정했기 때문
실제 운영 환경에서 디스크 I/O까지 수반한 Random 액세스 방식으로 146만개 블록을 읽는다면 수십초에서 수분이 걸릴 수 있음
이처럼 대량 데이터를 조인할 때 NL 조인은 비효율적이므로 이번에는 해시 조인으로 바꿔서 수행해봄
-- 일반적인 선분이력 조회 (해시 조인 시)
select /*+ leading(a) use_hash(b) */
sum(b.거래수량) 총거래수량
, sum(b.거래수량 * a.판매가) 총판매금액
, round(avg(b.거래수량 * a.판매가)) 평균판매금액
from 상품이력 a, 일별상품거래 b
where b.상품번호 = a.상품번호
and b.거래일자 between a.시작일자 and a.종료일자
;
call count cpu elapsed disk query current rows
---
--
--
--
--
--
--
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 114.01 114.24 1020 1578 0 1
---
--
--
--
--
--
--
total 4 114.01 114.24 1020 1578 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 38
Rows Row Source Operation
---
---
1 SORT AGGREGATE (cr=1578 pr=1020 pw=1020 time=114246968 us)
365300 HASH JOIN (cr=1578 pr=1020 pw=1020 time=115939618 us)
91325 TABLE ACCESS FULL 상품이력 (cr=409 pr=0 pw=0 time=365367 us)
365300 TABLE ACCESS FULL 일별상품거래 (cr=1169 pr=0 pw=0 time=1461275 us)
해시 조인을 이용했더니 114초나 걸렸음
인덱스 기반의 between 조인할 때(59초) 보다 더 오래걸린 것임
이유는, 각 상품별 이력이 평균 913건이나 되기 때문에 해시 테이블 탐색 비용이 매우 높은 것이 원인임
앞서 제시한 방안에 따라
두 개 이상 월에 걸치는 이력 레코드가 없도록 '상품이력2' 테이블을 만들어봄
선분형태의 이력이지만 한 달 범위를 넘지 않도록 했기 때문에 아래와 같이 '=' 조인문을 하나 더 추가해 줄 수 있음
상품번호 외에 월 조건까지 해시 키(Key)값으로 사용되게 되었으므로 해시 버킷에서 스캔해야 할 양은 최대 31개를 넘지 않음
select /*+ leading(a) use_hash(b) */
sum(b.거래수량) 총거래수량
, sum(b.거래수량 * a.판매가) 총판매금액
, round(avg(b.거래수량 * a.판매가)) 평균판매금액
from 상품이력2 a, 일별상품거래 b
where b.상품번호 = a.상품번호
and b.거래일자 between a.시작일자 and a.종료일자
and trunc(to_date(b.거래일자, 'yyyymmdd'), 'mm') = trunc(to_date(a.시작일자, 'yyyymmdd'), 'mm')
;
call count cpu elapsed disk query current rows
---
--
--
--
--
--
--
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.43 3.04 1560 1617 0 1
---
--
--
--
--
--
--
total 4 2.43 3.04 1560 1617 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 38
Rows Row Source Operation
---
---
1 SORT AGGREGATE (cr=1617 pr=1560 pw=1560 time=3043716 us)
365300 HASH JOIN (cr=1617 pr=1560 pw=1560 time=10108271 us)
100325 TABLE ACCESS FULL 상품이력2 (cr=448 pr=0 pw=0 time=401348 us)
365300 TABLE ACCESS FULL 일별상품거래 (cr=1169 pr=0 pw=0 time=1461263 us)
상품이력2 테이블에서 출력된 건수는 앞에서보다 9,000(=100,325-91,325)건 더 많아졌지만 성능은 비교할수 없이 빨라졌음
두번째 방안은 두개 이상 월에 걸치는 이력이 없도록 쿼리 시점에 선분이력을 변환해 주는 것임
그런 다음 조인하는 방법은 앞에서와 같고, 마찬가지로 해시 체인을 스캔하는 양을 최대 31개로 제한될 것임
예를 들어 회사의 역사가 20년 됐다고 가정하고, 먼저 아래처럼 월도 테이블을 만듬
create table 월도
as
select to_char(sysdate, 'yyyymm') 기준월, to_char(trunc(sysdate, 'mm'), 'yyyymmdd') 시작일자, '99991231' 종료일자
from dual
union all
select to_char(add_months(trunc(sysdate, 'mm'), -rownum), 'yyyymm')
, to_char(add_months(trunc(sysdate, 'mm'), -rownum), 'yyyymmdd')
, to_char(add_months(last_day(sysdate), -rownum), 'yyyymmdd')
from dual
connect by level <= 12*20
;
데이터는 아래와 같은 형식으로 입력돼 있음
SQL> select * from 월도 order by 1;
기준월 시작일자 종료일자
199004 19900401 19900430
199005 19900501 19900531
199006 19900601 19900630
199007 19900701 19900731
199008 19900801 19900831
............
200909 20090901 20090930
200910 20091001 20091031
200911 20091101 20091130
200912 20091201 20091231
201001 20100101 20100131
201002 20100201 20100228
201003 20100301 20100331
201004 20100401 99991231
아래와 같이 부등호 조건으로 '월도'테이블 과 '상품이력' 테이블을 조인한다면 '(2) 선분이력 끊기'에서 자세히
설명했듯이 두 개 이상 월에 걸친 상품이력이 여러개로 복제된다.
select a.기준월, b.시작일자, b.종료일자
from 월도 a, 상품이력 b
where b.시작일자 <= a.종료일자
and b.종료일자 >= a.시작일자
group by a.기준월 , b.시작일자, b.종료일자
상품이력이 여러 개 생기더라도 기준월은 각각 다른 값을 가지므로 거래월과 '=' 조인할 수 있다.
예를 들어, 20090821 ~ 20091007 기간에 상품이력이 있다면 아래 표와 같은 데이터가 만들어질 것임
!p352-Table.jpg!
select /*+ ordered use_merge(b) use_hash© */
sum(c.거래수량) 총거래수량
, sum(c.거래수량 * b.판매가) 총판매금액
, round(avg(c.거래수량 * b.판매가)) 평균판매금액
from 월도 a, 상품이력 b, 일별상품거래 c
where b.시작일자 <= a.종료일자
and b.종료일자 >= a.시작일자
and c.상품번호 = b.상품번호
and c.거래일자 between b.시작일자 and b.종료일자
and a.기준월 || '01' = trunc(to_date(c.거래일자, 'yyyymmdd'), 'mm')
;
call count cpu elapsed disk query current rows
---
--
--
--
--
--
--
Parse 1 0.00 0.00 0 1 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 7.09 15.03 35317 1581 18 1
---
--
--
--
--
--
--
total 5 7.09 15.03 35317 1582 18 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 38
Rows Row Source Operation
---
---
1 SORT AGGREGATE (cr=1581 pr=35317 pw=3765 time=15033994 us)
365300 HASH JOIN (cr=1581 pr=35317 pw=3765 time=25078378 us)
100325 MERGE JOIN (cr=412 pr=33067 pw=1515 time=21383572 us)
241 SORT JOIN (cr=3 pr=0 pw=0 time=1533 us)
241 TABLE ACCESS FULL 월도 (cr=3 pr=0 pw=0 time=1013 us)
100325 FILTER (cr=409 pr=33067 pw=1515 time=2267154 us)
5591825 SORT JOIN (cr=409 pr=33067 pw=1515 time=29578619 us)
91325 TABLE ACCESS FULL 상품이력 (cr=409 pr=0 pw=0 time=365321 us)
365300 TABLE ACCESS FULL 일별상품거래 (cr=1169 pr=0 pw=0 time=1461263 us)
select /*+ ordered use_merge(b) use_hash© */
sum(c.거래수량) 총거래수량
, sum(c.거래수량 * b.판매가) 총판매금액
, round(avg(c.거래수량 * b.판매가)) 평균판매금액
from 월도 a, 상품이력 b, 일별상품거래 c
where b.시작일자 <= a.종료일자
and b.종료일자 >= a.시작일자
and c.상품번호 = b.상품번호
and c.거래일자 between b.시작일자 and b.종료일자
and trunc(to_date(c.거래일자, 'yyyymmdd'), 'mm') = trunc(to_date(a.시작일자, 'yyyymmdd'), 'mm')
;
call count cpu elapsed disk query current rows
---
--
--
--
--
--
--
Parse 1 0.00 0.00 0 1 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 7.43 14.39 35332 1581 18 1
---
--
--
--
--
--
--
total 5 7.43 14.40 35332 1582 18 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 38
Rows Row Source Operation
---
---
1 SORT AGGREGATE (cr=1581 pr=35332 pw=3780 time=14395110 us)
365300 HASH JOIN (cr=1581 pr=35332 pw=3780 time=14486339 us)
100325 MERGE JOIN (cr=412 pr=33067 pw=1515 time=20984633 us)
241 SORT JOIN (cr=3 pr=0 pw=0 time=1769 us)
241 TABLE ACCESS FULL 월도 (cr=3 pr=0 pw=0 time=1017 us)
100325 FILTER (cr=409 pr=33067 pw=1515 time=2094984 us)
5591825 SORT JOIN (cr=409 pr=33067 pw=1515 time=29415760 us)
91325 TABLE ACCESS FULL 상품이력 (cr=409 pr=0 pw=0 time=365320 us)
365300 TABLE ACCESS FULL 일별상품거래 (cr=1169 pr=0 pw=0 time=1461263 us)
미리 만들어 둔 '상품이력2' 테이블을 이용할 때보다는 느리지만 그냥 해시 조인할 때의 114초 보다는 훨씬 빨랐음
이 방식을 이용하면 '일별상품거래'와 조인할 때는 빠르지만 '월도' 테이블과 조인하는 과정에서 오히려 병목이 생길수 있음
h5. Between 조인튜닝 요약
대상별 이력레코드가 많을 때의 between조인은 좋은 성능을 내기가 쉽지 않음을 알수 있음
많은 이력을 가진 마스터 데이터와 넓은 검색 구간의 거래 데이터를 조회하는 경우가 여기에 해당함
이것은 정보 시스템에서 자주사용하는 쿼리 패턴임
마스터 테이블 이력이라면 월말 시점마다 선분을 끊어주는 것을 고려하기 바람
쿼리 수행 시점에 실시간으로 선분을 끊는 방법도 있지만 복제되는 양에 따라 만족할만한 성능이 안 나올수 있고, 무엇보다 쿼리가 복잡해진다는게 단점
마스터 데이터 건수가 적으면서 변경이 잦은 경우라면
매일 전체 대상집합을 새로 저장하는 이력관리 방식(->스냅샷 형태)도 고려 가능함
변경이 발생하지 않는 대상 집합도 매일 새로 저장하기 때문에 데이터량은 더 많아지겠지만 대용량 조회시 검색효율은 오히려 좋아짐
물론 마스터 데이터 건수가 아주많을 때는 (수백만 개의 상품, 수천만 명의 고객 등) 고려의 대상이 못됨
h1. (10)조인에 실패한 레코드 읽기
조인에 실패했을때, 정해진 특정 레코드에서 가져온 값으로 보여주고 싶을때는 어떻게 쿼리해야할까?
cdr_rating(CDR 과금)테이블에 아래와 같이 국가와 지역별 요금 정보가 입력돼 있음
SQL> select 국가코드, '''' || 지역 || '''', 요금 from cdr_rating ;
국가 ''''||지역||'''' 요금
--
82 'A' 100
82 'B' 200
82 'C' 500
82 'D' 300
82 'E' 100
84 'A' 300
84 'B' 500
84 'C' 400
84 ' ' 800
86 'A' 500
86 'B' 200
86 ' ' 700
국가코드 82(한국)는 모든 지역에 대한 요금정보를 갖고 있음
하지만 국가코드 84(베트남)의 경우 A,B,C 가 아닌지역에 대해서는 일괄적으로 800원을 부과하려고 위와 같이 공백문자(' ')로 입력해 둔 것임
국가코드 86(중국)도 A,B가 아닌 지역에 대해서는 일괄적으로 700원이 부과됨
SQL> select * from cdr;
통화시간 국가 지역
--
20050315 010101 82 A
20050315 020101 82 B
20050315 030101 82 C
20050315 040101 84 A
20050315 050101 84 B
20050315 060101 84 C
20050315 070101 84 D --> cdr_rating에 매칭되는 정보 없음
20050315 080101 84 E |
20050315 090101 86 A
20050315 100101 86 B
20050315 110101 86 C --> cdr_rating에 매칭되는 정보 없음
20050315 120101 86 D |
20050315 130101 86 E |
20050315 140101 86 F |
이제 cdr 과 cdr_rating 두 테이블을 조인해 2005년 3월 15일 시점 통화내역과 요금정보를 출력하려 하는데,
일반적인 조인문을 사용하면 아래와 같이 조인에 실패한 통화내역은 출력이 되지 않음
SQL> select /*+ ordered use_nl® */
2 c.통화시간, c.국가코드, c.지역, r.요금
3 from cdr c, cdr_rating r
4 where c.통화시간 like '20050315%'
5 and c.국가코드 = r.국가코드
6 and c.지역 = r.지역 ;
통화시간 국가 지역 요금
--
--
20050315 010101 82 A 100
20050315 020101 82 B 200
20050315 030101 82 C 500
20050315 040101 84 A 300
20050315 050101 84 B 500
20050315 060101 84 C 400
20050315 090101 86 A 500
20050315 100101 86 B 200
cdr을 기준으로 Outer조인하면 통화내역은 모두 출력되겠지만 기타지역 통화내역에 대한 요금정보가 Null로 출력됨
SQL> select /*+ ordered use_nl® */
2 c.통화시간, c.국가코드, c.지역, r.요금
3 from cdr c, cdr_rating r
4 where c.통화시간 like '20050315%'
5 and c.국가코드 = r.국가코드(+)
6 and c.지역 = r.지역(+) ;
통화시간 국가 지역 요금
--
--
20050315 010101 82 A 100
20050315 020101 82 B 200
20050315 030101 82 C 500
20050315 040101 84 A 300
20050315 050101 84 B 500
20050315 060101 84 C 400
20050315 070101 84 D
20050315 080101 84 E
20050315 090101 86 A 500
20050315 100101 86 B 200
20050315 110101 86 C
20050315 120101 86 D
20050315 130101 86 E
20050315 140101 86 F
어떻게 쿼리해야 조인에 실패했을 때 지역이 공백 (' ')인 요금 정보를 가져올 수 있을까?
SQL> select /*+ ordered use_nl® */
2 c.통화시간, c.국가코드, c.지역, r.요금
3 from cdr c, cdr_rating r
4 where c.통화시간 like '20050315%'
5 and (r.국가코드, r.지역) =
6 (select c.국가코드, max(지역)
7 from cdr_rating
8 where 국가코드 = c.국가코드
9 and 지역 in (' ', c.지역) ) ;
통화시간 국가 지역 요금
--
--
20050315 010101 82 A 100
20050315 020101 82 B 200
20050315 030101 82 C 500
20050315 040101 84 A 300
20050315 050101 84 B 500
20050315 060101 84 C 400
20050315 070101 84 D 800
20050315 080101 84 E 800
20050315 090101 86 A 500
20050315 100101 86 B 200
20050315 110101 86 C 700
20050315 120101 86 D 700
20050315 130101 86 E 700
20050315 140101 86 F 700
9i까지는 use_concat 힌트를 이용해 아래와 같이 쿼리할수 있음
IN-List가 concatenation(or-expansion)방식으로 풀리면 뒤쪽에 있는 값이 먼저 실행되는 특징을 이용 하는 것임
select /*+ ordered use_nl® index(r pk_cdr_rating) */
c.통화시간, c.국가코드, c.지역, r.요금
from cdr c, cdr_rating r
where c.통화시간 like '20050315%'
and (r.국가코드, r.지역) =
(select /*+ use_concat */ c.국가코드, 지역
from cdr_rating
where 국가코드 = c.국가코드
and 지역 in (' ', c.지역)
and rownum <= 1)
call count cpu elapsed disk query current rows
---
--
--
--
--
--
--
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 35 0 14
---
--
--
--
--
--
--
total 4 0.00 0.00 0 35 0 14
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
---
---
14 NESTED LOOPS
14 INDEX RANGE SCAN PK_CDR (object id 32540)
14 TABLE ACCESS BY INDEX ROWID CDR_RATING
14 INDEX UNIQUE SCAN PK_CDR_RATING (object id 32538)
14 COUNT STOPKEY
14 INLIST ITERATOR
14 INDEX RANGE SCAN PK_CDR_RATING (object id 32538)
통화시간 국 지역 요금
--- --
--
--
20050315 010101 82 A 100
20050315 020101 82 B 200
20050315 030101 82 C 500
20050315 040101 84 A 800
20050315 050101 84 B 800
20050315 060101 84 C 800
20050315 070101 84 D 800
20050315 080101 84 E 800
20050315 090101 86 A 700
20050315 100101 86 B 700
20050315 110101 86 C 700
20050315 120101 86 D 700
20050315 130101 86 E 700
20050315 140101 86 F 700
-> 책내용: 아래와 같이하면 결과가 틀리게 됨
모든 결과에서 INLIST ITERATOR 로풀림 (Oracle 9.2.0.8)
select /*+ ordered use_nl® index(r pk_cdr_rating) */
c.통화시간, c.국가코드, c.지역, r.요금
from cdr c, cdr_rating r
where c.통화시간 like '20050315%'
and (r.국가코드, r.지역) =
(select /*+ use_concat */ c.국가코드, 지역
from cdr_rating
where 국가코드 = c.국가코드
and 지역 in (c.지역,' ')
and rownum <= 1)
call count cpu elapsed disk query current rows
---
--
--
--
--
--
--
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 35 0 14
---
--
--
--
--
--
--
total 4 0.00 0.00 0 35 0 14
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
Rows Row Source Operation
---
---
14 NESTED LOOPS
14 INDEX RANGE SCAN PK_CDR (object id 32540)
14 TABLE ACCESS BY INDEX ROWID CDR_RATING
14 INDEX UNIQUE SCAN PK_CDR_RATING (object id 32538)
14 COUNT STOPKEY
14 INLIST ITERATOR
14 INDEX RANGE SCAN PK_CDR_RATING (object id 32538)
통화시간 국 지역 요금
--- --
--
--
20050315 010101 82 A 100
20050315 020101 82 B 200
20050315 030101 82 C 500
20050315 040101 84 A 800
20050315 050101 84 B 800
20050315 060101 84 C 800
20050315 070101 84 D 800
20050315 080101 84 E 800
20050315 090101 86 A 700
20050315 100101 86 B 700
20050315 110101 86 C 700
20050315 120101 86 D 700
20050315 130101 86 E 700
20050315 140101 86 F 700
10g부터는 일반적인 use_concat 힌트로는 OR-Expansion 일어나지 않기 때문에 위와 같은 기법을 쓸수 없음
- use_concat에 특별한 인자를 넣어 위와 같은 방식으로 유도할순 있지만 CPU 비용모델에서는 통계정보상 카디널리티가 작은 값이 먼저 실행되지 때문에 결과가 보장될질 않음
10g에서는 위와 같은 기법을 사용하려면
- ordered_predicates 힌트를 사용하거나 no_cpu_costing힌트를 이용해 I/O비용 모델로 바꿔줘야함
SQL> explain plan for
2 select /*+ ordered use_nl® index(r pk_cdr_rating) */
3 c.통화시간, c.국가코드, c.지역, r.요금
4 from cdr c, cdr_rating r
5 where c.통화시간 like '20050315%'
6 and (r.국가코드, r.지역) =
7 (select /*+ use_concat(@subq 1) qb_name(subq) ordered_predicates */ c.국가코드, 지역
8 from cdr_rating
9 where 국가코드 = c.국가코드
10 and 지역 in (' ', c.지역)
11 and rownum <= 1) ;
해석되었습니다.
SQL> @plan
PLAN_TABLE_OUTPUT
Plan hash value: 1842647818
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--
| 0 | SELECT STATEMENT | 1 | 29 | 2 (0) | 00:00:01 | |
| 1 | NESTED LOOPS | 9 | 261 | 2 (0) | 00:00:01 | |
| INDEX RANGE SCAN | PK_CDR | 13 | 273 | 1 (0) | 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | CDR_RATING | 1 | 8 | 1 (0) | 00:00:01 |
| INDEX UNIQUE SCAN | PK_CDR_RATING | 1 | 0 (0) | 00:00:01 | |
| COUNT STOPKEY | |||||
| 6 | CONCATENATION | |||||
| FILTER | |||||
| INDEX UNIQUE SCAN | PK_CDR_RATING | 1 | 5 | 0 (0) | 00:00:01 |
| FILTER | |||||
| INDEX UNIQUE SCAN | PK_CDR_RATING | 1 | 5 | 0 (0) | 00:00:01 |
--
Predicate Information (identified by operation id):
---
2 - access("C"."통화시간" LIKE '20050315%')
filter("C"."통화시간" LIKE '20050315%')
4 - access(("R"."국가코드","R"."지역")= (SELECT /*+ USE_CONCAT (1) QB_NAME ("SUBQ") */
:B1,"지역" FROM "CDR_RATING" "CDR_RATING"???)
5 - filter(ROWNUM<=1)
7 - filter(ROWNUM<=1)
8 - access("국가코드"=:B1 AND "지역"=:B2)
9 - filter(ROWNUM<=1)
10 - access("국가코드"=:B1 AND "지역"=' ')
filter(LNNVL("지역"=:B1))
아래는 인덱스를 두 번 액세스하지 않고 서브쿼리에서 얻은 rowid로 테이블을 직접 액세스하도록 최종적으로 튜닝한 것임
SQL> explain plan for
2 select /*+ ordered use_nl® rowid® */
3 c.통화시간, c.국가코드, c.지역, r.요금
4 from cdr c, cdr_rating r
5 where c.통화시간 like '20050315%'
6 and r.rowid =
7 (select /*+ use_concat(@subq 1) qb_name(subq) ordered_predicates */ rowid
8 from cdr_rating
9 where 국가코드 = c.국가코드
10 and 지역 in (' ', c.지역)
11 and rownum <= 1
12 ) ;
해석되었습니다.
SQL> @plan
PLAN_TABLE_OUTPUT
--
Plan hash value: 1791744221
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-
| 0 | SELECT STATEMENT | 1 | 29 | 14 (0) | 00:00:01 | |
| 1 | NESTED LOOPS | 156 | 4524 | 14 (0) | 00:00:01 | |
| INDEX RANGE SCAN | PK_CDR | 13 | 273 | 1 (0) | 00:00:01 |
| 3 | TABLE ACCESS BY USER ROWID | CDR_RATING | 12 | 96 | 1 (0) | 00:00:01 |
| COUNT STOPKEY | |||||
| 5 | CONCATENATION | |||||
| FILTER | |||||
| INDEX UNIQUE SCAN | PK_CDR_RATING | 1 | 8 | 0 (0) | 00:00:01 |
| FILTER | |||||
| INDEX UNIQUE SCAN | PK_CDR_RATING | 1 | 8 | 0 (0) | 00:00:01 |
-
Predicate Information (identified by operation id):
---
2 - access("C"."통화시간" LIKE '20050315%')
filter("C"."통화시간" LIKE '20050315%')
4 - filter(ROWNUM<=1)
6 - filter(ROWNUM<=1)
7 - access("국가코드"=:B1 AND "지역"=:B2)
8 - filter(ROWNUM<=1)
9 - access("국가코드"=:B1 AND "지역"=' ')
filter(LNNVL("지역"=:B1))
h2. 문서에 대하여
* 최초작성자 : [~kwlee55]
* 최초작성일 : 2010년 04월 08일
* 이 문서는 [오라클클럽|http://www.gurubee.net] [코어 오라클 데이터베이스 스터디|4차 코어 오라클 데이터베이스 스터디] 모임에서 작성하였습니다.
* {color:blue}{*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*{color}