7. 테이블의 물리적 설계

개념적 데이터 모델링과 논리적 데이터 모델링을 통해 만들어진 테이블을 어떻게 설계 하는지에 대한 물리적 설계 방법이다.

 

7.1 테이블 설계시 주의사항

  1. 블록 영역을 위해 initial , Next parameter를 충분히 고려하라.

  2. 저장 될 tablespace를 반드시 지정한다.

  3. data의 실시간 복구가 요구되지 않는 경우 LOGGING절을 사용하지 마라.

  4. 필요한 경우 CACHE절을 사용하라.

 

 3) data의 실시간 복구가 요구되지 않는 경우 LOGGING절을 사용하지 마라.

  이 말은 우리가 DML문(update, delete, insert)를 실행하게 되면 변경 전 데이터 + 변경 후의 데이터가 SGA의 Redo log buffer cache영역에 저장

  되어 있다. 이는 뜻하지 않는 상황에 대비하여 데이터의 손실을 방지 하기 위함이다. 또한 데이터를 백업해주는 공간 이기도 하다.

  이처럼 개발자들이 생성하는 모든 테이블들은 위와 같은 싸이클에 의해 작업 내용들이 데이터베이스 내에 저장되는데

  이것을 테이블의 LOGGING모드 라고 표현한다. 반대는 NOLOGGING모드라고 한다.

   * LOGGIN mode 설정 방법

       SQL> create table test (no number) LOGGING;  : logging이라고 작성해주지 않아도 기본값은 logging 모드로 생성이 된다.

   * NOLOGGIN mode 설정 방법

       SQL> create table test (no number) NOLOGGING;

       SQL> alter table test NOLOGGING;    : nologging 모드로 변경한다.

 

  4) 필요한 경우 CACHE절을 사용하라.

   이 말은 자주 사용되는 데이터들이 버퍼 캐시 영역으로 Loader(읽혀져서 올라감)되면 다른 사용자의 작업에 의해 메모리로부터 지워지지않도록

   CACHE(캐시) 시켜주는 기능을 뜻한다. Data buffer cache는 제한된 공간이기에 사용자들이 조회 또는 변경하는 데이터들이 항상 그곳에 상주할 수

   없고 덮어씌여지기 때문에 자주 사용하는 데이터는 캐시 시켜두면 유용하다.

   * CACHE시키는 방법

       SQL> create table test (no number) CACHE;   : data buffer cache영역에 상주하게 된다.

   * NOCACHE시키는 방법

       SQL> create table test (no number)   : 기본 값은 NOCACHE이다.

 

 

7.2 테이블의 크기 설계

  1. 해당 테이블의 데이터 발생 빈도와 발생량을 분석해서 최초 요구되는 테이블의 크기를 산정하고,

     향후 요구되는 저장구조에 대한 전략수립에 이용된다.

  2. INITIAL은 최초 테이블의 크기를 , Next INITIAL 이후에 요구되는 저장구조의 크기를 결정한다.

 

개념적 데이터 모델링과 논리적 데이터 모델링을 수해하고 나면 테이블에 대한 논리적 구조(테이블명 /  컬럼명/ 데이터 타입/ 데이터 길이)들이

확정된다. 물리적 데이터 모델링 단계에서는 테이블을 생성할 때 얼마 만큼의 디스크 공간이 요구되는지, 향후 지속적으로 데이터가 저장되는지,

어떤 테이블스페이스에 생성할 지 분석한 뒤 최종적으로 테이블의 크기를 설계하게 된다.

 

블록 - 익스텐트 - 세그먼트 - 테이블스페이스 - 데이터베이스

 

블록 : 가장 작은 논리적 구조

        I/O의 단위를 결정하는 기준 값이다.

        기본크기 : 2, 4, 8, 16KB등

        오라클 블록의 크기는 데이터베이스를 처음 설치할때 결정됨. 설치 후에는 변경할 수 없다.

        하나의 블록 크기는 : init<DB명>.ora / spfile<DB명>.ora parameter파일에 정의되어 있는 DB_BLOCK_SIZE의 값에 의해 결정됨.

        하나의 블록은 헤드영역 / 데이터영역<실 데이터가 저장되는영역> 으로 구성됨.

 

익스텐트 :  테이블과 인덱스를 생성 시 사용자가 크기를 설정하지 않으면 오라클 서버가 제공하는 기본 크기 값으로 생성된다.

               할당된 첫 번째 공간을 : 최초 익스텐트 라고 한다.

 

7.2.1 테이블의 크기 계산

  1단계 : 요구되는 블록헤드의 총 크기를 계산한다. : 실체로 크기를 계산해 낼 수 있는 단계는 아니며 개념적 공식만을 추출할 수 있는 단계이다.

  2단계 : 하나의 블록 당 사용 가능한 데이터 영역의 크기를 계산한다.

  3단계 : 평균 행의 전체 컬럼의 길이를 계산한다.

  4단계 : 전체 행의 평균크기를 계산한다.

  5단계 : 한 개 블록당 저장할 수 있는 평균 행수를 계산한다.

  6단계 : 하나의 테이블에 요구되는 INITIAL 크기를 계산한다.

하나의 테이블을 생성하는데 몇 개의 블록이 필요할까? 그 갯수를 구하기 위해 가장 먼저 알아야 할 점은 테이블에 저장될 데이터의 크기이다. 테이블에 얼마만큼의 데이터가 저장될 것인지를 알아야하는데 이 정보는 분석단계에서 작성된 '수집장표 목록'과 같은 산출물이 필요하다.

