'동적 검색조건'에 해당되는 글 1건

  1. 2010.12.14 검색조건을 동적으로 바꾸어야 할 때 (1)

-동적인 조회조건에서 SQL 작성법
-
다양한 검색조건에서 SQL 튜닝방법

-쿼리변환의 부정적 측면 해결

 

아래는 신입사원과 김대리의 대화내용이다. 신입사원이 머리를 긁고 있다. 문제가 어려운 모양이다.

 

신입사원: 상황에 따라서 조회조건이 달라지는데 어떻게 처리하죠?

김대리: 각각의 상황에 대해 union all로 처리하고 서로 다른 SQL로 처리하면 되.

신입사원: 네 알겠습니다. (조금 후에) 김대리님, 그렇게 하면 SQL이 너무 길어서 복잡해져요.

          6가지의 조건이 상황에 따라 달라지기 때문이죠.  

김대리: 그럼 방법이 없지. Dynamic SQL로 작성해. Dynamic SQL을 쓰되 바인드 변수를 사용해야 돼.

신입사원: 그건 어떻게 사용하죠? 제가 Dynamic SQL 사용법을 몰라서 그럽니다.

김대리: 내가 조금 있다가 가르쳐 줄게.

신입사원: 감사합니다.

 

이런 상황에서는 Union all로 여러 개의 SQL을 작성하는 것 보다는 Dynamic SQL을 사용하는 것이 해결책이 될 수 있다. 또한 많은 사람들이 그렇게 하고 있다. 하지만 꼭 둘 중에 하나만 골라야 한다는 생각은 버려야 한다. 그렇지 않으면 Union all을 사용하여 SQL이 매우 길어지거나 C JAVA 언어의 도움을 받아 IF Then ELSE 로직으로 SQL을 동적으로 생성하는 불리함을 감수해야 한다. 따라서 이보다 더 쉽고 간단한 방법이 있다면 그것을 사용하면 된다.

 

환경

DBMS: Oracle11g R2

각 테이블의 PK 인덱스는 이미 존재하므로 추가적인 인덱스만 설명한다.

EMP_MGR_HR_DT_IX: employee( manager_id + hire_date )

EMP_DEPT_IX : employee( department_id )

EMP_JOB_IX : employee( job_id )

 

다양한 조회조건을 제외하면 SQL은 다음과 같이 단순하다.

 

SELECT e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

 

여기까지는 SQL이 쉽다. 하지만 여기서부터는 까다로운 요구사항 때문에 SQL에 분기가 발생한다. 원래는 6가지의 where 조건을 적용해야 하지만 지면관계상 요구사항은 네 가지로 한정한다.

 

업무 요구사항

l  네 가지 패턴으로 조회조건이 들어온다. 각각의 패턴들은 :v_delimit(구분자)로 식별이 가능하다.

l  패턴 1  :v_delimit = 1 인 경우는 j.job_id = :v_job 조건으로 조회한다.

l  패턴 2  :v_delimit = 2 인 경우는 e.manager_id = :v_emp AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to 조건으로 조회한다.

l  패턴 3  :v_delimit = 3 인 경우는 d.department_id = :v_dept 조건으로 조회한다.

l  패턴 4  :v_delimit = 4 인 경우는 l.location_id = :v_loc 조건으로 조회한다. 

l  모든 패턴 1~4 filter 조건 d.manager_id > 0 가 공통적으로 적용되어야 한다.

 

성능 요구사항

여기까지는 업무팀의 요구사항이지만 개발자의 요구사항도 있다. where 조건이 패턴에 따라 동적으로 변경되면서도 각 패턴의 실행계획을 튜너의 마음대로 조정할 수 있어야 한다. 즉 네 가지 패턴의 SQL에 대해 서로 다른 힌트를 사용할 수 있어야 한다.

 

이런 까다로운 요구사항을 보고 가장 먼저 떠올릴 수 있는 생각은 Union all로 분기하는 것이다. 하지만 이 방법은 SQL이 길어지므로 코딩량을 증가시킨다. 두 번째로 생각할 수 있는 방법은 Dynamic SQL을 사용하는 것이다. 하지만 이 경우는 Where 조건뿐만 아니라 Select 절도 동적으로 변경되어야 한다. 왜냐하면 구분자의 값에 따라 힌트를 동적으로 만들어야 하기 때문이다. 따라서 우리는 이런 방법들을 사용하지 않을 것이다.

