몇 년 전 데이터웨어하우스 업무를 처음 맡게 되면서, 개념을 잡기 위해 정리했던 내용을 발견했다. 여기에 다시 포스팅해둔다.

□ DW(DataWarehouse) 란?


○ 정의

사용자의 의사결정에 도움을 주기 위하여, 기간시스템의 데이터베이스에 축적된 데이터를 공통의 형식으로 변환해서 관리하는 데이터베이스

○ 특성

- 주제 지향성(subject-orientation): 데이터를 주제별로 구성함으로써 전산에 약한 사용자라도 이해하기 쉬움(운영계 DB는 어플리케이션 중심)

- 통합성(integration): 데이터가 DW에 들어갈 때는 일관적인 형태(명명법, 변수 측정, 코드화 구조 등)로 변환

- 시계열성(time-variancy): 과거의 데이터를 보관함(운영계 DB가 항상 최근의 데이터만 가지고 있는 것과 다름)

- 비휘발성(nonvolatilization): DW에 데이터가 적재되면 일괄처리(batch)에 의한 갱신 외에는 Insert나 Delete 등의 변경이 수행되지 않음

(출처:https://ko.wikipedia.org/wiki/%EB%8D%B0%EC%9D%B4%ED%84%B0_%EC%9B%A8%EC%96%B4%ED%95%98%EC%9A%B0%EC%8A%A4)

○ 아키텍쳐

DW마다 다르나 통상 아래와 같은 Layer들을 포함

  

- Data Source Layer: DW에 들어갈 다양한 타입(운영데이터, 웹서버 로그, 리서치자료 등), 다양한 포맷(텍스트파일, DB, 엑셀 등)의 데이터소스

- Data Extraction Layer: DM에 들어갈 데이터를 추출하며, 최소한의 데이터 정리 가능성이 있음

- Staging Area:  이후 DW 혹은 DM에서 이루어질 데이터 처리, 통합을 쉽게 하기 위해 존재하는 공간

- ETL Layer: 데이터에 분석 로직이 더해져 정보화되며, 데이터 정리가 이루어짐. ETL tool이 주로 이 단계에서 사용됨

- Data Storage Layer: 변형 및 정제된 데이터가 위치하는 Layer. Scope와 기능에 따라 3가지 타입의 개체가 존재(DataWarehouse, DataMart, OperationalDataStore(ODS))

- Data Logic Layer: Business rules가 저장되는 Layer. 데이터 변환에는 영향을 주지 않지만, 보고서의 형태에 영향을 줌

- Data Presentation Layer: 정보가 사용자들에게 보여지는 단계. OLAP tool이나 Reporting tool 이 단계에서 사용됨

- Metadata Layer: 메타데이터가 저장되는 Layer. 메타데이터 관리를 위해 Metadata tool을 사용함

- System Operations Layer: ETL job status, 시스템 성능, 유저 접속 로그 등 DW시스템 운영과 관련된 정보가 포함된 Layer







(출처:https://www.1keydata.com/datawarehousing/data-warehouse-architecture.html)

□ RM(Resource Manager)이란?

○ 정의

비효율적인 OS 관리로 초래된 문제들(자원의 부적절한 할당, 비효율적인 스케쥴링 등)을 해결하기 위해 Oracle DB server가 자원 관리 결정을 더 잘 하도록 하는 것

○ RM 기능

- 사용자 수나 시스템 부하에 관계없이 사용자에게 자원처리 최소량 보증

- 각각 다른 사용자나 응용프로그램에 CPU time 비율을 할당하여 가용 자원 분배. DW에서는 Batch job보다ROLAP 응용프로그램에 높은 비율이 주어짐

- 사용자 그룹(DB사용자를 리소스 그룹별로 그루핑)이 수행하는 작업의 병행성 수준을 제한

- Active Session Pool(동시에 활성화 되는 세션의 최대치로 구성) 생성. 최대치를 넘어서는 세션은 대기하며,해당 세션에 대해 소멸시간 설정가능

- 관리자 정의 기준에 따라 사용자를 다른 그룹으로 자동 전환 가능(특정 사용자 그룹이 지정된 시간보다 긴 세션을 수행할 경우, 해당 세션을 다른 사용자 그룹으로 옮길 수 있음)

- Optimizer가 추정한 특정 작업(규정된 시간제한을 넘어서는 작업)의 수행 방지

- Undo Pool 생성(한 그룹의 사용자들이 이용하는 undo 스페이스의 양으로 구성)

- session idle time의 양을 제한

- 자원할당을 위한 특정 메서드를 사용하기 위해 instance를 설정instance의 재시작이나 종료 없이 daytime이나 nighttime 설정으로 메서드 변경 가능)

- long-running session 이나 long-running SQL statement 취소를 허용

○ RM 요소

○ Resource Plan 이해

- 기본형태

(출처: http://docs.oracle.com/cd/B19306_01/server.102/b14231/dbrm.htm#i1007556)

하나의 플랜에 따라 각 Resource consumer group이 할당받는 CPU자원이 다름. 만약 SALES group이 자원을 사용하지 않으면 다른 group이 자원을 사용하도록 허용해야함.

- Subplan이 있는 형태

하나의 플랜 아래 Subplan을 두는 것이 가능하며, 이 플랜에 Resource consumer grouop을 세분하할 수 있음. Subplan이나 Resource consumer grouop은 하나 이상의 부모(플랜)을 가질 수 있음. ex) 위 그림에서GREAT_BREAD 플랜이 DAY plan과 NIGHT plan으로 나누어졌을 경우 두 플랜 모두 SALES_TEAM Subplan을 멤버로 두는 것이 가능

* 위 그림에는 없으나 항상 Default로 OTHER_GROUPS가 설정되어 있어야 함

○ DBMS_RESOURCE_MANAGER 패키지

데이터베이스 RM을 관리할 수 있는 시스템 권한(ADMINISTER_RESOURCE_MANAGER)이 있어야 함. 주로 DBA가 ADMIN 옵션으로 해당 권한을 가지고 있음. 권한 획득 후에는 DBMS_RESOURCE_MANAGER 패키지에 있는 프로시져 사용 가능

* 플랜 정보 보는 법

  - Viewing plan information:

select plan, comments from dba_rsrc_plans;

  - Viewing current consumer group for sessions:

select sid, serial#, resource_consumer_group from v$session;

  - Viewing the currently active plans:

alert system set resource_manager_plan = 플랜명;

select name, IS_TOP_PLAN from v$rsrc_plan;

  - Viewing consumer group Granted to user or role:

select * from dba-rsrc_consumer_group_privs;

  - Viewing directive:

select plan, max_est_exec_time, comments, type from dba_rsrc_plan_directives;

○ Pending Area

현재 수행중인 어플리케이션에 영향을 주지 않고 플랜을 만들거나 변경하거나 삭제할 수 있도록 하는 staging area. pending area에서 변경을 한 뒤에는 변경사항을 데이터사전에 적용하기 위해서 pending arear를validate하고 submit해야 함(pending area 없이 플랜을 생성하거나 변경, 삭제 하는 경우 에러 메시지 리턴).

clear_pending_area 프로시져를 통해 pending area로부터 모든 변경사항을 비우고, pending area를 비활성화시킬 수 있으며, 해당 프로시져 다음에는 반드시 다른 변경 시도를 위해서 pending area를 생성해야 함.

○ DBMS_SCHEDULER 패키지

PL/SQL 프로그램에서 호출될 수 있는 스케쥴링 프로시져 패키지. subprogram 중 window를 통해 플랜들을 각각 다른 시간에 자동으로 활성화되게 할 수 있음.

- CREATE_WINDOW Procedure 사용법:

DBMS_SCHEDULER.CREATE_WINDOW (

window_name IN VARCHAR2(윈도우 명),

resource_plan   IN VARCHAR2(리소스 플랜명),

start_date    IN TIMESTAMP WITH TIME ZONE DEFAULT NULL(시작시간),

repeat_interval IN VARCHAR2(반복 간격, Calendaring Syntax로 작성),

end_date      IN TIMESTAMP WITH TIME ZONE DEFAULT NULL(종료 시간),

duration   IN INTERVAL DAY TO SECOND(지속시간, numtodsinterval로 설정),

window_priority IN VARCHAR2(두 window가 겹칠 때 우선순위) DEFAULT 'LOW',

comments      IN VARCHAR2              DEFAULT NULL);

* Calendaring Syntax:

(http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm#BABFBCEF)

* numtodsinterval: numtodsinterval(n, 'interval unit') 형태로 사용

'interval unit'에는 'DAY', 'HOUR', 'MINUTE', 'SECOND' 가능

- DROP_WINDOW Procedure 사용법:

DBMS_SCHEDULER.DROP_WINDOW (

window_name IN VARCHAR2(윈도우 명),

force           IN BOOLEAN(윈도우 활성에 관계없이 실행 여부) DEFAULT FALSE);

* 윈도우 정보 보는 법

  - Viewing window and resource plan information:

select * from dba_schedular_windows;

□ 파티셔닝(Partitioning)

○ 개요

하나의 테이블이나 인덱스를 동일한 논리적 속성을 가진 여러 단위로 나누어 각각이 별도의 물리적 속성을 갖게 하는 것

○ 장점

- 데이터 접근시 스캔 범위를 줄여 성능 향상

- 데이터 훼손 가능성 저하 및 데이터 가용성 향상

- 각 파티션별 백업 및 복구작업 가능

- 테이블 파티션 단위로 디스크 I/O를 분산하여 부하 저하

○ 파티셔닝 종류(오라클 기준)

- 레인지 파티셔닝(Range Partitioning)

· 특정 컬럼 값을 기준으로 분할

· 주로 순차적인 데이터(historical data)를 관리하는 테이블에 사용

 ex) '가입계약관리' 테이블에서는 통상 최근 1~2년치만 접근

· 각 범위에 따라 데이터 분포도가 일정치 않음

· DDL 스크립트

CREATE TABLE TEST_TABLE

(I_YYYYMMDD VARCHAR2(8), I_CUSTOMER VARCHAR2(9), …… )

TABLESPACE TBS1

STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0)

