PLT 2.2 Index

PLT 2.2 Index

인덱스는 테이블이나 클러스트에서 쓰여지는 선택적인 객체로서, 오라클 데이터베이스 테이블내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조입니다.

• 자동 인덱스 : 프라이머리 키 또는 uinque 제한 규칙에 의해 자동적으로 생성되는 인덱스 입니다.

• 수동 인덱스 : CREATE INDEX 명령을 실행해서 만드는 인덱스들 입니다.

인덱스의 분류

1. 물리적 분류 : 비트리(B-tree) 인덱스와 비트맵(Bitmap) 인덱스

2. 논리적 분류 : 단일열 인덱스와 결합(Concatenated) 인덱스,

Unique 인덱스와 NonUnique 인덱스

물리적 분류

B-Tree index

• 인덱스의 칼럼이 여러개 동일한 키갑을 가지고 있을 경우 (ex 부서번호)

• 널값은 포함하지 않는다.

• 모든 칼럼이 같은 테이블 스페이스 안에 있을 때Restricted Rowid를 사용할 수 있다.

• 삭제된 리프모드가 20프로 이상일 때는 칼럼을 재구성해주어라

• 인덱스 제일 위는 root로 인덱스의 다음 레벨을 가리키는 엔트리를 포함하고 있습니다.

• 다음 레벨은 branch 블록으로 마찬가지로 다음 레벨의 인덱스 블록을  가리킵니다.

• 최하층 레벨은 leaf노드로 테이블의 행을 가리키는 인덱스 엔트리를  포함하고 있습니다.

• leaf블록은 키 값의 내림차순은 물론 오름차순의 키 값으로 인덱스를 검색하는데 편리하도록 양방향(doubling)으로 연결(link)되어 있습니다.

• Leaf Block에만 ROWID Bolck이 있습니다.

