DATOR


답이 틀린 SQL을 작성하지 않으려면?. . . (주)엔코아 황종하 이사 커버 스토리


coverstory201402.jpg

 

프로젝트에서 튜닝 및 SQL 검토 작업을 수행하다 보면 생각보다 답이 틀린 SQL이 많다. 일반적으로 이와 같은 오류가 나타나는 이유는 SQL과 관련된 부분과 모델 및 업무와 관련된 부분으로 나눠 볼 수 있다.

 

다양한 SQL 오류 유형이 있겠지만 여기서는 프로젝트에서 많이 나타나는 대표적인 두 가지 유형에 대해 소개해 보고자 한다.

 

Outer Join 표시 누락

 

가장 일반적인 오류 유형은 아마도 Outer Join 표시에 대한 누락일 것이다.

 

SELECT S.SALES_YMD ,

        S.ORG_ID ,

NVL(I.INCNTV_AMT, 0),

        S.SALES_QTY * NVL(I.INCNTV_AMT, 0)

FROM   SALES S ,

        INCN I

WHERE  SUBSTR(S.SALES_YMD, 0, 6) = I.INCN_YM(+)                                        

AND    S.MODEL_CD = I.MODEL_CD

 

SQL을 작성자는 SALES(판매)에 대해 맵핑되는 INCN(인센티브) 데이터가 없어도 SALES 데이터는 나오기를 원했으나 I.MODEL_CDOuter Join 표시 누락으로 인해 미 맵핑 데이터가 결과에서 빠지게 된다. 이것은 일반적으로 SQL 작성자의 실수인 경우가 많다. SQL이 복잡해지고 길어지다 보면 계속적인 유지보수 단계에서 누락되는 경우가 종종 발생한다.

 

가변 조건 처리

개발 및 유지보수의 편의를 위해 다양한 상황을 만족하는 하나의 SQL을 작성하여 사용하는 경우가 있다. SQL 작성자는 상수조건(PROD_CD) 그리고 항상 입력되는 필수조건(SALES_YMD)과 상황에 따라 가변적으로 입력되는 선택조건(STORE_ID, MODEL_NO)에 대해 아래와 같이 하나의 SQL로 작성했다.

 

SELECT *

FROM   SALES

WHERE  PROD_CD = 'HELI'

AND    SALES_YMD BETWEEN :V_SALES_YMD_FROM AND :V_SALES_YMD_TO

AND    MODEL_NO LIKE TRIM(:V_MODEL_NO) || '%'

AND    NVL(STORE_ID, ' ') LIKE TRIM(:V_STORE_ID) || '%'                                

 

작성자의 의도는 MODEL_NO STORE_ID 조건이 들어오지 않을 수 있으니 이럴 때는 조건이 없는 것처럼 동작하는 것이다. 위의 SQL에서 SALES_YMD를 위한 조건인 V_SALES_YMD_FROM V_SALES_YMD_TO가 입력되지 않는다면 결과는 나오지 않는다. 그렇다면 만약 가변조건인 MODEL_NO 조건과 STORE_ID 조건이 모두 입력되지 않는다면 SQL은 어떻게 될까?

 

많은 사람들은 아래 SQL과 같이 될 것으로 기대한다.

 

SELECT *

FROM   SALES

WHERE  PROD_CD = 'HELI'

AND    SALES_YMD BETWEEN :V_SALES_YMD_FR AND :V_SALES_YMD_TO  

 

물론 작성자의 의도가 이렇게 되기를 원했기 때문이다. 그러나 사실 그렇게 되지 않는다. 결과는 아래와 같다.

 

SELECT *

FROM   SALES

WHERE  PROD_CD = 'HELI'

AND    SALES_YMD BETWEEN :V_SALES_YMD_FR AND :V_SALES_YMD_TO

AND    MODEL_NO IS NOT NULL                                   

 

MODEL_NO IS NOT NULL이라는 조건이 존재한다. 왜 그럴까?

 

