관계형 데이터 조작

데이터 조작(Data Manipulation)

우리는 위에서 관계형 모델은 수학의 집합이론과 1차 술어 논리에 의하여 창안된 데이터베이스 모델이기 때문에 테이블 자체의 구조적 특성이 수학적 이론의 특성을 그대로 가지고 있다는 사실을 알게 되었다.

우리는 위에서 고객의 취미가 “골프”인 고객의 명단을 작성하라는 정보 요구를 파일 이나 계층형 모델에서 COBOL 프로그램의 주요 부분만을 기술하면서 정보를 조회하는 방법(순차적 방법)과 관계형 모델의 SQL을 이용한 정보 조회 방법(집합적 방법)을 살펴 보았다. 관계형 모델의 견고한 수학적 이론은 우리가 데이터를 처리할 때도 그대로 적용되는 것이다. (012.관계형 데이터 구조-각 열은 하나의 값을 갖는다 참고)



관계형 모델의 데이터 처리를 보면, 다시 한번 강조하지만 될 수 으면 하나씩의 레코드(행-Row)를 처리하는 로직으로 프로그램을 작성하지 말라는 것이다. 데이터의 입력(INSERT), 수정(UPDATE), 삭제(DELETE), 조회(SELECT)를 할 때 모두 집합적으로 처리하라는 것이다. 특히 조회(SELECT)의 8개의 관계 연산자를 자세히 보면, 우리가 수학 시간에 배운 부분 집합, 곱 집합, 합 집합, 공통 집합 등을 나타내는 것이 보일 것이다. 아래에는 데이터 처리를 집합적으로 처리해야 만 하는 이유와 각각의 관계(조회) 연산에 대해서 자세히 설명할 것이다.

이제 자료(Data)를 처리하는 방식을 보자. 관계형 데이터베이스 이전의 데이터베이스와 관계형 데이터베이스를 사용하여 자료를 처리하는 방식을 비교하여 설명해 보겠다. 아주 간단한 예를 들어보겠다.



계층형 데이터베이스 모델에서는 ‘부서’나 ‘사원’을 세그먼트(Segment)라 칭한다. ‘부서’를 루트(Root) 또는 부모(Parent) 세그먼트라 칭하고, ‘사원’을 자식 세그먼트라 칭한다. 부모 세그먼트와 자식 세그먼트의 관계는 일대다 이고, 관계형 데이터베이스 모델과의 구조적인 면에서의 큰 차이점은 계층형은 물리적으로 ‘부서코드’ 속성이 자식 세그먼트에 표현되지 않고, 내부적인 포인터(Pointer)에 의해서 연결되어 있고, 관계형 데이터베이스 모델은 ‘부서코드’ 속성이 ‘사원’ 테이블에 ‘부서코드’라고 표현된다는 것이다.

부서별 사원 수를 알고 싶어하는 정보요구가 있다고 가정하자.

계층형 데이터베이스(HDB)인 경우의 처리절차를 살펴보자. 우리는 앞에서 파일이나 계층형 모델에서 순차적인 COBOL 프로그램의 예를 보았다. 이와 마찬가지로 이러한 정보요구를 순차적으로 로직을 구현한다면 주요 로직은 다음과 같을 것이다.

1) 일단 ‘부서코드’는 주키(Primary key)로 선어되어 있을 것이다. 부서명 순으로 정보를 보고자 한다면 ‘부서명’ 에 Secondary Key를 생성해야 한다. 그래야만 부서명 순으로 정보를 보여줄 수 있다.

2) 코볼의 FD(File Definition) 문에 세그먼트와 속성 순서와 데이터 타입인 동일하게 선언을 해야 한다. (관계형 데이터베이스 모델은 이러한 작업이 필요 없음. 열의 순서는 의미가 없고, SELECT LIST절에 필요한 속성만을 나열하면 됨.)

3) ‘부서명’ 순으로 사원수를 보여주기 위하여 Work Area 에 임시로 저장할 사원 수 속성과 부서명 속성을 정의한다.

