결합 인덱스를 생성할 때에는 여러 가지 사항을 고려해야 한다. 결합 인덱스 생성의 고려사항을 준수하지 않는다면 결합 컬럼 인덱스는 무용지물이 될 수도 있기 때문이다.

지난 강의에서 결합 인덱스의 우선순위 중 랜덤 액세스에 대해확인해 보았다. 이번 호에서는 이 중 추출 랜덤 액세스와 정렬 랜덤 액세스를 제거 또는 감소시키는 방법에 대해자세히 확인해 보자.

많은 사이트들이 단일 컬럼 인덱스만을 고집하고 있다. 결합 인덱스를 생성하는 경우에도 두 개 정도의 컬럼으로만 구성하는 경우도 많다.

그렇다고 무조건 많은 개수의 컬럼으로 결합 컬럼 인덱스를 생성하는 것이 좋은 것은 아니지만 그 만큼 인덱스의 유연성은 증가하게 된다.

인덱스의 유연성은 매우 중요하다. 유연성에 의해 해당 테이블에 10개의 인덱스가 필요할 수도 있고 또는 5개의 인덱스만으로 모든 액세스 경로를 수용할 수도 있다.

따라서, 최소의 인덱스로 모든 액세스 경로를 최적화하기 위해서는 결합 컬럼 인덱스의 선정에 신중해야 한다.

추출 랜덤 액세스와 정렬 랜덤 액세스의 성능 저하를 이해하자

결합 컬럼 인덱스 선정에서 랜덤 액세스는 매우 중요한 항목이 된다. 이미 이전 강의에서 랜덤 액세스에 대한 개념과 확인랜덤 액세스는 언급했다.

이번에는 예제를 통해 추출 랜덤 액세스와 정렬 랜덤 액세스의 성능 저하를 확인해 보자.

확인 랜덤 액세스와 정렬 랜덤액세스의 성능 저하를 이해하기 위해 추출 랜덤 액세스와 정렬 랜덤 액세스의 정의부터 확인해 보자.

확인 랜덤 액세스와 정렬 랜덤 액세스는 다음과 같이 정의될 수 있다.

• - 추출 랜덤 액세스 : SELECT 절의 데이터를 추출하기 위해 인덱스 액세스 후 테이블을 액세스해야 하는 랜덤 액세스
• - 정렬 랜덤 액세스 : ORDER BY 절 또는 GROUP BY 절 등에 의해 인덱스 액세스 후 테이블을 액세스해야 하는 랜덤 액세스

지난 강의에서 언급한 확인 랜덤 액세스와의 차이는 무엇인가? 다음의 표를 확인해 보자.

표와 같이 확인 랜덤 액세스는 정렬 랜덤 액세스나 추출 랜덤 액세스와 차이점이 많지는 않다.

단지, 확인 랜덤 액세스는 조건에 맞지 않는다면 추출되는 데이터를 감소시킬 수 있기때문에 더 큰 성능 저하를 발생시키게 된다는 점이 다르다.

확인 랜덤 액세스의 성능 저하를 발생시키는 단일 블록 I/O의 증가는 정렬이나 추출 랜덤 액세스에서도 동일하게 발생하므로 성능 저하를 발생시킬 수 있다.

결국, 랜덤 액세스 중에서 추출되는 데이터를 감소시키는 확인 랜덤 액세스를 감소시키는 것이 제일 중요하지만 정렬또는 추출 랜덤 액세스 또한 감소시켜야 하는 성능 저하의 요소라는 것이다.

추출 랜덤 액세스와 정렬 랜덤 액세스를 감소시키자

추출 랜덤 액세스와 정렬 랜덤 액세스는 어떻게 감소시켜야 하는가?

이와 같은 랜덤 액세스를 모두 감소시키려면 잘못하면 하나의 인덱스에 모든 컬럼을 다 추가해야 하는 극단적인 상황이 발생할 수도 있을 것이다.

그렇다면 과연 어떻게 추출 랜덤 액세스와 정렬 랜덤 액세스를 최소화 시킬 수있을까?

정렬 랜덤 액세스 감소 확인

첫 번째로 정렬 랜덤 액세스의 감소를 확인해 보자.

정렬랜덤 액세스를 제거하기 위해서는 ORDER BY 절이나 GROUP BY 절의 컬럼을 인덱스에 추가해야 한다.

보통의경우 ORDER BY 절과 GROUP BY 절에는 많은 컬럼을 사용하지 않기 때문에 인덱스의 끝에 해당 컬럼을 추가하는 것은 어렵지 않을 것이다.

