출처: http://scidb.tistory.com/entry/다양한-검색조건의-튜닝방법
다양한 검색조건의 튜닝방법
업무적으로 볼때 조회화면의 검색조건들의 조합은 참으로 다양하다.
아래의 SQL 을 보면 WHERE 절의 모든 변수(:v_grade , :v_loc , :v_hiredate )에 값이 생략이 가능하다.
즉 모든 변수에 값이 들어올수도 있고 일부만 들어올수도 있고 전체가 안들어 올수도 있다.
where 절의 대부분이 그런조건이라고 가정하면 그런 SQL 들은 튜닝하기가 참 난감하다.
이럴때 당신이라면 어떻게 할것인가?
전통적인 튜닝 방법인 UNION ALL 로 모두 쪼개서 분리 할것인가?
아래의 인덱스 구조와 SQL 을 보자.
EMP 인덱스:
1) PK_EMP ( EMPNO )
2) EMP_IDX1 ( SAL )
3) EMP_IDX2 ( HIREDATE )
4) EMP_IDX3 ( DEPTNO )
DEPT 인덱스 :
1) PK_DEPT ( DEPTNO )
2) DEPT_IDX1 ( LOC )
SALGRADE 인덱스:
1) PK_SALGRADE( GRADE )
2) SALGRADE( HISAL, LOSAL)
1. 다양한 조건검색을 OR 로 처리할경우(원본 SQL)
FROM EMP a,
DEPT b,
SALGRADE c
WHERE a.deptno = b.deptno
and a.sal between c.losal and c.hisal
and (c.grade = :v_grade or :v_grade is null) --> 변수에 NULL 대입
and (b.loc = :v_loc or :v_loc is null) --> 변수에 NULL 대입
and (a.hiredate = :v_hiredate or :v_hiredate is null); --> 변수 :v_hiredate 에 '1980-12-17' 값을 대입한다.
SELECT a.empno, a.ename, a.job, b.dname, a.sal
FROM EMP a,
DEPT b,
SALGRADE c
WHERE a.deptno = b.deptno
and a.sal between c.losal and c.hisal
and c.grade LIKE :v_grade||'%' --> 변수에 NULL 대입
and b.loc LIKE :v_loc||'%' --> 변수에 NULL 대입
and a.hiredate LIKE :v_hiredate||'%'; --> 변수 :v_hiredate 에 '1980-12-17' 값을 대입한다.
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 1 |00:00:00.01 | 21 |
| 2 | NESTED LOOPS | | 1 | 1 |00:00:00.01 | 19 |
|* 3 | TABLE ACCESS FULL | SALGRADE | 1 | 5 |00:00:00.01 | 8 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 1 |00:00:00.01 | 11 |
|* 5 | INDEX RANGE SCAN | EMP_IDX1 | 5 | 14 |00:00:00.01 | 3 |
|* 6 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 1 |00:00:00.01 | 2 |
|* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter((:V_GRADE IS NULL OR "C"."GRADE"=TO_NUMBER(:V_GRADE)))
4 - filter((:V_HIREDATE IS NULL OR "A"."HIREDATE"=TO_DATE(:V_HIREDATE,'YYYY-MM-DD')))
5 - access("A"."SAL">="C"."LOSAL" AND "A"."SAL"<="C"."HISAL")
6 - filter(("B"."LOC"=:V_LOC OR :V_LOC IS NULL))
7 - access("A"."DEPTNO"="B"."DEPTNO")
해석 : 위 PLAN 을 보면 :v_hiredate 에 값이 들어 왔으므로 당연히 EMP_IDX2 인덱스를 먼저
ACCESS 해야 하지만 엉뚱한 테이블 부터 ACCESS 하여서 비효율이 발생 하였다.
즉 동적으로 변수값이 들어옴에 따라 PLAN 을 최적화 하지 못한다는 의미이다.
물론 버젼이 11g 라면 동적으로 최적화 할수 있는 기능(Adaptive Cursor sharing)이 있지만
항상 그렇게 되는건 아니다.
그러면 이런 문제를 해결하기위해 어떻게 해야 할까?
아래의 2~4 번에 해답이 있다.
2.엑세스 형태별로 UNION ALL 로 분리함
먼저 UNION ALL 로 분리하는 기준은 똑똑한 조건에 먼저 우선순위를 주었다.
다시말하면 :v_hiredate 는 굉장히 똑똑한 조건이므로 값이 들어오면 :v_grade 나 :v_loc 에 값이
들어오던 들어오지 않던 대세에 지장이 없다는 의미이다.
마찬가지 방법으로 :v_hiredate 가 들어오지 않는 상황에서는 두번째로 똑똑한 조건인 :v_grade 에
값이 들어오면 :v_loc 이 들어오던 들어오지 않던 중요하지 않다는 의미이다.
따라서 순서는 :v_hiredate --> :v_grade --> :v_loc 로 하였다.
실행시에 다른변수에는 값을 넣지않고 :v_hiredate 만 '1980-12-17' 값을 대입한다.
WHERE a.deptno = b.deptno
and a.sal between c.losal and c.hisal
and (c.grade = :v_grade or :v_grade is null)
and (b.loc = :v_loc or :v_loc is null)
and a.hiredate = :v_hiredate and :v_hiredate is not null
UNION ALL
SELECT .... --> :v_hiredate 가 안들어 오고 :v_grade 가 들어올때
WHERE a.deptno = b.deptno
and a.sal between c.losal and c.hisal
and c.grade = :v_grade
and (b.loc = :v_loc or :v_loc is null)
and :v_hiredate is null and :v_grade is not null
UNION ALL
SELECT .... --> :v_hiredate 가 안들어 오고 :v_grade 가 안들어오고 :v_loc 가 들어올때
WHERE a.deptno = b.deptno
and a.sal between c.losal and c.hisal때
and c.grade = :v_grade
and b.loc =:v_loc
and :v_hiredate is null and :v_grade is null and :v_loc is not null
UNION ALL
SELECT .... --> 변수에 아무것도 안들어 왔을때
WHERE a.deptno = b.deptno
and a.sal between c.losal and c.hisal
and :v_hiredate is null
and :v_grade is null
and :v_loc is null ;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 1 | UNION-ALL | | 1 | 1 |00:00:00.05 | 8 |
|* 2 | FILTER | | 1 | 1 |00:00:00.05 | 8 |
|* 3 | TABLE ACCESS BY INDEX ROWID | SALGRADE | 1 | 1 |00:00:00.05 | 8 |
| 4 | NESTED LOOPS | | 1 | 3 |00:00:00.08 | 7 |
| 5 | NESTED LOOPS | | 1 | 1 |00:00:00.04 | 5 |
| 6 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 1 |00:00:00.02 | 3 |
|* 7 | INDEX RANGE SCAN | EMP_IDX2 | 1 | 1 |00:00:00.01 | 2 |
|* 8 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 |00:00:00.02 | 2 |
|* 9 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 |00:00:00.01 | 1 |
|* 10 | INDEX RANGE SCAN | SALGRADE_IDX1 | 1 | 1 |00:00:00.01 | 2 |
|* 11 | FILTER | | 1 | 0 |00:00:00.01 | 0 |
| 12 | NESTED LOOPS | | 0 | 0 |00:00:00.01 | 0 |
| 13 | NESTED LOOPS | | 0 | 0 |00:00:00.01 | 0 |
| 14 | TABLE ACCESS BY INDEX ROWID | SALGRADE | 0 | 0 |00:00:00.01 | 0 |
|* 15 | INDEX RANGE SCAN | PK_SALGRADE | 0 | 0 |00:00:00.01 | 0 |
| 16 | TABLE ACCESS BY INDEX ROWID | EMP | 0 | 0 |00:00:00.01 | 0 |
|* 17 | INDEX RANGE SCAN | EMP_IDX1 | 0 | 0 |00:00:00.01 | 0 |
|* 18 | TABLE ACCESS BY INDEX ROWID | DEPT | 0 | 0 |00:00:00.01 | 0 |
|* 19 | INDEX UNIQUE SCAN | PK_DEPT | 0 | 0 |00:00:00.01 | 0 |
|* 20 | FILTER | | 1 | 0 |00:00:00.01 | 0 |
|* 21 | TABLE ACCESS BY INDEX ROWID | SALGRADE | 0 | 0 |00:00:00.01 | 0 |
| 22 | NESTED LOOPS | | 0 | 0 |00:00:00.01 | 0 |
| 23 | NESTED LOOPS | | 0 | 0 |00:00:00.01 | 0 |
| 24 | TABLE ACCESS BY INDEX ROWID| DEPT | 0 | 0 |00:00:00.01 | 0 |
|* 25 | INDEX RANGE SCAN | DEPT_IDX1 | 0 | 0 |00:00:00.01 | 0 |
| 26 | TABLE ACCESS BY INDEX ROWID| EMP | 0 | 0 |00:00:00.01 | 0 |
|* 27 | INDEX RANGE SCAN | EMP_IDX3 | 0 | 0 |00:00:00.01 | 0 |
|* 28 | INDEX RANGE SCAN | PK_SALGRADE | 0 | 0 |00:00:00.01 | 0 |
|* 29 | FILTER | | 1 | 0 |00:00:00.01 | 0 |
| 30 | MERGE JOIN | | 0 | 0 |00:00:00.01 | 0 |
| 31 | SORT JOIN | | 0 | 0 |00:00:00.01 | 0 |
|* 32 | HASH JOIN | | 0 | 0 |00:00:00.01 | 0 |
| 33 | TABLE ACCESS FULL | DEPT | 0 | 0 |00:00:00.01 | 0 |
| 34 | TABLE ACCESS FULL | EMP | 0 | 0 |00:00:00.01 | 0 |
|* 35 | FILTER | | 0 | 0 |00:00:00.01 | 0 |
|* 36 | SORT JOIN | | 0 | 0 |00:00:00.01 | 0 |
| 37 | INDEX FULL SCAN | SALGRADE_IDX1 | 0 | 0 |00:00:00.01 | 0 |
---------------------------------------------------------------------------------------------------
Predicate Information (지면관계상 생략)
해석 : PLAN 이 개발자가 의도한대로 분리되었고 A-Rows 와 Buffers 를 보면 분리된 SQL 중에서 첫번째
SQL 만 값이 있다.
하지만 최적의 SQL 이 되려면 아직도 멀었다.
3.UNION ALL 로 분리된 각각의 SQL 최적화
:v_grade 에 값이 들어오지 않는다면 더이상 SALGRADE 테이블은 필요가 없다.
과감히 FROM 절에서 삭제하자.
물론 a.sal 컬럼의 값에 NULL 이 있다면 답이 달라지므로 주의해야 한다.
FROM EMP a,
DEPT b,
SALGRADE c
WHERE a.deptno = b.deptno
and a.sal between c.losal and c.hisal
and (c.grade = :v_grade or :v_grade is null)
and (b.loc = :v_loc or :v_loc is null)
and a.hiredate = :v_hiredate
and :v_hiredate is not null
UNION ALL
SELECT ....
FROM EMP a,
DEPT b,
SALGRADE c
WHERE a.deptno = b.deptno
and a.sal between c.losal and c.hisal
and c.grade = :v_grade
and (b.loc = :v_loc or :v_loc is null)
and :v_hiredate is null
and :v_grade is not null
UNION ALL
SELECT ....
FROM EMP a,
DEPT b --> SALGRADE 테이블은 필요가 없음
WHERE a.deptno = b.deptno
and b.loc =:v_loc
and :v_hiredate is null
and :v_grade is null
and :v_loc is not null
UNION ALL
SELECT ....
FROM EMP a,
DEPT b --> SALGRADE 테이블은 필요가 없음
WHERE a.deptno = b.deptno
and :v_hiredate is null
and :v_grade is null
and :v_loc is null ;
PLAN 정보 및 Predicate Information (지면관계상 생략)
4.NVL 혹은 DECODE 함수의 활용
UNION ALL 로 분리하면 옵티마이져 입장에서는 환영할 일이지만 개발자 입장에서 보면 반복적인 코딩이 증가하고 유지보수시 일량이 늘어나는 단점이 있다.
그렇다면 코딩량을 줄일수 있는 최적의 방법은 없는것일까?
물론 방법이 있다.
아래의 SQL 을 보자.
아래의 SQL 은 UNION ALL로 분리된 SQL 중에서 마지막 2개의 SQL 을 합친 것이다.
FROM EMP a,
DEPT b
WHERE a.deptno = b.deptno
and b.loc = decode(:v_loc, null, b.loc, :v_loc) --:V_LOC 에 'CHICAGO' 대입
and :v_hiredate is null
and :v_grade is null
and :v_loc is not null
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------
| 1 | CONCATENATION | | 1 | 6 |00:00:00.03 | 7 |
|* 2 | FILTER | | 1 | 0 |00:00:00.01 | 0 |
| 3 | TABLE ACCESS BY INDEX ROWID | EMP | 0 | 0 |00:00:00.01 | 0 |
| 4 | NESTED LOOPS | | 0 | 0 |00:00:00.01 | 0 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 0 | 0 |00:00:00.01 | 0 |
|* 6 | INDEX FULL SCAN | DEPT_IDX1 | 0 | 0 |00:00:00.01 | 0 |
|* 7 | INDEX RANGE SCAN | EMP_IDX3 | 0 | 0 |00:00:00.01 | 0 |
|* 8 | FILTER | | 1 | 6 |00:00:00.03 | 7 |
| 9 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 6 |00:00:00.03 | 7 |
| 10 | NESTED LOOPS | | 1 | 8 |00:00:00.15 | 5 |
| 11 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 |00:00:00.01 | 3 |
|* 12 | INDEX RANGE SCAN | DEPT_IDX1 | 1 | 1 |00:00:00.01 | 2 |
|* 13 | INDEX RANGE SCAN | EMP_IDX3 | 1 | 6 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter((:V_LOC IS NOT NULL AND :V_GRADE IS NULL AND :V_HIREDATE IS NULL AND :V_LOC IS NULL))
6 - filter("B"."LOC" IS NOT NULL)
7 - access("A"."DEPTNO"="B"."DEPTNO")
8 - filter((:V_LOC IS NOT NULL AND :V_GRADE IS NULL AND :V_HIREDATE IS NULL AND :V_LOC IS NOT NULL))
12 - access("B"."LOC"=:V_LOC)
13 - access("A"."DEPTNO"="B"."DEPTNO")
해석 : DECODE 함수를 사용함으로써 맨마지막 2개의 SQL 을 합쳤으나 옵티마이져가 조건이 들어오는 경우와
들어오지 않는경우를 옵티마이져는 자동으로 UNION ALL 로 분리하였다.
그러나 항상 이렇게 분리되는것은 아니므로 주의를 요한다.
5.SQL 의 최종모습
FROM EMP a,
DEPT b,
SALGRADE c
WHERE a.deptno = b.deptno
and a.sal between c.losal and c.hisal
and (c.grade = :v_grade or :v_grade is null)
and (b.loc = :v_loc or :v_loc is null)
and a.hiredate = :v_hiredate
and :v_hiredate is not null
UNION ALL
SELECT a.empno, a.ename, a.job, b.dname, a.sal
FROM EMP a,
DEPT b,
SALGRADE c
WHERE a.deptno = b.deptno
and a.sal between c.losal and c.hisal
and c.grade = :v_grade
and (b.loc = :v_loc or :v_loc is null)
and :v_hiredate is null
and :v_grade is not null
UNION ALL
SELECT a.empno, a.ename, a.job, b.dname, a.sal
FROM EMP a,
DEPT b
WHERE a.deptno = b.deptno
and b.loc = nvl(:v_loc, b.loc)
and :v_hiredate is null
and :v_grade is null ;
결론 : 검색화면의 경우 다양한 검색조건들이 들어올수 있다.
기본적인 전략은 아래와 같이 순서대로 3가지 이다.
1.똑똑한 조건을 기준으로 UNION ALL 로 분리한다.(2번에 해당)
2.UNION ALL 로 분리된 각각의 SQL 을 최적화 한다.(3번에 해당) --> FROM 절의 테이블 갯수가 달라진다.
3.DECODE 나 NVL 을 사용함으로서 과도한 UNION ALL 로 분리되어 거대해지고
Shared Pool 에 무리를 줄수도 있는 SQL 을 통합하여 하나로 만든다.(4번에 해당)
