-동적인 조회조건에서 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
,

필자는 가끔 분석함수의 기능에 관해 질문을 받는다. 그때마다 대답을 하지 않고 대신에 질문에 부합하는 Analytic SQL을 보여주고 결과를 분석하게 한다. 바로 답을 주게 되면 개발자가 의존적이 되고 분석함수 각각의 기능도 금방 잊어버리기 때문이다.

 

개발자만 모르는 것이 아니다

얼마 전에 어느 DBA로 부터 요청이 왔다. 자신을 포함한 개발자들이 분석함수를 어려워하니 블로그에 분석함수의 모든 것을 정리한 문서를 올려달라는 것이었다. 물론 오라클 매뉴얼이나 Tomas Kyte Expert One on One 등의 책에 이 함수들의 기능이 있지만 페이지 수가 너무 많고 영문이라는 단점이 있다는 것이었다. 이것은 놀라운 일이다. 개발자뿐 아니라 DBA, 심지어 컨설턴트까지 Analytic Function에 관해 필자에게 질문을 던지곤 한다. Oracle8i 부터 기능이 구현되었으니 기간으로 따지자면 10년 이상 존재했던 함수인데 아직까지......

 

도대체 Analytic Function이 얼마나 어려우면 전문가 까지도 개념이 서지 않는 걸까? 고민 끝에 핵심만 정리한 문서를 올리기로 했다.

 

핵심은 3가지다

분석함수의 기능이 복잡한 것 같지만 사실은 3가지만 알면 90%를 이해한 것이다.

1) Over 절에서 사용하는 Order by의 기능

2) Over 절에서 사용하는 Partition by의 기능

3) Over 절에서 사용하는 Windowing 기능

 

이것이 90% 이다. 대부분의 개발자와 DBA들은 1)번과 2)번에 대해서 많이 알고 있지만 이상하게도 3)번에 대해서 개념이 서질 않는다고 하였다. 따라서 아래의 문서를 다운받아서 공부할 때 3)번을 집중적으로 보기 바란다.

 

그럼 나머지 10%?

나머지는 아래와 같다. 위의 3가지를 안다면 아래의 함수들은 쉽게 이해할 수 있다. 그저 종류가 많을 뿐이다.

 

      RANK, DENSE_RANK, and ROW_NUMBER --> 3가지 함수의 차이점

      FIRST/LAST

      NTILE, WIDTH_BUCKET, CUME_DIST and PERCENT_RANK

      Hypothetical Functions

      FIRST_VALUE/LAST_VALUE , LAG/LEAD

      Reporting Functions/RATIO_TO_REPORT

      Handling null

 

빨강색 부분은 개발자들이 많이 질문하는 것들이다. 참고하기 바란다.

 

Paper라고 다 같은 것은 아니다

매뉴얼이나 관련서적의 문제점은 페이지 수가 많다는 것이다. 예를 들어 분석함수 부분이 60페이지가 넘어간다면 기능을 익히는데 며칠 혹은 몇 주가 걸릴 수 있다. 필자는 페이지 수가 많은 것을 아주 싫어한다. 아래의 문서는 앞쪽의 목차와 중요성, 그리고 뒤쪽의 마무리 부분을 제외하면 9(18 페이지)으로 모든 기능과 개념을 설명하였다. 아마 한 두 시간 이내에 다 볼 수 있을 것이다.

 

invalid-file

테이블 생성 파일

invalid-file

Mastering Oracle Analytic Function ppt 파일



PS

분석함수를 문법이라고 치부해 버리는 사람들이 있다. 그렇지 않다. 많은 경우에 분석함수를 쓰는 것이 SQL 튜닝이 된다. 오죽하면 옵티마이져가 평범한 SQL을 분석함수를 사용하는 것으로 바꾸겠는가?
이제부터 필자에게 분석함수를 질문하는 개발자가 없기를 바란다. ^^


Posted by extremedb
,
오라클의 Regular Expressions을 정의 하면 아래와 같다
  • 유닉스의 정규식과 같음.
  • 강력한 Text 분석도구로서 Like 의 한계를 극복함.
  • Pattern-Matching-Rule의 정의가 자유로움.
  • 다양한 메타문자 제공.
Regular Expressions의 중요성은 아래와 같은 작업시 매우 증대된다
When
  • ETL/전환/이행.
  • Data Mining.
  • Data Cleansing.
  • 데이터 검증.
Regular Expressions의 기능은 다음과 같다
What
  • Text에서 특정 중복 단어의 확인
  • 특별한 상태에서 공백의 제거
  • 특정 문자의 파싱(parsing)
  • Text 에서 전화 번호, 우편 번호, 이메일 주소, 주민등록번호, IP 주소, 파일 이름, 경로 이름 등을 검증 및 추출이 가능
  • HTML 태그, 숫자, 날짜, 기타 특정 텍스트 데이터와 일치하는 패턴을 확인하고 다른 패턴으로 대체하는 것이 가능
  • Constraints 로 사용가능
이렇게 중요한 정규식(Regular Expressions)을 잘 모르거나 익숙치 못한 사람들을 위하여 개인적으로 정리한 파일을 올리니 이번기회에 반드시 정복하길 바란다.  의외로 고급 개발자들 중에 Regular Expressions의 사용을 꺼려하는 사람들이 많이 있는것 같다. 이 기능을 써야하는 경우임에도 쓰지 않는다면 코드는 길어질 것이며 유지보수 또한 힘들어 질것이다.
 
첨부파일에 정리된 내용은 다음과 같다 
  • 정규식 기본 Syntax.
  • 함수 사용법.
  • 정규식 고급 Syntax.
  • 11g New Features

주의 사항 :

  • 첨부된 파일에는 Oracle11g R1 까지의 내용을 정리한 것임.
  • 배포시에는 반드시 출처를 밝힐것.
  • Upload 시 200K 용량제한 때문에 파일을 2개로 분할 압축 하였으므로 모두 다운 받은후 압축을 풀면 됨.

invalid-file

오라클 Regular Expresssions 완전정복1





invalid-file

오라클 Regular Expresssions 완전정복2




 
사용자 삽입 이미지



Posted by extremedb
,

pdf 첨부파일에 일부 오류가 있어 수정해서 다시올립니다. 또한 Rollup 의 개념을 추가 하였습니다.
댓글을 참조 바랍니다. (2009.07.17)


테스트의 이유
SQL이 절차적 언어와 비교할때 가장 두드러 지는 특징은 두가지 이다.
첫번째는 집합처리가 된다는 점이며 두번째로는 처리순서및 로직이 필요 없다는 것이다.
오늘은 첫번째 특징인 집합처리에 대해서 간단한 테스트를 진행하려 한다.
이 테스트를 통과한 사람은 집합개념이 확실한 사람이라고 보면 된다.
필자가 이러한 테스트를 하는 이유는 실무에서 Group By Extension(Rollup, Cube, Grouping Sets) 를 잘사용할경우 Union 등을 없애고 같은 테이블을 반복해서 Scan 하는 비효율을 줄일수 있기 때문이다. 이렇게 되려면 최소한 Group By Extension들의 개념을 확실히 알필요가 있다.

먼저 문법을 알아야 한다.
먼저 이 테스트를 진행하기전에 RollUp, Cube, Grouping Sets 를 모른다면 테스트를 진행할수 없으므로 문법책을 먼저 보고오기 바란다. 위의 3가지 문법을 모두 다 안다고 보고 테스트를 진행할것이다.

먼저 답을 내는 방법을 알아보자.
아래와 같이 문제에 답을 하면 된다.

문제 : 아래의 SQL 을 논리적으로 풀어서 나타내시오.

SELECT A, B, SUM(C)
  FROM T1
GROUP BY ROLLUP(A, B)



답변 :

SELECT A, B, SUM(C)
  FROM T1
GROUP BY A, B
UNION ALL
SELECT A, NULL, SUM(C)
  FROM T1
GROUP BY A
UNION ALL
SELECT NULL, NULL, SUM(C)
 FROM T1