PARTITION BY RANGE (I_YYYYMMDD)  <- 파티셔닝 컬럼(16개 까지 지정 가능)

(PARTITION PAR_200307 VALUES LESS THAN (‘20030801’),

PARTITION PAR_200308 VALUES LESS THAN (‘20030901’), …… )

- 해시 파티셔닝(Hash Partitioning)

· 특정 컬럼 값에 해시 함수를 적용해 분할(데이터 관리보다 성능향상에 중점)

· 분포도를 정의하기 어려운 테이블을 파티셔닝할 때 많이 사용(균등한 분포도를 가질 수 있도록 조율, 주로 2의 배수로 파티셔닝)

· 해시 파티셔닝으로 구분된 파티션들은 동일한 논리, 물리 속성을 가지며 파티션에 지정된 값들을 DMBS가 결정하기때문에 각 파티션에 어떤 값이 들어있는지 알 수 없음

· DDL 스크립트

CREATE TABLE TEST_TALBE

( SERIAL NUMBER, CODE VARCHAR2(4), ……)

TABLESPACE TBS1

STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0)

PARTITION BY HASH(SERIAL)

(PARTITION PAR_HASH_1 TABLESPACE TBS2,

PARTITION PAR_HASH_2 TABLESPACE TBS3, ……)

