|
|
|
인덱스 관리에 대한 지침 사항
이 곳에서는 인덱스를 관리할 때 수행하는 지침 사항을 설명하고 다음 내용을 다룹니다.
- 테이블 데이터 삽입 후 인덱스 생성
- 테이블당 인덱스 수 제한
- 트랜젝션 입력 항목의 매개변수 지정
- 인덱스 블록의 영역 사용 지정
- 각 인덱스에 대한 테이블스페이스 지정
- 인덱스 생성 병렬화
- UNRECOVERABLE 인덱스 생성시 고려 사항
- 인덱스 크기 측정 및 저장 영역 매개변수 설정
인덱스는 테이블과 클러스터에 관련된 선택적 구조이며 테이블에서 명시적으로 생성되어 SQL명령문 실행 속도를 증가시킬 수 있습니다.
이 설명서의 색인으로 정보를 더욱 빨리 찾을 수 있는 것과 같이 Oracle인덱스는 테이블 데이터를 훤씬 빠른 액세스 경로를 제공합니다.
인덱스의 존재 여부는 SQL명령문의 문법에는 변화를 주지 않습니다. 인덱스는 단지 데이터에 재한 빠른 액세스 경로를 제공하며 실행 속도에만 영향을 줍니다.
인덱스된 데이터 값이 부여되면 인덱스는 값을 포함하는 행의 위치를 가리킵니다.
인덱스는 관련된 테이블의 데이터에 논리적으로나 물리적으로 독립적입니다. 기본 테이블이나 다른 인덱스에 영향을 주지 않고 언제든 인덱스를 생성하거나 삭제할 수 있습니다.
인덱스를 삭제하면 모든 응용 프로그램은 계속 수행되지만 이전에 인덱스된 데이터로의 액세스는 더욱 느려집니다. 독립 구조로서 인덱스는 저장 영역이 필요합니다.
Oracle은 인덱스가 생성되면 유지 관리하고 사용합니다. 사용자가 더 이상 추가 작업을 수행하지 않아도 새 행 추가, 갱신, 삭제와 같은 데이터에 대한 변경 사항을 모든 인덱스에 자종으로 반영합니다.
1. 테이블 데이터 삽입 후 인덱스 생성
SQL*Loader 또는 Import를 통해 데이터를 테이블에 삽입하거나 로드한 후에 테이블에 대한 인덱스를 생성해야 합니다.
인덱스가 없는 테이블에 데이터 행을 삽입한 후 다음 액세스를 위해 인덱스를 생성하는 것이 더 효율적입니다.
테이블 데이터가 로드되기 전에 인덱스를 생성하면 모든 인덱스는 테이블에 행이 삽입될 때 마다 갱신되어야 합니다.
또한 클러스터에 데이터를 삽입하기 전에 클러스터에 대한 인덱스를 생성해야 합니다.
테이터를 가진 테이블에 인덱스가 생성되면 Oracle은 정렬 영역을 사용해야 합니다.
Oracle은 인덱스 생성자(SORT_AREA_SIZE 초기화 매개변수에 의해 결정되는 사용자당 사용량)에 할당된 메모리 정렬 영역을 사용하면서 인덱스 생성에 할당된 임시 세그먼트에서 정렬 정보를 교환해야 합니다.
인덱스가 너무 크면 다음 작업을 수행하고자 할 것입니다.
새 임시 세그먼트 테이블스페이스를 생성합니다.
인덱스 생성자의 임시 세그먼트 테이블스페이스를 변경합니다.
인덱스를 생성합니다.
원한다면 임시 세그먼트 테이블스페이스를 제거하고 생성자의 임시 세그먼트 테이블스페이스를 재 지정할 수 있습니다.
2. 테이블당 인덱스 수 제한
테이블은 인덱스를 무제한으로 가질 수 있습니다. 그러나 인덱스가 많을수록 테이블이 수정할 때 오버헤드도 늘어납니다.
특별히 행이 삽입되거나 삭제될 때 테이블의 모든 인덱스도 갱신되어야 합니다. 열이 갱신되면 열을 포함하는 모든 인덱스도 갱신되어야 합니다.
따라서 테이블에서 데이터를 읽어들이는 속도와 테이블을 갱신하는 속도 사이에는 차이점이 있습니다.
예를 들어 테이블이 주로 읽기 전용이라면 인덱스가 많은 것이 유용하지만 테이블이 주로 갱신 위주라면 인덱스가 적은 것이 좋습니다.
3. 트랜잭션 입력 항목의 매개변수 지정
각 인덱스 생성 중에 INITRANS 와 MAXTRANS매개변수를 지정하여 초기에 인덱스 세그먼트의 데이터 블록에 트랜잭션 입력 항목에 대한 영역 할당량과 최대 할당량을 결정합니다.
자세한 내용은 "스키마 게체 관리에 대한 지침 사항의 저장 영역 매개변수 설정을 참조 하세요.
4. 인덱스 블록의 영역 사용 지정
테이블에 인덱스가 생성되면 인덱스의 테이터 블록은 최대 PCTFREE까지 기존 테이블의 기존 값으로 채워집니다.
인덱스 블록을 위해 PCTFREE로 예약된 영역은 새 행이 테이블에 삽입될 경우에만 사용되며 해당 인덱스 입력 항목은 정확한 인덱스블록(즉 앞과 다음 인덱스 입력 항목사이)에 있어야 합니다.
해당 인덱스 블록에 더 이상 사용할 수 있는 영역이 없으면 인덱스된 값은 다른 인덱스 블록에 넣어집니다.
따라서 많은 행을 인덱스된 테이블에 삽입하려면 새 인덱스 값을 수용하는 PCTFREE값이 높아야 합니다.
테이블에 삽입된 사항이 별로 없다면 관련된 인덱스의 PCTFREE값을 낮게 하여 인덱스 데이터를 유지하는데 필요한 블록수를 줄일수 있습니다.
주 : PCTUSED는 인덱스에 대해 지정될 수 없습니다.
5. 각 인덱스에 대한 테이블스페이스 지정
인덱스는 테이블스페이스에서 생성될 수 있습니다. 인덱스는 인덱스하는 테이블과 동일한 테이블스페이스에서 생성될 수도 있고 다른 테이블스페이스에서 생성될 수도 있습니다.
테이블과 인덱스에 대해 동일한 테이블스페이스를 사용한다면 데이터베이스 유지 관리가 더욱 편해지며 모든 관련된 테이터가 온라인 상태로 됩니다.
테이블과 인덱스에 대해 다른 디스크에서 다른 테이블스페이스를 사용하면 동일한 테이블스페이스에 테이블과 인덱스를 저장할 때 보다 디스크 경합이 줄어들므로 성능이 좋아집니다.
테이블과 인덱스에 대한 다른 테이블스페이스를 사용하고(데이터나 인덱스를 포함하는) 한 테이블스페이스가 오프라인이면 이 테이블을 참조하는 명령문은 성능이 보장되지 않습니다.
6. 인덱스 생성 병렬화
병렬 질의 옵션을 설치하면 인덱스 생성을 병렬화할 수 있습니다. 여러 프로세스가 인덱스를 생성하므로 하나의 서버가 계속 인덱스를 생성할 때 보다 더 빠르게 인덱스를 생성할 수 있습니다.
인덱스를 병렬로 생성하면 각각의 질의 서버 프로세스는 저장 영역 매개변수를 제각기 사용합니다. 따라서 INITIAL 5M과 PARALLEL DEGREE 12로 생성된 인덱스는 인덱스가 생성되는 동안 적어도 60M의 저장 영역을 필요로 합니다.
7. UNRECOVERABLE 인덱스 생성 시 고려 사항
CREATE INDEX 명령문에서 UNRECOVERABLE을 지정하면 리두 로그 레코드를 만들지 않고도 인덱스를 생성할 수 있습니다.
주 : UNRECOVERABLE 을 사용하여 생성된 인덱스는 아카이브되지 않으므로 인덱스를 생성한 후에는 백업야 합니다.
복구할 수 없는 인덱스를 생성하려면 다음과 같은 이점이 있습니다.
리두 로그 파일의 영역이 절약됩니다.
인덱스 생성 시간이 줄어듭니다.
대규모 인덱스의 병렬 생성 성능이 향상 됩니다.
일반적으로 소규모 인덱스보다 대규모 복구할 수 없는 인덱스의 성능이 상대적으로 향상됩니다.
복구할 수 없는 소규모 인덱스를 생성하면 인덱스 생성 시간에 커다란 영향을 주지 않습니다.
그러나 대규모 인덱스에서는 특히 인덱스 생성을 병렬화할 때 성능 향상에 커다란 영향을 줍니다.
8. 인덱스 크기 측정 및 저장 영역 매개변수 설정
"스키마 개체에 대한 영역 예측"에는 인덱스 크기를 측정하는데 필요한 공식이 있습니다.
인덱스를 생성하기 전에 인덱스 크기를 측정하면 다음과 같은 이점이 있습니다.
원하는 데이터베이스를 유지하는데 필요한 디스크 영역을 결정하는 인덱스와 테이블, 롤백 세그먼트, 리두 로그 파일의 측정치를 합하여 사용할 수 있습니다. 이 측정치를 가지고 올바른 하드웨어를 구매하고 다른 사항을 결정합니다.
각 인덱스를 측정한 크기를 사용하여 인덱스가 사용할 디스크 영역을 관리할 수 있습니다. 인덱스가 생성되면 적합한 저장 영역 매개변수를 지정하거나 인덱스를 사용할 응용 프로그램의 입출력 성능을 향상 시킬 수 있습니다.
예를 들어 테이블을 생성하기 전에 테이블의 최대 크기를 측정한다고 합시다 테이블을 생성할 때 저장 영역 매개변수를 설정하면 테이블과 데이터 세그먼트에 대해 적은 확장정도 연속적인 디스크 영역에 저장됩니다. 이는 테이블을 포함하는 디스크 입출력 작업에 필요한 시간을 감소시켜 줍니다.
단일 인덱스 입력 항목의 최대 크기는 대략 데이터 블록 크기에서 오버헤드를 뺀 값의 1.5배입니다.
테이블에서 처럼 인덱스를 생성할 때 저장 영역 매개변수를 명시적으로 설정할 수 있습니다.
인덱스에 대해 저장 영역 매개변수를 명시적으로 설정한다면 적은 수의 대규모 확장 영역에 인덱스의 테이터를 저장할 수 있습니다.
--제약 조건 비활성화 또는 삭제 전의 고려 사항
고유 키와 기본 키는 인덱스와 관련이 있으므로 UNIQUE이나 PRIMARY KEY제약 조건을 삭제하려고 할때인덱스를 삭제하고 생성하는 비용은 고려해야 합니다.
UNIQUE난 PRIMARY KEY제약 조건에 관련된 인덱스가 아주 크면 큰 인덱스를 삭제하고 재생성하는 것보다 제약 조건을 남겨둠으로써 시간을 절약할 수 있습니다.
인덱스 생성
이 곳에서는 인덱스 생성 방법에 대해 설명하고 다음 내용을 다룹니다.
- 제약 조건과 관련된 인덱스 생성
- 명시적으로 인덱스 생성
- 기존 인덱스 재생성
관련된 인덱스를 생성하는 UNIQUE나 PRIMARY KEY를 활성화하려면 테이블의 소유자는 인덱스가 포함되어야 할 테이블스페이스에 대한 할당량 또는 UNLIMITED TABLESPACE시스템 권한이 있어야 합니다.
LONG과 LONG RAW열은 인덱스될 수 없습니다.
고유 키나 기본 키에서 고유 인덱스를 생성하면 UNIQUE 키나 PRIMARY KEY무결성 제약 조건이 강제로 시행됩니다.
이 인덱스는 제약 조건이 설정되면 자동으로 생성됩니다. 즉 인덱스 생성을 위해 CREATE TABLE이나 ALTERT TABLE명령문에 다른 추가 작업을 수행할 필요가 없습니다.
인덱스 제약 조건이 정의되고 활성화될 때 그리고 정의 되었지만 비활성화되었던 제약 조건이 활성화되었을 째도 자동으로 생성됩니다.
일반적으로 CREATE UNIQUE INDEX구문을 사용하는 것보다는 고유성을 강제로 시행하는 제약 조건을 생성하는 것이 더 좋습니다.
제약 조건에 관련된 인덱스는 항상 제약조건의 이름이 따르므로 제약 조건 인덱스에 특정 이름을 지정할 필요가 없습니다.
INITIAL과 NEXT같은 인덱스에 대한 저장 영역 옵션을 지정하지 않으면 자동으로 호스트 테이블스페이스의 기본 저장 영역 옵션이 사용됩니다.
1. 제약 조건과 관련된 인덱스 생성
USING INDEX옵션을 가진 ENABLE절을 사용하여 UNIQUE키와 PRIMARY KEY제약 조건과 인덱스에 저장 영역 옵션을 설정할 수 있습니다.
다음 명령문은 PRIMARY KEY제약 조건을 정의하고 관련된 인덱스의 저장 영역 옵션을 지정합니다.
CREATE TABLE EMP (
EMPNO NUMBER(5) PRIMARY KEY, ... )
ENABLE PRIMARY KEY USING INDEX
TABLESPACE USERS
PCTFREE 0;
2. 명시적으로 인덱스 생성
SQL 명령어 CREATE INDEX를 사용하여 무결성 제약 조건 밖에 명시적으로 인덱스를 생성할 수 있습니다.
다음 명령문은 EMP테이블의 ENAME열에 대해 명명된 EMP_ENAME인덱스를 생성합니다.
CREATE INDEX EMP_ENAME ON EMP ( ENAME )
TABLESPACE USERS
STORAGE ( INITIAL 20K
NEXT 20K
PCTINCREASE 75)
PCTFREE 0;
여러 저장 영역 설정이 인덱스에 대해 명시적으로 지정됩니다.
3. 기존 인덱스 재생성
데이터 원본으로 기존 인덱스를 사용하여 인덱스를 생성할 수 있습니다. 이러한 방법으로 인덱스를 생성하면 저장 영역 특성을 변경할 수 있고 새 테이블스페이스로 이동시킬 수도 있습니다.
기존 데이터 원본에 준하여 인덱스를 재 생성하면 블록 내부의 단편화를 제거하게 됩니다. 인덱스를 삭제하고 CREATE INDEX명령어를 사용하는 것보다는 기존 인덱스를 재생성하는 것이 더 나은 성능을 부여합니다.
기존 인덱스를 재생성하려면 다음 명령문을 실행합니다.
ALTER INDEX index_name REBUILD;
인덱스 이름 바로 뒤에 그리고 다른 옵션 앞에 REBULID절이 나와야 합니다. 또한 REBUILD절과 DEALLOCATE STORAFE절을 함께 사용하면 안됩니다.
인덱스 변경
인덱스를 변경하려면 인덱스가 스키마에 포함되어 있거나 ALTER ANY INDEX 시스템 권한이 있어야 합니다.
트랜잭션 입력 항목의 매개변수를 변경하거나 저장 영역 매개변수를 바꿀 때만 인덱스를 변경할 수 있으며 인덱스 열 구조는 바꿀수 없습니다.
SQL명령어 ALTER INDEX를 사용하면 기본 키와 고유 키 무결성 제약조건을 강제로 시행하는 매개변수를 포함하여 인덱스의 저장 영역 매개변수를 변경할 수 있습니다.
예를 들어 EMP_ENAME인덱스를 변경하는 명령문은 다음과 같습니다.
ALTER INDEX EMP_NAME
INITRANS 5
MAXTRANS 10
STORAGE ( PCTINCREASE 50 ) ;
인덱스의 트랜젝션 입력 항목 설정(INITRANS, MAXTRANS)을 변경할 때 INITRANS의 새로운 설정은 나중에 할강된 데이터 블록에만 적용되는 반면 MAXTRANS의 새로운 인덱스의 모든 블록(이미 할당되었거나 나중에 할당된 블록)에 적용됩니다.
INITIAL 과 MAXEXTENTS저장 영역 매개변수는 변경될 수 없습니다. 다른 저장 영역 매개변수의 새로운설정은 나중에 할당된 저장 영역에만 영향을 줍니다.
무결성 제약 조건을 구현하는 인덱스의 경우 USING INDEX옵션을 가진 ENABLE절을 포함하는 ALTER TABLE명령문을 실행하면 저장 영역 매개변수를 조정할 수 있습니다.
예를 들어 다음 명령문은 이전 단원에서 정의한 인덱스의 저장 영역 옵션을 변경합니다.
ALTER TABLE EMP
ENABLE PRIMARY KEY USING INDEX
PCTFREE 5;
인덱스 영역 사용 모니터링
인덱스에서 키 값이 자주 삽입, 갱신, 삭제되면 인덱스는 시간이 경과함에 따라 영역을 효율적으로 사용할 수도 있지만 사용하지 못할 수도 있습니다.
우선 인덱스의 구조를 분석한 다음 INDEX_STATS뷰를 질의하여 인덱스의 효율적인 영역 사용을 규칙적으로 모니터할 수 있습니다.
SELECT PCT_USED FROM SYS.INDEX_STATUS WHERE NAME = 'index_name';
인덱스 영역 사용 백분율은 인덱스 키 삽입, 갱신, 삭제 횟수에 따라 달라집니다.
인덱스 검증, PCT_USED검서 인덱스 삭제 및 재생성 등의 작업을 여러 번 수행하여 인덱스의 평균 영역 사용 효율성을 관리할 수 있습니다.
인덱스 영역 사용이 평균 이하로 떨어지면 인덱스를 삭제하고 재생성하여 인덱스 영역을 줄입니다.
인덱스 삭제
인덱스를 삭제하려면 인덱스가 스키마에 포함되어 있거나 DROP ANY INDEX시스템 권한이 있어야 합니다.
다음과 같은 경우에 인덱스를 삭제할 수 있습니다.
인덱스가 더 이상 필요하지 않을 경우
인덱스에 관련된 테이블에 질의를 수행했을 때 기대되는 성능향상을 제공하지 못한 경우
응용 프로그램이 데이터 질의에 인덱스를 사용하지 않을 경우
인덱스를 사용할 수 없게 되어 재구축 전에 삭제해야 할 경우
인덱스가 너무 단편화되어 재구축 전에 삭제해야 할 경우
인덱스를 삭제할 때 인덱스 세그먼트의 모든 확장 영역은 해당되는 테이블스페이스로 되돌려지며 테이블스페이스의 다른 개체에 사용할 수 있습니다.
인덱스를 삭제하는 방법은 CREATE INDEX명령문으로 인덱스를 명시적으로 생성했는지 또는 테이블에서 키제약 조건을 정의하여 암시적으로 생성했는지에 따라 다릅니다.
주 : 테이블이 삭제되면 관련된 모든 인덱스도 자동으로 삭제됩니다.
UNIQUE키나 PRIMARY KEY제약 조건이 설정된 인덱스만을 삭제할 수는 없습니다.
제약 조건과 관련된 인덱스를 삭제하려면 제약 조건 자체를 사용할 수 없게 하거나 삭제해야 합니다.
DROP INDEX index_name;
[출처] 인덱스 관리에 대한 지침 사항 |작성자 오라클