GROUP BY NULL



위의 답은 문제의 SQL 을 논리적으로 풀어서 재작성 한것이다. 바로 Rollup 의 개념을 물어본 것이다.
이런식으로 답을 하면 된다. 간단하지 않은가?

이제 시작해보자.
문제는 4가지이며 모두 논리적으로 풀어서 작성하면 된다. 모든 문제는 3줄짜리 SQL 이다.
제약조건은 한문제를 푸는데 3분을 넘기면 안된다.

문제1 :
SELECT A, B, C, SUM(D)
  FROM T1
GROUP BY C, GROUPING SETS(A, B)

문제2 :
SELECT A, B, C, SUM(D)
  FROM T1
GROUP BY CUBE(  (A, B), C )

문제3 :
SELECT A, B, C, SUM(D)
  FROM T1
GROUP BY GROUPING SETS(A,B), GROUPING SETS(C,D)

문제4:
SELECT A, B, C, SUM(D)
  FROM T1
GROUP BY C, GROUPING SETS( ROLLUP(A), ROLLUP(B) )



모두 맟추면 집합개념이 확실한 사람...
위의 4가지 문제를 모두 쉽게 푸는 사람은 집합개념이 확실한 사람이라고 보면 된다.
만약 쉽게 떠오르지 않거나 머리가 아프다면 아직 문법을 모르거나 집합개념이 확실히 서지 않은 사람이라고 보면 된다.
문제가 어렵다고 너무걱정 하지말자. 아래의 자료에서 문제풀이를 보면서 개념정립을 하면 되기 때문이다.
답은 아래에 첨부된 파일에 존재한다.
개인적으로 Group By Extension을 정리한 문서이므로 자유로이 배포해도 된다.
하지만 배포 할때는 꼭 출처를 밝혀주기 바란다.
참고로 아래의 그림은 첨부된 파일의 한부분 이며 1번문제의 모범답안 이다.


사용자 삽입 이미지


invalid-file

Group By Extension 개념정리





편집후기 :
"과연 이런것 까지 알아야 하나" 라는 질문이 들어 왔다. 여러분들은 어떻게 생각하는가?
다음과 같은 한마디로 답변을 대신하고 싶다.
"SQL 문법과 집합개념은 SQL 튜닝의 시작점이다."

Posted by extremedb
,

부분범위 처리 혹은 페이징 처리시 탁월한 힘을 발휘하는것이 스칼라 서브쿼리이다. 여러가지 테스트 결과 스칼라 서브쿼리는 부분범위처리시 가장 좋다는 Nested Loop 조인보다 성능상 좀더 유리하다.  과다호출로 몸살을 앟고 있는 사용자 함수를 스칼라 서브쿼리로만 바꾸어도 튜닝이 된다. 또한 View Merging 이나 JPPD(Join Push Predicate Down) 등의 성공여부를 고민할 필요도 없으므로 특히 개발자 들에게 인기가 많은것 또한 사실이다.

모든 기능은 용도에 맞게 사용해야 한다.
  이렇게 많이 활용되고 있는 스칼라 서브쿼리도 한계는 있다. Multi Column, Multi Row 을 RETURN 하는 것은 만들수가 없다는 것이다. 물론 Pipilined Funtion 으로 해결이 가능하지만 모든경우에 이것을 만들기란 여간 힘든것이 아니다. 바로 이럴때 사용가능한것이 Cast + MultiSet 함수를 이용한 스칼라 서브쿼리이다. MultiSet 함수는 Oracle 8.1.7  부터 사용이 가능하다. 필자가 이기능을 소개하는 이유는 실무에서 이런기능을 꼭 써야하는 경우임에도 Pipelined 함수등을 사용하는것을 많이 보아서이다. Pipelined 등의 함수를 사용해야 할때는 자주사용하는 SQL 이 아주 길어서 Network 의 부하가 생길때 혹은 모듈로서 공유를 해야될때 이다.

그럼 한번 만들어보자.
아래의 스크립트는 customers 테이블과 sales 테이블을 이용하여 MultiSet 스칼라 서브쿼리를 구현한것이다.
Cast + MultiSet 스칼라 서브쿼리를 사용하려면 먼저 type 2개를 만들어야 한다.


CREATE OR REPLACE TYPE SALES_CUST_TYPE AS OBJECT
(
 PROD_COUNT NUMBER(5),
 CHANNEL_COUNT NUMBER(2),       --> 스칼라 서브쿼리가 RETURN 할 TYPE 선언
 AMOUNT_TOT NUMBER
)
;
  
CREATE OR REPLACE TYPE ARRAY_SALES_CUST  --> 위에서 선언한 TYPE 을 배열로 선언
AS VARRAY(1) OF SALES_CUST_TYPE ;       --> 고객별로 여러건이 RETURN 될 경우 값을 넉넉히 줄것.
         --> 아래의 예제는 고객당 1건만 RETURN 하므로 VARRAY 에 1을 준것이다.

이렇게 2개의 type 을 선언하면 모든 준비가 끝난다. Pipilined Funtion 의 코딩량에 비하면 사용하기가 훨신 간편함을 알수 있다. 그럼 이제 SQL 을 만들어 보자.

SELECT C.CUST_ID, C.CUST_YEAR_OF_BIRTH
             ,S.PROD_COUNT, S.CHANNEL_COUNT, S.AMOUNT_TOT
  FROM (
               SELECT /*+ FULL(C) */
                            C.CUST_ID, C.CUST_YEAR_OF_BIRTH,
                            CAST( MULTISET ( SELECT count(distinct s.PROD_ID)        AS PROD_COUNT
                                                                       ,count(distinct s.CHANNEL_ID)  AS CHANNEL_COUNT
                                                                       ,sum(s.AMOUNT_SOLD)           AS AMOUNT_TOT
                                                            FROM sh.sales s
                                                           WHERE s.cust_id = c.cust_id
                                                         ) AS  ARRAY_SALES_CUST --> 위에서 선언한 배열 TYPE 이용.
                                     ) AS SALES_CUST --> MultiSet 함수의 Alias 선언
                FROM sh.customers c
            )  C,
            TABLE(SALES_CUST) S                     --> 위에서 선언한 MultiSet 함수의 Alias 를 테이블로 이용함.
 WHERE S.PROD_COUNT  <> 0 ;    --> 스칼라 서브쿼리내의 특정컬럼을 조건절에 이용한 예제임.

SQL 의 결과 :

사용자 삽입 이미지















결과는 성공적이다. 성능면에서도 스칼라서브쿼리와 동일하다. 이것은 마치 스칼라서브쿼리의 장점과 PIPELINED TABLE 함수의 장점을 취한것과 같다. 하지만 대용량 배치 SQL 에서는 Hash 조인이나 Sort Merge 조인보다 성능이 좋지 못하므로 주의 해야한다.

결론 :
Cast + MultiSet 스칼라 서브쿼리는 부분범위 처리시 혹은 페이징 처리시에 탁월한 성능을 발휘하며 스칼라 서브쿼리의 단점인  Multi Column, Multi Row 을 RETURN 할수 있다. 뿐만아니라 View Merging 이나 JPPD (Join Predicate Push Down) 이 불가능할 경우의 훌륭한 해결책이 될수 있다.

P.S :
위의 예제는 Multi Column을 RETURN 하지만 Multi Row 를 RETURN 하지 않는다. 독자 여러분이 위의  Cast + MultiSet 스칼라 서브쿼리를 Multi Column, Multi Row 를 RETURN 하는것으로 바꾸어 보라. 백견이 불여일행 이다.

'Oracle > SQL Pattern' 카테고리의 다른 글

