오라클 성능 고도화 원리와 해법 II (2012년)
(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
12 개의 행이 선택되었습니다.
- 국가코드 82(한국)는 모든 지역에 대한 요금정보를 갖고 있다.
- 하지만 국가코드 84(베트남)의 경우 A, B, C가 아닌 지역에 대해서는 일괄적으로 800원을 부과하려고 위와 같이 공백문자(' ')로 입력해둔 것이다.
- 국가코드 86(중국)도 A, B가 아닌 지역에 대해서는 일괄적으로 700원이 부과된다.
- 아래는 cdr(통화내역) 테이블에 입력된 정보를 출력한 것이다.
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
-- cdr_rating에 매칭되는 정보 없음
20050315 070101 84 D
20050315 080101 84 E
--
20050315 090101 86 A
20050315 100101 86 B
-- cdr_rating에 매칭되는 정보 없음
20050315 110101 86 C
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
8 개의 행이 선택되었습니다.
- 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
14 개의 행이 선택되었습니다.
- 어떻게 쿼리해야 조인에 실패했을 때 지역이 공백(' ')인 요금 정보를 가져올 수 있을까? 아래와 같이 쿼리하면 된다.
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
14 개의 행이 선택되었습니다.
- 9i 까지는 use_concat 힌트를 이용해 아래와 같이 쿼리할 수 있었다.
- IN-List가 concatenation(or-expansion) 방식으로 풀리면 뒤쪽에 있는 값이 먼저 실행되는 특징을 이용하는 것이다.
SQL> select /*+ ordered use_nl(r) index(r pk_cdr_rating) */
2 c.통화시간, c.국가코드, c.지역, r.요금
3 from cdr c, cdr_rating r
4 where c.통화시간 like '20050315%'
5 and (r.국가코드, r.지역) =
6 (select /*+ use_concat */ c.국가코드, 지역
7 from cdr_rating
8 where 국가코드 = c.국가코드
9 and 지역 in (' ', c.지역)
10 and rownum <= 1) ;
통화시간 국가 지역 요금
------------------------------ ---- -------------------- ----------
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에 특별한 인자를 넣어 위와 같은 방식으로 유도할 순 있지만(4장 7절 참조) CPU 비용모델에서는 통계정보상 카디널리티가 작은 값이 먼저 실행되기 때문에 결과가 보장되질 않는다.
- 따라서 10g에서 위와 같은 기법을 사용하려면 아래와 같이 ordered_predicates 힌트(4장 12절'(3) 조건절 비교 순서' 참조)를 사용하거나 no_cpu_costing 힌트를 이용해 I/O 비용 모델로 바꿔줘야 한다.
SQL> set autotrace traceonly
SQL> select /*+ ordered use_nl(r) index(r pk_cdr_rating) */
2 c.통화시간, c.국가코드, c.지역, r.요금
3 from cdr c, cdr_rating r
4 where c.통화시간 like '20050315%'
5 and (r.국가코드, r.지역) =
6 (select /*+ use_concat(@subq 1) qb_name(subq) ordered_predicates */ c.국가코드, 지역
7 from cdr_rating
8 where 국가코드 = c.국가코드
9 and 지역 in (' ', c.지역)
10 and rownum <= 1) ;
14 개의 행이 선택되었습니다.
Execution Plan
----------------------------------------------------------
Plan hash value: 1842647818
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 15 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 42 | 15 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_CDR | 14 | 266 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| CDR_RATING | 1 | 23 | 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 | 10 | 0 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
|* 10 | INDEX UNIQUE SCAN | PK_CDR_RATING | 1 | 10 | 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))
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
907 bytes sent via SQL*Net to client
392 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
- 아래는 인덱스를 두 번 액세스하지 않고 서브쿼리에서 얻은 rowid로 테이블을 직접 액세스하도록 최종적으로 튜닝한 것이다.
SQL> select /*+ ordered use_nl(r) rowid(r) */
2 c.통화시간, c.국가코드, c.지역, r.요금
3 from cdr c, cdr_rating r
4 where c.통화시간 like '20050315%'
5 and r.rowid =
6 (select /*+ use_concat(@subq 1) qb_name(subq) ordered_predicates */ rowid
7 from cdr_rating
8 where 국가코드 = c.국가코드
9 and 지역 in (' ', c.지역)
10 and rownum <= 1
11 ) ;
14 개의 행이 선택되었습니다.
경 과: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1791744221
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 88 | 15 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 168 | 7392 | 15 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_CDR | 14 | 266 | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY USER ROWID| CDR_RATING | 12 | 300 | 1 (0)| 00:00:01 |
|* 4 | COUNT STOPKEY | | | | | |
| 5 | CONCATENATION | | | | | |
|* 6 | FILTER | | | | | |
|* 7 | INDEX UNIQUE SCAN | PK_CDR_RATING | 1 | 22 | 0 (0)| 00:00:01 |
|* 8 | FILTER | | | | | |
|* 9 | INDEX UNIQUE SCAN | PK_CDR_RATING | 1 | 22 | 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))
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
48 recursive calls
0 db block gets
92 consistent gets
0 physical reads
0 redo size
907 bytes sent via SQL*Net to client
392 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
"구루비 데이터베이스 스터디모임" 에서 2012년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4440
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.