SQLP 19회 20151205

1. 아래와 같은 테이블 ERD 가 있을 때 입력된 조회기간에 해당하는 고객번호, 고객명, 주문일자, 주문상품,

상품금액을 가져오는 SQL 을 작성하고, 해당 SQL 을 튜닝하시오 (필요하면 인덱스를 생성하시오)

* 고객 테이블 (100 만건)

- 고객번호(PK)

- 고객명

- 주민번호

- 기타 등등..

* 주문 테이블 -> ERD : 하루 평균 1000건 (3년치 180만건)

- 고객번호(PK)

- 주문번호(PK)

- 계약일자 Date 형임

- 주문상품

- 상품금액

- 기타 등등..

조건 1 : 고객명, 주민번호 는 변수로 들어오지 않을 수 있지만, 변수로 들어온다면 동시에 들어오며, 안되면 둘다 들어오지 않음

조건 2 : 조회기간 (계약일자) 은 반드시 들어오며 최대 7일까지 조회 가능함

조건 3 : 주민번호는 앞에 6자리만 변수로 들어오며, 주민번호 데이터 구성은 앞자리는 정상, 뒷자리 7자리는 암호화 되어 있음

조건 4 : 인덱스는 PK 만 잡혀있음 (고객 - 고객번호 , 주문 - 고객번호, 주문번호 )

2. 대리점에서 계약되는 리스트를 가져오는 쿼리가 아래와 같다. 아래 쿼리에 대해서 옵티마이저의 최적화를 위한

힌트 및 인덱스 설계를 하시오 (필요하면 SQL 변형도 가능함)

** 조회 SQL

SELECT B.대리점번호,

B.계약상태코드,

COUNT(*) AS 계약건수,

MAX(계약일자) AS 최근계약일자

FROM 대리점 A, 계약 B

WHERE A.대리점번호 = B.대리점번호

AND TO_CHAR(B.계약일자,'YYYYMM') : v_yyyymm

AND B.계약상태코드 IN ( SELECT 코드 FROM 코드테이블 WHERE 그룹코드 = 'A01')

AND A.업체번호 = :v_업체번호

GROUP BY B.대리점번호, B.계약상태코드

ORDER BY B.대리점번호

조건 1 : 600개 업체, 3000개 대리점 (하나의 업체에 4~6개 의 대리점이 있다고 가정), 계약 (10만건)

조건 2 : A01 에 해당하는 코드는 3개임

조건 3 : A01 에 해당하는 코드는 향후 더 추가 될 수 있음

Easy Develop