오라클 Regular Expressions 완전정복  (22) 2009.10.07
간단한 집합개념 Test  (7) 2009.07.06
Model 절에 대하여  (10) 2009.05.28
SQL 포기하지마라.  (7) 2009.05.28
Upgrade Select For Update  (3) 2009.05.19
Posted by extremedb
,
SQL 포기하지마라. 라는 글에서 Model 절을 언급한바 있다.
독자중에 좀더 자세히 알고 싶어 하는 분들이 있어서 개인적으로 정리한 자료를 올리게 되었다.
하지만 배포시에는 출처를 꼭 밝혀주기 바란다.
아래파일은 upload 시에 200 KB 한계가 있으므로 3개로 나누어 올리지만
3개를 모두 다운로드 받아 압축을 풀면 하나의 파일로 생성되므로 문제가 없을 것이다.

사용자 삽입 이미지











'Oracle > SQL Pattern' 카테고리의 다른 글

간단한 집합개념 Test  (7) 2009.07.06
스칼라 서브쿼리에서 Multi Column, Multi Row Return 하기.  (7) 2009.06.29
SQL 포기하지마라.  (7) 2009.05.28
Upgrade Select For Update  (3) 2009.05.19
응용력 발휘하기  (2) 2009.03.20
Posted by extremedb
,
SQL 은 방정식을 풀기에 적합한 언어인가?
  우연히 방정식을 푸는 요건이 있었는데 상황은 아래와 같았다.
요건은 "정수 X 를 정수 Y 로 계속 나누되 N 번 나눈 결과와 N-1 번 나눈 결과의 차이가 0.25 보다 작으면
멈추고 그때까지의  나눈값과 LOOP 횟수( N )를 출력하라" 였다. 물론 X 와 Y 는 변수이다.
이해를 쉽게 하기 위하여 왼쪽의 그림을 보자.

사용자 삽입 이미지
왼쪽의 그림을 설명하면  50을 2 로 8번 나누면 0.1953 인데 이것을 7번 나눈값으로 빼면 결과는 0.25 이하이다.

따라서 원하는 출력값은 0.1953 과 8 번(LOOP 횟수) 인 것이다.

이요건을 들은 개발자들은 저마다 자바나 PL/SQL 로 개발 하면 된다고 생각했다.

하지만 이기능을 요구한 사람의 말에 따르면 SELECT 쿼리결과로 나오는 것이 가장 좋다.

하지만 불가능하면 자바나 PL/SQL 등으로 구현해도 된다는 것이었다.

여러분이 만약 이러한 요구사항을 해결해야 한다면?

퀴즈라고 생각하고 머릿속에서 해법을 생각해보라.


포기하지마라
  사실 필자는 이문제가 성능이슈가 아니었고 SQL을 제법 잘사용하는 개발자들과 DBA 가 회의에 참석하였으므로 직접 나서지 않았는데 한마디 말 때문에 흥분하기 시작했다.

개발자1 : 이것은 SQL 로는 안됩니다. Loop 도 돌려야 하고 Loop 내에서 빠져나가는 로직도 추가해야 되기 때문입니다.
필자 : SQL 로 하는것을 포기 하지마십시오.
개발자1 : 네?
개발자2 : 5분이면 PL/SQL 함수로 구현이 가능할거 같습니다.
             그함수를 SQL 에서 call 하면 됩니다. 괜히 고생하지 마시죠.
필자 : 5분이라구요?....그렇다면 두분이서 작성한 코드를 10분뒤에 저에게 보여주세요.

  이렇게 해서 회의가 잠시중단이 되었다.
사실 필자는 자주 흥분하는편이 아닌데 언제부터인지 SQL 로는 불가능하며 비효율적이라는 소리를 들을때면 이상하게 자극이 된다. 어쩔수 없는 엔지니어 출신의 비애인것 같다.

It's Time to overhaul!
시간이 되어 개발자들이 작성한 코드를 검토해 보았다.

CREATE OR REPLACE function sf_calc(v_value number, v_division number)
return varchar2 as
   v_iteration pls_integer := 0;
   v_result number;
   v_result_before number;
BEGIN
   v_result := v_value;
   FOR v_counter IN 1..999999999 LOOP
  
      v_result := v_result / v_division;
      v_iteration := v_counter;
      EXIT WHEN v_iteration > 1 and   v_result_before  - v_result  <= 0.25;
      v_result_before = v_result ;
   END LOOP;
   return '결과 : ' ||to_char(v_result) || ' , '||
            'Loop 횟수 : '|| to_char(v_iteration);
END;

위의 함수는 약간 손볼곳이 있지만 그래도 결과에 충실하다. 위의 함수를 SQL 에서 사용하면 아래와 같다.

select sf_calc(50, 2) from dual;

결과 : .1953125  , Loop 횟수 : 8

  함수의 길이도 길지않고 이정도면 괜찮다고 말할수 있다. 하지만 또다른 비효율이 숨어 있었다.
결과를 comma (,) 기준으로 잘라서 2 컬럼으로 보여줘야 하는 문제가 여전히 남는다.
그렇다면 필자가 작성한 아래의 SQL 로 바꾸는 것은 어떤가?

select S as "결과", N + 1 as "Loop 횟수" From DUAL
model
dimension by (1 X)
measures ( :v_value S, 0 N)                                                    --> :v_value 에 50 대입
rules iterate (999999999) Until (PREVIOUS(S[1])-S[1]<=0.25)
( S[1] = S[1]/ :v_division,                                                      --> ::v_division 에 2 대입
  N[1] = ITERATION_NUMBER );  


결과              Loop 횟수
----------- -----------
   0.1953125                 8

Model 절 문법이 어렵다고 들었다.
  결과가 2컬럼으로 분리되어 정확히 나왔다. 코딩의 시간은 단 1분 이며 코드의 길이는 어떤가? 이정도면 충분히 짧다.
개발자들이 유지보수가 어렵다는 문제를 들고 나왔다. 1분만 투자하면 문법을 익힐수 있음에도 문법이 어렵다는 것은 미신이다.
현재 오라클이 11g 까지 나왔지만 각각의 문법들은 몇분만 투자하면 다 알수 있는 정도이다.
물론 성능면에서 신기능의 실행계획을 분석하는것은 다른 이야기 이다.
간단히 문법을 설명하면 아래와 같다.

1. rules iterate(999999999) 라는 것은 괄호 안의 작업(S[1] = ... 이후생략)을 999999999 번 반복해서 실행한다의 의미이다.
2. Until 이라는것은 Loop 를 빠져나오기 위한 조건을 지정 하는것이다.
3. PREVIOUS(N) 라는 것은 N-1 번째의 값을 의미한다. 따라서 PREVIOUS(S[1])-S[1]<=0.25 의 의미는
   "N -1 번째 :v_value 값에서 N 번째 :v_value 값을 뺀 차이가 0.25 이하일때" 인 것이다.
4. S[1] 의 값은  최초에는 :v_value 와 같지만  S[1]/ :v_division 로 계산된 값을 Loop 시마다 대입하므로 값이 계속 바뀐다.
5. N[1] 의 값은 Loop 시마다 1 증가한다. 예약어 ITERATION_NUMBER 의 기능때문이다.
    하지만 최초의 값이 0부터 시작되므로 select 시에 1을 더해서 보여줘야 한다.
6. measures ( :v_value S, 0 N) 라는 표현은 단순히 S[1] 과 N[1] 을 사용하기 위하여 단순히 alias 를 정의한것에 불과하다.


주의사항 :
  여기서 dimension by 는 아무런 뜻이 없다. 하지만 숫자 1 대신에 다른값을 사용할 경우 SQL 이 종료되지 않으므로 주의해야 한다.

Model 절로 무엇을 할수 있나?
 Model 절과 관련된 미신중의 하나는 Time series, 수요예측, 판매예측, 기상예측 등등 주로 이전실적에 여러가지 factor 를 추가하여 미래를 예측하는데 사용하거나 SQL 에서 축이동(Pivot/Unpivot) 등의 용도로만 사용된다고 알고 있다는 것이다. 하지만 위의 SQL 은 이것들중 어디에도 포함되지 않는다. 다시말하면 복잡한 연립방정식을 푸는데 제일좋은 것이 Model 절이다.  
       
