WITH players 
        AS (
        SELECT 'P' || ROWNUM username
        FROM   all_objects
        WHERE  ROWNUM <= 8
        )
        , weeks 
        AS (
        SELECT ROWNUM week
        FROM   all_objects
        WHERE  ROWNUM <= 7)
        SELECT week, 
              MAX(decode(rn,1,username, NULL)) u1,
              MAX(decode(rn,2,username, NULL)) u2,
              MAX(decode(rn,3,username, NULL)) u3,
              MAX(decode(rn,4,username, NULL)) u4,
              MAX(decode(rn,5,username, NULL)) u5,
              MAX(decode(rn,6,username, NULL)) u6,                              
              MAX(decode(rn,7,username, NULL)) u7,
              MAX(decode(rn,8,username, NULL)) u8
        FROM (SELECT username, 
                     week, 
                     row_number() over(PARTITION BY week ORDER BY rnd) rn 
              FROM (SELECT username, week, dbms_random.random rnd FROM players, weeks)) 
        GROUP BY week;  

• 질문) GROUP BY week와 max를 빼면 쿼리결과는 어떻게 나올것인가???

I. ROWNUM 유사 열

1. ROWNUM 의 사용용도

• 쿼리를 튜닝한다.
• 쿼리를 통해 페이지 분할
• 상위 N개의 처리를 수행

2. ROWNUM 사용용법

• ROWNUM 동작 방식
  • FROM과 WHERE 절이 먼저 처리된다.
  • FROM/WHERE로부터 출력된 각 행에 ROWNUM이 할당되고 ROWNUM이 증가된다.
  • SELECT가 적용된다.
  • GROUP BY가 적용된다.
  • HAVING이 적용된다.
  • 정렬된다.
  • << 주의 >> 정렬또는 집계가 왼료되기 전에 행에 할당된다.

• a. 잘못된 쿼리 예

SELECT ...., ROWNUM 
FROM T 
WHERE <WHERE clause>
GROUP BY <COLUMN>
HAVING <HAVING clause>
ORDER BY <COLUMNS>
SELECT *
FROM   emp
WHERE  ROWNUM <= 5
ORDER  BY sal DESC;

ROWNUM = 1;

FOR x IN (SELECT * FROM emp) 
LOOP 
    EXIT WHEN NOT (ROWNUM <= 5) 
    output RECORD TO temp 
    ROWNUM = NUMBER + 1 
END LOOP 
SORT temp

• b. 올바른 쿼리

SELECT *
FROM   (SELECT *
        FROM   emp
        ORDER  BY sal DESC)
WHERE  ROWNUM <= 5;

B. ROWNUM을 이용한 튜닝

• 최적하기가 뷰를 병합할 수 없는 경우
  • 집합 연산자 (UNION, UNION ALL, INTERSECT, 그리고 MINUS)
  • CONNECT BY 절
  • ROWNUM 유사 열
  • SELECT 목록의 집계 함수 (AVG, COUNT, MAX 등 )
• 함수 호출 줄이기

• AS-IS

DECLARE
BEGIN 
    dbms_application_info.set_client_info(0);
END;
SELECT a12.prc_chk_typ_desc 
      ,a11.prc_chk_dt
      ,a11.cmpt_loc_key
      ,a11.prod_key  upc_prod_key
      ,a11.loc_key
      ,MAX(F(a11.prod_key,a11.prc_chk_key, 'QTY', 'D',1))
      ,MAX(F(a11.prod_key,a11.prc_chk_key, 'AMT', 'D',1))
      ,MAX(F(a11.prod_key,a11.prc_chk_key, 'CODE', 'D',1))
      ,MAX(F(a11.prod_key,a11.prc_chk_key, 'PRC', 'D',1))                  
FROM   t1 a11
      ,t2 a12
WHERE a11.cmpt_loc_key = a12.cmpt_loc_key
AND   a11.loc_key   = a12.loc_key
AND   a11.prc_chk_key = a12.prc_chk_key
GROUP BY a12.prc_chk_typ_desc, a11.prc_chk_dt, a11.cmpt_loc_key, a11.prod_key, a11.loc_key;      

