오라클 - 테이블물리설계

◆ 第1章 테이블 용량 산정

서론 테이블의 용량 산정은 OTN Japan에 이하의 자료와 툴이 준비되어 있습니다. 테이블 사이즈의 산출 방법 테이블 사이즈 산정 씨트 여기서 설명하고 있는 산정 방법은 자료 「테이블 사이즈의 산출 밥법」의 산출방식을 조금 간략화해서 계산하기 쉽게 한 방법이다. 또한 지침을 만드는 것을 우선하였기 때문에  플랫폼이나 버젼 등의  세세한 요소를 생략하고 있으므로 여기에서 설명하는 산정방법은 정확하지 않다.그렇다 하더라고 오차는 실제 필요용량이 보다 많은 경우라도 산정후에 예상한 여유치는 허용 가능한 레벨이다. 또한 산정 방법은 위의 자료에선 지원하지 않는다.   테이블 사이즈 산정 방법 개요 테이블 사이즈 산정 수순은 크게 다음과 같다. （수순1）1레코드의 평균 길이를 구한다. （수순2）1블럭에 들어가는 레코드 수를 구한다. （수순3）CEIL(예상 레코드수-(수순2)의 값)×블럭 사이즈 =테이블 용량 CEIL은 지정한 수보다 같거나 큰 최소 정수를 반환하는 함수. 한 레코드의 평균 길이를 구하는 방법 레코드는 크게 그림과 같은 이미지로 저장되어진다. 레코드는 레코드 헤더가 하나와  각열의 헤더 및 열 데이터로 구성된다. 그림：레코드 구성 레코드헤더 열헤더 열헤더 열헤더 열헤더 열헤더 열헤더 （1）레코드 헤더의 사이즈 레코드 헤더의 사이즈는 3바이트이다. （2）열 헤더의 사이즈 대응하는 열의 데이터 갈이가 250바이트 이하(NULL포함)의 경우는 1바이트 251 이상의 경우는 3바이트이다. （3）열 데이터의 사이즈 테이터 형에 따라 다르다.주요 데이터 형에 대해서는 표1을 참조.데이터가 NULL일경우는 데이터 형도 0바이트이다. 표1：데이터형에 따른 실제 소비 사이즈   데이터 형    고정/가변 테이터가 격납될때의 길이 CHAR 고정/가변 바이트수 지정시는 테이블정의의 고정길이 분자지정시는 문자에 대해서 고정이지만 실제소비바이트수는 케릭터셋및 실제 데이터에 의해 다르다.예를 들어 SJIS DB에서 「char(3 char)」의 컬럼에 「AAA」란 문자가 들어있으면 3바이트 소비하지만「あああ」가 들어있으면 6바이트 소비한다. VARCHAR2 가변 실제로 저장되어 있는 데이터의 길이(바이트지정시,문자지정시) NCHAR 고정 테이블정의문자의 두배(AL16UTF16지정시) NVARCHAR2 가변 저장문자의 두배(AL16UTF16지정시) NUMBER 가변 길이 = 1 + CEIL (n / 2) CEIL은 지정한 수보다 같거나 큰 최소 정수를 반환하는 함수. n는 저장된 수치의 정수부·소수부를 합한 총 자리수. n>38의 경우는 38 유효 자리수 38자리수 미만의 음수의 경우는 더욱  1바이트 가산 DATE 고정 7바이트 TIMESTAMP 가변 초의 소수부에 데이터가 있는 경우：11바이트 고정 초의 소수부에 데이터가 없는 경우：7바이트 고정 TIMESTAMP WITH TIME ZONE 고정 13바이트 TIMESTAMP WITH LOCAL TIME ZONE 고정 11바이트 INTERVAL YEAR TO MONTH 고정 5바이트 INTERVAL DAY TO SECOND 고정 11바이트 RAW 가변 실제로 저장되어 있는 데이터의 길이 LONG 가변 실제로 저장되어 있는 데이터의 길이 LONG RAW 가변 실제로 저장되어 있는 데이터의 길이 BLOB/CLOB/NCLOB 지정에의해함 DISABLE IN ROW 지정시：20바이트 ENABLE IN ROW 지정으로 행내에 격납시：실 데이터 길이+36 바이트  ENABLE IN ROW 지정으로 행외(LOB 테이블) 에 격납시：실데이터 길이에 의해  3686바이트. 견적시는 여유를 두고  86바이트 고정으로 계산 한다. LOB 테이블의 견적에 대해서는 나중에 기술  EMPTY의 경우는 데이터 길이의 부분을 0으로서 계산 BFILE 고정 530바이트 ROWID 고정 6바이트(~Oracle7)　 10바이트(Oracle8~) 한 블럭에 들어가는 레코드수 구하는 법 DB 블록의 구조는 대략적으로는 그림 2대로입니다. 블록은 블록 헤더와 데이터 격납부 , 그리고 테이블 파라미터 PCTFREE의 지정으로 확보한 예비 영역으로 구성됩니다. （1）헤더 블럭사이즈 블럭헤더 사이즈는 이하의 계산식을 이용해 구할수 있습니다. INITRANS는 테이블 파라메터이다. 헤더의 사이즈 = 90 ＋ (INITRANS － 1) （2）예비영역 사이즈 예비영역의 사이즈는 이하의 계산식을 이용해 구할수 있습니다.CEIL은 지정한 수보다 같거나 큰 최소 정수를 반환하는 함수.PCTFREE는 테이블 파라미터입니다. PCTFREE는 단위가%이므로 , 계산때는 소수로 고친다 예비영역사이즈 = CEIL((테이블 격납처의 테이블 스페이스의 블럭사이즈 － 헤더사이즈) × PCTFREE) （3）데이터 격납부의 사이즈 데이터 격납부의 사이즈는 이하의 계산식을 이용해 구할수 있다. 데이터 격납부 사이즈  = 테이블 격납처의 테이블 스테이스 블럭사이즈－헤더사이즈-예비 영역사이즈 위의 값을、 TRUNC(데이터 격납부 사이즈 ÷ 평균레코드 길이) 가 한 블럭에 들어가는 레코드수가 된다. 그림 2：블럭의구조 블럭 헤더 데이터 격납부   예비 영영 평균 레코드 길이가  데이터 격납부보다 긴 경우의 산정 방법 평균 레코드 길이가  데이터 격납부보다 긴 경우 , 당연히 레코드는 복수의 블록에 걸쳐 격납됩니다. 이 상태를 행 연쇄라고 부릅니다. 행 연쇄가 발생하는 평균 레코드 길이의 경우의 테이블 용량의 견적은、 CEIL(평균레코드길이 ÷데이터격납부의 길이) × 예상레코드수×블럭사이즈 가 됩니다. CEIL은 지정한 수보다 같거나 큰 최소 정수를 반환하는 함수.데이터 격납부의 계산때는 , PCTFREE는 실제의 지정에 관련되지 않고 0으로서 계산해 주세요. 그리고 , 이 견적 방법은 레코드의 사이즈의 격차가 적은 경우는 유효합니다만 , 불규칙한 경우는 실제의 레코드의 격납순서에 열매 격납 사이즈가 큰폭으로 영향을 받기 때문에 , 꽤 큰 견적이 되어 버릴 가능성이 있습니다. LOB데이터의 산정방법 LOB테이블의 산정은 아래의 수순으로 한다. （수순1）CHUNK치를 블럭사이즈의 배수로 절상 LOB 테이블의 파라미터 CHUNK는 LOB의 I /O 사이즈가 됩니다. 다만 , CHUNK의 사이즈가 블록 사이즈의 배수가 아닌 경우는 , CHUNK의 사이즈를 상회한다. 제일 작은 블록 사이즈의 배수의 값이 실제의 I /O 사이즈가 됩니다. 예를 들면 CHUNK가  5KB , 블록 사이즈가  4KB의 경우는 , 실제 I /O 사이즈는  8KB가 됩니다. 이 경우 딕셔너리상도  5KB는 아니고  8KB가 됩니다.   （수순2）평균LOB길이를 수순1의 배수로 절상한다. LOB의 데이터는 순서 1으로 요구한 CHUNK 사이즈의 배수에 끝맺을 수 있어 격납됩니다. 예를 들면 평균 LOB길이가  28KB , CHUNK의 사이즈가  8KB의 경우는  32KB가 됩니다. （수순3）LOB격납 영역의 사이즈 구하기 LOB격납영역의 사이즈는 아래의 식으로 구한다. LOB격납영역의 샤이즈  = 수순2의값 × 예상레코드 수 （수순4）RETENTION(PCTVERSION)영역 구하기 LOB 테이블에 대해서 갱신을 실시했을 경우 , 갱신전 데이터는 UNDO 세그먼트(segment)가 아니고 , LOB 테이블 자신에게 격납됩니다. 따라서 , 견적 시에 이것을 예상해 둘 필요가 있습니다. 자동 UNDO 관리를 이용하고 있는 경우는 파라미터 RETENTION , 수동 UNDO 관리를 이용하고 있는 경우는 파라미터 PCTVERSION로 지정한 영역이 LOB의 UNDO를 위해서 확보됩니다. 시스템중에서 트랜잭션(transaction)로 최대로 발생할 수 있는 변경전 LOB 데이터가 격납 가능한 만큼 크기를 예상한다.. 잘 모르는 경우는 순서 3으로 요구한 용량의 20%정도 예상한다. （수순5）수순3과 수순4의 합계가 LOB테이블의 용량이 된다.

 