결론:
  MODEL 절은 오래전(6년전)에 나온 SQL 문법이지만 아직 늦지 않았다. 필자가 확인한 결과 아직도 모르는 사람들이 많이 있으므로 지금 익힌다면 선구자가 될것이다. SQL 은 여러분이 생각하는것보다 훨씬더 강력하고 효율적임을 알수있다. 위의 SQL과 같이 간단하지만 강력한 기능들이 많이 준비되어 있다. 예를들면 X-Query, Regular Expression, Pivot/UnPivot 등이 그것이다. 무기의 종류(SQL)를 여러개 준비한 사람과 기관총 하나만 가지고 있는 사람의 차이는 어마어마 하다. 준비된 사람은 요구조건에 따라서 단검을 사용할것인지 탱크를 사용할것인지 그것도 아니라면 F16 전투기를 사용할것인지 상황에 따라서 맞는 무기를 선택할수 있다.  SQL 공부 여기서 포기할것인가?

Posted by extremedb
,

어느 회식 자리에서
  오늘(5월 18일) 어느 사이트의 회식자리에서 프로젝트의 PM 옆자리에 앉게 되었다.
나중에 알고보니 그 PM 은 술자리 기피대상 1호 였는데 이유는 "공장"(?) 이야기를 너무 많이 한다는 것이었다.
필자는 운이 좋게도(?) 자리가 그사람 옆이라 공장 이야기를 많이 들을수 있었다.

PM : 요즘 배치프로그램을 보면 영화 매트릭스의 스미스(악당 요원)가 생각이 나네요.

필자 : 왜 그렇게 생각하세요?

PM:  해도 해도 너무해요. 예전에는 년배치, 분기배치, 월배치, 일배치 가 배치의 전부 였지요.
        하지만 90년대 후반부터 하루에 3~4번 도는 배치가 생기기 시작했습니다.
        이제는 시간당 한번 실행되는 배치도 성에 안차나 봅니다.
        심지어 최근에는 Near Real Time 배치(1~10분 주기)가 많아져 온라인 프로그램과 연관된 lock 문제가
        많이 발생하고 있습니다.
        Near Real Time 배치는 처리건수가 많지는 않지만 select for update를 사용하는 경우가 많은데
        바로 이것이 문제 입니다.

그게 스미스랑 무슨 상관이지?
  그 PM 은 우리가 물어보기도 전에 배치프로그램과 스미스 요원과의 연관성을 설명 하였다.
그의 주장은 영화 메트릭스 1~ 3편을 보면 스미스가 지속적으로 upgrade 되는 것을 볼수 있는데 그것은 아래의 3가지로 설명이 가능하다는 것이다..
1편에서의 돌연변이 --> 매트릭스의 아버지격인 컴퓨터 아키텍쳐의 통제에서 벗어난 행동을 한다.
                                 이것은 하루에 N 번 도는 배치에 해당한다.

2편에서의 복제기능 --> 다른사람을 스미스로 복제 해버린다.
                                 이것은 시간당 한번 실행되는 배치에 해당한다.

3편에서의 SuperMan --> 자유자재로 날아다니는 것은 물론이고 드디어 매트릭스를 벗어나 인간세계(Zion) 에 까지 영향을 미친다. 이것은 Near Real Time 배치에 해당하며 드디어 온라인 프로그램과의 충돌을 의미 한다는 것이다.

  우리는 그 PM의 이야기를 듯고 한동안 웃음바다가 되었다. 아주 멋진 비유이다.
필자가 생각해보아도 요즘은 Near Real Time 배치가 대세가 되었다.
사람의 욕심은 끝이 없어서 각종 하드웨어와 소프트웨어의 성능이 좋아질수록 거의 실시간으로 배치가 실행되고 배치의 결과물도 아주 짧은 시간에 나오기를 원하는 것이다. 그러다 보면 온라인 프로그램과의 충돌이 자주 일어날수 밖에 없다.

최대의 실수
  아래의 짧은 이야기가 오늘 최대실수가 될줄이야....
필자 : "하지만 스미스가 있으면 The One(Neo) 도 있습니다."
PM : "네? 무슨 말인지 설명해 주시죠."
허걱 술자리에서.. 칠판이나 SQL PLUS 등도 없는데 설명을 해야만 하다니....
드디어 필자가 그 PM 의 마법에 걸려들고야 말았다. 

아래는 예제는 필자의 이야기를 증명한것이다. 물론 회식 자리에서는 전부 말로 설명 하였다.

정말 배치프로그램은 무한정 기다려야 하나?
  배치프로그램에서 최초의 SQL은 Select For Update 문이고 이것은 온라인 프로그램의 Update 문과 같은 테이블을 사용한다고 가정하자. 또한 온라인 UPDATE 의 WHERE 절이 배치 SQL 의 WHERE 절에 속하거나 걸쳐 있다면 동시에 온라인과 배치가 수행될수 없는 구조이다. 
혹자는 SELECT FOR UPDATE 에 WAIT N 이나 NOWAIT 옵션을 주면 되지 않냐고 반문할지 모른다. 물론 그렇게 하면 무작정 기다리지는 않는다. 하지만 온라인 프로그램의 UPDATE 문이 3 초안에 COMMIT이나 ROLLBACK 되지 않으면 Select for Update wait 문에서 아래의 예제처럼 에러가 발생한다.

SESSION 1 :
SQL> UPDATE EMP
  2    SET SAL = SAL * 1.1
  3  WHERE EMPNO = 7566; --> 1건 UPDATE, 7566 번은 부서번호가 20번임에 유의하자.

1 row updated
...SESSION 1 에서  다른 작업(다른 SQL)이 진행중이며 아직 COMMIT 이 안된 상태임 ...

SESSION 2 : --> SESSION 1 에서 COMMIT 안된 상태에서 수행됨.
SQL> SELECT * FROM EMP
  2  WHERE DEPTNO = 20   --> 부서번호 20번 전체 SELECT
  3  FOR UPDATE WAIT 3; --> 3초간 기다린후에 에러가 발생됨.

ORA-30006: 리소스 사용 중. WAIT 시간 초과로 획득이 만료됨.


위의 에러는 해당 사이트에서 자주 발생 된다고 한다.

해결책 : The One IS SKIP LOCKED Option
  이렇게 에러가 발생하게 되면 배치프로그램의 EXCEPTION 이 매우 지저분 하게 된다.
EXCEPTION 에서 특별 처리를 하거나 아니면 다음주기(1~10분)을 기다렸다가 다시 시도 해야 되는데 이때에도 온라인 프로그램이 UPDATE 하고 있지 않기를 바라는수 밖에 없다.
너무나 억울하지 않은가?
다른세션에서 EMP 테이블을 1건 UPDATE 했을뿐인데 부서번호 20번에 해당하는 전체건에 대하여
SELECT FOR UPDATE 를 할수 없는것이다.

그렇다면 이문제를 어떻게 해결할수 있겠는가?
ORACLE 11g 를 사용한다면 SKIP LOCKED 옵션을 사용할수 있다.

SESSION 2 :
SQL> SELECT * FROM EMP
  2  WHERE DEPTNO = 20
  3  FOR UPDATE SKIP LOCKED;

--> What a option! U can select for update entire row except the row which has been aready locked!

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7788 SCOTT      ANALYST    7566 1987-04-19    3000.00               20
 7876 ADAMS      CLERK      7788 1987-05-23    1100.00               20
 7902 FORD       ANALYST    7566 1981-12-03    3000.00               20

     
  환상적이지 않은가?
LOCK 이걸린 7566 을 제외한 20번 부서 전체건에 대하여 성공적으로 LOCK 을 발생시켰다.
이기능을 2003 년에 발견하였지만 undocumnted 기능으로 분류되어 있었으므로 그때는 명시적으로 이기능을 사용하지 않았다. 하지만 여러분이 10g 에서 AQ(Advanced Queuing) 기능을 사용하고 있고 dequeue 옵션으로 no_wait 을 사용했다면 여러분의 의지와는 상관없이 오라클은 SKIP LOCKED 옵션을 사용한것이다.

