SQL튜닝 - PL/SQL 함수호출로 인한 부하를 줄이는 인덱스 구성방법

 

SQL튜닝 - PL/SQL 함수호출로 인한 부하를 줄이는 인덱스 구성방법

 

PL/SQL 사용자 정의함수는 실행속도가 매우 느립니다. PL/SQL 사용자 정의함수의 속도가 느린 이유는 다음과 같습니다

 

- PL/SQL 사용자 정의함수는 인터프리터 언어로 변환되어 가상머신 위에서 작동합니다.

- PL/SQL 사용자 정의함수를 호출할 때마다 CONTEXT SWITCHING이 발생합니다

- 내장 SQL에 대한 RECURSIVE CALL이 발생합니다.

 

select 회원번호,..., GET_ADDR(우편번호)
from 회원
where 생월일 like '01%' -- 수십 ~ 수백 만 건 조회

 

위 SQL에 PL/SQL 함수 GET_ADDR을 쓰지않고 조인문으로 처리하면 성능차이가 큽니다.

 

SELECT A.회원번호, (
    SELECT B.시 || ' '|| B.군구 || ' ' || B.읍면동
    FROM 기본주소 B
    WHERE B.우편번호 = A.우편번호
    AND B.순번 = 1
)기본주소
FROM 회원 A
WHERE 생년월일 LIKE '01%'


SELECT A.회원번호, B.시 || ' '|| B.군구 || ' ' || B.읍면동 AS 기본주소
FROM 회원 A , 기본주소 B
WHERE B.우편번호(+) = A.우편번호
AND 생년월일 LIKE '01%'
AND B.순번(+) = 1

 

PL/SQL 함수가 너무 복잡하면 그대로 쓸 수 밖에없는데 이럴 때는 인덱스 구성을 최적화하여 함수의 호출횟수를 최소화해야합니다.

 

효과적인 인덱스 구성을 통한 함수호출 최소화

 

아래 SQL에서 힌트를 통해 회원테이블을 FULL SCAN 하면 PL/SQL 함수 encryption 은 테이블의 레코드 수 만큼 실행됩니다.

 

SELECT /*+full(A)*/ 회원번호, ... , 등록일자
from 회원 a
where 암호화된_전화번호 = encryption(:phone_no)

 

다른 조건절이 있으면 함수는 조걸절에 해당하는 횟수만큼 실행됩니다.

 

SELECT /*+full(A)*/ 회원번호, ... , 등록일자
from 회원 a
where 생년 = '1987'
and 암호화된_전화번호 = encrypion(:phone_no)

 

아래와 같이 생년 단일컬럼으로 구성된 인덱스를 사용하면 암호회된_전화번호 조건절은 테이블 필터링 조건이 됩니다. 따라서 PL/SQL 함수는 테이블 엑세스 횟수 즉, 생년 = '1987' 을 만족하는 건수만큼 실행됩니다.

 

-- index 회원_X01 (생년)
select /*+index(a 회원_x01)*/ 회원번호, ... , 등록일자
from 회원 a
where 생년 = '1987'
and 암호화된_전화번호 = encrypion(:phone_no)

-- execution plan
select statement
  table access by index rowid batched of '회원'
    index range scan of '회원_x01'

-- predicate information
filter(암호화된_전화번호 = encrypion(:phone_no))
access ("생년" = '1987')

 

아래와 같이 생년 + 생월일 + 암호화된_전화번호로 구성된 인덱스를 사용하면 암호화된_전화번호는 인덱스 필터 조건입니다. 따라서 encryption 함수는 인덱스의 스캔횟수 즉, 생년 = 1987 을 만족하는 건수 만큼 수행됩니다.

 

-- 회원_X02 : 생년 + 생월일 + 암호화된_전화번호
select /*+index(a 회원_x02)*/ 회원번호, ... , 등록일자
from 회원 a
where 생년 = '1987'
and 암호화된_전화번호 = encrypion(:phone_no)

-- execution plan
select statement
  table access by index rowid batched of '회원'
    index range scan of '회원_x02'

-- predicate information
access ("생년" = '1987' AND 암호화된_전화번호 = encrypion(:phone_no))
FILTER (암호화된_전화번호 = encrypion(:phone_no))

 

생년 + 암호화된_전화번호로 구성된 인덱스를 사용하면 암호화된_전화번호는 인덱스 액세스 조건으로 사용됩니다. 따라서 단 한 번만 수행됩니다.

 

-- index 회원_X03 : 생년 + 암호화된_전화번호
select /*+index(a 회원_x03)*/ 회원번호, ... , 등록일자
from 회원 a
where 생년 = '1987'
and 암호화된_전화번호 = encrypion(:phone_no)

-- execution plan
select statement
  table access by index rowid batched of '회원'
    index range scan of '회원_x03'

-- predicate information
access ("생년" = '1987' AND 암호화된_전화번호 = encrypion(:phone_no))