아래의 SQL을 실행할 때는 구분자인
:v_delimit의 값에 1을 대입해야 한다. 즉 패턴 1의 경우이다. 따라서 :v_job :v_delimit를 제외한 나머지 변수 값은 모두 null이다.

 

SELECT /*+ USE_CONCAT */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND (   ( :v_delimit = 1 AND j.job_id = :v_job )               --> :v_delimit = 1 입력, :v_job = 'SA_MAN' 입력

        OR ( :v_delimit = 2 AND e.manager_id = :v_emp

                            AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to )

        OR ( :v_delimit = 3 AND d.department_id = :v_dept )

        OR ( :v_delimit = 4 AND l.location_id = :v_loc   )

       )

   AND d.manager_id > 0;

 

OR를 Union all로 바꿔서 생각한다면 이해가 빠를 것이다. 복잡한 요구사항을 만족하면서도 SQL이 매우 가벼워졌다. Union all을 사용한 경우와 SQL을 비교해 보기 바란다. 길이는 많이 짧아졌지만 Union all을 사용할 때와 성능상 동일하다. 다시 말해 실행시점에서 하나의 SQL 4개의 SQL로 분리될 것이다. (이를 OR-Expansion 이라 부른다) 이 정도 길이의 SQL 이라면 Union all로 구분하여 SQL을 각각 작성하는 방법이나 Dynamic SQL을 일부러 사용할 필요는 없다. 주의사항은 각 패턴 별로 적절한 인덱스가 있어야 한다는 것이다. 그렇지 않으면 구분자의 의미는 사라질 것이다. 이제 실행계획을 보자.

 

------------------------------------------------------------------------------------------------------------------

| Id  | Operation                         | Name             | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |

------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                  |                  |      1 |      5 |00:00:00.03 |      19 |          |

|   1 |  CONCATENATION                    |                  |      1 |      5 |00:00:00.03 |      19 |          |

|*  2 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |          |

|*  3 |    HASH JOIN                      |                  |      0 |      0 |00:00:00.01 |       0 |  988K (0)|

|   4 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |          |

|   5 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |          |

|   6 |       NESTED LOOPS                |                  |      0 |      0 |00:00:00.01 |       0 |          |

|   7 |        TABLE ACCESS BY INDEX ROWID| LOCATION         |      0 |      0 |00:00:00.01 |       0 |          |

|*  8 |         INDEX UNIQUE SCAN         | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |          |

|*  9 |        TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |          |

|* 10 |         INDEX RANGE SCAN          | DEPT_LOCATION_IX |      0 |      0 |00:00:00.01 |       0 |          |

|* 11 |       INDEX RANGE SCAN            | EMP_DEPT_IX      |      0 |      0 |00:00:00.01 |       0 |          |

|  12 |      TABLE ACCESS BY INDEX ROWID  | EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |          |

|  13 |     TABLE ACCESS FULL             | JOB              |      0 |      0 |00:00:00.01 |       0 |          |

|* 14 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |          |

|* 15 |    HASH JOIN                      |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  16 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  17 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |          |

|* 18 |       TABLE ACCESS BY INDEX ROWID | DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |          |

|* 19 |        INDEX UNIQUE SCAN          | DEPT_ID_PK       |      0 |      0 |00:00:00.01 |       0 |          |

|* 20 |       TABLE ACCESS BY INDEX ROWID | LOCATION         |      0 |      0 |00:00:00.01 |       0 |          |

|* 21 |        INDEX UNIQUE SCAN          | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |          |

|  22 |      TABLE ACCESS BY INDEX ROWID  | EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |          |

|* 23 |       INDEX RANGE SCAN            | EMP_DEPT_IX      |      0 |      0 |00:00:00.01 |       0 |          |

|  24 |     TABLE ACCESS FULL             | JOB              |      0 |      0 |00:00:00.01 |       0 |          |

|* 25 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |          |

|  26 |    NESTED LOOPS                   |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  27 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  28 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  29 |       NESTED LOOPS                |                  |      0 |      0 |00:00:00.01 |       0 |          |

|  30 |        TABLE ACCESS BY INDEX ROWID| EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |          |

|* 31 |         INDEX RANGE SCAN          | EMP_MGR_HR_DT_IX |      0 |      0 |00:00:00.01 |       0 |          |

|  32 |        TABLE ACCESS BY INDEX ROWID| JOB              |      0 |      0 |00:00:00.01 |       0 |          |