결론 :
11g 에서 드디어 SKIP LOCKED 이 11g 매뉴얼에 모습을 드러내었다.
이것은 대단한 Upgrade 이다. 특히 아래와 같이 천건 혹은 만건씩 처리되는 상주(Near Real Time)배치 SQL 에서 말이다.

SELECT *
  FROM TAB1
 WHERE 배치처리완료 = 'N'
   AND ROWNUM <= 1000
FOR UPDATE SKIP LOCKED;

온라인 프로그램에서 1건이 UPDATE 되었다고 해도 나머지 999 건은 처리가 될것이므로 ...

주의사항 : 9i 및 10g 에서 명시적으로 SKIP LOCKED 옵션을 사용하면 안된다.
               기능을 숨겨 놓은데는 다 이유가 있다. (NEO 가 1편부터 날라다닌것은 아니다.)
               술자리에서는 공장 이야기를 하지말자. NEO 가 있다는 것을 증명하기가 너무 힘들다...^^

'Oracle > SQL Pattern' 카테고리의 다른 글

Model 절에 대하여  (10) 2009.05.28
SQL 포기하지마라.  (7) 2009.05.28
응용력 발휘하기  (2) 2009.03.20
Hypothetical Functions 은 어떨때 써야하나?  (3) 2008.10.25
Hierarchical Queries  (3) 2008.06.23
Posted by extremedb
,

SQL 작성시 응용력을 필요로 하는경우가 많이 있다.
아래의 예제도 그런상황중의 하나이다.
일별매출실적 테이블에서 sysdate 기준으로 현재월의 첫번째 월요일부터 현재월의 마지막 금요일 까지의 실적을 구해야 되는 요구사항이 있다.
문제의 핵심은 해당월의  첫번째 월요일과 마지막 금요일을 구하는 것이다.
2009년 3월 기준으로 생각하면 첫번째 월요일은 2009년 3월 2일 마지막 금요일은 2009 년 3월 27일 이다. 

아래의 SQL 을 보자.

select  substr(TO_CHAR(기준일자, 'YYYYMMDD'), 1,6) as 년월 , sum(매출실적) as 월실적
  from 일별매출실적
 where 기준일자 between :현재월의 첫번째 월요일 and :현재월의 마지막 금요일
 group by substr(TO_CHAR(기준일자, 'YYYYMMDD'), 1,6)
;

위 SQL 은 변수를 2개 사용하고 있다.
:현재월의 첫번째 월요일:현재월의 마지막 금요일을 구하는 로직은 개발자가 자바 프로그램에서 작성할 예정이라고 한다.
그렇게 작성 할수는 있지만 로직이 길어지고  유지보수도 어려워지므로 Oracle DBMS 기능을 이용하여 쉽게 구하는 방법이 있는지 필자에게 물어보러 온 것이다.
아래의 SQL 을 보자

select next_day(trunc(sysdate,'mm')-1,'월요일') first_monday,
       trunc(next_day(last_day(sysdate)-7,'금요일')) last_monday
  from dual
;


FIRST_MONDAY LAST_MONDAY
------------ -----------
2009-03-02   2009-03-27

last_day, next_day 함수와 trunc 함수를 적절히 활용하면 문제를 쉽게 풀수 있다.
간단히 설명 하면 첫번째 월요일은 trunc(sysdate,'mm')-1 로 전달의 마지막 일자를 구하고 next_day 함수를 사용하여 다음 월요일을 구하면 된다.
마지막 금요일은 last_day 함수를 사용하여 해당월의 마지막 일자에서 7일을 뺀 결과를 next_day 함수를 사용하여 다음 금요일을 구하면 되는 것이다.

이렇게 하여 아래와 같은 simple 한 SQL 이 완성되었다.

select substr(TO_CHAR(기준일자, 'YYYYMMDD'), 1,6) as 년월 , sum(매출실적) as 월실적
  from 일별매출실적
 where 기준일자 between next_day(trunc(sysdate,'mm')-1,'월요일')
   and trunc(next_day(last_day(sysdate)-7,'금요일'))
 group by substr(TO_CHAR(기준일자, 'YYYYMMDD'), 1,6)  
;

결론:
문제의 핵심을 분석하고 DBMS 의 기능을 최대한 활용하여 상황을 빨리 해결 하는 능력이 필요하다.
문제해결 능력은 분석력 + 지식 + 응용력 이다.  
해당 SQL 의 경우 분석이나 지식보다는 응용력을 잘 발휘 해야되는 상황인 것 이다.

'Oracle > SQL Pattern' 카테고리의 다른 글

SQL 포기하지마라.  (7) 2009.05.28
Upgrade Select For Update  (3) 2009.05.19
Hypothetical Functions 은 어떨때 써야하나?  (3) 2008.10.25
Hierarchical Queries  (3) 2008.06.23
가로를 세로로 바꾸기  (0) 2008.05.27
Posted by extremedb
,
필자는 며칠전 고객에게 아래와 같은 문의 사항을 받았다.

고객 :"우리지점은 9월 달에 매출액이 112 억 에 전체 지점중에서 9등을 하였습니다.
          만약 9월 달에 우리지점이 매출 130억을 달성 했다면 전체지점에서 몇등인지
          볼수 있는 기능을 오라클 에서 제공 합니까?"

필자 : "제공합니다. "
         "현재는 어떻게 개발하시고 계시나요?"

고객 : "현재는 개발자가 월별 매출액 테이블에 한건(130억)을 insert 하고 COMMIT 되지 않은 상태에서
          RANK 함수를 써서 순위를 구하고 나머지 지점들의 순위는 화면단 로직으로 다걸러서 버리고
          해당지점의 순위가 화면에 출력되면 ROLLBACK 하고 있습니다."

필자 : "켁"(마시던 커피가 넘어가다가 목에서 막히는 소리임.^^)  

필자는 고객에게 Hypothetical Functions(가정에 근거한 함수) 를 추천 하였다.
이상하게도 개발자들 뿐아니라 DBA들 또한 Ranking Family (Rank/Dense Rank/Percent rank 등등) 함수는
즐겨사용함 에도 불구하고 가정함수(Hypothetical Functions) 는 쓰지 않는다.

아래는 2001년에 고객이 매출한 금액별로 순위를 정한 SQL 문이다.

SELECT cust_nbr, SUM(tot_sales) cust_sales,
  RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) rank,
  DENSE_RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) dense_rank,
  CUME_DIST( ) OVER (ORDER BY SUM(tot_sales) DESC) cume_dist,
  PERCENT_RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) percent_rank
FROM orders
WHERE year = 2001
GROUP BY cust_nbr
ORDER BY rank;

  CUST_NBR CUST_SALES       RANK DENSE_RANK  CUME_DIST PERCENT_RANK
---------- ---------- ---------- ---------- ---------- ------------
.....................................................      중간생략
         1    1151162         23         21 .766666667    .75862069
         8    1141638         24         22         .8   .793103448
        16    1068467         25         23 .833333333   .827586207
        22    1036146         26         24 .866666667   .862068966
       
21    1020541         27         25         .9   .896551724
       
28     986964         28         26 .933333333   .931034483
         7     971585         29         27 .966666667   .965517241
        29     903383         30         28          1            1

분석함수의 전형적인 결과이다.
위쿼리의 결과를 보고 오라클에게 이렇게 질문할수 있다.
만약 내가 백만원(1000000 원)의 매출(CUST_SALES)을 올렸다면 나의 순위(RANK)는 몇등인가?
위결과에서 밑줄친 부분을 보고 생각을 해보면 나의 순위는 28위임을 알수 있다.
즉 백만원이 28등 이라는것만 알면되는 요구사항이다. --> 결과가 한줄로 나와야 한다.
하지만 이것을 SQL 로 구현하기란 만만치 않다는걸 알수 있다.
이럴때 아래처럼 Hypothetical Functions을 사용한다면 손쉽게 불필요한 고객들의 정보를 제외하면서
보고싶은 나의 순위만 화면에 출력된다.

