이펙티브 오라클 (2008년)
SQL 바로 알기 0 0 1,412

by 구루비스터디 ROWNUM first_rows [2009.04.30]



        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를 빼면 쿼리결과는 어떻게 나올것인가???
WEEKU1U2U3U4U5U6U7U8
1P6P5P8P1P4P7P3P2
2P3P5P4P1P8P6P2P7
3P6P1P3P2P4P5P7P8
4P8P2P3P6P4P1P7P5
5P5P8P6P7P2P1P3P4
6P1P2P7P4P5P3P6P8
7P6P4P7P5P3P1P2P8


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" 장


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  /  

DEPTNOENAMESALCUMDEPTTOTSALBYDEPTCOMTOTTOTSAL
10MILLER130013008750130029025
10CLARK245037508750375029025
10KING500087508750875029025
20SMITH80080010875955029025
20ADAMS11001900108751065029025
20JONES29754875108751362529025
20SCOTT300010875108751962529025
20FORD300010875108751962529025
30JAMES95095094002057529025
30MARTIN1250345094002307529025
30WARD1250345094002307529025
30TURNER1500495094002457529025
30ALLEN1600655094002617529025
30BLAKE2850940094002902529025

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  /

DEPTNOENAMESALRNRANKDENSE_RANK
10KING5000111
10CLARK2450222
10MILLER1300333
20JONES3000111
20SCOTT3000211
20FORD3000311
20ADAMS1100442
20SMITH800553
30BLAKE2850111
30ALLEN1600222
30TURNER1500333
30WARD1250444
30MARTIN1250544
30JAMES950665


<< 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  /

 
DEPTNOSAL1SAL2SAL3
105000
102450
101300
203000
203000
202975
201100
302850
301600
301500


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  /

 
DEPTNOSAL1SAL2SAL3
10500024501300
20300029751100
30285016001500


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  /


PORTACTIVITYNXT_ACTIVITYACTIVITY_NXT_ACTIVITY_DATE
portloadcharge2002-08-102002-09-15
portchargesendtorepair2002-09-152003-02-10
portsendtorepairrepaired2003-02-102003-02-22
portrepairedload2003-02-222003-03-10
portloadcharge2003-03-102003-03-15
portcharge2003-03-15
port2sendtorepairrepaired2002-02-132002-02-26
port2repairedload2002-02-262002-08-11
port2loadcharge2002-08-112002-09-17
port2chargeload2002-09-172003-03-15
port2loadcharge2003-03-152003-03-21
port2charge2003-03-21
  • 이번트 사이의 날짜 계산

      
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  /

PORTACTIVITYNXT_ACTIVITYACTIVITY_NXT_ACTIVITY_DATEDAYS_BETWEEN
portloadcharge2002-08-102002-09-1536
portchargesendtorepair2002-09-152003-02-10148
portsendtorepairrepaired2003-02-102003-02-2212
portrepairedload2003-02-222003-03-1016
portloadcharge2003-03-102003-03-155
portcharge2003-03-15
port2sendtorepairrepaired2002-02-132002-02-2613
port2repairedload2002-02-262002-08-11166
port2loadcharge2002-08-112002-09-1737
port2chargeload2002-09-172003-03-15179
port2loadcharge2003-03-152003-03-216
port2charge2003-03-21
  • 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  /

PORTACTIVITYNXT_ACTIVITYDAYS_BETWEEN
portchargesendtorepair148
portloadcharge20.5
portrepairedload16
portsendtorepairrepaired12
port2chargeload179
port2loadcharge21.5
port2repairedload166
port2sendtorepairrepaired13


비 분석쿼리 대비 장점
  • 작성하기가 쉽다.
  • 성능이슈의 위험이 적다.
  • 훨씬 효율적이다.


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


  • 데이터를 그룹으로 나누기
    • FIRST_VALUE와 LAST_VALUE 해석함수를 이용하여 첫번째 행과 마지막 행을 선별
  • ROWID 범위로 분할하기
    • 쿼리를 취하여 또 다른 인라인 뷰로 감싼다.
  • 병렬처리
    • DBMS_JOBS을 이용한 최종 병렬 처리
"구루비 데이터베이스 스터디모임" 에서 2008년에 "이펙티브 오라클" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3565

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입