◆ 第2章 테이블 용량 산정 예

레코드가 한블럭에 들어가는 길이의 경우의 산정 SCOTT스키마에 있는 EMP테이블을 예로 산정을 해본다. 예상건수는 10,000건으로 한다. EMP테이블의 정의는 다음과 같다. SQL> desc emp 名前         NULL   ?   型 ------------------------------ -------- ------------------------ EMPNO                                  NOT NULL  NUMBER(4) ENAME                                                    VARCHAR2(10) JOB                                                         VARCHAR2(9) MGR                                                        NUMBER(4) HIREDATE                                                DATE SAL                                                         NUMBER(7,2) COMM                                                     NUMBER(7,2) DEPTNO                                                   NUMBER(2) （1）평균레코드길이 구하기 가변길이의 데이터형의 평균 데이터 길이는 , NUMBER형에 대해서는 정밀도 그대로의 데이터 길이 , VARCHAR2형에 대해서는 정의 데이터 길이의  7할이 가정합니다. 계산 근거는  표 1을 참조. 표3：각열의 길이 계산예 열명 데이터형 열 데이터의 길이 열 헤더의 길이 합계열 길이 EMPNO NUMBER(4) 1 + CEIL (4 / 2) = 3 byte 1byte 4byte ENAME VARCHAR2(10) CEIL (10 * 0.7) = 7byte 1byte 8byte JOB VARCHAR2(9) CEIL (9 * 0.7) = 7byte 1byte 8byte MGR NUMBER(4) 1 + CEIL (4 / 2) = 3byte 1byte 4byte HIREDATE DATE 7byte 1byte 8byte SAL NUMBER(7,2) 1 + CEIL (7 / 2) = 5byte 1byte 6byte COMM NUMBER(7,2) 1 + CEIL (7 / 2) = 5byte 1byte 6byte DEPTNO NUMBER(2) 1 + CEIL (2 / 2) = 3byte 1byte 3byte 중 계　레코드 길이 47byte 레코드길이(열 헤더 3byte 를 가산) 50byte （2）1블럭에 들어가는 레코드 수 구하기 필요한 테이블 파라메터는 전부 디폴트로 설정하였다. 블럭 사이즈는 8KB（8,192바이트)로 설정 표4：블럭의 빈영역 구하기 구하는대상 계산내용 블럭헤더 사이즈 86 ＋ (INITRANS － 1) = 90 ＋ (1 － 1) = 90byte 예비영역 사이즈 CEIL((테이블 격납처의 테이블 스테이스의 블럭사이즈 - 헤더사이즈 ) × PCTFREE) = CEIL((8,192 - 90) * 0.1) = 811byte 데이터 격납부 사이즈 테이블 격남처의 테이블 스페이스의 블럭사이즈 - 헤더 사이즈 - 예비영역 사이즈  = 8,192 - 90 - 811 = 7,291byte 1블록중에서 실제로 데이터를 격납할 수 있는 영역이  7,291바이트가 구해졌으므로 , 이것을 (1) 에서 구한 평균 레코드 길이  50바이트로 나누 (나머진 버림) 면  1블록에 146건 들어가는 것을 알 수 있다 （3）테이블 용량 구하기 이미 접했지만 、(2)로부터의 테이블의 용량은 예상레코드수 ÷ (2)의 값 × 블럭 사이즈 가 되고 실제로 계산해보면 CEIL(10,000 / 146) * 8,192 = 565,248byte = 552kb 가 된다. 레코드가 한 블럭에 들어가지 않는 길이의 경우의 산정 블록 사이즈  8KB에 대해서 VARACHAR2(3000) 의 3열의 레코드가 10,000건 삽입되는 테이블의 용량을 추측합니다. 데이터는 어느 열도 항상  3,000바이트 들어오는 것으로 합니다. （1）평균 레코드 길이 구하기 레코드길이는 (3,000 + 3(열 해더)) * 3(열) + 3(레코드 헤더) = 9,012바이트가 된다. （2）데이터 격납부의 사이즈 구하기 레코드가 블러거에 들어가는 케이스랑 똑같이 전제하면 7,291바이트가 된다. （3）테이블 용량 구하기 이와 같은 케이스의 테이블 용량의 계산방법은 CEIL(평균 레코드 길이 ÷ 데이터 격납부의 길이 ) × 예상 레코드수 × 블록 사이즈 이므로、 CEIL(9,012 / 7,291) * 10,000 * 8,192 = 163,840,000바이트 = 약  157메가바이트     LOB테이블의 용량 산정 블럭사이즈가 8KB인 경우 평균 1메가의 LOB를 1000건 격납하는 LOB테이블의 용량 산정예이다. RETENTION로 필요한 영역은 LOB격남영역의 20%、DISABLE STORAGE IN ROW지정으로 1000건 모두가 LOB테이블에 격남되어 있다라고 가정 CHUNK는 3KB로 한다.   （1）CHUNK의 값을 블록 사이즈의 배수에 절상      CHUNK가3KB이고 블럭사이즈가 8KB이므로 8KB가 된다.  （2）평균LOB길이를 (1)의 배수로 절상      1MB는 8KB의 배수이므로 1MB 그대로 한다. （3）LOB격납영역의 사이즈 구하기 LOB격납영역의 사이즈는 LOB격납영역 = (2)의 값 × 예상 레코드수 이므로 1,048,576 * 1,000 = 1,048,576,000바이트 = 1,000메가바이트 가 된다. （4）RETENTION영역을 구한후 더해 테이블 용량이 된다. RETENTION영역은 LOB격납영역의 20%이므로 (3)의 값을 1.2배 한1,200 메가바이트가 산정결과가 된다.