SELECT userenv('client_info') DATA FROM dual 
DATA
---------------------
10000
***

• ROWNUM을 이용한 TO-BE

DECLARE
BEGIN 
    dbms_application_info.set_client_info(0);
END;

SELECT /* + use_hash(a11, a12) */
       a12.prc_chk_typ_desc 
      ,a11.prc_chk_dt
      ,a11.cmpt_loc_key
      ,a11.prod_key  upc_prod_key
      ,a11.loc_key
      ,MAX(a)
      ,MAX(b)
      ,MAX(c)
      ,MAX(d)
FROM  (SELECT A11.* 
      ,F(a11.prod_key,a11.prc_chk_key, 'QTY', 'D',1)a
      ,F(a11.prod_key,a11.prc_chk_key, 'AMT', 'D',1)b
      ,F(a11.prod_key,a11.prc_chk_key, 'CODE', 'D',1)c
      ,F(a11.prod_key,a11.prc_chk_key, 'PRC', 'D',1)d       
      ,ROWNUM r
      FROM t1 a11) a11               
      ,t2 a12
WHERE a11.cmpt_loc_key = a12.cmpt_loc_key
AND   a11.loc_key   = a12.loc_key
AND   a11.prc_chk_key = a12.prc_chk_key
GROUP BY a12.prc_chk_typ_desc, a11.prc_chk_dt, a11.cmpt_loc_key, a11.prod_key, a11.loc_key;      
SELECT userenv('client_info') DATA FROM dual;


DATA
---------------------
200

2) 쿼리 조인하기

• C. ROWNUM을 이용한 페이지 매김

    SELECT * 
    FROM (SELECT /*+ first_rows */ a.* , ROWNUM rnum
          FROM ( SELECT * FROM xxowmm_tn_output_dtl ) a 
          WHERE ROWNUM <= 10 )
    WHERE rnum >= 7;      

D. ROWNUM을 이용한 상위 N개의 쿼리 처리

• 문제를 공략하는 두가지 방법
  • 클라이언트 애플리케이션이 이 쿼리를 열어서 처음 N개의 행만 가져오도록 한다.
  • 이 쿼리를 인라인 뷰로 사용하고 SELECT * FROM (your_query_here) WHERE ROWNUM <= N 과 같이 ROWNUM을 이용하여 결과를 제한한다.

• 일반적인 오라클의 쿼리 수행 방법
  • T에 대해 전체 스캔을 적용한다.
  • UNINDEXED_CLLUMN에 따라 T를 정렬 (좀더 정확히 얘기하면, 전체 정렬을 수행한다.)
  • 아마다 정렬 영역 메모리가 소진되어 임시 익스텐트를 디스크에 써야 할 수도 있다.
  • 첫 10개의 레코드가 요청된 경우 이들을 얻기 위하여 임시 익스텐트를 다시 병합한다.
  • 이 작업이 끝나면 임시 익스텐트를 청소(해제)한다.

• 오라클의 TOP-N 쿼리 동작 방법
  • 1. 전과 같이 T에 대해 전체스캔을 적용한다. ( 이 과정을 피할 수는 없다.)
  • 2. N 개의 요소로 이루어진 배열에서 :N개의 행만을 정렬한다. (배열 개념 적용)

E. ROWNUM 요약

• ROWNUM 이 할당되는 방법, 따라서 ROWNUM 을 사용하는 버그 없는 쿼리를 작성할 수 잇따.
• ROWNUM 이 쿼리의 처리에 영향을 미치는 방식, 따라서 ROWNUM 을 사용하여 결과 집합을 실체화하거나 웹에서 쿼리의 페이지를 매길 수 있다.
• ROWNUM 이 쿼리에 의해 수행되는 작업을 줄이는 방법, 따라서 엄청난 TEMP 공간을 소비했던 상위 N쿼쿼리가 이제는 TEMP 공간을 아예 사용하지
• 않고 훨씬 신속하게 결과를 반환한다.

II. 스칼라 하위 쿼리

1. 스칼라 하위 쿼리의 사용용도

• 외부 조인의 필요성 제거
• 단일 쿼리를 이용하여 다수의 테이블로부터 정보집계
• 단일 쿼리를 이용하여 다른 테이블/행으로부터 선택적으로 선택