MODEL_NO LIKE TRIM(:V_MODEL_NO) || ‘%’ 라는 조건에서 :V_MODEL_NO NULL이 되면 결과적으로 SQL MODEL_NO LIKE ‘%’이 된다. 이것은 MODEL_NO가 어떤 값이 있든 조건을 만족한다는 것이다. 그러나 MODEL_NO의 값이 NULL이면 이것은 조건을 만족하지 않는다. 그래서 이것을 다시 표현하면 MODEL_NO IS NOT NULL 된다. , MODEL_NO NULL이 아니면 된다는 것이다. 그래서 MODEL_NO IS NOT NULL 조건이 추가된 것이다. 이렇게 되면 의도했던 결과와 달라진다. , 이것은 MODEL_NO 컬럼이 NULL 허용(Nullable)일 경우에만 그렇다.

 

그러나 NVL(STORE_ID, ‘ ’) LIKE TRIM(:V_STORE_ID) || ‘%’ 조건은 STORE_ID 컬럼이 Null 값을 허용하더라도 NVL(STORE_ID, ‘ ’)에 의해서 항상 Not Null 효과가 된다. 그렇기 때문에 조건을 주지 않는 것과 동일하다. 의도했던 결과와 동일하다.

 

성능 측면에서 살펴보자. 하나의 SQL1개의 실행 계획을 갖는다. 여러 SQL로 작성할 것을 이와 같이 하나의 SQL로 작성할 경우 조건 입력 여부와 상관없이 하나의 실행 계획을 갖는다. 예를 들어, MODEL_NO가 입력되면 결과가 매우 적다면 해당 인덱스를 경유하는 실행 계획이 나올 수 있다. 그러나 실행할 때 MODEL_NO 조건이 입력되지 않는다면 전체 인덱스를 액세스하고 테이블에서 필터링하는 방식의 수행이 되어 성능이 좋지 않을 것이다.

 

개발 및 유지보수적인 측면과 성능적인 측면을 함께 고려하면서 SQL을 작성할 필요가 있다.

 

모델 및 업무 이해 부족

 

모델 및 업무에 대한 이해 부족으로 나타나는 일반적인 현상은 방사형 조인이다. 방사형 조인은 Cartesian Product의 하나의 유형이다. 그렇다면 왜 Cartesian Product이 발생하는 SQL을 작성하게 될까?

 

일반적인 원인은 PK 컬럼이 조인 조건에서 일부 누락됐기 때문이다.

 

두 개의 집합이 조인에 참여한다면 적어도 한쪽 집합의 PK는 모두 조인 조건 또는 상수 조건으로 나와야 된다. 그렇게 되면 해당 집합은 1집합이 된다. , PK가 모두 나열된 집합이 1집합이되는 것이다. 그렇게 되면 조인 형태는 1:1 또는 1:M이 되어 Cartesian Product이 발생하지 않게 된다. 그러나 PK 컬럼이 일부 누락된 형태로 조인되는 경우에는 M:M 조인 형태가 되어 Cartesian Product이 발생한다. 정리하면 두 개의 집합이 조인에 참여하는 경우, 조인에 참여하는 집합 중에서 적어도 하나의 집합은 Unique(1집합)해야 한다는 것이다. 그래야 1:1 조인 또는 1:M 조인 형태가 되어 Cartesian Product이 발생하지 않는다.

 

예를 들어 설명해 보자.

 

 그림1.jpg


 

[그림1] 예제 데이터 모델

 

그림1과 같은 데이터 모델에서 아래와 같은 SQL을 작성했다면 Cartesian Product이 발생한다.

 

 SELECT *

FROM   직원월급여 A, 직원월급여항목 B

WHERE  A.직원번호 = B.직원번호

AND    ...

 

직원월급여 테이블의 PK는 직원번호, 급여년월이고 직원월급여항목 테이블의 PK는 직원번호, 급여년월, 급여항목코드이다. 그런데 두 테이블 모두 PK 컬럼의 일부로만 조인을 하고 있다.

 

그림2.jpg  

 [그림2] 예제 데이터

 

