새로쓴 대용량 데이터베이스 솔루션 - 제 1부 액세스 영향요소의 이해1

제 1장 데이터 저장구조와 특징

 

1.1 table, index 분리형

- 관계형 DB 이전에는 key의 역할이 중요

key를 Access 할 때, data의 위치를 바로 파악할 수 있으면? -> data와 key를 함께 둘 필요가 X

하지만, key에서 다시 data를 찾아가는 번거로움은 감수해야 함

대용량 관계형 DB의 가장 일반적인 구조

 

1.1.1 분리형 테이블의 구조

저장될 data가 들어왔을 때, block 하나가 선택되어 data 값과 무관하게 저장됨

* 저장시 부하가 낮지만, 액세스 시 부담이 증가

block 하단 ‘free Space’는 row length 변화시 사용하는 공간(table 정의시에 파라메타(PCTFREE)로 저장 가능

 

* Tablespace : DB 내 실제 데이터를 저장하는 물리적 공간이며 세그먼트로 관리되는 모든 DBMS에 대해 저장소를 할당

- 논리적인 DB 저장공간이며 물리적 datafile로 구성됨 -> 용도별 분류(segment) -> 각 object들이 용도에 맞게 들어감

 

파티션된 table의 각 파티션이 서로 다른 tablespace에 존재하는 경우 여러 tablespace에 걸쳐 저장될 수 있으나 개별 파티션, 파티션 되지 않는 table들은 반드시 1개의 tablespace에만 존재해야 한다.

-> 상대적인 번호(고유번호가 아닌)를 붙이더라도 한 개의 Object 내에서는 식별 가능 = RowID(로우식별자)를 보다 짧게 구성할 수 있음을 의미

* RowID = Object No. + datafile No. + block No. + slot No.

-> 데이터 파일의 절대 번호는 없으나 index 내의 RowID에는 방(slot) 번호가 있음 = block 내 row 위치 이동해도 RowID의 값은 변하지 않음

 

자유로운 Row의 위치이동 -> 계속적인 이동 발생 -> Fragmentation 발생 -> Condensing 작업

IF) 여유공간이 작으면 빈번한 Condensing 발생 -> 부하의 원인이 되므로

SO) Row 길이가 증가될 황률은 높지 않아도 수정이 빈번하게 일어난다면 충분한 여유공간을 확보(PCTFREE)

BUT) Condensing에도 불구하고 row length가 길어지면 –> 여유공간 부족 -> Using another block (RowID 변경을 의미)

=> 과거의 주소에 내가 옮긴 주소를 넣어두는 방법으로 해결 (RowID 변경을 하지 않지만 여러 block을 읽는 overhead는 감수 해야함)

 

Row의 Migration (로우의 이주) : 로우를 삭제하고 다시 생성해야만 치유 가능

Row의 Chain : 여러 block에 걸쳐 data가 존재

 

 

1.1.2 Clustering Factor

- (index의 컬럼값으로 정렬된 index row의 순서)와 (table에 있는 로우의 위치) 가 얼마나 비슷한지를 나타내는 정도

- (13 page 다시 이해 필요한 페이지)

clustering factor 향상을 위해 테이블을 주기적으로 생성할 수 있음 (부하가 적지는 않으나, 테이블 재생성을 통해 저장순서/생성순서를 일치시킴)

 

 

1.1.3 분리형 테이블의 액세스 영향 요소

가) 넓은 범위의 액세스 처리에 대한 대처 방안

(1) 소형 table

- 보다 적극적인 방법을 적용할 수 있다. 중대한 액세스 형태에 속한다고는 보기 힘드므로 특별한 경우가 아니면 조치를 하지 않아도 무방

 

(2) 중형 table

- data insert 시에 부담이 상대적으로 대형보다 적음(critical factor가 되기 힘들다)

 

(3) 대형 table

- 단순 저장 목적 (log 정보 관리 table) : 자주 사용, 접근되어지지 않으며 특별한 경우에 특정 액세스 형태로 읽거나 대량 스캔 형태로 사용

- 랜덤 Access, 다양하지 않은 Access form (고객 table) : 한번에 대량의 데이터가 들어오는 경우가 적고 범위 처리를 자주 하지 않음