create index hr.employees_last_name_idx on hr.employees(last_name) pctrfee 30 storge(initial 200k next 200k pcffree = 30 storage (inittal 200k nkw 200k pctincrease 0 maxextents = 50

Bitmap index

• 테이블이 많은 행을 가지며 키 열은 적은 분포도(cardinality)를 가질 때, 즉 열이 갖는 서로 다른 값이 몇 개 안 될 때. 예를 들어 여권 기록을 포함하고 있는 테이블의 성별 열이나 결혼 여부 열의 경우에는 B-트리 인덱스 보다는 비트맵 인덱스가 더 유리할 것입니다.

• 질의문이 OR 연산자를 포함하는 여러 개의 WHERE 조건을 자주 사용할 때 유리합니다.

• 키 열이 읽기 전용이거나 갱신 작업을 적게 할 때 즉.Decision Support System과 같은 조회전용 업무나 OLTP업무 비중이 작은 업무에서 적합합니다.

• INSERT, UPDATE, DELETE와 같은 Query에서는 무의미 합니다.

• 분포도가 좋은(Unique성:값의종류가 많다) 값에 대한 Index는 불리 합니다.

• Bitmapped Index 적용 Column의 후보는 대개 5가지 정도 이내의 값을 갖습니다

비트맵(BitMap) 인덱스와 비트리(B-tree) 인덱스의 비교
B-tree 인덱스	비트맵 인덱스
큰 분포도(Cardinality)를 갖는 테이블에 적합	적은 분포도를 갖는 테이블에 적합
비교적 키의 갱신 비용이 적음	갱신 비용이 매우 큼
OR사용 Query문에 비 효율적	OR사용 Query문에 효율적
OLTP에 유용	DSS에 유용

논리적 분류

단일열 인덱스

-     단일 열 인덱스는 인덱스 키에 오직 하나만의 열을 갖습니다.

-     예를 들면 emp테이블의 empno만 인덱스로 지정하는 방법 입니다.

SQL> CREATE INDEX emp_empno_indx           ON emp(empno);

결합(Concatenated(=Composite)) 인덱스

-     하나 이상의 테이블 컬럼을 하나의 인덱스로 묶는 방법 입니다.

-     예를 들면 emp테이블의 empno와 ename을 하나의 인덱스로 지정하는 방법 입니다.

SQL>CREATE INDEX emp_empno_ename_indx         ON emp(empno, ename);

Unique 인덱스

-     Unique 인덱스는 인덱스를 사용한 컬럼의 중복 값들을 포함하지 않고 사용할 수 있는 장점이 있습니다.

-     프라이머리키 와 Unique 제약 조건 시 생성되는 인덱스는 Unique 인덱스입니다.

SQL>CREATE UNIQUE INDEX emp_empno_indx         ON emp(empno);

Non Unique 인덱스

-     일반적으로 Unique 인덱스가 아닌 인덱스들 입니다.

-     Non-Unique 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질 수 있습니다.

SQL>CREATE INDEX emp_ename_indx         ON emp(ename);

인덱스 컬럼의 선정

1. 분포도가 좋은 컬럼은 단독적으로 생성하여 활용도를 향상 시킵니다.

2. 자주 조합되어 사용되는 경우는 결합인덱스를 생성 합니다.

3. 엑세스 경우의 수를 만족할 수 있도록 각 인덱스간의 역할을 분담 합니다.

4. 가능한 수정이 빈번하지 않는 컬럼을 선정 합니다.

5. 가능한 한 컬럼이 여러 인덱스에 포함되지 않도록 합니다.

6. 기본키 및 외부키 (조인의 연결고리가 되는 컬럼)가 되는 컬럼을 선정합니다.

7. 결합 인덱스의 컬럼순서 선정에 주의 (사용빈도,유일성,SORT유형,부분범위처리 등을 고려)해야 합니다. 첫 번째 컬럼은 항상 사용되는 컬럼을 선택 해야 합니다

8. 실제 조사된 엑세스 종류를 토대로 선정 및 검증 해야 합니다.

인덱스가 사용되지 않는 경우

1. 인덱스 컬럼이 비교되기 전에 변형이 일어날 경우

2. 부정형(NOT, <>)으로 조건을 기술한 경우

3. 인덱스 컬럼이 NULL로 비교되는 경우(NULL, NOT NULL의 사용)

4. 옵티마이져가 특정 인덱스의 사용을 취사 선택 할 경우 사용되지 않을 수 있음이다.

인덱스 생성시 고려사항

1. 새로 추가된 인덱스는 기존 엑세스 경로에 영향을 미칠 수가 있습니다.

2. 지나치게 많은 인덱스는 많은 오버헤드를 발생 시킴니다.

3. 넓은 범위를 인덱스로 처리시 많은 오버헤드 발생 시킵니다.

4. 옵티마이져를 위한 통계 데이타를 주기적으로 갱신(ANALYZE) 합니다.

5. 인덱스를 위한 추가적인 저장공간이 필요 합니다.

6. 인덱스의 개수는 테이블의 사용형태에 따라 다릅니다. (검색위주형, 동시 다량처리형, 단일 처리형, 배치처리형)

7. 분포도가 양호한 컬럼도 처리 조건(범위)에 따라 분포도가 나빠 질 수 있습니다.

8. NULL 값을 가지는 컬럼의 인덱스 ENTRY는 생성되지 않습니다.

9. 조인(join)시에 인덱스가 사용되지 않으면 엑세스 경로는 무조건 특정 형태로 고정 되어 집니다.

인덱스 재구축 및 병합

재구축이 필요한 상황

-     기존 인덱스를 다른 테이블스페이스로 이동해야 할 경우

-     인덱스가 삭제된 엔트리를 많이 포함하고 있을 경우

-     기존의 정규 인덱스가 reverse 키 인덱스로 변환 되어야 할 경우(B-Tree Index만 지원)

인덱스 재구축의 특성

-     새 인덱스는 기존 인덱스를 데이터 소스로 사용하여 구축됩니다.

-     기존 인덱스를 사용하여 더 나은 성능을 얻기 위해 인덱스를 구축할 때는 정렬이 필요 없습니다.

-     이전 인덱스는 새 인덱스가 구축된 후 삭제됩니다.

-     재구축하는 동안 이전 인덱스와 새 인덱스 모두를 각각의 테이블스페이스에 수용할 충분한 공간이 필요합니다.

-     재구축된 인덱스는 삭제된 엔트리는 전혀 포함하고 있지 않게 됩니다. 따라서 인덱스는 보다 효율적으로 공간을 사용하고 있습니다.

-     새 인덱스가 구축되고 있는 동안 질의문은 기존 인덱스를 계속 사용 할 수 있습니다.

인덱스의 재구축 SQL>ALTER INDEX emp_ename_idx REBUILD TABLESPACE idx;

인덱스의 병합

부적당한 크기 지정이나 크기의 증가로 인해 인덱스 단편화가 생기는 경우 인덱스를 재구축하거나 병합할 수 있습니다. 이러한 작업을 수행하기 전에 각 옵션의 비용과 이익을 비교하여 상황에 가장 적합한 하나의 작업을 선택 해야 합니다.

인덱스 병합 예제 SQL> ALTER INDEX emp_ename_idx COALESCE;

재구축과 병합의 비용과 이익

재구축	병합
인덱스를 다른 테이블스페이스로 빠르게 이동	인덱스를 다른 테이블스페이스로 이동 못함
높은 비용.더 많은 디스크 영역 필요	낮은 비용.더 많은 디스크 영역 불필요
새로운 트리 생성,적용 가능한 경우 높이 축소	최하위 블록을 동일한 트리의 분기와 병합
원래 인덱스를 삭제하지 않고 저장 영역과 테이블 스페이스 매개변수를 빠르게 변경	사용을 위해 인덱스 최하위 블록을 빠르게 비움

인덱스의 유효성 검사

인덱스에 대한 정보를 가지고 있는 INDEX_STATS 데이터 사전에 기록 됩니다.

인덱스 분석 SQL>ANALYZE INDEX board_pk VALIDATE STRUCTURE;

ANALYZE INDEX명령을 수행 한 후 INDEX_STATS를 조회 합니다.

SQL>SELECT blocks,  btree_space, used_space,  pct_used "사용율(%)",                    lf_rows, del_lf_rows "삭제행"        FROM INDEX_STATS; BLOCKS BTREE_SPACE USED_SPACE  사용율(%) LF_ROWS 삭제행  5        23984   12489 53          892 51 1 row selected.

인덱스가 삭제된 행이 많으면 인덱스를 재구축 해야 합니다. 예를 들어 LF_ROW에 대한DEL_LF_ROWS의 비가 30%를 초과하면 인덱스를 재구축 해야 합니다.

인덱스 삭제

다음과 같은 경우에 인덱스를 삭제 합니다.

-         인덱스가 더 이상 필요하지 않을 경우

-         인덱스에 관련된 테이블에 질의를 수행했을 때 기대되는 성능 향상을 제공하지 못한 경우

-         응용 프로그램이 데이터 질의에 인덱스를 사용하지 않을 경우

-         인덱스를 사용할 수 없게 되어 재구축 전에 삭제해야 할 경우

-         인덱스가 너무 단편화되어 재구축 전에 삭제해야 할 경우

인덱스를 삭제할 때 인덱스 세그먼트의 모든 확장 영역은 해당되는 테이블스페이스로 되돌려지며 테이블스페이스의 다른 객체에 사용할 수 있습니다. 인덱스를 삭제하는 방법은 CREATE INDEX 문으로 인덱스를 명시적으로 생성했는지 또는 테이블에서 키 제약 조건을 정의하여 암시적으로 생성했는지에 따라 다릅니다. 테이블이 삭제되면 관련된 모든 인덱스도 자동으로 삭제됩니다. UNIQUE 키나 PRIMARY KEY 제약 조건이 설정된 인덱스만 삭제할 수는 없습니다.

제약 조건의 관련 인덱스를 삭제하려면 제약 조건 자체를 사용할 수 없게 하거나 삭제해야 합니다.

SQL>DROP INDEX  emp_ename;