Fixed Header : parameter값은 하나의 블록이 할당될 때 기본 정보를 저장하기 위해 반드시 요구되는 헤드 영역의 기본 크기. 기본값 : 75byte

Variable Transaction Header : 하나의 블록에서 하나의 트랜잭션을 처리하기 위해서 오버헤드(요구되는 여유공간)가 필요한데 이 크기를 의미한다. 

                                       기본값 : 23byte

Table Directory : 클러스의 테이블을 생성할 때 관련 정보를 저장하기 위해 요구되는 공간. 기본값 : 4byte 

Row Directory : 하나의 블록에 저장되어 있는 하나의 행마다 2byte가 요구된다. 즉 2 X 한블록의 평균 행 수 

INITRANS parameter : 하나의 블록에서 발생할 수 있는 트랜잭션의 최소 수를 제한하는 값.

MAXTRANS : 하나의 블록에서 발생하는 트랜잭션의 최대 수를 제한하는 값.

PCTFREE : 한 개 블록에 저장되어 있는 행을 변경하려고 했을 때 관련 행 정보를 같은 블록 내에 저장시키기 위해 미리 할당하는 여유 공간.

INITIAL prameter에 의해 테이블의 최초 크기를 결정할 때는 여러가지 기준을 설정할 수 있는데 , 하나의 익스텐트를 주단위/ 월단위/ 년단위로 크기를

설정할 수 있다. 만약 주 단위로 할당한다면 너무 작은 익스텐트가 너무 자주 할당되어서 저장공간에 대한 효율적 관리가 어렵다. 그리고 디스크 단편화 현상이 발생하기때문에 성능이 저하 될 수 있다. 만약 년 단위로 활성화 한다면 사용하지 않으면서 불필요한 디스크 공간을 확보하고 있기에 디스크 공간의 낭비가 될 수 있다. 일반적으로는 월 단위로 익스텐트가 활성화 될 수 있도록 저장 공간을 할당하는 것이 이상적인 형태가 된다.

7.2.2 OEM(Oracle Enterprise Manager)을 통해 테이블 크기를 예측한다.

 오라클 사에서는 10g version의 OEM을 통해 테이블의 크기를 예측하는 소프트웨어를 제공하고 있다.

생성할 테이블이름, 스키마명, 테이블스페이스명을 입력할 수 있고 컬럼명, 데이터타입, 컬럼의 길이 등을 입력하는 필드도 있다. 모든 정보가 입력되고

상단 부분의 테이블스페이스 필드 옆에 '테이블 크기 예측' 이라는 버튼을 클릭해서 미리 예측결과를 볼 수도 있다. 

RDBMS제품과는 별도의 라이센스가 요구되기 때문에 추가적인 비용이 발생하는 문제점이 있다. 또한 분석, 설계된 모든 테이블에 대해 물리적 크기를 계산할 필요는 없기 때문에 반드시 필요한 소프트웨어는 아니다. 

7.3 논-파티션 테이블과 파티션 뷰

 1980년대 : 데이터베이스 관리시스템의 도입

 1990년대 : 인터넷 환경의 급속한 도래. 기업의 데이터 양 증가.

 → 대용량 데이터의 저장과 관리는 컴퓨터의 성능을 저하시키고 + 백업 및 복구에 어려움. 해결방법으로 제안된 것 : 파티션 테이블

논 - 파티션 테이블 : '하나의 테이블은 반드시 하나의 테이블스페이스에 저장된다'는 논리적 구조를 말한다.

                           하나의 테이블이 하나의 테이블스페이스에만 저장되면 디스크 장치에 장애가 발생할 때 데이터 파일에 저장되어 있던 모든 

                           데이터를 참조할 수 없게 되는 경우가 발생하게 된다. 또한 데이터파일에 저장되어 있는 디스크레 많은 사용자가 동시 검색을

                           시도하면 '경합(Contention)현상이 발생하여 성능 저하 문제가 발생된다.

                           해결방법 : 파티션- 뷰 Data구조 : 여러개로 분리된 테이블을 하나의 테이블인 것처럼 결합하여 검색하는 객체

7.3.1 수직 파티션 테이블

파티션-테이블 : 하나의 테이블은 여러 개의 테이블스페이스에 저장된다. 라는 의미.

종류 : 2가지 

  1. 수직 파티션 테이블 : 칼럼의 성격에 따라 하나의 테이블을 여러 개의 테이블로 분리 + 설계 

  2. 수평 파티션 테이블 : 데이터 값을 기준으로 여러 개의 테이블로 분리 저장하는 설계 방법.

                               <단점 : 비용증가 / SQL문을 통한 구현에 많은 문제점을 갖고있음. / 테이블의 갯수 증가로인한 관리, 비용증가>

                                          

7.3.2 수평 파티션 테이블

 데이터 값을 기준으로 여러 개의 테이블로 분리된 데이터 구조.

 1. 범위 파티션 테이블 : 수평 파티션의 단점을 보완하기 위해 8i버전 부터 새롭게 이름을 변경하여 부르게됨.

                               논리적으로는 하나의 테이블이지만 입력되는 전표 날자 값에 따라 저장되는 테이블스페이스의 위치는 달라짐.

 2. 분할     

7.4 테이블 생성 스크립트의 작성