08. 고급 조인 테크닉

분석함수를 이용한 누적매출

select 지점, 판매월, 매출, sum(매출) over (partition by 지점 order by 판매월
                                           range between unbounded preceding and current row) 누적매출
  from 월별지점매출

- 분석함수는 오라클 8i부터 제공

월도를 기준으로 변환한 선분이력

select b. 상품번호, greatest(a . 시작일자， b . 시작일자) 시작일자
     , least(a . 종료일자， b . 종료일자) 종료일자, b. 데이터
  from 월도 a, 선분이력 b
 where b.시작일자 <= a.종료일자
   and b.종료일자 >= a.시작일자

- 겹치는 구간의 시작일자는 두 시작일자 중 큰값, 종료일자는 두 종료일자 중 작은값

복제용 테이블을 활용한 소계

1. 복제기법을 활용한 소계 및 총계
column 부서번호 format a10
select decode(no, 3, null, to char(deptno)) 부서번호
     , decode(no, 1, to char(empno) , 2, '부서계' ， 3, '총계' ) 사원번호
     , sum(sa1) 급여합， round (avg (sal) ) 급여평균
  from emp a, (select rownum no from dual connect by level <= 3)
  group by decode(no, 3, null, to char(deptno))
         , no, decode (no, 1, to char (empno) , 2, '부서계'， 3, '총계' )
  order by 1, 2;

2. 표준 rollup 구문을 활용한 소계 및 총계
break on 부서 번호
column 부서번호 format 9999
column 사원번호 format a10
select deptno 부서 번호
     , case when grouping(empno) = 1 and grouping(deptno) 1 then '총계'
       when grouping(empno) = 1 then '부서계'
       else to char(empno) end 사원번호
, sum(sal) 급여합， round(avg(sal)) 급여평균
  from emp
 group by rollup(deptno, empno)
 order by 1, 2;

서로 다른 상품권 결제 테이블의 설계 방식에 따른 쿼리작성

1. 온라인권번호, 실권번호 두 컬럼을 따로 두고, 레코드별로 둘 중 하나의 컬럼에만 값을 입력
select /*+ ordered use nl(b) use nl(c) use nl(d) */
       a.주문번호， a.결제일자， a.결제금액
     , nvl(b.온라인권번호， c.실권번호) 상품권번호
     , nvl(b.발행일시， d.발행일시) 발행일시  
  from 상품권결제 a, 온라인권 b, 실권 c, 실권발행 d
 where a. 결제일자 between :dtl and :dt2
   and b. 온라인권번호(+) = a. 온라인권번호
   and c. 실권번호(+) = a. 실권번호
   and d. 발행번호(+) = c. 발행번호

2. 상품권구분과, 상품권번호컬럼 구분. 구분: 1 - 온라인권번호, 2 - 실권번호
select /*t ordered use nl(b) use nl(c) use nl(d) */
       a.주문번호， a.결제일자， a .결제금액
     , nvl(b.온라인권번호， c.실권번호) 상품권번호
     , nvl(b.발행일시， d.발행일시) 발행일시
  from 상품권결제 a, 온라인권 b, 실권 c, 실권발행 d
 where a.결제일자 between :dt1 and :dt2
 and b.온라인권번호 (+) = decode(a.상품권구분, '1', a.상품권번호)
 and c.실권번호 (+) = decode(a.상품권구분 '2' , a. 상품권번호)
 and d.발행번호 (+) = c.발행번호

게시판 등의 화면 페이지 처리

1. 인덱스 구성
게시판_X01 : 게시판유형 + 등록일자 DESC + 번호 + 질문유형

2. 쿼리 구성
SELECT /*+ ORDERED USE NL(A) USE_NL(B) USE_NL(C) USE_NL(D) ROWID(A) * /
        A.등록일자， B.변호， A.제목， B.회원명 ， C.게시판유형명， D.질문유형명， X.CNT
  FROM (
        SELECT RID, ROWNUM ID, COUNT (*) OVER () CNT
          FROM (
                SELECT ROWID RID
                  FROM 게시판
                 WHERE 게시판유형 = :TYPE
                 ORDER BY 등록일자 DESC, 질문유형, 번호
         WHERE ROWNUM <= 31
        ) X, 게시판 A, 회원 B, 게시판유형 C, 질문유형 D
  WHERE X.ID BETWEEN 21 AND 30
    AND A.ROWID = X.RID
    AND B.회원번호 = A.작성자번호
    AND C.게시판유형 = A.게시판유형
    AND D.질문유형 = A.질문유형


- 인덱스에 질문유형을 추가하여 모든 테이블을 조인하여 모든 데이터를 출력한 후 SORT를 실행하는것이 아닌
  인덱스 블록만 읽어 SORT까지 우선 처리하고 조인한다.

인조식별자의 장단점

- 장점
 1. 인조식별자를 두면 PK, FK가 단일 컬럼으로 구성되므로 테이블 간 연결 구조가 단순해져서 이들 제약조건을 위해 사용되는 인덱스 저장공간이 최소화
 2. 다중 컬럼으로 조인할 때보다 조인연산을 위한 CPU 사용량이 상대적으로 조금 줄 수 있다. (미미한 정도)

- 단점 
 1. 조인 연산 횟수와 블록 I/O 증가로 더 많은 시스템 리소스를 낭비하기 쉽다.
 2. 엑세스 범위를 줄이지 못하면서 단지 조인을 위해서만 사용되는 PK인덱스가 많이 양산될 수 있다
 3. 데이터 모델을 이해하기 어려워진다. (실질 식별자를 찾기 어려워 엔티티의 가독성이 떨어짐)

=> 업무적으로 이미 통용되는 식별자이거나 유연성/확장성을 고려해 인조식별자를 설계하는 경우를 제외하면 
   가급적 인조식별자를 주지 않는 것이 좋다. 물리설계 단계에서 저장효율과 엑세스 효율 등을 고려하여 결정하는 것이 낫다.