database/oracle

테이블과 인덱스의 분리형

아이짱구 2017. 2. 17. 10:47

1. 분리형 테이블의 구조

테이블과 인덱스가 별도로 분리되어 있는 구조로 관계형 데이터베이스의 가장 일반적인 데이터 저장 형식.


분리형 테이블의 구조

자료가 입력되면 DBMS는 FREE LIST에서 DB의 여러 블록중 저장 가능한 블록을 확인 후 저장한다. 저장 시 블록내  이어진 공간이 없다면 전체 블록의 로우 위치를 재배치(Condensing)후 저장(반드시 한 조각이 되어야 함)하며, FREE SPACE는 이미 들어가 있는 로우들의 길이에 변화가 생겼을때 사용하는 여유 공간(UPDATE 등) - pctfree로 지정한다.


테이블스페이스(Tablespace)

데이터가 저장되는 논리적인 저장공간

물리적인 데이터 파일(Data file)로 구성

세그먼트(Segment)

테이블스페이스를 용도별로 나눔(데이터 오브젝트)

파티션(Partition)이 발생한 테이블이나 인덱스는 각각의 파티션이 단위 오브젝트가 됨

파티션된 테이블의 각 파티션이 서로 다른 테이블스페이스에 존재하는 경우는 여러 테이블스페이스에 걸쳐 저장

개별 파티션이나 파티션되지 않는 테이블들은 반드시 하나의 테이블스페이스에 존재

ROWID

오브젝트번호 + 데이터파일번호(테이블스페이스당 일련번호) + 블록번호 + 슬롯번호로 구성 

인덱스에 존재(테이블에 존재하지 않음) 

* ROWID의 오브젝트 번호와 데이터파일번호를 통해 물리적인 저장위치를 찾아서 거기에 있는 블록번호를 찾아가면 슬롯

* 블록 내에서 로우의 위치가 이동하더라고 ROWID는 결코 변하는 않음(슬롯은 로우(자료)의 위치를 가지고 있음)

로우가 한 조각으로 저장될 수 있는 위치로 이동 했을때 해당 슬롯에 있는 위치 값은 새로운 위치 값으로 변경

Oracle Physical Rowid

000000 + FFF + BBBBBB + RRR : Data Obect(6) + Relative File(3) + Block(6) + Row(3) : 16자리(저장 시 10자리)

Data Object: Database Segment 식별정보

Relative File: Tablespace에 상대적 Data file 번호

Block: Row를 포함하는 Data Bolck 번호

Row: Block에서 Row의 Slot

SQL Server의 Rid

Page Address(4) + File ID(2) + Slot Number(2) : 8 Byte

응축(Condensing)작업

이동이 계속적으로 발생하면 결국에는 사용할 수 없는 수 많은 작은 조각(Fragmentation)들이 발생

이어진 조각이 없어 저장이 불가능하게 될 때 자동으로 블록의 로우들을 재구성 작업

만약 여유공간(FREESPACE: PCTFREE값)이 너무 적게 지정되었다면 지나치게 빈번한 응축작업이 발생하여 부하의 원인이 될 수 있음

수정이 빈번하게 발생 할 가능성이 높다면 충분하게 여유공간(PCTFREE)을 지정해 주는 것이 유리

Row의 이주(Migration)

Row의 블록 이동 발생시 기존 ROWID에 새로운 ROWID를 넣어 ROWID를 찾을 수 있게함

인덱스의 ROWID를 변경시키지 않는 대신에 액세스를 할때 여러 블록을 읽어야 하는 오버헤드 발생

이주 현상은 Row나 Table을 삭제하고 Table을 재생성 해야만 재 사용 가능

체인(Chain)

여러 블록에 걸쳐 데이터가 존재한다는 점에서 이주와 유사

특정 로우의 길이가 한 블록의 크기를 넘을때 필요한 공간만큼 블록을 연결해서 저장하는 것

일반적으로 블록 내에 존재하는 로우는 가변 길이로 존재


2. 클러스터링 팩터(Clustering Factor)

 

클러스터링 팩터(Clustering Factor)


인덱스의 컬럼 값으로 정렬되어 있는 인덱스 Row의 순서와 테이블에 저장되어 있는 데이터 Row가 얼마나 비슷한 순서로 저장되어 있느냐에 대한 정도를 나타내는 것

INDEX1: 5 Row를 불러오는데 2블럭 읽음 - 클러스터링 팩터가 좋음

INDEX2: 3 Row를 불러오는데 3블럭 읽음 - INDEX1에 비해 클러스터링 팩터가 나쁨

분리형 테이블 구조의 최대 특징인 데이터 값을 임의의 위치에 저장한 경우 데이터 조회 작업시  최소한 한 개 이상의 블록을 엑세스 할 수 밖에 없음(Row를 액세스하지만 실제 DBMS는 블록을 액세스 후 Row를 찾음)

인덱스는 인덱스 컬럼과 ROWID로 정렬

ROWID로 정렬되었다는 것은 물리적인 데이터 파일의 블록으로 정렬되고, 거기에서 다시 슬롯번호로 정렬되었다는 것을 뜻함

클러스터링 팩터가 좋은 인덱스로 액세스하면 많은 Row를 액세스 하더라도 보다 적은 블록을 액세스하게 되어 효율적임

클러스터링 팩터를 높이는 방법

원하는 형태로 저장(저장 시 과도한 비용을 발생할 수 있음)

주기적 테이블을 재생성

저장 할 컬럼 순서를 전략차원에서 판단하여 결정


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


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

  • 먼저 가장 중요한 액세스 형태를 선정
  • 컬럼이 가진 처리 범위에 대한 문제를 해결할 수있는 방법을 찾아야 함
  • 특정한 모양으로 저장
  • 특정 액세스 형태에서만 효율을 얻을 수 있음
  • 데이터 등록 시의 부하 부담을 무시할 수 없다면 특정 위치에 저장을 하는 것은 다시 한번 검토
  • 지속적으로 증가하는 데이터
  • 관리적인 부담이 크다면 파티션을 적용
  • 인덱스를 전략적으로 구성하고 SQL의 실행계획을 최적화

클러스터링 팩터 향상전략

  • 분리형 구조는 아무렇게나 저장해도 괞찮다는 의미이지만 반대로 의도적으로 우리에게 유리한 형태로 저장하는 것도 가능
  • 테이블을 재생성
  • 주기적으로 재생성하여 이미 저장되어 있는 데이터의 응집도를 높여 주는 것
  • 체인을 감소시키고 블록 내 데이터의 저장율을 높여 불필요한 I/O를 줄이기 위해 사용
  • 가지 깊이(Branch Depth)가 정리되는 효과
  • 테이블에 데이터를 저장할 때 관련된 인덱스를 모두 제거하거나 비활성
  • 인덱스를 생성한 채로 대량의 데이터를 저장하면 테이블의 저장 속도가 크게 저하될 뿐만 아니라 인덱스에 많은 분할이 발생하여 인덱스 저장 밀도가 매우 나빠짐
  • 테이블을 생성하는 구문에 기본키 제약조건을 지정하면, 테이블 생성시 기본키 인덱스 생성으로 비효율(인덱스 저장 밀도가 나빠짐)

기타

각 DBMS별, 버젼별 옵션 등 다양 - 9i 이후 ASSM 도입(PCTUSED, FREELIST를 명시적으로 지정하지 않고 자동관리)

DBMS별 테이블 생성시 옵션들이 다양하며 그 옵션은 테이블의 용도(업무형태)에 따라 다양하게 구성될 수 있다.


출처: 두리누리