- 지속적으로 data 증가 (매출 table) : 관리 측면의 부담이 크다면 파티션을 적용해야 함. 잦은 대량 범위 스캔 발생. 다양한 액세스 형태를 가짐

 

나) Improve Clustering Factor Strategy

분리형 구조에서 정해지지 않는 순서로 데이터가 저장 된다? => 마음대로 저장이 가능하다? => 사용자가 의도적으로 유리한 형태로 저장 가능

how?) 강제로 제어할 수는 없다 => table을 주기적으로 재생성하는 방법으로 사용

 

목적 – 일반적으로 수행속도를 향상 시키기 위함

- Chain 감소, block 내 data 저장율을 높여 불필요한 I/O를 제거

- 대청소와 같은 개념으로도 사용 -> table 재생성 시 index도 재생성 되며 branch dept도 정리됨

 -> table 재생성시 새로 들어올 data가 access에 유리한 형태로 저장되어야 한다.

 -> clustering을 적용하여 clustering factor를 향상하는 것보다는 못하지만 투자대비 효과가 크다.

 

※ 주의점

- data저장 시 관련 index를 모두 제거하거나 disable 해야함

- 하지 않으면? index를 생성한 채로 대량의 데이터를 저장하면 테이블의 저장 속도가 크게 저하, index에 다량의 분할 발생

–> index 저장 밀도가 매우 나빠짐 -> index 효율 크게 저하

- table 생성구문에 습관적으로 기본키 제약조건이 지정되어 있어 테이블 생성시 기본키가 같이 생성되는 경우가 빈번하므로 조심해야 함.

 

 

1.2 Index-Organized Table

- Balnaced-Tree(B-Tree) index= ‘index column + RowID’ 으로 구성

- 트리구조의 최상위 하나의 ‘루트 노드’ & 가장 하위 자식 ‘리프 노드’ & 루트도 리프토 아닌 중간 노드 ‘브랜치 노드’

- 인덱스와 실제 데이터 저장된 데이터는 따로 관리되며 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾기 위한 주소 값을 지님

- B-Tree 의 구조상 인덱스를 경유하여 테이블 액세스 할 경우 index segment Access -> RowID를 이용, data segment Access 의 2번 액세스

- 인덱스와 다른 일반 컬럼들이 모두 같은 위치에 저장되는 형태 (ISAM 이나 VSAM 파일과 유사한 구조)

- 장점 : 저장구조의 절약, 액세스 효율 개선

 

 

1.2.1 일체형/분리형 테이블 비교

 

 

1.2.2 일체형 테이블 구조 및 특징

- RowID를 가지고 있지 않는다.

- 추가적인 인덱스(Secondary index)를 생성할 경우 기본키를 사용해야함

Row Header

Key Column

Non key Column

 

장점 : 인덱스를 경유하지 않으므로 논리적 액세스가 한 번 줄어듬 -> 산술적인 차이 (실제 인덱스 스캔하는 부분은 부담이 적으므로 큰 효과는 X)

인덱스 로우마다 임의의 위치에 흩어져 있는 테이블 액세스 횟수 감소 -> 넓은 범위 처리에서 큰 효과 발생

 

단점 : (일반컬럼을 모두 보유하므로) Row length가 증가할 확률이 높다 / Row 마다 일정한 길이로 존재하지 않는다 => row overflow 발생

(적절한 parameter 지정으로 단점을 많이 해소할 수 있음)

 

 

1.2.3 논리적 ROWID와 물리적 주소(Physical Guess)

- 키 값이 분리되는 경우 2개 node로 분할 => 영구적 물리주소를 가질 수 없는 논리적 한계를 뜻함

- 자신이 곧 table이므로 분할/병합 수행 시 ROWID가 변할 가능성이 크다

- 거의 모든 DB는 병합 수행 X (분할 보다 더 많은 데이터 이동이 발생) -> 기존의 index 구조를 유지 => rebuild의 필요중요성

- 일체형 table의 효과적인 활용을 위해 다양한 검색 pattern 지원 -> 추가 index 생성의 절대적인 필요성 -> 기본키 대신 논리적 ROWID 도입

 

- 일체형 table의 ROWID 값은 일반적인 ROWID와 유사한 구조&역할을 수행함

