절차형 처리를 SQL로 처리함으로서 액세스의 효율화 , 수행속도의 최적화에 대한 SAMPLE에 대해 알아본다
- 통신회사의 요금계산
- 고객의 실적을 관리하는 회사의 고객 실적집계 등 여러 곳에서 사용되어지는 집계 정보를 생성하는 곳에서 유용하게 사용할 수 있다.
여러 기간에 걸처진 고객의 상태변화를 가장 적은 단위(1일)로 나누어서 계산한다. 인식의 변화가 필수적이라 하겠다.
- 어느 통신 회사에서는 지난 달 사용자의 사용 실적에 따라 매월 청구 작업을 하고 있다. 월 사용료는 정액제로 계산되나 사용자의 특별한 사유에 따라 다양한 경우의 감액 요인이 발생한다.
- 예를 들어 장애자에 대한 감액, 연체에 의해 강제로 사용 정지를 당했을 때의 감액, 분실로 인해 일정 기간 사용이 중지된 경우의 감액 등이 있다. 빈번하게 발생하지는 않겠지만 이러한 감액 요인은 월 중 임의의 기간에 중첩되어 발생할 수 있고 여러 번 반복해서 발생하기도 한다.
- 장애자에 대한 감액을 제외하고는 몇 가지가 중첩되어 발생하더라도 한 가지의 감액 요인으로 간주한다. 장애자인 경우에는 장애 기간중에 다른 감액 요인이 발생하지 않았다면 정액의 20%, 다른 감액 요인이 있는 날인 경우는 감액 계산 후의 20%를 할인 받는다. 장애가 아닌 경우의 감액률은 90% 이며 고객수는 대략 500 만명이다.
- 고객의 상태 변화를 저장하고 있는 테이블은 다음과 같다.
CREATE TABLE 고객상태변경정보
(
고객코드 VARCHAR2(4) NOT NULL,
.
.
상태코드 VARCHAR2(20) NOT NULL,
상태시작일 VARCHAR2(8) NOT NULL,
상태종료일 VARCHAR2(8) NOT NULL
)
- 과거에 처리하던 방법은 다름과 같았다.
- 먼저 전체 고객들 중에서 청구대상 고객을 하나씩 차례로 읽어간다.
- 읽혀진 고객에 대해 고객상태의 변화 정보가 기록되어 있는 여러 테이블을 읽어 각각의 상태가 지속된 기간을 찾는다.
- 일자 수(30일) 만큼의 루프를 수행하여 여러 가지의 ‘IF’를 동원하여 각각의 일자가 감액되어야 할 방법을 결정하고 감액 종류별로 집계하여 감액률을 곱하여 더한다.
- 계산이 완료되었으면 그 결과를 테이블에 저장한다.
- 이와 같은 방법으로 전체 고객이 끝날 때까지 반복하여 수행한다.
- 사용자가 500만 명이라면 수행되는 SQL의 처리 횟수는 엄청나다. 수행 시간 또한 어마 어마 할 것이 분명하다.
- 다음과 같은 SQL 처리를 보자
SELECT 고객코드,
SUM(950 * NVL(감액률1,1) * NVL(감액률2,1) ) 감액금액,
COUNT( 감액률1||감액률2 ) 감액일수
FROM (
SELECT X.고객코드,
AVG(DECODE(상태코드,’분실’,0.9,’사용정지’,0.9,‘일시정지’,0.0 )) 감액률1,
MIN(DECODE(상태코드,’장애자상태’,0.2)) 감액률2
FROM (
SELECT 고객코드,상태코드,
GREATEST(‘19980601’,상태시작일) 시작일,
LEAST(‘19980630’,상태종료일) 종료일
FROM 고객상태변경정보
WHERE 상태코드 IN ( ‘분실’,’사용정지’,’일시정지’,’장애자상태’ )
AND 상태종료일 >= ‘19980601’
AND 상태시작일 <= ‘19980630’
) X, COPY_T Y
WHERE Y.NO BETWEEN SUBSTR(X.시작일,7,2) AND SUBSTR(X.종료일,7,2)
GROUP BY X.고객코드, Y.NO
)
GROUP BY 고객코드 ;
- 이 SQL은 과거의 절차형으로 복잡하게 작성된 처리 절차를 대신한다고 본다면 그에 비해 매우 단순하게 작성되어 있다. 그렇지만 고객의 상태가 아무리 복잡하게 변경되어 있는 경우에도 전혀 분제없이 원하는 결과를 얻을 수 있다.
- 보다 자세한 내용은 ‘대용량 데이터베이스 솔루션 II Page 1-51, 2.5 처리경로 개선의 용이성’을 참조하세요..