레거시 오라클 쿼리 리팩토링: 주문번호 부분입력으로 편의성 추가(Feat. 성능 최적화)

트러블 슈팅

 

 

레거시 자체 프레임워크에서 동적 쿼리 리팩토링 해보기

담당하고 있는 사이트의 관리자페이지 수정사항 요청인데 기록해두고 공유하면 좋을 것 같아서 써보려고 합니다.

java1.8, oracle11, 자체프레임워크를 사용중입니다.

실무내용이라 보안상 테이블구조나 전체쿼리는 공개하지 않습니다.

 

그리고 최대한 다른곳에 영향이 없게끔

큰 구조를 변경 하지않고 요구사항을 충족하는게 목표입니다.

---

상황설명

??? : 주문번호를 전부 적어야되서 불편해요

-> 419495-00012, 419495-00013, 419495-00014 전체 입력해야됨

 

나 : 그러네요 저런식이면 주문번호 필터기능을 사용을 안하고 정렬만 눌러서 사용하겠네요

-> 419495 만 입력했을때 해당 주문번호의 서브주문이 같이 검색되게 처리해야겠따!

-> 추가적으로 기존의 사용자들을 위해 419495-00012도 정상동작하게끔 처리해야겠다

 

-> 목표설정완료 -> 기존동작방식 + 주문번호 앞번호만 입력해도 검색가능하게끔

기존의 전체화면

 

 

---

기존쿼리 확인

 

기존의 화면 주문번호 입력 시

 

아래는 위의 화면을 조회했을때의 쿼리문의 일부이다

기존 주문번호 입력 시 쿼리문

 

AND  DECODE(O.ERPITEMNO,'',TO_CHAR(O.SOREFITEM),TO_CHAR(O.SOREFITEM||'-'||O.ERPITEMNO) ) IN ('419495-00012','419495-00013')

 

일단 DECODE 의미없어보인다.

그리고 IN절로 되어있는걸 LIKE문으로 바꿔서 동적쿼리를 작성해주면 될 것 같다.

 

---

기존 동적쿼리 생성하는 로직 확인

if(!StringUtils.isEmpty("colOrderId")){
    String OrderId[] = StringUtils.htmlSourcePrint(searchObj.get("colOrderId")).split(",");
    String reOrderId = "";
    for(String str : OrderId){
        reOrderId += "'" + str + "',";
    }
    int idx = reOrderId.lastIndexOf(",");

    if(reOrderId.substring(0, idx).length()>3){
        condition.put("colOrderId", reOrderId.substring(0, idx));
    }
}

[ AND  DECODE(O.ERPITEMNO,'',TO_CHAR(O.SOREFITEM),TO_CHAR(O.SOREFITEM||'-'||O.ERPITEMNO) ) IN (@@colOrderId) ]

 

위 내용을 보면 이미 콤마로 날아온 데이터를 다시 분해해서 조합을 하고 있다.

그리고 String을 사용하고 있는데 매우 불편하다.

전부 개선해보고, like문으로 변경해보도록하자

---

리팩토링

if(!StringUtils.isEmpty("colOrderId")){
    String[] orderId = StringUtils.htmlSourcePrint(searchObj.get("colOrderId")).split(",");
    StringBuilder reOrderId = new StringBuilder();
    for (String str : orderId) {
        reOrderId.append("CONCAT(CONCAT(TO_CHAR(O.SOREFITEM), '-'), TO_CHAR(O.ERPITEMNO)) LIKE '")
                .append(str.trim()).append("%' OR ");
    }
    if (reOrderId.length() > 0) {
        reOrderId.setLength(reOrderId.length() - 4);
    }
    if (reOrderId.length() > 0) {
        condition.put("colOrderId", reOrderId.toString());
    }
}

[ AND (@@colOrderId) ]

 

나름 정리했는데도 한눈에 안들어온다.

spring과 spring data jpa사용중이라면 querydsl로 쉽게 해결해버릴텐데....

 

아무튼 위와 같이 리팩토링해봤다.

 

주문번호가 3자리 이하인경우에는 검색이 안되게끔하는 기존로직은 그대로 뒀다.

 

그리고 CONCAT을 사용하면 인덱스 못타는 걸로 아는데 성능상 문제 있는거 아닌가요?

|| 구문을 사용하면 되잖아요 -> 매우 좋은 지적이다 그럴수도 있다.

 

하지만!

자체 프레임워크에서 SQL Injection 공격을 방어하기 위해서 || 구문은 못들어가게끔 처리하고있다.

 

그러면 어떻게 할꺼냐?

concat은 그대로 쓰고, 오라클의 함수기반인덱스(Function-based Index)를 생성해서 사용하면 된다.

like문도 뒤쪽에만 %처리하면 문제없어보인다.

 

함수기반 인덱스 관련 내용은 제일아래 참조페이지를 들어가보면된다.

 

---

 

결과확인

현재 레거시 프로젝트에

테스트코드 방식의 검증이 붙어있지 않다.

 

화면과 쿼리로 확인해보자

 

변경 후 화면 주문번호 입력 시

 

변경 후 주문번호 입력 시 쿼리문

 

AND (CONCAT(CONCAT(TO_CHAR(O.SOREFITEM), '-'), TO_CHAR(O.ERPITEMNO)) LIKE '419495%' OR CONCAT(CONCAT(TO_CHAR(O.SOREFITEM), '-'), TO_CHAR(O.ERPITEMNO)) LIKE '423225-00001%')

 

기존의 방식과 변경 후 방식이 둘다 작동함을 알 수 있다.

좋은방법이 더 있을 것 같지만 -> 우선 컬럼이 두개 찢어져있는거만 통합해버리면 성능이 대폭 좋아 질 것 같아보인다.

 

인덱스도 잘 타고 성능상 문제가 없어보여

여기까지만 하면 될 것 같다.

 

 

 

참고자료

https://gent.tistory.com/622

[Oracle] 함수 기반 인덱스 생성 방법 (Funtion-Based Index)