깡통

select
      WEEKS,
      sum(decode(D,'1', DAYNUM)) as SUN,
      sum(decode(D,'2', DAYNUM)) as MON,
      sum(decode(D,'3', DAYNUM)) as TUE,
      sum(decode(D,'4', DAYNUM)) as WED,
      sum(decode(D,'5', DAYNUM)) as THU,
      sum(decode(D,'6', DAYNUM)) as FRI,
      sum(decode(D,'7', DAYNUM)) as SAT
from (
  select
        ROWNUM                  as DAYNUM,
        to_char(to_date(b.YYMM||lpad(ROWNUM,2,'0'),'YYYYMMDD'), 'D') as D,
        cast(to_char(to_date(b.YYMM||lpad(rownum,2,'0'),'YYYYMMDD'), 'W') as number) +
        decode(
            sign(cast(to_char(to_date(b.YYMM||lpad(ROWNUM,2,'0'),'YYYYMMDD'), 'D') as NUMBER)
            -cast(to_char(to_date(b.YYMM||'01','YYYYMMDD'), 'D') as NUMBER)),
            0, -1, 1, -1, 0)  +1  as WEEKS
  from dual cross join (select '200510' as YYMM from dual) b
  connect by level<=to_number(to_char(last_day(to_date(b.YYMM,'YYYYMM')),'DD'))
)
group by WEEKS

오라클 데이터베이스  scott유저의 emp테이블을 보면 empno와 mgr컬럼이 있습니다.
 

mgr 컬럼의 데이터는 해당 사원의 관리자의 empno를 의미 합니다.
 

예를 들어서 아래의 데이터를 보면은..
 

  EMPNO ENAME         SAL        MGR
 ------ --------- ------- ----------
   7369     SMITH          800       7902
   7902     FORD          3000       7566

empno 7369사원의  관리자는 7902의 empno를 가진 사원이며
empno 7902사원의  관리자는 7566의 empno를 가진 사원입니다.
 
 
이런 상위 계층과 하위계층의 관계를 오라클에서는 START WITH와 CONNECT BY를 이용해서
쉽게 가져올 수 있습니다.
 
상품의 카테고리(대분류,중분류,소분류...)를 조회 할때 START WITH와 CONNECT BY를 이용하면
트리 구조로 편리하게 조회 할 수 있습니다.

게시판에서의 일반글과 답변글 과의 관계에서도 사용 할 수 있습니다.

 ⊙ START WITH와 CONNECT BY를 이용해 데이터를 계층적인 순서로  조회할 수 있습니다.
 
 
◈ START WITH
 
 - 계층 질의의 루트(부모행)로 사용될 행을 지정 합니다..
 - 서브쿼리를 사용할 수도 있습니다.
 
 
◈ CONNECT BY
 
 - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 합니다.
 - 보통 PRIOR 연산자를 많이 사용 합니다..
 - 서브쿼리를 사용할 수 없습니다..
 
 
 
◈ CONNECT BY의 실행순서는 다음과 같습니다.
 
 - 첫째 START WITH절
 - 둘째 CONNECT BY 절
 - 세째 WHERE 절 순서로 풀리게 되어있습니다.
 

-- 테스트를 위해서 scott유저로 접속을 합니다.
SQLPLUS scott/tiger
 
 
예제1)상위계층과 하위 계층 관계의 순서대로 쿼리해 옴
 
 
-- LEVEL 예약어를 사용하여  depth를 표현 할 수 있습니다.
-- 직업이 PRESIDENT을 기준으로 계층 구조로 조회합니다.
 
SQL>SELECT LEVEL,empno,ename, mgr
        FROM  emp
        START WITH job = 'PRESIDENT'   -- 직업이 PRESIDENT를 기준으로
        CONNECT BY PRIOR  empno = mgr; -- 사원(empno)과 관리자(mgr)의  관계를 계층 구조로 조회
   
    LEVEL      EMPNO ENAME                       MGR
--------- ---------- -------------------- ----------
        1       7839      KING
        2       7566      JONES                      7839
        3       7788      SCOTT                     7566
        4       7876      ADAMS                     7788
        3       7902      FORD                       7566
        4       7369      SMITH                       7902
        2       7698      BLAKE                       7839
        3       7499      ALLEN                       7698
        3       7521      WARD                       7698
        3       7654      MARTIN                     7698
        3       7844      TURNER                     7698
        3       7900      JAMES                       7698
        2       7782      CLARK                       7839
        3       7934      MILLER                      7782

--  LEVEL컬럼은 depth를 나타냅니다.
-- JONES의 관리자는 KING을 나타냅니다.
--  SCOTT의 관리자는 JONES를 나타냅니다.
--  예제와 같이 상/하의 계층 구조를 쉽게 조회 할 수 있습니다.

예제2) 사원성명을 계층 구조로 보여 줌

SQL>SET LINESIZE 100
SQL>SET PAGESIZE 100
SQL>COL ename FORMAT A20

 -- 예제1의 결과를 가지고 LPAD함수를 이용해서 ename왼쪽에 공백을 추가 했습니다.
SQL>SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job
       FROM emp
       START WITH job='PRESIDENT'
       CONNECT BY PRIOR empno=mgr;

 ENAME                     EMPNO        MGR JOB
-------------------- ---------- ---------- ---------
KING                        7839                   PRESIDENT
    JONES                  7566       7839      MANAGER
        SCOTT             7788       7566      ANALYST
            ADAMS         7876       7788      CLERK
        FORD               7902       7566      ANALYST
            SMITH          7369       7902      CLERK
    BLAKE                  7698       7839      MANAGER
        ALLEN               7499       7698     SALESMAN
        WARD               7521       7698     SALESMAN
        MARTIN             7654       7698     SALESMAN
        TURNER            7844       7698     SALESMAN
        JAMES              7900       7698     CLERK
    CLARK                  7782       7839     MANAGER
        MILLER              7934       7782     CLERK
 

예제3) 레벨이 2까지만 쿼리해서 가져오는 예제
  