- But, 키분할에 의해 data block 위치가 달라져도 변경되지 않음 => 완벽한 row의 물리적 주소로 보기 어려움

- 따라서, row가 존재할 가능성이 높은 주소라고 하며 Physical Guess(또는 Guess)라고 함

- data Access 할 경우 Guess가 부정확 하다고 판단된다면 PK로 다시 Access함

 

* 일체형 table 구조를 적용할 수 있는 table

 (1) 전자 카탈로그나 키워드 검색 테이블

 (2) 코드성 테이블

 (3) 색인 테이블

 (4) 공간(Spatial) 정보 관리용 테이블

 (5) 대부분 기본키로 검색되는 테이블

 (6) OLAP의 디멘젼 테이블

 (7) 로우 길이 짧고 트랜잭션이 빈번하게 발생되지 않는 테이블

 

* 추가 인덱스 사용시 액세스 수행 절차

 

 

1.2.4 Overflow Area

- 각 컬럼 간 활용도, 중요도, 서로간 결합도 등의 차이를 인지하여 전략적인 선택이 필요함

- 일체형 테이블 생성시 부여한 ‘이동 임계치 (PCTTHRESHOLD)’에 도달하면 ‘INCLUDING’ 절에서 지정한 컬럼들은 모두 오버플로우 영역에 저장됨 (?)

- 일체형 구조 본체는 인덱스 세그먼트에 저장

- 오버플로우 영역은 테이블 세그먼트에 생성

- 각 영역은 별도의 저장공간을 가질 수 있으므로 원하는 tablespace를 별도로 지정할 수 있다.

 

 

1.2.5 일체형 테이블의 생성

CREATE TABLE documents (
			doc_id varchar2(5),
			title_name varchar2(50),
			author varchar2(20),
			contents varchar2(2048),
			status varchar2(2),
CONSTRAINT pk_dociot PRIMARY KEY (doc_id) )
ORGANIZATION INDEX ------------------------(a)
TABLESPACE data01 ------------------------(b)
PCTTHRESHOLD 20 ------------------------(c)
INCLUDING contents ------------------------(d)
OVERFLOW TABLESPACE idx01 ; ------------------------(e)

(a) ORGANIZATION INDEX : 일체형 테이블 생성 정의 키워드

(b) TABLESPACE : index area가 저장될 테이블 스페이스 지정. STORAGE 파라메터도 사용 가능

(c) PCTTHRESHOLD : index block 내에 예약된 공간 백분율 지정.

  단일 로우 크기 > (PCTTHRESHOLD/100)*DB_BLOCK_SIZE => 범위 내 컬럼은 인덱스 영역에 남기고

  나머지 컬럼은 모두 오버플로우 영역으로 이동시킴

  INCLUDING 절에 지정된 열 이후 컬럼은 모두 오버플로우 영역으로 이동

  OVERFLOW를 지정하지 않는 경우, 임계값(Threshold)을 초과한 로우는 모두 거부(reject)됨

(d) INCLUDING : 테이블 행을 인덱스와 오버플로우 영역으로 나눌 컬럼을 지정하는 절

  로우가 최초로 입력될 때 적용되며 그 이후는 PCTTHRESHOLD에 의해 인덱스와 오버플로우 영역이 나누어진다.

(e) OVERFLOW TABLESPACE : 지정 임계값을 초과한 로우가 위치할 tablespace, storage block 활용 파라메터를 지정하는 절

 

정리

일체형 테이블에서 체인은 일반 테이블에서와는 조금 다른 의미를 가지고 있다. 일체형에서는 체인을 결정할 수 있으며 결과에 따라 액세스 성능이 달라질 수 있다. 일체형에서 로우가 체인이 되었다라는 의미는 리프노드의 저장밀도를 높이기 위한 전략이다. 즉, 인덱스영역과 오버플로우영역을 전략적으로 구분하여 액세스 효율성을 향상시킬 수 있다. 일체형 테이블 구조를 사용하기 위해서 현재 및 향후 액세스 패턴을 분석하여 중요도와 친밀도를 바탕으로 미래에 대한 어느정도 수준까지의 예측이 필요하며 기준 컬럼을 지정할 수 있다. 이 예측이 어렵다면 일체형 구조를 선택하지 않는 것이 현명하다.

 

 

