인덱스 재구성 및 삭제(Index Rebuild, Drop), 인덱스 숨기기(Index Invisible)
12.5. 인덱스 재구성 및 삭제
데이터 삽입, 삭제, 수정등이 일어나면 오라클 내부적으로 원본 데이터뿐 아니라 인덱스도 갱신을 시켜줍니다. 이때 특정한 부분에 값이 집중되어 입력 되는 경우등이 있다면 인덱스는 최초 인덱스를 생성할 때의 균형잡힌 트리(Balanced Tree)의 모양이 아닌 트리가 한쪽으로 기울어 지는 현상이 생길 수 있습니다. 이러한 경우 효율적인 인덱스 관리를 위해 인덱스를 재구성 또는 삭제 후 재생성 해야 합니다.
인덱스를 재구성하고 삭제하는 실습을 해보겠습니다.
실습
인덱스를 재구성 합니다.
ename 컬럼에 생성된 IDX_EMP_ENAME 인덱스를 재구성 하세요. |
ALTER INDEX idx_emp_ename REBUILD;
[실행결과]
Index IDX_EMP_ENAME이(가) 변경되었습니다.
인덱스를 삭제 합니다.
ename 컬럼에 생성된 IDX_EMP_ENAME 인덱스를 삭제 하세요. |
DROP INDEX IDX_EMP_ENAME;
[실행결과]
Index IDX_EMP_ENAME이(가) 삭제되었습니다.
12.6. 인덱스 숨기기
쿼리작업을 할 때 가끔은 인덱스가 없다면 SQL문의 실행계획이 어떻게 될까? 고민 하면서 인덱스를 DROP 하고 실행 계획을 본 후 다시 INDEX를 생성하여 실행 계획을 본 적이 있을 것 입니다.
적은 량의 데이터를 가진 테이블이라면 문제가 아닐텐데 많은 양의 데이터가 있는 테이블 이라면 삭제하고 생성하는데 부담이 있습니다. 이때 사용할 수 있는 것이 Oracle 11g에서 새로 소개된 INVISIBLE INDEX인데, 인덱스를 INVISIBLE 상태로 만들어서 오라클에서 명시적으로 사용하지 말라고 지시를 할 수 있습니다.
또한 대용량의 데이터를 테이블에 입력하는 경우 원본 테이블에 데이터가 입력되면 오라클이 인덱스를 자동으로 갱신하는데 이러한 작업은 대용량 데이터를 입력할 때 성능 저하의 원인이 될 수 있습니다. 이때 인덱스를 잠시 안보이도록 숨겨두고 작업이 끝난 후 보이도록 하여 재구성하는 것이 효율적 입니다.
ename 컬럼에 인덱스가 있을 때 쿼리를 실행 했을 때와 INVISIBLE 상태로 만든 후 쿼리를 실행했을 때의 실행 계획을 비교해 보겠습니다. 또한 INVISIBLE 상태로 되어 있는 인덱스를 딕셔너리 뷰를 통해 확인해 보겠습니다.
실습
실습을 위한 인덱스를 생성 합니다.
EMP 테이블의 ename 컬럼에 오름차순 인덱스를 생성 하세요. |
CREATE INDEX IDX_EMP_ENAME ON EMP(ENAME);
[실행결과]
Index IDX_EMP_ENAME이(가) 생성되었습니다.
WHERE절에 ename 컬럼을 출현시켜 인덱스를 경유하는지 확인해 보겠습니다.
EMP 테이블에서 ‘SMITH’ 사원의 사번, 이름을 조회하세요. |
SELECT EMPNO, ENAME FROM EMP WHERE ENAME = 'SMITH';
[실행결과]
| EMPNO | ENAME |
1 | 7369 | SMITH |
[실행계획]
ename 컬럼에 생성된 IDX_EMP_ENAME 인덱스를 경유함을 확인할 수 있습니다.
인덱스를 사용하지 않도록 하기 위해 숨겨 봅니다.
ename 컬럼에 생성된 인덱스를 숨기세요. |
ALTER INDEX IDX_EMP_ENAME INVISIBLE;
[실행결과]
Index IDX_EMP_ENAME이(가) 변경되었습니다.
WHERE절에 ename 컬럼을 출현시켜 인덱스를 경유하는지 확인해 보겠습니다.
EMP 테이블에서 ‘SMITH’ 사원의 사번, 이름을 조회하세요. |
SELECT EMPNO, ENAME FROM EMP WHERE ENAME = 'SMITH';
[실행결과]
| EMPNO | ENAME |
1 | 7369 | SMITH |
[실행계획]
ename 컬럼에 생성된 인덱스가 숨겨져 있으므로 경유하지 않았습니다.
INVISIBLE 상태에 있는 IDX_EMP_ENAME 인덱스를 딕셔너리 뷰에서 확인해 보겠습니다.
IDX_EMP_ENAME 인덱스가 INVISIBLE 상태에 있는것을 USER_INDEXES 뷰에서 확인하세요. |
SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES
WHERE INDEX_NAME = 'IDX_EMP_ENAME';
[실행결과]
| INDEX_NAME | VISIBILITY |
1 | IDX_EMP_ENAME | INVISIBLE |
#인덱스삭제, #인덱스Rebuild, #dropindx, #alterindex, #오라클인덱스, #인덱스, #인덱스강좌