◆ 第3章 컬럼의 나열순서의 기본지침

컬럼의 나열순서의 기본지침 컬럼의 나열순서는 기본적으로 관리하기 쉽도록.을 염두해 두고 설계합시다. 예를들어 아래의 항목을 유념합니다. 주키를 처음에 정의하자 관련성이 있는 컬럼(예：성과이름 ,부서코드와 과코드)은 연속으로 컬럼을 정의한다. 자주 사용되는 컬럼은 앞쪽에 기술한다. 레코드 길이를 줄이는 테크닉 레코드 길이를 가능한 짧게하면 할수록, 블럭당 저장할수 있는 레코드의 수가 많아 진다.그렇게 하면 캐쉬히트율이 높아지고 용량의 절약도 가능해 진다. （1）가변길이 테이터형 이용 예를들어 우편번호같이 자릿수가 정해져 있는 데이터형을 제외하고는 VARCHAR2와 같은 가변길이를 사용하자. 정의된 길이에 비해 실데이터가 작으면 작을수록 용량을 절약할수 있다. （2）SJIS이용 일본어의 데이터 저장효율(용량절약)의 관점에서 케릭터셋에 SJIS을이용하는것이 제일좋다.단 국제화 대응이나 이용 플랫폼과의 친화성이란 관점도 있으므로 일본어 데이터의 저장효율의 관점에서만 케릭터셋을 결정해서는 안될것이다. （3）NULL이 저장되기 쉬운 컬럼을 뒤에 정의한다. NULL이될 경유가 많은 컬럼은 모아서 뒷쪽에 배치하면 열 헤더가 생략되기때문에 레코드 길이를 줄일수 있다.