SQL>SELECT LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job
        FROM emp
        START WITH job='PRESIDENT'
        CONNECT BY PRIOR empno=mgr
        AND LEVEL <=2 ;
   
ENAME                     EMPNO        MGR JOB
-------------------- ---------- ---------- ---------
KING                        7839                   PRESIDENT
    JONES                  7566       7839      MANAGER
    BLAKE                  7698       7839      MANAGER
    CLARK                  7782       7839      MANAGER 

예제4) 각 label별로 급여의 합과 인원수를 구하는 예제
 
SQL> SELECT LEVEL, SUM(sal) total,COUNT(empno) cnt
        FROM emp
        START WITH job='PRESIDENT'
        CONNECT BY PRIOR empno=mgr     
        GROUP BY LEVEL
        ORDER BY LEVEL;
 
     LEVEL      TOTAL        CNT
---------- ---------- ----------
         1       5000          1
         2       8275          3
         3      13850          8
         4       1900          2

 

◈ 데이터가 많아질 경우....
 
 - 첫째로 풀리는 START WITH job='PRESIDENT' job 컬럼에 index가 생성되어 있지 않는다면
    속도를 보장할 수 없습니다.
 
 - 그리고 둘째로 풀리는 CONNECT BY PRIOR empno = mgr 역시 PRIOR 쪽의 컬럼값이 상수가
   되기 때문에 MGR컬럼에 index를 생성하여야 CONNECT BY의 속도를 보장할 수 있습니다.
  
 - 계층구조를 CONNECT BY, START WITH로 풀면 부분범위 처리가 불가능하고 Desc으로
   표현하기가 어렵 습니다.

참고..
 
http://dblab.changwon.ac.kr/oracle/sqltest/hierarchical.html/
http://www.en-core.com/

/*  UNION, UNION ALL
    UNION : 중복을 제거한 합집합
    UNION ALL : 모든 합집합         */
 SELECT 1 dummy FROM DUAL
  UNION ALL
 SELECT 2 dummy FROM DUAL

/*  Cartesian Product    */  
 SELECT *
   FROM ( SELECT 1 dummy FROM DUAL
           UNION ALL
    SELECT 2 dummy FROM DUAL
  ) a,
        ( SELECT deptno, count(*) cnt
       FROM emp
     GROUP BY deptno
  ) b
 
/*  각각의 합과 총합을 Reporting    */
 SELECT Decode(a.dummy, 1, Nvl(b.dname, 'Null'), 'Sum'),
        Decode(a.dummy, 1, Max(b.cnt), Sum(b.cnt))
   FROM ( SELECT 1 dummy FROM DUAL
           UNION ALL
    SELECT 2 dummy FROM DUAL
  ) a,
        ( SELECT b1.deptno, dname, count(*) cnt
       FROM emp b1, dept b2
     WHERE b1.deptno = b2.deptno
     GROUP BY b1.deptno, dname
  ) b
  GROUP BY a.dummy, Decode(a.dummy, 1, Nvl(b.dname, 'Null'), 'Sum')
  ORDER BY a.dummy, Decode(a.dummy, 1, Nvl(b.dname, 'Null'), 'Sum')
 
/*  각각의 합과 총합을 Reporting    */ 
SELECT a.sys_name 검사항목,
       sum(a.cnt)  총갯수,
       sum(decode(a.stat, '01', cnt, 0)) 운용,
       sum(decode(a.stat, '03', cnt, 0)) 예비운용,
       NULL 최종연동시작시간,
       NULL 최종연동완료시간
  FROM ( SELECT 'iNetPost' sys_name,
                stat,
                count(*) cnt
           FROM tb_office@np_link
          WHERE stat IN ('01', '03')
          GROUP BY stat
       ) a
 GROUP BY a.sys_name

CREATE OR REPLACE PROCEDURE all_tabspec_procedure IS

 v_column_id   user_tab_columns.column_id%TYPE;
 v_column_name  user_tab_columns.column_name%TYPE;
 v_data_type   user_tab_columns.data_type%TYPE;
 v_data_length  user_tab_columns.data_length%TYPE;
 v_data_precision user_tab_columns.data_precision%TYPE;
 v_data_scale  user_tab_columns.data_scale%TYPE;  
 v_nullable   user_tab_columns.nullable%TYPE;
 v_data_default  user_tab_columns.data_default%TYPE;

 v_data_length_num VARCHAR2(10);
 

 CURSOR table_spec_cursor(in_table_name user_tab_columns.table_name%TYPE) IS

  select LTRIM(RTRIM(column_id)) NO,
      LTRIM(RTRIM(column_name)) ITEM_NAME, LTRIM(RTRIM(data_type)) 속성,
      LTRIM(RTRIM(data_length)) 길이, data_precision 길이_P,
      data_scale 길이_S, nullable 널, data_default 디폴트
  from user_tab_columns
  where table_name = in_table_name
  order by column_id;

 CURSOR all_object_cursor IS
--  select object_name from user_objects where object_type in('TABLE', 'VIEW') ;
--  select object_name from user_objects where object_type = 'TABLE' ;
--  select object_name from user_objects where object_type = 'VIEW' ;
  select distinct object_name from user_objects where object_name in ('ANNUITY', 'ANNUITY_DET', 'BANK', 'BASE_DET', 'BENEFIT_HIST',
    'CARE', 'CARE_DET', 'CHANGE', 'CHANGE_DET', 'COMN_KEEP', 'COUPL', 'FAMILY', 'GROW', 'GROW_DET',
    'LOAN', 'PAY_HIST', 'REFER', 'SPECIAL_CON_DET', 'TERM_DET');