SELECT
  RANK(1000000) WITHIN GROUP (ORDER BY SUM(tot_sales) DESC) hyp_rank,
  DENSE_RANK(1000000) WITHIN GROUP (ORDER BY SUM(tot_sales) DESC) hyp_dense_rank,
  CUME_DIST(1000000) WITHIN GROUP (ORDER BY SUM(tot_sales) DESC) hyp_cume_dist,
  PERCENT_RANK(1000000) WITHIN GROUP (ORDER BY SUM(tot_sales) DESC) hyp_pcent_rank
FROM orders
WHERE year = 2001
GROUP BY cust_nbr;


  HYP_RANK HYP_DENSE_RANK HYP_CUME_DIST   HYP_PCENT_RANK
---------- -------------- ------------- ----------------
        28             26    .903225806               .9

결과가 너무나 환상적이지 않은가?
원하는 결과 한줄만 나오는 것은 물론이고 순위는 28위(Dense Rank 로는 26위) 라고 친절하게 나온다.

결론:
위의 상황과 같이 순위나 백분율 등을 예측하고 싶을때 Hypothetical Functions 을 쓰지 않는다면
고생을 하는것은 물론이고 성능이 좋을리도 없음을 기억하자.

편집후기 : 참고로 필자는 Hypothetical Function 이라는 말을 싫어한다.
                What IF Function 이라고 기억해야 머리에 오래남을 것이다.

'Oracle > SQL Pattern' 카테고리의 다른 글

Upgrade Select For Update  (3) 2009.05.19
응용력 발휘하기  (2) 2009.03.20
Hierarchical Queries  (3) 2008.06.23
가로를 세로로 바꾸기  (0) 2008.05.27
Jonathan Lewis 의 퀴즈 접근법  (0) 2008.05.15
Posted by extremedb
,
Hierarchical Queries 는 Recursive Query 혹은 Tree SQL 이라고 부른다.
BOM, 조직도 등을 개발할때 Hierarchical Queries 는 필수이다.
하지만  이상하게도 Hierarchical Queries는 개발자들의 기피대상 1위이다.
Self 조인을 사용한다든지 아니면 Recursive Function 등을 사용하는 등등 어떻게든  Hierarchical Query 를
피해갈려고 애쓴다.
9I 및 10g 에서 Connect By Query 는 이미 피해갈수 없을 정도로 막강해졌다.
즉 예전과는 달리 Connect By 가 워낙 막강해져서 이젠 이기능을 모방하려면 엄청난 노가다(?) 를 해야만 할것이다.
여기서는 기본적인 Connect By 문법은 이야기 하지않고  9i , 10g 에서 추가된 문법과 수정된 Connect By 관련
Plan 에 대해서만 이야기 할 것이다.

9i 신기능

Hierarchical Queries 관련하여 대표적인 미신이 2가지 있다.
1) 조인관련 신기능
첫번째는 조인을 할수가 없다는것이고 두번째는 조인이 포함된 뷰를 사용할수없다는 것이다.
이것은 8i 까지만 사실이고 9i 부터는 자유롭게 from 절을 이용하여 조인할수 있다.

SELECT  LEVEL,
              LPAD('  ',2*(LEVEL - 1)) || lname as ename,
              A.emp_id,
              A.manager_emp_id,
              B.name as deptname
FROM employee A, department B
WHERE A.dept_id = b.dept_id
START WITH A.emp_id = 7839
CONNECT BY PRIOR A.emp_id = A.manager_emp_id;

--결과
사용자 삽입 이미지


















2) 향상된 order by
Hierarchical Queries 의 문제는 order by 를 하면 계층구조가 깨진다는 것이다.
따라서 ORDER BY 를 하려면 인덱스 설계를 잘하는수 밖에 없었다.
하지만 9i 부터 ORDER SIBLINGS BY 절이 나오면서 그런 문제는 사라졌다.
다시말하면 계층구조가 다치지 않는 범위내에서 SORT 하는 기능이 추가 되었다.

SELECT LEVEL, LPAD('  ',2*(LEVEL - 1)) || lname ename,
       emp_id, manager_emp_id
FROM employee
START WITH manager_emp_id IS NULL
CONNECT BY PRIOR emp_id = manager_emp_id
ORDER SIBLINGS BY lname;

--결과

사용자 삽입 이미지


















3) Path 나타내기
계층구조를 한줄로 나타내는 기능이 9i 에서 추가 되었다.

SELECT SYS_CONNECT_BY_PATH(lname, '#')
FROM employee
START WITH manager_emp_id IS NULL
CONNECT BY PRIOR emp_id = manager_emp_id;

--결과

사용자 삽입 이미지


















10g 신기능


1) Root 노드와 Leaf 노드를 구분하는 기능이 추가되었다.
 CONNECT_BY_ROOT 는 반드시 컬럼을 지정하여야 하고
CONNECT_BY_ISLEAF 는 여부(1 이면 Y 라는 뜻이다)이다.

SELECT lname "Employee",
       CONNECT_BY_ROOT lname "Top Manager", 
       CONNECT_BY_ISLEAF
FROM employee
START WITH manager_emp_id = 7839
CONNECT BY PRIOR emp_id = manager_emp_id;

--결과
사용자 삽입 이미지


















2) CYCLE(무한루프) 방지 및 CYCLE 확인기능
먼저 무한루프를 만들기 위하여 데이터를 수정한다.

UPDATE employee
SET manager_emp_id = 7654 --> ROOT 노드(KING)의 매니져를 둠으로써 무한루프 발생
WHERE manager_emp_id IS NULL;

COMMIT;

SELECT LEVEL, LPAD('  ',2*(LEVEL - 1)) || lname "EMPLOYEE",
       emp_id, manager_emp_id
FROM employee
START WITH emp_id = 7839
CONNECT BY PRIOR emp_id = manager_emp_id;

ERROR:
ORA-01436: CONNECT BY loop in user data --> 무한루프가 발생한다.

그럼 해결책은?
아래처럼 하면된다.
아래는 반복을 제거하고 결과를 리턴한다.
 
SELECT LEVEL, LPAD('  ',2*(LEVEL - 1)) || lname "EMPLOYEE",
      
CONNECT_BY_ISCYCLE,               --> CYCLE 에러의 발생지점을 알려준다.
       emp_id, manager_emp_id
FROM employee
START WITH emp_id = 7839
CONNECT BY NOCYCLE PRIOR emp_id = manager_emp_id;    --> 무한루프를 방지한다.

--결과
사용자 삽입 이미지


















3) PLAN 상의 변경
추가적으로 PLAN 에서의 변경사항을 살펴보면
10.2.0.3 버젼까지 튜너들을 괴롭히던 원인을 알수없는 FULL SCAN 이 사라졌다.
--10.2.0.3 PLAN
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=2 Bytes=22)
   1    0   CONNECT BY (WITH FILTERING)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE)
   3    2       INDEX (UNIQUE SCAN) OF 'EMPLOYEE_PK' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=3)
   4    1     NESTED LOOPS
   5    4       BUFFER (SORT)
   6    5         CONNECT BY PUMP
   7    4       TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (Cost=1 Card=2 Bytes=22)
   8    7         INDEX (RANGE SCAN) OF 'IDX_EMPLOYEE_MGR' (INDEX) (Cost=1 Card=2)
   9    1    
TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=1 Card=2 Bytes=22)



위의 FULL SCAN(굵은글씨) 은 있을수 없는 정보(BUG)였는데 이는 10.2.0.4 에서  PLAN 상에서 사라졌다.
--10.2.0.4 PLAN

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=2 Bytes=22)
   1    0   CONNECT BY (WITH FILTERING)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE)
   3    2       INDEX (UNIQUE SCAN) OF 'EMPLOYEE_PK' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=3)
   4    1     NESTED LOOPS
   5    4       BUFFER (SORT)
   6    5         CONNECT BY PUMP
   7    4       TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (Cost=1 Card=2 Bytes=22)
   8    7         INDEX (RANGE SCAN) OF 'IDX_EMPLOYEE_MGR' (INDEX) (Cost=1 Card=2)

