고급 조인 테크닉 (by niceboyous) [2013.01.29]
Contents
- (1) 누적매출구하기
- (2) 선분이력 끊기
- (3) 데이터 복제를 통한 소계 구하기
- (4) 상호배타적 관계의 조인
- (5) 최종 출력 건에 대해서만 조인하기
- (6) 징검다리 테이블 조인을 이용한 튜닝
- --(7) 점이력 조회
- --(8) 선분이력 조인
- (9) 선분이력 조인 튜닝
- --(10) 조인에 실패한 레크드 읽기
(1) 누적매출구하기
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 ;
select * from 월별지점매출;
----------------------------------------------------------
지점 판매월 매출
---------- ---------- ----------
10 1 692
10 2 925
10 3 751
20 1 980
20 2 977
20 3 822
20 4 653
20 5 877
30 1 589
30 2 628
30 3 533
30 4 515
30 5 849
30 6 603
14 개의 행이 선택되었습니다.
경 과: 00:00:00.01
----------------------------------------------------------
- 누적 매출을 구한다. 각 지점별로 판매월과 함께 증가하는 누적매출을 구한다.
- Oracle 8i 이상부터는 분석 함수를 이용하여 간단하게 결과를 얻을 수 있다.
select 지점, 판매월, 매출
, sum(매출) over (partition by 지점 order by 판매월
range between unbounded preceding and current row) 누적매출
from 월별지점매출;
----------------------------------------------------------
지점 판매월 매출 누적매출
---------- ---------- ---------- ----------
10 1 692 692
10 2 925 1617
10 3 751 2368
20 1 980 980
20 2 977 1957
20 3 822 2779
20 4 653 3432
20 5 877 4309
30 1 589 589
30 2 628 1217
30 3 533 1750
30 4 515 2265
30 5 849 3114
30 6 603 3717
14 개의 행이 선택되었습니다.
경 과: 00:00:00.01
----------------------------------------------------------
- 분석함수가 지원되지 않는 버젼(Oracle 8i 이전 버젼)을 사용하는 경우에는 부등호 조인을 통해 누적매출을 구한다.
select t1.지점, t1.판매월, min(t1.매출) 매출, sum(t2.매출) 누적매출
from 월별지점매출 t1, 월별지점매출 t2
where t2.지점 = t1.지점
and t2.판매월 <= t1.판매월
group by t1.지점, t1.판매월
order by t1.지점, t1.판매월;
----------------------------------------------------------
지점 판매월 매출 누적매출
---------- ---------- ---------- ----------
10 1 692 692
10 2 925 1617
10 3 751 2368
20 1 980 980
20 2 977 1957
20 3 822 2779
20 4 653 3432
20 5 877 4309
30 1 589 589
30 2 628 1217
30 3 533 1750
30 4 515 2265
30 5 849 3114
30 6 603 3717
14 개의 행이 선택되었습니다.
경 과: 00:00:00.02
----------------------------------------------------------
(2) 선분이력 끊기
- 위 이미지는 시간을 나타내는 두 개의 선분이 서로 겹치는 모습을 표현한다.
- (a), (b), ©, (d)처럼 네가지 패턴이 있고 (e), (f)는 서로 겹치지 않는 모습을 참고로 그린 내용이다.
- 점선타원형의 표시는 겹치는 구간의 시작 및 종료 일자를 나타냈으며, 점선의 화살표는 시간흐름 별 진행 순서를 나타낸다.
- '월도'와 '선분이력' 두 테이블을 생성한다.
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;
----------------------------------------------------------
select * from 월도; --테이블 생성 확인.
기준월 시작일자 종료일자
-------------- -------------------- --------------------
2009/06 2009/06/01 2009/06/30
2009/07 2009/07/01 2009/07/31
2009/08 2009/08/01 2009/08/31
2009/09 2009/09/01 2009/09/30
2009/10 2009/10/01 2009/10/31
경 과: 00:00:00.05
----------------------------------------------------------
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;
----------------------------------------------------------
select * from 선분이력; -- 테이블 생성 확인.
상품번호 시작일자 종료일자 데이터
-------- -------------------- -------------------- --------
A 2009/07/13 2009/08/08 A1
A 2009/08/09 2009/08/20 A2
A 2009/08/21 2009/10/07 A3
경 과: 00:00:00.03
----------------------------------------------------------
--> 위에서 생성된 '월도', '선분이력' 두 테이블을 부등호 조인을 사용해 6개의 선분을 생성
select a.기준월, b.시작일자, b.종료일자, b.상품번호, b.데이터
from 월도 a, 선분이력 b
where b.시작일자 <= a.종료일자
and b.종료일자 >= a.시작일자
order by a.기준월, b.시작일자 ;
----------------------------------------------------------
기준월 시작일자 종료일자 상품번호 데이터 |시작선분 복제 스타일
----------- ----------------- ----------------- ------- ------- |
2009/07 2009/07/13 2009/08/08 A A1 |---5 -> 8 -- 스타일 a
2009/08 2009/07/13 2009/08/08 A A1 |---5 -> 9 -- 스타일 b
2009/08 2009/08/09 2009/08/20 A A2 |---6 -> 10 -- 스타일 c
2009/08 2009/08/21 2009/10/07 A A3 |---7 -> 11 -- 스타일 a
2009/09 2009/08/21 2009/10/07 A A3 |---7 -> 12 -- 스타일 d
2009/10 2009/08/21 2009/10/07 A A3 |---7 -> 13 -- 스타일 b
6 개의 행이 선택되었습니다.
경 과: 00:00.00
-------------------------------------------------
- 5번선분은 8,9 두 선분으로 복제, 7번 선분은 11,12,13 세개의 선분으로 복제 되었다.
select 상품번호
, case when lst = 시작일자1 and gst = 종료일자2 then 시작일자2 -- 스타일 a
when lst = 시작일자2 and gst = 종료일자1 then 시작일자1 -- 스타일 b
when lst = 시작일자1 and gst = 종료일자1 then 시작일자2 -- 스타일 c
when lst = 시작일자2 and gst = 종료일자2 then 시작일자1 -- 스타일 d
end 시작일자
, case when lst = 시작일자1 and gst = 종료일자2 then 종료일자1 -- 스타일 a
when lst = 시작일자2 and gst = 종료일자1 then 종료일자2 -- 스타일 b
when lst = 시작일자1 and gst = 종료일자1 then 종료일자2 -- 스타일 c
when lst = 시작일자2 and gst = 종료일자2 then 종료일자1 -- 스타일 d
end 종료일자
, 데이터
from (
select b.상품번호, b.데이터, a.기준월
, a.시작일자 시작일자1, b.시작일자 시작일자2
, a.종료일자 종료일자1, b.종료일자 종료일자2
, least(a.시작일자, a.종료일자, b.시작일자, b.종료일자) lst
, greatest(a.시작일자, a.종료일자, b.시작일자, b.종료일자) gst
from 월도 a, 선분이력 b
where b.시작일자 <= a.종료일자
and b.종료일자 >= a.시작일자
) ;
----------------------------------------------------------
상품번호 시작일자 종료일자 데이터
---------- -------------------- -------------------- ----------
A 2009/07/13 2009/07/31 A1
A 2009/08/01 2009/08/08 A1
A 2009/08/09 2009/08/20 A2
A 2009/08/21 2009/08/31 A3
A 2009/09/01 2009/09/30 A3
A 2009/10/01 2009/10/07 A3
6 개의 행이 선택되었습니다.
경 과: 00:00:00.01
----------------------------------------------------------
(3) 데이터 복제를 통한 소계 구하기
쿼리를 작성하다 보면 데이터 복제 기법을 활용해야 할 때가 많다. 데이터복제를 위해 일부러 카티션 곱(Cartesian Product)을 발생시켜 복제하기도 한다.
전통적으로 많이 쓰던 방식은 복제용 테이블(copy_t)을 미리 만들어두고 이를 활용하는 것이다. (*데카르트)
create table copy_t (no number, no2 varchar2(2));
테이블이 생성되었습니다.
경 과: 00:00:00.12
insert into copy_t
15:27:14 2 select rownum, lpad(rownum,2,'0') from all_tables where rownum<=31;
31개의 행이 생성되었습니다.
경 과: 00:00:01.36
alter table copy_t add constraint copy_t_pk primary key(no);
테이블이 변경되었습니다.
경 과: 00:00:00.32
create unique index copy_t_no2_idx on copy_t(no2);
인덱스가 생성되었습니다.
경 과: 00:00:00.00
- 하단에 쿼리를 실행하면 emp 테이블에 있는 14개의 레코드가 3개씩 총 42개로 복제된다.
select * from emp a, copy_t b where b.no<=3;
** 결과 첨부 생략
- 오라클 9i부터 dual 테이블을 이용하여, start with 절 없는 connect by 구문을 사용하면 두개의 집합이 자동으로 생성된다.
select rownum from dual connect by level <= 2;
ROWNUM
----------
1
2
경 과: 00:00:00.00
- 위 방법을 사용해서 emp 테이블을 복제 하는 방법.
select * from emp a, (select rownum no from dual connect by level <= 2) b
15:48:32 2 order by 1,2;
** 결과 첨부 생략
다음은 데이터 복제 기법을 사용하면 단일 sql로도 부서별 소계를 구할 수 있음을 보여준다.
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;
----------------------------------------------------------
부서번호 사원번호 급여합 급여평균
-------- ---------- ---------- ----------
10 7782 2450 2450
7839 5000 5000
7934 1300 1300
부서계 8750 2917
20 7369 800 800
7566 2975 2975
7788 3000 3000
7876 1100 1100
7902 3000 3000
부서계 10875 2175
30 7499 1600 1600
7521 1250 1250
7654 1250 1250
7698 2850 2850
7844 1500 1500
7900 950 950
부서계 9400 1567
17 개의 행이 선택되었습니다.
경 과: 00:00:00.00
----------------------------------------------------------
- 다음은 세 개로 복제하여 총계까지 구하는 사례이다.
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;
----------------------------------------------------------
부서번호 사원번호 급여합 급여평균
---------- ---------- ---------- ----------
10 7782 2450 2450
7839 5000 5000
7934 1300 1300
부서계 8750 2917
20 7369 800 800
7566 2975 2975
7788 3000 3000
7876 1100 1100
7902 3000 3000
부서계 10875 2175
30 7499 1600 1600
7521 1250 1250
7654 1250 1250
7698 2850 2850
7844 1500 1500
7900 950 950
부서계 9400 1567
총계 29025 2073
18 개의 행이 선택되었습니다.
경 과: 00:00:00.02
----------------------------------------------------------
- 표준 rollup문을 사용하여 간편하게 소계 및 총계를 구할 수 있다.
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;
----------------------------------------------------------
부서번호 사원번호 급여합 급여평균
---------- ---------- ---------- ----------
10 7782 2450 2450
7839 5000 5000
7934 1300 1300
부서계 8750 2917
20 7369 800 800
7566 2975 2975
7788 3000 3000
7876 1100 1100
7902 3000 3000
부서계 10875 2175
30 7499 1600 1600
7521 1250 1250
7654 1250 1250
7698 2850 2850
7844 1500 1500
7900 950 950
부서계 9400 1567
총계 29025 2073
18 개의 행이 선택되었습니다.
경 과: 00:00:00.01
----------------------------------------------------------
**Quiz
SELECT LEVEL
FROM (SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 2)
CONNECT BY LEVEL <= 3
--출력이 몇.... 개?
(4) 상호배타적 관계의 조인
- 상호배타적 관계 : 어떤 엔터티가 두 개 이상의 다른 엔터티의 합집합과 관계를 갖는 것
- ERD에 아래처럼 아크(Arc) 관계로 표시
- 실제 데이터베이스로 구현할때, 상품권결제 테이블은 아래 두가지 방법으로 구축한다.
- 1. 온라인권번호, 실권번호 두 컬럼을 따로 두고, 레코드별로 둘 중 하나의 컬럼에만 값을 입력한다. Outer 조인 이용
SELECT /*+ ordered use_nl(b) use_nl(c) use_nl(c) 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.발행번호;
- 2. 상품권구분과 상품권번호 컬럼을 두고, 상품권구분이 1일때는 온라인권번호를 입력하고 2일 때는 실퀀번호를 입력한다. Union all 이용
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 액세스까지 중복 발생
SELECT /*+ ordered use_nl(b) use_nl(c) use_nl(c) 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.발행번호;
3. 중복 액세스에 의한 비효울 제거
SELECT /*+ ordered use_nl(b) use_nl(c) use_nl(c) 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.발행번호;
(5)최종 출력 건에 대해서만 조인하기
*화면 페이지 처리시 흔히 사용되는 방식이다.
*조건과 정렬 컬럼을 결합인덱스로 구성하면 인덱스 블록만 읽어 테이블 액세스 범위와 조인 범위를 줄일 수 있다. ( 교재 참고 page. 312~ )
*조건과 정렬 컬럼이 많아져 인덱스 조정이 어려울 때는, 해당 테이블까지 액세스하여 전체를 읽어 정렬한 후 불필요한 조인 횟수를 줄일 수 있다. ( 교재 참고 page. 316~ )
*조건과 정렬 컬럼이 여러 테이블 각각 있다면? => 반정규화
**반정규화에 따른 고려사항
1. 반정규화 컬럼의 데이터 갱신을 위한 추가 프로그램 필요하다
2. 데이터 정합성 훼손의 위험성이 높아지므로, 데이터 부정합이 발생하지 않도록 조치한다.
3. 반정규화 없이 성능 문제를 해결할 수 있는 방법을 고민한다.
--
--
=> 수신확인자수와 수신대상자수를 세고 새글여부를 확인하는 스칼라 서브쿼리 성능문제로 발송메세지건수, 수신인수와 같은 추출 속성을 추가로 생성--
--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;
--=> 최종 출력되는 10건에 대해서만 수신정보와 새글 여부를 확인하는 방식으로 쿼리 변경하여 성능 문제 해결--
--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;
h3 (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. 의미상 주어에 해당하는 속성들을 식별자로 사용했다가 물리 설계 단계에서 저장 효율과 액세스 효율 등을 고려해서 결정한다.
- 장점
h3.(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 /*+ 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 | 고객별연체이력| 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)| 고객별연체이력| 5039 | 65507 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
- Index_desc 힌트와 rownum <=1 조건 사용시, 주의사항
- 인덱스 구성이 변경되면 쿼리 결과가 틀리게 될 수 있음을 반드시 기억 해야한다.
- first row(min/max) 알고리즘이 작동할 때는 반드시 min/max 함수를 사용하는 것이 올바른 선택이다.
- 낮은 성능 때문에 어쩔수 없이 Index(또는 index_desc) + rownum조건을 써야만 하는 경우가 생길 수 있고 그 때에는 프로그램 목록을 관리했다가 인덱스 구성 변경시 확인하는 프로세스를 반드시 거쳐야한다.
- 바로 다음에 설명 할 스칼라 서브쿼리도 아래와 같이 max 함수를 사용하고 싶지만, First row 알고리즘이 작동하지 않아 Index_desc 힌트와 rownum 조건을 사용한 경우다.
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테이블)로 진행되는 것에 주목하기 바란다.
- 정해진 시점 기준으로 조회
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.변경일자;
- 고객별 연체 이력 테이블을 두번 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.고객번호;
- 읽어야 할 자료가 많은 경우 - 일일이 문자열로 연결하는 작업은 번거로움.
- 이럴 땐, 아래와 같이 분석함수를 사용하는 것이 편리하다(자세한 원리는 5장 6절에서 설명)
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;
- 아래와 같이 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 |
----------------------------------------------------------------------------------------
(8) 선분이력 조인
- 임의 시점 조회
- 고객등급과 전화번호 변경이력을 관리하는 두 선분이력테이블이 있다고 하자.
- 고객과 이 두 선분이력 테이블을 조인해서 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'으로 조회될 것이다.
- 현재 시점 조회
- 위 쿼리를 이용해 과거,현재, 미래 어느 시점이든 조회할 수 있지만, 만약 미래 시점 데이터를 미리 입력하는 예약기능 이 없다면 "현재시점(현재 유효한 시점)" 조회는 아래와 같이 "=" 조건으로 만들어 주는 것이 효과적이다.
- 위 쿼리를 이용해 과거,현재, 미래 어느 시점이든 조회할 수 있지만, 만약 미래 시점 데이터를 미리 입력하는 예약기능 이 없다면 "현재시점(현재 유효한 시점)" 조회는 아래와 같이 "=" 조건으로 만들어 주는 것이 효과적이다.
select c.고객번호, c.고객명, c1.고객등급, c2.전화번호
from 고객 c, 고객등급변경이력 c1, 전화번호변경이력 c2
where c.고객번호 = 123
and c1.고객번호 = c.고객번호
and c2.고객번호 = c.고객번호
and c1.종료일자 = '99991231'
and c2.종료일자 = '99991232'
- 현재 시간이 2005년 6월 27일인데 그림처럼 미래 시점인 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.종료일자
Between조인
- 지금까지는 선분조인이 상수였다. 즉, 조회시점이 정해져 있었다.
- 그림에서 만약 우측(일별종목거래 및 시세)과 일별 거래 테이블로부터 읽히는 미지의 거래일자 시점으로 선분이력(종목이력)을조회할때 어떻게 해야 할까? 이때는 between조인을 이용하면 된다.
(이미지 삽입)
- 아래는 주식시장에서 과거 20년 동안 당일 최고가로 장을 마친(종가=최고가) 종목을 조회하는 쿼리이다.
- 위 그림의 일별종목거래및시세 테이블로부터 시가, 종가, 거래데이터를 일고 그당시 종목명과 상장주식수는 종목이력으로부터 가져오는데, 조인연산자가 '='이 아니라 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.종료일자
(9) 선분이력 조인 튜닝
(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(r) */
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(r) */
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(r) */
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(r) 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(r) 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(r) 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 |
|* 2 | 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 |
|* 4 | INDEX UNIQUE SCAN | PK_CDR_RATING | 1 | | 0 (0)| 00:00:01 |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | CONCATENATION | | | | | |
|* 7 | FILTER | | | | | |
|* 8 | INDEX UNIQUE SCAN | PK_CDR_RATING | 1 | 5 | 0 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
|* 10 | 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(r) rowid(r) */
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 |
|* 2 | 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 |
|* 4 | COUNT STOPKEY | | | | | |
| 5 | CONCATENATION | | | | | |
|* 6 | FILTER | | | | | |
|* 7 | INDEX UNIQUE SCAN | PK_CDR_RATING | 1 | 8 | 0 (0)| 00:00:01 |
|* 8 | FILTER | | | | | |
|* 9 | 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))
http://wiki.gurubee.net/pages/viewpage.action?pageId=4948223]