|* 33 |         INDEX UNIQUE SCAN         | JOB_ID_PK        |      0 |      0 |00:00:00.01 |       0 |          |

|* 34 |       TABLE ACCESS BY INDEX ROWID | DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |          |

|* 35 |        INDEX UNIQUE SCAN          | DEPT_ID_PK       |      0 |      0 |00:00:00.01 |       0 |          |

|* 36 |      INDEX UNIQUE SCAN            | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |          |

|* 37 |     TABLE ACCESS BY INDEX ROWID   | LOCATION         |      0 |      0 |00:00:00.01 |       0 |          |

|* 38 |   FILTER                          |                  |      1 |      5 |00:00:00.03 |      19 |          |

|* 39 |    HASH JOIN                      |                  |      1 |      5 |00:00:00.03 |      19 |  360K (0)|

|* 40 |     HASH JOIN                     |                  |      1 |      5 |00:00:00.01 |      11 |  385K (0)|

|  41 |      NESTED LOOPS                 |                  |      1 |      5 |00:00:00.01 |       4 |          |

|  42 |       TABLE ACCESS BY INDEX ROWID | JOB              |      1 |      1 |00:00:00.01 |       2 |          |

|* 43 |        INDEX UNIQUE SCAN          | JOB_ID_PK        |      1 |      1 |00:00:00.01 |       1 |          |

|* 44 |       TABLE ACCESS BY INDEX ROWID | EMPLOYEE         |      1 |      5 |00:00:00.01 |       2 |          |

|* 45 |        INDEX RANGE SCAN           | EMP_JOB_IX       |      1 |      5 |00:00:00.01 |       1 |          |

|* 46 |      TABLE ACCESS FULL            | DEPARTMENT       |      1 |     11 |00:00:00.01 |       7 |          |

|* 47 |     TABLE ACCESS FULL             | LOCATION         |      1 |     23 |00:00:00.03 |       8 |          |

------------------------------------------------------------------------------------------------------------------

 

4개의 SQL이 각각 다른 조건의 인덱스로 Driving 되었다. 실행계획도 최적이다. 다시 말해 :v_delimit = 1 이 아닌 경우의 SQL은 전혀 실행되지 않았다. 하지만 만약 Hash Join이 맘에 걸린다면 아래처럼 힌트를 추가할 수 있다. Global Hint를 사용하면 하나의 SQL에는 하나의 힌트만 사용한다는 제약을 극복할 수 있다.

 