4) 파라미터의 변화
_optimizer_connect_by_cost_based 파라미터가 추가 되었다.
하지만 이파라미터는 버그가 보고되고 있어 사이트에서 수동으로 false 로 바꾸어 놓는 경우가 많다.
false 로 바꾸면 connect by 쿼리에서 조인하는 경우(  9i 의1) 조인관련 신기능 참조 ) FTS 로 PLAN 이 바뀌는 경향이 있으므로 주의 해야한다.
아례 예제를 보자.

SELECT   LEVEL,
              LPAD('  ',2*(LEVEL - 1)) || lname as ename,
              A.emp_id,
              A.manager_emp_id,
              B.name as deptname
FROM employee A, department B
WHERE A.dept_id = b.dept_id
START WITH A.emp_id = 7839
CONNECT BY  PRIOR A.emp_id = A.manager_emp_id;

Execution Plan : --> alter session set "_optimizer_connect_by_cost_based" = true;  적용한 plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=2 Bytes=50)
   1    0   CONNECT BY (WITH FILTERING)
   2    1     NESTED LOOPS (Cost=2 Card=1 Bytes=34)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (Cost=1 Card=1 Bytes=22)
   4    3         INDEX (UNIQUE SCAN) OF 'EMPLOYEE_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=4 Bytes=48)
   6    5         INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
   7    1     NESTED LOOPS (Cost=2 Card=2 Bytes=50)
   8    7       NESTED LOOPS
   9    8         BUFFER (SORT)
  10    9           CONNECT BY PUMP
  11    8         TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (Cost=1 Card=2 Bytes=26)
  12   11           INDEX (RANGE SCAN) OF 'IDX_EMPLOYEE_MGR' (INDEX) (Cost=1 Card=2)
  13    7       TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=1 Bytes=12)
  14   13         INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
  15    1     MERGE JOIN (Cost=5 Card=14 Bytes=434)
  16   15       TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=4 Bytes=48)
  17   16         INDEX (FULL SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=4)
  18   15       SORT (JOIN) (Cost=4 Card=14 Bytes=266)
  19   18         TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=3 Card=14 Bytes=266)

 
Execution Plan --> alter session set "_optimizer_connect_by_cost_based" = false; 적용한 plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=1 Bytes=25)
   1    0   CONNECT BY (WITH FILTERING)
   2    1     FILTER
   3    2       COUNT
   4    3         NESTED LOOPS (Cost=3 Card=1 Bytes=25)
   5    4           TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=2 Card=1 Bytes=13)
   6    4           TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=1 Bytes=12)
   7    6             INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
   8    1     HASH JOIN
   9    8       CONNECT BY PUMP
  10    8       COUNT
  11   10         NESTED LOOPS (Cost=3 Card=1 Bytes=25)
  12   11           TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=2 Card=1 Bytes=13)
  13   11           TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=1 Bytes=12)
  14   13             INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
  15    1     COUNT
  16   15       NESTED LOOPS (Cost=3 Card=1 Bytes=25)
  17   16         TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=2 Card=1 Bytes=13)
  18   16         TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=1 Bytes=12)
  19   18           INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)

결론:
Hierarchical Queries는 조직도나 tree 구조의 BOM 업무의 거의 모든 기능을 커버한다.
위의 문법은 아주 기초적인 것들이니 반드시 이해하고 업무에 활용하기 바란다.

'Oracle > SQL Pattern' 카테고리의 다른 글

응용력 발휘하기  (2) 2009.03.20
Hypothetical Functions 은 어떨때 써야하나?  (3) 2008.10.25
가로를 세로로 바꾸기  (0) 2008.05.27
Jonathan Lewis 의 퀴즈 접근법  (0) 2008.05.15
Recursive SQL  (2) 2008.04.30
Posted by extremedb
,

가로를 세로로 바꾸기

세로를 가로로 만드는 방법에 대해서는 Recursive SQL 에서 충분히 논하였다.
그렇다면 가로를 세로로 바꾸는 방법중의 최적은 무엇인가?

그문제와 관련하여 필자는 얼마전 어떤 개발자가 유져로 부터 다음과 같은 질문을 받았는것을 보았다.

유져 : "DBMS 와 웹 Application 간에 배열 연동이 안되므로 변수를 배열로 던져주지 못하는 대신에 '7654,7698,7780,7788' 처럼 변수를 만들어서 Oracle 에 던지고 싶습니다.
그런데 이렇게 던지면 Oracle 에서 List 로 만들어서 처리할수 있습니까?"

개발자 : "예" --> 어느정도 내공이 있는 개발자임을 알수 있다.

유져 : "구분자(delimiter)가 항상 ',' 가 아닐수도 있고 1 byte 도 아닐수 있습니다.
예를들면 구분자가 ':;;:' 처럼 될수도 있고 그것은 프로그램 마다 다를수 있습니다.
그래도 가능 하겠습니까?"

개발자: (주저주저 하며 대답을 못함)

이런경우 일단 대답은 "예" 이다.


왜냐하면 프로그램마다 문자열과 구분자를 받을수 있기 때문이다.

SELECT :v_str , :v_deli FROM dual;
--여기서 :v_deli 는 구분자(',') 이며 :v_str 는 문자열 '7654,7698,7780,7788' 이다.

결과 :




Oracle 10g 라면 아래처럼 간단한 SQL 로 IN-LIST 를 만들수 있다.

SELECT
ltrim(regexp_substr(:v_deli||:v_str||:v_deli,'[^'||:v_deli||']+', 1, level ), :v_deli) AS empno
FROM dual
connect by level<= ( length(:v_deli||:v_str) - length(replace(:v_deli||:v_str, :v_deli)) ) / length(:v_deli)

결과 :

처리의 핵심첫번째로는 문자열 길이 에서 구분자(comma) 를 제거한 길이를 빼서 구분자의 길이로 나누면 row 갯수가 나온다는 점이다.
두번째로는 10g 부터 사용할수 있는 정규식을 사용하여 시작문자와 끝문자 를 정의해서 그사이에 있는 문자들을 가져올수 있다는 점이다.
예를들면 regexp_substr( 문자열, ',[^,]+') 이렇게 하면 문자열에서 ,(comma) 부터 다음 ,(comma) 까지의 문자들을 가지고 올수 있다.
물론 10g 미만의 버젼이라면 level 과 connect by 를 대신해서 copy_t 류의 테이블을 사용하고 substr 과 instr 함수를 적절히 조합하면 된다.

이해를 돕기위해 아래 SQL 을 실행해 보자.
아래 SQL 은 첫번째,두번째,세번째, 네번째 의 사번(EMPNO)을 위의방법을 이용하여 각각 가져오고 있다.

SELECT
regexp_substr(',7654,7698,7780,7788,', ',[^,]+', 1, 1 ) empno1,
regexp_substr(',7654,7698,7780,7788,', ',[^,]+', 1, 2 ) empno2,
regexp_substr(',7654,7698,7780,7788,', ',[^,]+', 1, 3 ) empno3,
regexp_substr(',7654,7698,7780,7788,', ',[^,]+', 1, 4 ) empno4
FROM dual;