4) ‘부서’ 세그먼트를 읽고 첫 번째 읽은 ‘부서명’을 저장한다. ‘GNP(Get Next Parent)’ 라는 계층형 데이터베이스 모델에서 부모 세그먼트를 읽고, 자식 세그먼트를 읽을 때 사용하는 명령어를 이용하여 ‘사원’세그먼트를 읽어 내려간다. 사원 세그먼트를 읽으면 Work Area의 사원수 속성의 값을 1로 바꾼다.

5) 다음 레코드를 읽어가면서 ‘사원명’이 바뀌면 Work Area에 다시 ‘사원명’을 저장하고, 사원수 속성값을 0으로 바꾼다. 계속해서 레코드를 읽어가면서 ‘사원명’이 바뀔 때까지 ‘사원수’를 더해가다가 파일의 최종 레코드이면 이 로직을 멈춘다.

6) 마지막‘Finalization Process’에서 화면이나 보여주고자 하는 Report의 포맷(Format)에 맞추어서 보여준다.

관계형 데이터베이스(RDB)인 경우의 처리절차를 보겠다.

SELECT 부서.부서명, COUNT(사원.사원번호)
FROM 부서, 사원
WHERE 부서.부서코드 = 사원.부서코드
GROUP BY 부서명

상기의 SQL 문장 하나로 계층형 데이터베이스에서의 처리절차가 끝나는 것이다.

계층형 데이터베이스에서의 데이터 처리 방식은 절차적인 RECORD처리 방식이고, 관계형 데이터베이스에서의 데이터 처리 방식은 원자값(Atomic Value)에 의한 집합(SET)처리 방식이다. SQL(Structured Query Language)은 전체 테이블의 집합(Set)에서 정의역(SQL의 WHERE조건에 정의해 주는 값)을 결정해 주면, 관계형 데이터베이스의 옵티마이져(Optimizer)가 계층형 데이터베이스에서 우리가 로직으로 레코드 한 건씩 처리하는 것을 수행한 후 그 결과를 즉 우리가 원하는 데이터 집합(Set)의 치역(値域)을 제공한다. 그러니 처리하는 로직은 옵티마이져한테 맡기고 우리는 정의역만 제공하면 그만인 것이다.

위의 예에서 퇴직자를 제외한 부서별 인원수를 요구하면 순차 처리 방식에서는 퇴직자를 제외하는 로직을 구현해야 하지만, 관계형 데이터베이스의 SQL에서는 WHERE 조건에 “퇴직일자 IS NOT NULL”을 추가하여 정의역만 바꿔주면 되는 것이다. 그런데 과거의 사용자들은 이러한 개념을 정확히 모르고 관계형 데이터베이스를 사용하면서도 많은 프로그램을 순차 처리 방식으로 처리하고 있는 것이다.

간단한 예 이여서 관계형 데이터베이스를 쓰면서 누가 계층형 데이터베이스처럼 처리하겠냐고 하겠지만 여러분이 관계형 데이터베이스를 사용하면서 DECLARE CURSOR 문장을 이용하여 데이터를 처리하는 방식이 모두 계층형 데이터베이스에서 레코드를 절차처리 하던 방식과 동일한 방식인 것이다. 관계형 데이터베이스의 성능에 가장 영향을 많이 미치는 요소는 Disk IO의 양과 SQL 문장의 실행 횟수인데, DECLARE CURSOR를 이용하여 Logic을 구사하여 한 건씩 데이터를 입력(Insert) 또는 수정(Update) 한다면 이는 절대적으로 성능을 보장 할 수 없는 것이다. 실제 예를 들어보겠다.

Cursor를 이용한 절차(Logic) 처리 방식-전체적인 구조를 살펴보자