BEGIN

 DBMS_OUTPUT.ENABLE(1000000);

 FOR allobj_cursor_rec IN all_object_cursor LOOP

  OPEN table_spec_cursor(allobj_cursor_rec.object_name);
 
  /* OBJECT NAME(TABLE OR VIEW) 인쇄*/
  DBMS_OUTPUT.PUT_LINE(' ');
  DBMS_OUTPUT.PUT_LINE(' ');
  DBMS_OUTPUT.PUT_LINE('====================   ' || allobj_cursor_rec.object_name || '   ====================');

  /* TYTLE 인쇄*/
  DBMS_OUTPUT.PUT_LINE('NO' || ',' || '   ' ||  'COLUMN_NAME' || ',' || '                      ' ||
          '속성' || ',' || '    ' || '길이' ||  ',' || '널'  ||  ',' || '       ' || '디폴트');

  LOOP
   FETCH table_spec_cursor
    INTO v_column_id, v_column_name, v_data_type, v_data_length, v_data_precision, v_data_scale, v_nullable, v_data_default;
   EXIT WHEN table_spec_cursor%NOTFOUND;

   /* （１）데이터Type이「NUMBER」인 경우는
     ① data_scale이０인 경우는data_precision을data_length로 지정    
                                              ② data_scale이NULL인 경우는data_length치를 그대로 사용
     ③ data_scale이０혹은NULL이 아닌 경우는「data_precision｜data_scale」을data_length로 지정한다
        여기서는v_data_length_num변수를 사용（data_length는NUMBER TYPE）
     ④ data_scale이０이아닌 경우는data_precision을data_length로서 사용
      （２）데이터Type이「NUMBER」가 아닌 경우는data_length치를 그대로 사용
   IF v_data_type = 'NUMBER' THEN
    IF v_data_scale = 0 THEN
     v_data_length := v_data_precision;
     DBMS_OUTPUT.PUT_LINE(v_column_id || ',' || '   ' || RPAD(LTRIM(RTRIM(v_column_name)),30,' ') || ','|| '   ' ||
       RPAD(v_data_type,10,' ') || ',' || '   ' || v_data_length || ',' || '   ' ||
       RPAD(v_nullable,1,' ') || ',' || '   ' || LTRIM(RTRIM(v_data_default)));
    ELSE
     v_data_length_num := CONCAT(CONCAT(TO_CHAR(v_data_precision),'|'),TO_CHAR(v_data_scale));

     DBMS_OUTPUT.PUT_LINE(v_column_id || ',' || '   ' || RPAD(LTRIM(RTRIM(v_column_name)),30,' ') || ','|| '   ' ||
         RPAD(v_data_type,10,' ') || ',' || '   ' || v_data_length_num || ',' || '   ' ||
         RPAD(v_nullable,1,' ') || ',' || '   ' || LTRIM(RTRIM(v_data_default)));

    END IF;
   ELSE
    v_data_length := v_data_length;
    DBMS_OUTPUT.PUT_LINE(v_column_id || ',' || '   ' || RPAD(LTRIM(RTRIM(v_column_name)),30,' ') || ','|| '   ' ||
      RPAD(v_data_type,10,' ') || ',' || '   ' || v_data_length || ',' || '   ' ||
      RPAD(v_nullable,1,' ') || ',' || '   ' || LTRIM(RTRIM(v_data_default)));
   END IF;

  END LOOP;
  CLOSE table_spec_cursor;
   
 END LOOP;

END all_tabspec_procedure;
/

세로로 나열된 데이터를 가로로 보여주는 것을 영어로 Pivioting 시킨다고 표현을 하더군요.

SUM(DECODE(...)) 를 보셨는지 모르겠네요.

강력한 기능이죠..이걸 이용하면 pivoting 이 가능합니다.

제가 테스트한 데이터와 쿼리를 올립니다.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

========
# 데이터
========
                           acct        month       amt        
                      ---------- ----------- -----------
    1           1          10
    1           2          10
    1           3          10
    1           4          10
    1           5          10
    1           6          10
    1           7          10
    1           8          10
    1           9          10
    1          10          10
    1          11          10
    1          12          10
    2           1          20
    2           2          20
    2           3          20
    2           4          20
    2           5          20
    2           6          20
    2           7          20
    2           8          20
    2           9          20
    2          10          20
    2          11          20

============
# 데이터 생성
============

CREATE TABLE test1( acct NUMBER(2), month NUMBER(2), amt NUMBER(2));
   
   
INSERT INTO test1 VALUES(1,1,10);
INSERT INTO test1 VALUES(1,2,10);
INSERT INTO test1 VALUES(1,3,10);
INSERT INTO test1 VALUES(1,4,10);
INSERT INTO test1 VALUES(1,5,10);
INSERT INTO test1 VALUES(1,6,10);
INSERT INTO test1 VALUES(1,7,10);
INSERT INTO test1 VALUES(1,8,10);
INSERT INTO test1 VALUES(1,9,10);
INSERT INTO test1 VALUES(1,10,10);
INSERT INTO test1 VALUES(1,11,10);
INSERT INTO test1 VALUES(1,12,10);
INSERT INTO test1 VALUES(2,1,20);
INSERT INTO test1 VALUES(2,2,20);
INSERT INTO test1 VALUES(2,3,20);
INSERT INTO test1 VALUES(2,4,20);
INSERT INTO test1 VALUES(2,5,20);
INSERT INTO test1 VALUES(2,6,20);
INSERT INTO test1 VALUES(2,7,20);
INSERT INTO test1 VALUES(2,8,20);
INSERT INTO test1 VALUES(2,9,20);
INSERT INTO test1 VALUES(2,10,20);
INSERT INTO test1 VALUES(2,11,20);
INSERT INTO test1 VALUES(2,12,20);

==================================
# 쿼리 1 : SUM(DECODE(...)) 이용
==================================
SELECT acct,
       SUM(DECODE(month,1,amt,0)) as MON1,
       SUM(DECODE(month,2,amt,0)) as MON1,
       SUM(DECODE(month,3,amt,0)) as MON1,
       SUM(DECODE(month,4,amt,0)) as MON1,
       SUM(DECODE(month,5,amt,0)) as MON1,
       SUM(DECODE(month,6,amt,0)) as MON1,
       SUM(DECODE(month,7,amt,0)) as MON1,
       SUM(DECODE(month,8,amt,0)) as MON1,
       SUM(DECODE(month,9,amt,0)) as MON1,
       SUM(DECODE(month,10,amt,0)) as MON1,
       SUM(DECODE(month,11,amt,0)) as MON1,
       SUM(DECODE(month,12,amt,0)) as MON1
FROM test1
GROUP BY acct;     

        
==================================
# 쿼리 2 : 다른 함수 이용
==================================
SELECT acct,
   SUM(amt * (1 - ABS(SIGN(month - 1)))) as Mon1,
   SUM(amt * (1 - ABS(SIGN(month - 2)))) as Mon2,
   SUM(amt * (1 - ABS(SIGN(month - 3)))) as Mon3,
   SUM(amt * (1 - ABS(SIGN(month - 4)))) as Mon4,
   SUM(amt * (1 - ABS(SIGN(month - 5)))) as Mon5,
   SUM(amt * (1 - ABS(SIGN(month - 6)))) as Mon6,
   SUM(amt * (1 - ABS(SIGN(month - 7)))) as Mon7,
   SUM(amt * (1 - ABS(SIGN(month - 8)))) as Mon8,
   SUM(amt * (1 - ABS(SIGN(month - 9)))) as Mon9,
   SUM(amt * (1 - ABS(SIGN(month - 10)))) as Mon10,
   SUM(amt * (1 - ABS(SIGN(month - 11)))) as Mon11,
   SUM(amt * (1 - ABS(SIGN(month - 12)))) as Mon12
FROM test1
GROUP BY acct;

두번째 쿼리 만든사람 대단하지 않습니까..
http://www.ncst.ernet.in/~sybase/faq/Q2.3.html 에 있길래 가져왔는데,
아주 감탄스럽습니다..

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

이상 제가 테스트한 내용이구요, 여러분들의 필요에 맞게 활용하시면 되겠네요.
SUM 뿐만 아니라 AVG, MAX도 DECODE함수와 함께 쓰일 수 있습니다.
정보가 더 필요하다 생각되시면 찾아보면 될거같군요.

sample1)
select codeclass 망구분, codedisp 장비종류, codedesc 장비설명,
       sum(decode(equipstatus, '01', cnt, 0)) 운용,
       sum(decode(equipstatus, '02', cnt, 0)) 철거예정,
       sum(decode(equipstatus, '05', cnt, 0)) 불용대상,
       sum(cnt) 시스템수
  from
(SELECT d.codedisp codeclass, c.codedisp , c.codedesc, m.equipstatus, count(*) cnt
  FROM oss_equipmast m, oss_codemast c,oss_codemast d
 WHERE equiptypecode not in ('BTS')
   AND c.category = 'EquipType'
   AND m.equiptypecode = c.code
   AND d.category = 'CodeClass'
   AND c.class = d.code
   AND c.class != 'TRANSMISSION'
 GROUP BY d.codedisp,c.codedisp, c.codedesc, m.equipstatus
)
GROUP BY codeclass, codedisp, codedesc
ORDER BY 1,2;

SELECT MODEL_SEQ_NO
     , MIN(DECODE(EXAM_TYPE, '00002', PLATFORM_FAIL, NULL))    PLATFORM_FAIL
  , MIN(DECODE(EXAM_TYPE, '00003', FUNC_FAIL, NULL))    FUNC_FAIL
  FROM (SELECT A.MODEL_SEQ_NO
       , A.EXAM_TYPE
             , A.GEREXM_LOOP_TIME || '차'|| '(' || SUM(DECODE(A.EXAM_TYPE, '00002', DECODE( C.TESTRESULT_CODE,'00002',DECODE(C.EXAMSTD_SN, 'EF', 0, 1),0 ), 0) ) || ')'  PLATFORM_FAIL     --플랫폼검수불합격
             , A.GEREXM_LOOP_TIME || '차'|| '(' || SUM(DECODE(A.EXAM_TYPE, '00003', DECODE( C.TESTRESULT_CODE,'00002',DECODE(C.EXAMSTD_SN, 'EF', 0, 1),0 ), 0) ) || ')'  FUNC_FAIL         --기능검수불합격
          FROM T_PE_EXAM_INFO A         -- 현재차수
             , (SELECT MODEL_SEQ_NO, EXAM_TYPE, MAX(GEREXM_LOOP_TIME) GEREXM_LOOP_TIME FROM T_PE_EXAM_INFO WHERE MODEL_SEQ_NO = :MODEL_SEQ_NO AND EXAM_MOTION_STATUS = '00004' GROUP BY MODEL_SEQ_NO, EXAM_TYPE ) B         -- 이전차수
             , T_PE_EXAMSTD_RESULT C
         WHERE 1 = 1
           AND A.MODEL_SEQ_NO = :model_seq_no
           AND A.MODEL_SEQ_NO = B.MODEL_SEQ_NO
           AND A.EXAM_TYPE = B.EXAM_TYPE
     AND A.GEREXM_LOOP_TIME = B.GEREXM_LOOP_TIME
           AND A.MODEL_SEQ_NO = C.MODEL_SEQ_NO
           AND A.EXAM_TYPE = C.EXAM_TYPE
           AND A.GEREXM_LOOP_TIME =C.GEREXM_LOOP_TIME
         GROUP BY A.MODEL_SEQ_NO, A.EXAM_TYPE, A.GEREXM_LOOP_TIME)
 GROUP BY MODEL_SEQ_NO;
 
 
 
SELECT VM.MODEL_SEQ_NO
     , VM.GEREXM_LOOP_TIME || '차'|| '(' || SUM(DECODE( TESTRESULT_CODE,'00002',DECODE(EXAMSTD_SN, 'EF', 0, 1),0 )) || ')'  PLATFORM_FAIL
  , V2.FUNC_FAIL
  FROM (SELECT MODEL_SEQ_NO, EXAM_TYPE, MAX(GEREXM_LOOP_TIME) GEREXM_LOOP_TIME
          FROM T_PE_EXAM_INFO
       WHERE MODEL_SEQ_NO = :MODEL_SEQ_NO
     AND EXAM_TYPE = '00002'
     AND EXAM_MOTION_STATUS = '00004'
    GROUP BY MODEL_SEQ_NO, EXAM_TYPE) VM
     , T_PE_EXAMSTD_RESULT V1
     , (SELECT A1.MODEL_SEQ_NO
             , A1.GEREXM_LOOP_TIME || '차'|| '(' || SUM(DECODE( A2.TESTRESULT_CODE,'00002',DECODE(A2.EXAMSTD_SN, 'EF', 0, 1),0 )) || ')'  FUNC_FAIL
          FROM (SELECT MODEL_SEQ_NO, EXAM_TYPE, MAX(GEREXM_LOOP_TIME) GEREXM_LOOP_TIME
                  FROM T_PE_EXAM_INFO
                 WHERE MODEL_SEQ_NO = :MODEL_SEQ_NO
                   AND EXAM_TYPE = '00003'
                   AND EXAM_MOTION_STATUS = '00004'
                 GROUP BY MODEL_SEQ_NO, EXAM_TYPE) A1
             , T_PE_EXAMSTD_RESULT A2
         WHERE 1 = 1
           AND A1.MODEL_SEQ_NO = A2.MODEL_SEQ_NO
           AND A1.EXAM_TYPE = A2.EXAM_TYPE
           AND A1.GEREXM_LOOP_TIME = A2.GEREXM_LOOP_TIME
         GROUP BY A1.MODEL_SEQ_NO, A1.GEREXM_LOOP_TIME) V2
 WHERE 1 = 1
   AND VM.MODEL_SEQ_NO = :MODEL_SEQ_NO
   AND VM.MODEL_SEQ_NO = V1.MODEL_SEQ_NO
   AND VM.EXAM_TYPE = V1.EXAM_TYPE
   AND VM.GEREXM_LOOP_TIME =V1.GEREXM_LOOP_TIME
   AND VM.MODEL_SEQ_NO = V2.MODEL_SEQ_NO
 GROUP BY VM.MODEL_SEQ_NO, VM.GEREXM_LOOP_TIME, V2.FUNC_FAIL
;

--------------------------------------------------------------------------------
BROKEN된 JOB을 자동으로 재실행 시키는 방법
=========================================

1. broken job
~~~~~~~~~~~~~~
Oracle에서 특정 작업을 주기적으로 실행시키기 위해서는 job을 이용하게 된다.
이것은 snp라는 background process가 각 job의 interval간격으로 작업을 실행
하는데, snapshot과 같은 것이 job의 대표적인 예이며, dbms_job package를
이용하여 직접 job을 등록 및 관리, 삭제가 가능하다.

이러한 job이 문제가 발생하여 수행이 오류가 발생하면 1분, 2분, 4분, 8분과
같은 간격으로 자동으로 재실행을 하게 되고, 이 주기가 해당 job의 inerval보다
크게 되면 그때부터는 interval간격마다 job을 실행하도록 시도한다. job의
interval이 1분보다 작으로 interval간격대로 시도하고, 예를 들어 interval이
2분 20초마다이면, 1분, 2분, 이후에는 2분 20초 마다씩 새로 fail된 job을
실행해 본다. 이렇게 fail이 발생한 job을 자동으로 재실행하는 것은 최대 16번
이며, 16번 시도후에는 job이 broken상태가 되어 더 이상 snp process는 시도를
하지 않게 된다.

oracle이 이렇게 16번 시도 후 job을 broken상태로 하는 이유는 16번 시도 때까지
문제가 해결되지 않은 job이라면, 예를 들어 network이 장시간 down되어 snapshot
refresh가 안 되는 것과 같이 문제가 장기화될 가능성이 많고 그러한 job을 계속
시도해 보는 것 자체가 cpu를 많이 소모하는 낭비되는 작업이라는 판단 때문이다.

그러나 일단 job이 broken되면, 그 job을 fail 상태로 만든 원인이 제거된 후에도
여전히 실행되지 않은 상태로 있게 되어 db admin이 수시로 broken된 job이 있는
지를 확인하고 manual하게 dbms_job.run등을 실행하여 다시 실행하도록 할 필요가
있다.

이러한 db admin 작업을 덜고 database 자체에서 broken job에 대해서도 계속
run을 시도하고자 하는 경우 이 문서를 이용하여 작업하면 된다.

2. dba_jobs view에 대해서
~~~~~~~~~~~~~~~~~~~~~~~~~~

dba_jobs(혹은 user_jobs)는 등록된 job에 관한 여러가지 정보를 사용자게 제공
한다.

특히 job의 broken과 관련하여 중요한 몇개의 column에 대해서 살펴본다.

broken : 해당 job이 broken되었으면 Y, 그렇지 않으면 N로 나타난다.
failures : job이 시도되었으나 fail된 숫자이다. 이 숫자가 16이 되면 broken이
Y가 되어 더 이상 snp process는 시도하지 않게 되어 이 숫자도
증가가 되지 않는다.
단, dbms_job.run을 user가 manual하게 실행해도 여전히 문제가 발생
하면 이 숫자가 이미 16이상이라도 시도할 때마다 계속 1씩 증가한다.

next_date: job이 다음에 실행될 시간이다.
job이 실행을 시작하는 시점에 last_date + interval = next_date로
계산한 후 job 실행이 끝나면 (혹은 오류 발생 후) 시작 시점에
계산된 next_date값이 dba_jobs에 기록되어 확인이 가능해 진다.
이 시간이 과거로 되어 있으면, failures가 0이고 broken이 N이라
하더라도 snp는 그 job을 run하려고 시도하지 않는다.

this_date: 이것은 현재 실행되는 job이 실행을 시작한 시간을 나타낸다. 이미
실행이 끝난 job이라면 이 부분은 null로 나타나면 이 컬럼에 값이
있는 job에 대해서는 dba_jobs_running에도 정보가 나타난다.

3. broken된 job을 실행되도록 하는 procedure

먼저, broken된 job을 찾아 broken을 false로 만들어 주고 snp가 다시 실행하도록
next_date를 지정해 주는 procedure를 만든다.
이 procedure는 아래 3-2와 같으며 수행한 기록을 남기기 위해 job_log라는
table을 만들었는데 이 부분은 빼도 무관하다.

3-1 log table 생성
아래 procedure를 실행시키기 전에 먼저 이와 같이 table을 만든다.

SQL> create table job_log (jobno number,
jobname varchar2(30),
jobdate date);

3-2 broken job을 snp가 다시 실행하도록 하기 위해 다음과 같은 release_job을
관리하고자 하는 job의 owner에서 생성한다.

(1) broken job을 선택할 때 dba_jobs 대신에 user_jobs를 이용한다.
dbms_job package는 항상 해당 owner의 job만을 대상으로 작동하므로,
dba_jobs를 확인하고 보이는 job에 대해서 연산하면 owner가 아닌 경우
그러한 job이 없다는 오류가 발생하게 된다.

(2) dbms_job.run을 바로 실행하지 않은 이유는 dbms_job.run은 procedure
내에서 call하지 못하도록 정의되어 실제 사용하면 오류가 발생하거나
수행이 되지 않는다.

(3) broken='Y' 뿐 아니라 failures가 15이상인 것을 함께 check하는 이유는
이 procedure로 인해 일단 broken이 N로 변경된 상태에서 여전히 오류가
있으면 다음 수행 때 broken이 N여서 제외되기 때문이다.

(4) dbms_job.change의 세번째 argument가 next_date인데 이렇게 next_date를
원하는 시간, 혹은 약간의 미래로 맞추어 놓아야 release_job이 수행된
이후 snp가 이 시간에 broken으로 모아진 job을 실행하게 된다.

(5) 결국 broken으로 선택된 job들을 release_jobs가 수행될 때마다 한번씩
snp process로 하여금 다시 시도되도록 지정되어 진다.

(6) procedure source
create or replace procedure release_jobs as
cursor my_broken_jobs is
select job, what from user_jobs where broken = 'Y' or failures > 15;
begin
for broken_jobs in my_broken_jobs
loop
begin
dbms_job.broken(broken_jobs.job,FALSE);
dbms_job.change(broken_jobs.job, null, sysdate+1/1440, null);
insert into job_log values (broken_jobs.job,
broken_jobs.what,
sysdate);
commit;
Exception
when others then
null;
end;
end loop;
end;
/

4. release_jobs를 job으로 등록한다.

위에서 정의한 release_job을 job으로 등록하여 broken된 job을 찾아 실행해주는
작업 자체가 주기적으로 실행되도록 한다.
release_jobs 자체는 network을 타거나 space를 필요로 하는 등의 작업이 아니라,
fail이 발생할 우려는 거의 없다.
이 release_jobs를 등록한 job의 next_date가 미래이고 fail이 없는지만 확인하면,
나머지 broken job들은 여기에서 등록된 job이 관리하게 된다.

SQL>variable job number;
SQL>exec dbms_job.submit(:job, 'RELEASE_JOBS;',sysdate,'sysdate+1/1440');
SQL>exec dbms_job.run(:job);
SQL>commit;
 
출처 : http://kr.blog.yahoo.com/jhoony73/590306.html?p=1&pm=l

hot.sh
# @(#)local.profile 1.8 99/03/26 SMI
umask 022
stty istrip
export ORACLE_BASE=/u01/ora901
export ORACLE_HOME=/u01/ora901
export ORACLE_SID=ORA901
export ORACLE_OWNER=ora901
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export NLS_LANG=American_America.KO16KSC5601
export PATH=$PATH:.:$ORACLE_HOME/bin:/bin:/usr/bin:/usr/ccs/bin:/usr/sbin:/bin:/etc
export EDITOR=vi
/u01/ora901/bin/sqlplus /nolog @/u01/ora901/hot.sql
*********************************************************************

*********************************************************************
hot.sql
connect / as sysdba
set echo on
spool backup.log
rem Starting Hot Backup
!echo 'ORACLE_SID='$ORACLE_SID
!date
archive log list
rem Backup system tablespace
alter tablespace users begin backup;
!date
!cp  /u02/ora901_data/oradata/ORA901/users01.dbf /u02/ora901_data/oradata/ORA901/users01.bak
alter tablespace users end backup;
spool off
exit
*********************************************************************

blade:/var/spool/cron/crontabs #
25 19 * * * su - ora901 -c /u01/ora901/hot.sh
crontab -e가 안될경우
export EDITOR=vi 를 해주시면 됩니다.
환경변수에 디폴트 에디터가 vi로 설정이 되어있지 않아서 이런 문제가 발생합니다.
 
출처 : http://blog.empas.com/myungho/3957282
 

1 . rowid와 rownum의 정의와 사용법을 알고 싶습니다..

rowid : 테이블에 있는 해당 로우를 찾기위해 사용되는 논리적인 정보
ROWID를 분석해보면
AAAArs AAD AAAAUa AAA
------ --- ------ ---
1 2 3 4
1) 6자리 : 데이터 오브젝트 번호
2) 3자리 : 상대적 파일 번호
3) 6자리 : 블록 번호
4) 3자리 : 블록내의 행 번호

어떤곳에서는 "물리적인 정보"라고 적혀있기도 하지만, 곰곰히 생각해보면 물리적인
정보보다는 오라클이 사용하는 "논리적인 정보"라고 보는것이 맞다고 생각됩니다.

rownum : 결과집합에 대한 가상의 순번
따라서 같은 SQL이라고 하더라도, 다른 rownum 을 가질수 있다.

2.각각이 언제,어떻게 사용하는건지 그 둘의 차이점은 뭔지 알려주시면 대단히 감사하겟습니다..

업무에 따라, 혹은 활용면에 있어 여러 각도로 사용할 수 있겠지만, 지금 생각나는 몇가지만
적어본다면,

1) rowid

가.행을 찾아가는 가장 빠른 방법이 rowid 라는 것을 이용.
update emp
set sal = 999
where rowid in (select rowid from emp where ename like 'A%');
==> 따라서 인덱스의 활용과도 연관성이 있을거라 생각됩니다. 인덱스는 인덱스 컬럼과 rowid를
가지고 있으니까요.

나.데이터가 non-unique 한 환경일때, 데이터에 "유일성"을 부여할수있다.
예를 들면, 이전 페이지에 나왔던 데이터가 다음 페이지에 중복되어 나오는것을 방지할수있다.
이순신이라는 사람이 10건 있다고 할때, 이전페이지에 7건이 나왔다고 하면 "다음"버튼을 누르
면 나머지 3명의 이순신이 나와야되는 업무가 있을때 like 를 쓰게되면 10명 모두 나오게 된다.
이때 rowid를 적절히 이용하면 이들에게도 유일성을 보장할 수 있다.
select * from emp
where ename || rowid > :ename || :before_rowid

참고로, 이 원리는 MSSQL2000에서도 그대로 반영되고 있습니다.
MSSQL2000의 인덱스페이지를 살펴보면, unique 일 경우에는 4byte의 "?"컬럼이 생기지않지만,
non-unique 할때는 4byte의 "?" 컬럼이 생겨 값들의 유일성을 보장하고 있는 것을 알수있습니다.
결국 unique라는 옵션 하나만 잘 사용해도 엄청난 수행속도를 보장받을수 있는거죠.

2) rownum

가.개발시 테스트 용도로 사용
100만건의 테이블을 잘못 select 하면 그 결과가 다 나올때까지 기다리는 경우가 있는데.
이때 where rownum < 10; 를 추가하여 실행시키면 기다릴필요가 없겠죠? (물론 ctrl+c 해도 되지
만...)
약간의 응용력만 있다면, 이 기능도 충분히 업무에 활용할수있다고 봅니다. (ex.게시판)

나. copy_t 테이블 생성시...
create table qq_t
as
select rownum no, to_char(rownum, '09') c_no from cdr -- cdr 테이블은 최소한 100건 이상
where rownum<100;

그외에도 개인의 응용력에 따라 유용하게 사용할 수 있을거라 생각됩니다.

from en-core forum
 
출처 : http://blog.empas.com/goldenant/5477949

physical I/O가 많은 상위 10개 테이블 조회하기
 
select table_name,total_phys_io
from ( select owner||'.'||object_name as table_name,
               sum(value) as total_phys_io
         from   v$segment_statistics
         where  owner!='SYS' and object_type='TABLE'
 and  statistic_name in ('physical reads','physical reads direct',
                         'physical writes','physical writes direct')
         group by owner||'.'||object_name
         order by total_phys_io desc)
 where rownum <=10;

lock 걸린 테이블 및 유저찾기
 
column username format a10
column lockwait format a10
column sql_text format a80
column object_owner format a14
column object format a15
select b.username username, c.sid sid, c.owner object_owner,
c.object object, b.lockwait, a.sql_text SQL
from v$sqltext a, v$session b, v$access c
where a.address=b.sql_address
and a.hash_value=b.sql_hash_value
and b.lockwait = 'WAIT'
and b.sid = c.sid
and c.owner != 'SYS';
 
리소스를 많이 차지 하는 sql찾기
 
1) 총 메모리 사용량이 많은 SQL (상위 N개)
SELECT BUFFER_GETS,DISK_READS,EXECUTIONS,SQL_TEXT
FROM (SELECT BUFFER_GETS,DISK_READS,EXECUTIONS,SQL_TEXT
FROM V$SQLAREA
ORDER BY BUFFER_GETS DESC )
WHERE ROWNUM <= :p_rank

2) 평균 메모리 사용량이 많은 SQL (상위 N개)
SELECT BUFFER_GETS,DISK_READS,
EXECUTIONS,BUFFER_PER_EXEC,SQL_TEXT
FROM ( SELECT BUFFER_GETS,DISK_READS, EXECUTIONS,
BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) BUFFER_PER_EXEC,
SQL_TEXT
FROM V$SQLAREA
ORDER BY BUFFER_GETS/DECODE(EXECUTIONS,0,1,EXECUTIONS) DESC )
WHERE ROWNUM <= :p_rank

3) 메모리, 디스크 사용량이 일정 기준치를 넘은 SQL
SELECT BUFFER_GETS,DISK_READS,
EXECUTIONS,BUFFER_GETS/EXECUTIONS,SQL_TEXT
FROM V$SQLAREA
WHERE BUFFER_GETS > :p_val1
OR DISK_READS > :p_val2
OR EXECUTIONS > :p_val3
 
실행중인 쿼리 보기
 
select s.sid, s.username username, s.osuser, s.program, a.sql_text SQL
from v$sqltext a, v$session s
where a.address = s.sql_address
and a.hash_value = s.sql_hash_value
 
출처 : http://blog.empas.com/goldenant
 
 
테이블이 사용중인 블록 크기를 계산해주는 SQL

/*
** Table이 사용하는 블럭 크기를 구하는 스크립트... <<박제용>>
**
** 사용법 : 1) DBA 권한으로 로그인한다.
** 2) SQL> @tab_block [table명]
**
** Notice : sum(blocks)는 사용하는 블럭의 갯수이며 사이즈는 db_block_size를
** 곱하여 얻을 수 있다.
*/
SELECT OWNER, TABLESPACE_NAME, SEGMENT_NAME, SUM(BLOCKS)
FROM DBA_EXTENTS
WHERE SEGMENT_NAME = UPPER('&1')
GROUP BY OWNER, TABLESPACE_NAME, SEGMENT_NAME
/

