SQL 튜닝 - 사례별 튜닝 기법

1. MAX 함수를 사용하여 체번을 하는 프로그램에서 종종 보게 되는 오류들

(1) MAX(seq) +1 대신 MAX(NVL(seq, 0)) + 1을 사용하는 경우

인덱스를 사용할 수 있도록 MAX와 NVL의 위치를 바꾸어 NVL(MAX(seq), 0) + 1로 작성해야 한다.

 

(2) MAX(seq) + 1 대신 MAX(TO_NUMBER(seq)) + 1을 사용하는 경우

위 경우와 마찬가지로 생성한 인덱스를 활용할 수 없는 SQL이다. 이 경우 seq 컬럼을 NUMBER 데이터 타입으로 변경하고 TO_NUMBER 함수를 제거하는 방안을 고려해야 한다.

 

(3) 인덱스를 seq 컬럼으로만 생성한 경우

WHERE절의 col_1 = '1' 부분을 삭제하거나, 인덱스를 col_1 + seq로 변경해야 한다.

 

(4) WHERE col_1 = '1' 조건을 사용하지 않는 경우

seq 컬럼으로 인덱스를 생성하되, UNIQUE INDEX 또는 PRIMARY KEY로 생성해야 한다.

 

2. 동일한 데이터 반복 사용

SQL을 요구사항에 따라 작성하다 보면 사실 그럴 필요가 없음에도 불구하고 같은 데이터를 반복해서 읽는 경우가 발생한다. 하나의 SQL에서 같은 테이블에 여러 번 접근하는 것은 성능 면에서 좋지 않은 영향을 끼칠 수 있으므로 한 번만 수행하는 것으로 수정하여 성능 개선을 꾀할 수 있다.

 

(1) UNION ALL문에서 동일 데이터의 반복 사용

-> CASE WHEN으로 개선

 

(2) 서브쿼리에서 같은 테이블의 컬럼 2개를 최대 값 조회

-> 두 개의 컬럼을 MAX를 구해서 CONCAT 함수로 사용하여 합한 후, 이 값을 메인쿼리의 SELECT절에서 SUBSTR을 사용하여 개선

 

3. DB LINK

DB LINK를 호출하는 횟수를 적게 하는 것이 일반적으로 성능이 좋다. 데이터가 적다면 NESTED LOOP JOIN으로 호출, 데이터가 많다면 한 번에 모두 읽어오게 호출

 

4. 병렬 처리를 위한 동일 테이블 반복 접근을 개선

데이터가 방대하다고 해서 무조건 병렬 처리 기능(UNION)을 사용하는 것은 좋지 않다. 계속해서 테이블의 같은 부분을 검색할 수 있기 때문이다. 이런 경우 추가적인 조건을 제시하여 대상 데이터를 여러 프로그램에서 나누어 처리하게 하는 방법을 권한다. 단, 추가적인 입력 조건으로는 NOT NULL 컬럼이면서 인덱스가 생성되어 있는 컬럼을 사용해야 한다. 또한 I/O를 고려하여 데이터의 입력 순서가 인덱스의 순서가 유사한 컬럼을 활용하는 것이 효과적이다. 가급적 적은 블록에 접근하여 원하는 결과를 가져올 수 있기 때문이다. 날짜를 저장하는 컬럼이 대표적인 예이다.

SELECT column_name1, column_name2
FROM table_1
WHERE type IN ('A', 'B', 'C', 'D', 'E', '1', '2', '3', '4', '5')

인덱스1 : INPUT_DATE_COLUMN
인덱스2 : CUSTOMER_ID, INPUT_DATE_COLUMN

위의 SQL을 여러 프로그램에서 병렬로 처리하기 위해 WHERE절의 TYPE IN() 조건을 쪼개어 첫 번째 프로그램에서는 type='A'인 데이터를 처리하게 하고 두 번째 프로그램에서는 type='B'인 데이터를 처리하게 하는 방식은 성능 개선에 도움이 되지 않는다. type 컬럼에는 인덱스가 없으므로 각각의 프로그램에서 매번 FULL TABLE SCAN을 수행할 것이기 때문이다.

 

따라서 인덱스가 있는 컬럼을 기준으로 나누어 처리하는 방식을 고려할 수 있는데, 먼저 변별력이 높은 customer_id를 기준으로 대상 데이터를 나눈 경우이다. 이렇게 처리하면 인덱스를 활용할 수 있으므로 각각의 인접한 블록에 있는 것이 아니므로 전체적으로 같은  블록에 중복해서 여러 차례 접근할 가능성이 높아진다.

 

데이터가 입력된 날짜를 저장하는 input_data_column 컬럼을 기준으로 대상 데이터를 나누어 처리한 경우를 보자. 입력된 날짜가 인접한 데이터는 인접한 블록에 위치할 가능성이 높으므로 각각의 프로그램에서 접근해야 할 데이터의 블록의 수가 적어진다. 따라서 대상 데이터가 대량인 경우는 이와 같은 방법으로 나누어 처리하도록 프로그램을 설계하는 것이 가장 이상적이다.

 

SELECT column_name1, column_name2
FROM table_1
WHERE input_data_column BETWEEN '20220101' AND '20220430'
AND type IN ('A', 'B', 'C', 'D', 'E', '1', '2', '3', '4', '5')

UNION

SELECT column_name1, column_name2
FROM table_1
WHERE input_data_column BETWEEN '20220501' AND '20220830'
AND type IN ('A', 'B', 'C', 'D', 'E', '1', '2', '3', '4', '5')

UNION

SELECT column_name1, column_name2
FROM table_1
WHERE input_data_column BETWEEN '20220901' AND '20221231'
AND type IN ('A', 'B', 'C', 'D', 'E', '1', '2', '3', '4', '5')

 

 

---

출처 : 실전 사례로 살펴보는 SQL 튜닝 비법