뷰에 인덱스를 만들 수 있을까? 뷰는 미리 생성된 쿼리일 뿐. 뷰를 인덱싱하고자 한다면, 베이스 테이블을 인덱싱하라.
NULL과 인덱스는 함께 사용되는가?
B*Tree 인덱스는 인덱스를 구성하는 모든 컬럼이 NULL인 엔트리를 저장하지 않는다.
비트맵 인덱스와 클러스터 인덱스는 제외.
x is null 의 조건도 하나 이상의 not null 컬럼이 포함된 결합인덱스가 있다면 해당 인덱스를 사용.
참조 키에는 인덱스가 생성되어야 하는가?
인덱스가 없는 참조 키는 데드락의 가장 큰 이유 부모 테이블의 키본 키를 수정하거나 부모 로우를 제거하려고 할 때 자식 테이블에 락을 걸기 때문
문제 발생 사례
on delete cascade 문을 가지고 자식 테이블에 인덱스가 없을 때. dept 테이블의 로우를 삭제 할때 emp 테이블의 전체 로우를 스캔, 다수의 로우를 삭제 한다면 삭제시 마다 스캔 하는 문제 발생.
부모 테이블의 조건으로 자식 테이블을 추출할 때.
참조 키에 인덱스가 필요 없는 경우
부모 테이블을 삭제하지 않는다.
부모 테이블의 유니크 또는 기본 키 값에 수정이 발생하지 않는다.
부모 테이블과 자식 테이블을 조인하지 않는다.
해당 인덱스를 왜 사용하지 않을까?
Case1 : 조건절의 컬럼이 인덱스의 선두 컬럼을 이용하지 않는 경우.
이 경우에는 모든 인덱스 엔트리를 조사해야 한다. 일반적으로 전체 테이블 스캔을 선택 할 것이다.
인덱스 스킵 스캔은 인덱스의 선행 컬럼의 구별된 값의 수가 적고 옵티마이저가 이것을 알고 있을 때 잘 작동.
Case2 : select count(*) from t (t에 b*tree 인덱스를 가진 경우)
모두가 NULL인 인덱스 키 엔트리는 인덱스에 생성되지 않기 때문에, 옵티마이저는 로우를 세기 위해 인덱스를 사용하는 것은 잘못된 것이라 인식.
Case3 : 일반 인덱스 생성 후 조회 하고자 하는 조건의 컬럼에 함수를 사용한 경우
Case4 : 암시적 형변환에 의한 경우
3,4의 케이스와 같이 조건절에 사용되는 모든 데이터베이스 컬럼에는 가능하면 함수를 제거 하라.
Case5 : 인덱스를 사용하는 것이 더 느린 경우
CBO 환경에서는 옵티마이저가 효과적이라고 판단할 때만 인덱스를 사용.
Case6 : 오랫동안 테이블의 통계정보를 수집하지 않는 경우
처음에는 작은 테이블이었으나 대용량 테이블이 될때 이전에는 인덱스가 필요 없었지만 지금은 필요하게 되었다. 통계정보를 갱신하면 인덱스를 사용하게 될 것 이다.
오해 : 인덱스 공간은 절대 재사용되지 않는다.
오라클은 인덱스의 공간을 정리해서 크기를 줄이려는 시도는 하지 않는다. (alter index rebuild, coalesce명령어를 통해 정리 가능)
인덱스를 재사용할 수 있는 로우가 삽입되면 인덱스의 공간은 재사용된다.
인덱스 블록이 비워지면 해당 블록은 인덱스 구조에서 제거될 수 있고, 또한 재사용될 수 있다. 블록이 완전히 비워져야만 freelist 블록으로 반환될 뿐 아니라 인덱스 엔트리를 하나라고 가지는 블록들은 공간이 남아 있더라도freelist에 존재하지 않는다.
오해 : 가장 변별력이 있는 요소가 선두에 와야 한다.
똑같다 차이 없다.
사용되는 쿼리에 의해 결정해야 한다. (인덱스를 어떻게 사용하는가에 따라 정의되어야 한다)
차라리 키 압축에 있어서는 변별력이 낮은 컬럼을 선두에 두는 것이 용량(I/O)에 대한 이점을 취할 수 있다. (오라클 5에서 인데스 압축을 구현하는 방법에 기인하여 변별력이 좋은 컬럼의 위치에 대한 해당 논쟁이 있었다. 버전 6에서 로우 레벨 락 추가로 해당 특징 제거)