SELECT /*+ USE_CONCAT LEADING(@SEL$1_1 l d e j) USE_NL(@SEL$1_1 d e j)

                      LEADING(@SEL$1_2 d e l j) USE_NL(@SEL$1_2 e l j)

                      LEADING(@SEL$1_3 e d l j) USE_NL(@SEL$1_3 d l j)

                      LEADING(@SEL$1_4 j e d l) USE_NL(@SEL$1_4 e d l) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND (   ( :v_delimit = 1 AND j.job_id = :v_job )               --> :v_delimit = 1 입력, :v_job = 'SA_MAN' 입력

        OR ( :v_delimit = 2 AND e.manager_id = :v_emp

                            AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to )

        OR ( :v_delimit = 3 AND d.department_id = :v_dept )

        OR ( :v_delimit = 4 AND l.location_id = :v_loc   )

       )

   AND d.manager_id > 0;

 

-------------------------------------------------------------------------------------------------------

| Id  | Operation                         | Name             | Starts | A-Rows |   A-Time   | Buffers |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                  |                  |      1 |      5 |00:00:00.01 |      20 |

|   1 |  CONCATENATION                    |                  |      1 |      5 |00:00:00.01 |      20 |

|*  2 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |

|   3 |    NESTED LOOPS                   |                  |      0 |      0 |00:00:00.01 |       0 |

|   4 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |

|   5 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |

|   6 |       NESTED LOOPS                |                  |      0 |      0 |00:00:00.01 |       0 |

|   7 |        TABLE ACCESS BY INDEX ROWID| LOCATION         |      0 |      0 |00:00:00.01 |       0 |

|*  8 |         INDEX UNIQUE SCAN         | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|*  9 |        TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |

|* 10 |         INDEX RANGE SCAN          | DEPT_LOCATION_IX |      0 |      0 |00:00:00.01 |       0 |

|  11 |       TABLE ACCESS BY INDEX ROWID | EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |

|* 12 |        INDEX RANGE SCAN           | EMP_DEPT_IX      |      0 |      0 |00:00:00.01 |       0 |

|* 13 |      INDEX UNIQUE SCAN            | JOB_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|  14 |     TABLE ACCESS BY INDEX ROWID   | JOB              |      0 |      0 |00:00:00.01 |       0 |

|* 15 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |

|  16 |    NESTED LOOPS                   |                  |      0 |      0 |00:00:00.01 |       0 |

|  17 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |

|  18 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |

|  19 |       NESTED LOOPS                |                  |      0 |      0 |00:00:00.01 |       0 |

|* 20 |        TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |

|* 21 |         INDEX UNIQUE SCAN         | DEPT_ID_PK       |      0 |      0 |00:00:00.01 |       0 |

|  22 |        TABLE ACCESS BY INDEX ROWID| EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |

|* 23 |         INDEX RANGE SCAN          | EMP_DEPT_IX      |      0 |      0 |00:00:00.01 |       0 |

|* 24 |       TABLE ACCESS BY INDEX ROWID | LOCATION         |      0 |      0 |00:00:00.01 |       0 |

|* 25 |        INDEX UNIQUE SCAN          | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|* 26 |      INDEX UNIQUE SCAN            | JOB_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|  27 |     TABLE ACCESS BY INDEX ROWID   | JOB              |      0 |      0 |00:00:00.01 |       0 |

|* 28 |   FILTER                          |                  |      1 |      0 |00:00:00.01 |       0 |

|  29 |    NESTED LOOPS                   |                  |      0 |      0 |00:00:00.01 |       0 |

|  30 |     NESTED LOOPS                  |                  |      0 |      0 |00:00:00.01 |       0 |

|  31 |      NESTED LOOPS                 |                  |      0 |      0 |00:00:00.01 |       0 |

|  32 |       NESTED LOOPS                |                  |      0 |      0 |00:00:00.01 |       0 |

|  33 |        TABLE ACCESS BY INDEX ROWID| EMPLOYEE         |      0 |      0 |00:00:00.01 |       0 |

|* 34 |         INDEX RANGE SCAN          | EMP_MGR_HR_DT_IX |      0 |      0 |00:00:00.01 |       0 |

|* 35 |        TABLE ACCESS BY INDEX ROWID| DEPARTMENT       |      0 |      0 |00:00:00.01 |       0 |

|* 36 |         INDEX UNIQUE SCAN         | DEPT_ID_PK       |      0 |      0 |00:00:00.01 |       0 |

|* 37 |       TABLE ACCESS BY INDEX ROWID | LOCATION         |      0 |      0 |00:00:00.01 |       0 |

|* 38 |        INDEX UNIQUE SCAN          | LOC_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|* 39 |      INDEX UNIQUE SCAN            | JOB_ID_PK        |      0 |      0 |00:00:00.01 |       0 |

|  40 |     TABLE ACCESS BY INDEX ROWID   | JOB              |      0 |      0 |00:00:00.01 |       0 |

|* 41 |   FILTER                          |                  |      1 |      5 |00:00:00.01 |      20 |

|  42 |    NESTED LOOPS                   |                  |      1 |      5 |00:00:00.01 |      20 |

|  43 |     NESTED LOOPS                  |                  |      1 |      5 |00:00:00.01 |      15 |

|  44 |      NESTED LOOPS                 |                  |      1 |      5 |00:00:00.01 |      13 |

|  45 |       NESTED LOOPS                |                  |      1 |      5 |00:00:00.01 |       6 |

|  46 |        TABLE ACCESS BY INDEX ROWID| JOB              |      1 |      1 |00:00:00.01 |       2 |

|* 47 |         INDEX UNIQUE SCAN         | JOB_ID_PK        |      1 |      1 |00:00:00.01 |       1 |

|* 48 |        TABLE ACCESS BY INDEX ROWID| EMPLOYEE         |      1 |      5 |00:00:00.01 |       4 |

|* 49 |         INDEX RANGE SCAN          | EMP_JOB_IX       |      1 |      5 |00:00:00.01 |       2 |

|* 50 |       TABLE ACCESS BY INDEX ROWID | DEPARTMENT       |      5 |      5 |00:00:00.01 |       7 |

|* 51 |        INDEX UNIQUE SCAN          | DEPT_ID_PK       |      5 |      5 |00:00:00.01 |       2 |

|* 52 |      INDEX UNIQUE SCAN            | LOC_ID_PK        |      5 |      5 |00:00:00.01 |       2 |

|* 53 |     TABLE ACCESS BY INDEX ROWID   | LOCATION         |      5 |      5 |00:00:00.01 |       5 |

-------------------------------------------------------------------------------------------------------

 

힌트에 쿼리블럭명을 사용하였다. 각각의 쿼리블럭명은 DBMS_XPLAN.DISPLAY_CURSOR 함수에 +ALIAS 옵션을 추가하면 조회할 수 있다. 아래의 예제가 그것이다.

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST +ALIAS' ));

 

중간생략

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

   1 - SEL$1 

   7 - SEL$1_1 / L@SEL$1

   8 - SEL$1_1 / L@SEL$1

   9 - SEL$1_1 / D@SEL$1

  10 - SEL$1_1 / D@SEL$1

  11 - SEL$1_1 / E@SEL$1

  12 - SEL$1_1 / E@SEL$1

  13 - SEL$1_1 / J@SEL$1

  14 - SEL$1_1 / J@SEL$1

  20 - SEL$1_2 / D@SEL$1_2

  21 - SEL$1_2 / D@SEL$1_2
중간생략

  53 - SEL$1_4 / L@SEL$1_4

중간생략

 

가장 좌측의 번호는 Plan 상의 id에 해당한다. 쿼리블럭명은 ‘/’을 기준으로 좌측이다. SEL$1_1부터 SEL$1_4까지 쿼리블럭명들을 볼 수 있다. 이것들을 힌트에 사용하면 조건절에 OR로 분기된 SQL이 아무리 많아도 원하는 SQL(쿼리블럭)만을 콕 집어서 실행계획을 변경시킬 수 있다.

 

OR-Expansion  VS  Union All

이제 OR를 이용한 경우와 Union all을 사용한 경우를 비교해보자. 아래의 SQLUnion all로 분기한 경우인데 두가지 단점이 있다. 특히 Oracle11g R2를 사용하는 사람은 눈 여겨 보아야 한다. 여기서도 구분자에는 1을 대입한다. 네가지 SQL의 힌트가 서로 다름을 주목하자.

 

SELECT /*+ leading(j e d l) use_nl(e d l) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND j.job_id = :v_job                   --> ‘SA_MAN’ 입력

   AND d.manager_id > 0

   AND :v_delimit = 1                      --> 1 입력

UNION ALL

SELECT /*+ leading(e d l j) use_nl(d l j) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND e.manager_id = :v_emp

   AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to

   AND d.manager_id > 0

   AND :v_delimit = 2

UNION ALL

SELECT /*+ leading(d e l j) use_nl(e l j) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND d.department_id = :v_dept

   AND d.manager_id > 0

   AND :v_delimit = 3

UNION ALL

SELECT /*+ leading(l d e j) use_nl(d e j) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND l.location_id = :v_loc 

   AND d.manager_id > 0  

   AND :v_delimit = 4 ;

 

단점 1: SQL의 길이가 너무 길다

구분자 별로 OR를 사용할 때보다 SQL이 많이 길어졌다. Union을 사용하는 방법의 단점은 SQL의 길이뿐만이 아니다. Oracle11g R2 에서는 개발자의 의도를 무시하는 결과가 발생할 수 있다. 개발자의 의도란 :v_delimit = 1 인 경우의 SQL만 실행하는 것이다. :v_delimit의 값이 2~4인 경우는 한 블록도 Scan해서는 안 된다. 과연 그렇게 되는지 아래의 Plan을 보자.

 

------------------------------------------------------------------------------------------------------------

| Id  | Operation                            | Name               | Starts | A-Rows |   A-Time   | Buffers |

------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                     |                    |      1 |      5 |00:00:00.01 |      22 |

|   1 |  UNION-ALL                           |                    |      1 |      5 |00:00:00.01 |      22 |

|*  2 |   FILTER                             |                    |      1 |      5 |00:00:00.01 |      20 |

|   3 |    NESTED LOOPS                      |                    |      1 |      5 |00:00:00.01 |      20 |

|   4 |     NESTED LOOPS                     |                    |      1 |      5 |00:00:00.01 |      15 |

|   5 |      NESTED LOOPS                    |                    |      1 |      5 |00:00:00.01 |      13 |

|   6 |       NESTED LOOPS                   |                    |      1 |      5 |00:00:00.01 |       6 |

|   7 |        TABLE ACCESS BY INDEX ROWID   | JOB                |      1 |      1 |00:00:00.01 |       2 |

|*  8 |         INDEX UNIQUE SCAN            | JOB_ID_PK          |      1 |      1 |00:00:00.01 |       1 |

|   9 |        TABLE ACCESS BY INDEX ROWID   | EMPLOYEE           |      1 |      5 |00:00:00.01 |       4 |

|* 10 |         INDEX RANGE SCAN             | EMP_JOB_IX         |      1 |      5 |00:00:00.01 |       2 |

|* 11 |       TABLE ACCESS BY INDEX ROWID    | DEPARTMENT         |      5 |      5 |00:00:00.01 |       7 |

|* 12 |        INDEX UNIQUE SCAN             | DEPT_ID_PK         |      5 |      5 |00:00:00.01 |       2 |

|* 13 |      INDEX UNIQUE SCAN               | LOC_ID_PK          |      5 |      5 |00:00:00.01 |       2 |

|  14 |     TABLE ACCESS BY INDEX ROWID      | LOCATION           |      5 |      5 |00:00:00.01 |       5 |

|* 15 |   FILTER                             |                    |      1 |      0 |00:00:00.01 |       0 |

|  16 |    NESTED LOOPS                      |                    |      0 |      0 |00:00:00.01 |       0 |

|  17 |     NESTED LOOPS                     |                    |      0 |      0 |00:00:00.01 |       0 |

|  18 |      NESTED LOOPS                    |                    |      0 |      0 |00:00:00.01 |       0 |

|  19 |       NESTED LOOPS                   |                    |      0 |      0 |00:00:00.01 |       0 |

|  20 |        TABLE ACCESS BY INDEX ROWID   | EMPLOYEE           |      0 |      0 |00:00:00.01 |       0 |

|* 21 |         INDEX RANGE SCAN             | EMP_MGR_HR_DT_IX   |      0 |      0 |00:00:00.01 |       0 |

|* 22 |        TABLE ACCESS BY INDEX ROWID   | DEPARTMENT         |      0 |      0 |00:00:00.01 |       0 |

|* 23 |         INDEX UNIQUE SCAN            | DEPT_ID_PK         |      0 |      0 |00:00:00.01 |       0 |

|  24 |       TABLE ACCESS BY INDEX ROWID    | LOCATION           |      0 |      0 |00:00:00.01 |       0 |

|* 25 |        INDEX UNIQUE SCAN             | LOC_ID_PK          |      0 |      0 |00:00:00.01 |       0 |

|* 26 |      INDEX UNIQUE SCAN               | JOB_ID_PK          |      0 |      0 |00:00:00.01 |       0 |

|  27 |     TABLE ACCESS BY INDEX ROWID      | JOB                |      0 |      0 |00:00:00.01 |       0 |

|  28 |   MERGE JOIN                         |                    |      1 |      0 |00:00:00.01 |       2 |

|  29 |    TABLE ACCESS BY INDEX ROWID       | JOB                |      1 |      1 |00:00:00.01 |       2 |

|  30 |     INDEX FULL SCAN                  | JOB_ID_PK          |      1 |      1 |00:00:00.01 |       1 |

|* 31 |    SORT JOIN                         |                    |      1 |      0 |00:00:00.01 |       0 |

|  32 |     VIEW                             | VW_JF_SET$B71A25AA |      1 |      0 |00:00:00.01 |       0 |

|  33 |      UNION-ALL                       |                    |      1 |      0 |00:00:00.01 |       0 |

|* 34 |       FILTER                         |                    |      1 |      0 |00:00:00.01 |       0 |

|  35 |        NESTED LOOPS                  |                    |      0 |      0 |00:00:00.01 |       0 |

|  36 |         NESTED LOOPS                 |                    |      0 |      0 |00:00:00.01 |       0 |

|* 37 |          TABLE ACCESS BY INDEX ROWID | DEPARTMENT         |      0 |      0 |00:00:00.01 |       0 |

|* 38 |           INDEX UNIQUE SCAN          | DEPT_ID_PK         |      0 |      0 |00:00:00.01 |       0 |

|  39 |          TABLE ACCESS BY INDEX ROWID | LOCATION           |      0 |      0 |00:00:00.01 |       0 |

|* 40 |           INDEX UNIQUE SCAN          | LOC_ID_PK          |      0 |      0 |00:00:00.01 |       0 |

|  41 |         TABLE ACCESS BY INDEX ROWID  | EMPLOYEE           |      0 |      0 |00:00:00.01 |       0 |

|* 42 |          INDEX RANGE SCAN            | EMP_DEPT_IX        |      0 |      0 |00:00:00.01 |       0 |

|* 43 |       FILTER                         |                    |      1 |      0 |00:00:00.01 |       0 |

|  44 |        NESTED LOOPS                  |                    |      0 |      0 |00:00:00.01 |       0 |

|  45 |         NESTED LOOPS                 |                    |      0 |      0 |00:00:00.01 |       0 |

|  46 |          NESTED LOOPS                |                    |      0 |      0 |00:00:00.01 |       0 |

|  47 |           TABLE ACCESS BY INDEX ROWID| LOCATION           |      0 |      0 |00:00:00.01 |       0 |

|* 48 |            INDEX UNIQUE SCAN         | LOC_ID_PK          |      0 |      0 |00:00:00.01 |       0 |

|* 49 |           TABLE ACCESS BY INDEX ROWID| DEPARTMENT         |      0 |      0 |00:00:00.01 |       0 |

|* 50 |            INDEX RANGE SCAN          | DEPT_LOCATION_IX   |      0 |      0 |00:00:00.01 |       0 |

|* 51 |          INDEX RANGE SCAN            | EMP_DEPT_IX        |      0 |      0 |00:00:00.01 |       0 |

|  52 |         TABLE ACCESS BY INDEX ROWID  | EMPLOYEE           |      0 |      0 |00:00:00.01 |       0 |

------------------------------------------------------------------------------------------------------------

 

단점 2 : 불필요한 쿼리블럭을 Scan 하며 힌트가 무시된다

Join factorization(1) 이라는 쿼리변환이 발생하여 불필요한 두 블록(Plan의 빨강색 부분) Scan 하였다. : v_delimit = 3 인 경우와 :v_delimit = 4인 경우의 SQL이 실행되어 버린 것이다. 확률은 많지 않겠지만 만약 테이블이 대용량이라면 index full scan과 그에 따른 테이블로의 접근은 성능에 치명적일 것이다. 또한 쿼리변환으로 인해 개발자가 작성한 힌트도 무시되어 sort merge join이 발생되었다.

의도하지 않은 쿼리변환을 경계하라
이렇게 다양한 검색조건에서 Union을 사용하는 경우는 11g R2부터 발생되는 Join factorization의 악영향에 주의해야 한다. 왜냐하면 :v_delimit = 1에 해당하는 SQL만 실행되어야 하지만 Join factorization으로 인해 인라인뷰 외부로 빠진 쿼리블럭은 구분자(:v_delimit )의 값에 영향을 받지 않기 때문이다.

 

그런데 Join factorization을 발생시키지 않을 목적으로 SQL 마다 rownum을 사용하는 사람이 있다. 아래의 SQL이 그것인데 그럴 필요 없다.

 

SELECT /*+ leading(j e d l) use_nl(e d l) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND j.job_id = :v_job                   --> 'SA_MAN' 입력

   AND d.manager_id > 0

   AND ROWNUM > 0

   AND :v_delimit = 1                      --> 1 입력

UNION ALL

SELECT /*+ leading(e d l j) use_nl(d l j) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND e.manager_id = :v_emp

   AND e.hire_date BETWEEN :v_hr_fr AND :v_hr_to

   AND d.manager_id > 0

   AND ROWNUM > 0 

   AND :v_delimit = 2

UNION ALL

중간생략

 

Rownum을 네 번 사용하면 Join factorization이 방지 되기는 하지만 SQL마다 조건절을 추가해야 하므로 막노동에 가깝고 SQL이 길어진다. 가장 쉬운 방법은 쿼리변환을 방지하는 힌트를 사용하는 것이다. 가장 위쪽 SQL의 힌트에 NO_FACTORIZE_JOIN(@SET$1)을 추가하면 된다. SQL마다 힌트를 추가할 필요는 없다. 아래의 예제를 보자.

 

SELECT /*+ leading(j e d l) use_nl(e d l) NO_FACTORIZE_JOIN(@SET$1) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

       j.job_title, d.department_name, l.city,l.country_id

  FROM employee e,

       job j,

       department d,

       location l

 WHERE e.job_id = j.job_id

   AND e.department_id = d.department_id

   AND d.location_id = l.location_id

   AND j.job_id = :v_job                   --> 'SA_MAN' 입력

   AND d.manager_id > 0

   AND :v_delimit = 1                      --> 1 입력

UNION ALL

SELECT /*+ leading(e d l j) use_nl(d l j) */

       e.employee_id, e.email, e.phone_number, e.hire_date, e.salary,