◆ 第4章 CREATE TABLE의 파라메터 설정

서론 본장은 로컬관리 테이블 스페이스영역을 이용하는 것을 전제로 합니다. 로컬관리 와 딕셔너리관리로 의미가 다른 파라메터 아래의 파라메터는 로컬관리와 딕셔너리관리에서의 그 의미가 다르다. INITIAL NEXT PCTINCREASE MAXEXTENTS 표5에서 다른점을 설명한다. 로컬관리 테이블 스페이스 영역에 있어 파라메터의 의미가 많이 다른것은 크기를 지정하는 파라메터（INITIAL/NEXT）가 익스텐트의 사이즈를 나타내는 것이 아니라 초기에 확보하는 총데이터의 용량이 되는것이다. 表5：로컬관리와 딕셔너리관리에서 의미가 다른 파라메터  파라메터                   딕셔너리 관리  로컬 관리 INITIAL 초기에 확보하는 익스텐트의 사이즈. 예를 들어 100MB의 INITIAL를 지정하면 1개 100MB의 익스텐트를 확보한다. 초기에 확보하는 익스텐트의 총 사이즈 예를들어 1MB의 UNIFORM지정의 테이블 스페이스에 100MB의INITIAL를 지정하면 100개의 1MB익스텐트를 확보한다. NEXT 두번째 익스텐트의 사이즈 예를들어 70MB의 NEXT를 지정하면 두번째 익스텐트의 사이즈 70MB의 크기로 작성된다. MINEXTENTS가 2이상일때 INITIAL와NEXT의 합계 사이즈가 되는 한개이상의 익스텐트가 확보된다.예를들어1MB의 UNIFORM지정의 테이블 스페이서에 MINEXTENTS 2、INITIAL 100MB、NEXT 70MB라 지정하면 170개의 1MB 익스텐트를 확보한다. PCTINCREASE 3번째 이후의 익스텐트를 확보할때 바로 전 익스텐트 사이즈로 부터 증가. 디폴트는 50(%). 예를들어 NEXT 100MB,PCTINCREASE 40이란 지정의 경우 3번째 익스텐트는 NEXT치의 40% 증가로 140MB、4번째는 3번째 익스텐트의 40% 증가로196MB가 된다. MINEXTENTS가 3이상일때 딕셔너리 관리와 동일계산법으로 한개 이상의 딕스텐트가 확보된다. 디폴트 0(%)。예를들어 1MB의UNIFORM 지정의 테이블 스페이스에 MINEXTENTS 4、INITIAL 100MB、NEXT 100MB、PCTINCREASE 40이라 지정하면 100 + 100 + 140 + 196 = 536개의 1MB 익스텐트을 확보한다. MAXEXTENTS 지정한 수보다 많은 익스텐트가 작성되지 않게 된다. 의미는 딕셔너리 관리와 동일하지만 지정해도 무시되어 UNLIMITED로 된다. 테이블 작성시의 지정을 고려해야할 파라메터들 로컬관리 테이블 스페이스에 있어서 항상 지정해야할 파라메터는 기본적으로 INITIAL뿐..이라고 생각해고 상관 없습니다. 물론 의식해서 지정하는 편이 좋은 파라메터는 여러가지 있습니다만. 상당히 처리량이 많은 경우가 아니라면 어느정도 다른 파라메터는 고려하지 않아도 상관없다. INITIAL는 가능한한 예상 레코드수를 수용할 만큼의 크기로 지정하자. 로컬관리 테이블 스페이스영역에서는 익스텐트의 수가 많은 것은 성능에 특별히 영향을 미치지 않지만 익스텐트의 확장은 익셔너리 관리 테이블 스페이스 정도는 아니기는 하지만 부하가 걸립니다. 초기의 디스크 용량의 제한등으로 예상 레코드의 확보를 할 수 없는 경우, 초기 레코드수가 예상 레코드수에 비해 큰폭으로 적은 경우는 작게 작성해, 필요에 따라서 확장하는 형태를 취합니다. 경우에 따라 설정을 고려 해야할 파라메터들 （1）NEXT/MINEXTENTS/PCTINCREASE 대규모 테이블의 경우, extent를 복수의 데이터 파일에 분산시켜 I/O성능의 향상을 도모하는 케이스가 있습니다.이와 같은 경우에 이용을 검토합니다.그 때 PCTINCREASE는 디폴트의 0인 편이 영역 계산이 하기 쉽다. （2）FREELIST GROUPS 디폴트의 1인 채로 상관없다. RAC 환경에서는 노드수에 맞춘 값을 기본으로 한다. （3）FREELISTS 디폴트의 1인 채로 상관없다. 대량 삽입의 트랜잭션이 동시 발생하는 경우는 값을 늘리는 것을 검토한다. （4）PCTFREE 초기 레코드 사이즈로부터 최종 레코드 사이즈가 길어질 수 있는 비율을 설정하는 것이 제일 효율적인 영역 관리를 할 수 있습니다.예를 들면 INSERT시의 레코드의 평균 사이즈가 100바이트로, 갱신을 거듭해 최종적으로 평균 130바이트가 되는 것이면 30(%)을 지정하면 좋습니다.레코드 사이즈의 증가율을 읽을 수 없는 경우는, 디폴트의 10으로 운용해, 재편성시에 값을 조정합니다. 읽기 전용의 테이블이면 0에서도 상관없다. （5）PCTUSED 기본적으로 디폴트의 40으로 상관없다. PCTUSED의 값을 높게 하면 블록의 재이용이 쉬어지므로  영역을 효율적으로 이용할 수 있습니다만, 비어있는 리스트에 블록이 많이 등록되기 쉽기 때문에 갱신계의 퍼포먼스가 떨어집니다. 낮게 하면 퍼포먼스는 오릅니다만 영역의 이용 효율은 떨어집니다.퍼포먼스를 우선으로 하는 경우는 보다 낮게 ,영역의 이용 효율을 우선으로 하는 경우는 보다 조금 비싸게 설정합니다.또 전건 검색의 퍼포먼스를 우선시키고 싶은 경우는 데이터를 채워 저장하는 것이 좋기 때문에 높은 값으로 설정합니다.덧붙여 PCTFREE와 PCTUSED의 값의 합계는 사양상 100을 넘을 수 없기 때문에, 이 범위에서 조정하도록 한다. （6）INITRANS 디폴트의 1인 채로 상관없다. 복수의 트랜잭션이 동시에 같은 블록에의 갱신이 빈발하는 경우는, 값을 늘리는 것을 검토 （7）LOGGING/NOLOGGING 테이블에 대한 처리로 NOLOGGING의 지정이 효과가 있는 것은 이하의 리스트에 있는 처리입니다.이러한 처리의 속도를 올리고 싶은 경우는 NOLOGGING의 지정을 검토한다. 다만, REDO 로그에 처리 내용이 기록되지 않기 때문에, 이러한 처리를 실시한 다음은 백업을 할 것을 추천한다. 다이렉트 로드（SQL*Loader） 다이렉트 로드,인서트（APPEND힌트포함 INSERT SELECT） CREATE TABLE ... AS SELECT ALTER TABLE ... MOVE PARTITION ALTER TABLE ... SPLIT PARTITION LOB 테이블에 저장되는 NOCACHE NOLOGGING 모드의 LOB의 INSERT, UPDATE 및 DELETE

출처 : http://blog.naver.com/hirokorea?Redirect=Log&logNo=20023344842