좀더 나은 나를 위하여

테이블에 무조건 인덱스를 만드는 것은 비효율적이다.

• 인덱스를 만들거나 수정시 DB 성능 지연 발생

• 분포도가 좋지 않은 컬럼에 인덱스를 생성하더라도 성능에 영향이 적음.

• INSERT, UPDATE, DELETE 시에 성능 저하

• 저장공간의 낭비

과다한 인덱스 생성으로 실제 테이블공간과 비슷하거나 더 많은 저장소 공간을 차지하는 배보다 배꼽이 더 큰 경우도 발생되고 있다.

만일 총 직원수가 3만명인 사원테이블(BIG_EMP)에

메니져사원번호(MGR )가 7698 인 직원이 10,000 명이고,

부서번호(DEPTNO )가 60 인 직원이 1,200 명인 상황에서 

SELECT PAYMENT FROM BIT_EMP 

  WHERE MGR='7698' AND DEPTNO='60';

위와 같은 쿼리에서 만일 메니져사원번호(MGR) 컬럼만 인덱스가 생성되어있다면

DB는 인덱스를 통하여 메니져사원번호가 '7698' 인 레코드를 검색후 

해당 레코드의 부서번호가 60인지 판단하는 식으로

조회할 것이다.

하지만 이러한 인덱스컬럼의 조건절이 대부분의(15%이상) 레코드의 조건에 매칭되는 경우에서의

인덱스 스캔방식의 조회는 비효율적이다.

이유는 인덱스 스캔방식에서는 실제테이블 레코드를 조회할때 Single Block I/O 를 사용하기 때문이다.

차라리 인덱스컬럼을 무시하고 전체 테이블을 읽는 풀 스캔이 더 효율적이다.

풀스캔은 Multi Block I/O 를 사용한다.

Single Block I/O

인덱스를 사용하여 검색(인덱스 스캔)을 하게 되면 한번의 I/O 요청에 한 블록씩 데이터를 읽는다.

1. 인덱스 테이블에서 조건절 검색 MGR='7698' 
2. ROWID 로 테이블 검색 (1만 ROW , 1만번 I/O)
3. 조건절 검색 ( DEPTNO='60')

Multi Block I/O

전체 데이블 검색(풀 스캔)은 데이터를 읽을 때 한번의 I/O 요청으로 여러 블록을 한꺼번에 읽는다.

1. 3만 / 150 = 2백 번 IO
2. 조건절 검색 ( DEPTNO='SALES')

풀 스캔을 유도하는 방법

인덱스가 걸린 컬럼을 가공

SELECT SAL FROM BIG_EMP 

WHERE DEPTNO='60' AND MGR || ‘’ ='7698‘;

힌트를 사용

SELECT /*+ FULL(BIG_EMP) */

  SAL FROM BIG_EMP 

WHERE DEPTNO='60' AND MGR='7698';

만일 총 직원수가 3만명인 사원테이블(BIG_EMP)에

메니져사원번호(MGR )가 7698 인 직원이 10,000 명이고,

부서번호(DEPTNO )가 60 인 직원이 1,200 명인 상황에서 

위의 조건에 만족하는 직원의 리스트를 조회하려고 아래와 같은

쿼리를 만들려 한다면,

SELECT * FROM BIG_EMP 

WHERE MGR=‘7698’ AND DEPTNO=’60’;

• MGR='7698' AND DEPTNO='60'
• DEPTNO='60' AND MGR='7698'

1. 가장 나중에 생성한 인덱스가 있는 컬럼부터 파싱
2. AND 조건이면 WHERE 절에서 가까운 순서대로, OR 조건이면 맨오른쪽에서 부터 순서대로

위의 순서대로 파싱을 한다.

즉. 대부분의 경우 컬럼의 순서에 신경쓸 필요가 없으며 구지 신경을 쓴다면 맨뒤에서 부터 WHERE 조건을 나열하면 된다.