전문가칼럼

DBMS, DB 구축 절차, 빅데이터 기술 칼럼, 사례연구 및 세미나 자료를 소개합니다.

관계형 데이터베이스 분석/설계 기본으로 돌아가자! 관계형 이론을 근간으로(3)

전문가칼럼
DBMS별 분류
Etc
작성자
admin
작성일
2021-02-22 16:04
조회
1607

데이터 조작(Data Manipulation 집합(Set)처리) 과 Key 와 Index

엔코아 정보 컨설팅의 장희식

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

부서별 사원 수를 알고 싶어하는 정보요구가 있었다고 가정하자.
계층형 데이터베이스(HDB)인 경우의 처리절차를 보겠다. - 부서코드순으로 보자.
1) 부서코드나 부서명 순으로 보고자 한다면 부서코드나 부서명으로 시작되는 Secondary Key를 생성해야 한다. 그래야만 부서코드나 부서명 순으로 데이터를 접근할 수 가 있다.
2) Work Area 에 임시로 저장할 사원 수 필드와 부서코드 필드를 정의한다.
3) Segment를 읽고 처음 읽은 레코드의 부서코드를 임시부서코드 필드에 저장하고 사원수도 하나 증가시킨다.
4) Segment의 레코드가 최종레코드인지를 확인하며 다음 레코드를 읽는다.
5) 읽은 레코드의 부서코드와 임시부서코드에 저장된 값을 비교하여 같으면 사원 수를 하나 더 증가시키고, 다르면 사원 수에 1값을 세팅한다.
6) 이런 식으로 매번 레코드의 읽은 값을 확인하면서 End of File을 만날 때까지 절차적으로 처리를 한다.

관계형 데이터베이스(RDB)인 경우의 처리절차를 보겠다. - 부서코드순으로 보자.
SELECT 부서코드, COUNT(*)
FROM 사원
GROUP BY 부서코드

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

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

여기에서 또 한가지 중요한 사항이 온라인 트랜잭션 프로세싱(OLTP OnLine Transaction Processing)은 대개의 경우 몇 개의 테이블을 조인(Join)하여 정보를 사용자에게 제공하는데 대부분이 NL(Nested Loop) 조인(Join)을 사용한다. 관계형 데이터베이스에서 조인의 개념은 1:M의 관계를 갖는 테이블을 조인하면, 1 * M = M의 곱집합의 행(Row)의 정보가 제공된다. 이러한 NL 조인의 경우나 또는 단일 테이블내의 대용량의 데이터 가운데 소량의 데이터를 빨리 찾는 기법이 색인(Index)을 사용하는 것이다.

관계형 데이터베이스 이전의 데이터베이스나 File시스템에서는 Key = Index 의 개념을 가지고 있다. 즉 Key라고 하는 것이 곧 접근경로(Access Path)이며, 이러한 Key가 곧 정렬(Sort)의 기준이 되는 경우가 많았다. 그러나 관계형 데이터베이스에서는 Key = Index가 아니다. 관계형 데이터베이스에서 Key라고 하는 것은 무결성(Integrity)개념에서 자세히 설명 하겠지만, Key는 실체 무결성(Entity Integrity - PK)과 참조 무결성(Referential Integrity - FK)에 관한 것이다.

우리가 Primary Key를 생성하면 보통 Unique Index가 생성된다. 이는 실체무결성의 정의에서 Primary Key는 Not Null 이며 Unique(행(Row)은 하나 이상의 컬럼(Column)에 의해서 유일하게 식별 되야 한다(Each row is unique) 데이터 구조의 세 번째 특성)해야만 한다는 특성을 만족시킬 수 있는 유일한 물리적(Physical)인 방법이 Unique Index를 생성해 주는 것이다. 현장에서 테이블의 스키마(Schema 데이터 구조) 정보를 보면 이러한 PK가 없다거나, PK가 없다면 Unique Index라도 있어야 하는데 아예 아무것도 없는 경우가 또한 종종 있다. 이러한 경우 무엇이 문제인가 위에서 조인의 개념을 설명했다. 만약 행(Row)의 유일성(Unique)이 깨진다면 위에 예를 든 부서 테이블에 “100”번의 부서가 두 번 나타난다면, 사원 테이블과 조인을 할 경우 “100”번의 부서에 속해있는 사원들은 두 번씩 나타날 것이다. 조인은 1 * M = M의 곱집합 이라고 하였는데 여기서는 2 * M = 2M의 수만큼 배로 나오게 되는 것이다. 유일성이라는 개념이 이렇게 중요한 개념임에도 실제 구성되어 있는 테이블들이 행(Row)의 유일성을 보장해 주는 아무 장치도 없다는 것은 참으로 놀라운 일이다.