2. 스칼라 하위 쿼리 사용용법

• A. 외부 조인 제거하기

SELECT a.username, COUNT(*)
FROM all_users a, all_objects b
WHERE a.username = b.owner(+)
GROUP BY a.username;  -- 21.266 seconds 

SELECT a.username
      ,(SELECT COUNT(*)
        FROM   all_objects b
        WHERE  b.owner = a.username) cnt
FROM all_users a;     -- 55.344 seconds 

• 참고) Oracle9i 릴리스 2에서 이들 스칼라 서브쿼리의 계획을 볼순 없다.

<< 하나 이상의 열이 필요한 경우 >>

• 1. 두 개의 스칼라 하위 쿼리를 사용 => 작업을 시간은 두배
• 2. 단일 스칼라 하위 쿼리를 사용

SELECT username
      ,to_number(SUBSTR(data
                       ,1
                       ,10)) cnt
      ,to_number(SUBSTR(data
                       ,11)) AVG
FROM   (SELECT a.username
              ,(SELECT TO_CHAR(COUNT(*)
                              ,'fm0000000009') || AVG(object_id)
                FROM   all_objects b
                WHERE  b.owner = a.username) data
        FROM   all_users a) a

• 3. 객체 유형 사용하기

CREATE OR REPLACE TYPE myScalarType AS OBJECT (cnt NUMBER, average NUMBER)
SELECT username
      ,a.DATA.cnt 
      ,a.DATA.average
FROM   (SELECT a.username
              ,(SELECT myScalarType(COUNT(*), AVG(object_id) )
                FROM   all_objects b
                WHERE  b.owner = a.username) data
        FROM   all_users a) a           

B. 다수의 테이블로부터 집계하기

• Question : 1. 최근 50일 동안 생성된 모든 사용자
• 2. 사용자 이름별로 사용자 이름, 사용자 ID, 생성일자, 이들이 소유하고 있는 테이블 수 , 제약조건의 수를 보여주는 보고서 작성

• 인라인 뷰 구현

SELECT a.username
      ,a.user_id
      ,a.created
      ,NVL(b.cons_cnt
          ,0) cons
      ,NVL(c.tables_cnt
          ,0) tables
FROM   all_users a
      ,(SELECT owner
             ,COUNT(*) cons_cnt
       FROM   all_constraints
       GROUP  BY owner) b
      ,(SELECT owner
             ,COUNT(*) tables_cnt
       FROM   all_tables
       GROUP  BY owner) c
WHERE  a.username = b.owner(+)
AND    a.username = c.owner(+)
AND    a.created > SYSDATE - 100;  -- 7.73 Seconds 

• 스칼라 서브 쿼리 구현

SELECT username
      ,user_id
      ,created
      ,(SELECT COUNT(*) 
       FROM   all_constraints
       WHERE owner = username )  cons
      ,(SELECT COUNT(*) 
       FROM   all_tables
       WHERE owner = username) TABLES
FROM   all_users
WHERE     all_users.created > SYSDATE - 100;  -- 1.063 Seconds

• 인라인 뷰에 술어 조건 추가

SELECT a.username
      ,a.user_id
      ,a.created
      ,NVL(b.cons_cnt
          ,0) cons
      ,NVL(c.tables_cnt
          ,0) tables
FROM   all_users a
      ,(SELECT all_constraints.owner, COUNT(*) cons_cnt
       FROM   all_constraints, all_users 
       WHERE all_users.created > SYSDATE  - 100
       AND   all_users.username = all_constraints.owner
       GROUP  BY owner) b
      ,(SELECT all_tables.owner, COUNT(*) tables_cnt
       FROM   all_tables , all_users 
       WHERE all_users.created > SYSDATE  - 100
       AND   all_users.username = all_tables.owner
       GROUP  BY owner) c
WHERE  a.username = b.owner(+)
AND    a.username = c.owner(+)
AND    a.created > SYSDATE - 100;  -- 0.657 Seconds  

C. 다른 테이블로부터 선택하기

• 행을 테이블 집합에 조인하기
• 조회하기

III. 해석함수