- 리스트 파티셔닝(List Partitioning)

· 특정 컬럼의 특정 값을 기준으로 파티셔닝

· 주로 이질적인 값이 많지 않고 분포도가 비슷하며 해당 컬럼의 조건이 많이 들어오는 경우에 사용

 ex) '서비스 계약' 테이블에서 서비스 가입 대리점을 A, 서비스 변경 대리점을 B라고 할 때, 모든 서비스 처리 데이터에는 대리점 타입이 두 가지 존재하고 대부분 조회 패턴에는 두 대리점을 구분하는 값이 주어지게 됨

· DDL 스크립트

CREATE TABLE SERVICE_CONTRACT

(I_YYYYMMDD VARCHAR2(8), I_CUSTOMER VARCHAR2(6),

I_DLR_IND VARCHAR2(2), I_DEALER VARCHAR2(6), …….)

TABLESPACE TBS1

STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0)

PARTITION BY LIST (I_DLR_IND)

(PARTITION PAR_A VALUES (‘A’), PARTITION PAR_S VALUES (‘S’))

- 레인지-해시 컴포지트 파티셔닝(Range-Hash Composite Partitioning)

· 레인지 방식으로 데이터를 파티셔닝하고 각 파티션 내에서 해시 방식으로 서브 파티셔닝을 적용하는 방식 → 서브 파티션이 독립된 세그먼트가 되는 것이 특징