1.3 clustering table

- 분리형 : 대량의 data 범위 처리시 많은 랜덤이 발생함

- 일체형 : 특정 Access 에서 랜덤이 없어지지만 다양한 Access 형태에서 더 큰 부담이 생김

 

 

1.3.1 Clustering Table의 개념

- 하나의 object로 table의 상위개념

- 정해진 컬럼값을 기준으로 동일한 값을 가진 하나 이상의 테이블의 로우를 같은 장소에 저장하는 물리적인 기법.

  -> 그것이 하나의 테이블을 대상으로 모아 두었다면 해당 테이블에 대한 대량의 범위처리 운송단가를 줄일 수 있고

      하나 이상의 테이블을 대상으로 모아 두었다면 조인의 연결에 의한 운송단가를 줄 일 수 있다는 의미

- 테이블은 클러스터 오브젝트 내에 생성되는 것으로 테이블의 상위개념.(테이블이 없는 클러스터는 무의미)

- 인덱스 클러스터와 해쉬함수 클러스터가 있고 인덱스 클러스터에는 단일테이블/다중테이블 클러스터링이 있음

- 클러스터를 정의해야 클러스터에 테이블 생성 할 수 있다.

- 클러스터에 데이터를 저장하기 위해서는 테이블 생성은 물론 클러스터 인덱스도 생성되어야 한다.

  ->일반 인덱스와의 차이

                      - 클러스터링되어 있다는 요소만 더 가지고 있음

                      - 규칙기준 옵티마이저시에 일반 인덱스보다 높은 랭킹을 부여 받음

                      - 비용기준 옵티마이저에서는 보다 적은 비용으로 취급되는 역할

 

 

1.3.2 단일테이블 클러스터링

 

- 넓은 범위 데이터를 동시에 액세스하고자 할 때 주로 사용

- 한 블록에 최대 2개의 단위 클러스터만 존재할 수 있도록 SIZE를 지정

- 그림에는 각 로우마다 단위 클러스터가 저장되어 있으나 실제로는 단 한번 block header에 물리적으로 한번만 저장됨

- 클러스터 값이 수정(ROWID의 변경) 된다면? -> 치명적인 문제가 발생할 수 있다.

* ROWID를 그대로 유지하기 위해 최초 로우 위치에 이주한 ROWID를 남겨두고 본체는 모두 이동시킴

- 대량 범위를 처리하더라도 랜덤 액세스를 현격하게 줄여 스캔을 극대화 함으로 일반 인덱스를 사용한 것 보다 약 5-8배 효율을 기대할 수 있음

- 분포도가 좁다는 것은 같은 단위 클러스터에 속한 로우수가 적음을 의미하므로 클러스터 인덱스 로우마다 스캔방식으로 액세스 할 테이블의 로우 수는 오히려 줄어든다. 결국 일반 인덱스를 사용한 것 보다 좋아지는 것이 아님

 

 

1.3.3 다중테이블 클러스터링

 

- 단위 클러스터에 두 개 이상의 테이블을 함께 저장하는 것

- block header에 클러스터 키 ID와 클러스터 키 정보를 가짐

- 어떤 테이블을 생성할 때 임의의 위치에 있더라도 아무 영향을 받지 않음 (지역적 투명성, Location Transparency)

- 두 개의 테이블이 조인할 때 최대의 효율을 발휘함

SELECT .....
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND d.deptno = '111';
--------------------Execution Plan----------------------
SELECT STATEMENT
	NESTED LOOPS
		TABLE ACCESS (CLUSTER) OF 'DEPT'
			INDEX (UNIQUE SCAN) OF 'DEPT_CLUSTER_IDX' (CLUSTER)
TABLE ACCESS (CLUSTER) OF 'EMP'

- 클러스터 인덱스를 경유하여 클러스터를 읽고 같은 클러스터 내에서 조인 수행. => 조인 대상이 같은 클러스터 내에 있다 = 추가 액세스 X

- 정규화 이전에는 한 레코드 안에 있어서 같이 액세스 가능한 것을 1정규화 작업에 의해 분할되어 자식 테이블은 부모 테이블과의 연결을 위해 상위 키를 각 로우에 보관해야 하고 분할된 테이블을 별도 생성하면 임의의 위치에 생성되어 다양한 방식에 조인이 발생 => 일일이 조인을 해야하는 부담

  => 다중테이블 클러스터링으로 몇가지 해소 가능