이미 컴파일된 프로시져소스를 보고싶을 때 사용하는 스크립트

/*
** PL/SQL 소스를 보기위한 스크립트.. <박제용>
**
** 사용법 : find_plsql [프로시져명칭]
**
**
*/
select text
from user_source
where name = upper('&1')
order by line;

테이블을 복사해주는 스크립트 (v8.0 only)

/*
** table을 다른 스키마 혹은 table로 복사 <<박제용>>
**
** Notice) 1. Oracle 8.0 이상에서만 지원.
** 2. sql*net 이 설정되어 있어야만 한다.
** 3. 테이블과 PK만 복사하고 인덱스는 모두 다시 생성해주어야 한다.
** 따라서 테이블을 생성해 주고 입력하는것이 좋다.
** 4. sql*plus 에서만 실행된다.
** 사용법) @tab_copy scott/tiger@link source_table_name target_table_name
**
*/
copy from &1 create &3 using select * from &2

/* 다른 DB로 복사할때는
copy from &1 to &2 create &4 using select * from &3
*/

/* 미리 만들어진 table에 입력할때는
copy from &1 insert &3 using select * from &2
*/

Table Data Size를 정확히 계산해주는 스크립트

/*
** Table Data Size를 정확히 계산해주는 스크립트. <<박제용>>
**
** 사용법 : @tab_size [table_name]
**
*/
analyze table &1 delete statistics;
analyze table &1 compute statistics;