그런데 이러한 경우도 관계형 데이터베이스 이전의 것처럼 로직으로 처리하여 데이터를 비교하면서 보여준다면 처리하는 데는 별 문제가 없을 것이다. Work Area(Temp Column)에 지점코드나 상품코드를 저장해 두었다가 같은 값이 나오면 두 번 처리를 안 하게끔 하고 다음 레코드를 읽어 처리하면 된다(곱집합의 개념이 없으니). 그러니 상기 계좌번호 컬럼이 지점코드 + 상품코드 + 고객번호 + 일련번호로 합쳐진 형태로 구성되어 Key로 형성되는 것이 레코드를 한건씩 읽어가면서 지점별 상품별 뭐 이런 식으로 합계를 생성하기가 훨씬 유리하기 때문에 과거 시스템(계층형 데이터베이스 또는 File 시스템)에서는 원자속성, 집합처리, Key와 Index의 차이점에 대한 개념 없이 테이블의 컬럼을 상기 사례와 같이 구성 해 놓는 것이 당연히 맞는 개념이며 사용하기가 훨씬 좋고, 생산성도 좋았던 것이다.

간단한 예 이여서 관계형 데이터베이스를 쓰면서 누가 계층형 데이터베이스처럼 처리하겠냐고 하겠지만 여러분이 관계형 데이터베이스를 사용하면서 DECLARE CURSOR 문장을 이용하여 데이터를 처리하는 방식이 모두 계층형 데이터베이스에서 레코드를 절차처리 하던 방식과 동일한 방식인 것이다. 관계형 데이터베이스의 성능에 가장 영향을 많이 미치는 요소는 Disk IO의 양과 DB Call의 횟수인데, 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_cntNUMBER := 0 ; -- 건수 Check

cursor_dlayINTEGER ; -- cursor 변수

retINTEGER ; -- Return Value

v_dateVARCHAR(8) ;

. . . . . .

v_anl_feeNUMBER ;

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_dateAND 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'

THENNULL;

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' THENNULL;

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의 내용이 어떤 것인지 너무 신경 쓰지 말자. 전체적인 구조를 유심히 봤으면 한다.

튜닝전의 Stored Procedure를 보면 Cursor(시스템이 사용하는 Work Area)를 선언하고, Cursor를 Open하고, 행(Row)을 한건씩 Fetch(파일 시스템의 Read개념)한 후 계산사항을 계산하고, 다른 테이블에 Insert를 한다. 전형적인 절차 처리 방식으로 구현된 Stored Procedure이다.

처리해야 할 행(Row)이 3,000,000건 이라고 가정해보자. 한 번의 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개의 연산자가 모두 집합개념이며, 데이터의 처리 입력, 수정, 삭제도 집합개념으로 처리를 해야만 한다는 것이다. 부디 관계형 데이터베이스를 사용하고자 하는 사람은 빨리 사고를 전환해라. 절차처리 개념에서 집합처리 개념으로!

데이터 구조의 첫 번째를 설명하면서 데이터의 조작, Key의 개념(물론 실체무결성에서 다시 설명하겠지만), Key와 Index의 차이 등을 살펴보았다. 이제 데이터 구조의 두 번째 특성 “각 열은 동일한 성격의 값을 가진다”에 대해서 살펴보자.

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