화면의 조회조건이 많을때의 SQL형태처리 - 다이나믹 태그문제
Updated:
1. 개요
화면을 개발하때 사용자들은 하나의 화면에 자신이 보고싶어하는 정보를 한꺼번에 보고 싶어하는 습성이 있고, 개발자들은 프레임워크나 WAS, 컨테이너상의 자바에서 SQL을 구현하는데 이때 이 1개의 SQL이 사용자가 요구하는 모든 조회조건을 처리하지 못하고 조회조건 갯수 * N승 개 만큼 나와서 여러가지 문제가 발생하는데 이를 금융권에서는 다이나믹 태그문제라고 부르고 있습니다. SI업계에서 개발할떄 가장 골치아픈 문제가 바로 이 문제인데 지금부터 한번 알아보겠습니다.
가. 다이나믹태그 문제
1) 정의
다이나믹태그란 무엇일까요? 인터넷에 검색을 해봐도 정확한 정의는 나와있지 않습니다. 다만 SQL 프로그램상 정의와 흔히 개발자들이 특히 금융권에서 개발할때 습관적으로 사용하는 단어등 몇가지 예를 찾아볼수 있었습니다. 사용하는 의미가 상당히 다릅니다. 프로그램상에서는 사전에 컴파일해놓은 SQL문장에 변수값만 필요에 따라 바뀌는 방식을 이야기합니다. 금융권 개발자들의 의미는 화면상의 SQL 조건이 다양하게 바뀔때 자바 프로그램상에서 SQL WHERE조건을 처리하는 방식을 이야기합니다. 그래서 이칼럼에서는 금융권 개발자들 입장에서 설명하도록 하겠습니다.
2) 다이나믹 태그의 문제점
다이나믹 태그형태를 SQL을 개발하면 화면의 검색조건이 10개일때 모두 2 * 10승개 즉 1024개의 SQL형태가 나옵니다. 개발할때 분명히 자바에서는 SQL하나만 만들었지만 실제 DBMS에 전달되는것은 WHERE 조건마다 SQL이 다르게 나오는 형태, 즉 다이나믹 SQL형태로 DB로 전달되기 때문에 1024개 형태가 나올수 있습니다. 화면의 조회조건이 3개이면 2 * 3승 해서 모두 8개의 조회조건이 다른 SQL이 나옵니다.
만약 1024개의 SQL형태가 나오는 화면이라면 이 SQL이 접근하는 테이블이 하나라고 가정하면 WHERE형태가 1024개인데 어떻게 인덱스를 형태에 맞춰서 만들어 줄수 있을까요? 아마 몇백개정도 만들어줘야 되지 않을까요? 바로 이 문제가 다이나믹 태그 문제입니다. 우리가 여기서 해법은 SQL을 1개만 사용해서 이문제를 해결할수 있습니다.
다만 개발자들이 이문제를 모르고 계신분들이 상당수 있기 때문에 이분들을 위해 이 칼럼을 쉽게 작성할려고 노력하였습니다. 튜닝 컨설팅을 나가보면 이 문제를 인식하지 못하고 개발한 곳에서는 성능 문제가 많이 나타납니다. 예를 들어 상업용 홈페이지를 하나 만들었는데 이 업체는 이 물품으로 생계를 꾸려가는곳이었고 개발할때 다이나믹 태그 문제를 인식하지 못한 업체가 개발을 하게되어, 이상한 성능문제가 나타나는 곳이었습니다.
가끔가다 시스템이 행업이 걸려 며칠동안 시스템이 마비되는 현상이 일어났는데 필자가 들어가서 분석해보니, 콜센터직원이 핸드폰번호로 사용자들을 검색하면 문제없는 화면에서 이름까지 넣어 검색하게 되면 악성SQL이 만들어져 그 시스템을 행업상태로 빠뜨리게 되는것이었습니다. 그 문제를 찾지못해 몇년동안 그 회사에 판매량 감소와 신뢰도 하락, 운영자들의 고통은 말할수 없을 정도였습니다. 이 문제는 간단하게 인덱스를 하나 더 만들어서 해결 완료 해줬습니다.
개발하는 방법을 산업군별로 살펴보면 금융권중 증권, 은행등에서는 이런일이 발생할수가 없습니다. 왜나면 사전에 이문제를 인식하고 필수조건을 반드시 지정하고 튜너들이 상당기간 튜닝을 수행하기 때문에 화면상 조건에 따른 속도저하 문제는 걱정할 필요가 없을 정도입니다. 그외 산업군은 이 문제에 대해 잘 알고 있는 분들이 많지 않습니다.
나. 다양한 조화조건이 있는경우 처리방법
고객들은 화면에 다양한 조회조건을 넣고 싶어합니다. 개발자들은 다음의 여러가지 방식중 한가지를 선택해서 화면을 개발할수 있습니다. 첫번째 OR SQL형태, 두번째 UNION ALL 형태, 세번째 NVL/DECODE/CASE 사용, 네번째 :1 IS NULL OR절 형태, 다섯째 If Then Else 로직, 여섯째 Dynamic Tag 사용형태등이 있습니다. 각각의 SQL형태마다 장단점이 있으며 이를 잘 알고 개발할때 사용하여야 합니다.
2. 예제
3개의 조회조건이 있는 화면을 예로 들어보겠습니다. 가번 다이나믹 태그 형태를 보면 필수조건이 없을경우 SQL형태가 모두 8개로 나타날수 있으며 아래와 같으며 인덱스를 각 경우의 수에 맞게끔 만들어 줘야 합니다.
만약 화면에 10개의 검색조건이 있고 필수조건이 없다면 모두 1024개 SQL형태가 만들어질수 있으며 이는 최소 몇백개 이상의 인덱스를 만들어 줘야 하는 불상사가 생깁니다. 다 못만들어 주겠죠? 그래서 특정조건을 검색하면 악성 SQL이 만들어지게 되고 화면 속도가 느려지고 시스템상이 성능문제가 발생합니다. 이때 필수조건을 잘 지정하게 되면 WHERE조건의 분기 갯수가 확 줄어드는 효과가 있으며 인덱스를 생성하는 갯수도 줄어들게 됩니다.
나번 원쿼리형태의 경우는 1개의 SQL로 8개의 다양한 WHERE절 검색조건을 SQL 1개로 처리할수 있습니다. 이제 이해되시나요?
가. 다이나믹 태그 형태를 사용한경우
-- 자바상에서는 IF THEN ELSE로직을 사용해서 1개 형태의 SQL을 처리하나 실제 DB에 전달되는 SQL은 아래와 같이
-- 8개 형태가 전달됩니다.
select c1, c2, c3 from t1 where c1 = :2;
select c1, c2, c3 from t1 where c2 = :3;
select c1, c2, c3 from t1 where c3 = :4;
select c1, c2, c3 from t1 where c1 = :2 and c2 = :3;
select c1, c2, c3 from t1 where c1 = :2 and c3 = :4;
select c1, c2, c3 from t1 where c2 = :2 and c3 = :4;
select c1, c2, c3 from t1 where c1 = :2 and c2 = :3 and c3 = :4;
select c1, c2, c3 from t1;
나. 원쿼리형태의 Static SQL형태
Select c1, c2, c3
from t1
where (:1 is null or c1 = :1)
and (:2 is null or c2 = :2)
and (:3 is null or c3 = :3);
가. OR절 사용형태
OR절을 사용하여 원쿼리로 표현할수 있습니다. 그러나 분기갯수가 여전히 8개를 다 적어 줘야합니다. 만일 1024개 분기가 필요하다면 SQL 라인이 최소 1000줄 이상이 되어야 하겠죠?
select /*+use_concat*/ c1, c2, c3
from t1
where :1 = 1 and c1 = :2
or ( :1 = 2 and c2 = :3)
or ( :1 = 3 and c3 = :4)
or ( :1 = 4 and c1 = :5 and c2 = :6)
or ( :1 = 5 and c1 = :7 and c3 = :8)
or ( :1 = 6 and c2 = :9 and c3 = :10)
or ( :1 = 7 and c1 = :11 and c2 = :12 and c3 = :13);
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 CONCATENATION (cr=4 pr=0 pw=0 time=0 us)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN T1_IX4 (cr=0 pr=0 pw=0 time=0 us cost=3 size=12 card=1)OF T1_IX4 (NONUNIQUE)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID T1 (cr=0 pr=0 pw=0 time=0 us cost=4 size=12 card=1)
0 INDEX RANGE SCAN T1_IX3 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)OF T1_IX3 (NONUNIQUE)
1 FILTER (cr=4 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN T1_IX4 (cr=4 pr=0 pw=0 time=0 us cost=3 size=12 card=1)OF T1_IX4 (NONUNIQUE)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN T1_IX4 (cr=0 pr=0 pw=0 time=0 us cost=3 size=12 card=1)OF T1_IX4 (NONUNIQUE)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID T1 (cr=0 pr=0 pw=0 time=0 us cost=4 size=12 card=1)
0 INDEX RANGE SCAN T1_IX3 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)OF T1_IX3 (NONUNIQUE)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN T1_IX4 (cr=0 pr=0 pw=0 time=0 us cost=3 size=12 card=1)OF T1_IX4 (NONUNIQUE)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=0 us cost=334 size=230772 card=19231)
나. Union ALL 사용형태
UNION ALL형태를 사용하여 처리할수 있습니다. UNION ALL형태는 조건에 따라 정확한 실행계획을 만들수 있는 장점이 있으나 여전히 코딩량이 많습니다. 1024개 SQL형태를 다 적어줘야 한다면 어머어마한 코딩량이 나올것 같네요.
select /*+use_concat */ c1, c2, c3
from t1
where :1 = 1 and c1 = :2
UNION ALL
select c1, c2, c3
from t1
WHERE ( :1 = 2 and c2 = :3)
UNION ALL
select c1, c2, c3
from t1
WHERE ( :1 = 3 and c3 = :4)
UNION ALL
select c1, c2, c3
from t1
WHERE ( :1 = 4 and c1 = :5 and c2 = :6)
UNION ALL
select c1, c2, c3
from t1
WHERE ( :1 = 5 and c1 = :7 and c3 = :8)
UNION ALL
select c1, c2, c3
from t1
WHERE ( :1 = 6 and c2 = :9 and c3 = :10)
UNION ALL
select c1, c2, c3
from t1
WHERE ( :1 = 6 and c1 = :11 and c2 = :12 and c3 = :13);
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 UNION-ALL (cr=4 pr=0 pw=0 time=0 us)
1 FILTER (cr=4 pr=0 pw=0 time=0 us)
1 INDEX RANGE SCAN T1_IX4 (cr=4 pr=0 pw=0 time=0 us cost=3 size=12 card=1)OF T1_IX4 (NONUNIQUE)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL T1 (cr=0 pr=0 pw=0 time=0 us cost=333 size=230772 card=19231)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID T1 (cr=0 pr=0 pw=0 time=0 us cost=4 size=12 card=1)
0 INDEX RANGE SCAN T1_IX3 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)OF T1_IX3 (NONUNIQUE)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN T1_IX4 (cr=0 pr=0 pw=0 time=0 us cost=3 size=12 card=1)OF T1_IX4 (NONUNIQUE)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN T1_IX4 (cr=0 pr=0 pw=0 time=0 us cost=3 size=12 card=1)OF T1_IX4 (NONUNIQUE)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID T1 (cr=0 pr=0 pw=0 time=0 us cost=4 size=12 card=1)
0 INDEX RANGE SCAN T1_IX3 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)OF T1_IX3 (NONUNIQUE)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN T1_IX4 (cr=0 pr=0 pw=0 time=0 us cost=3 size=12 card=1)OF T1_IX4 (NONUNIQUE)
다. NVL/DECODE/CASE 사용형태
이형태는 금융권에서 많이 사용하는 SQL형태인데 장점은 SQL 코딩량은 최소한으로 줄었으나 모든 경우를 처리해줍니다. 단점으로는 SQL실행계획 제어가 좀 어렵습니다. 제조, 공공, 통신과 같이 SQL 코딩량이 긴 산업군에서는 사용하기 힘들것으로 생각됩니다.
1) 사용형태
가. NVL형태
Select c1, c2, c3
from t1
where c1 = nvl(:1, c1)
and c2 = nvl(:2, c2)
and c3 = nvl(:3, c3)
Rows Row Source Operation
----------------------------------------------------------------------------------------------
0 STATEMENT
1 CONCATENATION (cr=1179 pr=0 pw=0 time=0 us)
1 FILTER (cr=1179 pr=0 pw=0 time=0 us)
1 TABLE ACCESS FULL T1 (cr=1179 pr=0 pw=0 time=0 us cost=338 size=12 card=1)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID T1 (cr=0 pr=0 pw=0 time=0 us cost=4 size=12 card=1)
0 INDEX RANGE SCAN T1_IX3 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)OF T1_IX3 (NONUNIQUE)
나. DECODE/CASE 형태
select /*+use_concat */ c1, c2, c3
from t1
where c1 = decode(:1, null, c1, :1)
and c2 = decode(:2, null, c2, :2)
and c3 = decode(:3, null, c3, :3)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
1 CONCATENATION (cr=1179 pr=0 pw=0 time=0 us)
1 FILTER (cr=1179 pr=0 pw=0 time=0 us)
1 TABLE ACCESS FULL T1 (cr=1179 pr=0 pw=0 time=0 us cost=340 size=12 card=1)
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID T1 (cr=0 pr=0 pw=0 time=0 us cost=4 size=12 card=1)
0 INDEX RANGE SCAN T1_IX3 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)OF T1_IX3 (NONUNIQUE)
2) NVL, DECODE, CASE를 사용한 경우 주의점
해당 칼럼이 NOT NULL 이어야 하며 NULL이 허용되는 칼럼이면 값이 잘못되어 나오므로 OR 칼럼 IS NULL 을 하나더 붙여줘야 합니다. IS NULL OR을 넣지않으면 null = null 형태가 되어 false를 리턴하므로 NULL 같은
잘못된 값이 나옵니다.
가. 잘못된 SQL
Select c1, c2, c3
from t1
where c1 = nvl(:1, c1)
and c2 = nvl(:2, c2)
and c3 = nvl(:3, c3); --c3가 null이 허용되는 칼럼인경우
나. 정확한 SQL
Select c1, c2, c3
from t1
where c1 = nvl(:1, c1)
and c2 = nvl(:2, c2)
and (c3 is null or c3 = nvl(:3, c3));
라. IS NULL OR 절 사용형태
우리회사에서 기본적으로 권고하는 방식이 이 방식입니다. 아래의 SQL형태는 칼럼의 NULL 사용여부와 상관없이 사용할수 있는 형태입니다. 코딩량도 작고 여러가지 장점이 많은 형태이지만 여전히 SQL 실행계획 제어는 좀 어렵다고 생각됩니다.
가번SQL 형태에서 C1에 PK가 생성되어 있다면 (:1 is null or c1 = :1) 조건도 인덱스 사용 가능합니다.
그러나 Unique, 일반 인덱스 일 경우 사용 불가합니다. 나번SQL에서 C2가 필수조건으로 지정되면 IS NULL 항목이 없어지고 나번 SQL 형태처럼 사용이 가능합니다.
가. SQL
Select /*+USE_CONCAT*/ c1, c2, c3
from t1
where (:1 is null or c1 = :1)
and (:2 is null or c2 = :2)
and (:3 is null or c3 = :3);
나. 필수칼럼 지정SQL
Select /*+USE_CONCAT*/ c1, c2, c3
from t1
where (:1 is null or c1 = :1)
and c2 = :2
and (:3 is null or c3 = :3);
3. 결론 및 권고사항
WHERE 조건이 다양하게 바뀔경우
-
필수칼럼을 반드시 지정하고 OR절/UNION ALL/IS NULL OR절을 활용한 원쿼리방식의 Static SQL 형태로 사용 권고합니다. 필수조건을 만들고 그기에 인덱스를 생성해주는것이 가장 좋습니다.
1) 필수조건은 분기처리 갯수를 획기적으로 줄여줄수 있으며, 다이나믹태그 문제를 방지할수 있다.
2) 필수조건으로 가능한 기간들을 지정 하지말고, 만일 어쩔수 없는 상황이라면 대체적으로 일/주단위가 적합하며 월/년 단위일경우 성능테스트가 필요합니다.
3) 금융권에서는 STATIC형태의 원쿼리로 다이나믹태그 형태를 방지하는것이 적합하며, 제조등 산업군은 다이나믹태그 형태를 권장하지만 필수조건을 반드시 지정 권고합니다.왜냐하면 제조등 산업군은 개발자들이 다이나믹태그형태를 많이 사용하며 데이타처리 요구사항이 상당히 복잡하여 SQL라인이 긴 경우가 많습니다. 다이나믹 태그형태는 실행계획 조정 용이하고 사용 편리성등이 좋습니다. -
Where 조건절이 매우 많은 어쩔수 없는 경우에는 Dynamic Tag 사용가능(중요하지않고 빈번하지 않는 화면일경우)
1) Dynamic Tag형태는 파싱문제나 래치경합등을 발생할 가능성 높아짐.
2) Dynamic vs Static SQL 장단점 고려(가능한한 OR조건을 사용하고 상황에 따라 다른 케이스도 사용가능)
4. 결언
이번 칼럼은 개발자나 아키텍트라면 반드시 알아야할 다이나믹 태그문제 였습니다. 전에는 원쿼리형태의 Static SQL 형태를 권고하였는데 이제는 다이나믹 테그도 필요하면 사용하라고 이야기합니다. 많이 후퇴한셈이죠.
개념만 알고 있어도 나중에 화면 개발할때나 장애처리시 유용하게 사용할수 있습니다. 필자도 시스템 튜닝이나 장애처리 지원을 할때는 반드시 개발자들한테 물어보는 사항이 이 문제입니다. 어떻게 화면 구성을 해봤는지 알아야 튜닝 전략도 세울수 있고 장애처리 할때도 이 문제를 감안해야합니다. 오늘도 좋은하루 되세요. ^^