오라클 - 인덱스물리설계

◆ 제1장 익덱스의 용량 산정

서론 인덱스, 특히 B-Tree 인덱스의 용량 견적에 대해서는, 전회에서도 소개한 것 것과 같습니다만 OTN Japan에 이하의 자료·툴이 준비되어 있습니다.  영역사이즈의 산정방법 영역사이즈 산정시트 여기서 해설하고 있는 견적 방법은, 전회와 같이 자료 「영역 사이즈의 견적 방법」의 내용을 약간 간략화하고, 계산하기 쉽게 한 방법입니다.또, 지침을 내는 것을 우선해, 플랫폼이나 버젼에 의한 차이를 시작으로 해 세세한 요소를 생략하고 있으므로, 여기서 해설하고 있는 견적 방법은 완전하게는 정확하지는 않습니다. 또, 11월부터 OTN 온라인 서비스의 일환으로서 용량 견적 서비스가 시작되었습니다.이쪽도 이용하십시오. B-Tree인덱스의 산정방법 개요 인덱스의 견적 순서는 대략적으로 아래와 같다. （수순1）　1레코드의 평균길이를 구한다. （수순2）　1블럭에 저장되는 레코드수를 구한다. （수순3）　CEIL(예상 레코드수 × 1.05 ÷ 수순2의값) × 블럭사이즈가 인덱스 사이즈가 된다. （수순4）　빈 테이블에 인덱스를 작성하는 경우는 수순2의 값×1.3이 익덱스 용량이 된다. 순서 1, 순서 2의 자세한 것은 후술 합니다. 순서 4가 필요한 이유는, 순서 3까지 방법으로 구할 수 있는 용량이 이미 예상 건수의 레코드가 저장되고 있는 테이블에 대해서 인덱스를 작성했을 경우의 용량이기 때문입니다. 빈 테이블과 인덱스를 작성하고 나서 예상 건수의 레코드를 삽입했을 경우, 어떠한 값을 가지는 레코드가 어떠한 차례로 삽입될지를 사전에 모릅니다.그 때문에, 레코드가 이미 존재하고 있는 상태로부터 인덱스를 작성했을 경우에 비하면 블록 근처의 레코드의 격납 효율이 떨어져 버립니다.덧붙여 승수의 1.3은 필자의 경험에 의하는 것인 것을 덧붙혀 둡니다.저장되는 데이터의 특성이나 차례에 따라 더욱 여유를 보는 것이 좋은 케이스 될수도 있다. 한편 1.1 정도의 승수로 하는 케이스도 있을수 있다. 1레코드의 평균길이를 구하는 법 인덱스의 레코드는 대략적으로 그림1과 같은 이미지로 저장된다. 그림1：B-Tree인덱스의 레코드 구성 레코드헤더 ROWID 오버헤드 열 헤더 열 데이터 열 데이터 열 데이터 열 데이터 열 데이터 （1）레코드 헤더의 사이즈 레코드 에더의 사이즈는 3바이트이다. （2）ROWID 사이즈 Oracle9i의 본래의 ROWID의 사이즈는 10바이트입니다만, 인덱스 레코드중의 ROWID는 Oracle7 시대의 6바이트의 포맷으로 저장됩니다.다만, 글로벌의 파티션·인덱스의 경우는 10바이트가 됩니다. （3）오버헤드 사이즈 (127바이트 이하의 열수 × 1) ＋ (128바이트 이상의 열수 × 2) ＋ 4 바이트가 됩니다.임의가 아닌 인덱스 의 경우는 더욱 1바이트 필요로 합니다. （4）열 헤더의 사이즈 대응하는 열의 데이터 길이가 250바이트 이하의 경우는 1바이트, 251바이트 이상의 경우는 3바이트입니다. （5）열 데이터의 사이즈 데이터형에 따라서 다릅니다.주요 데이터형에 대해서는 전회 설명한 표 1을 참조해 주세요. 주의점으로서(1)+(4)+(5)의 합계(복합 인덱스의 경우는 전색인 대상열의(4)+(5)가 대상)가 9바이트에 못 미친 경우는 9바이트로 절상한다. 1블럭에 저장되는 레코드수 구하는 법 DB블록의 구조는 대략적으로는 그림 2대로입니다.인덱스에서도 테이블과 다르지 않습니다.블록은 블록 헤더와 데이터 격납부, 그리고 인덱스 파라미터 PCTFREE의 지정으로 확보한 예비 영역으로 구성됩니다. （1）블럭헤더의 사이즈 블록 헤더의 사이즈는 이하의 계산식으로 구할 수 있습니다. INITRANS는 인덱스 파라미터입니다。 헤더사이즈 = 96 ＋ 24 × INITRANS （2）예비영역 사이즈 예비 영역의 사이즈는 이하의 계산식으로 구할 수 있습니다.PCTFREE는 인덱스 파라미터입니다.PCTFREE는 단위가%이므로, 계산때는 소수로 고친다. 예비영역사이즈 = CEIL((인덱스 테이블영역의 블럭 사이즈  － 헤더사이즈) × PCTFREE) （3）데이터 격납부의 사이즈 데이터 격납부의 사이즈는 이하의 계산식으로구할 수 있습니다. 데이터 격납부의 사이즈 = 인덱스 테이블의 테이블 영역의 블럭사이즈 －예비영역사이즈 이상의 값에 의해  TRUNC(데이터 격납부 사이즈÷ 평균레코드 길이) 이 1블럭에 저장되는 레코드의 수가 된다. 그림2：블럭의 구성 블럭 헤더 데이터 격납부 예비영역 LOB인덱스의 용량 산정 방법 Oracle9i에서는 LOB 인덱스는 LOB 테이블을 작성했을 때에 LOB 테이블과 같은 테이블 영역에 Oracle이 내부적으로 작성합니다.자동으로 작성되기 위해서 간과되기 쉽상입니다만, LOB 인덱스의 견적도 제대로 실시한다. LOB 인덱스의 용량은 LOB 테이블의 최저5%의 크기를 Oracle이 자동으로 확보합니다.그러므로 LOB 인덱스의 용량은 LOB 테이블의 용량의5%로 추측한다. 비트맵 인덱스의 용량 산정 방법 비트맵 인덱스의 견적에 대해서는, 실제로 들어 오는 데이터의 내용이나, 데이터 압축의 상황에 실용량이 좌우되기 때문에 사전에 추측하는 것은 곤란합니다. 단지 같은 열에 대해서 작성한 B-Tree 인덱스보다는 작아지는 것으나 큰 견적이 되어 버립니다만 B-Tree 인덱스의 견적을 유용

 

