61번.
아래와 같은 고객연락처변경이력 테이블이 있다. 고객은 10만 명이고, 연락처구분으로 '휴대전화','집전화','회사전화','팩스','이메일','기타' 여섯 개 값이 존재하는 상황에서 아래 SQL을 위한 최적 인덱스를 설계하시오. (PK 인덱스 컬럼 순서는 ERD 식별자 순서와 다를 수 있으니, ERD 식별자는 무시하고 인덱스를 구성할 것)
| 고객연락처변경이력 # 고객ID NUMBER # 연락처구분 VARCHAR2(10) # 시작일자 VARCHAR2(8) * 종료일자 VARCHAR2(8) * 연락처번호 VARCHAR2(12) * . . . o . . . o . . . |
SELECT *
FROM 고객연락처변경이력
WHERE 고객ID = 200
AND 연락처구분 IN ('휴대전화','집전화','회사전화')
AND :BASE_DT BETWEEN 시작일자 AND 종료일자
/* :BASE_DT 변수에는 주로 최근 일자를 입력함 */
풀이
# 두 가지 쟁점
1. 선분이력 테이블에 대한 인덱스를 설계할 때 시작일자와 종료일자 중 어느 컬럼을 앞쪽으로 둘 것인가?
2. IN 조건인 연락처구분을 범위검색 조건(BETWEEN, 부등호) 앞쪽에 둘 것인가, 뒤쪽에 둘것인가?
*1번 쟁점
-. 고객연락처변경이력은 선분이력테이블이다. 선분이력에서 특정시점(base_dt)을 조회할 때는 아래와 같은 조건절 사용
| WHERE :BASE_DT BETWEEN 시작일자 AND 종료일자 위 조건절을 분해하면 WHERE 시작일자 <= :BASE_DT AND 종료일자 >= :BASE_DT |
-. 최근 시점 이력을 조회할 떄는 종료일자 조건을 이용하는 것이 유리하다. 종료 일자 >= :BASE_DT 조건을 만족하는 데이터가 소량이기 때문이다.
-. 반대로, 오래된 과거 이력을 조회할 때는 시작일자가 유리
-. 이력테이블에서는 과거와 현재, 어느시점 다 조회가능하지만 주로 최근(현재 시점, 전월 말일 시점 등) 데이터를 조회하므로 일반적으로 종료일자 조건을 이용하는 것이 유리하다.
-. 문제에서 BAST_DT 변수는 주로 최근 일자를 입력한다고 했기 때문에 종료일자 + 시작일자(과거데이터도 조회 할 때를 고려해야 하기 때문)
종료일자 + 시작일자 INDEX에서 시작일자가 없다면?
-. 종료일자 INDEX를 다 읽고나서 시작일자를 필터링 해야한다. 그 과정에서 랜덤엑세스 많이 발생-. 지금 문제와 같은 상황에서 고객별 연락처를 변경하는 경우가 흔치 않아 고객ID 조건을 만족하는 이력 데이터가 매우 적다면, 오랜 과거 시점 이력을 조회하더라도 랜덤엑세스 발생량이 많지 않을 것이므로 의도적으로 시작일자를 제외 할 순 있다. 그런 가정을 하면 종료일자만 둘 수 있다.
* TEST Script (2번 쟁점을 설명하기 위해 고객의 연락처구분별로 평균 5번씩 변경이력 발생 한다고 가정)
select index_name, blevel
from user_indexes
where table_name = '고객연락처변경이력';
INDEX_NAME BLEVEL
고객연락처변경이력_X01 2
고객연락처변경이력_X02 2
고객연락처변경이력_X03 2* 브랜치가 2 Level, 리프 블록을 포함해 인덱스는 총 3 Level이다
-. 인덱스 스캔 단계에서 블록I/O 발생량 측정
# 인덱스(고객ID, 종료일자)
set serveroutput off;
var base_dt varchar2(8);
execute :base_dt := to_char(sysdate,'yyyydmmdd');
select /*+ gather_plan_statistics index(c (고객ID, 종료일자))*/ count(*)
from 고객연락처변경이력 C
where 고객ID = 200
and 연락처구분 in ('휴대전화','집전화','회사전화')
and :base_dt between 시작일자 and 종료일자;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN| 고객연락처변경| 1 | 1 | 3 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("고객ID"=200 AND "종료일자">=:BASE_DT AND "종료일자" IS NOT NULL)
filter((INTERNAL_FUNCTION("연락처구분") AND "시작일자"<=:BASE_DT))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
# 인덱스(고객ID, 연락처구분)
select /*+ gather_plan_statistics index(c (고객ID, 연락처구분))*/ count(*)
from 고객연락처변경이력 C
where 고객ID = 200
and 연락처구분 in ('휴대전화','집전화','회사전화')
and :base_dt between 시작일자 and 종료일자;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 8 |
| 2 | INLIST ITERATOR | | 1 | | 3 |00:00:00.01 | 8 |
|* 3 | INDEX RANGE SCAN| 고객연락처변경| 3 | 1 | 3 |00:00:00.01 | 8 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("고객ID"=200 AND (("연락처구분"='집전화' OR "연락처구분"='회사전화' OR "연락처구분"='휴대전화'))
AND "종료일자">=:BASE_DT AND "종료일자" IS NOT NULL)
filter("시작일자"<=:BASE_DT)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
| -. (고객ID + 연락처구분) 순으로 구성한 인덱스는 IN-LIST Iterator방식으로 처리하여 인덱스 수직적 탐색 3번함 -. (고객ID + 연락처구분) 5개 더 많은 블록 I/O 발생 └ 인덱스 리프 블록 하나에 300개 레코드가 저장된다고 가정하면, 인덱스 리프 블록에 1500(=300rows * 5block) 레코드를 스캔한 분량 |
※ (고객ID=, 종료일자 >=) 조건을 만족하는 데이터가 1500개를 넘지 않는다면, 연락처구분을 필터로 처리하더라도 고객연락처변경이력_X01 인덱스를 스캔하는 쪽이 유리하다.
연락처 구분 ('휴대전화','집전화','회사전화','팩스','이메일','기타') 여기서 INLIST로 ('휴대전화','집전화','회사전화') 3번 수직탐색하면서 스캔한 블록갯수가 더 적다면 고객연락처변경이력_X02(고객ID, 연락처구분) 인덱스를 쓰는게 더 좋을 수 있다는 말인데, 이렇게 될려면, 각 고객이 여섯 개 연락처 구분마다 각 번호를 250이상 변경해야한다. 상식적으로 불가능에 가까운 수치다.
결론적으로 (고객ID + 종료일자(+시작일자) + 연락처구분)으로 구성하는것이 옳다.
* TEST Script
create table 고객연락처변경이력
nologging
as
select 고객ID, 연락처구분
, 시작일자, 종료일자
, (case when 연락처구분 = '휴대전화' then '010'
else lpad(round(dbms_random.value(1,999)),3,'0')end)
|| lpad(round(dbms_random.value(1,9999)),4,'0')
|| lpad(round(dbms_random.value(1,9999)),4,'0') 연락처번호
from (select rownum as 고객ID from xmltable('1 to 100000'))
, (select (case rownum
when 1 then '휴대전화'
when 2 then '집전화'
when 3 then '회사전화'
when 4 then '팩스'
when 5 then '이메일'
when 6 then '기타' end
) 연락처구분
from xmltable('1 to 6') )
, (select to_char(add_months(sysdate, -rownum*12), 'yyyymmdd') 시작일자 ,
(case when rownum = 1 then '99991231'
else to_char(add_months(sysdate, -(rownum*12)+12)-1, 'yyyymmdd')
end) 종료일자
from xmltable('1 to 5') -- 연락처구분별로 5번씩 이력변경 발생
)
order by dbms_random.value;
create index 고객연락처변경이력_x01 on 고객연락처변경이력(고객ID, 종료일자, 시작일자, 연락처구분) nologging;
create index 고객연락처변경이력_x02 on 고객연락처변경이력(고객ID, ,연락처구분, 종료일자, 시작일자) nologging;
create index 고객연락처변경이력_x03 on 고객연락처변경이력(연락처구분, 고객ID, 종료일자, 시작일자) nologging;※참고
-. allstats format
ALLSTATS Format
ALLSTATS Format 이 Format은 Starts, A-Rows, Buffer의 출력값을 누적하여 보여준다. 구분 항목 설명 PLAN 정보 ID ..
www.gurubee.net
'Database > SQLP' 카테고리의 다른 글
| [SQLP] 핵심노트2) 6.고급SQL튜닝1_12번 (0) | 2023.10.18 |
|---|---|
| [친절한SQL 6장] 기본 DML 튜닝 (0) | 2023.02.14 |
| [친절한 SQL 5장] 소트 튜닝 (0) | 2023.01.19 |
| [친절한 SQL 4장] 조인튜닝 (0) | 2023.01.08 |
| [친절한 SQL 3장] 인덱스 튜닝 (2) | 2022.12.26 |