• 키워드 SELECT가 소개된 이래로 SQL 에 발생한 가장 획기적인 것!!!

1. 특징

• 여러행의 값을 계산할 수 있도록 해준다.
• 행을 그룹으로 묶지 않고도 같은 작업을 수행 할 수 있도록 해준다.
• 상세한 레코드와 함께 이들을 표시할 수 있도록 한다.

2. 종류

3.참조문서 "게이터 웨어하우스에서 분석을 위한 SQL" 장

• http://www.oracle.com/technology/documentation/warehouse.html
• ^analyticfunction.ppt
• ^Analytic Function in Oracle8i and 9i.pdf

4. 해석 함수의 이용사례

SQL> SELECT deptno, ename, sal,
  2  SUM(sal) over(PARTITION BY deptno ORDER BY sal) CumDeptTot,  -- 급여가 놓은 순으로 특정부서내에서의 누적계산
  3  SUM(sal) over(PARTITION BY deptno) SalByDept,                --부서별 합계 계산 
  4  SUM(sal) over(ORDER BY deptno, sal) ComTot,                  -- sal에 의해 정렬도고 난 후에  sal 열의 누적을 생성한다. 
  5  SUM(sal) over() TotSal                                       -- sum(sal) 과 동일 
  6  FROM emp
  7  ORDER BY deptno, sal
  8  /  

14 rows selected

• 1) 파티션에서 특정 행 찾기 ==> 교재 참조 (p.684)
• 2) 그룹의 상위 N 찾기
  • ROW_NUMBER는 정렬 후에 파티션의 모든 행에 고유한 일련의 수를 할당한다.
  • RANK는 파티션의 모든 행에 순위를 부여한다. 같은 값을 가진 행에는 동일한 순위가 부여된다. 순위사이에는 공백이 있을 수 있다.
  • 예를 들면 두개의 행이 1로 순위가 매겨지면 2번은 존재하지 않게된다. 1이 두 개가 존재하면 2라는 수는 존재할 수 없다.
  • DENSE_RANK는 RANK와 같이 파티션의 각 행에 순위를 부여한다. 같은 값을 가진행에는 동일한 순위가 부여된다. 순위는 연속적이어서 순위 사이에 공백이 존재하지 않는다.

                                            
SQL> 
SELECT deptno,                           
  2        ename,                             
  3        sal,                               
  4        row_number() over(PARTITION BY deptno ORDER BY sal DESC) rn,
  5        rank() over(PARTITION BY deptno ORDER BY sal DESC) rank,
  6        dense_rank() over(PARTITION BY deptno ORDER BY sal DESC) dense_rank
  7  FROM   emp                               
  8  ORDER BY deptno, sal DESC
  9  /
   | DEPTNO |ENAME         |     SAL   |       RN   |     RANK  |DENSE_RANK |
   |     10 |KING          |    5000   |        1   |        1  |         1 |
   |     10 |CLARK         |    2450   |        2   |        2  |         2 |
   |     10 |MILLER        |    1300   |        3   |        3  |         3 |
   |        |              |           |            |           |           |
   |     20 |SCOTT         |    3000   |        1   |        1  |         1 |        
   |     20 |FORD          |    3000   |        2   |        1  |         1 |
   |     20 |JONES         |    2975   |        3   |        3  |         2 |
   |     20 |ADAMS         |    1100   |        4   |        4  |         3 |
   |     20 |SMITH         |     800   |        5   |        5  |         4 |
   |        |              |           |            |           |           |
   |     30 |BLAKE         |    2850   |        1   |        1  |         1 |
   |     30 |ALLEN         |    1600   |        2   |        2  |         2 |
   |     30 |TURNER        |    1500   |        3   |        3  |         3 |
   |     30 |MARTIN        |    1250   |        4   |        4  |         4 |
   |     30 |WARD          |    1250   |        5   |        4  |         4 |
   |     30 |JAMES         |     950   |        6   |        6  |         5 |