SELECT GREATEST(4, ceil(NUM_ROWS/
( (round(((1958-(INI_TRANS*23))*((100-PCT_FREE)/100))/AVG_ROW_LEN)))) * 2048) TableSize_Kbytes
FROM user_tables
WHERE table_name = upper('&1');

dead lock이 발생했을때 발생시킨 유저와 SQL문을 찾아주는 SQL

/*　　
**　　
**　 사용법　　 :SQL> @find_deadlock
**　 Description : 데드락이 발생할 경우 locking 된 유저와 sql문을 보여준다.
**　　
**　 데드락이 발생한 유저를 kill 하려면.
** Alter system kill session '{serial#},{SID}';
**
*/
Select a.serial#, a.sid, a.username, b.id1, c.sql_text
from v$session a, v$lock b, v$sqltext c
where b.id1 in( select distinct e.id1 from v$session d, v$lock e
where d.lockwait = e.kaddr)
and a.sid = b.sid
and c.hash_value = a.sql_hash_value
and b.request = 0;

딕셔너리에서 해당 키워드에 관한 뷰, 테이블을 찾아주는 SQL

/*
** 딕셔너리로부터 입력한 키워드에 관한 테이블명을 조회한다. <<박제용>>
**
** 사용법 : SQL> @dic_find [키워드(대소문자가림)]
**
*/
col TABLE_NAME format a15
col COMMENTS format a100