(Oracle 8.1.7.4 버전에서 구현된 Stored Procedure)
CREATE OR REPLACE PROCEDURE SP_PROCA (arg_ymd IN CHAR) IS
v_cnt NUMBER := 0 ; -- 건수 Check
cursor_dlay INTEGER ; -- cursor 변수
ret INTEGER ; -- Return Value
v_date VARCHAR(8) ;
. . . . . .
v_anl_fee NUMBER ;CURSOR c_dlayrt_list IS
SELECT /*+ use_hash( a b c ) */
REQ_DATE, COLL_DATE, DLAY_MONTH,
. . . . . .
FROM
(SELECT /*+ ordered use_hash( a b ) */ a.*
FROM TABLEA a,
( SELECT mb_cdhd_no, MAX(req_date) req_date,
SUBSTR(MAX(req_date||coll_date),9,8) coll_date
FROM TABLEA
WHERE coll_date < = v_std_date
GROUP BY mb_cdhd_no ) b
WHERE a.req_date = b.req_date
AND a.coll_date = b.coll_date
AND a.mb_cdhd_no = b.mb_cdhd_no) a,
(SELECT MB_CDHD_NO, MAX(lst_coll_date) LST_COLL_DATE,
. . . . . .
FROM TABLEB
WHERE std_date = v_std_date
GROUP BY mb_cdhd_no ) b,
( SELECT mb_cdhd_no, SUM(NVL(temp_come_left_amt,0)) TEMP_COME_LEFT_AMT
FROM TABLEC
WHERE temp_come_left_amt > 0
AND cncl_yn NOT IN ('1')
GROUP BY mb_cdhd_no ) c
WHERE a.mb_cdhd_no = b.mb_cdhd_no(+)
AND a.mb_cdhd_no = c.mb_cdhd_no(+);
BEGIN
v_date := sf_getetldate('1', arg_ymd);
IF sf_log_dlay(arg_ymd,'SP_PROCA','TABLEAMBO','1',0,v_date,'R','1') != 'S' THEN
NULL;
END IF;
v_std_date := arg_ymd;
v_cnt := 0;
BEGIN
SELECT 1 AS cnt
INTO v_exist_chk
FROM dual
WHERE EXISTS ( SELECT 'X'
FROM TABLEAmbo
WHERE std_date = v_std_date);
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_exist_chk := 0;
END;
IF v_exist_chk > 0 THEN
FOR i IN 0..9 LOOP
DELETE FROM TABLEAmbo
WHERE std_date = v_std_date AND rownum < 200000;
COMMIT;
END LOOP;
END IF;
COMMIT;
OPEN c_dlayrt_list;
LOOP
FETCH c_dlayrt_list INTO
v_req_date, v_coll_date, v_dlay_month,
. . . . . .;
EXIT WHEN c_dlayrt_list%NOTFOUND;
v_cnt := v_cnt + 1;
IF v_reqday_cnt >= 0 THEN
v_dlay_month := v_dlay_month + 1;
. . . . . .
END IF;
INSERT INTO TABLEAmbo VALUES
(v_std_date, v_mb_cdhd_no, v_req_date,
. . . . . . );
IF MOD(v_cnt, 10000) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
IF sf_log_dlay(arg_ymd,'SP_PROCA','TABLEAMBO','1',v_cnt,v_date,'Y','2') != 'S'
THEN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
IF sf_log_dlay(arg_ymd,'SP_PROCA','TABLEAMBO', '1',v_cnt,v_date ||
'(' || TO_CHAR(SQLCODE) || ')','N' '2') != 'S' THEN NULL;
END IF;
END;

SQL로 구현한 SET처리 방식

INSERT /*+ append */ INTO TABLEAmbo a
select /*+ use_hash( a b c ) */
std_date, a.MB_CDHD_NO, REQ_DATE, COLL_DATE,
. . . . . .
FROM
(SELECT /*+ leading(b) full(a) parallel(a 4) use_hash(a b) */ a.*
FROM TABLEA a,
(SELECT /*+ index_ffs(a TABLEA_pk) parallel_index(a 4) */
mb_cdhd_no, MAX(req_date||coll_date) o_max
FROM TABLEA a
WHERE coll_date < = '20030618'
GROUP BY mb_cdhd_no ) b
WHERE a.req_date = substr(b.o_max,1,8)
AND a.coll_date = substr(b.o_max,9)
AND a.mb_cdhd_no = b.mb_cdhd_no) a,
( SELECT /*+ full(a) parallel(a 4) */
MB_CDHD_NO,
MAX(lst_coll_date) LST_COLL_DATE,
SUM(DECODE(NVL(lo_lon_amt,0),0,0,1)) LON_DLAY_CNT,
. . . . . .
FROM TABLEB a
WHERE std_date = '20030618'
GROUP BY mb_cdhd_no) b,
( SELECT /*+ full(a) parallel(a 4) */ mb_cdhd_no,
SUM(NVL(temp_come_left_amt,0)) TEMP_COME_LEFT_AMT
FROM TABLEC a
WHERE temp_come_left_amt > 0
AND cncl_yn NOT IN ('1')
GROUP BY mb_cdhd_no) c
WHERE a.mb_cdhd_no = b.mb_cdhd_no(+)
AND a.mb_cdhd_no = c.mb_cdhd_no(+)

