[DB] SQLP 실기 - 2

누적매출을 구하시오

• 지점, 판매월, 매출을 컬럼으로 가지는 테이블의 월별 누적매출을
  윈도우 함수를 사용하여 구하고 사용하지 않고 구하시오

  select 지점, 판매월, 매출,
  sum(매출) over(partition by 지점 order by 판매월)
  from table

  select a.지점, a.판매월, a.매출, sum(b.매출)
  from table a, table b
  where a.지점 = b.지점
  and a.판매월 >= b.판매월
  group by a.지점, a.판매월, a.매출
  order by a.지점, a.판매월
   

실기 2 인덱스, 조인 튜닝

create index 주문 on idx_01(고객번호, 주문일시) local
create index 고객 on idx_02(거주지역코드, 고객명)

select /*+ leading(c) use_nl(o) index(c idx_02) index(o idx_01)*/
o.주문일시, o.주문번호 . . . . 
from 고객 c, 주문 o
where (c.거주지역코드 = 02 and c.고객명 = 김철수)
or    (c.거주지역코드 = 05 and c.고객명 = 홍길동)
and o.고객번호 = c.고객번호
and o.주문실시 between to_date('20150301', 'yyyymmdd')
and to_date('20150314235959', 'yyyymmddhh24miss')
order by o.주문일시, c.고개명

• 파티션 테이블은 인덱스 정의시 파티션 인덱스를 고려하기
• 조인 순서와 방식을 정확히 기술
• 인덱스 적용
• 거주지역 코드와 고객명을 가공하지 않고 정확히 조건지정

검색조건이 있을수도 있고 없을수도 있음

create index table on index_01(고객번호, 주문일자);
create index table on index_02(주문일자);

select /*+index(a index_01)*/ 고객번호, 주문일시, . .
from 주문 a
where :cusid is not null
and 고객번호 = :cusid 
and 주문일자 >= to_date(:dt1, 'yyyymmdd')
and 주문일자 < to_date(:dt2, 'yyyymmdd')+1
union all
select /*+index(b index2)*/ 고객번호, 주문일시 . . .
from 주문 b
where :cusid is null
and 주문일자 >= to_date(:dt1, 'yyyymmdd')
and 주문일자 < to_date(:dt2, 'yyyymmdd')+1
order by 주문일자 desc;

• 고객번호가 있을수도 있고 없을수도 있으므로 항상 최적의 계획 대응
• 인덱스를 대응하여 2개 작성
• 쿼리문도 2개 작성하여 union all로 대응
• 정렬은 항상 마지막에
• 주문일자가 date 형태이므로 between을 사용하려면 시간까지 작성해야 하지만 입력값은 STRING이므로 편하게 date와 부등호로 처리한다

페이징처리, 최근접속일시 없으면 null

create index 고객 on idx_01(고객상태코드, 등록일자, 고객번호);
create index 고객접속이력 on idx_02(고객번호, 접속일시);

select t1.고객번호, t1.고객명, t1.등록일시,
        (select /*+고객접속이력 IDX_02*/max(접속일시)
         from 고객접속이력
         where 고객번호 = t1.고객번호
         and 접속일자 >= trunc(add_month(sysdate, -1))) 최근접속일시
from  (select t1.*, rownum as rown
       from  (select /*+고객 IDX_01 */고객번호, 고객명, 등록일시
              from 고객
              where 고객상태코드='AC'
              order by 등록일시, 고객번호) t1
       where rownum <= :page1*20) t1
where rown > (:page1 -1) * 20 

• 먼저 선택도가 좋은 고객상태코드로 최소한의 고객을 가져온디.
• ROWNUM 과 부등호로 총 페이지를 가져오고 한번더 가공하여 20개만 가져온다.
• 최근 접속일시는 LEFT OUTER 조인보다는 스칼라 서브쿼리로 한다.
• 인덱스로 고객테이블의 정렬작업을 제거한다.

AC상태 고객을 전부 조회

select /*+ leading(a b) full(a) use_hash(b) */
a.고객번호, a.고객명, a.폰번, b.최근고객접속일자
from 고객 a left outer join 
     (select /*+NO_MERGE*/고객번호, max(고객접속일자) 최근고객접속일자
     from 고객접속이력
     where 고객접속일자 >= add_months(sysdate, -1)
     group by 고객번호) b
 on a.고객번호 = b.고객번호 
 where a.고객상태코드 = 'AC'
order by a.고객번호, a.고객명

• all_rows 조회이므로 적절한 full 스캔, hash조인 활용
• swap_join_inputs 등 hash조인의 빌드인풋 조정 (현재는 leading으로 지정)
• 인라인 뷰에서 먼저 최근고객접속이 null이아닌 (고객접속일자 >= add_months(sysdate, -1)을 만족하는) 데이터만 찾고 group by 한다.
• 뷰머징 방지를 위해 no_merge 힌트.
• 인라인뷰를 left outer join 하므로, 최근접속일자가 1달 이상인 고객은 null로 변환한다.