SQL 튜니이론 - 다양한 선택조건 처리방식 OR EXPANSION, LIKE/BETWEEN, UNION ALL, NVL/DECODE, DYNAMIC QUERY

SQL 튜니이론 - 다양한 선택조건 처리방식 OR EXPANSION, LIKE/BETWEEN, UNION ALL, NVL/DECODE, DYNAMIC QUERY

 

OR 조건을 활용한 선택조건 처리

 

select * from 거래
where(:cust_id is null or 고객ID = :cust_id)
and 거래일자 between :dt1 and :dt2

-- EXECUTION PLAN
SELECT STATEMENT
    TABLE ACCESS (FULL) OF '거래' (TABLE)

 

OR 조건을 활용한 위의 방식은 옵티마이저에 의한 OR EXPANSION 쿼리 변환이 작동하지 않으므로 [고객ID + 거래일자] 순으로 인덱스를 구성해도 인덱스를 사용할 수 없습니다. 따라서 인덱스 선두 칼럼에 대한 옵션 조건에 OR 조건을 사용해서는 안됩니다. [거래일자 + 고객ID] 순으로 구성한 인덱스는 사용할 수 있지만 고객 ID를 필터조건으로 사용하는 것은 큰 비효율입니다. 거래일자가 범위검색조건인 BETWEEN으로 사용되기때문에 고객 ID 컬럼은 테이블 필터조건으로 쓰이기 때문입니다. 단, 18C 버전부터 인덱스에 모든 테이블 레코드가 NULL이 아닌 경우 OR 조건도 인덱스 필터조건으로 처리합니다.

 

--19C
SELECT STATEMENT
    FILTER
        TABLE ACCESS (BY INDEX ROWID) OF '거래'(TABLE)
            INDEX RANGE SCAN OF '거래_IDX3'(INDEX)

-- predicate information