중간생략

 

위처럼 힌트를 한번만 추가하여 쿼리변환을 방지하면 하면 불필요한 블록을 Scan하지 않으며, 개발자가 작성한 힌트를 무시하지 않는다. Oracle11g R2를 사용한다면 직접 실행계획을 확인해보기 바란다.

 

결론 

동적인 검색조건이 많지 않아 Union all을 사용할 때에도 쿼리변환을 조심해야 한다. 원하지 않는 블록을 Scan할 수 있기 때문이다. 이때 쿼리변환을 방지할 목적으로 Rownum을 사용하는 것은 좋지 않다. 왜냐하면 Join factorization을 막을 수는 있지만 또 다른 쿼리변환인 FPD(2) JPPD(3)등의 쿼리변환도 같이 막혀버린다. 따라서 NO_FACTORIZE_JOIN 힌트를 사용하는 것이 적절하다.

오라클의 버전이 올라갈수록 쿼리변환의 기능이 많아진다. 하지만 기능이 많아질수록 어두운 측면도 부각된다. 물론 쿼리변환의 문제점은 자주 발생하지는 않으며 예외적인 경우이다. 하지만 그 예외가 발생된다면 위의 SQL처럼 원하지 않을 때도 쿼리변환이 발생하여 문제가 될 것이다. 지금은 CBQT의 태동기이므로 앞으로 문제가 개선될 것으로 기대한다.  

 