select * from dictionary
where COMMENTS like ('%&1%')
/

컬럼명만 가지고 테이블과 설정상태를 찾아주는 SQL

/*
** 컬럼의 스펙과, 소속 테이블을 찾는다. <<박제용>>
**
** 사용법 : SQL> @col_find [컬럼명]
**
*/
col CNAME format a20
col COLTYPE format a10
col NULLS format a5
col DEFAULTVAL format a10

select TNAME, COLNO, CNAME, COLTYPE, WIDTH, NULLS, DEFAULTVAL
from col
where CNAME = UPPER('&1')
/

Constraint 이름으로 해당 테이블과 컬럼찾는 SQL

/*
**=============================================
** CONSTRAINT 이름으로 사용 테이블 찾기
**=============================================
**
** Usage : @Show_Columns Constraint_Name
** Description : Shows The Columns Bound By A Constraint
** 사용예 : SQL> @show_Columns PK_EMPNO
*/
SET VERIFY OFF
CLEAR BREAK
BREAK ON CONSTRAINT_NAME ON TABLES

SELECT SUBSTR(CONSTRAINT_NAME,1,25) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME
FROM ALL_CONS_COLUMNS
WHERE CONSTRAINT_NAME = UPPER('&1');

컬럼에 걸려있는 constraint 를 보여주는 SQL