지면 할애상 SELECT List에 표현되는 많은 컬럼 들을 생략했다. 여기서 보여주고자 하는 것은 절차처리 방식과 집합처리 방식의 차이점을 보여주고자 하는 것이니 단위 SQL의 내용이 어떤 것인지 너무 신경 쓰지 말자. 전체적인 구조를 유심히 봤으면 한다.

로직처리를 SQL로 변경전의 스토어드 프로시져(Stored Procedure)를 보면 커서(Cursor: 시스템이 사용하는 Work Area)를 선언하고, 커서를 오픈(Open)하고, 행(Row)을 한 건씩 펫치(Fetch : 파일 시스템의 Read개념)한 후 계산사항을 계산하고, 다른 테이블에 입력을 한다. 전형적인 절차 처리 방식으로 구현된 스토어드 프로시져(Stored Procedure)이다.

처리해야 할 행(Row)이 3백만 건 이라고 가정해보자. 한 번의 Insert문장을 실행하는데 예를 들어 0.001초만 걸린다 하더라도 3,000,000 * 0.001 / 60 = 50분이 걸린다. 이렇게 처리하여서는 도저히 시간을 줄일 수 없는 것이다. 또한 커서(Cursor)를 이용하여 처리한 프로그램은 로직 구현뿐만 아니라 코딩 공수 측면 등 여러 부분에서 이로울 게 하나도 없음에도 많은 프로그래머들이 이렇게 프로그램을 짜고 있다. 실제 튜닝 프로젝트에서 Cursor를 이용한 절차처리는 30분 이상이 걸린 반면, 한 번의 SQL로 처리한 결과는 5분만에 끝날 수 있었다. 데이터가 증가하면 할수록 Cursor를 이용한 프로그램은 소요시간이 기하급수적으로 증가할 것이다.

튜닝 한 Stored Procedure는 원하는 정보를 Hash Join을 이용하여 Select하면서 Select List에서 계산할 것을 계산하면서 바로 Insert를 하도록 구현하고 있다. 대용량 Batch처리 프로그램은 주로 Hash Join의 기법을 이용한다. 여기서 말하고자 하는 것은 이러한 기법을 어떻게 사용하는지에 관한 것이 아니므로 자세한 것은 다른 참고서적을 참조하기 바란다. 중요한 것은 로직으로 처리한 것을 하나의 SQL문장으로 처리하면서 어떻게 수행속도를 보장하게끔 SQL을 구사하느냐가 가장 중요한 것이다. 이러한 수행속도 향상 기법도 여기서 논하고자 하는 주제와는 별도의 주제이므로 다른 참고서적을 이용하기 바란다.

계속해서 반복되는 얘기 같지만 이러한 현상 또한 전산을 해온 선배들이 관계형 데이터베이스의 데이터 처리 방식인 SET처리 방식을 이해 못하고 과거에 COBOL 프로그램 형식으로 짜는 방식으로 프로그램을 한 결과인 것이다. 다시 한번 강조하지만 관계형 데이터베이스를 사용하면서 가장 중요한 개념은 집합(SET)이다. 테이블(Table), 관계(Relationship), 속성(Attribute)이 모두 집합 개념이며, 데이터를 조회하는 8개의 연산자가 모두 집합개념이며, 데이터의 처리 입력, 수정, 삭제도 집합개념으로 처리를 해야만 한다는 것이다. 부디 관계형 데이터베이스를 사용하고자 하는 사람은 빨리 사고를 전환해라. 절차처리 개념에서 집합처리 개념으로!

SELECT(OR RESTRICT).

PROJECT.

PRODUCT.

JOIN.

UNION.

INTERSECT.

DIFFERENCE.

DIVISION.

출처 : 한국데이터베이스진흥원

제공 : DB포탈사이트 DBguide.net