Egloos | Log-in


[Oracle] START WITH~ CONNECT BY PRIOR~

◈ START WITH
 
 - 계층 질의의 루트(부모행)로 사용될 행을 지정 합니다..
 - 서브쿼리를 사용할 수도 있습니다.
 
 
◈ CONNECT BY
 
 - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 합니다.
 - 보통 PRIOR 연산자를 많이 사용 합니다..
 - 서브쿼리를 사용할 수 없습니다..
 - PRIOR
   PRIOR 이 붙는 column 이 가져온 row 의 column을 의미한다. 즉 상위에 존재할 데이타가 되

   게 된다. 어느쪽에 붙느냐 잘 따져 본다.
  
 
◈ CONNECT BY의 실행순서는 다음과 같습니다.
 
 - 첫째 START WITH절
 - 둘째 CONNECT BY 절
 - 세째 WHERE 절 순서로 풀리게 되어있습니다.

 

◈ SYNTEX
  
   SELECT
   FROM
   START WITH
   CONNECT BY PRIOR
   AND
   ORDER SIBLINGS BY

 

   or

  

   SELECT
   FROM
   WHERE
   START WITH
   CONNECT BY PRIOR
   ORDER SIBLINGS BY
  
◈ 이용
   1) 쇼핑목 카테고리 관계 - 대분류, 중분류, 소분류 등을 트리 구조로
   2) 게시판 에서 일반글 과 답글과의 관계 등을 트리 구조로    
  
◈ 데이터가 많아질 경우....
 
 - 첫째로 풀리는 START WITH job='PRESIDENT' job 컬럼에 index가 생성되어 있지 않는다면
    속도를 보장할 수 없습니다.
 
 - 그리고 둘째로 풀리는 CONNECT BY PRIOR empno = mgr 역시 PRIOR 쪽의 컬럼값이 상수가
   되기 때문에 MGR컬럼에 index를 생성하여야 CONNECT BY의 속도를 보장할 수 있습니다.
  
 - 계층구조를 CONNECT BY, START WITH로 풀면 부분범위 처리가 불가능하고 Desc으로
   표현하기가 어렵 습니다.
  

--------------- 설명
-- 아래 예제 1
1) job 이 president 인 row 을 가져온다.
2) 가져온 row 에서 prior 이 붙은 comumn 의 데이타를 가져온다. 여긴선 empno 다.
3) PRIOR empno = mgr  empno 을 mgr 로 사용하는 row 을 가져온다. 기존의 row 상위, 비교해서 가져온 row 하위에 있게 된다.
4) 이제 두번째로 가져온 row 에서 PRIOR empno = mgr 을 실행시킨다.
5) 이런 과정이 연속으로 반복되면서 최종적으로 가져온 data 는 트리 구조를 이루게 된다.(계층구조)
6) LEVEL 은 depth 을 의미한다.

7) empno = PRIOR mgr 한다면 가져온 row 의 mgr을 기준으로 비교하여 data을 가져오게 된다.
   예제 2 참조

 
---- 예제 1
SELECT                  LEVEL,empno,ename, mgr, job  --  LEVEL 은 depth 을 의미한다.
FROM                     emp
START WITH             job = 'PRESIDENT'   -- 직업이 PRESIDENT를 기준으로
CONNECT BY PRIOR empno = mgr -- 사원(empno)과 관리자(mgr)의  관계를 계층

 

-- level 을 공백으로 찍어 본다.

SELECT                  LPAD(' ', 4*(LEVEL-1)) || ename ename,LEVEL, empno, mgr, job 
FROM                     emp
START WITH             job = 'PRESIDENT'   -- 직업이 PRESIDENT를 기준으로
CONNECT BY PRIOR empno = mgr    -- 사원(empno)과 관리자(mgr)의  관계를 계층

-- 결과치
ENAME         LEVEL EMPNO MGR     JOB
KING             1 7839      PRESIDENT
    JONES         2 7566 7839 MANAGER
        SCOTT     3 7788 7566 ANALYST
            ADAMS 4 7876 7788 CLERK
        FORD     3 7902 7566 ANALYST
            SMITH 4 7369 7902 CLERK
        JJS         3 9000 7566 ANALIST
    BLAKE         2 7698 7839 MANAGER
        ALLEN     3 7499 7698 SALESMAN
        WARD     3 7521 7698 SALESMAN
        MARTIN     3 7654 7698 SALESMAN
        TURNER     3 7844 7698 SALESMAN
        JAMES     3 7900 7698 CLERK
    CLARK         2 7782 7839 MANAGER
        MILLER     3 7934 7782 CLERK