· 레인지 파티셔닝으로 관리와 성능 이슈 향상, 해쉬 파티셔닝으로 데이터 균등 배치와 병렬화, 서브 파티션에 특정 테이블 스페이스 지정가능, 서브 파티션별 풀 스캔을 할 수 있어 스캔 범위 줄이는 효과

· DDL 스크립트

CREATE TABE TB_RANGE_HASH

(I_YYYYMMDD VARCHAR2(8), I_SERIAL NUMBER, SALE_PRICE NUMBER, ……)

TABLESPACE TBS1

STORAGE (INITIAL 2M NEXT 2M PCTINCREASE 0)

PARTITION BY RANGE (I_YYYYMMDD)

SUBPARTITION BY HASH (I_SERIAL)

(PARTITION SALES_1997 VALUES LESS THAN (‘19980101’)

(SUBPARTITION SALES_1997_Q1 TABLESPACE TBS2,

SUBPARTITION SALES_1997_Q2 TABLESPACE TBS3), ……)

<조건 제어문>

PL/SQL의 조건제어문에는 크게 IF와 CASE가 있다.

- IF문

IF var1 >10 THEN

do something

ELSIF var1 <= 10 AND var1 > 1 THEN

do something

ELSE

do something

END IF;

IF문은 위와 같이 사용하며, ELSE IF가 아니라 ELSIF인 것에 주의가 필요하다. 또한 AND, OR를 사용한다.

- CASE문

CASE var1 

WHEN var1 > 10 THEN

do something

WHEN var1 <= 10 AND var > 1 THEN

do something

ELSE

NULL;

END CASE;

CASE문은 위와 같이 사용하며, SQL의 CASE문과 유사한 사용법을 보인다. 

조건 분기 시 세부 로직에 NULL을 사용할 수 있는데, 아무것도 하지 않는다는 명시적 표현이다.

<반복 제어문>

반복제어문을 사용하는 방법에는 여러가지가 있는데, 가장 활용도가 높은 것이 FOR LOOP와 WHILE LOOP인 것 같다.

- FOR문

FOR var1 IN 1 .. 10 LOOP

do something

END LOOP;

FOR문의 위와 같이 사용하며 반복의 범위를 Start .. End의 형태로 표현할 수 있다.

- WHILE문

DECLARE

cnt NUMER := 0;

BEGIN

 WHILE var 1 < 10;

do something;

cnt : cnt + 1;

END LOOP;

<PL/SQL 구문의 구조>

PL/SQL은 다음과 같은 구조를 가지고 있다.

DECLARE (선언부)

프로그램에서 사용하는 객체 정의

BEGIN (실행부)

프로그램의 구체적인 실행 내용

EXCEPTION (예외처리부)

프로그램에서 예외 발생 시 처리할 내용

END;

/

위 구문에서 DECLARE 나 EXCEPTION 등은 로직에 따라서 생략도 가능하며, BEGIN과 END 사이에 다시 중첩으로 실행 블록을 넣는 것도 가능하다.

<변수>

변수의 선언은 var1 NUMBER; 와 같이 앞쪽에 변수명을 적고 뒤쪽에 데이터 타입을 적는다. 선언 시 var1 NUMBER DEFAULT 20;과 같이 기본값을 설정할 수 있다.

변수의 대입은 var1 := 10; 과 같이 := 연산자를 사용해야 한다.

변수의 타입은 크게 스칼라, 콤포지트, 참조 3가지로 나눌 수 있다.

 - 스칼라 타입 : 오라클에 의해 미리 정의된 데이터 타입이다. NUMBER, CHAR, VARCHAR2, DATE, BOOLEAN이 있다.

 - 콤포지트 타입 : 사용자에 의해 정의되어야 하는 타입이다. RECORD, TABLE, Nested TABLE, VARRAY가 있다.

 - 참조 타입 : 사용자가 정의하는 타입으로 처리 대상 데이터를 참조할 수 있는 특징이 있다. REF CURSOR가 있다.