ORDER BY 절 또는 GROUP BY 절의 컬럼을 인덱스에 추가해야 하기 때문에 정렬 랜덤 액세스의 감소보다는 정렬 랜덤 액세스의 제거가 발생할 것이다.

추출 랜덤 액세스 감소 확인

두 번째로 추출 랜덤 액세스의 감소를 확인해 보자. 추출 랜덤 액세스는 정렬 랜덤 액세스처럼 간단한 문제는 아니다.

추출 랜덤 액세스는 SELECT 절에 의해 발생하게 되며 추출랜덤 액세스를 제거하기 위해서는 SELECT 절의 모든 컬럼을 인덱스에 추가해야 할 것이다. 잘못하면 매우 많은 컬럼으로 구성된 인덱스가 생성될 수도 있을 것이다.

그렇다면 추출 랜덤 액세스를 감소시키기 위해 어떻게 해야 하는가? 다음의세 가지를 고려해야 할 것이다.

• - 매우 자주 사용하는 SQL에 대해서는 추출 랜덤 액세스 제거 고려
• - 하나의 인덱스로 많은 SQL에 대해 추출 랜덤 액세스를 제거할 수 있다면 컬럼이 많더라도 인덱스에 컬럼 추가 고려
• - 인라인 뷰를 통해 데이터가 감소하는 경우 ROWID 이용하여 추출 랜덤 액세스 감소 고려

처리 데이터의 건수와 결과 데이터의 건수 비교

첫 번째와 두 번째의 경우는 많이 사용하는 SQL 또는 하나의 인덱스로 많은 SQL의 추출 랜덤 액세스를 제거할 수 있다면 많은 컬럼으로 인덱스를 구성하는 것도 고려할 수 있다는 의미이다.

세 번째는 다음 예제를 통해 확인해 보자.

SELECT 카드번호, 거래일자, 거래구분, 사용액
  FROM 
     ( SELECT 카드번호, 거래일자, 거래구분, 사용액
         FROM 거래내역
        WHERE 카드번호 = '111'
        ORDER BY 거래일자
     )
 WHERE ROWNUM <= 5;

예제에서 인라인 뷰의 결과는 1,000건이라고 가정하자. 물론, 최종 결과는 ROWNUM 연산자에 의해 5건의 데이터가 결과로 추출될 것이다.

카드번호+거래일자 인덱스를 생성한다면 다른 랜덤 액세스는 발생하지 않으며 추출 랜덤 액세스는 1,000번이 발생할 것이다.

다음과 같이 SQL을 수행한다면 어떻게 되겠는가?

SELECT 카드번호, 거래일자, 거래구분, 사용액
  FROM 
     ( SELECT ROWID RID
         FROM 거래내역
        WHERE 카드번호 = '111'
        ORDER BY 거래일자
     ) A,
       거래내역 B
 WHERE A.RID = B.ROWID
   AND ROWNUM <= 5;

이런 SQL을 수행한다면 A 인라인 뷰는 카드번호+거래일자 인덱스를 이용한다면 ROWID 값은 인덱스에 존재하므로 A 인라인 뷰는 인덱스 스캔으로만 원하는 데이터를 모두 추출할 수 있게 된다.

따라서 모든 랜덤 액세스는 발생하지 않게되며 A 인라인 뷰와 거래내역 테이블은 ROWNUM에 의해 5번만 조인을 수행하게 되어 거래내역 테이블은 5번의 랜덤 액세스가 발생하게 된다.

앞의 SQL과 뒤의 SQL은 동일한 결과를 추출하지만 랜덤 액세스의 횟수는 1,000번에서 5번으로 감소하게 되었다.

이처럼 처리 데이터의 건수와 결과 데이터의 건수를 비교하여 감소하는 형태의 SQL이라면 이와 같은 방법을 사용하여 추출 랜덤 액세스를 감소시킬 수 있다.

추출 랜덤 액세스 또한 감소시킨다면 최적의 성능을 기대할 수 있을 것이다.

이와 같은 방법을 이용하여 추출 랜덤 액세스를 제거하거나 감소시키는 활동이 필요하다. 랜덤 액세스의 감소는 SQL 최적의 성능을 보장할 수 있는 방법 중 하나라는 사실을 명심하길 바란다.

다음 강의에서는 정렬 제거를 위한 인덱스 컬럼 선정에 대해 이야기 해보자.