주의사항은 컬럼구분자를 오라클 정규식의 예약어인 파이프라인 이나 괄호 )( ][ {} 등을 구분자로 사용하지 말라는것이다.
파이프라인 이나 괄호 대신에 ':;;:' 처럼 적당한 다른문자를 사용하면 됨.


'Oracle > SQL Pattern' 카테고리의 다른 글

응용력 발휘하기  (2) 2009.03.20
Hypothetical Functions 은 어떨때 써야하나?  (3) 2008.10.25
Hierarchical Queries  (3) 2008.06.23
Jonathan Lewis 의 퀴즈 접근법  (0) 2008.05.15
Recursive SQL  (2) 2008.04.30
Posted by extremedb
,

Jonathan Lewis 의 퀴즈 접근법

Jonathan Lewis 의 Cost-Based Oracle Fundamental 이라는 책을보면
퀴즈에 대한 Jonathan의 재미있는 접근법과 사고방식을 엿볼수 있다.
물론 SQL을 이용한 퀴즈문제 이다.

퀴즈는 두 수학자의 대화로 부터 시작하며 수학자2의 세딸아이의 나이를 맞추는 것이다.
Jonathan은 본퀴즈에 대한 자신의 접근법이 성능과는 무관하다는 것을 먼저 이야기 하였다.
퀴즈의 제약조건은 다른 언어(3GL 또는 4GL)의 도움없이 순수한 select 쿼리 하나로만 이루어져야 한다.
수학자1이 나이를 맞추어야 하며 수학자1이 힌트를 요구할때마다 수학자2가 적절한
힌트를 주는 방식으로 대화가 진행된다.

수학자1 : Do you have any children ?
수학자2 : Yes, three.
수학자1 : How old are they ?
수학자2 : Multiply their ages together and you get 36 --> 세딸의 나이를 곱하면 36임을 알수있다.
수학자1 : That's not enough information to work out the answer
-->위의정보로는 부족하므로 힌트를 더 요구한다.
수학자2 :Add their ages together and the answer is the same as the number of people
in this room --> 세딸의 나이를 더하면 이방의 사람수와 같음을 알수 있다.
수학자1 :That's still not enough information to work out the answer
--> 그것만으론 부족해서 힌트를 더 요구한다.
수학자2 :The oldest daughter has a pet hamster with a wooden leg.
-->가장 나이가 많은 딸이 햄스터를 키운다.
수학자1 :Thank you. I've got the answer.
--> 수학자1이 드디어 정답을 맞추었다.

위의 대화를 종합하여 필자는 다음과 같은 결론을 내렸다.

1.세딸의 나이의 곱이 36 이다. 따라서 1*1*36 일수도 있고 1* 2* 18 일수 도 있고 나머지조합도 여럿일수 있다.
2.세딸의 나이의 합은 그방의 사람의 수와 일치하나 세딸의 나이의곱 = 36 and 세딸의 나이의 합이 같은 조합 >= 2
의 조건으로도 조합이 여러개 생겨서 정답을 구할수 없으므로 수학자 1이 힌트를 더요구하고 있다.
3.가장 나이가 많은 딸이 한명임을 알수있다. 즉 막내와 둘째는 나이가 같을수 있지만 장녀는 둘보다 나이가 많다는 것이다.

select 문 한방으로 퀴즈를 풀수 있어야 한다.
이제부터 Jonathan의 방식대로 한줄 한줄씩 풀어보자.

먼저1~36 까지의 나이 LIST 를 만든다.
with age_list as ( select rownum age from all_objects where rownum <= 36),

1)위에서만든 LIST 로 곱이 36인 막내, 둘째, 첫째아이의 나이조합을 만들고
나이의 합과 곱도 만든다.
product_check as (
select age1.age as youngest,
age2.age as middle,
age3.age as oldest,
age1.age + age2.age + age3.age as summed,
age1.age * age2.age * age3.age as product
from age_list age1,
age_list age2,
age_list age3

where age2.age >= age1.age
and age3.age >= age2.age
and age1.age * age2.age * age3.age = 36 ),


2)세딸의 나이를 더하면 이방의 사람수와 같으나 그조합이 여러개임
summed_check as
(
select youngest, middle, oldest, summed, product
from ( select youngest, middle, oldest, summed, product,
count(*) over (partition by summed) ct
from product_check )
where ct > 1 --> 나이의 합의 조합이 2개 이상 나와야 한다.
)

3)가장 나이가 많은 딸이 한명임을 이용한 조건
select *
from summed_check
where oldest > middle;

With 문의 특징과 분석함수를 이용하여 단계적(절차적)으로 퀴즈를 풀고 있다.
유용하고 수학적인 접근법이다.

여러분이라면 더멋지게 퀴즈를 풀수 있지 않을까?

서브쿼리 팩토링(with 서브쿼리) 의 한가지 주의사항은 인라인뷰와는 다르게 no_merge 힌트등을 쓸필요가 없고 (꼭써야 한다면 서브쿼리에 Merterialize 힌트를 사용한다) 서브쿼리에 inline힌트를 써서 옵티마이져 자신이 서브쿼리를 최적화 한다는데 묘미가 있다고 Jonathan은 밝히고 있다.
아래 압축파일중에 9장 with_subq_01~02 부분을 보면된다. 꼭 테스트 해보기 바란다.
관련 SQL:
http://www.apress.com/book/downloadfile/2450

'Oracle > SQL Pattern' 카테고리의 다른 글

응용력 발휘하기  (2) 2009.03.20
Hypothetical Functions 은 어떨때 써야하나?  (3) 2008.10.25
Hierarchical Queries  (3) 2008.06.23
가로를 세로로 바꾸기  (0) 2008.05.27
Recursive SQL  (2) 2008.04.30
Posted by extremedb
,

Recursive SQL

Oracle/SQL Pattern 2008. 4. 30. 11:30

Recursive SQL

개념 :
SQL 이 좋은 언이이긴 하지만 집합처리 언어이기 때문에 불편한점이 많은 것이 사실이다.
특히 아래와 같은 구조에서 세로를 가로로 바꿀려고 하면 힘들다.

SELECT DEPTNO, ENAME
FROM EMP;

DEPTNO ENAME
------ ----------
10 CLARK
10 KING
10 MILLER
20 SMITH
20 ADAMS
20 FORD
20 SCOTT
20 JONES
30 ALLEN
30 BLAKE
30 MARTIN
30 JAMES
30 TURNER
30 WARD

위의 데이터를 아래처럼 부서번호별로 가로로 바꾸어야 하는것이 미션이다.

DEPTNO ENAME
------- ------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

오라클의 경우 조금 복잡한 Recursive SQL 을 작성해야 한다.

select deptno,
ltrim(sys_connect_by_path(ename,','),',') ename
from (
select deptno,
ename,
row_number() over (partition by deptno order by empno) rn,
count(*) over (partition by deptno) cnt
from emp
)
where level = cnt
start with rn = 1
connect by prior deptno = deptno and prior rn = rn-1 ;

그럼 mySQL은 어떨까?
놀랍게도 mySQL 은 group_concat 한방으로 해결하고 있다.

select deptno,
group_concat(ename order by empno separator, ',') as ename
from emp
group by deptno;

사실 필자는 오라클 9i 이전버젼부터 mySQL 의 정규식을 부러워 했었다.
mySQL 은 정말 그렇고 그런? DBMS 일까? 다시생각해야 될필요가 있다.

그럼 DB2 나 SQL SERVER 는 Recursive SQL 이 사용가능할까?

현재 많은 사람들이 Recursive 쿼리가 안된다고 판단하고 Self Join 을 사용하고 있다.
그러나 DB2나 SQL 서버의 최신버젼은 아래와 같이 Recursive With 를 사용하면 된다.
with x (deptno, cnt, list, empno, len)
as (
select deptno, count(*) over (partition by deptno),
cast(ename as varchar(100)), empno, 1
from emp
union all
select x.deptno, x.cnt, x.list ',' e.ename, e.empno, x.len+1
from emp e, x --> recursive call
where e.deptno = x.deptno
and e.empno > x. empno
)
select deptno,list
from x
where len = cnt ;

참고로 오라클은 Recursive With 절 이나 group_concat 은 11g 버젼에서도 지원하지 않고 있다.
오라클에서는 Recursive function 은 예전부터 지원되어 왔다.

참조서적 : SQL Cookbook By Anthony Molinaro(O'Reilly)

'Oracle > SQL Pattern' 카테고리의 다른 글

응용력 발휘하기  (2) 2009.03.20
Hypothetical Functions 은 어떨때 써야하나?  (3) 2008.10.25
Hierarchical Queries  (3) 2008.06.23
가로를 세로로 바꾸기  (0) 2008.05.27
Jonathan Lewis 의 퀴즈 접근법  (0) 2008.05.15
Posted by extremedb
,