이 외에 특수한 속성으로 %TYPE과 %ROWTYPE이 있다. 이 둘은 미리 정의된 변수의 데이터 타입을 참조할 때 사용한다.

먼저, %TYPE은 특정 컬럼의 데이터 타입을 참조할 때 쓰인다.

DECLARE

var1 dept.deptno%TYPE

BEGIN

SELECT deptno INTO var1 FROM dept WHERE location='KOREA'

END;

위와 같은 프로그램에서는 var1이 dept 테이블의 deptno 컬럼의 타입을 따라가는 것이다.

다음으로 %ROWTYPE은 특정 행 구조를 참조할 때 쓰인다.

DECLARE

row1 dept%ROWTYPE

BEGIN

SELECT deptno, dname, location INTO row1 FROM dept WHERE deptno=10;

END;

위와 같은 프로그램에서는 row1이 dept테이블의 특정 row의 구조(deptno, dname, location)의 데이터 타입을 모두 가져간다.

PL/SQL은 오라클에서 SQL을 확장하여 만든 프로그래밍 언어로 SQL에는 없는 반복문이나 분기문 등을 사용할 수 있다. 

시스템 운영을 하면서 자질구레한 데이터 조작 업무가 많은데, SQL로만 하기에는 너무 공수가 많이 들고 

그렇다고 JAVA나 Pro* C로 짜자니 과한 것 같은 경우가 있다. 이럴 때 적합한 것이 PL/SQL이지 않을까 싶어 기초정도만 배워두려 한다.

사내 정보자료실에서 <전문가를 위한 오라클 PL/SQL 입문> (저: 어시스트 교육부)라는 책을 빌렸다. 

해쉬 인덱스는 해쉬 함수(Hash function)을 기반으로 인덱스의 엔트리를 구성한다. 인덱스 엔트리는 아래 그림과 같이 버켓(bucket)이라고 불리는 공간에 저장되며 각 버켓에 해쉬 함수에 의해 생성된 버켓 번호가 부여된다. 바로 이 버켓들의 집합이 해쉬 인덱스인 것이다. 

해쉬 인덱스를 생성하면 인덱스로 구성하려는 필드 값들을 해쉬 함수로부터 버켓 번호를 부여받아 해당 버켓에 엔트리를 저장한다. 해쉬 인덱스를 사용하여 검색을 할 때도 인덱스를 구성한 필드 값을 해쉬 함수에 적용하여 버켓 번호를 알아낸 뒤 해당 버켓 번호를 가진 버켓 엔트리 중에서 추출하는 것이다.

해쉬 함수는 대규모 데이터 키 집합에 대해서 적은 범위의 데이터 집합으로 대응시키는 함수를 말한다. 대응되는 함수 값(=해쉬 값)은 입력 값의 분포와 상관 없이 균등하게 분포되어야 한다. 가장 간단한 예로는 모듈러 연산을 들 수 있겠다. 예를 들어 해쉬함수 H(X) = X % 3이라고 정의한다면, 인덱스를 구성하고자 하는 컬럼의 값을 3으로 나누어 나머지에 해당하는 값을 버켓 번호로 지정하는 것이다.(아래 예시 참조)

해쉬 함수는 인덱스 엔트리들 각 버켓에 균등하게 할당되도록 짜여져 있겠지만, 입력 값에 따라서 완전히 동일할 수는 없기 때문에 특정 버켓이 오버플로가 발생할 수 있다. 이 경우 버켓의 개수를 늘리고 해쉬 함수를 변경 하는 등의 인덱스 재구성 작업이 필요하다. 

해쉬 인덱스는 B+트리 인덱스에 비해 등동조건 검색(ex select * from 테이블 where A컬럼 ='1001')에 유리하다. B+트리 인덱스는 트리의 높이만큼 검색을 해야하지만, 해쉬 인덱스는 해당 버켓을 찾는데 한 번의 검색이면 되기 때문이다. 하지만 데이터가 순차적으로 저장되지 않고 각 버켓에 나누어 저장되기 때문에 범위조건 질의(ex select * from 테이블 where A컬럼 between '1001' and '2001')에는 효과가 없다.