◆ 제2장 인덱스의 용량 산정 예

B-Tree인덱스의 견적 SCOTT schema에 있는 EMP표를 예로 ENAME열에 비임의의 B-Tree 인덱스를 작성했을 경우의 견적을 실제로 해 보겠습니다.예상 건수는 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）평균레코드 길이 구하기 이번은 ENAME열의 평균열 길이를 7바이트로 가정해 계산합니다.이것에 열헤더 사이즈 1바이트를 더한 8바이트가 열의 평균길이가 됩니다.레코드 헤더의 사이즈를 더하면 9바이트를 웃돌기 때문에, 8바이트의 길이로 계산을 합니다.이것에 레코드 헤더 3바이트, ROWID6 바이트, 오버헤드 6바이트(1 + 4 + 1, 최초의 1바이트는 열의 오버헤드, 4는 고정치, 마지막 1은 비임의 색인의 분 )을 더한 23바이트가 평균 레코드길이가  됩니다. （2）1블럭에 저장되는 레코드수 구하기 필요한 인덱스 파라미터는 모두 디폴트(INITRANS:2, PCTFREE:10)으로 합니다.블록 사이즈는 8 KB(8,192바이트)로 합니다. 표1：블럭의 빈영역 구하기 구할 대상 계산 내용 블럭헤더 사이즈 96 ＋ 24 × INITRANS = 96 ＋ 24 × 2 = 144byte 예비영역사이즈 CEIL((인덱스의 테이블영역의 블럭 사이즈 － 헤더 사이즈) × PCTFREE) = CEIL((8,192 - 144) * 0.1) = 805byte 데이터 격납부의 사이즈 인덱스의 테이블영역의 블럭 사이즈 －헤더 사이즈－예비영역사이즈 = 8,192 - 144 - 805 = 7,243byte  블록중에 실제로 데이터를 격납할 수 있는 영역이 7,243바이트가 구해졌으므로, 이것을(1)로 요구한 평균 레코드길이 23바이트로 나누는 1 블록에 314건 들어가는 것을 알 수 있습니다. （3）인덱스 용량 구하기 인덱스 용량의 계산방법은 CEIL(예상 레코드수÷1블럭에 저정되는 레코드수 ×블럭사이즈 이므로、 CEIL(10,000 * 1.05 / 314) * 8,192 = 278,528바이트 가 된다.

◆ 제3장 CREATE INDEX의 파라메터 설정

서두 본장은 로컬 관리표 영역을 이용하고 있는 것을 전제로 이야기를 진행시킵니다.또, B-Tree 인덱스를 전제로 하고 있습니다. 로컬관리와 딕셔너리관리에서 의미가 다른 파라메터들 이하의 파라미터는 로컬 관리와 딕쇼내리 관리에서의 기능이 다릅니다. INITIAL NEXT PCTINCREASE MAXEXTENTS 인덱스 생성시 지정을 고려해야할 파라메터들 （1）INITIAL IINITIAL는 가능한 한 예상 레코드수를 커버할수 있을 만한 크기를 지정합시다. 로컬 관리 테이블스페이스 에서는 extent의 수가 많은 것은 퍼포먼스에 특히 영향을 주지 않습니다만 extent의 확장은 딕쇼내리 관리표 영역정도는 아니기는 하지만 그만한 부하가 걸립니다.초기의 디스크 용량의 제한등으로 예상 레코드 만큼의 확보를 할 수 없는 듯한 경우 초기 레코드수가 예상 레코드수에 비해 큰폭으로 적은 경우는 작게 작성해  필요에 따라서 확장하는 형태를 취합니다. （2）PCTFREE 인덱스의 PCTREE의 설정의 관점은, 테이블과 달리 두 개의 관점이 있습니다. 우선 첫째는, 테이블과 같이 초기 레코드 사이즈로부터 최종 레코드 사이즈가 길어질 수 있는 비율을 설정하는 관점입니다.예를 들면 INSERT시의 레코드의 평균 사이즈가 20바이트로, 갱신을 거듭해 최종적으로 평균 25바이트가 되는 것이면 25(%)를 지정하면 좋습니다. 레코드 사이즈의 증가율을 읽을 수 없는 경우는, 5로 운용해, 재편성시에 값을 조정합니다. 읽기 전용의 테이블이면 0에서도 상관하지 않습니다.테이블의 PCTFREE에서는 디폴트의 10을 추천 했습니다만, 인덱스의 경우는 자리수가 큰폭으로 증가하는 갱신이 발생하는 것이 생각하기 어려운 열을 대상으로 하는 것이 많기 때문에, 추천치를 보다 작은 것으로 하고 있습니다. 둘째는, 갱신은 아니고 신규 추가를 위해서 확보하는 영역의 관점입니다.인덱스의 경우, 예를 들면 그림 3과 같이, 값이 10, 11, 13, 14, 15, 17, 19라고 하는 레코드로 차지할 수 있고 있어 빈 곳이 없는 상태로 값이 12의 레코드가 삽입되면, 10, 11, 12, 13이라고 하는 레코드로 구성되는 블록과 14, 15, 17, 19라고 하는 레코드로 구성되는 블록에 분할됩니다(반반에 분할하고 있는 것은 어디까지나 예로, 데이터의 어디서 분할될까는 실제는 데이터의 내용에 의존합니다).이와 같은 블록의 분할(리프·블록의 분할)은, 인덱스의 단편화가 진행되거나 특히 OLTP계 처리로의 갱신에 있어서의 퍼포먼스 열화라고 하는 악영향이 있습니다.PCTFREE로 확보된 영역은 리프·블록의 분할을 억제하기 위한 대INSERT 예비 영역이라고 해도 이용됩니다. 예를 들면 시퀀스를 이용해 값이 승순으로 삽입되어 갱신도 없는 열이면 제일 큰 값을 격납하고 있는 블록밖에 리프·블록의 분할의 대상이 되지 않기 때문에 PCTFREE는 0이어도 상관하지 않습니다.값이 내림차순으로 삽입된다든가, 랜덤에 삽입되는 경우는, 경우에 따라서는 50 정도의 큰 값을 지정할 필요가 있습니다. PCTFREE의 값은 이상의 두 개의 관점을 조합해서 결정합니다. 그림3：리프 블록의 분할의 예 경우에 따라 지정을 고려해야할 파라메터 （1）NEXT/MINEXTENTS/PCTINCREASE 대규모 인덱스의 경우, extent를 복수의 데이터 파일에 분산시켜 I/O성능의 향상을 도모하는 경우가 있습니다.이와 같은 경우에 이용을 검토합니다.그 때 PCTINCREASE는 디폴트의 0인 편이 영역 계산이 하기 쉽습니다. （2）FREELIST GROUPS 디폴트의 1이여도 상관없습니다.RAC 환경에서는 노드수에 맞춘 값을 기본으로 한다 （3）FREELISTS 디폴트의 1이여도 상관없습니다. 대량 삽입의 트랜잭션(transaction)가 동시 발생하는 경우는 값을 늘리는 것을 검토 한다. （4）PCTUSED 인덱스는 블록중의 레코드가 모두 삭제되지 않으면 블록이 재이용되지 않는 사용으로 되어 있으므로, 본파라미터는 존재하지 않습니다. （5）INITRANS 디폴트의 2이여도 상관없습니다.(테이블과는 디폴트치가 다릅니다).복수의 트랜잭션(transaction)로 동시에 같은 블록에의 갱신이 빈발하는 경우는, 값을 늘리는 것을 검토. （6）LOGGING/NOLOGGING 인덱스에 대한 처리로 NOLOGGING의 지정이 효과가 있는 것은 이하의 리스트에 있는 처리입니다.이러한 처리의 속도를 올리고 싶은 경우는 NOLOGGING의 지정을 검토한다.다만, REDO 로그에 처리 내용이 기록되지 않기 때문에, 이러한 처리를 실시한 다음은 백업을 하는 것이 좋다.덧붙여 다이렉트·로드나 다이렉트·로드·인서트라고 하는 처리에 대해서는, 테이블과는 달라 인덱스에 대한 삽입은 NOLOGGING 지정이어도 REDO를 LOGGING 지정과 같이 생성합니다. CREATE INDEX ALTER INDEX ... REBUILD ALTER INDEX ... SPLIT PARTITION ALTER INDEX ... REBUILD PARTITION

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