/*
**=======================================
** 해당 COLUMN에 걸려 있는 CONSTRAINT확인
**=======================================
**
** Usage : @Show_Constraints Table_Name Column_Name
**
** Description : 해당 Table의 Column에 걸려 있는 Constraint를 보여준다.
**
** < 실행 예 >
** SQL> @SHOW_CONSTRAINTS WIDGETS LENGTH
**
*/

SET VERIFY OFF
CLEAR BREAK
BREAK ON TABLES ON COL_NAME
SELECT SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME,
SUBSTR(CONSTRAINT_NAME,1,25) CONSTRAINT_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = UPPER('&1')
AND COLUMN_NAME = UPPER('&2');

PK와 FK간의 연관관계를 찾아 보여주는 SQL

/*　　
**　　
**　 사용법　　 　 :> @Show_Positions　 Parent_Table　 Child_Table　　
**　 Description　 :　 Shows Primary And Foreign Key Positions　　
**　　
**　 WARNING　　 :　 이 문장은 해당 Table의 Constraint생성시 Naming　　　
**　　　　　　　　　 Convention을 따른 경우에 적용되도록 되어 있다.　　
**
*/
SET VERIFY OFF　　
CLEAR BREAK　　
BREAK ON CONSTRAINT_NAME ON TABLES
SELECT SUBSTR(CONSTRAINT_NAME,1,27) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,15) COL_NAME,
SUBSTR(POSITION,1,3) POSITION,
SUBSTR(OWNER,1,7) OWNER
FROM USER_CONS_COLUMNS WHERE TABLE_NAME = UPPER('&1') AND CONSTRAINT_NAME LIKE 'PK%'
UNION
SELECT SUBSTR(CONSTRAINT_NAME,1,27) CONSTRAINT_NAME,
SUBSTR(TABLE_NAME,1,15) TABLES,
SUBSTR(COLUMN_NAME,1,25) COL_NAME,
SUBSTR(POSITION,1,3) POSITION,
SUBSTR(OWNER,1,7) OWNER
FROM USER_CONS_COLUMNS WHERE TABLE_NAME = UPPER('&2') AND CONSTRAINT_NAME LIKE 'FK%'
ORDER BY 1 DESC,4 ASC;

테이블의 특정 컬럼에 중복된 값을 찾는 SQL

/*
**=============================================
** 중복된 값 있는지 찾기
**=============================================
** Usage : @중복찾기.sql [테이블명] [중복을조사할컬럼명]
**
** Warning : 똑같은값이 2개 이상있을때 처음값은 출력 않되고 2번째 값부터 출력됨. <>
*/

select * from &1 A
where rowid >
(SELECT min(rowid) FROM &1 B
WHERE B.&2 = A.&2)
order by &2;