그림2와 같은 데이터 상황에서 직원번호만으로 조인을 수행하면 결과는 6건이 된다. , PK의 일부 누락은 둘 중 어느 집합도 1집합이 아니기 때문에 M:M 조인이 되는 것이다.

 

이와 같이 Cartesian Product이 발생하면 Distinct 또는 Grouping 작업을 통해서 결과 레벨을 다시 줄이고자 한다. 그래서 작성된 SQLDistinct가 존재한다면 이것은 잘못 작성된 SQL일 확률이 높으니 확인해 보기 바란다. 또한 Cartesian Product이 발생된 상태에서 Grouping 작업을 통한 Count, Sum 등의 결과는 잘못된 결과가 된다.

 

방사형 조인

 

방사형 조인이란 기준 집합(1집합, 부모집합)을 중심으로 여러 개의 자식 집합(M집합)과 조인을 수행하는 형태를 말한다. 이와 같은 방사형 조인 형태는 생각보다 자주 나타난다.

 

예를 들어, 현업 담당자가 직원월급여 정보를 표시하면서 참고 정보로 직원연락처를 함께 표시해 달라는 요청을 했다. 아래와 같이 SQL을 작성했다면 이것 또한 Cartesian Product이 발생한다.

 

SELECT A.직원번호, A.직원명, B.급여년월, B.급여금액, C.처정보

FROM   직원 A, 직원월급여 B, 직원연락처 C

WHERE  A.직원번호 = B.직원번호

AND    A.직원번호 = C.직원번호

AND    A.직원번호 = :직원번호                                                                                                

 

그림3과 같은 데이터 상황을 예로 살펴보자.

 

그림3.jpg  

[그림3] 예제 데이터

 

직원 테이블의 PK는 직원번호이고 직원월급여 테이블의 PK는 직원번호, 급여년월이다. 그리고 직원연락처의 PK는 직원번호, 연락처순번이다. SQL에서 보면 직원 테이블(부모 집합)을 중심으로 두 개의 자식 테이블인 직원월급여, 직원연락처 테이블과 조인을 하고 있다. 얼핏 보면 직원 테이블의 PK가 모두 조인에 사용되었기 때문에 문제 없어 보인다

 

A.직원번호 = B.직원번호

A.직원번호 = C.직원번호 


라는 조건을 이행 규칙을 적용하여 다시 쓰면 다음과 같다.

 

A.직원번호 = B.직원번호

B.직원번호 = C.직원번호 


직원월급여(B) 테이블과 직원연락처(C) 테이블은 PK 컬럼의 일부인 직원번호만으로 M:M 조인을 수행하여 결국 Cartesian Product이 발생한다. 방사형 조인은 데이터 모델을 정확히 이해하면 쉽게 발견할 수 있다.

 

방사형 조인이 발생하면 해당 화면에서 표시하고자 하는 레벨이 무엇인지에 대한 정확한 이해가필요하다. 업무를 파악해 보았더니, 담당자가 원했던 것이 직원월급여 정보 레벨로 데이터를 표현하면서 참조 정보로 직원의 대표 연락처 정보를 원했던 것이라면 직원연락처 정보를 1집합 레벨로 변경하는 처리가 필요하다. 반대의 경우 역시 마찬가지다.

 

Cartesian Product을 확인하는 방법은 지금까지 설명한 것처럼 PK가 조인 조건 또는 상수 조건으로 모두 사용되었는지 확인하는 것이다. 또한 데이터 모델에서 부모 테이블을 중심으로 여러 자식 테이블과 조인 형태(방사형 조인)를 갖는다면 업무에 맞게 기준 정보 레벨까지 표현하고 참조정보로 표현하고자 하는 나머지 자식 정보는 1집합으로 변형하여 표현하면 된다.

 

업무를 정확하게 이해하고 SQL로 적절하게 표현하지 못한다면 잘못된 결과가 나오게 된다. 모델 이해 능력과 SQL 활용 능력을 키우는게 중요하다

Tag :

Leave Comments