검색조건이 동적으로 바뀔 때는OR로 분기하는 방법을 사용하라. 이 방법을 적절히 사용하면 Union all을 사용하는 방법의 단점인 SQL이 길어지는 것을 피할 수 있다. 또한  Dynamic SQL처럼 힌트와 where절을 동적으로 교체할 필요 없이 명시적으로 작성할 수 있다. Where 절에 OR를 사용하는 것이 항상 나쁜 것은 아니며 분명 뭔가 남다른 장점이 있다. 우리는 그 점을 이해해야 한다.  

 

1: JF(Join factorization)을 간단히 설명하면 Union / Union All 사용시 공통으로 사용하는 테이블을 분리시키는 것이다. 즉 아래와 같이 SQL1 SQL2로 변경되는 기능이다.

SQL1

SELECT /*+ USE_HASH(c s)  */

       s.prod_id, s.cust_id, s.quantity_sold,

       s.amount_sold, c.channel_desc

  FROM sales s, channels c

 WHERE c.channel_id = s.channel_id

   AND c.channel_id = 3

UNION ALL

SELECT /*+ USE_HASH(c s) */

       s.prod_id, s.cust_id, s.quantity_sold,

       s.amount_sold, c.channel_desc

  FROM sales s, channels c

 WHERE c.channel_id = s.channel_id

   AND c.channel_id = 9 ;

 

