추출 랜덤 액세스와 정렬 랜덤 액세스

• 무조건 많은 개수의 칼럼으로 결합 인덱스를 생성하는 것은 아니지만 그만큼 인덱스의 유연성 증가
• 유연성에 의해 테이블에 10개의 인덱스가 필요할 수도 있고 5개의 인덱스로 모든 액세스 경로를 수용 할 수도 있음
• 최소의 인덱스로 모든 액세스 경로를 최적화하기 위해서는 결합 칼럼 인덱스의 선정에 신중해야함

 

추출 랜덤 액세스 : SELECT절의 데이터를 추출하기 위해 인덱스 액세스 후 테이블을 액세스해야 하는 랜덤 액세스

정렬 랜덤 액세스 : ORDER BY절, GROUP BY 절 등에 의해 인덱스 액세스 후 테이블을 액세스 해야하는 랜덤 액세스

 

종류	발생위치	추출데이터
확인 랜덤 액세스	WHERE절/ HAVING절	감소 또는 동일
정렬 랜덤 액세스	ORDER BY절/ GROUP BY절	동일
추출 랜덤 액세스	SELECT절	동일

-> 확인 랜덤 액세스는 조건에 맞지 않으면 추출되는 데이터를 감소시켜 더 큰 성능 저하를 발생시킴

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

 

-> 확인 랜덤 액세스를 감소 시키는것이 제일 중요하지만 정렬, 추출 랜덤 액세스도 성능 저하의 요소

 

추출 랜덤 액세스, 정렬 랜덤 액세스 감소

이와 같은 랜덤 액세스를 모두 감소시키려고하면 잘됫하면 하나의 인덱스에 모든 칼럼을 다 추가해야 하는 극단적 상황 발생

 

정렬 랜덤 액세스 감소 확인 

• 정렬랜덤 액세스를 제거하기 위해선 ORDER BY절이나 GROUP BY절의 칼럼을 인덱스에 추가 해야함
• 보통의 경우 ORDER BY절과 GROUP BY절에는 많은 칼럼을 사용하지 않아 인덱스의 끝에 해당 칼럼을 추가하는 것은 어렵지 않음
• ORDER BY절 또는 GROUP BY절의 칼럼을 인덱스에 추가해야 하기 때문에 정렬 랜덤액세스의 감소보다 제거가 발생

 

추출 랜덤 액세스 감소 확인

• 추출 랜덤 액세스는 SELECT절에서 일어나므로 제거하기 위해서는 SELECT 절의 모든 칼럼을 인덱스에 추가해야 함
• 잘못하면 매우 많은 칼럼으로 구성된 인덱스가 생성될 수도 있음
• 추출 랜덤 액세스 감소를 위해서는 3가지 고려해야함
  1. 매우 자주 사용하는 SQL에 대해서는 추출 랜덤 액세스 제거 고려
  2. 하나의 인덱스로 많은 SQL에 대해 추출 랜덤 액세스를 제거할 수 있다면 칼럼이 많더라도 인덱스에 칼럼 추가 고려
  3. 인라인 뷰를 통해 데이터가 감소하는 경우 ROWID이용하여 추출랜덤 액세스 감소 고려

 

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

1,2번의 경우 하나의 SQL이나 인덱스로 많은 추출 랜덤 액세스를 제거할 수 있다면 많은 칼럼으로 인덱스 구성하는 것도 고려대상

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

• 인라인뷰의 결과가 1000건이라고 가정, 최종결과는 WHERE절 조건의 ROWNUM 연산자로 5건의 데이터 결과 추출됨
• 카드번호 + 거래일자 인덱스를 생성한다면 다른 랜덤 액세스는 발생하지 않으며 추출 랜덤 액세스는 1000번 발생

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

• A인라인 뷰는 카드번호 + 거래일자 인덱스를 이용한다면 ROWID 값은 인덱스에 존재하므로 A인라인 뷰는 인덱스 스캔으로만 원하는 데이터 모두 추출 가능 
• 따라서 모든 랜덤 액세스는 발생하지 않음
• A인라인뷰와 거래내역 테이블은 ROWNUM에 의해 5번만 JOIN을 수행
• 거래내역 테이블은 5번의 랜덤 액세스 발생

-> 위와 아래의 쿼리는 같은 결과를 추출하지만 랜덤 액세스는 1000번에서 5번으로 감소

-> 처리 데이터의 건수와 결과 데이터의 건수를 비교하여 감소하는 형태의 SQL은 위와 같은 코드로 추출 랜덤 액세스 감소시

 

출처: http://www.gurubee.net/lecture/2235

추출 랜덤 액세스와 정렬 랜덤 액세스