- 키 값이 중복해서 저장되지 않고 한 레코드를 액세스 하듯이 효율적으로 조인을 할 수 있음.

  => 각 테이블의 독립성이 보장되어 특정 조건을 만족하는 로우만 읽기 가능

- 유효한 배열만 로우를 생성하므로 저장공간을 보다 절약할 수도 있음

 

※ 주의

- 특수한 관계의 연결에는 큰 도움이 되지만 각 테이블의 유연성은 훼손됨

  => 불가분 관계(대부분 부모,자식을 같이 액세스 하는 경우)가 아니면 자유로운 테이블로 존재하며 활동할 수 있도록 독립성을 부여해야함.

 

 

1.3.4 클러스터링 테이블의 비용

- 클러스터링은 관계형 데이터베이스의 최대 약점인 넓은 범위의 처리를 해결하며 필연적으로 발생하는 조인의 효율성을 크게 높여줌

- but, 비용을 고려하지 않은 효과는 이상에 불과하며 그에 상응하는 대가는 항상 따라야 한다.

  * 우리에게 유용한 부분을 얼마나 해결해 줄 수 있는가

  * 그 대가에 대한 부담을 얼마나 감수 할 수 있는가

- 최선의 결정을 위해 *해결해야할 과제, *보유자원을 충분히 분석하고 다양한 해결방안을 검토하는 것이 먼저

- 클러스터링은 단지 검색의 효율을 높여줄 뿐 입력, 수정, 삭제에는 추가적인 부하가 발생되며 부정/긍정적인 요소의 조화가 중요함

 

가) INSERT 부하

- 정해진 위치를 찾아 저장되어야 하므로 일반 테이블에 비해 추가적인 부하가 발생

- 클러스터링 테이블은 각 로우가 가진 클러스터 키값에 따라 저장위치가 달라짐 -> 프리리스트 요구 횟수 증가

ex) 일반 테이블은 할당받은 블록에 100개의 로우를 한 번에 저장 가능 but, 클러스터링 테이블은 최악에 경우 100개 블록에 따로 저장될 수 있음

- 부하의 정도를 알아보기 위한 방법 -> test용 클러스터링 테이블 생성 -> 데이터 일정량 입력

ex) INSERT INTO ..... SELECT .... WHERE ROWNUM <= 100 처럼 사용하여 일정량을 증가시키며 수행속도 확인

- 일반 인덱스와 적절한 역할 분담을 고려하여 전략적인 인덱스를 구성했다면 인덱스 수는 감소하므로 클러스터링을 적용 한 뒤에도 부하 크게 증가X

 

나) UPDATE 부하

- 클러스터키 컬럼의 값을 수정시키는 경우, 그 외의 일반 컬럼을 수정하는 경우 2가지가 있음

- 클러스터링 테이블 로우는 클러스터키 컬럼값에 따라 저장되어 있으므로 컬럼값이 변함 => 다른 단위 클러스터로 이동해야 함을 의미

  -> 변경된 값 단위 클러스터로 이동 시킨 후, ROWID를 변경시키지 않기 위해 원래 위치에 이주한 위치정보를 남겨둠

- 클러스터키 컬럼이 아닌 일반 컬럼을 수정하는 경우는 일반 테이블 수정과 동일하다

 

다) DELETE 부하

- 클러스터링테이블에 로우를 삭제하더라도 클러스터 인덱스는 그대로 존재하기 때문에 추가적인 처리가 발생하지 않는다.

- 클러스터링 테이블 DROP시에 내부적으로는 롤백 세그먼트를 받아 데이타를 저장하고 DELETE를 한다.

  => 한 단위 클러스터에 2개의 테이블이 저장되어있다면 단위클러스터의 일부분을 삭제하겠다는 것이 단위 클러스터를 삭제 하려는 것이 아니므로 클러스터 입장에서는 DELETE 할 수 밖에 없다. 이런 작업이 많은 부하를 줄 뿐 아니라 큰 롤백 세그먼트가 필요하게 된다.