SQL2

SELECT s.prod_id prod_id, s.cust_id cust_id, s.quantity_sold,

       s.amount_sold, vw_jf_set$0a277f6d.item_2 channel_desc

  FROM (SELECT c.channel_id AS item_1, c.channel_desc AS item_2

          FROM channels c

         WHERE c.channel_id = 3

        UNION ALL

        SELECT c.channel_id AS item_1, c.channel_desc AS item_2

          FROM channels c

         WHERE c.channel_id = 9) vw_jf_set$0a277f6d, --> JF 가 발생하면 인라인뷰vw_jf ~ 가 생성된다.

       sales s                                       --> sales 테이블을 인라인뷰 외부로 분리시킴

 WHERE vw_jf_set$0a277f6d.item_1 = s.channel_id ;

 

2: FPD(Filter Push Down)는 뷰/인라인뷰 외부의 조건이 뷰 내부로 파고드는 기능이다.

3: JPPD(Join Predicate Push Down)는 뷰/인라인뷰 외부의 조인조건이 뷰 내부로 파고드는 기능이다. FPD JPP의 차이는 FPD는 상수조건이 파고드는 것이며 JPPD는 조인절이 파고든다는 점이다.

참고: JF JPPD CBQT(Cost Based Query Transformation)이며 FPDHQT(Heuristic Query Transformation)이다. HQT Rule Based Query Transformation 이라고 부르기도 한다.


 

Posted by extremedb

댓글을 달아 주세요

  1. Ejql 2011.01.17 16:16  댓글주소  수정/삭제  댓글쓰기

    좋은 글 잘 읽었습니다. 감사합니다.