----예제 2 - PRIOR 위치 변경
SELECT           LPAD(' ', 4*(LEVEL-1)) || ename ename,LEVEL, empno, mgr, job  -- level 을 공백으로 찍어 본다.
FROM             emp
START WITH       job = 'CLERK'   -- 직업이 CLERK를 기준으로
CONNECT BY empno = PRIOR mgr -- 사원(empno)과 관리자(mgr)의  관계를 계층   


-- 결과치
ENAME             LEVEL EMPNO MGR     JOB
SMITH             1     7369 7902 CLERK
    FORD         2     7902 7566 ANALYST
        JONES     3     7566 7839 MANAGER
            KING 4     7839      PRESIDENT
ADAMS             1     7876 7788 CLERK
    SCOTT         2     7788 7566 ANALYST
        JONES     3     7566 7839 MANAGER
            KING 4     7839      PRESIDENT
JAMES             1     7900 7698 CLERK
    BLAKE         2     7698 7839 MANAGER
        KING     3     7839      PRESIDENT
MILLER             1     7934 7782 CLERK
    CLARK         2     7782 7839 MANAGER
        KING     3     7839      PRESIDENT


---- 예제 3: 조건 절 사용
-- 1) WHERE  절 사용    
SELECT           LPAD(' ', 4*(LEVEL-1)) || ename ename,LEVEL, empno, mgr, job  -- level 을 공백으로 찍어 본다.
FROM             emp
WHERE     ename LIKE '%K%'
START WITH       job = 'PRESIDENT'   -- 직업이 PRESIDENT를 기준으로
CONNECT BY PRIOR empno = mgr -- 사원(empno)과 관리자(mgr)의  관계를 계층

-- 2) CONNECT BY PRIOR 아래에 AND 사용
SELECT           LPAD(' ', 4*(LEVEL-1)) || ename ename,LEVEL, empno, mgr, job  -- level 을 공백으로 찍어 본다.
FROM             emp
START WITH       job = 'PRESIDENT'   -- 직업이 PRESIDENT를 기준으로
CONNECT BY PRIOR empno = mgr -- 사원(empno)과 관리자(mgr)의  관계를 계층
AND      ename LIKE '%K%'

-- 3) LEVEL 조건 사용
SELECT           LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job
FROM             emp
START WITH       job='PRESIDENT'
CONNECT BY PRIOR empno =mgr
AND              LEVEL <= 2

 

---- 예제4 :  각 label별로 급여의 합과 인원수를 구하는 예제
 
SELECT           LEVEL, SUM(sal) salTotal,COUNT(empno) empnCnt
FROM             emp
START WITH       job='PRESIDENT'
CONNECT BY PRIOR empno=mgr     
GROUP BY         LEVEL
ORDER BY         LEVEL


-- 결과치 
     LEVEL   salTotal    empnCnt
---------- ---------- ----------
         1       5000          1
         2       8275          3
         3      13850          8
         4       1900          2

 

CONNECT BY ... START WITH ... 의 제약 조건****************************************

순환 구조 내에서는 JOIN 이 불가능하므로 JOIN 을 수행하는 QUERY 로 구성된 VIEW 에 대해서도 SELECT 가 불가능합니다. 다른 테이블들과의 JOIN 을 하기 위해서는 순환 구조를 가지는 문장을 INLINE VIEW 로 처리한 후 JOIN 에 참여하게 해야 합니다.

 

CONNECT BY 는 순환 구조에서 무한 LOOP 가 발생할 수 있습니다. 이러한 루프는 서로 다른 ROW에 부모와 자식이 존재하는 경우 발생하며, ORA-1436 에러를 발생합니다. 이는 데이터 자체의 논리적 구조의 결함에 의해 발생하는 것입니다. 모든 데이터의 부모 데이터가 항상 존재할 수는 없듯이, 이러한 부분이 있는 지를 검증해보아야만 하고 만약 이렇게 된 겨웅가 있다면 이는 전체적인 구조 설계가 잘못되었거나 대상 테이블에 입력된 데이터가 잘못된 경우 입니다. 대부분 후자의 경우에 무한 LOOP 이 발생하게 됩니다.

 

