문제

1. 다음 SQL을 부분범위처리가 가능한 형태로 수정하시오. 2010년 3월 4일에 판매되었지만 3월 3일에는 판매되지 않은 아이템을 추출하려는 것이며, SALES 테이블 PK 인텍스는 <ord_date + item_no> 순으로 구성되었다.


SELECT ITEM_NO
FROM   SALES
WHERE  ORD_DATE = '20100304' 
MINUS  
SELECT ITEM_NO
FROM   SALES
WHERE  ORD_DATE = '20100303'

2. PK가 <주문일자 + 주문순번>으로 구성된 '주문' 테이블이 있다. 이 테이블에서 특정 일자(:ORD_DT)의 가장 첫 번째와 마지막 주문 레코드를 추출하는 최적의 SQL을 작성하시오. (단, ORACLE 데이터베이스를 사용 중이며, 해당 일자에 주문 레코드가 한 건도 없으면 O을 리턴해야 함)

3. 다음 중 Oracle에서 대량의 데이터를 빠르게 Insert하는 방안으로 잘못된 것은? (단, 온라인 트랜잭션이 없는 야간 배치 프로그램에서 수행)

① insert를 수행하기 전에 인텍스를 Unusable 상태로 바문다.
② 병렬 모드로 insert한다.
③ insert select 문장에 /*+ append */ 힌트를 사용한다.
④ insert 문장에 아래와 같이 nologging 옵션을 지정한다.

insert into target nologging
select * from source;

4. 아래 update와 insert 문장을 merge 문장으로 바꾸시오.


UPDATE EMP E
SET    (ENAME, DEPTNO, SAL) (SELECT ENAME,
                                    DEPTNO,
                                    SAL
                             FROM   EMP_COPY
                             WHERE  EMPNO E.EMPNO)
WHERE  EXISTS (SELECT 'x'
               FROM   EMP_COPY
               WHERE  EMPNO E.EMPNO)
;

INSERT INTO EMP
SELECT *
FROM   EMP_COPY C
WHERE  NOT EXISTS (SELECT 'x'
                   FROM   EMP
                   WHERE  EMPNO C. EMPNO)
;

5. 5개 근무지역에 각각 2만명의 사원이 배속되서 일하는 회사가 있다. 아래 쿼리에 대한 튜닝 방안으로 올바른 것은?


SELECT 직급.담당업무
FROM   사원
WHERE  근무지역 = 10 
UNION  
SELECT 직급.담당업무
FROM   사원
WHERE  근무지역 = 20
;

Execution Plan
-------------------------------------------------------------------
0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=8 Bytes=120)
1  0  SORT (UNIQUE) (Cost=8 Card=8 Bytes=120)
2  1   UNION-ALL
3  2    TABLE ACCESS (FULL) OF '사원' (Cost=2 Card=4 Bytes=60)
4  3    TABLE ACCESS (FULL) OF '사원' (Cost=2 Card=4 Bytes=60)

① UNION 대신 UNION ALL을 시용한다.
② 쿼리를 아래와 같이 변경한다.

SELECT 직급.담당업무 FROM 사원 WHERE 근무지역 IN (10, 20)

③ 쿼리를 아래와 같이 변경한다.

SELECT 직급.담당업무 FROM 사원 WHERE 근무지역 IN (10, 20) GROUP BY 직급담당업무

④ 근무지역을 선두로 갖는 인텍스를 생성한다.

6. 배치 프로그램 튜닝 방안 중 잘못된 것은?

① 자원 사용량이 적절히 배분되도록 타임 윈도우를 조정한다
② 부분범위처리 기법을 활용해 프로그램 응답 속도를 향상시킨다.
③ 반복적으로 사용할 공통 집합을 정의하고 이를 임시 테이블로 생성한다.
④ 파티션과 병렬처리를 활용한다.

정답

1	{code:sql} SELECT A.ITEM_NO FROM SALES A WHERE A.ORD_DATE = '20100304' AND NOT EXISTS (SELECT 'x' FROM SALES B WHERE B.ITEM_NO = A.ITEM_NO AND B.ORD_DATE = '20100303') {code}
2	{code:SQL} SELECT NVL(MIN(최소주문순번), 0) 최소주문순번, NVL(MAX(최대주문순번), 0) 최대주문순번 FROM (SELECT MIN(주문순번) 최소주문순번 FROM 주문 WHERE 주문일자 = :ORD_DT UNION ALL SELECT MAX(주문순번) 최대주문순번 FROM 주문 WHERE 주문일자 = :ORD_DT) {code}
3	④ nologging은 테이블에 지정하는 옵션이다.
4	{code:SQL} MERGE INTO EMP E USING EMP_COPY C ON (E.EMPNO = C.EMPNO) WHEN MATCHED THEN UPDATE SET E.ENAME = C.ENAME, E.DEPTNO = C.DEPTNO, E.SAL = C.SAL WHEN NOT MATCHED THEN INSERT (EMPNO, ENAME, DEPTNO, SAL) VALUES (C.EMPNO, C.ENAME, C.DEPTNO, C.SAL); {code}
5	③ 예시한 문장에 union 대신 union all을 사용하면 결과가 틀릴 수 있다. 그리고 10만명 중 4만명의 사원 레코드를 인텍스를 통해 읽는다면 오히려 느릴 것이다.
6	② 배치 프로그램은 전체 처리속도 최적화를 목표로 설정해야 한다.

문서에 대하여

최초작성자 : ~xsoft
최초작성일 : 2013년 11월 30일
이 문서는 오라클클럽 오라클 성능 트러블슈팅 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 한국 데이터베이스 진흥원에서 출간한 'SQL 전문가 가이드'를 참고하였습니다.*

3_5장 연습문제 (by xsoft) [2013.11.27]

문제