-변경이력에서 여러 건을 조회 할 때의 해결방법
-변경이력 조회시 rownum의 활용
들어가기 전에
이 글을 읽기 위해서 지난번 글(변경이력 테이블에 종료일자가 필요한가?)을 먼저 읽고 오기 바란다. 그렇지 않으면 이해하기 힘들며 이 글과 어떻게 연결되는지 알 수 없다.
이전 글이 다분히 이론적이었다면 이번에는 실전적인 문제를 다룬다. 이 글에서 언급된 기법을 알지 못하면 실무에서 종료일자가 없이 시작일자만을 사용했을때 성능이 떨어질 것이다.
이 글은 종료일자의 장단점에 대해 충분한 고민후에 사용했던 사람들에 대한 공격이 아님을 일러둔다. 역정규화에 대한 위험성을 알리고, 정규형 또한 빠른 성능을 가졌다는 것을 증명함으로써 종료일자를 사용할 때는 많은 고민이 있어야 한다는 것을 주장하는 것이다.
글의시작
지난 글에서 뜨거운 토론이 있었다. 무려 댓글이 28개나 달렸으며 의견도 크게 두 가지로 갈렸다. 즉 데이터 정합성을 위해 종료일자를 자제해야 한다는 측과 성능관점에서는 장점이 워낙 크므로 종료일자를 사용해야 한다는 측의 두 가지이다. 여러 반론들도 쏟아졌다. 현실 세계의 다양하고 복잡한 SQL이 있으므로 지난 시간에 언급했던 세가지 유형만으로는 충분하지 않다는 것. 일리가 있다. 그래서 이번 시간에는 현실에서 많이 나오는 패턴에 대하여 알아보기로 하자. 즉 MAX 값 한 건이나 특정시점의 한 건을 구하는 것이 아니라 여러 건이 조회되는 패턴에 대해 알아보겠다. 지난 글에서 여러 사람들이 이야기 하는 현실적인 예제란 아래와 같았다.
SELECT a.svc_no, a.cust_no, a.acct_no, a.svc_date, a.txt,
b.start_dt, b.txt, c.start_dt, c.txt, d.start_dt, d.txt
FROM svc a, svc_hist b, acct_hist c, cust_hist d
WHERE a.svc_date BETWEEN SYSDATE - 201 AND SYSDATE - 1
AND b.svc_no = a.svc_no
AND d.cust_no = a.cust_no
AND c.acct_no = a.acct_no
AND a.svc_date BETWEEN b.start_dt AND b.end_dt -- 서비스 변경이력
AND a.svc_date BETWEEN c.start_dt AND c.end_dt -- 계정 변경이력
AND a.svc_date BETWEEN d.start_dt AND d.end_dt -- 고객 변경이력
AND b.txt > ' '
AND c.txt > ' '
AND d.txt > ' ' ;
시작일자만으로 이 SQL을 빠르게 조회할 수 있니?
조인조건을 보면 특정시점이 아닌 a.svc_date로 변경이력과 between 조인을 하고 있다. 이런 스타일의 SQL에서는 시작일자 컬럼만으로는 between을 사용할 수 없으므로 느리다는 것. 그리고 SQL의 결과는 한 건이 아니라 여러 건이기 때문에 rownum = 1 조건을 사용할 수 없으므로 역정규화를 하여 종료일자를 추가하는 것이 성능상 유리하다는 의견이 있었다. 이것이 사실일까?
환경 Oracle11g R2
테스트를 위하여 object를 생성하고 통계정보를 수집한다.
CREATE TABLE CUST_HIST AS --고객이력
SELECT A.CUST_NO, B.*
FROM ( SELECT LEVEL AS CUST_NO
FROM DUAL
CONNECT BY LEVEL <= 50) A,
( SELECT SYSDATE - level AS start_dt, --시작일시
SYSDATE - level + 1 - 1/24/60/60 AS end_dt, --종료일시
'종료일자의 필요성 테스트' as txt
FROM DUAL
CONNECT BY LEVEL <= 2000) B;
ALTER TABLE CUST_HIST ADD CONSTRAINT PK_CUST_HIST PRIMARY KEY (CUST_NO, START_DT) USING INDEX;
CREATE INDEX IX_CUST_HIST_01 ON TLO.CUST_HIST (CUST_NO, END_DT, START_DT) ;
CREATE TABLE ACCT_HIST AS -- 계정이력
SELECT A.*, B.*
FROM ( SELECT LEVEL AS ACCT_NO
FROM DUAL
CONNECT BY LEVEL <= 50) A,
( SELECT SYSDATE - level AS start_dt, --시작일시
SYSDATE - level + 1 - 1/24/60/60 AS end_dt, --종료일시
'종료일자의 필요성 테스트' as txt
FROM DUAL
CONNECT BY LEVEL <= 2000) B;
ALTER TABLE ACCT_HIST ADD CONSTRAINT PK_ACCT_HIST PRIMARY KEY (ACCT_NO, START_DT) USING INDEX;
CREATE INDEX IX_ACCT_HIST_01 ON ACCT_HIST (ACCT_NO, END_DT, START_DT) ;
CREATE TABLE SVC AS --서비스
SELECT *
FROM ( SELECT LEVEL AS svc_no,
TRUNC(dbms_random.value(1,51)) AS CUST_NO,
TRUNC(dbms_random.value(1,51)) AS ACCT_NO,
SYSDATE - level + 1 - 0.5 AS SVC_DATE, --서비스 가입일시
'종료일자의 필요성 테스트' as txt
FROM DUAL CONNECT BY LEVEL <= 2000) A ;
ALTER TABLE SVC ADD CONSTRAINT PK_SVC PRIMARY KEY (SVC_NO) USING INDEX;
CREATE INDEX IX_SVC_01 ON SVC (SVC_DATE) ;
CREATE TABLE SVC_HIST AS --서비스이력
SELECT A.svc_no, B.*
FROM ( SELECT svc_no
FROM SVC) A,
( SELECT SYSDATE - level AS start_dt, --시작일시
SYSDATE - level + 1 - 1/24/60/60 AS end_dt, --종료일시
'종료일자의 필요성 테스트' as txt
FROM DUAL
CONNECT BY LEVEL <= 2000) B;
ALTER TABLE SVC_HIST ADD CONSTRAINT PK_SVC_HIST PRIMARY KEY (SVC_NO, START_DT) USING INDEX NOLOGGING;
CREATE INDEX IX_SVC_HIST_01 ON SVC_HIST (SVC_NO, END_DT, START_DT) NOLOGGING;
begin
dbms_stats.gather_table_stats(user, 'SVC_HIST', cascade => true);
dbms_stats.gather_table_stats(user, 'CUST_HIST', cascade => true);
dbms_stats.gather_table_stats(user, 'ACCT_HIST', cascade => true);
dbms_stats.gather_table_stats(user, 'SVC', cascade => true);
end;
이제 테스트를 수행해보자. 종료일자 + 시작일자 인덱스의 장점은 최근 데이터를 구할 때 효율적이다. 따라서 종료일자 + 시작일자 인덱스를 이용하여 비교적 최근 데이터 200 건을 조회해보자. 종료일자 + 시작일자 인덱스를 강제로 사용하기 위해 힌트를 사용하였다.
SELECT /*+ USE_NL(A B C D) INDEX(A IX_SVC_01) INDEX(B IX_SVC_HIST_01)
INDEX(C IX_ACCT_HIST_01) INDEX(D IX_CUST_HIST_01) */
a.svc_no, a.cust_no, a.acct_no, a.svc_date, a.txt,
b.start_dt, b.txt, c.start_dt, c.txt, d.start_dt, d.txt
FROM svc a, svc_hist b, acct_hist c, cust_hist d
WHERE a.svc_date BETWEEN SYSDATE - 201 AND SYSDATE - 1
AND b.svc_no = a.svc_no
AND d.cust_no = a.cust_no
AND c.acct_no = a.acct_no
AND a.svc_date BETWEEN b.start_dt AND b.end_dt
AND a.svc_date BETWEEN c.start_dt AND c.end_dt
AND a.svc_date BETWEEN d.start_dt AND d.end_dt
AND b.txt > ' '
AND c.txt > ' '
AND d.txt > ' ' ;
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 200 |00:00:00.04 | 1937 |
|* 1 | FILTER | | 1 | | 200 |00:00:00.04 | 1937 |
| 2 | NESTED LOOPS | | 1 | | 200 |00:00:00.04 | 1937 |
| 3 | NESTED LOOPS | | 1 | 180M| 200 |00:00:00.09 | 1738 |
| 4 | NESTED LOOPS | | 1 | 1868K| 200 |00:00:00.04 | 1352 |
| 5 | NESTED LOOPS | | 1 | 19399 | 200 |00:00:00.02 | 783 |
| 6 | TABLE ACCESS BY INDEX ROWID| SVC | 1 | 201 | 200 |00:00:00.01 | 7 |
|* 7 | INDEX RANGE SCAN | IX_SVC_01 | 1 | 201 | 200 |00:00:00.01 | 4 |
|* 8 | TABLE ACCESS BY INDEX ROWID| SVC_HIST | 200 | 96 | 200 |00:00:00.02 | 776 |
|* 9 | INDEX RANGE SCAN | IX_SVC_HIST_01 | 200 | 96 | 200 |00:00:00.01 | 576 |
|* 10 | TABLE ACCESS BY INDEX ROWID | ACCT_HIST | 200 | 96 | 200 |00:00:00.02 | 569 |
|* 11 | INDEX RANGE SCAN | IX_ACCT_HIST_01 | 200 | 96 | 200 |00:00:00.01 | 371 |
|* 12 | INDEX RANGE SCAN | IX_CUST_HIST_01 | 200 | 96 | 200 |00:00:00.01 | 386 |
|* 13 | TABLE ACCESS BY INDEX ROWID | CUST_HIST | 200 | 96 | 200 |00:00:00.01 | 199 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SYSDATE@!-201<=SYSDATE@!-1)
7 - access("A"."SVC_DATE">=SYSDATE@!-201 AND "A"."SVC_DATE"<=SYSDATE@!-1)
8 - filter("B"."TXT">' ')
9 - access("B"."SVC_NO"="A"."SVC_NO" AND "B"."END_DT">=SYSDATE@!-201 AND "B"."END_DT" IS NOT NULL)
filter(("B"."START_DT"<=SYSDATE@!-1 AND "A"."SVC_DATE">="B"."START_DT" AND
"A"."SVC_DATE"<="B"."END_DT"))
10 - filter("C"."TXT">' ')
11 - access("C"."ACCT_NO"="A"."ACCT_NO" AND "C"."END_DT">=SYSDATE@!-201 AND "C"."END_DT" IS NOT
NULL)
filter(("C"."START_DT"<=SYSDATE@!-1 AND "A"."SVC_DATE">="C"."START_DT" AND
"A"."SVC_DATE"<="C"."END_DT"))
12 - access("D"."CUST_NO"="A"."CUST_NO" AND "D"."END_DT">=SYSDATE@!-201 AND "D"."END_DT" IS NOT
NULL)
filter(("D"."START_DT"<=SYSDATE@!-1 AND "A"."SVC_DATE">="D"."START_DT" AND
"A"."SVC_DATE"<="D"."END_DT"))
13 - filter("D"."TXT">' ')
1937 블럭을 Scan하였다. 수행시간도 1초미만으로 최적이다. 그럼 이제 최근 데이터가 아닌 오래된 데이터를 구해보자. 종료일자 + 시작일자 인덱스의 단점은 오래된 데이터를 구할 때 드러난다.
SELECT /*+ USE_NL(A B C D) INDEX(A IX_SVC_01) INDEX(B IX_SVC_HIST_01)
INDEX(C IX_ACCT_HIST_01) INDEX(D IX_CUST_HIST_01) */
a.svc_no, a.cust_no, a.acct_no, a.svc_date, a.txt,
b.start_dt, b.txt, c.start_dt, c.txt, d.start_dt, d.txt
FROM svc a, svc_hist b, acct_hist c, cust_hist d
WHERE a.svc_date BETWEEN SYSDATE - 1999 AND SYSDATE - 1799 -- 오래된 데이터 조회
AND b.svc_no = a.svc_no
AND d.cust_no = a.cust_no
AND c.acct_no = a.acct_no
AND a.svc_date BETWEEN b.start_dt AND b.end_dt
AND a.svc_date BETWEEN c.start_dt AND c.end_dt
AND a.svc_date BETWEEN d.start_dt AND d.end_dt
AND b.txt > ' '
AND c.txt > ' '
AND d.txt > ' ' ;
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 200 |00:00:02.15 | 6498 |
|* 1 | FILTER | | 1 | | 200 |00:00:02.15 | 6498 |
| 2 | NESTED LOOPS | | 1 | | 200 |00:00:02.15 | 6498 |
| 3 | NESTED LOOPS | | 1 | 182M| 200 |00:00:01.31 | 6301 |
| 4 | NESTED LOOPS | | 1 | 1889K| 200 |00:00:01.21 | 4425 |
| 5 | NESTED LOOPS | | 1 | 19528 | 200 |00:00:01.11 | 2344 |
| 6 | TABLE ACCESS BY INDEX ROWID| SVC | 1 | 202 | 200 |00:00:00.01 | 7 |
|* 7 | INDEX RANGE SCAN | IX_SVC_01 | 1 | 202 | 200 |00:00:00.01 | 3 |
|* 8 | TABLE ACCESS BY INDEX ROWID| SVC_HIST | 200 | 97 | 200 |00:00:00.77 | 2337 |
|* 9 | INDEX RANGE SCAN | IX_SVC_HIST_01 | 200 | 97 | 200 |00:00:00.65 | 2137 |
|* 10 | TABLE ACCESS BY INDEX ROWID | CUST_HIST | 200 | 97 | 200 |00:00:00.09 | 2081 |
|* 11 | INDEX RANGE SCAN | IX_CUST_HIST_01 | 200 | 97 | 200 |00:00:00.09 | 1887 |
|* 12 | INDEX RANGE SCAN | IX_ACCT_HIST_01 | 200 | 97 | 200 |00:00:00.09 | 1876 |
|* 13 | TABLE ACCESS BY INDEX ROWID | ACCT_HIST | 200 | 97 | 200 |00:00:00.01 | 197 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(SYSDATE@!-1999<=SYSDATE@!-1799)
7 - access("A"."SVC_DATE">=SYSDATE@!-1999 AND "A"."SVC_DATE"<=SYSDATE@!-1799)
8 - filter("B"."TXT">' ')
9 - access("B"."SVC_NO"="A"."SVC_NO" AND "B"."END_DT">=SYSDATE@!-1999 AND "B"."END_DT" IS NOT NULL)
filter(("B"."START_DT"<=SYSDATE@!-1799 AND "A"."SVC_DATE">="B"."START_DT" AND
"A"."SVC_DATE"<="B"."END_DT"))
10 - filter("D"."TXT">' ')
11 - access("D"."CUST_NO"="A"."CUST_NO" AND "D"."END_DT">=SYSDATE@!-1999 AND "D"."END_DT" IS NOT NULL)
filter(("D"."START_DT"<=SYSDATE@!-1799 AND "A"."SVC_DATE">="D"."START_DT" AND
"A"."SVC_DATE"<="D"."END_DT"))
12 - access("C"."ACCT_NO"="A"."ACCT_NO" AND "C"."END_DT">=SYSDATE@!-1999 AND "C"."END_DT" IS NOT NULL)
filter(("C"."START_DT"<=SYSDATE@!-1799 AND "A"."SVC_DATE">="C"."START_DT" AND
"A"."SVC_DATE"<="C"."END_DT"))
13 - filter("C"."TXT">' ')
무려 6498 블럭을 Scan 하였다. 즉 3배 이상 느려져서 수행시간도 2초가 넘어버렸다. 물론 오래된 데이터를 자주 사용하지 않는다면 피해가 줄어들 것이다. 하지만 가끔이라도 오래된 데이터를 조회한다면 Timeout이 발생할 수 있다. 즉 성능을 위해 종료일자 컬럼을 추가하였지만 시점이 언제인가에 따라 성능이 더 느려질 수 있다는 것이다. 이 예제에서는 오래된 데이터를 2005년도로 가정하고 조회하였다. 현실에서는 2005년이면 그리 오래된 것이 아닐 수도 있다. 만약 테스트 데이터를 더 많이 만들어 2000년도 이전 데이터를 조회한다면 더 느려질 것이다.
그럼 이 문제를 어떻게 해결할 거니?
먼저 type을 하나 만들자. 이제부터는 종료일자 컬럼은 없다고 가정한다. 그리고 인덱스도 시작일자 인덱스만 사용한다.
CREATE OR REPLACE TYPE hist_type AS OBJECT
(b_st_dt date,
b_txt char(24),
c_st_dt date,
c_txt char(24),
d_st_dt date,
d_txt char(24) );
/
SELECT a.svc_no, a.cust_no, a.acct_no, a.svc_date, a.txt,
a.h.b_st_dt, a.h.b_txt, a.h.c_st_dt, a.h.c_txt, a.h.d_st_dt, a.h.d_txt
FROM (SELECT a.*,
(SELECT hist_type(b.start_dt, b.txt, c.start_dt, c.txt, d.start_dt, d.txt)
FROM ( SELECT * FROM svc_hist b ORDER BY start_dt DESC) b, --변경이력1
( SELECT * FROM cust_hist c ORDER BY start_dt DESC) c, --변경이력2
( SELECT * FROM acct_hist d ORDER BY start_dt DESC) d --변경이력3
WHERE b.svc_no = a.svc_no
AND b.start_dt <= a.svc_date
AND b.txt > ' '
AND c.cust_no = a.cust_no
AND c.start_dt <= a.svc_date
AND c.txt > ' '
AND d.acct_no = a.acct_no
AND d.start_dt <= a.svc_date
AND d.txt > ' '
AND ROWNUM = 1) AS h -- ROWNUM 사용
FROM svc a
WHERE a.svc_date BETWEEN SYSDATE - 201 AND SYSDATE - 1) a ;
위의 SQL이 복잡해 보이지만 스칼라 서브쿼리에 집중해 주기 바란다. 스칼라 서브쿼리 내부의 인라인뷰는 변경이력 테이블들이다. 여러 개의 서로 다른 변경이력 테이블을 조회한다고 해도 from 절에 인라인뷰를 계속 추가하면 된다. 위의 예제에서는 변경이력을 3개만 사용하였다. 그리고 여러 건을 조회할 때에도 rownum을 사용할 수 있음을 알 수 있다. 여기서 인라인뷰에 order by를 사용한 것은 Index_desc 힌트와 rownum = 1 조합은 안전한가? 에서 언급된 order by가 적용된 인라인뷰와 rownum의 원리를 이용한 것이다. 그리고 FPD(Filter Push Down : 조건이 뷰 내부로 파고듦)을 활용한 것이다.
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 200 |00:00:00.01 | 7 |
|* 1 | COUNT STOPKEY | | 200 | | 200 |00:00:00.02 | 2006 |
| 2 | NESTED LOOPS | | 200 | 1 | 200 |00:00:00.01 | 2006 |
| 3 | NESTED LOOPS | | 200 | 1 | 200 |00:00:00.01 | 1202 |
| 4 | VIEW | | 200 | 1 | 200 |00:00:00.01 | 600 |
|* 5 | TABLE ACCESS BY INDEX ROWID | CUST_HIST | 200 | 100 | 200 |00:00:00.01 | 600 |
|* 6 | INDEX RANGE SCAN DESCENDING| PK_CUST_HIST | 200 | 18 | 200 |00:00:00.01 | 400 |
| 7 | VIEW | | 200 | 1 | 200 |00:00:00.01 | 602 |
|* 8 | TABLE ACCESS BY INDEX ROWID | ACCT_HIST | 200 | 100 | 200 |00:00:00.01 | 602 |
|* 9 | INDEX RANGE SCAN DESCENDING| PK_ACCT_HIST | 200 | 18 | 200 |00:00:00.01 | 402 |
| 10 | VIEW | | 200 | 1 | 200 |00:00:00.01 | 804 |
|* 11 | TABLE ACCESS BY INDEX ROWID | SVC_HIST | 200 | 100 | 200 |00:00:00.01 | 804 |
|* 12 | INDEX RANGE SCAN DESCENDING | PK_SVC_HIST | 200 | 18 | 200 |00:00:00.01 | 604 |
|* 13 | FILTER | | 1 | | 200 |00:00:00.01 | 7 |
| 14 | TABLE ACCESS BY INDEX ROWID | SVC | 1 | 201 | 200 |00:00:00.01 | 7 |
|* 15 | INDEX RANGE SCAN | IX_SVC_01 | 1 | 201 | 200 |00:00:00.05 | 4 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
5 - filter("C"."TXT">' ')
6 - access("C"."CUST_NO"=:B1 AND "C"."START_DT"<=:B2)
8 - filter("D"."TXT">' ')
9 - access("D"."ACCT_NO"=:B1 AND "D"."START_DT"<=:B2)
11 - filter("B"."TXT">' ')
12 - access("B"."SVC_NO"=:B1 AND "B"."START_DT"<=:B2)
13 - filter(SYSDATE@!-201<=SYSDATE@!-1)
15 - access("A"."SVC_DATE">=SYSDATE@!-201 AND "A"."SVC_DATE"<=SYSDATE@!-1)
성능 또한 최적이다. 물론 종료일자 + 시작일자 인덱스에 비해 조금 많은 블럭을 Scan 하였지만 무시할 수 있는 정도이다. 이 정도면 종료일자 + 시작일자 인덱스 보다 우월하다. 그 이유는 오래된 데이터를 조회 할 때에도 성능이 동일 하다는 것이다. 아래의 SQL을 보자.
SELECT a.svc_no, a.cust_no, a.acct_no, a.svc_date, a.txt,
a.h.b_st_dt, a.h.b_txt, a.h.c_st_dt, a.h.c_txt, a.h.d_st_dt, a.h.d_txt
FROM (SELECT a.*,
(SELECT hist_type(b.start_dt, b.txt, c.start_dt, c.txt, d.start_dt, d.txt)
FROM ( SELECT * FROM svc_hist b ORDER BY start_dt DESC) b,
( SELECT * FROM cust_hist c ORDER BY start_dt DESC) c,
( SELECT * FROM acct_hist d ORDER BY start_dt DESC) d
WHERE b.svc_no = a.svc_no
AND b.start_dt <= a.svc_date
AND b.txt > ' '
AND c.cust_no = a.cust_no
AND c.start_dt <= a.svc_date
AND c.txt > ' '
AND d.acct_no = a.acct_no
AND d.start_dt <= a.svc_date
AND d.txt > ' '
AND ROWNUM = 1) AS h
FROM svc a
WHERE a.svc_date BETWEEN SYSDATE - 1999 AND SYSDATE - 1799) a ;
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 200 |00:00:00.01 | 7 |
|* 1 | COUNT STOPKEY | | 200 | | 200 |00:00:00.02 | 2004 |
| 2 | NESTED LOOPS | | 200 | 1 | 200 |00:00:00.01 | 2004 |
| 3 | NESTED LOOPS | | 200 | 1 | 200 |00:00:00.01 | 1202 |
| 4 | VIEW | | 200 | 1 | 200 |00:00:00.01 | 602 |
|* 5 | TABLE ACCESS BY INDEX ROWID | CUST_HIST | 200 | 100 | 200 |00:00:00.01 | 602 |
|* 6 | INDEX RANGE SCAN DESCENDING| PK_CUST_HIST | 200 | 18 | 200 |00:00:00.01 | 402 |
| 7 | VIEW | | 200 | 1 | 200 |00:00:00.01 | 600 |
|* 8 | TABLE ACCESS BY INDEX ROWID | ACCT_HIST | 200 | 100 | 200 |00:00:00.01 | 600 |
|* 9 | INDEX RANGE SCAN DESCENDING| PK_ACCT_HIST | 200 | 18 | 200 |00:00:00.01 | 400 |
| 10 | VIEW | | 200 | 1 | 200 |00:00:00.01 | 802 |
|* 11 | TABLE ACCESS BY INDEX ROWID | SVC_HIST | 200 | 100 | 200 |00:00:00.01 | 802 |
|* 12 | INDEX RANGE SCAN DESCENDING | PK_SVC_HIST | 200 | 18 | 200 |00:00:00.01 | 602 |
|* 13 | FILTER | | 1 | | 200 |00:00:00.01 | 7 |
| 14 | TABLE ACCESS BY INDEX ROWID | SVC | 1 | 202 | 200 |00:00:00.01 | 7 |
|* 15 | INDEX RANGE SCAN | IX_SVC_01 | 1 | 202 | 200 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
5 - filter("C"."TXT">' ')
6 - access("C"."CUST_NO"=:B1 AND "C"."START_DT"<=:B2)
8 - filter("D"."TXT">' ')
9 - access("D"."ACCT_NO"=:B1 AND "D"."START_DT"<=:B2)
11 - filter("B"."TXT">' ')
12 - access("B"."SVC_NO"=:B1 AND "B"."START_DT"<=:B2)
13 - filter(SYSDATE@!-1999<=SYSDATE@!-1799)
15 - access("A"."SVC_DATE">=SYSDATE@!-1999 AND "A"."SVC_DATE"<=SYSDATE@!-1799)
예전 데이터를 조회할 때에도 성능이 동일함을 알 수 있다. 이것은 매우 중요한 관점이다. 같은 SQL을 실행함에도 매우 느릴때가 있고, 매우 빠를 때가 있다면 그 SQL의 성능은 불안정하다고 할 수 있다. 이로써 이전 글에서 이슈가 되었던 여러 건을 조회할 때에도 종료일자는 필요 없음을 알 수 있다. 물론 SQL이 몇 줄 길어지긴 했지만 그 이유 때문에 역정규화의 단점인 데이터의 정합성을 해칠 수는 없는 일이다. SQL이 몇 줄 늘어나더라도 이렇게 사용해야 하는 이유는 또 있다.
역정규화할때 필요한 정합성을 체크하는 프로그램의 길이를 생각하면 몇 줄의 손해는 아무것도 아니다. 여기에 더하여 정합성을 보정하는 프로그램도 필요하므로 위의 몇 줄이 추가된 SQL이 손해라고 생각할 수는 없다. 또한 위의 SQL이 어려운 전문가용 SQL이라고 생각 하지는 않는다.
우리는 Type을 쓸 수 없다
만약 type을 이용할 수 없는 환경이라면 아래의 SQL을 이용해야 한다. 비슷한 유형이지만 이 경우는 변경이력 테이블을 두 번 Scan하는 비효율을 감안해야 한다. 물론 이때에도 rownum = 1은 사용할 수 있다. 먼저 최근의 데이터를 조회한다.
SELECT a.svc_no, a.cust_no, a.acct_no, a.svc_date, a.txt,
b.start_dt, b.txt, c.start_dt, c.txt, d.start_dt, d.txt
FROM (SELECT a.*,
(SELECT b.rowid||c.rowid||d.rowid as rid
FROM ( SELECT * FROM svc_hist b ORDER BY start_dt DESC) b,
( SELECT * FROM cust_hist c ORDER BY start_dt DESC) c,
( SELECT * FROM acct_hist d ORDER BY start_dt DESC) d
WHERE b.svc_no = a.svc_no
AND b.start_dt <= a.svc_date
AND b.txt > ' '
AND c.cust_no = a.cust_no
AND c.start_dt <= a.svc_date
AND c.txt > ' '
AND d.acct_no = a.acct_no
AND d.start_dt <= a.svc_date
AND d.txt > ' '
AND ROWNUM = 1) AS rid
FROM svc a
WHERE a.svc_date BETWEEN SYSDATE - 201 AND SYSDATE - 1 ) a,
svc_hist b, cust_hist C, acct_hist D
WHERE SUBSTR(A.RID, 1, LENGTH(A.RID)/3) = B.ROWID
AND SUBSTR(A.RID, LENGTH(A.RID)/3 + 1, LENGTH(A.RID)/3) = C.ROWID
AND SUBSTR(A.RID, 2*LENGTH(A.RID)/3 + 1, LENGTH(A.RID)/3) = D.ROWID ;
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 200 |00:00:00.02 | 2610 |
| 1 | NESTED LOOPS | | 1 | 8040G| 200 |00:00:00.02 | 2610 |
|* 2 | COUNT STOPKEY | | 200 | | 200 |00:00:00.01 | 2006 |
| 3 | NESTED LOOPS | | 200 | 1 | 200 |00:00:00.01 | 2006 |
| 4 | NESTED LOOPS | | 200 | 1 | 200 |00:00:00.01 | 1202 |
| 5 | VIEW | | 200 | 1 | 200 |00:00:00.01 | 600 |
|* 6 | TABLE ACCESS BY INDEX ROWID | CUST_HIST | 200 | 100 | 200 |00:00:00.01 | 600 |
|* 7 | INDEX RANGE SCAN DESCENDING| PK_CUST_HIST | 200 | 18 | 200 |00:00:00.01 | 400 |
| 8 | VIEW | | 200 | 1 | 200 |00:00:00.01 | 602 |
|* 9 | TABLE ACCESS BY INDEX ROWID | ACCT_HIST | 200 | 100 | 200 |00:00:00.01 | 602 |
|* 10 | INDEX RANGE SCAN DESCENDING| PK_ACCT_HIST | 200 | 18 | 200 |00:00:00.01 | 402 |
| 11 | VIEW | | 200 | 1 | 200 |00:00:00.01 | 804 |
|* 12 | TABLE ACCESS BY INDEX ROWID | SVC_HIST | 200 | 100 | 200 |00:00:00.01 | 804 |
|* 13 | INDEX RANGE SCAN DESCENDING | PK_SVC_HIST | 200 | 18 | 200 |00:00:00.01 | 604 |
| 14 | NESTED LOOPS | | 1 | 201M| 200 |00:00:00.02 | 2410 |
| 15 | NESTED LOOPS | | 1 | 201K| 200 |00:00:00.02 | 2212 |
| 16 | VIEW | | 1 | 201 | 200 |00:00:00.02 | 2013 |
|* 17 | FILTER | | 1 | | 200 |00:00:00.01 | 7 |
| 18 | TABLE ACCESS BY INDEX ROWID | SVC | 1 | 201 | 200 |00:00:00.01 | 7 |
|* 19 | INDEX RANGE SCAN | IX_SVC_01 | 1 | 201 | 200 |00:00:00.01 | 4 |
| 20 | TABLE ACCESS BY USER ROWID | CUST_HIST | 200 | 1000 | 200 |00:00:00.01 | 199 |
| 21 | TABLE ACCESS BY USER ROWID | ACCT_HIST | 200 | 1000 | 200 |00:00:00.01 | 198 |
| 22 | TABLE ACCESS BY USER ROWID | SVC_HIST | 200 | 40000 | 200 |00:00:00.01 | 200 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
6 - filter("C"."TXT">' ')
7 - access("C"."CUST_NO"=:B1 AND "C"."START_DT"<=:B2)
9 - filter("D"."TXT">' ')
10 - access("D"."ACCT_NO"=:B1 AND "D"."START_DT"<=:B2)
12 - filter("B"."TXT">' ')
13 - access("B"."SVC_NO"=:B1 AND "B"."START_DT"<=:B2)
17 - filter(SYSDATE@!-201<=SYSDATE@!-1)
19 - access("A"."SVC_DATE">=SYSDATE@!-201 AND "A"."SVC_DATE"<=SYSDATE@!-1)
변경이력에서 단 하나의 컬럼만 조회할 때는 위의 방법을 사용할 필요가 없다. rowid 대신에 값을 select 하면 된다. 따라서 비효율도 없다. 하지만 변경이력에서 여러 컬럼을 조회해야 한다면 위의 방법처럼 rowid를 사용해야 한다. 그렇게 되면 변경이력 테이블은 두 번씩 액세스 하는 셈이다. 하지만 이때에도 인덱스는 두 번 Scan 하지 않는다. 이제 성능의 안정성을 보기 위해 오래된 데이터를 조회해 보자.
SELECT a.svc_no, a.cust_no, a.acct_no, a.svc_date, a.txt,
b.start_dt, b.txt, c.start_dt, c.txt, d.start_dt, d.txt
FROM (SELECT a.*,
(SELECT b.rowid||c.rowid||d.rowid as rid
FROM ( SELECT * FROM svc_hist b ORDER BY start_dt DESC) b,
( SELECT * FROM cust_hist c ORDER BY start_dt DESC) c,
( SELECT * FROM acct_hist d ORDER BY start_dt DESC) d
WHERE b.svc_no = a.svc_no
AND b.start_dt <= a.svc_date
AND b.txt > ' '
AND c.cust_no = a.cust_no
AND c.start_dt <= a.svc_date
AND c.txt > ' '
AND d.acct_no = a.acct_no
AND d.start_dt <= a.svc_date
AND d.txt > ' '
AND ROWNUM = 1) AS rid
FROM svc a
WHERE a.svc_date BETWEEN SYSDATE - 1999 AND SYSDATE - 1799 ) a,
svc_hist b, cust_hist C, acct_hist D
WHERE SUBSTR(A.RID, 1, LENGTH(A.RID)/3) = B.ROWID
AND SUBSTR(A.RID, LENGTH(A.RID)/3 + 1, LENGTH(A.RID)/3) = C.ROWID
AND SUBSTR(A.RID, 2*LENGTH(A.RID)/3 + 1, LENGTH(A.RID)/3) = D.ROWID;
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 200 |00:00:00.02 | 2602 |
| 1 | NESTED LOOPS | | 1 | 8080G| 200 |00:00:00.02 | 2602 |
|* 2 | COUNT STOPKEY | | 200 | | 200 |00:00:00.02 | 2004 |
| 3 | NESTED LOOPS | | 200 | 1 | 200 |00:00:00.01 | 2004 |
| 4 | NESTED LOOPS | | 200 | 1 | 200 |00:00:00.01 | 1202 |
| 5 | VIEW | | 200 | 1 | 200 |00:00:00.01 | 602 |
|* 6 | TABLE ACCESS BY INDEX ROWID | CUST_HIST | 200 | 100 | 200 |00:00:00.01 | 602 |
|* 7 | INDEX RANGE SCAN DESCENDING| PK_CUST_HIST | 200 | 18 | 200 |00:00:00.01 | 402 |
| 8 | VIEW | | 200 | 1 | 200 |00:00:00.01 | 600 |
|* 9 | TABLE ACCESS BY INDEX ROWID | ACCT_HIST | 200 | 100 | 200 |00:00:00.01 | 600 |
|* 10 | INDEX RANGE SCAN DESCENDING| PK_ACCT_HIST | 200 | 18 | 200 |00:00:00.01 | 400 |
| 11 | VIEW | | 200 | 1 | 200 |00:00:00.01 | 802 |
|* 12 | TABLE ACCESS BY INDEX ROWID | SVC_HIST | 200 | 100 | 200 |00:00:00.01 | 802 |
|* 13 | INDEX RANGE SCAN DESCENDING | PK_SVC_HIST | 200 | 18 | 200 |00:00:00.01 | 602 |
| 14 | NESTED LOOPS | | 1 | 202M| 200 |00:00:00.03 | 2402 |
| 15 | NESTED LOOPS | | 1 | 202K| 200 |00:00:00.02 | 2205 |
| 16 | VIEW | | 1 | 202 | 200 |00:00:00.02 | 2011 |
|* 17 | FILTER | | 1 | | 200 |00:00:00.01 | 7 |
| 18 | TABLE ACCESS BY INDEX ROWID | SVC | 1 | 202 | 200 |00:00:00.01 | 7 |
|* 19 | INDEX RANGE SCAN | IX_SVC_01 | 1 | 202 | 200 |00:00:00.01 | 3 |
| 20 | TABLE ACCESS BY USER ROWID | CUST_HIST | 200 | 1000 | 200 |00:00:00.01 | 194 |
| 21 | TABLE ACCESS BY USER ROWID | ACCT_HIST | 200 | 1000 | 200 |00:00:00.01 | 197 |
| 22 | TABLE ACCESS BY USER ROWID | SVC_HIST | 200 | 40000 | 200 |00:00:00.01 | 200 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=1)
6 - filter("C"."TXT">' ')
7 - access("C"."CUST_NO"=:B1 AND "C"."START_DT"<=:B2)
9 - filter("D"."TXT">' ')
10 - access("D"."ACCT_NO"=:B1 AND "D"."START_DT"<=:B2)
12 - filter("B"."TXT">' ')
13 - access("B"."SVC_NO"=:B1 AND "B"."START_DT"<=:B2)
17 - filter(SYSDATE@!-1999<=SYSDATE@!-1799)
19 - access("A"."SVC_DATE">=SYSDATE@!-1999 AND "A"."SVC_DATE"<=SYSDATE@!-1799)
이번에는 같은 방법으로 오래된 데이터를 조회 해보았지만 성능은 역시 안정적이다. 최근 데이터를 조회할 때는 종료일자 + 시작일자 인덱스가 1937 블럭을 Scan하였고 위의 SQL 에서는 2602블럭을 Scan 하였으므로 좋지 않다고 생각할 수 있다. 하지만 오래된 데이터를 구할 때 종료일자 + 시작일자 인덱스가 6498 블럭이나 Scan 하였다는 점을 생각하면 같은 테이블들을 두번씩이나 사용했음에도 성능에서 우열을 가리기 힘들다.
결론
변경이력을 Between 으로 조인해서 여러 건을 조회할 때에도 종료일자의 장점은 찾기 힘들므로 변경일자(시작일자)만 사용하면 된다. 어찌되었건 현재와 같은 무조건적인 종료일자의 사용은 자제해야 한다. 역정규화를 하거나 하지 않는 것은 당신의 자유다. 그리고 변경이력에 종료일자를 추가하여 역정규화 하는 것은 정합성 측면에서는 손실이지만 장점도 있다. 하지만 그것은 성능이 아니라 SQL이 간단해진다는 관점이 아닐까? 몇 배나 단순해질지가 문제이긴 하지만....
PS
이 글을 보고 성급하게 운영중인 테이블을 건드리면 안 된다. 제발 운영중인 테이블과 인덱스는 그냥 두기 바란다. 만약 역정규화를 제거하려면 다음 번 모델링 시에 반영하기 바란다. 그것이 아니라면 철저히 준비하고 실행하기 바란다.
현실세계의 모든 문제를 해결할 수 있는 글이란 없다. 그러므로 시작일자 컬럼만으로 해결하기 어려운 또 다른 패턴의 SQL이 있다면 나에게 알려주기 바란다. 그래야 더 많은 문제가 해결 될 터이니. 시작일자만으로 구현하기 어려운 SQL들도 결국은 몇 개의 패턴으로 정리되지 않을까 생각해본다.
'Modeling' 카테고리의 다른 글
| 과학과 철학의 간격 (2) | 2010.12.15 |
|---|---|
| 3부 - 변경이력 테이블에 종료일자가 필요한가? (8) | 2010.12.03 |
| 변경이력 테이블에 종료일자가 필요한가? (30) | 2010.11.17 |
| 역정규화 무엇이 문제인가? (7) | 2009.12.14 |
| Varchar2(8) VS Date 어느 것이 우월한가? (18) | 2009.10.21 |