아래와 같이 두 개 이상의 필드를 이용해서 인덱스를 구성하는 것을 복합 인덱스(Composite index)라고 한다. 인덱스는 검색키와 주소의 쌍으로 구분되며 이를 인덱스 엔트리(Index Entry)라 부르는데, 복합인덱스는 검색키 N개와 주소의 쌍이 인덱스 엔트리가 되는 것이다.

이 때, 인덱스는 대소관계에 의해 검색키1을 우선 정렬하고 검색키1에 대해여 검색키2를 정렬하는 방식으로 구성된다. 예를 들어, 아래 좌측과 같은 테이블이 있고 A와 B컬럼을 복합 인덱스로 하면,  아래 우측과 같이 인덱스가 만들어 지는 것이다.

A컬럼과 B컬럼의 인덱스가 각각 만들어져 있다면,  select * from 테이블 where A컬럼='1001' and B컬럼='2' 라는 질의를 날렸을 때, A컬럼='1001'의 조건으로 참조된 결과와 B컬럼='2'의 조건으로 참조된 결과를 비교하여 중첩된 레코드를 찾을 것이다. 하지만 복합 인덱스로 구성했을 경우에는 두 조건을 만족하는 레코드를 하나의 인덱스로 처리할 수 있어서 효율적이다. 

또한 select B컬럼 값 from 테이블 where A컬럼='1001' 처럼 원본 데이터에 접근하지 않고도 복합 인덱스내에서 바로 질의 처리가 가능해지는 경우도 있다.

단, 필드의 순서에 따라 인덱스 효과가 달라지기 때문에 주의를 요한다. 

예를 들어,

select * from 테이블 where  A컬럼 = '1001' and B컬럼 > '2' 와 

select * from 테이블 where  A컬럼 > '1001' and B컬럼 = '2'라는 질의를 날린다고 해보자. 

첫 번째 질의는 A컬럼 키 1001을 찾은 뒤 제한 된 B 컬럼의 검색키를 검색하면되지만, 두 번째 질의는 A컬럼 > '1001'을 만족하는 검색키를 모두 검색하면서 B컬럼='2' 조건을 조회하기 때문에 효율적으로 동작하지 않는다. 따라서 두번째 질의를 잘 처리하기 위해서는 (B컬럼, A컬럼)의 순서로 구성된 필드 집합으로 복합 인덱스를 생성해야 한다.

결국 복합 인덱스를 만들 때는 자주 사용되는 질의 유형을 사전에 분석하여 결정해야 하는 것이다.

DB에서 데이터에 대한 접근은 결국 디스크 I/O라는 뜻이다. 디스크를 헤드를 위치시키고, 디스크를 회전하고, 데이터를 전송하고... CPU만큼이나 빠르게 작동하지 않는다. 때문에 검색의 효율을 높이기 위해서 인덱스를 설정한다. 이 인덱스의 방식 중 가장 널리 사용되는 방식이 B+트리 인덱스이다.

차수가 N인 B+트리의 노드 구조는 위와 같이 N개의 포인터와 N-1개의 검색키의 집합으로 구성되어 있다.  

키는 Key i-1 < Key i < Key i+1 ... 의 관계를 가지고 있으며, Key i와 Key i+1 사이에 있는 포인터인 P i+1이 가리키는 하위 노드 집합에는 Key i보다는 크고 Key i+1보다는 작거나 같은 검색키들이 위치해 있다. 

레코드가 10만 개인 테이블에서 특정 레코드를 검색한다고 했을 때, 인덱스가 구성되어 있지 않다면 최악의 경우 10만 건을 전부 스캔해야 한다. 반면, 차수가 10인 B+트리로 구성했을 경우 아래와 같이 구조가 형성되기 때문에 최악의 경우에도 10개의 키를 4번 검색하는 것만으로 원하는 레코드를 찾을 수 있다. 

대략적으로 시간복잡도가, O(N)에서 O(logN)으로 향상되는 셈이다.