--filter(TO_DATE(:DT1) <= TO_DATE(:DT2)
--    filter(:cust_id null or '고객ID' = TO_NUMBER(:CUST_ID)
A-- ACCESS ("거래일자" >= :DT1 and "거래일자" <= :DT2)

 

인덱스에 포함되지 않은 컬럼은 어차피 테이블에서 필터링하므로 OR 조건으로 옵션처리해도 무방합니다.

 

OR 조건을 활용한 옵션처리

- 인덱스 엑세스 조건으로 사용 불가
- 인덱스 필터 조건으로 사용 불가
- 테이블 필터 조건으로 만 사용 가능
- 컬럼의 not null 상태가 보장되면 18C 부터 인덱스 필터조건으로 사용가능

 

OR 조건을 활용한 옵션처리의 특성 고려하면 옵션 처리 조건에 가급적 사용하지 말아야 합니다. OR 조건의 유일한 장점은 옵션 조건 컬럼이 NULL 허용컬럼이더라도 결과집합을 보장한다는 것 뿐입니다. 아래와 같은 조건절에 자체에 대한 OR 조건은 OR-EXPANSION을 통해 인덱스 사용이 가능합니다.

 

SELECT *
FROM 거래
WHERE 고객Id = :cust_id
AND
(:dt_type = 'A' and 거래일자 between :dt1 and :dt2)
OR
(:dt_type = 'B' and 거래일자 between :dt1 and :dt2)

-- EXECUTION PLAN
SELECT STATEMENT
    CONCATENATION
        FILTER
            TABLE ACCESS BY LOCAL INDEX ROWID OF 거래 TABLE
                INDEX RANGE SCAN OF '거래_idx1' (index)
        FILTER
            TABLE ACCESS BY LOCAL INDEX ROWID OF 거래 TABLE
                INDEX RANGE SCAN OF '거래_idx1' (index)                

 

LIKE/BETWEEN을 활용한 옵션조건 처리

 

변별력이 좋은 필수 조건이 있는 상황에서 LIKE/BETWEEN을 후행 컬럼에 대한 조건으로 사용하는 것은 나쁘지 않은 선택입니다. 필수 조건 컬럼을 인덱스 선두에두고 엑세스 조건으로 사용하면 LIKE/BETWEEN이 인덱스 필터 조건이라도 충분히 좋은 성능을 낼 수 있습니다.

 

-- INDEX : 등록일시 + 상품분류코드
SELECT *
FROM 상품
WHERE 등록일시 >= trunc(sysdate) -- 필수조건/ 인덱스 엑세스 조건
AND 상품분류코드 LIKE :Prd_cls_cd || '%' -- 옵션조건

 

필수 조건의 변별력이 좋지 않을때는 비효율적입니다.

 

-- 인덱스 : 상품대분류코드 + 상품코드
SELECT * FROM  상품
WHERE 상품대분류코드 = :prd_lcls_cd -- 필수 조건
and 상품코드 like :prd_cd || '%' -- 옵션 조건

 

위 쿼리의 테이블에서 특정 상품대분류코드 해당하는 레코드가 많다면 TABLE FULL SCAN이 유리할 수 있습니다. 그런데 옵티마이저는 상품코드까지 입력할 때를 기준으로 INDEX RANGE SCAN을 선택합니다. 상품코드가 입력된다면 최적이겠지만 입력되지 않으면 문제가 발생합니다. LIKE/BETWEEN 패턴을 사용하고자 할 때는 아래 네 가지 경우에 속하는지 반드시 점검해야 합니다.

 

인덱스 선두 컬럼에 LIKE/BETWEEN 사용한 경우

 

-- index : 고객ID + 거래일자
SELECT *
FROM 거래
WHERE 고객ID LIKE :CUST_ID || '%'
AND 거래일자 BETWEEN :dt1 and :dt2

 

고객ID가 입력되지 않으면 거래일자가 인덱스 필터조건이므로 인덱스의 전체 데이터를 읽으면서 거래일자 조건을 필터링하게됩니다. 인덱스를 거래일자 + 고객ID 로 구성하면 거래일자가 인덱스 엑세스 조건으로 동작하지만 고객ID 값을 입력할때 필터조건으로 동작하기 때문에 비효율이 발생합니다.

 

NULL 허용컬럼에 LIKE/BETWEEN 패턴조건을 사용한 경우

 

SELECT * FROM 거래
WHERE 고객ID LIKE :CUST_ID || '%'
AND 거래일자 between :dt1 and :dt2

 

만약 고객ID가 null 허용칼럼이고 실제로 :cust_id에 Null이 입력되면 거래일자 조건에 맞고 고객id가 null 인 레코드는 결과에 포함되야함에도 결과집합에서 노락됩니다. between 조건을 사용해도 마찬가지 입니다.

 

숫자형 컬럼에 LIKE/BETWEEN 패턴조건을 사용한 경우

 

-- idx : 거래일자 + 고객ID
SELECT * FROM 거래
WHERE 거래일자 = :trd_dt
AND 고객_ID like :cust_id || '%

 

고객id가 입력되면 두 컬럼모두 인덱스 엑세스 조건으로 사용됩니다. 다만, 고객 id가 숫자형 컬럼이면 자동으로 형변환이 일어나므로 고객 id가 필터조건으로 사용됩니다. 특정 고객의 하루치 거래를 모두 스갠하고 고객id로 필터링하는 비효율이 발생합니다.

 

SELECT * FROM 거래
WHERE 거래일자 = :trd_dt
AND TO_CHAR(고객_ID) like :cust_id || '%

 

가변길이형 컬럼에 LIKE/BETWEEN 패턴조건을 사용한 경우

 

where 고객명 like :cust_nm || '%' -- :cust_nm = '김훈'

 

가변길이 컬럼을 위 쿼리와 같이 조회하면 입력값을 포함하는 다른 값까지 함께 조회됩니다. 따라서 칼럼이 가변길이형일때는 변수 길이가 같은 레코드만 조회되도록 아래와 같은 조건절이 추가되거나 %를 빼고 정확히 일치하는 값만 조회하도록 해야합니다. 단, 이런 경우에는 고객명을 입력하지 않으면 어떤 고객도 출력되지 않습니다.

 

where 고객명 like :cust_nm || '%' -- :cust_nm = '김훈'
and length(고객명) = length(nvl(:cust_nm,고객명))

where 고객명 like :cust_nm 

 

UNION ALL 활용

 

:cust_id 변수에 값을 입력했는지에 따라 하나의 쿼리만 실행되게 하는 방식입니다.

 

-- cust_id :선택조건
SELECT * FROM 거래
WHERE :cust_id is null
AND 거래일자 between :dt1 and :dt2
union all
SELECT * FROM 거래
WHERE :cust_id is not null
AND 고객명 = :cust_id
AND 거래일자 between :dt1 and :dt2

-- EXECUTION PLAN
SELECT STATEMENT
    UNION ALL
        FILTER
            TABLE ACCESS BY LOCAL INDEX ROWID OF '거래'
                INDEX RANGE SCAN OF 거래_idx1(index) -- 거래일자
        FILTER
            TABLE ACCESS BY LOCAL INDEX ROWID OF '거래'
                INDEX RANGE SCAN OF 거래_idx1(index) -- 고객id + 거래일자

 

이 패턴을 사용하면 :cust_id 변수의 값 입력과 무관하게 최적의 인덱스를 사용합니다. 유일한 단점은 코딩량이 길어진다는 점 입니다.

 

NVL/DECODE 함수활용

 

SELECT *
FROM 거래
WHERE 고객ID = NVL(:CUST_ID, 고객ID)
AND 거래일자 between :dt1 and :dt2

SELECT *
FROM 거래
WHERE 고객ID = DECODE(:CUST_ID, NULL, 고객ID, :CUST_ID)
AND 거래일자 between :dt1 and :dt2

-- EXECUTION PLAN
SELECT STATEMENT
  CONCATENATION
    FILTER -- :CUST_ID IS NULL
      TABLE ACCESS (BY LOCAL INDEX ROWID) OF '거래'
        INDEX(RANGE SCAN) OF '거래_IDX1' (INDEX) -- 거래일자
     FILTER -- :CUST_ID IS NOT NULL
      TABLE ACCESS (BY LOCAL INDEX ROWID) OF '거래'
        INDEX(RANGE SCAN) OF '거래_IDX2' (INDEX) -- 고객id + 거래일자

 

:cust_id 변수에 값을 입력하지 않으면 위쪽 브랜치에서 거래일자가 선두인 인덱스를 사용하고 입력하면 아래 브랜치에서 고객ID + 거래일자 인덱스를 사용합니다. NVL/DECODE 함수 모두 동일합니다.

 

고객ID 컬럼을 함수의 인자로 사용해도 인덱스를 사용할 수 있는 이유는 OR EXPANSION이 발생하기 때문입니다. 옵티마이저가 UNION ALL 방식으로 쿼리를 변환합니다. OR EXPASION 이 발생하지 않으면 NVL, DECODE의 인자로 사용한 컬럼을 인덱스 엑세스 조건으로 사용할 수 없습니다. 고객ID = 고개걍 의 경우 인덱스에서 이 조건을 만족하는 어느 한 시점을 찾을 수 없기 때문입니다.

 

NVL/DECODE 방식의 장점은 옵션 조건 컬럼을 인덱스 엑세스 조건으로 사용할 수 있다는 점입니다. 단점은 null 허용 컬럼에 사용할 수 없다는 점입니다. 조건절 변수에 Null을 입력하면 값이 실제로 Null인 레코드는 결과집합에서 누락되기 때문입니다.

 

NVL/DECODE 함수를 여러 개 사용하면 그 중 변별력이 가장 좋은 컬럼을 기준으로 한 번만 OR EXPANSION이 발생합니다. OR EXPANSION의 기준으로 선택되지 않은 컬럼은 인덱스 구성 컬럼이어도 모두 인덱스 필터 조건으로 처리됩니다.

 

DYNAMIC SQL

 

DYNAMIC SQL을 이용해 조건절을 동적으로 구성할 수 있는 시스템에서는 옵션 조건에 = 연산자를 항상 사용할 수 있습니다.이에 변별력 있는 컬럼을 엑세스 조건으로 사용할 수 있게 인덱스만 잘 구성해주면 됩니다. 다만 DYNAMIC SQL을 허용하지 않는 시스템도 있고 허용하더라도 힌트로 엑세스 경로를 고정할때 옵션조건 튜닝을 해야합니다. DYNAMIC SQL에 힌트를 명시하면 상충하여 성능 문제를 야기할 수 있습니다.

DYNAMIC SQL을 이용하더라도 하드 파싱에 의한 성능 문제가 발생하지 않도록 바인드 변수를 잘 활용해야합니다. 조건절을 동적으로 구성한다고해서 입력값 까지 동적으로 변경할 이유는 없기때문입니다.