마지막으로 이와 같은 CONNECT BY 를 사용한 후 ORDER BY 를 사용할 경우 실제로는 결과값이 정렬이 되지는 않았습니다. 하지만 ORACLE 9i 에서부터는 ORDER SIBLINGS BY 라는 구문이 추가되어 실제 값들을 정렬을 수행하면서 순환 구조로 전개가 되어 데이터의 추출이 이루어집니다.


예)
select  level, a.org_id 팀코드, a.org_chief 팀장, c51_emp_pg.emp_nm(a.org_chief) 팀장명, a.org_pdept 상위팀코드
from    c51_om_org_vw a
start   with a.org_id = (select b.org_id
                         from   c51_hrm_emp_vw b
                         where  b.emp_id = '8919688')
connect by prior a.org_pdept = a.org_id
order   by level;

출처 - http://blog.naver.com/ussg/150033753665

by wandori | 2009/03/05 17:43 | DATA ARCHITECTURE | 트랙백 | 덧글(0)

[Oracle] DBMS_JOB 패키지를 이용한 스케줄링

[syntax]
dbms_job.submit(job, what, next_date, interval);

[파라미터 설명]
Argument Name                    Type                 IN/OUT   Default?
-------------------------------- -------------------- -------- --------
JOB                              BINARY_INTEGER       OUT     
WHAT                             VARCHAR2             IN      
NEXT_DATE                        DATE                 IN       DEFAULT <== 다음 수행될 시간
INTERVAL                         VARCHAR2             IN       DEFAULT <== NEXT_DATE 다음 실행 시간( 작은 따옴표로 감싸야함 )
NO_PARSE                         BOOLEAN              IN       DEFAULT
INSTANCE                         BINARY_INTEGER       IN       DEFAULT
FORCE                            BOOLEAN              IN       DEFAULT

[실습]
-- job 등록(이대로 돌려주면 됨)
variable job number;
exec dbms_job.submit(:job, 'D05_DEC_SUMM_SCH_PR;',to_date('20090310', 'YYYYMMDD') + 03/24 ,'add_months(trunc(sysdate), 1) + 03/24');
-- trunc(sysdate) <== trunc 를 통해서 시간을 클리어 시키고 내가 실행 시키고 싶은 시간(ex: + 03/24) 을 더함

-- job 등록 확인
select * from user_jobs;

by wandori | 2009/02/12 18:23 | DATA ARCHITECTURE | 트랙백 | 덧글(0)

[Oracle] 통계정보 생성

[syntax]

ANALYZE  object-clause operation  STATISTICS
         [ VALIDATE STRUCTURE [CASCADE] ]
         [ LIST CHAINED ROWS [INTO table] ]

실습 : SQL> analyze table TBL_OFFICE compute statistics for all indexed columns;

      Table analyzed.

by wandori | 2008/12/16 11:32 | DATA ARCHITECTURE | 트랙백 | 덧글(0)

[DB] MSSQL ORACLE datatye 매핑

Transparent Gateway for Microsoft SQL Server(TG4MSQL) 에서 data type conversion 정리

Data Type Conversion

The gateway converts Microsoft SQL Server data types to Oracle data types as follows:

Table A-1 Data Type Conversions

Microsoft SQL ServerOracleComment
BIGINTNUMBER(19) 
BINARYRAW-
BITNUMBER(3)-
CHARCHAR-
DATETIMEDATEFractional parts of a second are truncated
DECIMALNUMBER(p[,s])-
FLOATFLOAT(49)-
IMAGELONG RAW-
INTEGERNUMBER(10)NUMBER range is -2,147,483,647 to 2,147,483,647
MONEYNUMBER(19,4)-
NCHARNCHAR-
NTEXTLONG-
NVARCHARNCHAR-
NUMERICNUMBER(p[,s])-
REALFLOAT(23)-
SMALL DATETIMEDATEThe value for seconds is returned as 0
SMALL MONEYNUMBER(10,4)-
SMALLINTNUMBER(5)NUMBER range is -32,767 to 32,767
TEXTLONG-
TIMESTAMPRAW-
TINYINTNUMBER(3)-
UNIQUEIDENTIFIERCHAR(36) 
VARBINARYRAW-
VARCHARVARCHAR2-

출처 - http://download-west.oracle.com/docs/cd/B19306_01/gateways.102/b14270/apa.htm

by wandori | 2008/10/22 14:16 | DATA ARCHITECTURE | 트랙백 | 덧글(0)

◀ 이전 페이지          다음 페이지 ▶