14 rows selected
SQL> UPDATE emp SET  sal = 3000 WHERE ename = 'JONES';
1 row updated
SQL> 
SQL> SELECT deptno,
  2        ename,
  3        sal,
  4        row_number() over(PARTITION BY deptno ORDER BY sal DESC) rn,
  5        rank() over(PARTITION BY deptno ORDER BY sal DESC) rank,
  6        dense_rank() over(PARTITION BY deptno ORDER BY sal DESC) dense_rank
  7  FROM   emp
  8  ORDER BY deptno, sal DESC
  9  /

<< ROWNUMBER VS ROW_NUMBER() 비교 >>

• ROWNUBER : 결과집합으로부터 처음 "N"개의 행을 얻는데 적합
• ROW_NUBER : 결과집합내의 그룹으로부터 처음 "N"개의 행을 얻는데 적합

3) 전치(피보팅) What means????

• 1. DECODE를 이용한 피벗 수행

    SELECT deptno
  2        ,DECODE(dsnse_rank,1,sal) sal1
  3        ,DECODE(dsnse_rank,2,sal) sal2
  4        ,DECODE(dsnse_rank,3,sal) sal3
  5  FROM   (SELECT deptno
  6                ,sal
  7                ,dense_rank() over(PARTITION BY deptno ORDER BY sal DESC) dsnse_rank
  8          FROM   emp)
  9  WHERE  dsnse_rank < = 3
 10  /

 

2. 그룹 함수( MAX)를 이용한 피벗 수행

• <<핵심>> 집계(그룹)함수는 NULL을 무시한다는 사실!!

SQL> SELECT deptno
  2        ,MAX(DECODE(dsnse_rank,1,sal)) sal1
  3        ,MAX(DECODE(dsnse_rank,2,sal)) sal2
  4        ,MAX(DECODE(dsnse_rank,3,sal)) sal3
  5  FROM   (SELECT deptno
  6                ,sal
  7                ,dense_rank() over(PARTITION BY deptno ORDER BY sal DESC) dsnse_rank
  8          FROM   emp)
  9  WHERE  dsnse_rank < = 3
 10  GROUP BY deptno
 11  /

 

4) 이전행 / 다음행

• 활동일자에 의해 정렬

SQL> SELECT port
  2        ,activity
  3        ,lead(activity) over(PARTITION BY port ORDER BY activity_) nxt_activity
  4        ,activity_
  5        ,lead(activity_) over(PARTITION BY port ORDER BY activity_) nxt_activity_date
  6  FROM   t
  7  /

• 이번트 사이의 날짜 계산

      
SQL> SELECT port, activity, nxt_activity, activity_ , nxt_activity_date, nxt_activity_date - activity_ days_between
  2  FROM (
  3  SELECT port
  4        ,activity
  5        ,lead(activity) over(PARTITION BY port ORDER BY activity_) nxt_activity
  6        ,activity_
  7        ,lead(activity_) over(PARTITION BY port ORDER BY activity_) nxt_activity_date
  8  FROM   t)
  9  /

• AVG와 GROUP BY 사용

SQL> SELECT port, activity, nxt_activity,  AVG (nxt_activity_date - activity_) days_between
  2  FROM (
  3  SELECT port
  4        ,activity
  5        ,lead(activity) over(PARTITION BY port ORDER BY activity_) nxt_activity
  6        ,activity_
  7        ,lead(activity_) over(PARTITION BY port ORDER BY activity_) nxt_activity_date
  8  FROM   t)
  9  WHERE nxt_activity IS NOT NULL
 10  GROUP BY port, activity, nxt_activity
 11  ORDER BY 1,2
 12  /

비 분석쿼리 대비 장점

• 작성하기가 쉽다.
• 성능이슈의 위험이 적다.
• 훨씬 효율적이다.

5) 병렬 처리

• ROWID 범위를 이용
• 익스텐트를 통에 넣기
  • 익스텐트를 누적 크기에 따라 N개의 그룹 중 하나에 포함시킴으로써 통에 넣는다.

• 데이터를 그룹으로 나누기
  • FIRST_VALUE와 LAST_VALUE 해석함수를 이용하여 첫번째 행과 마지막 행을 선별
• ROWID 범위로 분할하기
  • 쿼리를 취하여 또 다른 인라인 뷰로 감싼다.
• 병렬처리
  • DBMS_JOBS을 이용한 최종 병렬 처리

• analyticfunction.ppt