• 이 문서는 구루비에서 작성하였습니다.
• 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=3901064&
• 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.

1. ROWNUM 유사열

(ROWNUM PSEUDO COLUMNS)

• ROWNUM 사용용도

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

참고 http://www.oracle.com/technology/global/kr/oramag/oracle/06-sep/o56asktom.html

1.1 ROWNUM 동작방식
1.2 ROWNUM을 이용한 튜닝
1.3 ROWNUM을 이용한 페이지 매김
1.4 ROWNUM을 이용한 상위 N개의 쿼리 처리
1.5 ROWNUM 요약

2. 스칼라 하위쿼리

2.1 외부 조인의 필요성 제거

하나 이상의 열을 필요로 할 경우 외부조인으로 돌아간다. 하나 이상의 스칼라하위쿼리를 사용한다. 단일 스칼라 하위쿼리를 이용한 트릭을 쓴다.('||' 사용 등) 객체유형(type)을 사용한다. (사용안해봄)

2.2 단일 쿼리를 이용하여 다수의 테이블로부터 정보집계

인라인 뷰 구현 INLINE_VIEW.LOG

스칼라 서브 쿼리 구현 Scalar_SUB.LOG

2.3 단일 쿼리를 이용하여 다른 테이블/행으로부터 선택적으로 선택

• 행을 테이블 집합에 조인하기
• 조회하기 (SQLLDR과 함께 데이터 조회를 수행함)

3. 해석함수

• 집합지향 SQL에게 결과집합에 대해 어느 정도까지 배열의미론을 사용하게 해준다.
• 행을 그룹으로 묶지 않고도 같은 작업을 수행하게 해준다.

예제)

select deptno,
       ename,
       sal,
       sum(sal) over (partition by deptno order by sal) CumDeptTot,
       sum(sal) over (partition by deptno) SalByDept,
       sum(sal) over (order by deptno, sal) CumTot,
       sum(sal) over () TotSal
  from emp
order by deptno,sal;

• sum(sal) over (partition by deptno order by sal) : 부서별로 낮은 급여 순으로 정렬
• sum(sal) over (partition by deptno) : 부서별 급여합계
• sum(sal) over (order by deptno, sal) : 부서별, 급여별 조회 하면서 급여의 누적
• SUM(SAL) OVER () : 총계

실행로그

3.1 파티션에서 특정 행 찾기

특정 컬럼의 최신 행을 뽑아라.

-- 일반적 쿼리
select * 
  from big_table t1
 where last_ddl_time = ( select max(last_ddl_time) 
                           from big_table t2
		          where t2.owner = t1.owner )

실행로그
=> 대량의 데이터일 경우 상호연관된 하위쿼리의 반복으로 인하여 비효율 발생.

-- 조금 나아진 쿼리
select * 
  from big_table t1, 
     ( select owner, max(last_ddl_time) max_time 
         from big_table 
	group by owner ) t2 
 where t1.owner = t2.owner
   and t1.last_ddl_time = t2.max_time

실행로그
=> 조금 나아지긴 했으나 t1테이블에 대해서 두번 전체스캔을 수행하여 비효율 발생

-- 해석함수를 사용
select owner, last_ddl_time, object_name, object_type
  from 
       ( select t1.*,
                max(last_ddl_time) over (partition by owner) max_time
           from big_table t1 
       )
 where last_ddl_time = max_time;

실행로그
=> t1테이블을 한번만 전체 스캔하면서 최대last_ddl_time을 계산함.

3.2 그룹의 상위 N

• ROW_NUMBER : 정렬 후에 파티션의 모든 행에 고유한 일련의 수를 할당.
• RANK : 파티션의 모든 행에 순위를 부여. 같은 값을 가진 행에는 동일한 순위가 부여됨. 순위사이에는 공백이 있을 수 있음.
• DENSE_RANK : RANK와 같이 파티션의 모든 행에 순위를 부여. 순위는 연속적이어서 순위사이에 공백이 있을 수 없음.

  set linesize 2000
  break on deptno skip 1
  select deptno,
          ename,
  	sal,
  	row_number() over (partition by deptno order by sal desc) rn,
  	rank() over (partition by deptno order by sal desc) rank,
  	dense_rank() over (partition by deptno order by sal desc) dense_rank
     from emp
    order by deptno, sal desc;
  

  

ROW_NUMBER 및 RANK, DENSE_RANK 는 그룹을 파티션으로 나누고, 해당 파티션에서 상위N개를 얻고 싶을 때에만 사용한다. 전체 집합에서 상위 N개를 구하고자 할때는 ROWNUM을 사용한다( ROWNUM의 최적화, p664 ROWNUM을 이용한 상위 N개의 쿼리 처리 참조 )

3.3 전치(피보팅)

부서별로 상위 세개의 봉급을 가로로 나열하라

• DEPTNO, SAL1, SAL2, SAL3

1단계)

select deptno,
	sal,
	dense_rank() over (partition by deptno order by sal desc) dense_rank
   from emp;

2단계)

-- 인라인뷰를 사용한 피벗적용
select deptno, 
       decode( dense_rank,1, sal ) sal1,
       decode( dense_rank,2, sal ) sal2,
       decode( dense_rank,3, sal ) sal3
  from 
       ( select deptno,
		sal,
		dense_rank() over (partition by deptno order by sal desc) dense_rank
	   from emp
       )
 where dense_rank < 3;

3단계)

-- NULL 제거
select deptno, 
       max(decode( dense_rank,1, sal )) sal1,
       max(decode( dense_rank,2, sal )) sal2,
       max(decode( dense_rank,3, sal )) sal3
  from 
       ( select deptno,
		sal,
		dense_rank() over (partition by deptno order by sal desc) dense_rank
	   from emp
       )
 where dense_rank < 3
 group by deptno;

실행로그:해석함수를 이용한 피보팅

3.4 이전 행/다음 행

• LAG : 결과집합의 앞을 본다.
• LEAD : 결과집합의 뒤를 본다.

예제)
다음 테이블에서 FLAG의 날짜 별로 가장 최근 이전/이후 일자를 구하여라.

• FLAG, DT, BF_DT, AF_DT

SELECT FLAG,
DT,
LAG(DT) OVER (PARTITION BY FLAG ORDER BY DT) BF_DT,
LEAD(DT) OVER (PARTITION BY FLAG ORDER BY DT) AF_DT
FROM STUDY_TEST;

결과보기

3.5 병렬 처리

3.6 해석함수 요약

문서정보

• 이 문서는 구루비에서 작성하였습니다.
• 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
• 출처 : http://wiki.gurubee.net/pages/viewpage.action?pageId=3901064&
• 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.