SQL Tuning Day 4

diense_kk·2025년 12월 20일

DB

목록 보기

7/8

SQL Tuning Day 4

효율적인 코드

코드 1.

SELECT 장비번호, 장비명, 상태코드
	 , (SELECT MAX(변경일자)
     		FROM 상태변경이력
        WHERE 장비번호 = P.장비번호) AS 최종변경일자
     , (SELECT MAX(변경순번)
     		FROM 상태변경이력
        WHERE 장비번호 = P.장비번호
      		AND 변경일자 = (SELECT MAX(변경일자)
            					FROM 상태변경이력
                          WHERE 장비번호 = P.장비번호)) AS 최종변경순번
	FROM 장비 AS P
WHERE 장비구분코드 = 'A001'

코드 2.

SELECT 장비번호, 장비명, 상태코드
	,SUBSTR(최종이력, 1, 8) AS 최종변경일자
    ,SUBSTR(최종이력, 9) AS 최종변경순번
    FROM(
    	SELCT 장비번호, 장비명, 상태코드
        	,(SELCT MAX(변경일자||변경순번)
            	FROM 상태변경이력
              WHERE 장비번호 = P.장비번호) AS 최종이력
        	FROM 장비 AS P
        WHERE 장비구분코드 = 'A0001'
    )

인덱스를 "장비번호 + 변경일자 + 변경순번"으로 구성했을 경우 두 쿼리 중 어느 쿼리가 더 효율적일까?
각 장비당 이력이 많지 않으면 크게 상관없지만, 이력이 많다면 쿼리2가 성능이 문제가 될 수 있는 패턴이다.
인덱스 컬럼을 가공했기 때문이다. 각 장비에 속한 과거 이력 데이터를 모두 읽어야하므로 장비당 이력 레코드가 많다면 코드1 보다 성능이 더 안좋을 수 있다.

Index Skip Scan

인덱스 선두 컬럼을 조건절에 사용하지 않으면 옵티마이저는 기본적으로 Table Full Scan을 선택한다. Table Full Scan보다 I/O를 줄일 수 있거나 정렬된 결과를 쉽게 얻을 수 있다면, Table Full Scan을 사용하기도 한다.
오라클은 인덱스 선두 컬럼이 조건절에 없어도 인덱스를 활용하는 새로운 스캔 방식을 선보였는데, Index Skip Scan이 바로 그것이다.
이 스캔은 조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용하다.
예를 들면 급여 테이블에서 Distinct Value가 적은 것은 부서, 많은 것은 급여일 것이다.
이때 인덱스 선두 컬럼이 없을 때 Index Skip Scan이 작동한다. (물론 중간 컬럼이 없을 때도 작동한다.)

Index SKip Scan은 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 '가능성이 있는' 리프 블록만 골라서 액세스하는 스캔 방식이다.

Partition Pruning

파티션 프루닝(Partition Pruning)은 시스템에서 불필요한 파티션을 읽지 않고 건너뛰어 성능을 향상시키는 기술로, SQL 쿼리의 조건절을 분석해 "가지치기"처럼 액세스 대상이 아닌 데이터를 제거하는 데이터베이스 최적화 기법이다. 이를 통해 대용량 데이터처리 시 디스크 I/O를 줄이고 쿼리 속도를 크게 향상시킬 수 있다.

인덱스 구조 테이블

랜덤 액세스가 아예 발생하지 않도록 테이블을 인덱스 구조로 생성한 것을 IOT(오라클), 클러스터형 인덱스(MS-SQL)라고 부른다.
테이블을 찾아가기 위한 ROWID를 갖는 일반 인덱스와 달리 IOT는 그 자리에 테이블 데이터를 갖는다. 즉, 테이블 블록에 있어야 할 데이터를 인덱스 리프 블록에 모두 저장하고 있다.
IOT에서는 인덱스 리프 블록이 곧 데이터 블록이다.

인덱스 구조로 테이블을 생성하는 방법이다.

CREATE TABLE index_org_t(
	A NUMBER,
    B VARCHAR(10),
    CONSTRAINT index_org_t_pk PRIMARY KEY(a)
)
ORGANIZATION INDEX;

참고로, 일반 테이블은 힙 구조 테이블이라고 부른다.
일반 힙 구조 테이블에 데이터를 입력할 때는 랜덤 방식을 사용한다. 즉, FreeList로부터 할당 받은 블록에 정해진 순서 없이 데이터를 입력한다. 반면 IOT는 인덱스 구조 테이블이므로 정렬 상태를 유지하며 데이터를 입력한다.

IOT는 인위적으로 클러스터링 팩터를 좋게 만드는 방법 중 하나이다. 같은 값을 가진 레코드들이 100% 정렬된 상태로 모여 있으므로 랜덤 액세스가 아닌 시퀀셜 방식으로 데이터를 액세스한다.
이 때문에 BETWEEN이나 부등호 조건으로 넓은 범위를 읽을 때 유리하다.

클러스터 테이블

클러스터 테이블에는 인덱스 클러스터와 해시 클러스터 두 가지가 있다.

인덱스 클러스터 테이블

인덱스 클러스터 테이블은 값이 같은 레코드를 한 블록에 모아서 저장하는 구조이다.
한 블록에 모두 담을 수 없을 때는 새로운 블록에 할당해서 클러스터 체인으로 연결한다.

여러 테이블 레코드를 같은 블록에 저장할 수도 있다. 이것을 다중 테이블 클러스터라고 부른다.
일반 테이블은 하나의 데이터 블록을 여러 테이블이 공유할 수 없다.

클러스터형 인덱스는 IOT와 가깝다.
오라클 클러스터는 키 값이 같은 데이터를 같은 공간에 저장해 둘 뿐, IOT나 SQL Server의 클러스터형 인덱스처럼 정렬하지는 않는다.

클러스터에 테이블을 담기 전에 클러스터 인덱스를 반드시 정의해야 된다. 클러스터 인덱스는 데이터 검색 용도로 사용할 뿐만 아니라 데이터가 저장될 위치를 찾을 때도 사용되기 때문이다.

클러스터 인덱스도 일반 B*Tree 인덱스 구조를 사용하지만, 테이블 레코드를 일일이 가리키지 않고 해당 키 값을 저장하는 첫 번째 데이터 블록을 가리킨다는 점이 다르다. 즉, 일반 테이블에 생성한 인덱스 레코드는 테이블 레코드와 1:1 대응 관계를 갖지만, 클러스터 인덱스는 테이블 레코드와 1:M 관계를 갖는다. 따라서 클러스터 인덱스의 키 값은 항상 Unique이다.