MIN/MAX함수 인덱스 사용 방법
회사에서 모니터링 도중 단순 쿼리인데 1.5초? 정도 걸리는 쿼리가 있어 확인해보았다.
MAX함수를 이용해서 일련번호를 계산하는 쿼리었는데 뭐가 문제 있었을까?
아래 MAX함수를 복습하면서 살펴보자!
인덱스를 이용하여 MAX MIN함수 사용하기
목차 MIN/MAX 함수란 사용방법 문재의 쿼리 해결방법 변경후실행계획
MIN/MAX 함수란
- 여러 값들을 모아서 단 하나의 결과를 돌려주는 함수
- 집계함수 중 일부로 결과 값들 중 max, min 값을 호출
- GRUOP BY 와 함께 사용할 경우 행들을 소그룹화 된다.
사용방법
SELECT MAX(컬럼명) FROM 테이블
SELECT MAX(SQNO) FROM SAMPLE_TABLE
문제의 쿼리
SELECT LPAD( NVL( MAX( SUBSTR( SQNO, 2 ) ), 0 ) + 1, 15, '0' ) AS SQNO /*일련번호 */
FROM ADRES_NBR /* 주소 테이블 */
WHERE IN_CD =:CD
*해당 오라클은 19c
- 쿼리 작성자에게 물어보니 결과 값 이상없다고 말해주었다.
- 결과 값은 문제 없이 잘 나왔다. 그래서 플랜을 한번 떠봤다
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6127 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 23 | | |
|* 2 | INDEX FAST FULL SCAN| IX_LC_ADRES_NBC_PK | 3367K| 73M| 6127 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------
- 2번에 INDEX FAST FULL SCAN을 이용 했다. 아마 인덱스 항목만 쿼리문안에 있는거 같다.
- 그래서 인덱스를 살펴보았다. IX_LC_ADRES_NBC_PK : IN_CD + SQNO 명시 되어있다.
- 그럼 1번 SORT AGGREGATE가 이상한지 보자. 보통 전체 로우 대상으로 집계함수를 수행할 때 사용한다.
- 전체 데이터를 정렬은 안하지만 SORT AREA를 사용하기 저런 표현을 사용했다고한다. (나중에 집계함수 설명할 때 작성해야겠다.)
그럼 문제점가 없다?
INDEX 활용을 100프로 못한게 아쉽다.
- INDEX를 활용해서 사용할 경우 인덱스 맨 처음 혹은 맨 마지막에서부터 값을 읽기 때문에 테이블 엑세스가 발생하지 않는다.
- 효율적으로 사용하기 위해서는 min max 컬럼은 인덱스 가장마지막 위치, where 절에서는 = 조건으로 모두 인덱스를 구성하는 컬럼이어야한다.( 함수인자 컬럼이 모두 인덱스에 포함)
위 조건으로 적용되는건 오라클 8i부터라고한다! 그전 버전은 위와 같은 실행계획이 나온다
위에 쿼리는 위 조건을 만족한다 ?
- max 안에 sqno 컬럼이 있고, where절에는 in_cd가 잘 있다.
- 근데 자세히 보자 max안에 substr함수가 존재하기 때문에 형변환이 일어났다.
- 인덱스컬럼에 형변환이 존재하는 경우 인덱스 조건을 만족하지 않는다.
- 위 쿼리는 단지 인덱스에 in_cd만 사용했다.
해결방법
NVL( SUBSTR( MAX( sqno), 2 ) , 0 )
- 위에처럼 순서만 변경하면 끝난다.
- 지금 생각해보니… 왜 substr을 넣었을까…? 확인해봐야겠다.
변경 후 실행계획
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 23 | | |
| 2 | FIRST ROW | | 1 | 23 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| IX_LC_ADRES_NBC_PK | 1 | 23 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
- 위 3번을 보면 index INDEX RANGE SCAN (MIN/MAX)라는 오퍼레이션이 보인다.
- 찾아보니 해당 오퍼레이션을 min/max 알고리즘이라고 부른다.
- 인덱스를 범위스캔하면서 FIRST ROW 만 찾고 종료되는 실행계획이라고 한다.
- 코스트, 실제 실행시간 비교 시 아래가 효율적이라고 판단된다.